首页 > 代码库 > MySQL学习（二）复制

MySQL学习（二）复制

2024-07-13 04:21:15   225人阅读

    复制解决的问题是保持多个服务器之间的数据的一致性，就如同通过复制保持两个文件的一致性一样，只不过MySQL的复制要相对要复杂一些，其基本过程如下：
    1）在主库上将数据更改记录到二进制日志（Binary Log）中（这些记录被成为二进制日志事件，即binlog）
    2）本分将主库上的日志复制到自己的中继日志（Relay Log）中
    3）备库读取中继日志中的事件，将其重放到备库数据之上。
    从上面可以看出，复制需要四个进程或线程做事情：主库保存日志、主库根据备库的请求转储日志并发送给备库，备库接受日志保存在中继日志中，备库从中继日志中读取信息重放到备库中。当然复制本身可以说只需要3个线程，不算主库保存日志。
   从逻辑上来看，复制对于主库的开销主要在I/O上，记录日志、转储日志和网络传输，如果同时支持多个备库，这些消耗是叠加的，对主库的压力比较大。
   MySQL支持语句复制和行复制，语句复制在MySQL 3.23版就已经存在，行复制到5.1版才加进来。行复制是其他数据库较为常用的方式，语句复制相对更加简单。在MariaDB 10.0.12中用的是混合模式，即默认使用语句模式，特殊情况下采用行复制。复制模式的启用可以在my.ini或my.cnf中定义：binlog_format=statement/row/mixed，分别代表语句、行、混合模式，可以参考http://www.linuxidc.com/Linux/2013-06/86632.htm，写的非常详细。
    MySQL复制的特点：
   1）配置较为简单
   2）很方便进行主从切换，即更换主或者增加从都非常简单
   3）基于语句的复制方式，导致在从库完全重放主库的SQL，如果从库配置较差，可能会出现很大的延迟，甚至导致从库无法完成其他工作。
   4）主库执行SQL是并发执行的，从库重放是串行的，会带来更严重的延迟。
   5）一般来说，从库版本要高于主库版本，因为复制是向下兼容的，即高版本的MySQL实例可以读取低版本的二进制日志。
   配置复制实例，为方便起见，采用相同的数据库版本，如下
   1）本机启动两个MySQL实例
   环境为：Windows 7 x64，IP地址为192.168.1.11，两个MariaDB 10.0.12实例
   将MySQL实例1启动端口设为3308，实例2启动端口设为3307，实例1作为主服务器，实例2作为从服务器，端口设置在my.ini中，本人将my-medium.ini复制改为my.ini，作为配置文件，修改其端口，启动MySQL命令为mysqld，连接命令mysql -uroot -P 3308和mysql -uroot -P 3307，因为root帐号默认密码为空，所以没有-p参数。
     a)在主实例上配置
     MariaDB [(none)]>grant replication slave on *.* to rep1@‘192.168.1.%‘ identified by ‘123456‘;
     修改my.ini增加或修改以下内容
     log_bin=mysql-bin
     server_id=10
     这里的server_id要保证在所有MySQL实例中唯一
    MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 500 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
    可以看出复制文件为mysql-bin.000003
    b)在从服务器上做设置
    修改my.ini文件，增加或修改内容
log_bin=mysql-bin
server-id = 11
relay_log = D:\mysql\mariadb-10.0.12-winx64-1\data\mysql-relay-bin
log_slave_updates = 1
read_only = 1
   这里要注意，log_bin在主从命名一致，这不是必须的，但是这样做有利于未来切换方便，server_id也要保证唯一，relay_log是中继日志的位置，log_slave_updates=1表明中继日志的修改也要记录从库的更新日志中，这有利于保证数据的一致性，还有利于从从服务器复制数据，read_only=1会阻止没有权限的账户更新数据。
    配置复制，
   MariaDB [(none)]> change master to master_host=‘192.168.1.11‘,master_port=3308,master_user=‘rep1‘,master_password=‘123456‘,master_log_file=‘mysql-biin.000003‘,master_log_pos=0;
   这里的参数都很简单明了，需要注意的是两个master_log_file是主服务器的log日志，日志文件名称和主服务器的show master status;显示的一致，master_log_pos=0表明从头开始读取log文件，其实这两个参数不是很实用，因为MySQL实例重启一次或者调用flush logs;就会生成新的日志文件，如果这里指定了文件名，就需要手工重新设置change master to .....，否则会产生1236错误，如果不指定，系统会自动识别，master_log_pos也是一样。
   MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.11
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-biin.000003
          Read_Master_Log_Pos: 4
               Relay_Log_File: ShiYongqiang-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-biin.000003
             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: 4
              Relay_Log_Space: 248
              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
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)
 
ERROR: No query specified
   这里显示Slave_IO_Running，Slave_SQL_Running都为NO，前者代表请求数据的线程没有运行，后者代表重放SQL的线程没有执行，还可以看到其他的参数，如可以针对某个数据库复制、针对某个表复制等等，现在是针对全服务器复制的。
   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.1.11
                  Master_User: rep1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 506
               Relay_Log_File: mysql-relay-bin.000028
                Relay_Log_Pos: 535
        Relay_Master_Log_File: mysql-bin.000015
             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: 506
              Relay_Log_Space: 1244
              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: 10
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)
 
ERROR: No query specified

可以看出这里的从服务器的复制开始运行了，需要注意log文件的名称和上面未必一致，因为在实验时重启了几次服务器，重启服务器的命令为：
   启动  mysqld
   关闭 mysqladmin -uroot -P 3308 shutdown
还可以查看到底有哪些线程在执行，
MariaDB [none]> show processlist\G;
*************************** 1. row ***************************
      Id: 5
    User: root
    Host: localhost:3994
      db: shiyq
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 28
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 6714
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 29
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 39
   State: Slave has read all relay log; waiting for the slave I/O thread to upda
te it
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
 
ERROR: No query specified
可以看出id为28的是和主服务器通信的线程，29为重写SQL的线程。
在主服务器上运行同样的命令，可以看到以下效果
MariaDB [(none)]> show processlist\G;
*************************** 1. row ***************************
      Id: 9
    User: rep1
    Host: ShiYongqiang:4807
      db: NULL
 Command: Binlog Dump
    Time: 6875
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 11
    User: root
    Host: localhost:9309
      db: NULL
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
2 rows in set (0.00 sec)
 
ERROR: No query specified
可以看出id为9的线程，是二进制日志转储线程，已经将所有的二进制文件都发送给了从服务器。
下面可以看看实际效果，
主从服务器是一样的配置，所以其默认数据库是一样的，如下
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
 
主服务器：
MariaDB [(none)]> use shiyq
Database changed
MariaDB [shiyq]> create table t(a int auto_increment primary key,b varchar(10));
 
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [shiyq]> insert into t(b) values(‘shiyq‘);
Query OK, 1 row affected (0.00 sec)
 
MariaDB [shiyq]> insert into t(b) select b from t;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
 
MariaDB [shiyq]> insert into t(b) select b from t;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
 
MariaDB [shiyq]> insert into t(b) select b from t;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
 
MariaDB [shiyq]> select * from t;
+---+-------+
| a | b |
+---+-------+
| 1 | shiyq |
| 2 | shiyq |
| 3 | shiyq |
| 4 | shiyq |
| 6 | shiyq |
| 7 | shiyq |
| 8 | shiyq |
| 9 | shiyq |
+---+-------+
8 rows in set (0.00 sec)
从服务器：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| test |
+--------------------+
5 rows in set (0.00 sec)
 
MariaDB [(none)]> use shiyq;
Database changed
MariaDB [shiyq]> show tables;
+-----------------+
| Tables_in_shiyq |
+-----------------+
| t |
+-----------------+
1 row in set (0.00 sec)
 
MariaDB [shiyq]> select * from t;
+---+-------+
| a | b |
+---+-------+
| 1 | shiyq |
| 2 | shiyq |
| 3 | shiyq |
| 4 | shiyq |
| 6 | shiyq |
| 7 | shiyq |
| 8 | shiyq |
| 9 | shiyq |
+---+-------+
8 rows in set (0.00 sec)
可以看出复制完全正常。
下面增加一个从服务器，端口为3309，server-id为12，在my.ini的修改如下
log-bin=mysql-bin
server-id = 12
relay_log = D:\mysql\mariadb-10.0.12-winx64-3\data\mysql-relay-bin
log_slave_updates = 1
read_only = 1
启动这个MySQL实例，叫做实例3，默认情况下，没有shiyq数据库，运行下列命令：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> change master to master_host=‘192.168.1.11‘,master_port=3308, master_user=‘rep1‘,master_password=‘123456‘; 
Query OK, 0 rows affected (0.04 sec)
 
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.1.11
                  Master_User: rep1
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 3002
               Relay_Log_File: mysql-relay-bin.000025
                Relay_Log_Pos: 3289
        Relay_Master_Log_File: mysql-bin.000015
             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: 3002
              Relay_Log_Space: 3629
              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: 10
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
1 row in set (0.00 sec)
 
ERROR: No query specified
 
MariaDB [(none)]> show processlist\G;
*************************** 1. row ***************************
      Id: 3
    User: root
    Host: localhost:9761
      db: NULL
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 4
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 25
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 5
    User: system user
    Host:
      db: NULL
 Command: Connect
    Time: 669
   State: Slave has read all relay log; waiting for the slave I/O thread to upda
te it
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
 
ERROR: No query specified
 
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| test |
+--------------------+
5 rows in set (0.00 sec)
在主服务器上运行如下命令：
MariaDB [shiyq]> show processlist\G;
*************************** 1. row ***************************
      Id: 9
    User: rep1
    Host: ShiYongqiang:4807
      db: NULL
 Command: Binlog Dump
    Time: 8171
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 11
    User: root
    Host: localhost:9309
      db: shiyq
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
*************************** 3. row ***************************
      Id: 15
    User: rep1
    Host: ShiYongqiang:9895
      db: NULL
 Command: Binlog Dump
    Time: 72
   State: Master has sent all binlog to slave; waiting for binlog to be updated
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)
 
ERROR: No query specified
可以看出，有了两个二进制日志转储线程，为两个从服务器服务。
再增加一个服务器，将实例二作为主服务器，进行复制，修改my.ini如下：
port=3310
log-bin=mysql-bin
server-id = 13
relay_log = D:\mysql\mariadb-10.0.12-winx64-4\data\mysql-relay-bin
log_slave_updates = 1
read_only = 1
在实例1上运行命令：
MariaDB [shiyq]> create database shiyq1;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [shiyq]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| shiyq1 |
| test |
+--------------------+
6 rows in set (0.00 sec)
所有的服务器都更新了。
在实例二运行如下命令：
MariaDB [shiyq]> create database shiyq2;
Query OK, 1 row affected (0.00 sec)
 
MariaDB [shiyq]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| shiyq |
| shiyq1 |
| shiyq2 |
| test |
+--------------------+
7 rows in set (0.00 sec)
可以看到实例4上也增加了数据库，在这些服务器上用show master status;show slave status\G;show processlist\G，可以看到相应的线程。
   2）两台完全相同机器的MySQL实例
   创建两个VirtualBox的虚拟机，操作系统为ubuntu 14.04服务器版，在部署的时候遇到一些小问题，安装了一台虚拟器，使用apt-get安装了系统自带的mysql后，为了方便和保证一致性，直接复制了使用，发现不行，在加载的时候提示uuid已存在，后来才知道需要用VirutalBox的命令C:\Program Files\Oracle\VirtualBox>VBoxManage.exe clonevdi d:\vm\ubuntu.vdi d:\vm\ubuntu1.vdi，才可以，然后部署了两个虚拟机，启动之后发现，还是不行，复制的虚拟机ip没有获取网络的DHCP，后来修改了一下虚拟机的网络配置，将网络连接方式改成桥接就好了。
   ubuntu自带的mysql为Oracle的主流版本，版本为很有意思，Oracle子公司Redhat自带的MySQL是MariaDB。
   两个虚拟机的IP地址分为192.168.1.152和192.168.1.154，前者为实例1，后者为实例2，实例1作为主服务器，实例2作为从服务器。
   修改实例1的/etc/mysql/my.conf，增加或修改如下
server-id = 10
log_bin = /var/log/mysql/mysql-bin.log
   设置复制权限，
mysql> grant replication slave on *.* to rep1@‘192.168.1.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.01 sec)
   修改实例2的/etc/mysql/my.conf，增加或修改如下
server-id = 11
log_bin = /var/log/mysql/mysql-bin.log
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
read_only = 1
    运行命令
 mysql>change master to master_host=‘192.168.1.152‘,master_port=3306, master_user=‘rep1‘,master_password=‘123456‘; 
 mysql>start slave;
   然后在主服务器上运行sql，可以看到实际效果
配置双向复制
修改实例1的/etc/mysql/my.cn，增加
relay_log = /var/log/mysql/mysql-relay-bin.log
log_slave_updates = 1
read_only = 1
运行命令
mysql> grant replication slave on *.* to rep1@‘192.168.1.%‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.01 sec)
 mysql>change master to master_host=‘192.168.1.154‘,master_port=3306, master_user=‘rep1‘,master_password=‘123456‘; 
 mysql>start slave;
双向复制理论上是可行的，比如在终端窗口输入，因为传输速度很快，等你切换终端的时候，就已经复制过来了，但是在高并发的情况下，传输可能会滞后，就会出现问题，可以配置双向复制，但是只启动一个主从复制，如果有问题，可以随时切换。


联系
我们