Instantly share code, notes, and snippets.

Embed
What would you like to do?
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