MySQL主备搭建

1.为什么要复制数据库？

1. 读写分离
   通过MySQL复制可以实现读写分离，将读操作分布到多个不同的服务器上，减轻服务器的压力。
   2. 备份
   从库可以作为数据的异地实时备份。
   3. 故障切换
   当主库遇到故障，系统可以切换到从库，实现故障切换。
   4. 升级测试
   从库可作为测试服务器的数据库。

2.复制数据的步骤：

1. 在主库上把数据更改记录到二进制日志（Binary Log）中。
2. 备库将主库上的日志复制到自己的中继日志（Relay Log）中。
3. 备库读取中继日志中的事件，将其在备库上重新执行一遍。

在两台机器分别启动 MySQL 实例, MySQL 搭建方式可以参考

关键配置:
Master:

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 222
default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0
#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log
#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

Salve

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 61
read_only=1
default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0
#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log
#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

配置解析

3.搭建主从

现在我们的环境如下

• 192.168.88.61:3306 Master 节点，正在使用

• 192.168.88.222:3306 新搭建数据库，要在上面做 192.168.41.83 的备库

  同步用户

  我们创建一个用户名为 repl 的用户，授予 REPLICATION SLAVE 权限专门用来同步
  

  db83-3306>>CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
  Query OK, 0 rows affected (5.01 sec)
  db83-3306>>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
  Query OK, 0 rows affected (0.00 sec)
  db83-3306>>flush privileges;
  Query OK, 0 rows affected (0.00 sec)

  开启同步

  回到备份文件，我们从头部找到 Master 备份时间点的二进制日志位置

  [mysql@mysql-test-72 ~]$ head -30 mysql_backup_full.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;

  MASTER_LOG_FILE 和 MASTER_LOG_POS 就是在 Master 执行 show master status 得到的二进制位置信息。现在，我们执行同步命令

  -- 重置复制
  -- reset slave;
  -- 同步配置
  CHANGE MASTER TO
  MASTER_HOST='192.168.41.83',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='repl',
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=154;
  -- 开启同步
  start slave

  实际执行结果如下
  

  db72-3306>>CHANGE MASTER TO
    -> MASTER_HOST='192.168.41.83',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=154;
  Query OK, 0 rows affected, 2 warnings (0.20 sec)
  db72-3306>>start slave;
  Query OK, 0 rows affected (0.01 sec)

  查看同步状态

  db72-3306>>show slave status \G;
  *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.83
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 154
              Relay_Log_Space: 527
              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: 833306
                  Master_UUID: 15958368-e9a0-11e8-a98c-ecb1d77febe4
             Master_Info_File: /data/mysql_db/mysql_seg_3306/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: 
  1 row in set (0.00 sec)
  ERROR: 
  No query specified

  我们可以从 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master 这三个参数可以判断出同步状态是否正常

• Slave_IO_Running 取 Master 日志的线程, Yes 为正在运行

• Slave_SQL_Running 从日志恢复数据的线程, Yes 为正在运行
• Seconds_Behind_Master 当前数据库相对于主库的数据延迟， 这个值是根据二进制日志的时间戳计算得到的(秒)