Linux开发环境搭建(二)--MySQL配置主从复制

心已赠人 2022-06-01 08:35 160阅读 0赞

# Linux下MySQL主从同步配置 #

主服务器：centos7 IP:192.168.1.101[CentOS][] 6.5 MySQL主从同步，MySQL版本5.6.25

从服务器：centos6.5 IP:192.168.1.102

一、主服务器相关配置

1、创建同步账户并指定服务器地址

\[root[@localhost][localhost] ~\]mysql -uroot -p  
mysql>use mysql  
mysql>grant replication slave on \*.\* to 'testuser'@'192.168.1.102' identified by '12345678';  
mysql>flush privileges \#刷新权限

授权用户testuser只能从192.168.1.102这个地址访问主服务器192.168.1.101的数据库，并且只具有数据库备份的权限

2、修改/etc/my.cnf配置文件vi /etc/my.cnf

\[mysqld\]下添加以下参数，若文件中已经存在，则不用添加  
server-id=1    
log-bin=mysql-bin  \#启动MySQL二进制日志系统，  
binlog-do-db=ourneeddb  \#需要同步的数据库  
binlog-ignore-db=mysql  \#不同步mysql系统数据库，若还有其它不想同步的，继续添加  
\[root[@localhost][localhost] ~\]/etc/init.d/mysqld restart \#重启服务

3、查看主服务器master状态(注意File与Position项，从服务器需要这两项参数)

4、导出数据库

导出数据库之前先锁定数据库

flush tables with read lock;    \#数据库只读锁定命令，防止导出数据库的时候有数据写入

unlock tables; \#解除锁定

导出数据库结构及数据：mysqldump -uroot -p ourneeddb > /home/ourneeddb.sql

导出存储过程及函数　：mysqldump -uroot -p -ntd -R ourneeddb > ourneeddb\_func.sql

tips:-ntd导出存储过程、-R导出函数

二、从服务器相关配置

1、修改/etc/my.cnf配置文件vi /etc/my.cnf

\[mysqld\]下添加以下参数，若文件中已经存在，则不用添加  
server-id=2  \#设置从服务器id，必须于主服务器不同  
log-bin=mysql-bin  \#启动MySQ二进制日志系统  
replicate-do-db=ourneeddb  \#需要同步的数据库名  
replicate-ignore-db=mysql  \#不同步mysql系统数据库  
\[root@localhost~ \]/etc/init.d/mysqld restart \#重启服务

2、导入数据库

导入过程这里不做阐述

3、配置主从同步

\[root@localhost~ \]mysql -uroot -p  
mysql>use mysql   
mysql>stop slave;  
mysql>change master to  
      master\_host='192.168.1.101',  
      master\_user='testuser',  
      master\_password='12345678',  
      master\_log\_file='mysql-bin.000012',  
      master\_log\_pos=120;  \#log\_file与log\_pos是主服务器master状态下的File与Position  
mysql>start slave;  
mysql>show slave status\\G;

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
Slave\_IO\_State: Waiting for master to send event  
Master\_Host: 192.168.1.101  
Master\_User: testuser  
Master\_Port: 3306  
Connect\_Retry: 60  
Master\_Log\_File: mysql-bin.000012  
Read\_Master\_Log\_Pos: 120  
Relay\_Log\_File: orange-2-relay-bin.000003  
Relay\_Log\_Pos: 283  
Relay\_Master\_Log\_File: mysql-bin.000012  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
Replicate\_Do\_DB: orange  
Replicate\_Ignore\_DB: mysql,test,information\_schema,performance\_schema  
Replicate\_Do\_Table:  
Replicate\_Ignore\_Table:  
Replicate\_Wild\_Do\_Table:  
Replicate\_Wild\_Ignore\_Table:  
Last\_Errno: 0  
Last\_Error:  
Skip\_Counter: 0  
Exec\_Master\_Log\_Pos: 120  
Relay\_Log\_Space: 1320  
Until\_Condition: None  
Until\_Log\_File:  
Until\_Log\_Pos: 0  
Master\_SSL\_Allowed: No  
Master\_SSL\_CA\_File:  
Master\_SSL\_CA\_Path:  
Master\_SSL\_Cert:  
Master\_SSL\_Cipher:  
Master\_SSL\_Key:  
Seconds\_Behind\_Master: 0  
Master\_SSL\_Verify\_Server\_Cert: No  
Last\_IO\_Errno: 0  
Last\_IO\_Error:  
Last\_SQL\_Errno: 0  
Last\_SQL\_Error:  
Replicate\_Ignore\_Server\_Ids:  
Master\_Server\_Id: 1  
Master\_UUID: 773d2987-6821-11e6-b9e0-00163f0004f9  
Master\_Info\_File: /home/mysql/master.info  
SQL\_Delay: 0  
SQL\_Remaining\_Delay: NULL  
Slave\_SQL\_Running\_State: Slave has read all relay log; waiting for the slave I/O thread to update it  
Master\_Retry\_Count: 86400  
Master\_Bind:  
Last\_IO\_Error\_Timestamp:  
Last\_SQL\_Error\_Timestamp:  
Master\_SSL\_Crl:  
Master\_SSL\_Crlpath:  
Retrieved\_Gtid\_Set:  
Executed\_Gtid\_Set:  
Auto\_Position: 0

注意查看Slave\_IO\_Running: Yes  Slave\_SQL\_Running: Yes 这两项必须为Yes 以及Log\_File、Log\_Pos要于master状态下的File,Position相同

如果都是正确的，则说明配置成功！

[CentOS]: http://www.linuxidc.com/topicnews.aspx?tid=14
[localhost]: https://my.oschina.net/u/570656