mysql主从重新同步

1、主库导出全库

 　　export_mysql.sh

#! /bin/bash

start_time=`date "+%Y-%m-%d %H:%M:%S"`


#To do
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob  -uroot -p123456 --master-data=2 -A > master.sql
#To do


end_time=`date "+%Y-%m-%d %H:%M:%S"`

duration=`echo $(($(date +%s -d "${end_time}") - $(date +%s -d "${start_time}"))) | awk '{t=split("60 s 60 m 24 h 999 d",a);for(n=1;n<t;n+=2){if($1==0)break;s=$1%a[n]a[n+1]s;$1=int($1/a[n])}print s}'`

echo "start_time:  $start_time" >>time.log
echo "time diff :  $duration" >>time.log

2、从库删除库

drop database data;

注意：从库删除除了(information_schema、performance_schema、mysql、sys)外的所有库

3、将数据主库导出的数据文件复制到从库

4、从库导入数据文件

　　import_mysql.sh

#! /bin/bash

start_time=`date "+%Y-%m-%d %H:%M:%S"`


#To do
mysql -uroot -p123456 < master.sql
#To do


end_time=`date "+%Y-%m-%d %H:%M:%S"`

duration=`echo $(($(date +%s -d "${end_time}") - $(date +%s -d "${start_time}"))) | awk '{t=split("60 s 60 m 24 h 999 d",a);for(n=1;n<t;n+=2){if($1==0)break;s=$1%a[n]a[n+1]s;$1=int($1/a[n])}print s}'`

echo "start_time:  $start_time" >>time.log
echo "time diff :  $duration" >>time.log

5、从库重置主从复制

head -n 30 master.sql

找到类似如下信息的语句：

-- CHANGE MASTER TO MASTER_LOG_FILE='db1-bin.000078', MASTER_LOG_POS=194;

stop slave;

reset slave;

CHANGE MASTER TO MASTER_HOST='192.168.92.11', \
MASTER_PORT=3306, \
MASTER_USER='replica', \
MASTER_PASSWORD='replica', \
MASTER_LOG_FILE='db2-bin.000078', \
MASTER_LOG_POS=194;

CHANGE MASTER报错：
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
解决：
CHANGE MASTER TO MASTER_AUTO_POSITION=0;