Using mysqldump and database users

Full process for me when migrating from one Mac OS X dev environment to a new one.

0) Get rid of newer version of MySQL on new MacBook

I had accidentally installed MySQL 8, so I had to remove it because it was newer than my old MacBook.

# Remove binaries
$ brew uninstall mysql

# Remove data/config that is leftover
$ rm -r /usr/local/var/mysql/

1) Install same version of MySQL on new MacBook

# Install version that matched old MacBook
$ brew install mysql@5.7

# Because it is an old version, you have to do a special configuration step
$ echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile

# Start server
$ mysql.server start

2) Dump data on old MacBook

$ mysqldump -uroot --flush-privileges --routines --all-databases > complete_dump.sql

3) Restore data on new MacBook

$ mysql -p -uroot < complete_dump.sql

You may want to take a look at mysqlpump, it can dump all databases, and can export the users as CREATE USER and GRANT statements instead of relying on the mysql system db + mysql_upgrade.

mysqlpump # Dumps (almost) all databases (see [2])
mysqlpump --exclude-databases=% --users # Dumps all user accounts

It's unclear from the docs if you can do mysqlpump --users and get both all databases and the users.

Some system tables get omitted by default, see restrictions.

mysqldump version 10.19 contains the option --system=users wich adds CREATE USER with the GRANT commands into the dump.

mysqldump -h host -u root -p \
    --add-drop-database --routines -B database_name \
    --system=users > backup.sql

source: https://mariadb.com/kb/en/mysqldump/

Connect to your database server and execute:

select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user;

You'll get something like this:

+--------------------------------------------------------+
| concat('show grants for ','\'',user,'\'@\'',host,'\'') |
+--------------------------------------------------------+
| show grants for 'mariadb.sys'@'localhost'              |
| show grants for 'mysql'@'localhost'                    |
| show grants for 'root'@'localhost'                     |
+--------------------------------------------------------+

Capture the output to some temporary file.

And then cycle through each line in that temporary file, sending it against your mysql server, capturing the output.

Output will be something that you can use to reconstruct users on another server:

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION    

Here's a script I'm currently using:

mysql -u${BACKUP_DB_USER} -p${BACKUP_DB_PASSWORD} -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt
sed '1d' user_list_with_header.txt > ./user.txt
while read user; do  mysql -u${BACKUP_DB_USER} -p${BACKUP_DB_PASSWORD} -e"$user" > user_grant.txt; echo "-- ${user}" >> user_privileges.txt; sed '1d' user_grant.txt >> user_privileges.txt; done < user.txt
echo "flush privileges" >> user_privileges.txt;
awk '{print $0";"}'  user_privileges.txt > all_user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt

You'll have all grant statements in the file all_user_privileges_final.sql.

Of course, you can limit your initial query to list only user(s) you want.

You can only use the option --system=users to get the user's dump from mysql/MariaDB if mysql client is for MariaDB.

For example:

1. check mysql --version. If output is like mysql Ver 15.1 Distrib 10.6.8-MariaDB then

2. mysqldump -h <hostname> -u <usename> -p --system=users > mysqldb_users.sql