MySQL 主从复制之 多个库同步到一个库 + 主从同步脚本

由于微服务分库导致无法跨库统计查询，因此利用 MySQL 主从复制的方式，将主服务器上的多个数据库同步到从服务器上的单个数据库上，从库设置只读，仅用于统计查询。

环境准备

1、通过 docker 创建 2 个数据库

[root@node3 ~]# docker ps -a
CONTAINER ID        IMAGE               PORTS                               	NAMES
f6c2b2c01858        mysql:5.7         33060/tcp, 0.0.0.0:3308->3306/tcp   		mysql3306
48ffbe49fea3        mysql:5.7         33060/tcp, 0.0.0.0:3307->3306/tcp   		mysql3307

2、主库 创建 2 个数据库，分别创建一个表，如下图

3、从库创建一个数据库，并把主库里面的表手动传输到从库里

参数配置 

1、docker 主库 mysql3306

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4


log_timestamps=SYSTEM
default-time-zone='+8:00'
server_id=3306
log-bin=mysql-bin
binlog_format=row

gtid-mode=on
enforce-gtid-consistency=ON

binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys

2、docker 从库 mysql3307 设置

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4

log_timestamps=SYSTEM
default-time-zone='+8:00'
character-set-server=utf8

server_id=3307

replicate-rewrite-db=school->com_sch
replicate-rewrite-db=company->com_sch

# replicate-wild-do-table=com_sch.student
# replicate-wild-do-table=com_sch.employee

gtid-mode = ON
enforce-gtid-consistency=ON

replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

3、创建同步账号，同步

# 主库执行
grant replication slave on *.* to 'mysync'@'%' identified by 'Mysync@123';

# 从库
change master to master_host='192.168.26.129',master_user='mysync',master_password='Mysync@123',master_port=3306,master_auto_position=1;

4、通过上面方式同步，只能同步数据，不能同步结构，下面提供主从同步脚本

exe_sql.sh

#!/bin/bash

function exesql(){

    LOG_NAME=`date +%Y%m%d%H%M`.txt
    PREFIX=logs/
    LOG=$PREFIX$LOG_NAME
    echo "Current File Is: "$LOG


    STATUS=`docker exec $1 bash -c 'mysql -u'$2' -p'$3' -e "show slave status\G"'`

    echo "$STATUS" | sed -n '/Last_Error/,/Skip_Counter/p' | sed '$d' | awk '{ if (NR==1){ split($0,arr,"Query:"); print arr[2] }}' | sed 's/^[ \t]*//' > $LOG
    echo "$STATUS" | sed -n '/Last_Error/,/Skip_Counter/p' | sed '$d' | awk '{ if(NR!=1){print $0}}' >> $LOG

    echo "Executed Gtid Next Is: "$4

    # Format to single line SQL statement
    # By python
    SQL=`python -c 'import get_sql; print get_sql.Getsql("'$LOG'")'`

    echo "Execute Sql Is: "$SQL

    if [ "$SQL" != 'NoData' ]; then

        /usr/bin/expect <<-EOF

        set timeout 30
        spawn docker exec -it $1 /bin/bash
        expect "/#" { send "mysql -u$2 -p'$3'\r"; }

        expect "mysql>" { send "$SQL;\r" }
        expect "mysql>" { send "stop slave;\r" }
        expect "mysql>" { send "set session gtid_next='$4';\r" }
        expect "mysql>" { send "begin;commit;\r" } 
        expect "mysql>" { send "set session gtid_next='AUTOMATIC';\r" }
        expect "mysql>" { send "start slave;\r" }
        expect eof

        EOF

    fi
}

get_sql.py 

masterdb = ['`school`', '`company`']
slavedb = '`com_sch`'

def Getsql(log_name):

    with open(log_name) as lines:
        array=lines.readlines()
        array2=[]
        for i in array:
            i=i.strip('\r\n')
            array2.append(i)

    sql = "".join(array2)
    sql = sql[1:-1]

    for db in masterdb:
        if sql.find(db) != -1:
            val = sql.replace(db, slavedb)
            break
        else:
            val = "NoData"

    return (val)

if __name__ == "__main__":
    print Getsql('logs/202012281848.txt')

start_slave.sh   启动脚本，这里有3个参数需要改成对应的环境，mysql 容器名，用户名和密码

#!/bin/bash

USER=root
PASSWD=!root123
MYSQL=mysql3307

STATUS=`docker exec $MYSQL bash -c 'mysql -u'$USER' -p'$PASSWD' -e "show slave status\G;" 2>/dev/null'`

IOSQL=`echo "$STATUS" | grep -E "Slave_IO_Running|Slave_SQL_Running" | head -2`
ERROR=`echo "$STATUS" | grep -E "Last_Errno" | head -1`

IO_STATUS=`echo "$IOSQL" | grep "Slave_IO_Running" | awk '{print $2}' | tr -d '^M'`
SQL_STATUS=`echo "$IOSQL" | grep "Slave_SQL_Running" | awk '{print $2}' | tr -d '^M'`
LAST_ERRNO=`echo "$ERROR" | grep "Last_Errno" | awk '{print $2}' | tr -d '^M'`

GTID=`echo "$STATUS" | sed -n '/Executed_Gtid_Set/p' | awk '{print $2}'`
ID=`echo $GTID | awk -F ":" '{print $1}'`
NUM=`echo $GTID | awk -F ":" '{print $2}' | awk -F "-" '{print $2}' | tr -d '\r'`
NUM=$(($NUM+1))
GTID_NUM=$ID":"$NUM

echo $GTID_NUM

if [ $IO_STATUS = 'Yes' -a $SQL_STATUS = 'Yes' ]; then
    echo "Slave is Running"
elif [ $LAST_ERRNO = 1146 -o $LAST_ERRNO = 1049 ]; then
    echo "Slave is autorepair..."
    source exe_sql.sh; exesql $MYSQL $USER $PASSWD $GTID_NUM
elif [ $LAST_ERRNO = 1304 ]; then
    echo "Procedure is wrong..."

    /usr/bin/expect <<-EOF

    set timeout 30
    spawn docker exec -it $MYSQL /bin/bash
    expect "/#" { send "mysql -u$USER -p'$PASSWD'\r"; }
    expect "mysql>" { send "stop slave;\r" }
    expect "mysql>" { send "set session gtid_next='$GTID_NUM';\r" }
    expect "mysql>" { send "begin;commit;\r" } 
    expect "mysql>" { send "set session gtid_next='AUTOMATIC';\r" }
    expect "mysql>" { send "start slave;\r" }
    expect eof

    EOF

fi