MySQL 主从复制原理:


    从库生成两个线程,一个I/O线程,一个SQL线程;


    i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;


    主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;


    SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;


MySQL 主从复制主要用途:


1.读写分离

    在开发工作中,有时候会遇见某个sql 语句需要锁表,导致暂时不能使用读的服务,这样就会影响现有业务,使用主从复制,让主库负责写,从库负责读,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。


2.数据实时备份,当系统中某个节点发生故障时,可以方便的故障切换


一、如何配置MYSQL的主从复制?


1. 两台数据库服务器,IP分别为 192.168.216.128 和 192.168.216.129,在服务器上装MYSQL(我的配置版本为5.5.56)


2. 打开 192.168.216.128 服务器上的MYSQL的配置文件 /etc/my.cnf (路径根据自己服务器的情况来看),将其中的 server-id 设为1(默认为1,总之两台服务器要设置为不同的ID),然后重启MYSQL服务


3. 打开 192.168.216.129 服务器上的MYSQL的配置文件 /etc/my.cnf (路径根据自己服务器的情况来看),将其中的 server-id 设为2(默认为1),然后重启MYSQL服务


4. 设 192.168.216.128 为主服务器,那么在主服务器上加一个从服务器可以登录的用户,语句如下:


1
GRANT REPLICATION SLAVE ON *.* TO 'sally'@'192.168.216.129' IDENTIFIED BY 'ilovesally';
FLUSH PRIVILEGES

    

    建好后,在192.168.216.129 服务器上执行以下语句


1
mysql -h 192.168.216.128 -usally -pilovesally


    然后试一下可不可以连上,如果可以,则正确,如果连不上,看一下什么原因,是否是防火墙的原因,如果是则去配置防火墙的规则。


5. 以上完成后在主服务器上执行以下语句,查询master的状态


show master status;

    

    可以看到以上结果,这儿只需要看 File 和 Position,其它的两个分别是白名单和黑名单,意思为同步哪几个数据库和不同步哪几个数据库,可自行根据需求进行设置。记录了前两个字段后,在从库上执行以下语句:

CHANGE MASTER TO
MASTER_HOST='192.168.216.128',
MASTER_USER='sally',
MASTER_PASSWORD='ilovesally',
MASTER_LOG_FILE='mysql-bin.000020',
MASTER_LOG_POS=1441;

6. 执行完毕后,在从库上继续执行如下语句:

slave start;
show slave status\G;

    这样,查看从服务器的状态,如果状态中的用红线标出来两个参数的值都为YES,那证明配置已经成功,否则可以检查一下具体问题出现在什么地方。

 

    这样,就算配置完成了。在主库中新建数据库,新建一张表,插几条数据,到从库上查询一下看是否已经同步过来。

 

如果失败,可以从以下几个方面去排查问题:


1.首先试一下主从服务器相互之间是否 PING 得通

2.试一下远程连接是否正确,如果连不上,则有可能是网卡不一致防火墙没有放行 3306 端口

3.server-id 是否配成一致

4.bin-log 的信息是否正确

  

二、如何配置MYSQL的主主复制?


    上面说了主从复制的配置方法,现在接着上面的配置继续,然后实现双主复制,让以上的两个服务器互为主从。


1. 在主服务器上配置 /etc/my.cnf 文件,配置如下:

auto_increment_increment=2         #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1            #起始值。一般填第n台主MySQL。此时为第一台主MySQL
binlog-ignore=mysql                #忽略mysql库【我一般都不写】
binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】

    配置之后重启MYSQL服务


2.在从服务器上配置 /etc/my.cnf 文件,配置如下

auto_increment_increment=2   #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=2   #起始值。一般填第n台主MySQL。此时为第二台主MySQL
binlog-ignore=mysql   #忽略mysql库【我一般都不写】
binlog-ignore=information_schema   #忽略information_schema库【我一般都不写】

    配置之后重启MYSQL服务


3. 在从服务器上添加一个主服务器可以访问的用户,命令如下:

GRANT REPLICATION SLAVE ON *.* TO 'sally1'@'192.168.216.128' IDENTIFIED BY 'ilovesally';
FLUSH PRIVILEGES

    建好后,在192.168.216.128 服务器上执行以下语句


mysql -h 192.168.216.129 -usally1 -pilovesally

    如果可以连上,则进行下一步,连不上的话,参考上面进行问题排查。


4. 因为要互为主从,所以现在从服务器也是master ,所以也要查看一下状态

show master status;

    查到相应的信息后,在原来的主服务器上执行以下命令(因为现在它现在也是另一台的从服务器)


CHANGE MASTER TO
MASTER_HOST='192.168.216.129',
MASTER_USER='sally1',
MASTER_PASSWORD='ilovesally',
MASTER_LOG_FILE='mysql-bin.000021',
MASTER_LOG_POS=1457;

5. 执行完毕后,在原主库上继续执行如下语句:

start slave;
show slave status\G;

    同上,如果出现如下画面,则证明配置成功。


6. 在两台服务器的MYSQL中分别进行一些建库、建表、插入、更新等操作,看一下另一台会不会进行同步,如果可以则证明主主配置成功,否则还是上面的排错方法,进行错误排查。

 

三、如何配置MYSQL的双主多从?


    现在已经是双主配置了,但是如果要进行读写分离,那么我们要再增加N台从库,如何做呢?非常简单,按如下操作即可:


  1. 新增加一台数据库服务器,192.168.216.130,数据库配置均与前两台相同


2. 确定一下要将哪一台当作自己的主服务器,我们姑且设 192.168.216.128 为主服务器


3. 在第三台服务器中编辑 /etc/my.cnf ,将其 server-id 设为 3(保证与前两个不一样即可),然后重启MYSQL服务


4. 在主服务器中,增加一条用户记录,用于当前服务器对主库对的连接,代码如下:

GRANT REPLICATION SLAVE ON *.* TO 'farrow'@'192.168.216.130' IDENTIFIED BY 'ilovesally';
FLUSH PRIVILEGES;


5. 在 192.168.216.130 服务器上测试是否可以连接到主库

mysql -h 192.168.216.130 -ufarrow -pilovesally

    如果可以连上,则可以进行下一步,否则根据上面的提示排查问题。


6. 在 192.168.216.130 服务器上查询 master 当前状态

    看到相关信息后,我们执行如下操作:

CHANGE MASTER TO
MASTER_HOST='192.168.216.128',
MASTER_USER='sally',
MASTER_PASSWORD='ilovesally',
MASTER_LOG_FILE='mysql-bin.000020',
MASTER_LOG_POS=1441;


7. 执行完毕后,我们查询一下当前服务器的状态

start slave;
show slave status;

    如果状态如下,则说明配置正确


    如果此处有问题,参考上面所提排查并解决问题。


8. 此时我们在 192.168.216.128 上建库、建表、插入、更新、删除数据,在 另外两台上分别进行查看,发现均已经同步。但是如果我们在 192.168.216.129 上做相应的操作,则发现只有 192.168.216.128 上进行了相应的同步,而 192.168.216.130 上的数据并未同步。这是为什么呢?因为我们设置的主库是 192.168.216.128,所以在 192.168.216.129 进行数据操作的时候并未同步,这显然不符合我们的需求,那么,我们要怎么修改呢?非常简单,在互为主从的两台服务器的配置文件中均加入以下语句:

log-slave-updates=on

    加上后将两台服务器的MYSQL重启,然后再进行测试,发现数据已经可以同步了。如果要再多加一些从服务器,和以上类似,现在我们做的是双主一从,我们可以再加N台从服务器,配置也是一样的。

 

    至此,MYSQL主从复制、主主复制、双主多从配置我们均已经搞定。


    Mysql 主从复制是mysql 高可用,高性能的基础,有了这个基础,mysql 的部署会变得简单、灵活并且具有多样性,从而可以根据不同的业务场景做出灵活的调整。