difference.txt

## difference.txt
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+

# 5.5 doesn't have password_expired

## dump-and-upgrade-mysql-user.sh
#!/bin/bash
HOST=192.168.100.112
USER=root
PASS=cheeseburger

LOCALHOST=127.0.0.1
LOCALUSER=root
LOCALPASS=cheeseburger

# Awesome snoyes and mgriffin on freenode/#mysql suggested a much better way!
mysql -h$HOST -u$USER --password="$PASS" -BNe "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user" | mysql -h $LOCALHOST -u $LOCALUSER --password="$LOCALPASS"


# OLD WAY

#DATE=`date +%Y-%m-%d-%H-%M-%S`
#MYSQLDUMP_DIR="/tmp/mysql.user-$DATE"
#mkdir -p $MYSQLDUMP_DIR
## Dump just the mysql.user data, as we already have the updated structure
#mysqldump -h $HOST -u $USER --password="$PASS" --skip-secure-auth --single-transaction --no-create-info --flush-logs mysql user > "$MYSQLDUMP_DIR/mysql.user-db-data.sql"
#sed -i -e 's/ VALUES / (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,Alter_routine_priv,Create_user_priv,ssl_type,ssl_cipher,x509_issuer,x509_subject,max_questions,max_updates,max_connections,max_user_connections,Event_priv,Trigger_priv,Create_tablespace_priv,plugin,authentication_string,password_expired) VALUES /g' "$MYSQLDUMP_DIR/mysql.user-db-data.sql"
#sed -i -e 's/),(/,"N","N","N","",null,"N"),(/g' "$MYSQLDUMP_DIR/mysql.user-db-data.sql"
#sed -i -e 's/);/,"N","N","N","",null,"N");/g' "$MYSQLDUMP_DIR/mysql.user-db-data.sql"
#mysql -h $LOCALHOST -u $LOCALUSER --password="$LOCALPASS" < "$MYSQLDUMP_DIR/mysql.user-db-data.sql"

## mysql-5.0.user.dump.example.sql
LOCK TABLES `user` WRITE;
INSERT INTO `user` VALUES ('%','root','cheese','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0),('%','debian-sys-maint','cheese','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N','N','N','N','N','','','','',0,0,0,0);
UNLOCK TABLES;