Mysql中主从复制的原理、及配置过程
Mysql中binlog日志---binlog 基本认识
MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。
二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
1)、开启binlog日志
## 服务器上，打开mysql配置文件 vim /etc/my.cnf ## 添加如下配置 log-bin=mysql-bin ## 重启mysql service mysqld restart
2)、查询binlog日志
## 连接服务器 mysql -h127.0.0.1 -uroot -proot ## 查询binlog配置 ## Variable_name为log_bin的值为ON，表示已经开启binlog日志 mysql>show variables like 'log_%'; ## 每次mysql重启，File、Position都会重置 mysql>show master status\G
3)、常用binlog日志操作命令
## 查看所有binlog日志列表 mysql>show master logs; ## 查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值 mysql>show master status; ## 刷新log日志，自此刻开始产生一个新编号的binlog日志文件 mysql>flush logs; ##注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加-F选项也会刷新binlog日志； ## 重置(清空)所有binlog日志 mysql>reset master;
1.什么是主从复制？
原理：主从分离,什么意思呢？我们不妨画个图看看。如图1所示：
1.1主从复制原理：
写进主服务器中的数据在从服务器中原模原样的也有。
简单来说就是保证主服务器（Master）和从服务器（Slave）的数据是一致性的，向Master插入数据后，Slave会自动从Master把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性。
1.2实现原理：
在主服务器中执行例如：insert、update等语句的时候把执行的这些语句的所有操作都给记录到一个叫binlog（二进制日志）中，记录下来之后呢，那么这个从服务器来读这个主服务器的binlog，在这里要特别注意一下，这个主服务器直接把binlog给从服务器是无法直接使用的，这个时候，从服务器把binlog读过来之后首先分析一下，形成能够为自己所用的relaylog然后再施加到自己身上，这样整个流程OK了！所以我们根据以上原理可以分析出以下几点：
1、主服务器要配置binlog
2、从服务器要配置replaylog
3、因为binlog是比较敏感的，那么从服务器如何拥有权限读取master呢？
答案：使用授权方式，主服务器要授予从服务器账号。
4、从服务器使用账号连接主服务器。
复制如何工作
复制有三个步骤：
1、Master将数据改变记录到二进制日志(binary log)中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志事件(binary log events)
2、Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)
3、Slave重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)
复制涉及到三个线程：
1、主节点 binary log dump 线程（IO线程）
当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。
2、从节点I/O线程
当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。
3、从节点SQL线程
SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。
1.3 MYSQL复制原理及其流程
1）在Slave 服务器上执行start slave命令开启主从复制开关，开始进行主从复制。
2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容
3）Master服务器接收到来自Slave服务器的IO线程的请求后，二进制转储IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点
1.4 Mysql 复制能解决什么问题
一、高可用和故障切换
复制能够帮避免MySql单点失败，因为数据都是相同的，所以当Master挂掉后，可以指定一台Slave充当Master继续保证服务运行，因为数据是一致性的（如果当插入Master就挂掉，可能不一致，因为同步也需要时间），当然这种配置不是简单的把一台Slave充当Master，毕竟还要考虑后续的Salve同步Master
二、负载均衡
因为读写分离也算是负载均衡的一种，所以就不单独写了，因为一般都是有多台Slave的，所以可以将读操作指定到Slave服务器上（需要代码控制），然后再用负载均衡来选择那台Slave来提供服务，同时也可以吧一些大量计算的查询指定到某台Slave，这样就不会影响Master的写入以及其他查询
在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
Master负责写操作的负载，也就是说一切写的操作都在Master上进行，而读的操作则分摊到Slave上进行。这样一来的可以大大提高读取的效率。在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就是说大量的数据操作是集中在读的操作，这也就是为什么我们会有多个Slave的原因。但是为什么要分离读和写呢？熟悉DB的研发人员都知道，写操作涉及到锁的问题，（快照读VS当前读，写操作一定是当前读，所以会产生锁。）不管是行锁还是表锁还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操作其其他的N个节点上进行，从另一个方面有效的提高了读的效率，保证了系统的高可用性。
三、数据备份
一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全
四、业务模块化
可以一个业务模块读取一个Slave，再针对不同的业务场景进行数据库的索引创建和根据业务选择MySQL存储引擎， 不同的slave可以根据不同需求设置不同索引和存储引擎
随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。
2.准备工作：
预备两台服务器，我这里使用虚拟机安装了两个Centos7_64位操作系统，并分别在两台服务器上安装mysql。我的IP地址分别为：192.168.216.157/192.168.216.158，这里我定义157为主服务器，158为从服务器。
首先，我们编辑主服务器中mysql配置文件。
在编辑mysql配置文件之前你要考虑，在一个机房里，一主多从，或者是主主复制或者是被动模式的主主复制等多种情况，那么这多台服务器之间相互他们日志复制来binlog，复制来复制去会不会乱了呢？真有可能会乱，那么碰到这样情况你怎么办？因此，我们要给每一台服务器起一个独特的server-id。那么这个id呢，正常情况下做主从服务器都会在同一个局域网内，因同意个局域网内IP前三段都一样，只有最后一段不一样，所以我们一般都是拿ip的末尾一段作为server-id,当然这不是硬性的规定，只是习惯而已。因此我们开始配置my.cnf文件。
#编辑主服务器 (192.168.216.157)的mysql配置文件 [root@localhost ~]# vim /etc/my.cnf #给服务器骑一个独特的id server-id=157 #声明二进制日志的文件为mysql-bin log-bin=mysql-bin max_binlog_size = 100M expire_logs_days = 7 #binary logging format - mixed recommended #二进制日志的格式 binlog_format=mixed
对于配置中主binlog-format=row/statement的解释：
statement:记录执行语句，例如update...
row:记录的是磁盘变化
到底哪个好？
update age=age+1 where id=3;//语句长而磁盘变化少，宜用row
update set money=money+1000;//语句短而变化多，宜用statement
如果以上两个你拿不准，请使用mixed，由系统根据语句来决定。
进入/data/mysql目录并查看以前mysql日志信息，删除以前的log主服务器日志信息
[root@localhost ~]# cd /data/mysql/ [root@localhost mysql]# rm -rf ./mysql-bin.* 如下图所示：这里我们不删除，先保存副本迁移到其他目录，以防修改错误
至此，我们的主服务器就配置好了，接下来我们来配置一下从服务器...
首先我们需要配置/data/mysql目录下my.cnf文件。
#编辑从服务器 (192.168.216.158)的mysql配置文件 [root@localhost ~]# vim /etc/my.cnf #给服务器骑一个独特的id server-id=158 #声明二进制日志的文件为mysql-bin log-bin=mysql-bin max_binlog_size = 100M expire_logs_days = 7 #binary logging format - mixed recommended #二进制日志的格式 binlog_format=mixed relay-log=mysql-relay
同样，进入/data/mysql目录并查看以前mysql日志信息，删除以前的log主服务器日志信息
[root@localhost mysql]# cd /data/mysql/ [root@localhost mysql]# rm -rf ./mysql-bin.* 如下图所示：这里我们不删除，先保存副本迁移到其他目录，以防修改错误
更改了mysql配置文件，所以需要 分别重新启动 主从服务器的mysql服务
[root@localhost mysql]# systemctl restart mysqld.service #主服务器 [root@localhost mysql]# systemctl restart mysqld.service #从服务器
以上主从服务器相关配置已经完成，那么我们开始正式测试这个功能！
首先登陆主服务器，登陆mysql ( 因为我的mysql已经写到了环境变量里面直接登录就好 ) [root@localhost mysql]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.2.11-MariaDB-log Source distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> show variables like 'server_id'; #配置文件修改server-id成功 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 157 | +---------------+-------+ 1 row in set (0.00 sec)
用同样的方式登陆从服务器
[root@localhost mysql]# mysql -uroot -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 10 Server version: 10.2.11-MariaDB-log Source distribution Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> show variables like 'server_id'; #配置文件修改server-id成功 +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 158 | +---------------+-------+ 1 row in set (0.00 sec)
在主服务器上为从服务器账号授予读取权限
授权test用户拥有testDB数据库的所有权限（某个数据库的所有权限）：
MariaDB [(none)]> grant replication client,replication slave on *.* to ck@'192.168.216.158' identified by '123456'; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.02 sec)
在从服务器通过语句指定要复制的主服务器（注意，可以一主多从，不可一丛多主）,在使用下面语句之前，我们需要只要当前主服务器日志文件走到哪了，使用show master status查看.
MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 342 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
在从服务器执行下面语句，开启复制
MariaDB [(none)]> change master to -> master_host='192.168.216.157', -> master_user='ck', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=342; Query OK, 0 rows affected (0.03 sec)
在从服务器上查看两台服务器状态有么有打通
MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.216.157 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 342 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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: 342 Relay_Log_Space: 256 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error:
启动从服务器功能，再次查看状态。
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.216.157 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 342 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 342 Relay_Log_Space: 860 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:
# Slave_IO_Running: Yes，Slave_SQL_Running: Yes 说明同步正常进行
# Seconds_Behind_Master: 0 就是完全同步了
3.实战测试
分别查看主从服务器都有哪些数据库
主服务器： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec) 从服务器： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | +--------------------+ 3 rows in set (0.00 sec)
由此，我们先在主服务器上创建数据库test,并再次查看主服务器有哪些数据库。
主服务器： MariaDB [(none)]> create database test; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | test | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) 从服务器： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | test | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec) MariaDB [(none)]>
看见没有？从服务器是不是多了一个demo数据库？
不信咱再测试一下...
我们使用主服务器的test，然后建立一张student表，并插入两条数据，看看从服务器是不是自动的为我们新建sutent表，表中并包含我们刚从主服务器中插入的两条数据。我们测试一下：
主服务器： MariaDB [(none)]> use test; Database changed MariaDB [test]> create table stutent -> ( -> id int primary key AUTO_INCREMENT, -> name varchar(20), -> age varchar(20) -> ); Query OK, 0 rows affected (0.04 sec) MariaDB [test]> insert into stutent values(1,'徐守威1','24'); Query OK, 1 row affected (0.01 sec) MariaDB [test]> insert into stutent values(2,'徐守威2','23'); Query OK, 1 row affected (0.01 sec) 从服务器： MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.02 sec) MariaDB [(none)]> use test; Database changed MariaDB [test]> show tables; +----------------+ | Tables_in_test | +----------------+ | stutent | +----------------+ 1 row in set (0.00 sec) MariaDB [test]> select * from stutent; +----+------------+------+ | id | name | age | +----+------------+------+ | 1 | 徐守威1 | 24 | | 2 | 徐守威2 | 23 | +----+------------+------+ 2 rows in set (0.01 sec) MariaDB [test]>
处理完后，我们紧接着查看一下从服务器是不是多了一张表，并且里面有两条数据。
好了，mysql主从复制的问题到此就结束了！
我们还可以从线程列表看出复制线程，主库上可以看到由从库I/O线程向主库发起的连接。
mysql> show processlist \G*************************** 1. row *************************** Id: 44 User: repl Host: localhost:32866 db: NULLCommand: Binlog Dump Time: 73032 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL
同样，我们看看从库的线程，有两个，一个I/O线程，一个SQL线程：
mysql> show processlist \G*************************** 1. row *************************** Id: 4 User: system user Host: db: NULLCommand: Connect Time: 73422 State: Waiting for master to send event Info: NULL*************************** 2. row *************************** Id: 5 User: system user Host: db: NULLCommand: Connect Time: 72417 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL
这两个线程都是再system user 帐号下运行，I/O线程是写日志到中继日志的线程， SQL线程是重放SQL的线程。
参考：
本文为崔凯原创文章,转载无需和我联系,但请注明来自冷暖自知一抹茶ckhttp://www.cksite.cn
- 最新评论
- 总共0条评论
