這幾年真的記憶力衰退很多! 過去很熟的 Mysql 都已經變得很陌生!
Mysql Database migration 方法有很多種,其中最傳統的方式就是 mysqldump to other mysql server .
在此小弟寫下來記憶未來有機會才能再翻出來提醒自己.
1.Backup Database on Old Mysql Server
— Backup one Database on Old Server
# mysqldump -u root -p db_name > db_name.sql;— Backup all Database on Old Server
# mysqldump -u root -p –all-databases > DB_backup.sql;
2.Create Database on New Mysql Server— Restore Database to New Mysql Server
— Restore all DB# mysql -u root -p < DB_backup.sql
— Create New db_nameon New MysqlServer
# mysqladmin create db_name
# mysql -u root -p db_name < db_name.sql
3.Show user on old Mysql Server
# mysql
Welcome to the MySQLmonitor. Commands end with ; or g.
Your MySQLconnection id is 369176900
Server version: 5.0.77-log Source distribution
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.
mysql> show grants for da_rose; #da is user name
+—————————————————————————————————————————-+
| Grants for da@% |
+—————————————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘da‘@’%’ IDENTIFIED BY PASSWORD ‘0d4d022f07b97345’ |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `DIxxxx02db`.* TO ‘da‘@’%’ |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `Dxxxx1db`.* TO ‘da‘@’%’ WITH GRANT OPTION |
+———————————————————————————————————————–+
4.Add user on New Mysql Server
# Mysql –p root
mysql> GRANT USAGE ON *.* TO ‘da‘@’%’ IDENTIFIED BY PASSWORD ‘0d4d022f07b97345’ ;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `DIxxxx02db`.* TO ‘da‘@’%’ ;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for da;
+——————————————————————————-+
| Grants for da@% |
+——————————————————————————-+
| GRANT USAGE ON *.* TO ‘da‘@’%’ IDENTIFIED BY PASSWORD ‘0d4d022f07b97345’ |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `Dxxxx1db`.* TO ‘da_rose‘@’%’ |
+——————————————————————————-+
5.Check database connetion status for user site
# mysql -h $new_mysql_server_ip –u user_name -p
mysql> show grants for da;
mysql> show databases ;
▪