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 dumpslave (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-binserver-id=1innodb_file_per_table=ONskip_name_resolve=ON2、开启数据库，查看状态信息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-logrelay-log-index=relay-log.indexserver-id=2innodb_file_per_table=ONskip_name_resolve=ON2、开启数据库，查看状态MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';| relay_log | relay-log || relay_log_index | relay-log.index 3、从节点指定主节点，要注意bin日志和position，信息来自于主节点SHOW MASTER LOGSCHANGE MASTER TO MASTER_HOST='192.168.150.137',MASTER_USER='repluser',MASTER_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: NULLMaster_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: 01 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: 0Master_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: 11 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: 0Master_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: 11 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.indexaria_log_control ib_logfile1 mysql relay-log.000001 relay-log.infoibdata1 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 18master-bin.000003581192.168.150.137repluserreplpass3306600