Mysql 主从复制

为什么使用主从

主从复制、读写分离主要目的就是为了提高数据库的并发性,在主库更新,在从库查询,在出库出现问题时,可以快速切换到从库提供服务,不需要人为参与,提高了系统的可用性,同时数据在从库进行备份,提高数据安全性;

主从原理

  • 1、master 服务器上数据发生改变时,将 sql 记录在 binlog 日志中;
  • 2、slave 服务器在一定的时间间隔内对 master 的 binlog 进行探测,判断是否发生改变,如果改变,则开启一个 I/OThread 请求,获取 master 的 binlog 日志;
  • 3、此时 master 会为 I/O 线程启动一个 dump 线程,将 master 的 binlog 发送给 slave,保存在 slave 的 relay_log 中,salve 启动 SQL 线程读取中继日志中的
    binlog,在本地执行,使得 master 和 slave 数据保持一致;

主从配置步骤

  • 1、采用两台 ubuntu 虚拟机,分别开启一个 mysql 服务,ip 地址分别为 189(主)和 188(从);
  • 2、进入 mysql 主库,添加一个从库的用户,
mysql -uroot -p
// 192.168.100.188 从库 IP 地址
// root 用户名
// test123 密码
grant replication slave on *.* to 'root'@'192.168.100.188' identified by 'test123';
  • 3、修改主库的配置文件,开启 binlog,重启 mysql 服务,查看 binlog 信息;
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
// 服务器的唯一标识
server-id               = 100
// 开启 binlog
log_bin                 = mysql-bin
// binlog日志保留的天数,清除超过10天的日志
expire_logs_days        = 10
max_binlog_size   = 100M
// 需要同步的数据库
binlog_do_db            = myproject
#binlog_ignore_db       = include_database_name
#
service mysql restart

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: myproject
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
  • 4、修改从库的配置文件中的 server-id,id 不能与主库相同,如果存在多个从库时,也不能与其他从库 id 相同;
server-id=101
  • 5、进入从库,输入一下命令,启动 salve,查看 salve 运行情况;
mysql> change master to
    // 主库 ip
    -> master_host='192.168.100.189',
    // 主库中新建的用户名
    -> master_user='root',
    // 新建用户的密码
    -> master_password='test123',
    // 主库 binlog 日志名称
    -> master_log_file='mysql-bin.000001',
    // binlog 日志偏移量
    -> master_log_pos=862,
    -> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 476
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 644
        Relay_Master_Log_File: mysql-bin.000008
             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: 476
              Relay_Log_Space: 854
              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: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

当 Slave_IO_State 为 Waiting for master to send event 时,则表示主从配置成功;

  • 6、测试主从
    在主库创建新表 demo, 并插入一条记录;
CREATE TABLE `demo` (
    ->   `id` INT NOT NULL AUTO_INCREMENT,
    ->   `a` VARCHAR(45) NULL,
    ->   `b` VARCHAR(45) NULL,
    ->   PRIMARY KEY (`id`))
    -> ENGINE = InnoDB
    -> DEFAULT CHARACTER SET = utf8;

insert into demo5 (a, b) values ('aaa', 'bbb');

查看从库记录;

mysql> show tables;
+---------------------+
| Tables_in_myproject |
+---------------------+
| demo5               |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from demo5;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 | aaa  | bbb  |
+----+------+------+
1 row in set (0.00 sec)

配置过程中遇到的问题

  • 1、配置完从库后查看从库运行情况,发现从库正在连接主库,并且有错误提示;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 756
               Relay_Log_File: ubuntu-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Connecting
            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: 756
              Relay_Log_Space: 156
              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: 2003
                Last_IO_Error: error connecting to master 'root@192.168.100.189:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.100.189' (111)
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210414 01:21:37
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

在命令行直接连接主库,发现无法连接成功;

yzw@ubuntu:/etc$ mysql -h192.168.100.189 -uroot -p
Enter password: 
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.100.189' (111)

此时只需要修改主库配置文件中的 bind-address,将其注释或者改为 0.0.0.0,此项配置意思是 mysql 服务只监听本地,因此从库无法连接成功;

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
  • 2、Slave_IO_State 无值并有错误信息;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 862
               Relay_Log_File: ubuntu-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: No
            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: 862
              Relay_Log_Space: 156
              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: 13114
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 210414 01:50:22
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

查看主库,发现主库的 Position 为 154,从库设置了 862,因此才会报错,修改从库设置后重新启动即可成功;

mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000016
         Position: 154
     Binlog_Do_DB: myproject
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.01 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_log_pos=154;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
  • 3、从库数据库名称和主库不同,主库操作后,从库无法同步;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.189
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000016
          Read_Master_Log_Pos: 476
               Relay_Log_File: ubuntu-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000016
             Slave_IO_Running: Yes
            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: 1049
                   Last_Error: Error 'Unknown database 'myproject'' on query. Default database: 'myproject'. Query: 'CREATE TABLE `demo7` (   `id` INT NOT NULL AUTO_INCREMENT,   `a` VARCHAR(45) NULL,   `b` VARCHAR(45) NULL,   PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 854
              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: 1049
               Last_SQL_Error: Error 'Unknown database 'myproject'' on query. Default database: 'myproject'. Query: 'CREATE TABLE `demo7` (   `id` INT NOT NULL AUTO_INCREMENT,   `a` VARCHAR(45) NULL,   `b` VARCHAR(45) NULL,   PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: fd430c7c-6cb7-11ea-95dc-000c296fcfe0
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 210414 01:58:11
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

此时必须在从库创建和主库一样的数据库名称,重新同步一下即可(主库执行 flush logs 即生成新的 binlog 日志,修改主库读取的 binlog 名称);

主从同步机制

  • 1、binlog+pos(默认为异步)

  • 2、半同步复制(5.5版本后)
    master 执行完一个事务后不是立即将结果返回客户端,而是等待至少一个 slave 接收到了主库 binlog,并保存在 slave 的 relay_log 后,才会向客户端返回结果;

  • 3、全同步复制
    当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。

  • 4、数据库中间件
    所有的读写请求都走中间件,由中间件去分配请求是到主库还是从库,记录所有请求主库的 key,如果还没有同步时,就有读请求进来,此时从库书库还没有更新,则中间件会把请求发到主库,如果已经同步,则将请求发到从库,这样就能保证数据的一致性;

推荐阅读更多精彩内容