Mysql主从复制、分库分表

一、个人理解什么是MYSQL复制？

• 复制是指将主数据库的 DDL和 DML 操作通过二进制日志传到复制服务器(也叫从库)上,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。MYSQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他服务器的主库,实现链状的复制 。

• 注意:

  由于MySQL实现的是并不是完全同步的复制,所以主从库之间存在一定的差距,在从库上进行的査询操作需要考虑到这些数据的差异, 一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从库查询, 实时性要求高的数据仍然需要从主数据库获得。

 二、原理概述

•  (1) 首先, MySQL主库在事务提交时会把数据变更作为事件 Events 记录在二进制日志文件Binlog中; MySQL主库上的 sync_binlog参数控制 Binlog日志刷新到磁盘。

•  (2) 主库推送二进制日志文件 Binlog中的事件到从库的中继日志 Relay Log, 之后从库根据中继日志 Relay Log重做数据变更操作,通过逻辑复制以此来达到主库和从库的数据一致。

•  (3 ) MySQL通过3个线程来完成主从库间的数据复制:其中 Binlog Dump线程跑在主库上, I/0线程和 SQL线程跑在从库上。当在从库上启动复制时,首先创建I/0程连接主库,主库随后创建 Binlog Dump线程读取数据库事件并发送给 I/0线程, I0线程获取到事件数据后更新到从库的中继日志 Relay Log中去,之后从库上的 SQL线程读取中继日志RelayLog中更新的数据库事件并应用

• (4) 通过 SHOW PROCESSLIST命令在主库上査看 BinlogDump线程,从 BinlogDump 线程的状态可以看到, Mysql的复制是主库主动推送日志到从库去的,是属于“推”日志的方式来做同步。同样地,在从库上通过 SHOW PROCESSLIST可以看到l/O线程和 SQL线程, l/O线程等待主库上的 Binlog Dump线程.发送事件并更新到中继日志 RelayLog, SQL线程读取中继日志并应用变更到数据库。

三、Mysql复制的日志文件类型 

1.    二进制日志文件 Binlog有三种格式：

            （1）Statement: 基于 SQL语句级别的 Binlog,每条修改数据的 SQL都会保存到 Binlog里。

            （2）Row:  基于行级别,记录每一行数据的变化,也就是将每行数据的变化都记录到 Binlog 里面, 记录得非常详细, 但是并                         不记录原始 SQL; 在复制的时候, 并不会因为存储过程或触发器造成主从库数据不一致的问通, 但是记录的日志量                            较Statement格式要大得多 。

            （3）Mixed: 混合Statement和Row模式,默认情况下采用 Statement模式记录,某些情况下会切换到 Row模式。

               注：在 binlog_format设置为 Row格式时, MySQL实际上在 Binlog中逐行记录数据的变更, Row格式比 Statement格式更                        能保证从库数据的一致性(复制的是记录,而不是单纯操作 SQL)。当然, Row格式下的 Binlog的日志量很可能会增大                        非常多,在设置时需要考虑到磁盘空间间题。参数 binlog_format可以在全局设置或者在当前 session动态设置: 在全                        局设置会影响所有session,而在当前 session设置则仅仅影响当前 Session。可以通过 SET命令来实时修改二进日                          志文件(Binlog)的格式。

                 相关命令：

                 查看当前复制方式：show variables like '%binlog%format%';

                 更改复制方式：set global binlog_format = 'ROW';     set global binlog_format = 'STATEMENT';

 四、复制搭建过程

搭建前提：需提前在服务器上安装mysql多实例，可参考（服务器(Centos)上安装运行多个Mysql实例）：https://blog.csdn.net/Akxandy/article/details/104584591            

 采用异步复制方式搭建： 主库执行完Commit后，在主库写入Binlog日志后即可成功返回客户端，无需等Binlog日志传送               给从库。

            步骤：

            1、确保主从库安装了相同版本的数据库。（本例子mysql3309为主库，mysql3310为从库）

            2、修改主数据库服务器的配置文件 my.cnf,开启 BINLOG,并设置 server-id的值。这两个参数的修改需要重新启动数据库                      服务才可以生效

注意：如果mysql目录下无log目录，请先创建log目录

            3、在主库上使用 mysql -uroot 进入数据库,设置一个复制使用的账户,并授予 REPLICATION SLAVE权限。这里创建一个复制用户test,可以从IP为192.169.56.104的主机进行连接:

命令：GRANT REPLICATION SLAVE ON *.* To 'test'@'192.168.42.104' IDENTIFIED BY '123456';

4、然后得到主库上当前的二进制日志名和偏移量值。这个操作的目的是为了在从数据库启动以后,从这个点开始进行数据的恢复。执行 show master status;

5、修改从数据库的配置文件 my.cnf中的 server-id参数。注意 server-id的值必须是唯一的,不能和主数据库的配置相同,如果有多个从数据库服务器,每个从数据库服务器必须有自己唯一的 server-id值。在 mycnf中修改如下:

6、在从库上,使用 - -skip-slave- start选项启动从数据库,这样不会立即启动从数据库服务上的复制进程,方便我们对从数据库的服务进行进一步的配置:

7、对从数据库服务器做相应设置,指定复制使用的用户,主数据库服务器的IP、端口以及开始执行复制的日志文件和位置等,参考代码如下:

CHANGE MASTER TO MASTER_HOST='192.168.42.104',MASTER_PORT=3309,MASTER_USER='test',MASTER_PASSWORD='123456' ,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=120;

8、在从库上,启动 slave线程:

9、这时slave上执行 show processlist命令将显示类似如下的进程:

执行show slave status;将显示：

10、在master上执行 show processlist;命令将显示类似如下的进程:

11、测试在主库上新建数据库和表，并插入数据，看看从库中是否会自动创建相关的数据库和表、插入数据。

12、主库中创建数据库cust和表cs_user，并插入数据

13、检查从库是否自动生成数据库cust，cs_user表及数据

 

 

本人亲测修改、删除主从库都能实时同步，这里就不在给大家一一截图了，望能帮到每一位小伙伴，记录学习路上的点点滴滴，奥力给，奥力给