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