一、MySQL主备基本原理
1.如下图所示是基本的主备切换流程
MySQL主备切换流程
在状态
1
中，客户端的读写都直接访问节点
A
，而节点
B
是
A
的备库，只是将
A
的更新都同步过来，到本地执行。这样可以保持节点B
和
A
的数据是相同的。
当需要切换的时候，就切成状态2
。这时候客户端读写访问的都是节点
B
，而节点
A
是
B
的备库。
在状态
1
中，虽然节点
B
没有被直接访问，但是依然建议把节点
B
（也就是备库）设置成只读 （readonly
）模式。这样做，有以下几个考虑：
- 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
- 防止切换逻辑有bug，比如切换过程中出现双写，造成主备不一致；
- 可以用readonly状态，来判断节点的角色。
把备库设置成只读了，还怎么跟主库保持同步更新呢？
因为
readonly
设置对超级
(super)
权限用户是无效的，而用于同步更新的 线程，就拥有超级权限。
2.节点A到节点B这条线的内部流程是什么样的？
如下图所示就是执行一个update语句在节点A执行，然后同步到节点B的完整流程图。
主备流程图
备库B跟主库A之间维持了一个长连接。主库A内部有一个线程，专门用于服务备库B的这个长连接。
一个事务日志同步的完整过程是这样的：
- 在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量
- 在备库B上执行start slave命令，这时候备库会启动两个线程，就是图中的io_thread和 sql_thread。其中io_thread负责与主库建立连接。
- 主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。
- 备库B拿到binlog后，写到本地文件，称为中转日志（relay log）。
- sql_thread读取中转日志，解析出日志里的命令，并执行。
这里需要说明，后来由于多线程复制方案的引入，sql_thread演化成为了多个线程。
二、配置主从同步的基本步骤
有很多种配置主从同步的方法，可以总结为如下的步骤：
- 在主服务器上，必须开启二进制日志机制和配置一个独立的ID
- 在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
- 在开始复制进程前，在主服务器上记录二进制文件的位置信息
- 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
- 配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置
三、详细配置主从同步的方法
环境准备
CentOS7
mysql安装包：mysql8.0.12_bin_centos7.tar.gz
安装前主备，执行后重启系统：
[root@hdss7-11 ~]# systemctl stop firewalld
[root@hdss7-11 ~]# systemctl disable firewalld
[root@hdss7-11 ~]# setenforce 0
[root@hdss7-11 ~]# sed -ir '/^SELINUX=/s/=.+/=disabled/' /etc/selinux/config3.1备份主服务器原有数据到从服务器
mysqldump -uroot -p --all-databases --lock-all-tables > ~/master_db.sql说明：
- -u ：用户名
- -p ：示密码
- --all-databases ：导出所有数据库
- --lock-all-tables ：执行操作时锁住所有表，防止操作时有数据修改
- ~/master_db.sql :导出的备份数据（sql文件）位置，可自己指定
3.2 在从服务器上进行数据还原
mysql> source /root/master_db.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
...
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| seckilling |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.00 sec)
3.3主服务器配置
3.3.1 在配置文件/data/mysql/conf/my.cnf中，修改如下几个字段
[mysqld]
server_id = 1 #服务器 id ，主从机器在同一局域网内必须全局唯一，不能相同
log-bin = /data/mysql/binlog/binlog
log_bin_index = /data/mysql/binlog/binlog.index
binlog-do-db =palan-dev #待同步的数据库，如果有多个以空格隔开db1 db2 db3 ....
binlog-ignore-db =mysql #不同步的数据 如果有多个以空格隔开db1 db2 db3 ....3.3.2查看主服务器是否开启binlog日志，如果没有开启则开启
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/binlog/binlog |
| log_bin_index | /data/mysql/binlog/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------+
6 rows in set (0.00 sec)
查看生成的binlog日志文件
[root@wxc wxc]# ls /data/mysql/binlog/ -al
total 2388
drwxr-xr-x. 2 mysql mysql 194 Nov 15 19:16 .
drwxr-xr-x. 7 mysql mysql 66 Nov 15 16:09 ..
-rw-r-----. 1 mysql mysql 156 Nov 15 17:18 binlog.000001
-rw-r-----. 1 mysql mysql 170 Nov 15 17:18 binlog.000002
-rw-r-----. 1 mysql mysql 1775 Nov 15 17:40 binlog.000003
-rw-r-----. 1 mysql mysql 1122 Nov 15 18:25 binlog.000004
-rw-r-----. 1 mysql mysql 2409901 Nov 15 19:10 binlog.000005
-rw-r-----. 1 mysql mysql 231 Nov 15 19:10 binlog.000006
-rw-r-----. 1 mysql mysql 210 Nov 15 19:10 binlog.000007
-rw-r----- 1 mysql mysql 191 Nov 15 19:16 binlog.000008
-rw-r----- 1 mysql mysql 264 Nov 15 19:16 binlog.index
3.3.3给从库授权账号，让从库可以复制
mysql> CREATE USER 'rootslave'@'10.4.7.139' IDENTIFIED WITH mysql_native_password BY 'qazWSX123+++';
mysql> grant replication slave on *.* to 'rootslave'@'10.4.7.139';
mysql> FLUSH PRIVILEGES;重新启动MySQL
3.3.4查看主库状态
mysql> show master status\g;
+---------------+----------+--------------+------------------+--------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+--------------------------------------------+
| binlog.000008 | 191 | | | 7fe7893e-2723-11eb-bd3b-000c2957e9b2:1-150 |
+---------------+----------+--------------+------------------+--------------------------------------------+
1 row in set (0.01 sec)
ERROR:
No query specified
3.4从服务器配置
3.4.1 在配置文件/data/mysql/conf/my.cnf中，修改如下几个字段
server_id = 2 #这里的id一定不要和主库id相同重新启动数据库systemctl restart mysqld
3.4.2 登录数据库
mysql> change master to master_host='10.4.7.138',master_user='rootslave',master_password='qazWSX123+++',master_log_file='binlog.000005',master_log_pos=191;
mysql> start slave;3.4.3查看从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.138
Master_User: rootslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000008
Read_Master_Log_Pos: 191
Relay_Log_File: wxc-relay-bin.000011
Relay_Log_Pos: 391
Relay_Master_Log_File: binlog.000008
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: 191
Relay_Log_Space: 829
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: 7fe7893e-2723-11eb-bd3b-000c2957e9b2
Master_Info_File: mysql.slave_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:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7fe7893e-2723-11eb-bd3b-000c2957e9b2:10-150
Executed_Gtid_Set: 7fe7893e-2723-11eb-bd3b-000c2957e9b2:1-7:10-150
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.10 sec)
其中Slave_IO_Running和Slave_SQL_Running表示两个线程的状态，这两个线程必须都为YES，如果有一个为no都不会进行主从同步
四、FAQ
4.1
mysql出现ERROR1698(28000):Access denied for user root@localhost错误解决方法
参考：mysql出现ERROR1698(28000):Access denied for user root@localhost错误解决方法 - 丹华抱一鷇音子 - 博客园
119
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
