MySQL Data migration

I. Introduction to Data migration
The so-called data migration refers to the migration of the original database system to another business system
The reasons for data migration are varied and may be business changes, hardware upgrades, platform switching, or upgrading MySQL databases

Considerations for Migration:
(1), the same version can be migrated
(2), note the version can be used by the engine
(3), low version can be to the high version of the data migration, high version is generally compatible with the low version of the features
(4), high-version data can not be moved to the lower version of the data, there will be data incompatibility situation
(5), migration is to pay attention to export data and backup data, in the event of a migration failure, immediately start the backup scheme to ensure the normal operation of the company's business
(6), in the data migration, to select the minimum number of users, most of the morning between 1-4 points to do data migration
(7), in the data migration, please write your technical documents, after the complete test in the local, you can try data migration
(8) DBAs need joint Support (Master Programmer, Testers, OPS, etc.) to ensure the security and stability of data migration when migrating data

Second, the data migration needs to use some knowledge

1, Database Backup tool mysqldump (1), formal environmental data backup:                            mysqldump-uroot-p123456-h 10.0.0.254-p 3306 zytest--single-transaction-- Flush-logs--routines--events--master-data=2 > zytest.sql--single-transaction option to submit a BEGIN SQL statement before the data is out, Begin does not block how the program and guarantees the consistency of the data--flush-logs began to refresh the log everywhere before,-- Routines Export stored procedures and custom functions--evets export event--master-data append the location and file name of the Binlog to the file. This option will automatically close the--lock-tables (lock table) (2), local use root user backup, Back up a table syntax for a library: Mysqldump-uusername-ppassword dbname table1 table2 >bakcup.sql Example: mysqldump-uroot-pzy123 zytest Vendors >vendors.sql (3), back up multiple libraries, need to add--databasesmysqldump-uroot-pzy123--databases zytest Feng >backup.sql (4), Back up all libraries mysqldump-uroot-pzy123--all-databases >all.sql

2, MySQL command can be data Recovery (1), restore the database (such as: Zytest) mysql-uroot-pzy123 <zytest.sql (2), restore a table of the database vendorsmysql-uroot-pzy123 Zytest <vendors.sql