详解MySQL主从复制实战 - 基于日志点的复制

基于日志点的复制

1、在主库与从库上建立专用的复制账号

mariadb [employees]> create user 'repl'@'172.%' identified by '123456';

注意在生产上的密码必须依照相关规范以达到一定的密码强度, 并且规定在从库上的特定网段上才能访问主库

2、在主库与从库上授予复制权限

mariadb [employees]> grant replication slave on *.* to 'repl'@'172.%';

3、配置主库

注意启用二进制日志需要重启服务, 而server_id是一个动态参数, 可以结合命令行与配置文件以达到免重启的持久化配置. 注意server_id在集群中是唯一的.

[mysqld]
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
binlog_format = row
server_id = 101

note: 把日志与数据分开是个好习惯, 最好能放到不同的数据分区

4、配置从库

选项log_slave_update决定是否把中继日志relay_log存放到本机的binlog中, 如果是配置链路复制, 那么该选项必填. 注意server_id在集群中是唯一的.

[mysqld]
# replication
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
server_id = 102
# slaves
relay_log    = /var/log/mysql/relay-bin
relay_log_index  = /var/log/mysql/relay-bin.index
relay_log_info_file  = /var/log/mysql/relay-bin.info
log_slave_updates = on
read_only

5、初始化从库的数据

此处使用mysqldump在主库上进行备份, 在生产上建议大家用xtrabackup进行无锁的热备(基于innodb引擎).

备份主库上的employees数据库的数据

mysqldump --single-transaction --master-data=1 --triggers --routines --databases employees -u root -p >> backup.sql

将备份文件backup.sql通过scp或者docker volume卷挂载到从服务器上, 并且导入至从库中

mysql -u root -p < backup.sql

6、启动复制链路

现有和, 并且已经通过mysqldump将数据同步至从库slave中. 现在在从服务器slave上配置复制链路

mariadb [(none)]> change master to master_host='master', master_user='repl', master_password='123456', master_log_file='mariadb-bin.000029', master_log_pos=516;
query ok, 0 rows affected (0.02 sec)

在从库上启动复制链路

mariadb [(none)]> start slave;
query ok, 0 rows affected (0.01 sec)

7、在从库上检查slave状态

slave_io_running与slave_sql_running必须为yes, 如果出现错误须详细阅读last_io_error或last_sql_error的提示信息

mariadb [(none)]> show slave status\g
*************************** 1. row ***************************
        slave_io_state: waiting for master to send event
         master_host: master
         master_user: repl
         master_port: 3306
        connect_retry: 60
       master_log_file: mariadb-bin.000029
     read_master_log_pos: 516
        relay_log_file: relay-bin.000002
        relay_log_pos: 539
    relay_master_log_file: mariadb-bin.000029
       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: 516
       relay_log_space: 831
       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: 101
        master_ssl_crl:
      master_ssl_crlpath:
          using_gtid: no
         gtid_io_pos:
   replicate_do_domain_ids:
 replicate_ignore_domain_ids:
        parallel_mode: conservative
1 row in set (0.00 sec)

8、在主库检查dump线程

检测是否已经正确启动binlog dump线程

mariadb [(none)]> show processlist \g
*************************** 1. row ***************************
   id: 7
  user: root
  host: 172.20.0.1:41868
   db: employees
 command: sleep
  time: 56
  state:
  info: null
progress: 0.000
*************************** 2. row ***************************
   id: 10
  user: repl
  host: 172.20.0.3:45974
   db: null
 command: binlog dump
  time: 246
  state: master has sent all binlog to slave; waiting for binlog to be updated
  info: null
progress: 0.000

可以看到row 2上有command为binlog dump的命令被启动, 证明复制线程已经被成功启动

9、总结

优点

1. 技术成熟, bug相对较少
2. 对sql查询没有任何限制, 如基于gtid复制时不是所有sql都可以使用
   

缺点

1. 故障转移时重新获取新主的日志偏移量较为困难

在一主多从环境下, 若旧master宕机后在集群中选举出新master, 其他的从库要对这个新的master进行重新同步, 由于每个db的binlog都是独立存在, 所以很难找出开始同步的日志点

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。