1.MySQL 主从复制的原理
MySQL主从复制涉及到三个线程,一个运行在主节点(log dump thread),其余两个(I/O thread, SQL thread)运行在从节点
1)主节点 log dump 线程
当从节点连接主节点时,主节点会为其创建一个log dump 线程,用于发送和读取bin-log的内容。在读取bin-log中的操作时,log dump线程会对主节点上的bin-log加锁,当读取完成,在发送给从节点之前,锁会被释放。主节点会为自己的每一个从节点创建一个log dump 线程。
2)从节点 I/O线程
当从节点上执行`start slave`命令之后,从节点会创建一个I/O线程用来连接主节点,请求主库中更新的bin-log。I/O线程接收到主节点的blog dump进程发来的更新之后,保存在本地relay-log(中继日志)中。
3)从节点 SQL线程
SQL线程负责读取relay-log中的内容,解析成具体的操作并执行,最终保证主从数据的一致性。
对于每一个主从连接,都需要这三个进程来完成。当主节点有多个从节点时,主节点会为每一个当前连接的从节点建一个log dump 进程,而每个从节点都有自己的I/O进程,SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务,这样在执行同步数据任务的时候,不会降低读操作的性能。比如,如果从节点没有运行,此时I/O进程可以很快从主节点获取更新,尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止,至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中,当服务再次起来之后,就可以完成数据的同步。
要实施复制,首先必须打开Master 端的binary log(bin-log)功能,否则无法实现。
4)复制的基本过程
- 在从节点上执行sart slave命令开启主从复制开关,开始进行主从复制。从节点上的I/O 进程连接主节点,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
- 主节点接收到来自从节点的I/O请求后,通过负责复制的I/O进程(log dump 线程)根据请求信息读取指定日志指定位置之后的日志信息,返回给从节点。返回信息中除了日志所包含的信息之外,还包括本次返回的信息的bin-log file 的以及bin-log position(bin-log中的下一个指定更新位置);
- 从节点的I/O进程接收到主节点发送过来的日志内容、日志文件及位置点后,将接收到的日志内容更新到本机的relay-log(中继日志)的文件(Mysql-relay-bin.xxx)的最末端,并将读取到的binary log(bin-log)文件名和位置保存到master-info 文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容,请发给我”;
Slave 的 SQL线程检测到relay-log 中新增加了内容后,会将relay-log的内容解析成在主节点上实际执行过SQL语句,然后在本数据库中按照解析出来的顺序执行,并在relay-log.info中记录当前应用中继日志的文件名和位置点。
2.完成MySQL主从复制(一主两从)
master配置
1.在/etc/my.cnf中添加:
[mysqld]
# 开启binlog
log-bin=mysql-bin
server-id=1
#binlog-do-db=test_db #需要同步的数据库,如果不配置则同步全部数据库
expire-logs-days=10 #binlog #日志保留的天数,清除超过10天的日志,防止日志文件过大,导致磁盘空间不足
2.配置完成后,重启mysql:
systemctl mysql restart
3.查看当前binlog日志的信息(后面有用):
mysql> show master status;
或
mysql> show master status\G;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
slave配置
1.在/etc/my.cnf中添加
[mysqld]
server-id=2
#另外一台192.168.23.112设置为server-id=3
2.设置启动参数
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.23.110',
-> MASTER_USER='root',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154,
-> master_port=3306;
3.刷新一下配置
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
开启同步
mysql> START SLAVE;
# 查看启动状态
mysql> show slave status\G; #或者show slave status;
注意这俩值都为 Yes时 则为成功
Slave_IO_Running: Yes # io线程 此线程为NO 则可能是 数据过大等原因
Slave_SQL_Running: Yes # sql 线程 此线程为NO 就是SQL 同步异常 根据Position 参数解决
然后我一台从机器:192.168.23.112一只报错,按照网上的重新加载mysql库中的那几张表也不行
如果报错:
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.23.110',
-> MASTER_USER='root',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=154,
-> master_port=3306;
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.
可以在/etc/my.cnf中添加
[root@localhost support-files]# cat /etc/my.cnf
[mysqld]
bind-address=0.0.0.0
port=3306
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql/mysql.log
pid-file=/usr/local/mysql/mysql.pid
symbolic-links=0
#skip-grant-tables
log-bin=mysql-bin
server-id=3
然后重启数据库:systemctl restart mysql
重新登录数据库:mysql -u root -p
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.23.110',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_USER='root',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_POS=154,
-> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
成功!
然后查看一下:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.23.110
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes #这两个为Yes就行了
Slave_SQL_Running: Yes #这两个为Yes就行了
......
1 row in set (0.00 sec)
ERROR:
No query specified