Mysql主从复制,级联复制（重点，重点，重点）命令，参数

一. MySQL复制

扩展方式： Scale Up纵向 ，Scale Out横向
纵向：配置
横向：机器
MySQL的扩展
读写分离
复制：每个节点都有相同的数据集
向外扩展
二进制日志
单向
复制的功用

 数据分布
 
 负载均衡读
 
 备份
 
 高可用和故障切换（主坏了，将从切换为主）
 
 MySQL升级测试

前端（分片管理器—调度器）

1.一主一从（小企业）

必须启用二进制日志
与sync同步有关
主master负责DML语言，增删改
从slave 负责读

2.一主多从（业务多）

二. 主从复制原理

必须在，主服务器里面启用二进制日志
从服务器，不要求，如果有二级从服务器，从服务器就得启用
relay中继

MySQL垂直分区（ scale up）

分库分表：将一个服务器上的数据放到多个服务器
同一个数据库中毫不相干（join内连接）的若干表，放到不同的服务器中（都是主）

MySQL水平分片（Sharding）

把同一个表的数据按一定的逻辑拆分（根据用户的编号，范围等）

对应shard中查询相关数据

水平分片：就需要，前端（分片管理器—调度器）索引----用户在哪个服务器上

三. MySQL复制

主从复制线程：

主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志relay log中
SQL Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件：

master.info：用于保存slave连接至master时的相关信息，例如账号、密码.服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地replay log日志的对应关系

主从复制特点：
异步复制
主从数据不一致比较常见

复制架构：
Master/Slave, Master/Master, 环状复制
一主多从
从服务器还可以再有从服务器 级联复制
一从多主:适用于多个不同数据库

复制需要考虑二进制日志事件记录格式
STATEMENT（5.0之前） 语句型
ROW（5.1之后，推荐）行型（推荐）
MIXED 混合型

级联复制----主—从–从…

四. 主从复制配置

主从配置过程：参看官网
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

主节点配置：

(1) 启用二进制日志

 [mysqld]
 log_bin

(2) 为当前节点设置一个全局惟一的ID号

 [mysqld]
 server_id=#
 log-basename=master 可选项，设置datadir中日志名称，确保不`依赖主机名`

(3) 创建有复制权限的用户账号
*.*所有库

grant  replication slave  on   *.* to 'new'@'192.168.1.100'   identified  by  'password';

刷新权限flush privileges;

关闭所有表锁定数据库以防止写入数据

主节点：上锁
flush    tables    with  read  lock;

从节点取消锁
unlock    tables;

查看从二进制日志的文件和位置开始进行复制

show master status;

从节点配置：

(1) 启动中继日志

 [mysqld]
server_id=# 为当前节点设置一个全局惟的ID号
read_only=ON 设置`数据库只读`
relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index 默认值hostname-relay-bin.index

(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程

查看从节点状态

show slave status  \G

 mysql> CHANGE MASTER TO 
 MASTER_HOST='host',                     主节点地址
MASTER_USER='repluser',  				 用户
MASTER_PASSWORD='replpass',   			  密码
MASTER_LOG_FILE=' mariadb-bin.xxxxxx',    文件
 MASTER_LOG_POS=#;    				要记住位置show master  logs;

这样就会生成master.info 主结点信息
relay-log.info中继日志

show slave status 	\G

最后开启线程 start slave;
查看线程：show processlist;

测试：
在主节点创建库，看从节点是否同步

①新建主从复制

注意:主服务器有数据—需要备份

主节点

[root@master ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=8
log-bin


systemctl restart mariadb

#创建一个用户
[root@master ~]#mysql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by '123456'; 

#查看二进制文件和位置
MariaDB [(none)]> show master logs;

从节点

[root@slave ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18  


systemctl restart mariadb


连接master
[root@slave1 ~]#mysql 
MariaDB [(none)]> CHANGE MASTER TO   MASTER_HOST='192.168.8.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002',
MASTER_LOG_POS=545;
 
MariaDB [(none)]> start slave;

查看状态
 show slave status\G

② 给老的master创建slave

主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

主节点

#在主服务器完全备份
mkdir /backup
chown mysql.mysql /backup
		#备份
		--single-transaction 会自动刷新一次日志 ，
		要配合-F备份前滚动日志
		注意这里的- -master-data=1
mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql


	拷贝到从服务器
scp /backup/fullbackup_2019-11-27_17\:41\:17.sql  192.168.8.11:/data/


#建议优化 主 和 从 节点服务器的性能
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2
MariaDB [hellodb]> set global sync_binlog=0
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;

#将完全备份还原到新的从节点
dnf -y install mariadb-server


vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server-id=11
read-only


systemctl restart mariadb

#配置从节点，从完全备份的位置之后开始复制
[root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2019-11-27_17\:41\:17.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

[root@slave ~]#vim /data/fullbackup_2019-11-27_17\:41\:17.sql
CHANGE MASTER TO
MASTER_HOST='192.168.8.10',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,                                                               
          MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

[root@slave ~]#mysql < /data/fullbackup_2019-11-27_17\:41\:17.sql

将备份拷到 从服务器scp

给备份文件加入

vim /data/all.sql
CHANGE MASTER TO
MASTER_HOST='192.168.37.7',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,                         
MASTER_LOG_FILE='mariadb-bin.000002', 
MASTER_LOG_POS=245;

查看状态show slave status\G
启动线程start slave

③ 主服务器down，提升一个从服务器成为新的主

例如：突然断电（或者物理性损坏）

挑选从服务器提升为新的主
查看那个编号那个最大----那个最新

show slave status\G

1. 新master修改配置文件，关闭read-only配置

vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only=OFF
log-bin=/data/mysql/logbin/mysql-bin

2. 清除旧的master复制信息

MariaDB [hellodb]>set global read_only=off;
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;

3. 在新master上完全备份

mysqldump -A --single-transaction --master-data=1 -F > 
backup.sql
scp backup.sql 10.0.0.28:

分析旧的master 的二进制日志，将未同步到至新master的二进制日志导出来，恢复到新master,尽可能恢复数据

3. 其它所有 slave 重新还原数据库，指向新的master

vim backup.sql 
CHANGE MASTER TO
 MASTER_HOST='10.0.0.18',
   MASTER_USER='repluser',
     MASTER_PASSWORD='centos',
       MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=371;


mysql
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
MariaDB [hellodb]>set sql_log_bin=off;
MariaDB [hellodb]>source backup.sql;
MariaDB [hellodb]>set sql_log_bin=on;
启动线程
MariaDB [hellodb]>start slave;

主从复制相关

1. 限制从服务器为只读

read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效

以下命令会阻止所有用户, 包括主服务器复制的更新，防止写入数据

flush    tables    with  read  lock;

2. 在从节点清除信息
   注意：以下都需要先 STOP SLAVE

RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE  ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和
PASSWORD 等

3. 复制错误解决方法
   可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID

#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

4. START SLAVE 语句，指定执到特定的点

START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL
   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos
thread_types:
   [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

复制冲突的解决

#方法1
MariaDB [(none)]> stop slave;
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave; 


#方法2
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL                                                       
                                         
[root@slave1 ~]#systemctl restart mariadb

保证主从复制的事务安全

在master节点启用参数：

sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
innodb_support_xa=ON #分布式事务MariaDB10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘

在slave节点启用服务器选项：

skip-slave-start=ON #不自动启动slave

在slave节点启用参数：

sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

五.级联复制

如果要启用级联复制,需要在中间从服务器启用以下配置

注意级联节点也是从节点---->配置和从结点一样

[mysqld]
log_bin     这个二进制日志，记录的是自身的更新
log_slave_updates    让级联节点->把中继日志同步到数据库并且写入二进制日志中
server-id=18
read-only

注意：
主节点做备份---->给级联
级联做备份---->给从节点

三台主机：

1  master 服务器配置
vim /etc/my.cnf
server-id=1
log-bin=/data/logbin/mysql


systemctl restart mariadb

创建用户用于同步信息
mysql> grant replication slave on *.* to repluser@'192.168.36.%' identified by  'centos';

备份
mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql 
scp /data/all.sql 级联从服务器上:/data



2 中间的级联从服务器
vim /etc/my.cnf
server-id=2
log-bin
log_slave_updates       // #级联复制中间节点的必选项   关键
read-only

vim /data/all.sql 
CHANGE MASTER TO
MASTER_HOST='主服务器',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=400;   


mysql 
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/all.sql
MariaDB [(none)]> show master logs;  #记录二进制位置，给第三个节点使用  
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> start slave;


mysqldump -A --single-transaction --master-data=1  -F > /data/all.sql  做备份
scp /data/all.sql 最终的从服务器上:/data

3 最后的从服务器
vim /etc/my.cnf
server-id=3
read-only

vim /data/all.sql 
CHANGE MASTER TO
MASTER_HOST='中间的级联服务器',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=245; 

mysql <  /data/all.sql 
mysql> start slave;

注意的问题：

1、限制从服务器为只读

在从服务器上设置read_only=ON
注意：此限制对拥有SUPER权限的用户均无效
阻止所有用户, 包括主服务器复制的更新，防止写入数据

 mysql> flush    tables    with  read  lock;

2、从服务器清除

RESET SLAVE：从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL：清除所有从服务器上设置的主服务器同步信息，如：PORT, HOST, USER和 PASSWORD 等
注意：以上都需要先STOP SLAVE

3、从服务器忽略

sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量 set global sql_slave_skip_counter=1;

4、如何保证主从复制的事务安全

参看https://mariadb.com/kb/en/library/server-system-variables/

• 在master节点启用参数：
  sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
  如果用到的为InnoDB存储引擎：
  innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
  innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
  sync_master_info=# #次事件后master.info同步到磁盘
• 在slave节点启用服务器选项：
  skip-slave-start=ON 不自动启动slave
  在slave节点启用参数：
  sync_relay_log=# #次写后同步relay log到磁盘
  sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

常见问题

1. 假如从节点创建一个数据库db2（从节点只读 ready_only）
2. 主节点又创建一个数据库db2，进而同步从节点，从节点会出现错误，并且不能同步
3. 导致的问题
   主节点不能与从节点同步
4. 解决办法：

• 忽略它（避免影响主从复制，导致不能备份数据）
  最主要确认从库没有写权限 ，ready_only
  1、 stop slave;临时停止复制
  2、 set global sql_slave_skip_counter=# ; 忽略几个错误
  3、 start slave;
  4、 忽略错误编号（在show slave start \G中看）
  只能在配置文件中slave-skip-errors=1062
  然后重启服务—线程会自动重启

  总结：从节点不能使用DML语言，只能使用select

优化

#建议优化 主 和 从 节点服务器的性能
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2
MariaDB [hellodb]> set global sync_binlog=0
MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;