通过 mysqldump 搭建基于 gtid MySQL 5.7 主从复制

安装主从 MySQL 5.7

# 主 MySQL5.7useradd mysql /sbin/nologincd /usr/localtar -xvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gzmv mysql-5.7.23-linux-glibc2.12-x86_64/ mysqlcd mysqlmkdir data confvim conf/my.cnf-------------------------------------------[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.socksymbolic-links=0log-error=/tmp/mysqld.logpid-file=/usr/local/mysql/data/mysqld.pidserver-id=10port=3388gtid_mode=onenforce_gtid_consistency=onlog_binbinlog_format=row-------------------------------------------export PATH=$PATH:/usr/local/mysql/binmysqld --defaults-file=/usr/local/mysql/conf/my.cnf --initialize --user=mysqlmysqld_safe --defaults-file=/usr/local/mysql/conf/my.cnf --user=mysql &# 从 MySQL5.7useradd mysql /sbin/nologincd /usr/localtar -xvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gzmv mysql-5.7.23-linux-glibc2.12-x86_64/ mysqlcd mysqlmkdir data confvim conf/my.cnf-------------------------------------------[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.socksymbolic-links=0log-error=/tmp/mysqld.logpid-file=/usr/local/mysql/data/mysqld.pidserver-id=20port=3388gtid_mode=onenforce_gtid_consistency=onlog_binbinlog_format=row-------------------------------------------export PATH=$PATH:/usr/local/mysql/binmysqld --defaults-file=/usr/local/mysql/conf/my.cnf --initialize --user=mysqlmysqld_safe --defaults-file=/usr/local/mysql/conf/my.cnf --user=mysql &

主从节点更改 root@'localhost' 密码和创建复制账号

set sql_log_bin=0;alter user root@'localhost' identified by 'MySQL5.7';create user root@'%' identified by 'MySQL5.7';grant all on *.* to root@'%';flush privileges; set sql_log_bin=1;

在主节点生成测试数据

#!/bin/bashsysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua \--mysql-host=192.168.1.225 \--mysql-port=3388 \--mysql-user=root \--mysql-password=MySQL5.7 \--oltp-test-mode=complex \--oltp-tables-count=10 \--oltp-table-size=1000000 \--threads=10 \--time=120 \--report-interval=10 \--db-driver=mysql prepare

将主节点的数据传到从节点

mysqldump -S /tmp/mysql.sock -u root -pMySQL5.7 \--all-databases --triggers --routines --events \--single-transaction --quick | mysql -h 192.168.1.226 -P 3388 -u root -pMySQL5.7

将从服务器指定到主服务器

change master to master_host='192.168.1.225',master_port=3388,master_user='root',master_password='MySQL5.7',master_auto_position=1;start slave;show slave status\G