拥有较大数据的mysql迁移方案_java 大量数据mysql迁移方案-CSDN博客

本文提供了三种迁移大型MySQL数据库的方法。方案一利用mysqldump和LOAD DATA INFILE实现快速导入；方案二涉及停服复制数据文件并压缩传输；方案三通过binlog同步确保数据一致性。每种方案都有其适用场景和技术细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

方案1

In this case, normally, the best solution is a mysqldump using the --tab option like this:

mysqldump --tab=/path/to/serverlocaldir --single-transaction <database> table_a

tab option produce 2 file, one file -table_a.sql- that contains only the table create statement and the oher file -table_a.txt- contains tab-separated data.

Now you can create your new table

create table table_b like table_a;

Then you simply load data in your new table via LOAD DATA without care for the table's name.

LOAD DATA INFILE '/path/to/serverlocaldir/table_a.txt' 
  INTO TABLE table_b FIELDS TERMINATED BY '\t' ...

LOAD DATA is usually 20 times faster than using INSERT statements.

LOAD DATA速度比INSERT语句要快，这里其实倒入也可以使用mysqlimport命令

方案2

I recently moved a 30GB database with the following stragegy:

Old Server

Stop mysql server
Copy contents of datadir to another location on disk (~/mysqldata/*)
Start mysql server again (downtime was 10-15 minutes)
compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata)
copy the compressed file to new server

New Server

install mysql (don't start)
unzip compressed file (tar -xzvf mysqldata.tar.gz)
move contents of mysqldata to the datadir
Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (mysql will generate these)
Start mysql

这种方法就是直接复制数据库的文件结构，要求必须相同的mysql版本，和相同的配置才可以用这种方法

方案3

If you are considering migrating to another DB Server with the exact same version of MySQL, you may want torsync the datadir from the old server to the new server.

This will work regardless of InnoDB file layout or even the presence of MyISAM tables.

install the same version of mysql on ServerB that ServerA has
On ServerA, run RESET MASTER; to erase all binary logs before the rsycn process. If binary logging is not enabled, you can skip this step.
On ServerA, run SET GLOBAL innodb_max_dirty_pages_pct = 0; from mysql and about 10 minutes (This purges dirty pages from the InnoDB Buffer Pool. It also helps perform a mysql shutdown faster) If your database is all MyISAM, you can skip this step.
rsync /var/lib/mysql of ServerA to /var/lib/mysql on ServerB
Repeat Step 3 until an rsync takes less than 1 minute
service mysql stop on ServerA
Perform one more rsync
scp ServerA:/etc/my.cnf to ServerB:/etc/.
service mysql start on ServerB
service mysql start on ServerA (optional)

Essentially, here is what such a script would like this

其实这个跟方案2是一样的。只是操作方法不同而已，当然要求也是一样的

reference

http://serverfault.com/questions/445489/how-to-efficiently-dump-a-huge-mysql-innodb-database

http://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another

http://dba.stackexchange.com/questions/24116/mysql-perform-a-big-data-migration-between-tables

https://matthew.mceachen.us/blog/faster-mysql-dumps-and-loads-with-tab-and-use-threads-1047.html