ubuntu 16.04配置MySQL主从同步的配置方法

准备工作

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等其他安全软件的情况下，#可能会记录了多余的规则，需要手工删除

1.修改mysql配置

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]log-bin = /var/log/mysql/mysql-bin.log #开启二进制日志，默认是注释掉的，我们去掉注释server-id = 1 #设置server-idbind-address = 0.0.0.0 #默认是127.0.0.1，此处我们设置为任意地址，放开远程访问，这么操作之前一定要确保防火墙配置正确，否则会产生安全风险

创建用户并授权：用户：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;" #刷新权限

$ 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 |       |         |          |+------------------+----------+--------------+------------------+-------------------+

使用如下脚本产生数据库备份文件

#此处以备份wordpress数据库为例子datadump=`which mysqldump`mysqluser="root"userpass="password" wordpressdb="wordpress"backupwordpress_sql=$wordpressdb.`date +%Y%m%d`.sqlif $datadump -u $mysqluser --password=$userpass -h localhost --opt $wordpressdb > $backupwordpress_sql 2>&1then  echo " backup $wordpressdb success"else  echo " backup $wordpressdb error"  exit 1fi#检验文件尾部是否存在 “-- 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

1.修改mysql配置

$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]server-id = 2 #设置server-id，必须唯一log_bin         = /var/log/mysql/mysql-bin.log #日志也最好打开

$ 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 -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;"

$ mysql -u root -p -e "start slave;"

$ mysql -u root -p -e "show slave status\G;"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:    ...

还可以用到的其他相关参数：

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的[mysqld]可添加修改如下选项：

# 不同步哪些数据库 binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # 只同步哪些数据库，除此之外，其他不同步 binlog-do-db = game

总结

更多相关文章

随机推荐