MySQL两台机器_mysql两台机器数据copy---源自官方文档

1 2.11.13Copying MySQL Databases to Another Machine2 In cases where you need to transfer databases between different architectures, you can use mysqldump to create a file containing SQL statements. You can then transfer the file to the other machine and feed it asinput to the mysql client.3

4 Use mysqldump --help to see what options are available.5

6 The easiest (although not the fastest) way to move a database between two machines is to run the following commands on the machine on which the database islocated:7

8 mysqladmin -h 'other_hostname'create db_name9 mysqldump db_name | mysql -h 'other_hostname'db_name10 If you want to copy a database froma remote machine over a slow network, you can use these commands:11

12 mysqladmin create db_name13 mysqldump -h 'other_hostname' --compress db_name |mysql db_name14 You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:15

16 mysqldump --quick db_name | gzip >db_name.gz17 Transfer the file containing the database contents to the target machine and run these commands there:18

19 mysqladmin create db_name20 gunzip < db_name.gz |mysql db_name21 You can also use mysqldump and mysqlimport to transfer the database. For large tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full path name of the directory you use to store the output frommysqldump.22

23 First, create the directory forthe output files and dump the database:24

25 mkdir DUMPDIR26 mysqldump --tab=DUMPDIR db_name27 Then transfer the files inthe DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:28

29 mysqladmin create db_name # create database30 cat DUMPDIR/*.sql | mysql db_name # create tables in database31 mysqlimport db_name DUMPDIR/*.txt # load data into tables32 Do not forget to copy the mysql database because that is where the grant tables are stored. You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.33

34 After you import the mysql database on the new machine, execute mysqladmin flush-privileges so that the server reloads the grant table information.35

36 Note37 You can copy the .frm, .MYI, and .MYD files for MyISAM tables between different architectures that support the same floating-point format. (MySQL takes care of any byte-swapping issues.) See Section 15.2, “The MyISAM Storage Engine”.