加快 MySQL 数据迁移

2023-10-14 09:57:02 浏览数 (2)

一、先导

自建目标实例（略）

二、源导出

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

代码语言：javascript复制
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语句

代码语言：javascript复制
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock -N < show_grants.sql > grants.sql
sed -i 's/$/&;/g' grants.sql;

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

代码语言：javascript复制
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock -e "
select concat('alter table ',table_schema,'.',table_name,' add ',concat('index ',index_name),' (',col,');') create_index
  from (
select table_schema,table_name,index_name,group_concat(column_name order by seq_in_index) col
  from information_schema.statistics 
 where table_schema in ('test', 'test_jhy') and index_name <> 'PRIMARY'
 group by table_schema,table_name,index_name
 order by table_schema,table_name,index_name) t;" -N > create_index.sql

4. 导出源库结构

代码语言：javascript复制
# 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. 导出源库数据

代码语言：javascript复制
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. 目标实例设置

代码语言：javascript复制
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. 处理结构导出文件

代码语言：javascript复制
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. 导入结构

代码语言：javascript复制
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < dump_db.sql

4. 创建用户与权限

代码语言：javascript复制
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < grants.sql

5. 导入数据

代码语言：javascript复制
gunzip dump_data.sql.gz
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < dump_data.sql

6. 添加索引

代码语言：javascript复制
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 statusG

（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 statusG

（13）启动主库复制 start slave; show slave statusG

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

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

数据迁移 mysql 权限 数据 索引

0 人点赞