实现目标
一般操作主数据库。从数据库不能直接被操作，只能用来做备份（操作从数据库，可能会导致Slave_SQL_Running：No），两个数据库分别在不同的服务器上。当从服务器连接状态出问题，应该及时检查，并排查问题，恢复数据。当主服务器数据库奔溃，应该能及时恢复。
主从复制并不能解决来自sql的攻击。应当定时的在从服务器上，将数据定期备份为文件。
环境
- ubuntu 16.04(2个)
- virtualBox
- mysql 5.7.23（分别安装到两个ubuntu服务器）
- 主库IP：192.168.9.108
- 从库IP：192.168.9.34
主库mysql配置
修改mysql配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# server-id 服务器唯一标识
server-id = 1
# log_bin 启动MySQL二进制日志
log_bin = master-bin
# 指定记录二进制日志的数据库
log_bin_index = master-bin.index
# 指定记录二进制日志的数据库
binlog_do_db = chart
# 指定不记录二进制日志的数据库
binlog_ignore_db = mysql
bind-address = 0.0.0.0
重启mysql
service mysqld restart
主库防火墙配置
$ sudo iptables -D INPUT -p sctp --dport 3306 -j DROP
#增加配置，只允许特定地址访问数据库端口
$ sudo iptables -A INPUT -p tcp -s 192.168.9.34 --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等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除
主mysql用户授权
用于创建访问该mysql的用户
# 先重启下mysql
sudo service mysql restart
# 进入mysql
# 创建用户
CREATE USER 'slaver'@'192.168.9.34' IDENTIFIED BY 'slavepassword';
# 用户ip限制
GRANT REPLICATION SLAVE ON *.* TO 'slaver'@'192.168.9.34';
# 刷新权限
flush privileges;
查看master状态
SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000007 | 154 | chart | mysql | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.33 sec)
记住file列和position列，会在从mysql中用到。
主库备份，为从库的第一次数据同步准备数据
#此处以备份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
从服务器配置文件
server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
replicate-do-db = chart
replicate-ignore-db = mysql
log_bin = /var/log/mysql/mysql-bin.log
从库数据库配置
# iptables -A OUTPUT -p tcp -d master_ip --dport 3306 -j ACCEPT
#删除可能已经存在的配置，避免出现多条重复记录
$ sudo iptables -D OUTPUT -p tcp -d 192.168.9.108 --dport 3306 -j ACCEPT
#增加配置
$ sudo iptables -A OUTPUT -p tcp -d 192.168.9.108 --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等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除
还原数据库
$ 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
从库链接主库，创建线程
# 在从库mysql命令行中
CHANGE MASTER TO MASTER_HOST='192.168.9.108', MASTER_USER='slaver', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=154;
# 启动 同步线程
start slave;
# 查看状态
show slave status\G;
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
# 这里两个都为yes说明主从复制搭建成功
网友评论