[教學] Mysql DB Migration - mysqldump

這幾年真的記憶力衰退很多! 過去很熟的 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_name on New Mysql Server

  # mysqladmin create db_name

  # mysql -u root -p db_name < db_name.sql

3.Show user on old Mysql Server

  # mysql

  Welcome to the MySQL monitor.  Commands end with ; or \g.

  Your MySQL connection 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 ;

▪