加快 MySQL 数据迁移-Toy模板网

这篇具有很好参考价值的文章主要介绍了加快 MySQL 数据迁移。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

一、先导

自建目标实例（略）

二、源导出

1. 生成查询用户权限的SQL语句

2. 生成权限的SQL语句

3. 生成创建非主键索引的SQL语句

4. 导出源库结构

5. 导出源库数据

三、目标导入

1. 目标实例设置

2. 处理结构导出文件

3. 导入结构

4. 创建用户与权限

5. 导入数据

6. 添加索引

四、后续

配置到源的复制和目标实例自己的从库

一、先导

自建目标实例（略）

二、源导出

1. 生成查询用户权限的SQL语句

mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock -e "
select concat('show grants for \`',user,'\`@\`',host,'\`;') from mysql.user where user not like 'mysql.%';" -N > show_grants.sql

2. 生成权限的SQL语句

mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock -N < show_grants.sql > grants.sql
sed -i 's/$/&;/g' grants.sql;

3. 生成创建非主键索引的SQL语句

mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock -e "
select concat('alter table \`',table_schema,'\`.\`',table_name,'\` ',create_index,';')
from (
select table_schema, table_name, group_concat(if(index_name='PRIMARY',concat('add primary key (',index_columns,')'),concat('add ',if(NON_UNIQUE=0, ' unique ', ' '), 'index \`',index_name,'\` (',index_columns,')'))) create_index
from (
select table_schema, table_name, index_name, NON_UNIQUE, group_concat(concat('\`',column_name,'\`', if(sub_part is null,'',concat('(',sub_part,')'))) order by seq_in_index) index_columns
from information_schema.statistics
where table_schema in ('test', 'test_jhy') and index_name <> 'PRIMARY'
and (table_schema, table_name, index_name) not in (
select t1.table_schema, t1.table_name, t1.index_name
from information_schema.statistics t1, information_schema.columns t2
where t1.table_schema in ('test', 'test_jhy') and index_name <> 'PRIMARY'
and t1.table_schema = t2.table_schema and t1.table_name = t2.table_name and t1.column_name = t2.column_name
and t2.extra='auto_increment')
group by table_schema, table_name, index_name, NON_UNIQUE) t
group by table_schema, table_name) t;" -N > create_index.sql

4. 导出源库结构

# max_allowed_packet 不能大于目标库的值
mysqldump -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock --no-data --single-transaction --triggers --routines --events --set-gtid-purged=OFF --master-data=2 -e --max_allowed_packet=1073741824 -e --net_buffer_length=16384 --databases test test_jhy | gzip > dump_db.sql.gz

5. 导出源库数据

mysqldump -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock --single-transaction --set-gtid-purged=OFF --master-data=2 -e --max_allowed_packet=1073741824 -e --net_buffer_length=16384 --no-create-db --no-create-info --skip-triggers --databases test test_jhy | gzip > dump_data.sql.gz

三、目标导入

1. 目标实例设置

bulk_insert_buffer_size=1073741824
innodb_buffer_pool_size=34359738368
innodb_flush_log_at_trx_commit=0
max_allowed_packet=1073741824
concurrent_insert=AUTO
innodb_autoinc_lock_mode=2
skip-log-bin

2. 处理结构导出文件

gunzip dump_db.sql.gz
# 删除除主键外的索引。不能删除主键，否则建表时遇到自增列报错：
# Incorrect table definition; there can be only one auto column and it must be defined as a key
sed -i '/ KEY `/d;' dump_db.sql
# 删除闭括号前的逗号
sed -i ':a;N;$!ba;s/,\n) ENGINE=InnoDB/\n) ENGINE=InnoDB/g' dump_db.sql

3. 导入结构

mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < dump_db.sql

4. 创建用户与权限

mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < grants.sql

5. 导入数据

gunzip dump_data.sql.gz
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < dump_data.sql

6. 添加索引

mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < create_index.sql

四、后续

配置到源的复制和目标实例自己的从库

（1）分析主库
mysql -uroot -p12345 -S /data/18251/mysqldata/mysql.sock < analyze_table.sql

（2）配置到腾讯云 MySQL 实例的复制，观察一段时间
change master to
master_host='172.18.3.1',
master_port=18251,
master_user='root',
master_password='12345',
master_log_file='aaaaa',
master_log_pos=xxxxx;

start slave;
show slave status\G

（3）停主库
mysqladmin -uroot -p12345 -S /data/18251/mysqldata/mysql.sock shutdown
ps -ef | grep mysqld

（4）启用binlog
vim my.cnf

（5）启主库，观察复制状态
mysqld_safe --defaults-file=/home/mysql/my.cnf &

（6）停主库
mysqladmin -uroot -p12345 -S /data/18251/mysqldata/mysql.sock shutdown

（7）复制到从库数据目录
cd /data/18251/
rm dump_data.sql
scp -r * 10.10.10.2:/data/18251/

（8）启主库但不开启复制
mysqld_safe --defaults-file=/home/mysql/my.cnf --skip-slave-start &

（9）确定并记录主库 master 位点
show master status;

（10）删除从库的 auto.cnf
cd /data/18251/mysqldata/
rm auto.cnf

（11）启从库但不开启复制
mysqld_safe --defaults-file=/home/mysql/my.cnf --skip-slave-start &

（12）配置从库到主库的复制
stop slave;
reset slave all;

change master to
master_host='10.10.10.1',
master_port=18251,
master_user='repl',
master_password='12345',
master_log_file='bbbbb',
master_log_pos=yyyyy;

start slave;
show slave status\G

（13）启动主库复制
start slave;
show slave status\G

（14）观察从库复制状态，直到追平后的准实时复制正常
show slave status\G

（15）分析从库
mysql -uroot -p12345 -S /data/18251/mysqldata/mysql.sock < analyze_table.sql文章来源地址https://www.toymoban.com/news/detail-692006.html

到了这里，关于加快 MySQL 数据迁移的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！