/difference.txt
Last active Sep 10, 2015
Difference in columns mysql.user between 5.0.95 and 5.6.19
| +------------------------+-----------------------------------+------+-----+---------+-------+ | |
| | 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 |
| #!/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" |
| 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment