1. 使用mysqldump
cat dump_and_replication.sh
代码语言：javascript复制#!/bin/bash
source ~/.bashrc
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -D db1 -e "
set global sql_log_bin=0;
alter table t1 drop primary key, drop index Index_1, drop index Index_2;
"
date
mysqldump -uroot -p123456 -h192.168.1.36 -P3306 --quick --no-create-info --apply-slave-statements --single-transaction db1 t1 --master-data=1 --set-gtid-purged=OFF | mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -D db1
date
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -D db1 -e "
stop slave;
alter table t1 add primary key (id), add index Index_1 (c1,c2), add index Index_2 (c3,c4);
start slave;"
date
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -D db1 -e "set global sql_log_bin=1;"2. 使用mydumper
下载安装：https://github.com/mydumper/mydumper
cat dumper_and_loader.sh
代码语言：javascript复制#!/bin/bash
source ~/.bashrc
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -D db1 -e "set global sql_log_bin=0;"
rm -rf /data1/mydumper/*
date
mydumper -h 192.168.1.36 -P 3306 --user root --password 123456 -o /data1/mydumper/ -t 8 -B db1 -T t1
cat ~/drop_index.sql >> /data1/mydumper/t1-schema.sql
date
myloader -u root -p 123456 -o -t 16 -d /data1/mydumper/ -S /data/3306/mysqldata/mysql.sock
master_log_file=`cat /data1/mydumper/metadata | grep Log | head -1 | awk -F': ' '{print $2}'`
master_log_pos=`cat /data1/mydumper/metadata | grep Pos | head -1 | awk -F': ' '{print $2}'`
change_master="stop slave; reset slave all; change master to master_host='192.168.1.36', master_port=3306, master_user='root', master_password='123456', master_log_file='$master_log_file', master_log_pos=$master_log_pos; start slave; show slave statusG"
echo $change_master
date
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -Ddb1 < ~/add_index.sql
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -e "$change_master"
date
mysql -uroot -p123456 -S /data/3306/mysqldata/mysql.sock -D space -e "set global sql_log_bin=1;"cat drop_index.sql
代码语言：javascript复制alter table t1 drop index idx1, drop index idx2, drop index idx3;cat add_index.sql
代码语言：javascript复制alter table t1 add index idx1 (c1), add index idx2 (c2), add index idx3 (c3);
