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
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock -N < show_grants.sql > grants.sql
sed -i 's/$/&;/g' grants.sql;
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
# 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
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
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
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
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < dump_db.sql
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < grants.sql
gunzip dump_data.sql.gz
mysql -uwxy -p12345 -S /data/18253/mysqldata/mysql.sock < dump_data.sql
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