共计 7995 个字符，预计需要花费 20 分钟才能阅读完成。
MySQL 主从复制架构及实现
1、原理：
复制功能及作用：
数据分布
负载均衡：进行读操作的负载均衡，适用于读密集型的应用
可以用于备份
高可用和故障切换
MySQL 的升级测试主从复制：
从服务器：IO 线程：从 master 请求二进制日志信息，并保存至中继日志；SQL 线程：从 relay log 中读取日志信息，在本地完成重放；主节点：dump Thread：为每个 Slave 的 I /O Thread 启动一个 dump 线程，用于向其发送 binary log events；特点：异步模式：async
1、从服务器落后于主服务器
2、主从数据不一致现象比较常见
复制架构：M/ S 主从，M/ M 主主，环状复制
一主多从：从服务器还可以再有从服务器
二进制日志的事件记录格式：SET datetime = now()
1、基于行 ROW
2、基于语句 STATEMENT
3、混合 MIXED2、配置过程：
主从
master
(1)启动二进制日志；[mysqld]配置文件中添加
log-bin=master-bin
(2)设置一个当前群集中唯一的 server-id;
[mysqld]配置文件中添加
server_id=#
(3)创建一个有复制权限的账号(REPLICATION SLAVE,REPLICATION CLIENT);
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
进程：binlog dump
slave
(1)启用中继日志；[mysqld]配置文件中添加
relay_log=relay-log
relay_log_index=relay-log.index
(2)设置一个在当前集群中的唯一的 server-id；[mysqld]配置文件中添加
server_id=#
(3)使用有复制权限用户账号连接至主服务器，并启动复制线程；进程：IO thread,SQL thread
实验：架构 主服务器地址 192.168.150.137 从服务器地址 192.168.159.138
两台 mariadb 均通过 yum 安装，版本相同 mariadb-5.5.52-1.el7.x86_64
主节点
1、修改配置文档，添加参数
vim /etc/my.cnf
在 [mysqld] 中添加如下几行
log-bin=master-bin
server-id=1
innodb_file_per_table=ON
skip_name_resolve=ON
2、开启数据库，查看状态信息
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';
log_bin | ON
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 30379 |
| master-bin.000002 | 1038814 |
| master-bin.000003 | 245 |
+-------------------+-----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| server_id | 1 |
+----------------------+-------------------+
3 rows in set (0.00 sec)
3、授权账号
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser' @ 192.168.%.%'IDENTIFIED BY'replpass';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
从节点
1、修改配置文档，添加参数
vim /etc/my.cnf
在 [mysqld] 中添加如下几行
relay-log=relay-log
relay-log-index=relay-log.index
server-id=2
innodb_file_per_table=ON
skip_name_resolve=ON
2、开启数据库，查看状态
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';
| relay_log | relay-log
|| relay_log_index | relay-log.index
3、从节点指定主节点，要注意 bin 日志和 position，信息来自于主节点 SHOW MASTER LOGS
CHANGE MASTER TO MASTER_HOST='192.168.150.137',MASTER_USER='repluser',MA
STER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=245;
4、通过目录查看从节点状态，此时 bin log 已指定完成，Slave_IO_Running 和 Slave_SQL_Running 进程还没有开启
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.150.137
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 245
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: No
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: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 245
Relay_Log_Space: 245
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: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
5、开启 SLAVE 进程, 此时 IO 进程和 SQL 进程均为 YES 状态
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.150.137
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 497
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 782
Relay_Master_Log_File: master-bin.000003
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: 497
Relay_Log_Space: 1070
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: 1
1 row in set (0.00 sec)
功能验证：主节点进行测试库创建
MariaDB [(none)]> CREATE DATABASE mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-bin.000001 | 30379 |
| master-bin.000002 | 1038814 |
| master-bin.000003 | 580 |
+-------------------+-----------+
3 rows in set (0.00 sec)
从库进行同步验证：mydb 库已经过来，状态信息中 bin log 也应用过来了
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
[(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.150.137
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 580
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 865
Relay_Master_Log_File: master-bin.000003
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: 580
Relay_Log_Space: 1153
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: 1
1 row in set (0.00 sec)
另：从库的数据文件夹中会记录一些信息，master.info 会记录我连的主库的地址，bin log 信息等
[root@localhost ~]# ls /var/lib/mysql/
aria_log.00000001 ib_logfile0 mydb performance_schema relay-log.index
aria_log_control ib_logfile1 mysql relay-log.000001 relay-log.info
ibdata1 master.info mysql.sock relay-log.000002 test
[root@localhost ~]# cd /var/lib/mysql/
[root@localhost mysql]# file master.info
master.info: ASCII text
[root@localhost mysql]# cat master.info
18
master-bin.000003
581
192.168.150.137
repluser
replpass
3306
60
0本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-04/142565.htm
正文完
星哥玩云-微信公众号
