mysql5.7传统主从同步原理以及实现
MySQL主从同步原理图如下:
从库生成两个线程,一个I/O线程,一个SQL线程; I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中; 主库会生成一个 log dump 线程,用来给从库I/O线程传binlog; SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;
环境配置 首先要安装mysqk5.7 而且两台数据库服务器mysql版本要一致
192.168.88.67 master rhel7 mysql5.7
192.168.88.68 slave rhel7 mysql5.7
配置 Master 192.168.88.67
[root@master ~]#cat >> /etc/my.cnf <<EOF log‐bin=mysql‐bin‐master #启用二进制日志 server‐id=1 #本机数据库ID 标示 binlog‐do‐db=test #可以被从服务器复制的库, 二进制需要同步的数据库名 binlog‐ignore‐db=mysql #不可以被从服务器复制的库 lower_case_table_names=1 EOF [root@master mysql]# service mysqld restart [root@master mysql]# mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ mysql> CREATE DATABASE test; #创建与配置文件可以允许被复制的数据库相对应 Query OK, 1 row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE TABLE666 (bTypeId int,bName char(16),price int,publishing char(16)); Query OK, 0 rows affected (0.01 sec) mysql> show master status; +-------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-master.000003 | 842 | test | mysql | | +-------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> grant replication slave on *.* to slave@"192.168.88.%" identified by "GuangZhou_123"; #授权让从库可以连接 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>exit [root@master mysql]# cd /var/lib/mysql [root@master mysql]# mysqldump -uroot -p1 -B test > test.sql #导出数据库 [root@master mysql]# scp test.sql 192.168.88.68:/root #传给从服务器
配置 Slave 192.168.88.68
[root@slvae ~]#cat >> /etc/my.cnf <<EOF server-id=2 lower_case_table_names=1 EOF [root@slave ~]# service mysqld restart [root@slave ~]#mysql -uslave -p -h 192.168.88.67 #尝试在从库远程连接主库 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | +--------------------+ 1 row in set (0.00 sec) mysql>exit [root@slave ~]# mysql -uroot -p < test.sql 向数据库导入testdb数据库 [root@slave ~]#mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | table666 | +------------------+ 1 row in set (0.00 sec) 然后建立主从连接 mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> change master to master_host='192.168.88.67',master_user='slave',master_password='GuangZhou_123'; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.88.67 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: 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: 0 Relay_Log_Space: 154 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: 1045 Last_IO_Error: error connecting to master 'slave@192.168.88.67:3306' - retry-time: 60 retries: 1 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 221121 16:21:00 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) 为此主从同步搭建完成!
有可能出现的问题1
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.88.67 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Connecting Slave_SQL_Running: Yes 如果IO线程显示的是正在连接中,很可能是从数据库无法通过slave去连接主数据库,可以尝试通过命令去连接主数据库是否成功 [root@slave ~]#mysql -uslave -p -h 192.168.88.67 ERROR 1045 (28000): Access denied for user 'slav'@'192.168.88.68' (using password: YES) 如果无法连接成功,可以去修改主数据库的授权信息,进行再次确认授权 mysql>stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>start slave; Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.88.67 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: Yes
有可能出现的问题2
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.88.67 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: No mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.88.67 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: Read_Master_Log_Pos: 4 Relay_Log_File: slave-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: Slave_IO_Running: Yes Slave_SQL_Running: Yes 如果还不行重复多几次就可以了
测试传统主从同步
在主库中添加信息 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('1','Linux','66','DZ'); NTO table666(bTypeId,bName,price,publishing) VALUES('4','MySQL1','71','QH'); INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('5','MySQL2','72','QH'); INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('6','MySQL3','73','QH');Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('2','CLD','68','RM'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('3','SYS','90','JX'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('4','MySQL1','71','QH'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('5','MySQL2','72','QH'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO table666(bTypeId,bName,price,publishing) VALUES('6','MySQL3','73','QH'); Query OK, 1 row affected (0.00 sec) mysql> select * from table666; +---------+--------+-------+------------+ | bTypeId | bName | price | publishing | +---------+--------+-------+------------+ | 1 | Linux | 66 | DZ | | 2 | CLD | 68 | RM | | 3 | SYS | 90 | JX | | 4 | MySQL1 | 71 | QH | | 5 | MySQL2 | 72 | QH | | 6 | MySQL3 | 73 | QH | +---------+--------+-------+------------+ 6 rows in set (0.00 sec) 在从库中查看 mysql> ues test; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) mysql> show tables; +------------------+ | Tables_in_test | +------------------+ | table666 | +------------------+ 1 row in set (0.00 sec) mysql> select * from table666; +---------+--------+-------+------------+ | bTypeId | bName | price | publishing | +---------+--------+-------+------------+ | 1 | Linux | 66 | DZ | | 2 | CLD | 68 | RM | | 3 | SYS | 90 | JX | | 4 | MySQL1 | 71 | QH | | 5 | MySQL2 | 72 | QH | | 6 | MySQL3 | 73 | QH | +---------+--------+-------+------------+ 6 rows in set (0.00 sec) 传统主从测试成功!