一:实验环境
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 |
+----+-------+