mysql主从配置

主从复制-主从复制搭建

简述

Mysql搭建主从复制很简单,简述为如下步骤： 1.主机上备份数据库 2.从机上恢复数据库 3.主机创建一个授权复制权限的用户 4.从机 change master to

重要参数

• 主(Master)

max_binlog_size = 2048M                 # 默认为1024M
binlog_format = ROW                     # 必须为ROW
transaction-isolation = READ-COMMITTED
expire_logs_days = 7                    # binlog保留多少天，看公司计划安排
server-id = 10                          # 必须和所有从机不一样，且从机之间也不一样
log_bin = bin.log                       # 开启binlog
binlog_cache_size = # binlog 缓存的大小，设置时要当心
sync_binlog = 1                         # 必须设置为1 
innodb_flush_log_at_trx_commit = 1      # 提交事物的时候刷新日志
innodb_support_xa = 1                   # 启用对两阶段提交的支持，8.0弃用此参数，设置默认启用，不允许关闭
# -------------------以下参数看业务需求--------------------------
binlog-do-db = # 需要复制的库
binlog-ignore-db = # 需要被忽略的库

• 从(Slave)

log_slave_updates                     # 将SQL线程回放的数据写入到从机的binlog中去（用于级联复制）
server-id = 11                        # 必须在一个复制集群环境中全局唯一
log_bin = bin.log                     # 开启binlog
relay-log-recover = 1                 # I/O thread crash safe – IO线程安全
relay_log_info_repository = TABLE     # SQL thread crash safe – SQL线程安全
master_info_repository = TABLE
read_only = 1
slave_parallel_workers=16              #开启多个works,看机器支持
slave_parallel_type=LOGICAL_CLOCK     # 并行复制，5.7.2起支持# -------------------以下参数看业务需求--------------------------
replicate-do-db = # 需要复制的库
replicate-ignore-db = # 需要忽略的库
replicate-do-table = # 需要复制的表
replicate-ignore-table = # 需要忽略的表

复制环境搭建

服务器信息

主：192.168.20.101 从：192.168.20.102

Master上创建一个复制用户

-- 主机上操作createuser'rpl'@'%'identifiedby'123456';
grantreplicationslaveon *.* to'rpl'@'%';

从机上登录验证下连接是否正常

-- 从机上操作
mysql-h192.168.20.101-urpl-p--测试连接是否正常

主从数据备份同步

主机操作

• 备份主库信息

# 备份全库
mysqldump --single-transaction --master-data=1 -A > master.sql

• 将备份传输文件到从机上去

scp/root/master.sql192.168.20.102:/root从库操作

• 恢复主库传过来的备份

mysql-uroot -p < /root/master.sql

• 查看 MASTER_LOG_FILE 与 MASTER_LOG_POS

head-30 /root/master.sql

# 或者在 master 上执行
show master status

log: bin.000003 和 Pos: 633 表明该备份开始时的 filename 和 postition

• CHANGE MASTER

change master to mysql> change master to master_host='192.168.20.101',master_user='rpl',master_password='123456',master_port=3306,master_log_file='bin.000003',master_log_pos=633;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

• 启动 slave

mysql> start slave;

• 查看主从复制状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event        # IO 线程的状态
                  Master_Host: 192.168.20.101                          # Master 的地址
                  Master_User: rpl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000003                              # IO线程读取到的主机文件
          Read_Master_Log_Pos: 633                                     # IO线程读取文件中的位置
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 314
        Relay_Master_Log_File: bin.000003                              # 从机SQL线程执行到的文件
             Slave_IO_Running: Yes                                     # io thread 状态
            Slave_SQL_Running: Yes                                     # sql thread 状态
              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: 633                                    # SQL线程执行到文件的位置
              Relay_Log_Space: 511
              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                                    # Slave 落后 Master 的秒数(不精准)
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0                                    # I/O 错误信息
                Last_IO_Error:
               Last_SQL_Errno: 0                                    # SQL 进程错误信息
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 10
                  Master_UUID: a7776f71-c8be-11e9-838f-0050563bb195
             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: a7776f71-c8be-11e9-838f-0050563bb195:1-3
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Slave_IO_Running和 Slave_SQL_Running都显示为 Yes,说明复制是正常的。

• Relay_Log

• Relay_Log_File和Relay_Log_Pos是中继日志(Relay_Log)信息由于IO线程拉取数据的速度快于SQL线程回放数据的速度，所

• Relay_Log可在两者之间起到一个缓冲的作用

• Relay_Log的格式和binlog的格式是一样的，但是两者的内容是不一样的(不是和binlog一一对应的),可使用mysqlbinlog relay.000002 -vv查看

• Relay_Log在SQL线程回放完成后，默认就会被删除，而binlog不会(由expire_logs_days控制)

• Relay_Log可以通过设置relay_log_purge=0，使得 - - Relay_Log不被删除(MHA中不希望被Purge)，需要通过外部的脚本进行删除

• Seconds_Behind_Master Seconds_Behind_Master 字面意思为Slave落后Master的 秒数 ，但是实际上并不是十分准确。因为回放的SQL线程可能落后很多，比如新加了一个库需要追很多数据。 计算的方式为： Slave的SQL线程执行时刻的时间减去event产生时刻的时间。 可以使用Percona ToolKit中的pt-heartbeat来获得精准的延迟时间

验证主从

• Master

# Master上创建数据库 aaa
mysql> create database aaa;
mysql> use aaa;
mysql> create table t(id int);
mysql> insert into t values(1),(2),(3);

• Slave

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema || aaa                || mysql              || performance_schema || sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use aaa;
Database changed

mysql> select * from t;
+------+
| id   |
+------+
|    1 ||    2 ||    3 |
+------+
3 rows in set (0.00 sec)