Migration DB MySQL / MariaDB

Serveur Source

mysqldump mydb -u myuser -pP@ssw0rd |pigz > /var/mydb.sql.gz

mysql> SELECT user,host,password FROM mysql.user WHERE user='myuser';
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| myuser	   | localhost | *FFCB02F67B3F444B093A3A20CB5CB10481C0807C |
+------------------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'myuser'@'localhost';
+--------------------------------------------------------------------------------------------------------------+
| Grants for myuser@localhost                                                                                  |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*FFCB02F67B3F444B093A3A20CB5CB10481C0807C'|
| GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'localhost'                                                     |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Voir également

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

Serveur Destination

CREATE DATABASE mydb;
-- CREATE USER 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*FFCB02F67B3F444B093A3A20CB5CB10481C0807C';
-- REVOKE ALL PRIVILEGES ON *.* FROM 'myuser'@'localhost';
GRANT USAGE ON *.* TO 'myuser'@'localhost' IDENTIFIED BY PASSWORD '*FFCB02F67B3F444B093A3A20CB5CB10481C0807C';
GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'localhost';
-- FLUSH PRIVILEGES;

zcat /var/mydb.sql.gz |mysql -D mydb

Autres

SELECT USER,db FROM mysql.db

mysqldump mysql --tables user --where="user='myuser'" --skip-add-drop-table --skip-add-locks --no-create-info --replace --complete-insert