MySQL主从复制

一、前言

  现如今，数据库发展越来越快，虽然主从架构已经落伍了，取而代之的是更加复杂的数据库集群。但是作为一个小型公司，数据库的主从架构是最基本的架构，所以了解完主从架构，也就能看懂更加复杂的架构。下面介绍一下该架构。

二、数据库的读写分离

  为什么要读写分离？

对于一个小型网站，可能单台数据库服务器就能满足需求，但是在一些大型的网站或者应用中，单台的数据库服务器可能难以支撑大的访问压力，升级服务器性能，成本又太高，必须要横向扩展。还有就是，单库的话，读、写都是操作一个数据库，数据多了之后，对数据库的读、写性能就会有很大影响。同时对于数据安全性，和系统的稳定性，也是挑战。

  数据库的读写分离的好处？

1. 将读操作和写操作分离到不同的数据库上，避免主服务器出现性能瓶颈；

2. 主服务器进行写操作时，不影响查询应用服务器的查询性能，降低阻塞，提高并发；

3. 数据拥有多个容灾副本，提高数据安全性，同时当主服务器故障时，可立即切换到其他服务器，提高系统可用性；

  读写分离的基本原理？

就是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE）操作，而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到其他从数据库。以SQL为例，主库负责写数据、读数据。读库仅负责读数据。每次有写库操作，同步更新到读库。写库就一个，读库可以有多个，采用日志同步的方式实现主库和多个读库的数据同步。
三、MySQL复制描述

  主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

请注意：当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

  3.1、mysql支持的复制类型：

　　（1）：基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。  
       一旦发现没法精确复制时，会自动选着基于行的复制。    
　　（2）：基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
　　（3）：混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

 3.2、复制解决的问题：

   MySQL复制技术有以下一些特点：
    (1)数据分布 (Data distribution )
    (2)负载平衡(load balancing)
    (3)备份(Backups) 
    (4)高可用性和容错行 High availability and failover

3.3、mysql数据库存储数据的过程：

主mysql：

1、客户发起事务动作，mysql服务将事务记录到事务日志（Binary log）中，将事务执行的一些操作记录在内存中，如果事务没有完成，内存断电，可以从事务日志中将没有完成的事务进行回滚。

2、将内存中完成的操作记录到二进制日志中

3、将二进制日志中的完整事务操作，定期写到磁盘文件中

（mysql数据库中有个dump的进程，会将二进制日志改变的部分提取出来，交给从服务器的I/O进程进行读取）

从mysql

4、如果从mysql想要同步数据库的话，可以监听主mysql上的二进制日志，将主mysql二进制日志中发生变化的部分提出到从mysql上，放入从mysql的relay log日志（中继日志）中

5、再将中继日志中的操作读入内存，由内存将操作记录到二进制日志中，再定期写到磁盘文件中。

（mysql从数据库中有I/O线程和SQL线程，I/O线程会监控主mysql服务器上的二进制文件，如果二进制文件发生改变后就去读取发生改变的部分，加载到自己的relay log中继日志中，并由SQL线程读取中继日志中的数据，加载到内存中，再写入二进制文件中。）

四、MySQL主从复制过程

MySQL主从复制的两种情况：同步复制和异步复制，实际复制架构中大部分为异步复制。复制的基本过程如下：

1、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。

2、Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。

3、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。

4、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

五、MySQL数据库复制配置

最简单的复制模式就是一主一从的复制模式了，下面开始操作：

首先使用yum方式安装Mysql服务，要求版本一致，并启动服务

yum install mariadb

yum install  mariadb-server

注意：保证服务器在同一网段

在Master服务器上：

1、修改主MySQL的主配置文件

vim /etc/my.cnf

server_id=1    #配置server-id，让主服务器有唯一ID号

log-bin=mysql-bin  #打开Mysql日志，日志格式为二进制

skip-name-resolve  #关闭名称解析，（非必须）

systemctl start mariadb     #启动服务

cd /var/lib/mysql-->ll-->mysqlbinlog mysql-bin.000001 查看二进制日志，生成mysql-bin.000001和mysql-bin.index文件

2、在主MySQL上给从MySQL添加登录权限

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'192.168.%.%' IDENTIFIED BY 'magedu';

3、查看Master服务器状态

在Master的数据库执行mysql>show master status;查看主服务器二进制日志状态

--------------+

| File                         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------------+----------+--------------+------------------+-------------------

+mysql-bin.000004 |      416 |              |                  |                   |

+------------------------+----------+--------------+------------------+-----

mysql> show binlog events G;  查看二进制日志状态

在从服务器上：

4、配置slave从服务器

对slave进行配置，打开中继日志，指定唯一的servr ID，设置只读权限。在配置文件加入如下值：

vim /etc/my.cnf

server-id=2   #配置server-id，让从服务器有唯一ID号

relay_log = mysql-relay-bin   #打开Mysql日志，日志格式为二进制

read_only = 1   #设置只读权限

log_bin = mysql-bin   #开启从服务器二进制日志

log_slave_updates = 1 #使得更新的数据写进二进制日志中

systemctl start mariadb     #启动服务

5．启动从服务器复制线程

在master和slave都已经配置好后，只需要把slave指向master即可，并开始重做master二进制日志中的事件。

mysql -uroot -p

CHANGE MASTER TO MASTER_HOST='192.168.159.151', 

MASTER_USER='slave',

MASTER_PASSWORD='magedu', 

MASTER_LOG_FILE='mysql-bin.000004',

MASTER_LOG_POS=416;

注意：上面的sql语句是从头开始复制第一个binlog，如果想从某个位置开始复制binlog，就需要在change master to时指定要开始的binlog文件名和语句在文件中的起点位置，参数为：master_log_file和master_log_pos。

执行start slave； # 启动复制线程

6、查看从服务器状态

可使用SHOW SLAVE STATUSG查看从服务器状态，如下所示，也可用show processlist G查看当前复制状态：

Slave_IO_Running: Yes #IO线程正常运行

Slave_SQL_Running: Yes #SQL线程正常运行

7、测试

在主服务器上：show databases;-->create database magedu;

在从服务器上查看：show databases;

上面就是最简单的主从复制模式，不过有时候随着时间的推进，binlog会变得非常庞大，如果新增加一台slave，从头开始复制master的binlog文件是非常耗时的，所以我们可以从一个指定的位置开始复制binlog日志，可以通过其他方法把以前的binlog文件进行快速复制，例如copy物理文件。在change master to中有两个参数可以实现该功能，master_log_file和master_log_pos，通过这两个参数指定binlog文件及其位置。我们可以从master上复制也可以从slave上复制，假如我们是从master上复制，具体操作过程如下：

（1）为了防止在操作过程中数据更新，导致数据不一致，所以需要先刷新数据并锁定数据库：flush tables with read lock。

（2）检查当前的binlog文件及其位置：show master status。

| File                                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------------+----------+--------------+------------------+-------------------+

| mysqlmaster-bin.000001 |      630   |              |                  |                   |

+------------------------+----------+--------------+------------------+-----

（3）主服务器上另开一连接（另开一窗口，不退出第一个窗口，退出后上面的锁表将失效）

通过mysqldump命令创建数据库的逻辑备份：

mysqldump -uroot -p -P3306 --all-databases  --triggers --routines --events >mysqlall.sql。

（4）有了master的逻辑备份后，对数据库进行解锁：unlock tables。

（5）把mysqlall.sql复制到新的slave上，执行：

scp mysqlall.sql  192.168.X.X:/tmp/

mysql -uroot -p -h 127.0.0.1 -P3306 < /tmp/mysqlall.sql  把master的逻辑备份插入slave的数据库中。

（6）现在可以把新的slave连接到master上了，只需要在change master to中多设置两个参数master_log_file='mysql-bin.000003'和master_log_pos='107'即可，然后启动slave：start slave，这样slave就可以接着107的位置进行复制了。

登陆从数据库执行:

CHANGE MASTER TO MASTER_HOST='192.168.159.120',

MASTER_USER='systop2',

MASTER_PASSWORD='systop',

MASTER_LOG_FILE='mysqlmaster-bin.000003',

MASTER_LOG_POS=107;

（7）start slave；

show slave status G;

查看Slave_IO_Running: Yes

   Slave_SQL_Running: Yes都显示yes表示启动正常。

若报Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' 错误，Slave_IO_Running为no

执行flush logs;（清空缓冲区数据，此时缓冲区数据都会被写到数据库里）

mysql> show master status;

重新执行第6步。

有时候master并不能让你锁住表进行复制，因为可能跑一些不间断的服务，如果这时master已经有了一个slave，我们则可以通过这个slave进行再次扩展一个新的slave。原理同在master上进行复制差不多，关键在于找到binlog的位置，你在复制的同时可能该slave也在和master进行同步，操作如下：

（1）为了防止数据变动，还是需要停止slave的同步：stop slave。

（2）然后刷新表，并用mysqldump逻辑备份数据库。

（3）使用show slave status查看slave的相关信息，记录下两个字段的值Relay_Master_Log_File和Exec_Master_Log_Pos，这个用来确定从后面哪里开始复制。

（4）对slave解锁，把备份的逻辑数据库导入新的slave的数据库中，然后设置change master to，这一步和复制master一样。

实验中出现的问题：

1、在同步后出现：

Slave_IO_Running: Yes

Slave_SQL_Running: NO

原因：1.程序可能在slave上进行了写操作，所以从服务器上要在配置文件中写入：read_only=1

  2.也可能是slave机器重起后，事务回滚造成的.

解决办法：

方法1、查看master服务器中的二进制日志记录show master status;

   重新在从服务器上进行配置，change master to .....

方法2、先在从服务器上停掉slave：slave stop |stop slave

   set global sql_slave_skip_counter=1;

   #以上是用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。 

   重启slave：slave start |start slave

2、在启动数据库后查看slave status

show slave statusG;

出现：

Slave_IO_Running: No

    Slave_SQL_Running: Yes

    这是因为slave 开启了，但是没有指向master，或没有指向成功,或防火墙没关。

    需要重新查看master的数据，确保change master to master....指向成功。

转载：http://blog.51cto.com/youguofusi/2051302