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

2023-09-14 17:41:09 77

准备工作

1.主从数据库版本最好一致

2.主从数据库内数据保持一致

主数据库：121.199.27.227/ubuntu16.04MySQL5.7.21(阿里云）

从数据库：182.254.149.39/ubuntu16.04MySQL5.7.21(腾讯云）

防火墙配置

配置主服务器只允许特定IP访问数据库的端口，避免不必要的攻击。

主库防火墙配置

#iptables-AINPUT-ptcp-sslave_ip--dport3306-jACCEPT
#删除可能已经存在的配置，避免出现多条重复记录
$sudoiptables-DINPUT-ptcp-s182.254.149.39--dport3306-jACCEPT
$sudoiptables-DINPUT-ptcp-s127.0.0.1--dport3306-jACCEPT
$sudoiptables-DINPUT-ptcp--dport3306-jDROP
$sudoiptables-DINPUT-pudp--dport3306-jDROP
$sudoiptables-DINPUT-psctp--dport3306-jDROP
#增加配置，只允许特定地址访问数据库端口
$sudoiptables-AINPUT-ptcp-s182.254.149.39--dport3306-jACCEPT
$sudoiptables-AINPUT-ptcp-s127.0.0.1--dport3306-jACCEPT
$sudoiptables-AINPUT-ptcp--dport3306-jDROP
$sudoiptables-AINPUT-pudp--dport3306-jDROP
$sudoiptables-AINPUT-psctp--dport3306-jDROP
$sudoiptables-L-n
#保存配置
$sudoapt-getinstalliptables-persistent
$sudonetfilter-persistentsave
#配置被保存到/etc/iptables/rules.v4/etc/iptables/rules.v6这两个文件下面，
#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除
从库防火墙配置
#iptables-AOUTPUT-ptcp-dmaster_ip--dport3306-jACCEPT
#删除可能已经存在的配置，避免出现多条重复记录
$sudoiptables-DOUTPUT-ptcp-d121.199.27.227--dport3306-jACCEPT
#增加配置
$sudoiptables-AOUTPUT-ptcp-d121.199.27.227--dport3306-jACCEPT
$sudoiptables-L-n
#保存配置
$sudoapt-getinstalliptables-persistent
$sudonetfilter-persistentsave
#配置被保存到/etc/iptables/rules.v4/etc/iptables/rules.v6这两个文件下面，
#最好确认一下实际保存的内容，尤其是安装了denyhosts等其他安全软件的情况下，
#可能会记录了多余的规则，需要手工删除

主数据库master配置

1.修改mysql配置

$sudovim/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

$sudoservicemysqlrestart
$mysql-uroot-p-e"CREATEUSER'repl'@'182.254.149.39'IDENTIFIEDBY'slavepass';"#创建用户
$mysql-uroot-p-e"GRANTREPLICATIONSLAVEON*.*TO'repl'@'182.254.149.39';"#分配权限
$mysql-uroot-p-e"flushprivileges;"#刷新权限

3.查看master状态，记录二进制文件名(mysql-bin.000001)和位置(333802)：

$mysql-uroot-p-e"SHOWMASTERSTATUS;"
Enterpassword:
+------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------+
|mysql-bin.000001|333802||||
+------------------+----------+--------------+------------------+-------------------+

4.主库备份，为从库的第一次数据同步准备数据

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

#此处以备份wordpress数据库为例子
datadump=`whichmysqldump`
mysqluser="root"
userpass="password"
wordpressdb="wordpress"
backupwordpress_sql=$wordpressdb.`date+%Y%m%d`.sql
if$datadump-u$mysqluser--password=$userpass-hlocalhost--opt$wordpressdb>$backupwordpress_sql2>&1
then
echo"backup$wordpressdbsuccess"
else
echo"backup$wordpressdberror"
exit1
fi
#检验文件尾部是否存在“--Dumpcompletedon”，如果存在不存在，则说明备份出错了。
if[0-eq"$(sed'/^$/!h;$!d;g'$backupwordpress_sql|grep-c"Dumpcompletedon")"];
then
echo"backup$wordpressdberror"
exit1
else
echo"backup$wordpressdbsuccess"
fi

执行脚本,确保最后输出备份成功

$cd~
$sudobashbackup_wordpress.sh

从服务器slave配置

1.修改mysql配置

$sudovim/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.首次还原数据库：

$sudoservicemysqlrestart
$scp-P22-rroot@121.199.27.227:~/wordpress.*.sql./
#删除可能存在的一行警告信息，这行警告信息可能导致我们无法恢复数据
$sed-i"/^mysqldump:\[Warning\]Usingapasswordonthecommandlineinterfacecanbeinsecure\./d"wordpress.*.sql
$mysql-uroot-p-e"dropdatabasewordpress;"
$mysql-uroot-p-e"createdatabasewordpress;"
$mysql-uroot-pwordpress
3.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：
$mysql-uroot-p-e"CHANGEMASTERTOMASTER_HOST='121.199.27.227',MASTER_USER='repl',MASTER_PASSWORD='slavepass',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=333802;"
4.启动slave同步进程：
$mysql-uroot-p-e"startslave;"
5.查看slave状态：
$mysql-uroot-p-e"showslavestatus\G;"
Enterpassword:
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
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>stopslave;）,然后再修改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库。
总结
以上所述是小编给大家介绍的ubuntu16.04配置MySQL主从同步的配置方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

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

热门推荐

随机推荐