共计 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、混合 MIXED |
2、配置过程：
主从
| 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
正文完
星哥玩云-微信公众号
