MySQL 5.6到MySQL 8的主从复制（通过5.7版本做桥接实现）

use spacex;
create table space_praise_record (
  userid bigint(20) not null default '0' comment '用户id',
  objectid bigint(20) not null default '0' comment '对象id,作品id或者分享id',
  type smallint(6) not null default '0' comment '0 作品； 1 分享',
  createtime timestamp not null default current_timestamp,
  status smallint(6) not null default '1' comment '状态 0 取消赞 1 未读点赞 2 已读点赞 ',
  touserid bigint(20) not null default '-1',
  primary key (userid,objectid,type),
  key inx_to_userid (touserid,userid,status),
  key inx_objectid (objectid,type,status,createtime),
  key index_1 (touserid,status),
  key inx_touserid_createtime (touserid,createtime)
) comment='点赞记录表';

/home/mysql/mysql-5.7/bin/mysqldump -uwxy -p123456 -h172.19.1.90 -P30000 --single-transaction --triggers --routines --events --set-gtid-purged=OFF --master-data=2 -e --max_allowed_packet=1073741824 -e --net_buffer_length=16384 --databases vvmobileliveconfig vvmobilelivemetadata --no-data --no-create-db --skip-add-drop-table > dump_schema.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




# 替换掉存储程序中的定义者，因为源库中的用户有可能在目标库中不存在
sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i dump_schema.sql

# 替换掉高版本中新定义的保留字，如 rank
sed 's/ Rank=/ `Rank`=/g;s/ Rank</ `Rank`</g;s/,Rank)/,`Rank`)/g;s/,rank,/,`rank`,/g;s/\.rank,/\.`rank`,/g;s/AS rank,/AS `rank`,/g' -i dump_schema.sql

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

# 导入结构到 MySQL 8 实例
mysql -uwxy -p123456 -S /data/18251/mysqldata/mysql.sock < dump_schema.sql

create database space;
use space;
create table space_praise_record (
  userid bigint(20) not null default '0' comment '用户id',
  objectid bigint(20) not null default '0' comment '对象id,作品id或者分享id',
  type smallint(6) not null default '0' comment '0 作品； 1 分享',
  createtime timestamp not null default current_timestamp,
  status smallint(6) not null default '1' comment '状态 0 取消赞 1 未读点赞 2 已读点赞 ',
  touserid bigint(20) not null default '-1',
  primary key (userid,objectid,type),
  key inx_to_userid (touserid,userid,status),
  key inx_objectid (objectid,type,status,createtime),
  key index_1 (touserid,status),
  key inx_touserid_createtime (touserid,createtime)
) engine=innodb default charset=utf8mb4 comment='点赞记录表';

# 继续处理上一步的导出文件

# 将引擎从 innodb 改为 blackhole
# sed 's/ENGINE=InnoDB/engine=blackhole/g' -i dump_schema.sql

# 去掉分区子句中的 ENGINE = InnoDB
# sed 's/ ENGINE = InnoDB//g' -i dump_schema.sql

# 导入 5.7 
/home/mysql/mysql-5.7/bin/mysql -h127.0.0.1 -P18252 -uwxy -p123456 < dump_schema.sql

stop slave;
reset slave all;

change master to
master_host='10.10.10.1',
master_port=3306,
master_user='u1',
master_password='123456',
master_log_file='mysqlbinlog.000001',
master_log_pos=120;

change replication filter replicate_do_table = (spacex.space_praise_record), replicate_rewrite_db = ((space, spacex));

-- 如果是整库复制
-- change replication filter replicate_do_db = (vvmobileliveconfig,vvmobilelivemetadata);

start slave;

stop slave;
reset slave all;

change master to
master_host='10.10.10.2',
master_port=3306,
master_user='u1',
master_password='123456';

change replication filter replicate_do_table = (space.space_praise_record);

-- 如果是整库复制
-- change replication filter replicate_do_db = (vvmobileliveconfig,vvmobilelivemetadata);

mysqldump -u u1 -p123456 -S /data/3306/mysqldata/mysql.sock --no-create-info --quick --apply-slave-statements --single-transaction --master-data=1 space space_praise_record | mysql -u u1 -p123456 -h10.10.10.1 -P3306 -Dspace

cat dump_and_load_data.sh 

#!/bin/bash
source ~/.bash_profile

date

/home/mysql/mysql-5.7/bin/mysqldump -uwxy -p123456 -h172.19.1.90 -P30000 --single-transaction --skip-triggers --set-gtid-purged=OFF --master-data=1 -e --max_allowed_packet=1073741824 -e --net_buffer_length=16384 --databases vvmobileliveconfig vvmobilelivemetadata --no-create-db --skip-add-drop-table --no-create-info --quick --apply-slave-statements > /data/18252/dump_data.sql

date

/home/mysql/mysql-5.7/bin/mysql -h127.0.0.1 -P18252 -uwxy -p123456 < /data/18252/dump_data.sql

date

nohup ~/dump_and_load_data.sh > ~/dump_and_load_data.log 2>&1 &

cat create_index.sh 

#!/bin/bash
source ~/.bash_profile

sed -i '1i set sql_log_bin=0;' create_index.sql

date

/home/mysql/mysql-5.7/bin/mysql -h127.0.0.1 -P18252 -uwxy -p123456 < create_index.sql

date

nohup ~/create_index.sh > ~/create_index.log 2>&1 &

mysql -uwxy -p123456 -S /data/18251/mysqldata/mysql.sock -e "
select concat('analyze table \`',table_schema,'\`.\`',table_name,'\`;') 
  from information_schema.tables 
 where table_schema in ('vvmobileliveconfig', 'vvmobilelivemetadata');" -N > analyze_table.sql

mysql -uwxy -p123456 -S /data/18251/mysqldata/mysql.sock < analyze_table.sql