Mysql之主从架构的复制原理及主从/双主配置详解(一)

最新推荐文章于 2025-03-25 17:35:58 发布

sz_bdqn

最新推荐文章于 2025-03-25 17:35:58 发布

阅读量3.1k

点赞数 1

分类专栏： MYSQL Linux

本文链接：https://blog.youkuaiyun.com/sz_bdqn/article/details/46251415

版权

Linux 同时被 2 个专栏收录

57 篇文章

订阅专栏

MYSQL

23 篇文章

订阅专栏

1. 复制概述

Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。

复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

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

1.1 mysql支持的复制类型：

(1) 基于语句的复制: 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高.一旦发现没法精确复制时,会自动选着基于行的复制。

(2) 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

(3) 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

1.2 复制解决的问题MySQL复制技术有以下一些特点：

(1) 数据分布 (Data distribution )

(2) 负载平衡(load balancing)

(3) 备份(Backups)

(4) 高可用性和容错行 High availability and failover

1.3 复制如何工作

整体上来说，复制有3个步骤：

(1)master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2)slave将master的binary log events拷贝到它的中继日志(relay log)；

(3)slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

下一步就是slave将master的binary log拷贝到它自己的中继日志。

首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制-->复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

2.复制配置有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。要点：

负责在主、从服务器传输各种修改动作的媒介是主服务器的二进制变更日志，这个日志记载着需要传输给从服务器的各种修改动作。因此，主服务器必须激活二进制日志功能。从服务器必须具备足以让它连接主服务器并请求主服务器把二进制变更日志传输给它的权限。

示例: 主从同步配置: 注意问题：

1 .版本

(1)双方的MySQL版本要一致;

(2)如果不一致：主的要低于从的;

2.从什么位置开始复制：

(1)都从0开始：两台新服务器,没有任何数据;

(2)主服务器已经运行一段时间，并且存在不小的数据集：把主服务器备份，然后在从服务恢复，从主服务器上备份时所处的位置开始复制;

环境：

Master/Slave	Platfrom	IP	APP Version
Mater	CentOS6.5_X86-64	192.168.1.52	mariadb-10.0.10
Slave	CentOS6.5_X86-64	192.168.1.52	mariadb-10.0.10

1.配置主从服务器：

#Master的配置：

(1)创建有复制权限的用户,命令如下：

mysql> CREATE USER 'pmel'@'localhost' IDENTIFIED BY '888888';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ,GRANT OPTION FROM 'pmel'@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT RELOAD,LOCK TABLES, REPLICATION CLIENT ,REPLICATION SLAVE ON *.* TO 'pmel'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>

(2)启用二进制日志主配置文件/etc/my.cnf [mysqld]段中,修改如下行：

<span style="font-size:12px;">log-bin = /mydata/binlogs/master-bin</span>

(3)修改server-id 主配：置文件/etc/my.cnf [mysqld]段中,修改如下行

server-id = 1

操作命令：

<span style="font-size:10px;font-weight: normal;">[root@localhost ~]# 
[root@localhost ~]# egrep "log-bin|server-id" /data/3306/my.cnf
#log-bin=mysql-bin
log-bin=/data/3306/mysql-bin
server-id = 1
[root@localhost ~]# 
[root@localhost ~]# 
[root@localhost ~]# ll /data/3306/
total 32
drwxr-xr-x 6 mysql mysql 4096 May 30 04:07 data
-rw-r----- 1 mysql root  7098 May 30 04:07 ilanni.err
-rw-rw---- 1 mysql mysql    5 May 30 04:07 ilanni.pid
-rw-r--r-- 1 mysql mysql  782 May 30 04:09 my.cnf
-rw-rw---- 1 mysql mysql  126 May 30 04:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql  107 May 30 04:07 mysql-bin.000002
-rw-rw---- 1 mysql mysql   56 May 30 04:07 mysql-bin.index
srwxrwxrwx 1 mysql mysql    0 May 30 04:07 mysql.sock
[root@localhost ~]# </span>

#Slave的配置：

(1)修改server-id主配置文件/etc/my.cnf [mysqld]段中,修改如下行：

server-id = 2

(2)启用中继日志,关闭二进制日志

#log-bin = /mydata/mysql-bin //加上注释
relay-log = /mydata/relaylogs/relay-bin

确保中继日志已开启

mysql> 
mysql> 
mysql> SHOW GLOBAL VARIABLES LIKE 'relay_log';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| relay_log     | /data/3307/relay-bin |
+---------------+----------------------+
1 row in set (0.01 sec)

(3)连接Master服务器

mysql> CHANGE MASTER TO MASTER_USER='pmel', MASTER_HOST='192.168.1.52',MASTER_PORT=3306, MASTER_PASSWORD='888888';
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> 
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: localhost
                  Master_User: pmel
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             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: 0
              Relay_Log_Space: 107
              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: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

在输出的结果当中:

Slave_IO_Running: No

Slave_SQL_Running: No

表明slave还没有开始复制过程。日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。实际上，MySQL知道的第一个事件的位置是4。

(4)在Slave上面启动复制线程:

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.52
                  Master_User: pmel
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 259
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 405
        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: 259
              Relay_Log_Space: 555
              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
1 row in set (0.01 sec)

mysql>

slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。

你可查看master和slave上线程的状态。在master上，你可以看到slave的I/O线程创建的连接：

在master上输入show processlist\G;

MariaDB [(none)]> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 8

User: root

Host: localhost

db: NULL

Command:

Query Time: 0

State:

init Info: SHOW PROCESSLIST

Progress: 0.000

*************************** 2. row ***************************

Id: 9

User: tom

Host: 172.16.41.2:56446

db: NULL

Command: Binlog

Dump Time: 97

State: Master has sent all binlog to slave;

waiting for binlog to be updated Info: NULL

Progress: 0.000

2 rows in set (0.04 sec)

行2为处理slave的I/O线程的连接。

在slave服务器上运行该语句：

MariaDB [(none)]> SHOW PROCESSLIST\G

*************************** 1. row ***************************

Id: 5

User: root

Host: localhost

db: NULL

Command:

Query Time: 0

State: init

Info: SHOW PROCESSLIST

Progress: 0.000

*************************** 2. row ***************************

Id: 6

User: system user

Host:

db: NULL

Command:

Connect Time: 186

State: Waiting for master to send event

Info: NULL

Progress: 0.000

*************************** 3. row ***************************

Id: 7

User: system user

Host:

db: NULL

Command:

Connect Time: 167883

State: Slave has read all relay log;

waiting for the slave I/O thread to update it

Info: NULL

Progress: 0.000

3 rows in set (0.00 sec)

MariaDB [(none)]>

行2为SQL线程状态,行3为I/O线程状态.

2.测试

(1)在Master上面创建一个数据库:


mysql> CREATE DATABASE testdb1;
Query OK, 1 row affected (0.00 sec)

(2)在Slave上面查看：


mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| testdb1            |
| testdb123          |
+--------------------+
6 rows in set (0.00 sec)

mysql>

OK!已经同步过来了,下面为第二种情况假如master已经运行很久了，想对新安装的slave进行数据同步，甚至它没有master的数据。如何指定一个位置开始同步复制：为了效果我将在Master中导入一些新数据,并且将Slave恢复到最初状态;

在Master上的操作：

(1)导入新数据：
[root@Master ~]# mysql < hellodb.sql

MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 10289 | | |
+-------------------+----------+--------------+------------------+
此时我对数据库插入一些数据并备份：
MariaDB [(none)]> CREATE DATABASE testdb;
[root@Master ~]# mysqldump --all-databases --flush-logs --master-data=1 --lock-all-tables > all.sql //将此文件传给新Slave.[root@Master ~]# scp all.sql root@172.16.41.2:/root/

(2)在从服务器上导入：
[root@Slave ~]# mysql < all.sql
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.41.1', MASTER_USER='tom', MASTER_PASSWORD='qazwsx123', MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=367;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
6 rows in set (0.03 sec)
OK! 简单的主从复制到此结束！

MySQL简单复制应用扩展：
1、主从服务器时间要同步（ntp）：
echo "*/5 * * * * /usr/sbin/ntpdate YOUR NTP_SERVER_IP" >> /var/cron/root

2、如何限制从服务器只读？
在从服务器上,/etc/my.cnf[mysqld]段中添加下行read-only=ON
注意：仅能限制那不具有SUPER权限用户无法执行写操作；
想限制所有用户：
mysql> FLUSH TABLES WITH READ LOCK;

3、如何主从复制时的事务安全？
在主服务器上配置：
sync_binlog=1

错误收集：
http://blog.sina.com.cn/s/blog_5037eacb0102vhwf.html
http://blog.itpub.net/7607759/viewspace-719707/
http://blog.youkuaiyun.com/i_bruce/article/details/17055135

参考文章：

http://maoqiu.blog.51cto.com/8570467/1398202