# 主服务器运行sql# 如果主服务器之前有数据，或正在持续读写，需要同步之前数据则加锁手动dump# flush table with read lock；# mysqldump 到 从服务器show master status；# unlock tables；# 从服务器运行# 直接用root同步，省略单独建账户步骤CHANGE MASTER TO MASTER_HOST='主服务器地址', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='MYSQL_ROOT_PASSWORD', MASTER_LOG_FILE='MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS; start slave;
主服务新建数据库，表，插入数据，从服务器上验证 master/my.cnf[mysqld]log-bin = /var/lib/mysql/binlogserver-id = 1; binlog_do_db = db1 # 需同步的DB, 默认全部; binlog_do_db = db2; binlog_ignore_db = db3 # 忽略的DB; binlog_ignore_db = db4
slaver1/my.cnf[mysqld]server-id = 21 # 每台服务器名字不一样即可; binlog_do_db = db1 # 需同步的DB, 默认全部; binlog_do_db = db2; binlog_ignore_db = db3 # 忽略的DB; binlog_ignore_db = db4
slaver2/my.cnf[mysqld]server-id = 22; binlog_do_db = db1 # 需同步的DB, 默认全部; binlog_do_db = db2; binlog_ignore_db = db3 # 忽略的DB; binlog_ignore_db = db4
使用docker启动三个mysql测试#!/usr/bin/bashset -eMYSQL_ROOT_PASSWORD="123"echo "正在创建一主二从Mysql"docker run --name mysql-master -h mysql-master -e MYSQL_ROOT_PASSWORD="$MYSQL_ROOT_PASSWORD" -d mysql:5.7docker run --name mysql-slaver1 -h mysql-slaver1 --link mysql-master:master -e MYSQL_ROOT_PASSWORD="$MYSQL_ROOT_PASSWORD" -d mysql:5.7docker run --name mysql-slaver2 -h mysql-slaver2 --link mysql-master:master -e MYSQL_ROOT_PASSWORD="$MYSQL_ROOT_PASSWORD" -d mysql:5.7echo "正在修改my.cnf配置文件"docker exec mysql-master bash -c 'cat << EOF >> /etc/mysql/my.cnf[mysqld]log-bin = /var/lib/mysql/binlogserver-id = 1EOF'docker exec mysql-slaver1 bash -c 'cat << EOF >> /etc/mysql/my.cnf[mysqld]server-id = 21EOF'docker exec mysql-slaver2 bash -c 'cat << EOF >> /etc/mysql/my.cnf[mysqld]server-id = 22EOF'echo "正在重启所有Mysql服务器"sleep 5sdocker restart mysql-master mysql-slaver1 mysql-slaver2# 全新安装，不考虑主服务器之前需要同步的数据# 如需同步之前数据，先锁定主服务器只读，mysqldump出数据# 手动备份到从服务器再执行同步配置echo "正在获取主服务器log_bin文件和位置"_tmp=$(docker exec -i mysql-master mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "flush table with read lock; show master statusG unlock tables")MASTER_LOG_FILE=$(echo "$_tmp" | awk '/File:/ {print $2}')MASTER_LOG_POS=$(echo "$_tmp" | awk '/Position:/ {print $2}')echo "----------------------------"echo " File: $MASTER_LOG_FILE"echo " Position: $MASTER_LOG_POS"echo "----------------------------"echo "正在设置从服务器同步配置"sleep 5docker exec mysql-slaver1 mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='$MYSQL_ROOT_PASSWORD', MASTER_LOG_FILE='$MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS; start slave"docker exec mysql-slaver2 mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "CHANGE MASTER TO MASTER_HOST='master', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='$MYSQL_ROOT_PASSWORD', MASTER_LOG_FILE='$MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS; start slave"echo "设置完成, 查看从服务器运行状态"sleep 5echo "----------------------------"docker exec mysql-slaver1 mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "show slave statusG" | grep -iP "IO_Running|SQL_Running"docker exec mysql-slaver2 mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "show slave statusG" | grep -iP "IO_Running|SQL_Running"echo "----------------------------"
# 输出正在创建一主二从Mysql048e5f4b09c05364176ec1cebc76b0cc090af54994e3708d21a71a9c6cabba861ceabd786c752eb30fa5eef8308f1756fd339c9cc8dfe2004adcc699885b89b205056dfe221304c722d04654ffe28913117c2c39c59290f109111c36710235ff正在修改my.cnf配置文件正在重启所有Mysql服务器mysql-mastermysql-slaver1mysql-slaver2正在获取主服务器log_bin文件和位置mysql: [Warning] Using a password on the command line interface can be insecure.---------------------------- File: binlog.000003 Position: 154----------------------------正在设置从服务器同步配置mysql: [Warning] Using a password on the command line interface can be insecure.mysql: [Warning] Using a password on the command line interface can be insecure.设置完成, 查看从服务器运行状态----------------------------mysql: [Warning] Using a password on the command line interface can be insecure. Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_SQL_Running_State: Slave has read all relay log; waiting for more updatesmysql: [Warning] Using a password on the command line interface can be insecure. Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates----------------------------
测试验证