MySQL数据库——集群篇——1
一 MySQL 主从复制
1 主从复制架构和原理
1.1 服务性能扩展方式
Scale Up，向上扩展，垂直扩展
向上扩展，买更好的服务器，这种方式比较简单，一般情况下向上扩展就可以解决问题，但是如果代价太大了（规格越高的硬件需要花费的钱越多），就不可取了。而且向上扩展总有极限的。
Scale Out，向外扩展，横向扩展
横向扩展是通过副本（读写分离）、垂直切分，水平切分的方式，把不同的数据放在不同的节点（物理部署的MySQL实例）中。
1.2 MySQL的扩展
- 读写分离
- 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
1.3 复制的作用
- 数据分布
- 负载均衡读操作
- 备份
- 高可用和故障切换
- MySQL升级测试
1.4 复制架构
一主一从复制架构
一主多从复制架构
1.5 主从复制原理
文字叙述
Master 端操作：
首先进行数据更新操作。
数据更新后，将操作写入二进制日志（Bin Log）。
随后，Slave 服务线程获取 Bin Log 中的内容。
数据传输与 Slave 端处理：
Slave 服务线程把获取到的 Bin Log 内容传输给 Slave 的 IO 线程（io Thread）。
IO 线程将接收到的内容写入中继日志（Relay Log）。
接着，SQL 线程（SQL Thread）读取 Relay Log 中的内容。
最后，依据 Relay Log 中的内容在 Slave 上进行数据更新，从而实现主从数据的同步。
主从复制相关线程（线程：Thread）
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
跟复制功能相关的文件：
master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等
relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系
mariadb-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志
1.6 主从复制特点
异步复制: 客户端性能良好
主从数据不一致比较常见
1.7 各种复制架构
一Master/一Slave
一主多从
从服务器还可以再有从服务器
Master/Master
一从多主:适用于多个不同数据库
环状复制
复制需要考虑二进制日志事件记录格式
STATEMENT（5.0之前）
ROW（5.1之后，推荐）
MIXED
2 实现主从复制配置
参考官网
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中日志名称，确保不依赖主机名说明：
server-id的取值范围：
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此slave的连接
(3) 查看从二进制日志的文件和位置开始进行复制
SHOW MASTER LOG;(4) 创建有复制权限的用户账号
GRANT REPLICATION SLAVE ON . TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';从节点配置：
(1) 启动中继日志
[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index(2) 使用有复制权限的用户账号连接至主服务器，并启动复制线程
CHANGE MASTER TO MASTER_HOST='masterhost',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;
START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;范例：新建主从复制
[root@master ~]#yum -y install mariadb-server
[root@master ~]#vim /etc/my.cnf
#[mysqld]下新增下面两行
server-id=8
log-bin
[root@master ~]#systemctl start mariadb
[root@master ~]#systemctl enable mariadb
[root@master ~]#mysql
#查看二进制文件和位置
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 245 |
+--------------------+-----------+
1 row in set (0.00 sec)
#创建复制用户
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.3.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#从节点
[root@slave ~]#yum install mariadb-server -y
[root@slave ~]#vim /etc/my.cnf
#[mysqld]下添加下面一行
server-id=18
[root@slave ~]#systemctl restart mariadb
[root@slave ~]#systemctl enable mariadb
[root@slave ~]#mysql
MariaDB [(none)]> help change master to
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.3.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 399
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 685
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes #IO线程是yes
Slave_SQL_Running: Yes #SQL线程是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: 399
Relay_Log_Space: 981
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: 8
1 row in set (0.00 sec)
MariaDB [(none)]>
#验证主从复制
#主库导入数据
[root@master ~]#mysql < testdata.sql
[root@master ~]#mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use test;
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| departments |
| dept_emp |
| employees |
| salaries |
| titles |
+----------------+
5 rows in set (0.00 sec)
#从库验证数据同步情况
[root@slave ~]#mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use test;
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| departments |
| dept_emp |
| employees |
| salaries |
| titles |
+----------------+
5 rows in set (0.00 sec)3 主从复制相关
3.1 限制从服务器为只读
read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效注意：以下命令会阻止所有用户, 包括主服务器复制的更新
FLUSH TABLES WITH READ LOCK;3.2 在从节点清除信息
注意：以下都需要先 STOP SLAVE
xxxxxxxxxx RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay logRESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和PASSWORD 等3.3 复制错误解决方法
可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID 注意: Centos 8.1以上版本上主从节点同时建同名的库和表不会冲突，建主键记录会产生冲突
#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL 范例：复制冲突的解决
#CentOS7上Mariadb5.5 在slave创建库和表,再在master上创建同名的库和表,会出现复制冲突,而在CentOS8上的Mariadb10.3上不会冲突
#如果添加相同的主键记录都会冲突
#从库2执行
MariaDB [(none)]> create database demo;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| demo |
| hellodb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
#主库执行
MariaDB [(none)]> create database demo;
Query OK, 1 row affected (0.00 sec)
#从库2执行
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.17
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000002
Read_Master_Log_Pos: 417
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 620
Relay_Master_Log_File: mariadb-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error 'Can't create database 'demo'; database exists' on query. Default database: 'demo'. Query: 'create database demo'
Skip_Counter: 0
Exec_Master_Log_Pos: 334
Relay_Log_Space: 999
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'demo'; database exists' on query. Default database: 'demo'. Query: 'create database demo'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 8
1 row in set (0.00 sec)
#方法1
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G
#方法2
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
slave_skip_errors=1007 或者 slave_skip_errors=ALL
[root@slave1 ~]#systemctl restart mariadb3.4、使用中的相关命令
3.4.1、MySQL 数据库中用于重置从库（Slave）复制相关信息的命令
reset slave all; 4.主主复制
主主复制：两个节点，都可以更新数据，并且互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
auto_increment_offset=1 #开始点
auto_increment_increment=2 #增长幅度另一个节点使用偶数id
auto_increment_offset=2
auto_increment_increment=2