准备工作
1. 主从数据库版本最好一致
2. 主从数据库内数据保持一致
主数据库: 121.199.27.227 /ubuntu 16.04 MySQL 5.7.21 (阿里云)
从数据库: 182.254.149.39 /ubuntu 16.04 MySQL 5.7.21 ( 腾讯云 )
防火墙配置
配置主服务器只允许特定 IP 访问数据库的端口, 避免不必要的攻击
主库防火墙配置
- # iptables -A INPUT -p tcp -s slave_ip --dport 3306 -j ACCEPT
- # 删除可能已经存在的配置, 避免出现多条重复记录
- $ sudo iptables -D INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT
- $ sudo iptables -D INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
- $ sudo iptables -D INPUT -p tcp --dport 3306 -j DROP
- $ sudo iptables -D INPUT -p udp --dport 3306 -j DROP
- $ sudo iptables -D INPUT -p sctp --dport 3306 -j DROP
- # 增加配置, 只允许特定地址访问数据库端口
- $ sudo iptables -A INPUT -p tcp -s 182.254.149.39 --dport 3306 -j ACCEPT
- $ sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
- $ sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
- $ sudo iptables -A INPUT -p udp --dport 3306 -j DROP
- $ sudo iptables -A INPUT -p sctp --dport 3306 -j DROP
- $ sudo iptables -L -n
- # 保存配置
- $ sudo apt-get install iptables-persistent
- $ sudo netfilter-persistent save
- # 配置被保存到 / etc/iptables/rules.v4 /etc/iptables/rules.v6 这两个文件下面,
- # 最好确认一下实际保存的内容, 尤其是安装了 denyhosts 等其他安全软件的情况下,
- # 可能会记录了多余的规则, 需要手工删除
从库防火墙配置
- # iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT
- # 删除可能已经存在的配置, 避免出现多条重复记录
- $ sudo iptables -D OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT
- # 增加配置
- $ sudo iptables -A OUTPUT -p tcp -d 121.199.27.227 --dport 3306 -j ACCEPT
- $ sudo iptables -L -n
- # 保存配置
- $ sudo apt-get install iptables-persistent
- $ sudo netfilter-persistent save
- # 配置被保存到 / etc/iptables/rules.v4 /etc/iptables/rules.v6 这两个文件下面,
- # 最好确认一下实际保存的内容, 尤其是安装了 denyhosts 等其他安全软件的情况下,
- # 可能会记录了多余的规则, 需要手工删除
主数据库 master 配置
1. 修改 mysql 配置
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
在 [mysqld] 部分进行如下修改:
- [mysqld]
- log-bin = /var/log/mysql/mysql-bin.log #开启二进制日志, 默认是注释掉的, 我们去掉注释
- server-id = 1 #设置 server-id
- bind-address = 0.0.0.0 #默认是 127.0.0.1, 此处我们设置为任意地址, 放开远程访问, 这么操作之前一定要确保防火墙配置正确, 否则会产生安全风险
2. 重启 mysql, 创建用于同步的用户账号
创建用户并授权: 用户: repl 密码: slavepass
- $ sudo service mysql restart
- $ mysql -u root -p -e "CREATE USER'repl'@'182.254.149.39'IDENTIFIED BY'slavepass';" #创建用户
- $ mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO'repl'@'182.254.149.39';" #分配权限
- $ mysql -u root -p -e "flush privileges;" # 刷新权限
3. 查看 master 状态, 记录二进制文件名 (mysql-bin.000001) 和位置(333802):
- $ mysql -u root -p -e "SHOW MASTER STATUS;"
- Enter password:
- +------------------+----------+--------------+------------------+-------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+------------------+-------------------+
- | mysql-bin.000001 | 333802 | | | |
- +------------------+----------+--------------+------------------+-------------------+
4. 主库备份, 为从库的第一次数据同步准备数据
使用如下脚本产生数据库备份文件
- # 此处以备份 wordpress 数据库为例子
- datadump=`which mysqldump`
- mysqluser="root"
- userpass="password"
- wordpressdb="wordpress"
- backupwordpress_sql=$wordpressdb.`date +%Y%m%d`.sql
- if $datadump -u $mysqluser --password=$userpass -h localhost --opt $wordpressdb> $backupwordpress_sql 2>&1
- then
- echo "backup $wordpressdb success"
- else
- echo "backup $wordpressdb error"
- exit 1
- fi
- # 检验文件尾部是否存在 -- Dump completed on, 如果存在不存在, 则说明备份出错了
- if [ 0 -eq "$(sed'/^$/!h;$!d;g'$backupwordpress_sql | grep -c"Dump completed on")" ];
- then
- echo "backup $wordpressdb error"
- exit 1
- else
- echo "backup $wordpressdb success"
- fi
执行脚本, 确保最后输出备份成功
- $ cd ~
- $ sudo bash backup_wordpress.sh
从服务器 slave 配置
1. 修改 mysql 配置
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
修改 server-id, 每个数据库的 server-id 要求是唯一的, 不能相互冲突
- [mysqld]
- server-id = 2 #设置 server-id, 必须唯一
- log_bin = /var/log/mysql/mysql-bin.log #日志也最好打开
2. 首次还原数据库:
- $ sudo service mysql restart
- $ scp -P 22 -r root@121.199.27.227:~/wordpress.*.sql ./
- # 删除可能存在的一行警告信息, 这行警告信息可能导致我们无法恢复数据
- $ sed -i "/^mysqldump: [Warning] Using a password on the command line interface can be insecure./d" wordpress.*.sql
- $ mysql -u root -p -e "drop database wordpress;"
- $ mysql -u root -p -e "create database wordpress;"
- $ mysql -u root -p wordpress <wordpress.*.sql
3. 重启 mysql, 打开 mysql 会话, 执行同步 SQL 语句(需要主服务器主机名, 登陆凭据, 二进制文件的名称和位置):
$ mysql -u root -p -e "CHANGE MASTER TO MASTER_HOST='121.199.27.227', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=333802;"
4. 启动 slave 同步进程:
$ mysql -u root -p -e "start slave;"
5. 查看 slave 状态:
- $ mysql -u root -p -e "show slave statusG;"
- Enter password:
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 121.199.27.227
- Master_User: repl
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000001
- Read_Master_Log_Pos: 9448236
- Relay_Log_File: VM-114-251-ubuntu-relay-bin.000002
- Relay_Log_Pos: 17780
- Relay_Master_Log_File: mysql-bin.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: No
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- ...
当 Slave_IO_Running 和 Slave_SQL_Running 都为 YES 的时候就表示主从同步设置成功了接下来就可以进行一些验证了, 比如在主 master 数据库的 test 数据库的一张表中插入一条数据, 在 slave 的 test 库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效, 还可以关闭 slave(mysql>stop slave;), 然后再修改 master, 看 slave 是否也相应修改(停止 slave 后, master 的修改不会同步到 slave), 就可以完成主从复制功能的验证了
还可以用到的其他相关参数:
master 开启二进制日志后默认记录所有库所有表的操作, 可以通过配置来指定只记录指定的数据库甚至指定的表的操作, 具体在 mysql 配置文件的 [mysqld] 可添加修改如下选项:
- # 不同步哪些数据库
- binlog-ignore-db = mysql
- binlog-ignore-db = test
- binlog-ignore-db = information_schema
- # 只同步哪些数据库, 除此之外, 其他不同步
- binlog-do-db = game
如之前查看 master 状态时就可以看到只记录了 test 库, 忽略了 manual 和 mysql 库
参考链接
基于 ssl 的 mysql 的主从复制及排错
MySQL 主从复制 (Master-Slave) 实践
Linux 配置 mysql 主从同步的 iptables 设置
iptables 只拒绝某个 ip 的所有连接
How to save rules of the iptables? [duplicate]
MySQL 主从复制与主主复制
mysql-proxy 实现读写分离
mysql 主从同步不一致后的解决方法
来源: https://www.thinksaas.cn/group/topic/839311/