1.了解SQL的执行过程2.Docker下搭建MySQL&查看BinLog文件3.MySQL中涉及的锁4.MySQL数据库设计-字段类型
前言
提示:以下是本篇文章正文内容
一、主备什么,为什么要用主备?
主备通俗的来说,就是数据复制。建立一个备库,和主数据库一模一样的数据库环境。
那主备能给我们做什么呢?
- 准实时备份,保证数据的完整性。当主宕机后,可以进行快速切换。
- 做读写分离,将读写压力分散。
- 进行业务扩展。
二、主备的流程
原理:是基于二进制日志(bin log)进行数据增量同步。
- Master 服务器会把数据变更产生的二进制日志(bin log)通过 Dump 线程发送给 Slave 服务器。
- Slave 服务器中的 I/O 线程负责接受二进制日志,并保存为中继日志(relay log)。
- Slave再起一个线程读取中继日志(relay log),并在Slave执行,完成同步。
各个日志的含义
三、在Docker下搭建主备环境
步骤一:新建一个备库
我们在之间的文章中已经在Docker中搭建了一个3306的数据环境,为了满足主备的需要。我们在搭建一个备库的环境。
docker run -p 3307:3306 --name mysql_slave -v /opt/mysql/slave/conf:/etc/mysql/conf.d -v /opt/mysql/slave/logs:/logs -v /opt/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
步骤二:查看是否安装成功
[root@iZ8vb7xgouhcfciakm9plqZ /]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8b3fe3977bed mysql "docker-entrypoint.s…" 7 seconds ago Up 5 seconds 33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp mysql_slave
f7e9a7e047ac mysql "docker-entrypoint.s…" 6 days ago Up 6 days 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp mysql
935090dd3061 apachephp:v1 "/usr/bin/supervisord" 8 days ago Up 8 days 0.0.0.0:8080->80/tcp, :::8080->80/tcp vigorous_borg
ff7f2bea7cc3 e121d5f99e1e "/usr/bin/supervisord" 8 days ago Up 8 days 80/tcp apache
没什么问题,确定了有两个端口的环境,3306和3307.
步骤三:查看是不是得到了不同的IP
[root@iZ8vb7xgouhcfciakm9plqZ /]# docker inspect --format='{{.NetworkSettings.IPAddress}}' f7e9a7e047ac
172.17.0.4
[root@iZ8vb7xgouhcfciakm9plqZ /]# docker inspect --format='{{.NetworkSettings.IPAddress}}' 8b3fe3977bed
172.17.0.5
已经分配了 两个Ip,Master 服务器地址是 172.17.0.4 ,Slave 服务器地址是 172.17.0.5,MySQL 的端口号都为 3306。
步骤四:进行主环境的my.cnf文件配置修改
进入Bash
[root@iZ8vb7xgouhcfciakm9plqZ /]# docker exec -it f7e9a7e047ac /bin/bash
root@f7e9a7e047ac:/# mysql -uroot -p
修改my.cnf
[mysqld]
## 设置server_id,一般设置为IP,同一局域网内注意要唯一
server_id=100
## 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
binlog-ignore-db=mysql
## 开启二进制日志功能,可以随便取,最好有含义(关键就是这里了)
log-bin=edu-mysql-bin
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
root@8b3fe3977bed:/etc/mysql# vi my.cnf
bash: vi: command not found
apt-get update
apt-get install vim
步骤五:创建一个salve的用户
创建数据同步用户,为了让备库来监听
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
步骤六:进入备库进行,相似的配置修改
同样的步骤操作slave
修改my.cnf配置
[mysqld]
## 设置server_id,一般设置为IP,注意要唯一
server_id=101
## 复制过滤:也就是指定哪个数据库不用同步(mysql库一般不同步)
binlog-ignore-db=mysql
## 开启二进制日志功能,以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin
## 为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式(mixed,statement,row,默认格式是statement)
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1
结束后重启容器.
步骤七:主备建立关系(核心步骤)
核心步骤成败在此一举了
Master:
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| edu-mysql-bin.000001 | 711 | | mysql | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
看一下File和Position的值。
!!!重点
我们刚刚查询到了IP用上了。在从节点上执行
mysql> change master to master_host='172.17.0.4', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=711, master_connect_retry=30;
Query OK, 0 rows affected, 10 warnings (0.04 sec)
解释
master_host: Master 的IP地址
master_user: 在 Master 中授权的用于数据同步的用户
master_password: 同步数据的用户的密码
master_port: Master 的数据库的端口号
master_log_file: 指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值
master_log_pos: 从哪个 Position 开始读,即上文中提到的 Position 字段的值
master_connect_retry: 当重新建立主从连接时,如果连接失败,重试的时间间隔,单位是秒,默认是60秒。
成功
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.4
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000003
Read_Master_Log_Pos: 323
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 328
Relay_Master_Log_File: edu-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: 323
Relay_Log_Space: 541
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: 100
Master_UUID: eb9ceac6-ca92-11eb-8f75-0242ac110004
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:
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.00 sec)
遇到问题
- Slave_IO_State:为空,可能是slave未执行start slave;
- 重置Matser操作
- 停止已经启动的绑定,stop slave
- 重置绑定,reset master
- 执行复制主机命令,change master to master_host …
- 异常:Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
Slave_IO_State: Connecting to master
Master_Host: 172.17.0.4
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: edu-mysql-bin.000001
Read_Master_Log_Pos: 711
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: edu-mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Error: error connecting to master 'slave@172.17.0.4:3306' - retry-time: 30 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
解决方式:
错误原因:密码加密方式不支持,
在主服务器执行以下命令:
ALTER USER ‘slave’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123456’;
FLUSH PRIVILEGES;
四、主备数据不一致,怎么办?
利用GTID模式强化数据库的主备一致
配置:
-- 是否开启GTID复制功能
gtid_mode = on
-- 启动强制GTID的一致性,自动根据GTID进行同步数据,无需查找File和Pos点
enforce_gtid_consistency = 1
-- 5.7.6以上中默认TRUE
binlog_gtid_simple_recovery = 1
-- 默认开启,启动自动relay log恢复
relay_log_recovery = ON
-- 将master.info保存在表中
master_info_repository = TABLE
-- 将relay.info保存在表中
relay_log_info_repository = TABLE
五、延迟优化
主备服务器时间,可能会因为某个事务,在数据库执行时间过长,而出现了数据延迟,我们在进行数据操作的时候,尽量避免大事务的产生,将大事务切分为小事务去执行。
我们可以升级MySQl的版本,在大于5.6之后的版本中,采用了并行复制,并行的操作,很大程度上提高了性能,减小延迟。
再加上一个延迟监控的保险,建立心跳表,去监听主从延迟的时间。
六、进行高可用架构的设计
1. 高可用设计主要分为了两种
1. 无状态,只需要进行故障转移或者剔除,常见的有NGINX
2. 有状态,我们需要从业务和数据维度去考虑
2.数据核对
高可用的设计,并不能保证万无一失,在最后我们需要去做一些兜底的操作,数据核对就很关键了
- 在业务上,开发需要去做数据对账,比如,销售情况和总数是否相等
- 在数据上,DBA可以做数据抽样对比,来看是否一致