MySQL主备的基本原理
在状态1中，客户端的读写都直接访问节点A，而节点B是A的备库，只是将A的更新都同步过来，到本地执行。这样可以保持节点B和A的数据是相同的。当需要切换的时候，就切成状态2。这时候客户端读写访问的都是节点B，而节点A是B的备库
在状态1中，虽然节点B没有被直接访问，但是建议把备库节点B，设置成只读模式。有以下几个原因：
1.有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作
2.防止切换逻辑有bug
3.可以用readonly状态，来判断节点的角色
把备库设置成只读，还怎么跟主库保持同步更新？
readonly设置对超级权限用户是无效的，而用于同步更新的线程，就拥有超级权限
下图是一个update语句在节点A执行，然后同步到节点B的完整流程图：
备库B和主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。一个事务日志同步的完整过程如下：
1.在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量
2.在备库B上执行start slave命令，这时备库会启动两个线程，就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接
3.主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B
4.备库B拿到binlog后，写到本地文件，称为中转日志
5.sql_thread读取中转日志，解析出日志里的命令，并执行
由于多线程复制方案的引入，sql_thread演化成了多个线程
本章环境：
系统：CentOS Linux release 7.9.2009 (Core)
| 主机IP | hostname | 标识 |
|---|---|---|
| 10.10.220.17 | mysql-server | master |
| 10.10.220.18 | mysql-slave | slave |
安装前准备：
mysql-server mysql-slave 同下配置
关闭防火墙 关闭selinux
[root@mysql-server ~]# systemctl stop firewalld.service
[root@mysql-server ~]# systemctl distable firewalld.service
[root@mysql-server ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@mysql-server ~]# setenforce 0
这里不在写mysql8.0安装教程，具体请参考：
https://blog.csdn.net/weixin_43713949/article/details/119450743?spm=1001.2014.3001.5501
Master配置
安装好mysql后我们直接进入/etc/my.cnf配置文件插入如下字段：
log-bin=mysql-bin
binlog_format=mixed server-id=1 #服务器唯一性标识符，每台服务器配置必须保存不一样
read-only=0
binlog-do-db=test_db #需要备份的那个数据库名叫 “test_db”（可选）
auto-increment-increment=2 #这里设置用来台服务器来做备份，按个人情况定
auto-increment-offset=1 #表示这台服务器序号，从1开始，不超auto-increment-increment
修改完成后master于slave 重启mysql
[root@mysql-slave ~]# systemctl restart mysqld
随后我们登陆mysql-server 创建一个用于备份的用户
mysql> CREATE USER 'perfma_cp'@'10.10.220.17' IDENTIFIED WITH mysql_native_password BY 'Linux@112';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'perfma_cp'@'10.10.220.17';
Query OK, 0 rows affected (0.01 sec)
创建成功后我们输入select user, host from user; 查看下所创建用户的host权限是否为 % 如果不为% io同步时会报错，输入update user set host = ‘%’ where user = ‘perfma_cp’; 更新host权限为% 更新完成后重启mysql
mysql> select user, host from user;
+------------------+--------------+
| user | host |
+------------------+--------------+
| root | % |
| test | % |
| perfma_cp | 10.10.220.17 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
+------------------+--------------+
8 rows in set (0.00 sec)
mysql> update user set host = '%' where user = 'perfma_cp';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user, host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| perfma_cp | % |
| root | % |
| test | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
+------------------+-----------+
8 rows in set (0.00 sec)
执行 show master status；(看到下面信息)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 156 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql-bin.000001和156这两个值需要记得后面有用
Slave配置
mysql-slave的my.cnf配置
log-bin=mysql-bin
binlog_format=mixed server-id=2 #//服务器唯一性标识符，每台服务器配置必须保存不一样
replicate-do-db=test_db #//要同步的数据库名
relay-log=mysql.relay.bin
log-slave-updates=ON
重启mysql 登陆mysql执行如下语句：
[root@mysql-slave ~]# mysql -uroot -p'Linux@112'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.10.220.17',
-> MASTER_USER='perfma_cp',
-> MASTER_PASSWORD='Linux@112',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=156;
Query OK, 0 rows affected, 8 warnings (0.08 sec)
mysql> quit
Bye
[root@mysql-slave ~]# systemctl restart mysqld
含义解释 CHANGE MASTER TO
-> MASTER_HOST=‘mysql主的IP’,
-> MASTER_USER=‘mysql主所创建的用于备份的用户名’,
-> MASTER_PASSWORD=‘mysql主所创建的用于备份的用户密码’,
-> MASTER_LOG_FILE=‘mysql主show master status;执行后的File’,
-> MASTER_LOG_POS=mysql主show master status;执行后的Position;
重启mysql后再次登陆mysql-slave 执行show slave status\G;
看到如下两项为yes则表示配置成功了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.10.220.17
Master_User: perfma_cp
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 156
Relay_Log_File: mysql.relay
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-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: 156
Relay_Log_Space: 7080
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
Master_UUID: 25340262-018f-11ec-a1c9-aa503bb6b602
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
测试：
在master和slave服务器上登陆MySQL运行如下脚本创建数据库test_db;
mysql> CREATE DATABASE IF NOT EXISTS test_db default charset utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 2 warnings (0.01 sec)
单独在master服务器上创建表，并插入数据
mysql> USE test_db;
Database changed
mysql> CREATE TABLE user(
-> id int not null auto_increment,
-> user_name VARCHAR(50),
-> password VARCHAR(10) ,
-> name VARCHAR(50),
-> status VARCHAR(10) ,
-> constraint pk__person primary key(id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO user (`user_name`,`password`,`name`,`status`) VALUES('admin','admin','admin','1');
Query OK, 1 row affected (0.04 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from user;
+----+-----------+----------+-------+--------+
| id | user_name | password | name | status |
+----+-----------+----------+-------+--------+
| 1 | admin | admin | admin | 1 |
+----+-----------+----------+-------+--------+
1 rows in set (0.00 sec)
到slave服务器上test_db中查看是否同步了相同的表和数据
已同步，则配置master—>slave 的主从复制完成
搭建 B—>A 的主从复制
操作步骤
实际就是步骤一的逆向操作。将slave（10.10.220.18）作为主服务器，master（10.10.220.17）作为从服务器。步骤基本和上面一样。 其中 master、slave服务器的\etc\my.cnf配置文件 继续追加 主从配置内容即可。
在slave服务器中创建备份用户为perfma_cp2
mysql> CREATE USER 'perfma_cp2'@'10.10.220.18' IDENTIFIED WITH mysql_native_password BY 'Linux@112';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'perfma_cp2'@'10.10.220.18';
Query OK, 0 rows affected (0.00 sec)
修改账户权限
mysql> use mysql;
Database changed
mysql> select user, host from user;
+------------------+--------------+
| user | host |
+------------------+--------------+
| perfma_cp2 | 10.10.220.18 |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+--------------+
5 rows in set (0.00 sec)
mysql> update user set host = '%' where user = 'perfma_cp2'；
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user, host from user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| perfma_cp2 | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
打开 /etc/my.cnf ， 开启slave服务器的binarylog：
新增内容如下：
重启mysql
不需要导出slave的初态同步到master上了，因为master和slave的初态是一样的（步骤同一实现的），在slave上查看master日志状态。
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 2818
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
在到master服务器开启中继relay_log
/etc/my.cnf新增如下内容:
重启mysql
在master上开启同步
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.10.220.18',
-> MASTER_USER='perfma_cp2',
-> MASTER_PASSWORD='Linux@112',
-> MASTER_LOG_FILE='mysql-bin.000005',
-> MASTER_LOG_POS=2818;
Query OK, 0 rows affected, 8 warnings (0.04 sec)
host为B的IP地址，user、password是在B上创建的备份用户，log_file、log_pos是在B上看到的master状态信息。
在master机器上查看slave信息
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.10.220.18
Master_User: perfma_cp2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 156
Relay_Log_File: mysql.relay
Relay_Log_Pos: 324
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test_db2
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 2
Exec_Master_Log_Pos: 156
Relay_Log_Space: 522
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: 2
Master_UUID: ec9c5527-42d1-11ec-b0ce-faa4afccb01a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
如果IO进程和SQL进程都为YES，说明从B到A的同步成功。
即:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试
在master、slave两台服务器中任意一台的MySQL test_db2中添加数据另外一台都自动同步。
slave：
mysql> INSERT INTO user (`user_name`,`password`,`name`,`status`) VALUES('admin','admin','admin','2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+-----------+----------+------+--------+
| id | user_name | password | name | status |
+----+-----------+----------+-------+--------+
| 1 | admin | admin | admin | 1 |
| 3 | admin | admin | admin | 2 |
+----+-----------+----------+-------+--------+
2 rows in set (0.00 sec)
master：
mysql> select * from user;
+----+-----------+----------+-------+--------+
| id | user_name | password | name | status |
+----+-----------+----------+-------+--------+
| 1 | admin | admin | admin | 1 |
| 3 | admin | admin | admin | 2 |
+----+-----------+----------+-------+--------+
2 rows in set (0.00 sec)
至此，MySQL双机热互备配置完毕。
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
