MySQL 应用实践（超长文预警）

最新推荐文章于 2023-11-29 21:36:57 发布

学渣角鹿白

最新推荐文章于 2023-11-29 21:36:57 发布

阅读量199

点赞数

分类专栏：【文档】文章标签： mysql mariadb innodb

本文链接：https://blog.csdn.net/a20251839/article/details/110407177

版权

【文档】专栏收录该内容

17 篇文章 0 订阅

订阅专栏

一：实验环境

1.1：概述

网络：
内部网络：172.16.1.0/24（仅主机模式）；
外部网络：192.168.1.0/24（桥接模式）；
服务器：
2 台 VMware Workstation 虚拟机（node103、node104）；
安装 CentOS 7.2（已经过系统初始化操作）；
yum 安装 MariaDB；
实验环境基础服务：
外网 DNS & NTP：192.168.1.250；
内网 DNS & NTP：172.16.1.250；

1.2：虚拟机配置

1.2.1：node103

eth0 网卡

[root@node103 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0 
BOOTPROTO="static"
NAME="eth0"
DEVICE="eth0"
ONBOOT="yes"
IPADDR="192.168.1.103"
PREFIX="24"
GATEWAY="192.168.1.1"
DNS1="192.168.1.250"

eth1 网卡

[root@node103 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth1
BOOTPROTO="static"
NAME="eth1"
DEVICE="eth1"
ONBOOT="yes"
IPADDR="172.16.1.103"
PREFIX="24"
DNS1="172.16.1.250"

crond 任务计划

[root@node103 ~]# crontab -l
*/30 * * * * /usr/sbin/ntpdate 172.16.1.250 && /usr/sbin/hwclock -w

1.2.2：node104

eth0 网卡

[root@node104 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0 
BOOTPROTO="static"
NAME="eth0"
DEVICE="eth0"
ONBOOT="yes"
IPADDR="192.168.1.104"
PREFIX="24"
GATEWAY="192.168.1.1"
DNS1="192.168.1.250"

eth1 网卡

[root@node104 ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth1
BOOTPROTO="static"
NAME="eth1"
DEVICE="eth1"
ONBOOT="yes"
IPADDR="172.16.1.104"
PREFIX="24"
DNS1="172.16.1.250"

crond 任务计划

[root@node104 ~]# crontab -l
*/30 * * * * /usr/sbin/ntpdate 172.16.1.250 && /usr/sbin/hwclock -w

二：MariaDB 安装配置

2.1：安装 MariaDB

两台服务器均使用 yum 安装 mariadb-server：

]# yum install mariadb-server -y

2.2：配置 MariaDB

备份配置文件：

]# cp /etc/my.cnf{,.bak}

2.2.1：node103 配置

编辑 /etc/my.cnf：

[root@node103 ~]# vim /etc/my.cnf               
[mysqld]
bind-address=172.16.1.103
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
max_connections=1024

innodb_file_per_table=1
innodb_buffer_pool_size=536870912
innodb_log_group_home_dir=/data/mysql_logs/
innodb_log_file_size=10485760

long_query_time=5
slow_query_log=1
slow_query_log_file=/data/mysql_logs/mysql-slow.log

server-id=103
log-bin=/data/mysql_logs/mysql-binlog
relay-log=/data/mysql_logs/mysql-relay

[mysqld_safe]
log-error=/data/mysql_logs/mysql-error.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

2.2.2：node104 配置

编辑 /etc/my.cnf：

[root@node104 ~]# vim /etc/my.cnf
[mysqld]
bind-address=172.16.1.104
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
max_connections=1024

innodb_file_per_table=1
innodb_buffer_pool_size=536870912
innodb_log_group_home_dir=/data/mysql_logs/
innodb_log_file_size=10485760

long_query_time=5
slow_query_log=1
slow_query_log_file=/data/mysql_logs/mysql-slow.log

server-id=104
log-bin=/data/mysql_logs/mysql-binlog
relay-log=/data/mysql_logs/mysql-relay

[mysqld_safe]
log-error=/data/mysql_logs/mysql-error.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

2.3：my.cnf 配置说明

2.3.1：MySQL 程序相关配置

bind-address=172.16.1.104
指定 mysql 监听地址；

数据调用只使用内网环境，所以 mysql 只监听内网地址；
socket=/var/lib/mysql/mysql.sock
指定 scoket 文件，保持默认；
user=mysql
指定 mysql 程序的启动用户；

默认应该也是 mysql 用户；
symbolic-links=0
禁用软链接；

MySQL 建议禁用此项，以抵御安全风险；
相比于禁用此项，开启软链接可以将数据文件移动至别处，在数据目录中创建相应的软链接来指向真实数据文件；
datadir=/data/mysql
自定义数据文件存放目录；
max_connections=1024
设置 MySQL 的最大并发连接数；

该选项默认值为 151；
MySQL 不是直接面向用户的，而是面向程序，所以无需过大；

2.3.2：InnoDB 相关配置

innodb_file_per_table=1
使用单独表空间；

即每张表的数据和索引都存储于自己专用的表空间文件中；
InnoDB 的很多高级特性都依赖于此参数，建议设定为 ON；
innodb_buffer_pool_size=536870912
InnoDB 的内存缓冲区大小；

如果是数据库专用服务器，该值尽量设得大一些；
虚拟机内存分配为 1G，这里分配给缓冲区的大小为 512M；
innodb_log_group_home_dir=/data/mysql_logs/
指定事务日志的保存路径；
innodb_log_file_size=10485760
每个事务日志文件的大小；

默认 5M，这里调整为 10M；
对于事务频繁的数据库，该值建议调得再大些，有利于提高 IO 效率；
但也不宜过大，因为在需要恢复数据时，会延长数据恢复时间；

2.3.3：慢日志相关配置

long_query_time=5
慢查询时长定义（即多长时间的查询会被认为是 “慢查询”）；

默认就是 5s；
根据业务查询复杂程度和慢日志实际记录情况合理调整；
slow_query_log=1
开启记录慢日志；
slow_query_log_file=/data/mysql_logs/mysql-slow.log
指定慢日志文件保存路径；

2.3.4：MySQL 复制相关配置

server-id=103
定义 MySQL 服务器 ID，每个节点保证不同；
log-bin=/data/mysql_logs/mysql-binlog
开启二进制日志记录，并指定二进制日志文件保存路径；

主从复制架构中，从节点需要关闭二进制日志；
relay-log=/data/mysql_logs/mysql-relay
开启中继日志，并指定中继日志文件保存路径；

主从复制架构中，主节点不需要开启中继日志；

2.3.5：mysqld_safe

log-error=/data/mysql_logs/mysql-error.log
开启错误日志记录，并指定错误日志保存路径；
pid-file=/var/run/mariadb/mariadb.pid
指定 pid 文件路径；

2.4：启动 mariadb

]# mkdir /data/{mysql,mysql_logs} -pv
]# chown mysql:mysql /data/mysql* -R

启动 mariadb 并设置为开机启动：

]# systemctl enable mariadb
]# systemctl start mariadb

初始化安全配置：

]# mysql_secure_installation

三：MySQL 参数查询验证

MySQL 有两类参数：VARIABLES（变量参数）和 STATUS（状态参数）；

每类参数的生效范围均为两种：SESSION（会话）和 GLOBAL（全局）；

查看参数的命令：SHOW [GLOBAL|SESSION] VARIABLES [LIKE 'variable_name']; 和 SHOW [GLOBAL|SESSION] STATUS [LIKE 'status_name'];

以下查看操作均为当前会话的变量参数配置（默认的会话变量参数会继承全局变量参数，除非存在运行时修改）；

3.1：存储引擎

3.1.1：查看当前默认存储引擎

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

当前默认存储因为为 InnoDB；

3.1.2：InnoDB 相关参数

InnoDB 表空间

测试步骤：

创建测试数据库，在库中创建两张表；
查看数据文件，每张表是否有单独的表空间文件；

查看单独表空间启用情况：

MariaDB [(none)]> SHOW VARIABLES LIKE '%per_table';  
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

创建测试数据库，并在其中创建两张表：

MariaDB [(none)]> CREATE DATABASE sample_db;
MariaDB [(none)]> USE sample_db;
MariaDB [sample_db]> CREATE TABLE tbl1(id INT UNSIGNED NOT NULL,name VARCHAR(100));
MariaDB [sample_db]> CREATE TABLE tbl2(id INT UNSIGNED NOT NULL,name VARCHAR(100));

查看每张表的数据文件是否为单独的表空间文件：

[root@node103 ~]# ll /data/mysql/sample_db/
total 412
-rw-rw---- 1 mysql mysql    65 Nov 25 10:17 db.opt
-rw-rw---- 1 mysql mysql  8586 Nov 25 10:31 tbl1.frm
-rw-rw---- 1 mysql mysql 98304 Nov 25 10:31 tbl1.ibd
-rw-rw---- 1 mysql mysql  8586 Nov 25 10:32 tbl2.frm
-rw-rw---- 1 mysql mysql 98304 Nov 25 10:32 tbl2.ibd

tbl1 和 tbl2 各有一个 frm 和 ibd 文件，这就是参数 innodb_log_file_size=1 的效果：

ibd：表空间文件；
frm：即 format，定义表结构；

InnoDB 缓冲区

查看 InnoDB 缓冲区大小：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+

为之前设定的 512 M；

查看缓冲区的区段（实例）数量：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+

默认为1；
如果物理内存比较大，可以将 buffer_pool 设为多个 instances，多个 instances 的大小总和为 innodb_buffer_pool_size 指定的内存大小；

InnoDB 事务日志

事务日志保存路径：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log_group_home_dir';
+---------------------------+-------------------+
| Variable_name             | Value             |
+---------------------------+-------------------+
| innodb_log_group_home_dir | /data/mysql_logs/ |
+---------------------------+-------------------+

查看每组事务日志的数量，以及每个事务日志的大小：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log_files_in_group';   
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log_file_size';     
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 10485760 |
+----------------------+----------+

每组默认为 2 个事务日志，且至少 2 个；
每个事务日志的大小为之前设定的 10M；

可以通过事务日志文件看出效果：
[root@node103 ~]# ll /data/mysql_logs/ib_logfile* -h
-rw-rw---- 1 mysql mysql 10M Nov 25 10:32 /data/mysql_logs/ib_logfile0
-rw-rw---- 1 mysql mysql 10M Nov 25 09:59 /data/mysql_logs/ib_logfile1
事务日志文件名为指定路径下的 ib_logfile*；

InnoDB 日志刷写机制

查看当前的事务提交后日志刷写机制：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

默认为 1；

该参数有三个值可供选择：

0：日志缓冲区每秒向日志文件同步一次，同时将日志文件中的事件同步到数据文件；
1：事务提交时，日志缓冲区向日志文件同步，同时将日志文件中的事件同步到数据文件；
2：事务提交时，日志缓冲区向日志文件同步，但日志文件中的事件不会即刻同步到数据文件，而是内核根据实际情况选择同步时机；

MySQL 的数据同步过程为：log buffer -> log file -> data file；

上述三个值的比较：

性能：2 > 1 > 0；
数据可靠性：0 > 1 > 2；

InnoDB IO 线程数量

查看文件读写的 IO 线程数量：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_%_io_threads';           
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+

默认均为 4；

一般调整得与 CPU 的核心数量相匹配；

InnoDB 打开文件数量上限

查看 InnoDB 可打开的文件数量上限：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_open_files';  
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 300   |
+-------------------+-------+

默认 300；

根据数据库规模及业务实际情况进行调整；

InnoDB 并发线程数量

查看并发线程数量限制：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+

默认为0，即不做限制；

InnoDB 分布式事务

查看分布式事务开启情况：

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_support_xa';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_support_xa | ON    |
+-------------------+-------+

默认开启；

3.1.3：事务隔离

查看当前的事务隔离级别：

MariaDB [(none)]> SHOW VARIABLES LIKE 'tx_isolation';        
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+

MariaDB InnoDB 的默认事务隔离级别为 REPEATABLE-READ；

事务隔离分为四个级别：

READ-UNCOMMITTED ，0，读未提交（导致脏读、不可重复读和幻读）；
READ-COMMITTED，1，读已提交（避免脏读；导致不可重复读和幻读）；
REPEATABLE-READ，2，可重读（避免脏读和不可重复读；导致幻读）；
SERIALIZABLE，3，串行化（串行化读，避免了脏读、不可重复读和幻读）；

隔离级别越高（0 -> 3），越能保证数据的完整性和一致性，但是对并发性能的影响也越大；

事务隔离不彻底（或事务并发）导致的三种情况：

脏读：一个事务在未提交的情况下，另一事务即可读取更改后的结果；

A 事务对数据做了增/删/改操作，还未提交；
B 事务对同一数据产生读操作，此时读到的是数据更改后的结果；
A 事务发生回滚；
B 事务读到和之前不一样的结果。导致 B 事务产生脏读；
不可重复读：

在 B 事务中，需要对同一条数据进行两次读取：
B 事务对这条数据进行第一次读取；
A 事务对同一数据进行了修改，并提交；
B 事务再次对这条数据进行读取时，和第一次读取的结果不同，导致同一事务对同一数据的不可重复读；
幻读：

B 事务对一定范围内的数据进行批量修改；
在 B 事务修改过程中，A 事务对这个范围内的其中一条数据做了修改；
这时就导致 B 事务的批量修改没有达到预定的结果，B 事务仿佛执行成功了，但又好像没成功；

3.2：MySQL 日志

3.2.1：日志存储方式

查看 MySQL 日志的存储方式：

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

默认为 FILE，即以日志文件的方式存储查询日志和慢日志；

日志存储方式分为：

FILE：日志文件；
TABLE：表；
二者可以同时存储；

3.2.2：查询日志

查询日志开启情况

查看查询日志开启情况：

MariaDB [(none)]> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

默认不开启查询日志；

查询日志文件路径

查看查询日志文件路径：

MariaDB [(none)]> SHOW VARIABLES LIKE 'general_log_file';
+------------------+-------------+
| Variable_name    | Value       |
+------------------+-------------+
| general_log_file | node103.log |
+------------------+-------------+

默认日志文件名为：server_id.log；

如果仅指定文件名，则相对路径指向的是 MySQL 的数据文件目录；
也可以指定绝对路径；

3.2.3：慢查询日志

慢查询日志开启情况

查看慢查询日志开启情况：

MariaDB [(none)]> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+

此参数还有一种表示方式：log_slow_queries

慢查询日志文件路径

查看慢日志文件路径：

MariaDB [(none)]> SHOW VARIABLES LIKE 'slow_query_log_file';    
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log_file | /data/mysql_logs/mysql-slow.log |
+---------------------+---------------------------------+

为配置文件中设置的 /data/mysql_logs/mysql-slow.log；

慢查询日志记录速率限制

查看慢查询日志记录速率限制：

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_slow_rate_limit';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| log_slow_rate_limit | 1     |
+---------------------+-------+

默认为 1；

限制慢日志记录速率的原因在于，如果数据库发生某种性能故障，导致此时很多查询操作都很慢，慢查询日志的记录频率很高，这时就没必要全都记录在慢查询日志中了；

慢查询时长定义

查看慢查询时长定义：

MariaDB [(none)]> SHOW VARIABLES LIKE 'long_query_time';   
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+

查询时间查过 5s，即定义为慢查询，记录到慢查询日志中；

3.2.4：错误日志

错误日志共记录以下几类信息（不仅仅是错误信息）：

mysql 程序运行过程中产生的错误信息；
mysql 程序的启动和关闭过程中输出的信息；
event scheduler（事件调度器）运行时产生的信息；
主从复制架构中，从服务器的复制线程（IO_THREAD、SQL_THREAD）启动时产生的日志；

错误日志文件路径

查看错误日志开启情况（错误日志文件路径）：

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_error';      
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | /data/mysql_logs/mysql-error.log |
+---------------+----------------------------------+

定义了日志文件路径即表示开启，关闭为 OFF；

警告信息的记录级别

警告信息也会记录在错误日志中，log_warnings 定义警告信息的记录级别；

查看警告信息的记录级别：

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 1     |
+---------------+-------+

默认为 1；
该参数不是布尔类型，不支持{ON|OFF}类型的定义；

log_warnings 值：

0：表示不记录警告信息；
1：表示记录警告信息；
也可以设置为大于 1，具体含义未深究；

3.2.5：二进制日志

二进制日志记录格式

查看当前的二进制日志记录格式：

MariaDB [(none)]> SHOW VARIABLES LIKE 'binlog_format';       
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+

默认为 STATEMENT；

二进制日志可以记录：

STATEMENT：引起数据改变和存在引起数据改变可能性的语句；
ROW：改变后的结果；
MIXED：也可以二者混合。

二进制日志开启情况

查看当前二进制日志是否开启：

MariaDB [(none)]> SHOW VARIABLES LIKE 'log_bin';      
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

配置文件中指定了二进制日志文件的路径，命令行中只能看到是否开启；

单个二进制日志文件的最大值

查看单个二进制日志文件的最大值：

MariaDB [(none)]> SHOW VARIABLES LIKE 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+

默认 1G，达到这个值会自然滚动；
某些操作会导致二进制日志文件强制滚动，比如 flush logs，mysql 重新启动等；

二进制日志刷写机制

查看当前二进制日志的刷写机制：

MariaDB [(none)]> SHOW VARIABLES LIKE 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+

默认为 0；

该参数是数值型：

0：表示 MySQL 本身不控制 binlog 的刷写，由内核自己控制从 log buffer 到 log file 的刷写；
1：表示 1 次事务刷写 1次；
n：表示 n 次事务刷写 1次；

设置为 0 的性能高，但不可靠；
设置为 1 最可靠，但性能损耗较大；
应该根据业务和系统负载情况选择一个合理的值，综合考虑数据一致性和性能；

二进制日志文件列表

查看当前已有的二进制日志文件列表：

MariaDB [(none)]> SHOW MASTER LOGS;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| mysql-binlog.000001 |     30361 |
| mysql-binlog.000002 |   1038814 |
| mysql-binlog.000003 |      1853 |
| mysql-binlog.000004 |       245 |
+---------------------+-----------+

MariaDB [(none)]> SHOW BINARY LOGS;
+---------------------+-----------+
| Log_name            | File_size |
+---------------------+-----------+
| mysql-binlog.000001 |     30361 |
| mysql-binlog.000002 |   1038814 |
| mysql-binlog.000003 |      1853 |
| mysql-binlog.000004 |       245 |
+---------------------+-----------+

正在使用的二进制日志文件

查看当前正在使用的二进制日志文件：

MariaDB [(none)]> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000004 |      245 |              |                  |
+---------------------+----------+--------------+------------------+

File 为当前正在使用的二进制日志文件；

Position 为最近一次事件的结束位置（Position 是以字节为单位的偏移量，记录每个事件的位置）；

Binlog_Do_DB 和 Binlog_Ignore_DB 是 “复制过滤器” 相关的参数，后续实验过程会提及；

查看二进制日志中的事件

查看 mysql-binlog.000003 中起始位置为 1483 的事件（操作）：

MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-binlog.000003' FROM 1483 LIMIT 1;   
+---------------------+------+------------+-----------+-------------+---------------------------+
| Log_name            | Pos  | Event_type | Server_id | End_log_pos | Info                      |
+---------------------+------+------------+-----------+-------------+---------------------------+
| mysql-binlog.000003 | 1483 | Query      |       103 |        1576 | CREATE DATABASE sample_db |
+---------------------+------+------------+-----------+-------------+---------------------------+

mysqlbinlog 命令

mysqlbinlog 命令是专用于查看 binlog 的：

使用 mysqlbinlog 查看和上面相同的事件：

[root@node103 ~]# mysqlbinlog /data/mysql_logs/mysql-binlog.000003 -j 1483 --stop-position=1576
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201125  9:59:09 server id 103  end_log_pos 245         Start: binlog v 4, server v 5.5.68-MariaDB created 201125  9:59:09 at startup
ROLLBACK/*!*/;
BINLOG '
bbq9Xw9nAAAA8QAAAPUAAAAAAAQANS41LjY4LU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABtur1fEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAMYjJEw==
'/*!*/;
# at 1483
#201125 10:17:49 server id 103  end_log_pos 1576        Query   thread_id=10    exec_time=0     error_code=0
SET TIMESTAMP=1606270669/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE sample_db
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog 的常用选项：

–start-datetime=“YYYY-MM-DD hh:mm:ss”
指定要查看的起始时间点；

–stop-datetime=“YYYY-MM-DD hh:mm:ss”
指定要查看的结束时间点；

-j, --start-position=INT
指定要查看的起始位置；

–stop-position=INT

指定要查看的起始位置；

3.2.6：中继日志

查看中继日志开启情况（中继日志文件路径）：

MariaDB [(none)]> SHOW VARIABLES LIKE 'relay_log';  
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| relay_log     | /data/mysql_logs/mysql-relay |
+---------------+------------------------------+

为配置文件中设置的路径；设置了路径即表示开启中继日志；

中继日志主要用于主从复制架构中，在从服务器上开启中继日志，记录主服务器的二进制日志文件同步过来的事件。

3.2.7：事务日志

相关查询操作已包含在 InnDB 事务日志中。

四：MySQL 主从实验

实验步骤：

搭建主从复制结构，node103 为 MASTER，node104 为 SLAVE；
搭建完成后查看两个节点的连接情况；
MASTER 节点做一些数据修改操作，验证 SLAVE 节点是否同步；
模拟 SLAVE 节点宕机并恢复后，主从复制的情况；
删除主从，以进行后续实验；

4.1：搭建 MySQL 主从

为达到初始状态，先清空node103 上之前的测试数据库：
MariaDB [(none)]> DROP DATABASE sample_db;

采用配置文件模块化的方式，以方便之后的复制结构更改；

4.1.1：MASTER 节点

编辑配置文件

注释 my.cnf 中的相关配置：

[root@node103 ~]# vim /etc/my.cnf
#server-id=103
#log-bin=/data/mysql_logs/mysql-binlog
#relay-log=/data/mysql_logs/mysql-relay

另起一个配置文件 repl.cnf，配置 MASTER 节点相关参数：

[root@node103 ~]# vim /etc/my.cnf.d/repl.cnf 
[mysqld]
server_id=103
log-bin=/data/mysql_logs/mysql-master
sync_binlog=1

MASTER 节点只需开启二进制日志；

sync_binlog=1，表示每次事务提交都刷写磁盘上的二进制日志文件，保证了 MASTER 节点上事务的完整性；但在事务繁忙时会损耗一定性能；

重启 mariadb

[root@node103 ~]# systemctl restart mariadb

授权复制数据的用户

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'172.16.1.%' IDENTIFIED BY '123456';

MariaDB [(none)]> FLUSH PRIVILEGES;

记录二进制日志位置

查看当前使用的二进制日志文件，以记录主从复制的起始位置：

MariaDB [(none)]> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-master.000001 |      245 |              |                  |
+---------------------+----------+--------------+------------------+

4.1.2：SLAVE 节点

编辑配置文件

注释 my.cnf 中的相关配置：

#server-id=104
#log-bin=/data/mysql_logs/mysql-binlog
#relay-log=/data/mysql_logs/mysql-relay

另起一个配置文件 repl.cnf，配置 SLAVE 节点相关参数：

[root@node104 ~]# vim /etc/my.cnf.d/repl.cnf 
[mysqld]
server_id=104
relay-log=/data/mysql_logs/mysql-slave
read_only=ON
sync_master_info=1
sync_relay_log_info=1

SLAVE 节点仅需开启中继日志；并且设为只读；

sync_master_info=1、sync_relay_log_info=1，表示每次事务提交都刷新数据目录下的 master.info 和 relay-log.info 文件，保证了主从节点事务的一致性；

重启 mariadb

[root@node104 ~]# systemctl restart mariadb

为 SLAVE 指定 MASTER

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.103',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master.000001',MASTER_LOG_POS=245;

启动复制线程

MariaDB [(none)]> START SLAVE;

4.1.3：查看主从建立情况

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: mysql-slave.000003
                Relay_Log_Pos: 532
        Relay_Master_Log_File: mysql-master.000001
             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: 245
              Relay_Log_Space: 1109
              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: 103

4.2：测试主从复制

4.2.1：数据添加同步测试

MASTER 节点创建数据库和表，添加相应数据；

MariaDB [(none)]> CREATE DATABASE sample_db;
MariaDB [(none)]> USE sample_db;
MariaDB [sample_db]> CREATE TABLE tbl1(id INT UNSIGNED NOT NULL, name VARCHAR(100));
MariaDB [sample_db]> INSERT tbl1 (id,name) VALUES (1,'user1'),(2,'user2'),(3,'user3');

MariaDB [sample_db]> select * from tbl1;                                  
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
+----+-------+

SLAVE 节点验证同步结果：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_db          |
+--------------------+

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
+----+-------+

SLAVE 节点查看同步状态：

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master.000001
          Read_Master_Log_Pos: 1026
               Relay_Log_File: mysql-slave.000003
                Relay_Log_Pos: 1313
        Relay_Master_Log_File: mysql-master.000001
             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: 1026
              Relay_Log_Space: 1890
              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: 103

当前同步到的位置为 mysql-master.000001 的 1026；

查看 MASTER 状态：

MariaDB [sample_db]> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-master.000001 |     1026 |              |                  |
+---------------------+----------+--------------+------------------+

和 SLAVE 显示的同步位置一致；

4.2.2：数据删除同步测试

MASTER 节点删除一条记录：

MariaDB [sample_db]> DELETE FROM tbl1 WHERE id=1;

SLAVE 节点验证：

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  2 | user2 |
|  3 | user3 |
+----+-------+

4.2.3：模拟 SLAVE 宕机

关闭 SLAVE 节点的 mariadb：

[root@node104 ~]# systemctl stop mariadb

MASTER 节点添加数据：

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (4,'user4'),(5,'user5');

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
+----+-------+

启动 SLAVE 节点的 mariadb：

[root@node104 ~]# systemctl start mariadb

查看复制进程状态：

MariaDB [(none)]> SHOW SLAVE STATUS\G
……
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
……

验证数据同步：

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
+----+-------+

说明 SLAVE 节点的复制线程是随 mysql 进程启动的；

如果 SLAVE 宕机时间过长，恢复后会有很多数据等待同步，为加快恢复速度，可以将 MASTER 节点的数据进行备份（备份同时 flush-logs 重新建立一个二进制日志文件，以便记录同步位置），将备份恢复到 SLAVE 节点后，重新建立主从关系；

4.3：MySQL 半同步

MySQL 半同步是建立在主从复制基础上的，主从复制是异步，通过启用半同步插件来实现半同步；

MySQL 复制的同步类型有三种：

异步
主节点本地写入成功即返回 “成功”；

半同步
主节点将请求发给从节点，确认从节点已接收请求后即返回 “成功”；

MySQL的半同步是指：只有一个从节点是同步的，其它从节点是异步的；

同步
主节点收到写入请求，同步给从节点，待确认从节点写入完成后才返回 “成功”；

4.3.1：安装半同步复制插件

semisync_master.so，主节点安装；

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

semisync_slave.so，从节点安装；

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

4.3.2：启用半同步复制

MASTER 节点

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

SLAVE 节点

MariaDB [(none)]> SET GLOBAL rpl_semi_sync_slave_enabled=ON;

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

如果从节点的复制线程在启用半同步复制之前是启动的，需要重启其中的 IO_THREAD：

MariaDB [(none)]> STOP SLAVE IO_THREAD;
MariaDB [(none)]> START SLAVE IO_THREAD;

4.3.3：核实半同步连接

MASTER 节点查看半同步复制的 SLAVE 节点是否已连接：

MariaDB [(none)]> SHOW STATUS LIKE 'rpl_semi_sync_master_clients';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_master_clients | 1     |
+------------------------------+-------+

显示已有一个 SLAVE 连接进来；

4.4：删除 MySQL 主从

删除主从，以继续后续实验。

停止 SLAVE 复制线程

MariaDB [(none)]> STOP SLAVE;

重置 SLAVE 节点

MariaDB [(none)]> RESET SLAVE;

重置 MASTER 节点

MariaDB [(none)]> RESET MASTER;

重置 MASTER 后，会创建一个序号为 000001 的空的二进制日志文件：

MariaDB [(none)]> SHOW MASTER STATUS;         
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-master.000001 |      245 |              |                  |
+---------------------+----------+--------------+------------------+

五：MySQL 双主实验

实验步骤：

搭建 MySQL 双主复制结构，node103 为 MASTER1，node104 为 MASTER2；
搭建完成后验证两个节点的连接情况；
MASTER1 创建测试数据库，并授权给测试用户，MASTER2 使用测试用户连入 MySQL，查看相关数据是否同步正常；
MASTER2 删除数据，MASTER1 查看是否同步；
前端部署 HAProxy 代理两个 MySQL 节点，分别在两个节点上添加一些数据，用前端代理 IP 连入 MySQL，查看数据是否完整；
删除 MySQL 双主，以进行后续实验；

5.1：搭建 MySQL 双主

5.1.1：MASTER1 节点

编辑配置文件

编辑 repl.cnf：

[root@node103 ~]# vim /etc/my.cnf.d/repl.cnf 
[mysqld]
server_id=103
log-bin=/data/mysql_logs/mysql-master1
relay-log=/data/mysql_logs/mysql-slave1
sync_binlog=1
sync_master_info=1
sync_relay_log_info=1

重启 mariadb

[root@node103 ~]# systemctl restart mariadb

授权复制数据的用户

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'172.16.1.%' IDENTIFIED BY '123456';

MariaDB [(none)]> FLUSH PRIVILEGES;

记录二进制日志位置

查看当前使用的二进制日志文件，以记录主从复制的起始位置：

MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-master1.000001 |      490 |              |                  |
+----------------------+----------+--------------+------------------+

5.1.2：MASTER2 节点

编辑配置文件

编辑 repl.cnf：

[root@node104 ~]# vim /etc/my.cnf.d/repl.cnf 
[mysqld]
server_id=104
log-bin=/data/mysql_logs/mysql-master2
relay-log=/data/mysql_logs/mysql-slave2
sync_binlog=1
sync_master_info=1
sync_relay_log_info=1

重启 mariadb

[root@node104 ~]# systemctl restart mariadb

授权复制数据的用户

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'172.16.1.%' IDENTIFIED BY '123456';

MariaDB [(none)]> FLUSH PRIVILEGES;

记录二进制日志位置

查看当前使用的二进制日志文件，以记录主从复制的起始位置：

MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-master2.000001 |      490 |              |                  |
+----------------------+----------+--------------+------------------+

5.1.3：配置互为主从

MASTER1

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.104',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master2.000001',MASTER_LOG_POS=490;

MariaDB [(none)]> START SLAVE;

MASTER2

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.103',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master1.000001',MASTER_LOG_POS=490;

MariaDB [(none)]> START SLAVE;

5.1.4：查看主从建立情况

MASTER1

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.104
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master2.000001
          Read_Master_Log_Pos: 490
               Relay_Log_File: mysql-slave1.000002
                Relay_Log_Pos: 533
        Relay_Master_Log_File: mysql-master2.000001
             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: 490
              Relay_Log_Space: 824
              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: 104

MASTER2

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master1.000001
          Read_Master_Log_Pos: 490
               Relay_Log_File: mysql-slave2.000002
                Relay_Log_Pos: 533
        Relay_Master_Log_File: mysql-master1.000001
             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: 490
              Relay_Log_Space: 824
              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: 103

5.2：测试双主复制

5.2.1：MASTER1 数据添加测试

MASTER1 创建测试数据库，并授权给测试用户：

MariaDB [(none)]> CREATE DATABASE sample_db;
MariaDB [(none)]> GRANT ALL ON sample_db.* TO 'sample'@'%' IDENTIFIED BY '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;

MASTER1 在测试数据库创建表，并插入数据：

MariaDB [sample_db]> CREATE TABLE tbl1(id INT UNSIGNED NOT NULL, name VARCHAR(100));
MariaDB [sample_db]> INSERT INTO tbl1 (id,name) VALUES (1,'user1'),(2,'user2');

MASTER2 用测试用户登录，查看数据：

[root@node104 ~]# mysql -usample -p
Enter password: 

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+

5.2.2：MASTER2 数据删除测试

MASTER2 删除一条数据：

MariaDB [(none)]> DELETE FROM sample_db.tbl1 WHERE id=1;

MASTER1 验证数据同步：

MariaDB [sample_db]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  2 | user2 |
+----+-------+

5.3：搭建 HAProxy 代理

使用 node102 作为 HAProxy 代理；

为了方便，直接使用 yum 安装 HAProxy；

5.3.1：安装 HAProxy

[root@node102 ~]# yum install haproxy -y

5.3.2：配置 MySQL 代理

[root@node102 ~]# cp /etc/haproxy/haproxy.cfg{,.bak}

[root@node102 ~]# vim /etc/haproxy/haproxy.cfg
listen mysql
  bind 172.16.1.102:3306
  mode tcp
  balance roundrobin
  server 172.16.1.103 172.16.1.103:3306 check inter 3000 fall 3 rise 5
  server 172.16.1.104 172.16.1.104:3306 check inter 3000 fall 3 rise 5

5.3.3：启动 HAProxy

[root@node102 ~]# systemctl enable haproxy
[root@node102 ~]# systemctl start haproxy
[root@node102 ~]# ss -tnl | grep 3306
LISTEN     0      3000   172.16.1.102:3306                     *:*

5.4：利用代理测试双主数据同步

MASTER1 插入数据：

MariaDB [sample_db]> INSERT INTO tbl1 (id,name) VALUES (3,'master1');

MASTER2 插入数据：

MariaDB [sample_db]> INSERT INTO tbl1 (id,name) VALUES (4,'master2');

使用 navicat 通过代理 IP 连入 MySQL：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8nIp1sbX-1606743015482)(C:\Users\My\AppData\Roaming\Typora\typora-user-images\image-20201125172016032.png)]

查看数据是否完整：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zhr5Z5xs-1606743015484)(C:\Users\My\AppData\Roaming\Typora\typora-user-images\image-20201125172125755.png)]

通过代理向表中插入一条数据：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pwty3yPq-1606743015486)(C:\Users\My\AppData\Roaming\Typora\typora-user-images\image-20201125172229967.png)]

MASTER1 验证：

MariaDB [sample_db]> SELECT * FROM sample_db.tbl1;                   
+----+---------+
| id | name    |
+----+---------+
|  2 | user2   |
|  3 | master1 |
|  4 | master2 |
|  5 | master  |
+----+---------+

MASTER2 验证：

MariaDB [sample_db]> SELECT * FROM tbl1;                   
+----+---------+
| id | name    |
+----+---------+
|  2 | user2   |
|  3 | master1 |
|  4 | master2 |
|  5 | master  |
+----+---------+

5.5：删除 MySQL 双主

重置 MASTER1 的 SLAVE：

MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> RESET SLAVE;

重置 MASTER2 的 SLAVE：

MariaDB [(none)]> STOP SLAVE;
MariaDB [(none)]> RESET SLAVE;

重置 MASTER1 的 MASTER：

MariaDB [(none)]> RESET MASTER;

重置 MASTER2 的 MASTER：

MariaDB [(none)]> RESET MASTER;

六：MySQL 备份与恢复

目前有两个数据库：sample_db 和 sample_db1，都有一张 tbl1 的表，表中各有两条数据；

查看现有数据：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_db          |
| sample_db1         |
+--------------------+

MariaDB [(none)]> SHOW TABLES IN sample_db;
+---------------------+
| Tables_in_sample_db |
+---------------------+
| tbl1                |
+---------------------+

MariaDB [(none)]> SHOW TABLES IN sample_db1;
+----------------------+
| Tables_in_sample_db1 |
+----------------------+
| tbl1                 |
+----------------------+

MariaDB [(none)]> SELECT * FROM sample_db.tbl1; 
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+
2 rows in set (0.00 sec)

6.1：mysqldump

mysqldump 仅支持全量备份，不支持增量；

6.1.1：备份策略：全量+binlog

实验过程：

双主结构，MSTER2 节点做了全量备份，一段时间后，MSTER1 节点故障，需要使用备份文件进行恢复，并重建双主；

MASTER2 进行全量备份（温备或热备）；
往数据库中添加一些数据；
关闭 MASTER1 的 mariadb，并删除数据文件；
使用 MASTER2 的备份恢复 MASTER1，并使双主继续正常工作；

执行温备

[root@node104 ~]# mysqldump -uroot -p -hlocalhost --all-databases -x -R -E --master-data=2 --flush-logs > /root/alldb-$(date +%F).sql
Enter password: 

[root@node104 ~]# ll alldb-2020-11-27.sql
-rw-r--r-- 1 root root 516927 Nov 27 17:31 alldb-2020-11-27.sql

–all-databases：指定备份所有数据库；
-x：即 --lock-all-tables，锁定所有数据库的所有表（读锁）；
-R：即 --routines，包括备份存储过程和存储函数；
-E：即 --events，包括备份事件调度器；
–master-data=2：记录 CHANGE MASTER TO 语句，并注释；（=1 表示不注释）；
–flush-logs：锁定表完成后，备份开始前，进行日志刷新操作；

这样即在锁定所有数据库的所有表后，刷新日志文件，开始执行温备（数据文件、存储过程、存储函数、事件调度器），并将指定 MASTER 的语句进行注释一并写入 sql 文件，以便用于在新的 SLAVE 节点上恢复；备份时所有数据只能读不能写；

执行热备

InnoDB 存储引擎时可用 --single-transaction 来启动一个备份事务，此时备份任务被当做事务处理，根据设定的事务隔离级别，数据 IO 可达到相应的并行程度，备份过程中既能读又能写，达到热备的效果；

[root@node104 ~]# mysqldump -uroot -p -hlocalhost --all-databases --single-transaction -R -E --master-data=2 --flush-logs > /root/alldb-$(date +%F).sql

添加数据

添加数据，模拟在执行全量备份后数据发生了改变：

MariaDB [sample_db1]> INSERT INTO tbl1 (id,name) VALUES (3,'user3'),(4,'user4');

模拟 MASTER1 数据损坏

关闭 mariadb：

[root@node103 ~]# systemctl stop mariadb

删除数据文件：

[root@node103 ~]# rm -rf /data/mysql/*
[root@node103 ~]# ll /data/mysql      
total 0

MASTER1 重置 mariadb

保留之前的配置文件，清空数据目录和日志目录；然后启动 mariadb；

[root@node103 ~]# rm -rf /data/*
[root@node103 ~]# mkdir /data/{mysql,mysql_logs}
[root@node103 ~]# chwon mysql:mysql -R /data
[root@node103 ~]# systemctl start mariadb
[root@node103 ~]# mysql_secure_installation

MASTER1 还原数据

先停止 MASTER2 的复制线程：

MariaDB [(none)]> STOP SLAVE;

拷贝备份文件：

[root@node103 ~]# scp 172.16.1.104:/root/alldb-2020-11-27.sql /root/

MASTER1 恢复备份数据：

[root@node103 ~]# mysql -uroot -p -hlocalhost < alldb-2020-11-27.sql

查看 MASTER1 中当前的数据：

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sample_db          |
| sample_db1         |
+--------------------+

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+
2 rows in set (0.00 sec)

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+
2 rows in set (0.00 sec)

此时还没有备份执行后添加的数据；

恢复 MySQL 双主

查看备份文件中记录的二进制日志位置：

[root@node103 ~]# grep '^-- CHANGE MASTER' alldb-2020-11-27.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-master2.000004', MASTER_LOG_POS=245;

恢复 MASTER1 为 MASTER2 的 SLAVE：

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.104',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master2.000004',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

MASTER1 核实新添加的数据：

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
+----+-------+

MASTER1 授权复制用户：

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repl'@'172.16.1.%' IDENTIFIED BY '123456';
MariaDB [(none)]> FLUSH PRIVILEGES;

记录 MASTER1 二进制日志位置：

MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-master1.000003 |   517821 |              |                  |
+----------------------+----------+--------------+------------------+

重置 MASTER2 的 SLAVE：

MariaDB [(none)]> RESET SLAVE;

重新设置 MASTER2 为 MASTER1 的 SLAVE：

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.103',MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-master1.000003',MASTER_LOG_POS=517821;

MariaDB [(none)]> START SLAVE;

查看两个节点的 SLAVE 状态：

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.103
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master1.000003
          Read_Master_Log_Pos: 517821
               Relay_Log_File: mysql-slave2.000002
                Relay_Log_Pos: 533
        Relay_Master_Log_File: mysql-master1.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: 517821
              Relay_Log_Space: 824
              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: 103
             
MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.104
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master2.000004
          Read_Master_Log_Pos: 472
               Relay_Log_File: mysql-slave1.000002
                Relay_Log_Pos: 760
        Relay_Master_Log_File: mysql-master2.000004
             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: 472
              Relay_Log_Space: 1051
              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: 104

验证恢复后 MySQL 双主

MASTER1 添加数据：

MariaDB [(none)]> INSERT INTO sample_db1.tbl1 (id,name) VALUES (5,'user5'),(6,'user6');

MASTER2 验证：

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
+----+-------+

MASTER2 添加数据：

MariaDB [(none)]> INSERT INTO sample_db1.tbl1 (id,name) VALUES (7,'user7'),(8,'user8');

MASTER1 验证：

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
|  7 | user7 |
|  8 | user8 |
+----+-------+

6.2：xtrabackup

安装 percona-xtrabackup

[root@node103 ~]# yum install percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm

6.2.1：备份策略：全量+binlog

查看当前数据：

MariaDB [sample_db]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+
2 rows in set (0.00 sec)

MariaDB [sample_db]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
|  7 | user7 |
|  8 | user8 |
+----+-------+

进行全量备份

[root@node103 ~]# innobackupex --user=root --password=123456 /data/backup/

[root@node103 ~]# ll /data/backup/2020-11-30_20-48-48/
total 18460
-rw-r----- 1 root root      434 Nov 30 20:48 backup-my.cnf
-rw-r----- 1 root root 18874368 Nov 30 20:48 ibdata1
drwxr-x--- 2 root root     4096 Nov 30 20:48 mysql
drwxr-x--- 2 root root     4096 Nov 30 20:48 performance_schema
drwxr-x--- 2 root root       49 Nov 30 20:48 sample_db
drwxr-x--- 2 root root       49 Nov 30 20:48 sample_db1
-rw-r----- 1 root root       25 Nov 30 20:48 xtrabackup_binlog_info
-rw-r----- 1 root root      135 Nov 30 20:48 xtrabackup_checkpoints
-rw-r----- 1 root root      479 Nov 30 20:48 xtrabackup_info
-rw-r----- 1 root root     2560 Nov 30 20:48 xtrabackup_logfile

删除数据

[root@node103 ~]# rm -rf /data/mysql/*
[root@node103 ~]# rm -rf /data/mysql_logs/*

prepare 全量备份

[root@node103 ~]# innobackupex --apply-log /data/backup/2020-11-30_20-48-48/

恢复

[root@node103 ~]# systemctl stop mariadb

[root@node103 ~]# innobackupex --copy-back /data/backup/2020-11-30_20-48-48/

[root@node103 ~]# chown -R mysql:mysql /data/mysql*

验证数据

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
+----+-------+

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
|  7 | user7 |
|  8 | user8 |
+----+-------+

6.2.2：备份策略：全量+增量+binlog

执行全量备份

[root@node103 ~]# innobackupex --user=root --password=123456 --no-timestamp /data/backup/all-db

添加数据

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (3,'incremental1');

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
+----+--------------+

执行第一次增量备份

[root@node103 ~]# innobackupex --user=root --password=123456 --incremental --no-timestamp /data/backup/incremental-1 --incremental-basedir=/data/backup/all-db

添加数据

MariaDB [(none)]> INSERT INTO sample_db.tbl1 (id,name) VALUES (4,'incremental2');

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;                                  
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
|  4 | incremental2 |
+----+--------------+

执行第二次增量备份

[root@node103 ~]# innobackupex --user=root --password=123456 --incremental --no-timestamp /data/backup/incremental-2 --incremental-basedir=/data/backup/incremental-1

删除数据

[root@node103 ~]# rm -rf /data/mysql/*
[root@node103 ~]# rm -rf /data/mysql_logs/*

prepare

[root@node103 ~]# innobackupex --apply-log --redo-only /data/backup/all-db/
[root@node103 ~]# innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-1
[root@node103 ~]# innobackupex --apply-log --redo-only /data/backup/all-db/ --incremental-dir=/data/backup/incremental-2
[root@node103 ~]# innobackupex --apply-log /data/backup/all-db/

恢复

[root@node103 ~]# systemctl stop mariadb

[root@node103 ~]# innobackupex --copy-back /data/backup/all-db/

[root@node103 ~]# chown -R mysql:mysql /data/mysql*

验证

[root@node103 ~]# systemctl start mariadb

MariaDB [(none)]> SELECT * FROM sample_db.tbl1;                                  
+----+--------------+
| id | name         |
+----+--------------+
|  1 | user1        |
|  2 | user2        |
|  3 | incremental1 |
|  4 | incremental2 |
+----+--------------+

MariaDB [(none)]> SELECT * FROM sample_db1.tbl1;
+----+-------+
| id | name  |
+----+-------+
|  1 | user1 |
|  2 | user2 |
|  3 | user3 |
|  4 | user4 |
|  5 | user5 |
|  6 | user6 |
|  7 | user7 |
|  8 | user8 |
+----+-------+

学渣角鹿白

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL 应用实践（超长文预警）

一：实验环境1.1：概述网络：内部网络：172.16.1.0/24（仅主机模式）；外部网络：192.168.1.0/24（桥接模式）；服务器：2 台 VMware Workstation 虚拟机（node103、node104）；安装 CentOS 7.2（已经过系统初始化操作）；yum 安装 MariaDB；实验环境基础服务：外网 DNS & NTP：192.168.1.250；内网 DNS & NTP：172.16.1.250；1.2：虚拟机配置1.2.1：
复制链接

扫一扫

专栏目录