# mysql 主从同步实战


<!--more-->


## 主从同步

### 1. Master配置

- vim /etc/my.cnf 添加以下bin_log配置

  ```
  #bin_log配置 
  log_bin=mysql-bin 
  server-id=1 
  sync-binlog=1 
  binlog-ignore-db=information_schema 
  binlog-ignore-db=mysql 
  binlog-ignore-db=performance_schema 
  binlog-ignore-db=sys
  #relay_log配置 
  relay_log=mysql-relay-bin 
  log_slave_updates=1 
  relay_log_purge=0

- 重启服务  systemctl restart mysqld

- 主库给从库授权

  ```mysql
  grant replication slave on *.* to root@'%' identified by '123456'; 
  grant all privileges on *.* to root@'%' identified by '123456';
  flush privileges;
  
  //查看主库状态信息，例如master_log_file='mysql-bin.000007',master_log_pos=154 
  show master status;
  ```

### 2. Slave配置

- 修改Slave的MySQL配置文件my.cnf，两台Slave的server-id分别设置为2和3 

  ```properties
  #bin_log配置 
  log_bin=mysql-bin 
  #服务器ID,从库1是2,从库2是3 
  server-id=2 
  sync-binlog=1 
  binlog-ignore-db=information_schema 
  binlog-ignore-db=mysql 
  binlog-ignore-db=performance_schema 
  binlog-ignore-db=sys 
  #relay_log配置 
  relay_log=mysql-relay-bin 
  log_slave_updates=1 
  relay_log_purge=0 
  read_only=1
  ```

- 重启服务 systemctl restart mysqld

- 开启同步

  登录MySQL，在Slave节点的MySQL命令行执行同步操作，例如下面命令（注意参数与上面`show master status`操作显示的参数一致）

  ```mysql
  change master to master_host='192.168.31.199',master_port=3306,master_user='root',master_password ='123456',master_log_file='mysql-bin.000007',master_log_pos=154; 
  # master_log_file 和 master_log_pos都是从 show master status取
  
  start slave; // 开启同步
  ```



接下来就可以测试了



## 半同步复制配置

### 1. master节点

- 登录mysql 安装插件

  ```mysql
  install plugin rpl_semi_sync_master soname 'semisync_master.so'; 
  show variables like '%semi%';
  ```

- vim /etc/my.cnf

  ```properties
  # 自动开启半同步复制 
  set global rpl_semi_sync_master_enabled = ON;
  
  # 开启以后在配置文件中添加 每次重启就默认开启
  rpl_semi_sync_master_enabled=ON 
  rpl_semi_sync_master_timeout=1000
  ```

- 重启服务 systemctl restart mysqld



### 2. Slave配置

- 登录mysql, 执行以下命令

  ```mysql
  install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
  ```

- vim /etc/my.cnf

  ```
  # 自动开启半同步复制 
  rpl_semi_sync_slave_enabled=ON 
  ```

- 重启服务

### 3. 测试半同步

- show variables like '%semi%';

- cat /var/log/mysqld.log

- 可以看到日志中已经启动半同步

  ```
  Start semi-sync binlog_dump to slave (server_id: 2), pos(mysql-bin.000005, 154)
  ```



## 并行复制配置

### 1. Master

- show variables like '%binlog_group%';

- 设置参数

  ```mysql
  set global binlog_group_commit_sync_delay=1000;
  set global binlog_group_commit_sync_no_delay_count=10;
  ```

### 2. Slave

- 设置参数1

  ```mysql
  show variables like '%slave_parallel%';
  
  #先停止
  stop slave;
  
  set global slave_parallel_type='LOGICAL_CLOCK';
  set global slave_parallel_workers=8;
  
  start slave;
  
  #最好写到配置文件, 因为重启后就失效了, 配置完要重启
  slave_parallel_type=LOGICAL_CLOCK
  slave_parallel_workers=8
  relay_log_info_repository=TABLE
   
  ```

- 设置参数2

  ```mysql
  show variables like '%relay_log%';
  
  #以下需要到my.cnf配置
  relay_log_recovery=1
  ```

- 查看

  ```mysql
  select * from performance_schema.replication_applier_status_by_worker;
  #可以查看到worker_id等
  ```

  

**注意: 参数生效最好配置到my.conf中, 这样在重启的时候,可以继续生效**