MySQL：搭建主备复制

概述

学习一下MySQL搭建备库
主从复制就是从库利用主库的binlog进行实时的恢复
1、从库启动一个线程，连接主库io线程
2、主库接受连接，启动一个线程dump线程
3、dump线程读取binlog，将binlog日志传给从库的io线程
4、io线程将接收到的binlog日志，写入到从库的relaylog日志中
5、从库启动一个SQL apply线程，读取relay log，应用binlog中的SQL语句

注意：
1、主库的dump线程是因为从库的io线程连接而诞生
2、要分离看io thread和sql thread 备份从库的时候，可以关闭sql thread，io thread正常运行
3、注意下面的几个常用命令
show processlist 查看主库和从库的线程状态
help start slave
show slave status \G

环境信息

主机名	IP地址	角色
mysql_10	192.168.56.10	master
mysql_11	192.168.56.10	slave

搭建步骤

主库的相关配置

在/etc/my.conf 中添加几个参数

server_id=1
binlog-do-db=test
log-bin=mysql-bin

在主库中创建一个复制用户

mysql> create user 'replicate'@'%' identified by 'replicate';
Query OK, 0 rows affected (0.01 sec)
mysql> 
mysql> grant replication slave on *.* to 'replicate'@'%';
Query OK, 0 rows affected (0.00 sec)

搭建备库

对主库做全备

[root@mysql_10 databackup]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=mysql --no-timestamp /databackup

在从库上新建备份目录，并把主库的全备传输到从库上

scp -r /databackup/* 192.168.56.11:/databackup

应用一下全备

[root@mysql_11 mysqldata]# innobackupex --apply-log /databackup/

在从库上恢复全备

[root@mysql_11 mysqldata]# innobackupex --copy-back /databackup

修改一些数据库目录的权限

[root@mysql_11 mysqldata]# chown -R mysql:mysql /mysqldata/

启动从库

[root@mysql_11 mysqldata]# mysqld_safe --defaults-file=/etc/my.cnf &

登录到从库中

[root@mysql_11 mysqldata]# mysql -uroot -pmysql -S /mysqldata/mysql.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 

建立主备连接

指定主从关系

MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.56.10',MASTER_USER='replicate',MASTER_PASSWORD='replicate',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=468,MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.35 sec)

其中的MASTER_LOG_FILE这个数值，为bin_log起点。可在全备的xtrabackup_info 中可以看到

[root@mysql_10 databackup]# cat xtrabackup_info 
uuid = 6098b5f3-3fef-11e9-b781-080027724318
name = 
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf --user=root --password=... --no-timestamp /databackup
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.7.22-log
start_time = 2019-03-06 17:08:10
end_time = 2019-03-06 17:08:16
lock_time = 0
binlog_pos = filename 'mysql-bin.000001', position '154'
innodb_from_lsn = 0
innodb_to_lsn = 2597329
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

启动备库

MySQL [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

查看备库的状态

查看是否io和sql线程都启动

MySQL [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 3 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 5 | system user | | NULL | Connect | 16 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

查看io和sql线程是否有错误

MySQL [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.56.10
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 468
               Relay_Log_File: mysql_11-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          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: 468
              Relay_Log_Space: 154
              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: 1236
                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: 1
                  Master_UUID: 9a26b8d5-3c98-11e9-9764-080027724318
             Master_Info_File: /mysqldata/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: 190306 17:19:01
     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: 
1 row in set (0.00 sec)

至此，MySQL搭建主备复制完成