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