第一章:MySQL基础架构
1.1 Server层
包括连接器,查询缓存,分析器,优化器,执行器等,涵盖了MySQL大多数核心服务功能,以及所有内置函数,所有跨存储引擎功能都在这一层实现,比如存储过程,视图,触发器等
1.1.1 连接器
管理连接,权限验证
- 在用户连接成功后,连接器会到权限表查出拥有的权限,之后在这个连接中的权限判断,都依赖于此时读到的权限。
- 如果连接太长时间没动静会自动断开,由参数
wait_timeout
控制,默认8小时 - MySQL再执行过程中临时使用的内存是管理在连接对象里面的,这些资源在连接断开的时候会释放,所以长连接积累下来,可能导致内存占用太大,被系统强行杀掉(OOM),从现象看就是MySQL异常重启了
1.1.2 查询缓存
MySQL拿到一个查询请求会先到查询缓存查看,之前是否执行过这条语句,之前执行过的语句及结果可能会以key-value对的行是,被直接缓存到内存中,key是查询的语句,value是查询的结果。如果查询能够直接在缓存中找到,这个value就直接返回客户端。
但是只要对一个表有更新操作,这个表上所有的查询缓存都被清空。对于更新频繁的数据库来说,查询的命中率非常低,除非是一张静态表,那么适合使用查询缓存
通过设置query_cache_type=DEMAND
,这样对默认的SQL不使用查询缓存,而对于要使用查询缓存的语句可以显式指定
1.1.3 分析器
词法分析,语法分析
词法分析:分析SQL语句,比如识别关键字,识别表名,字段名等
语法分析:根据词法分析的结果,根据语法规则,判断SQL是否符合MySQL语法
1.1.4 优化器
生成执行计划,选择索引
1.1.5 执行器
执行执行计划
1.2 存储引擎层
存储引擎负责MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信,这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明,存储引擎API包含几十个底层函数,但是存储引擎不会去解析SQL,不同存储引擎也不会相互通信,只是简单响应上层服务器的请求
在文件系统中,MySQL将每个数据库保存为数据目录下的一个子目录,创建表时,MySQL会在数据库子目录下创建一个和表名相同的.frm文件保存表的定义,不同的存储引擎保存数据和索引的方式是不同的,但表的定义则是在MySQL的server层统一处理的。
不同存储引擎的特性对比如下表:
Feature | MyISAM | Memory | InnoDB | Archive | NDB |
---|---|---|---|---|---|
B-tree indexes | Yes | Yes | Yes | No | No |
Backup/point-in-time recovery | Yes | Yes | Yes | Yes | Yes |
Cluster database support | No | No | No | No | Yes |
Clustered indexes | No | No | Yes | No | No |
Compressed data | Yes | No | Yes | Yes | No |
Data caches | No | N/A | Yes | No | Yes |
Encrypted data | Yes | Yes | Yes | Yes | Yes |
Foreign key support | No | No | Yes | No | Yes |
Full-text search indexes | Yes | No | Yes | No | No |
Geospatial data type support | Yes | No | Yes | Yes | Yes |
Geospatial indexing support | Yes | No | Yes | No | No |
Hash indexes | No | Yes | No | No | Yes |
Index caches | Yes | N/A | Yes | No | Yes |
Locking granularity | Table | Table | Row | Row | Row |
MVCC | No | No | Yes | No | No |
Replication support | Yes | Limited | Yes | Yes | Yes |
Storage limits | 256TB | RAM | 64TB | None | 384EB |
T-tree indexes | No | No | No | No | Yes |
Transactions | No | No | Yes | No | Yes |
Update statistics for data dictionary | Yes | Yes | Yes | Yes | Yes |
第二章:安装
安装之前需要先将防火墙以及selinux关闭
systemctl stop firewalld
setenforce 0
以下所有安装包,均再官网可以下载 https://dev.mysql.com/downloads/mysql/
2.1 rpm包安装
2.1.1 环境规划
IP | 操作系统 | MySQL版本 | 安装方式 |
---|---|---|---|
192.168.240.220 | CentOS7.4 | MySQL5.7.26 | rpm包方式 |
2.1.2 安装
安装依赖包
yum install net-tools -y
卸载mariadb-libs
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y
上传rpm包到/usr/local/src目录下,解压
tar xf /usr/local/src/mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar -C /usr/local/src/
cd /usr/local/src/
安装
rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm mysql-community-client-5.7.26-1.el7.x86_64.rpm mysql-community-common-5.7.26-1.el7.x86_64.rpm mysql-community-libs-5.7.26-1.el7.x86_64.rpm
2.1.3启动数据库
systemctl start mysqld
查看数据库是否正常启动,查看到mysqld进程表示数据库启动正常
[root@localhost src]# ss -nltp | grep mysqld
LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=6784,fd=22))
从mysqld日志文件/var/log/mysqld.log中找mysqld的root临时密码
修改root密码,方式如下
[root@localhost src]# mysqladmin -uroot -p password
Enter password: # 输入临时密码
New password: # 输入新密码
Confirm new password: # 确认新密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
2.2 二进制包安装
2.2.1 环境规划
IP | 操作系统 | MySQL版本 | 安装方式 |
---|---|---|---|
192.168.240.221 | CentOS7.4 | MySQL5.7.26 | 二进制包方式 |
2.2.2 安装
创建mysql用户
useradd -M -s /sbin/nologin mysql
创建相关目录(可按照自己习惯自定义)
mkdir -p /datadir/{data,tmp,logs}
chown -R mysql.mysql /datadir
上传二进制包到/usr/local/src目录下,解压
# 解压
tar xf /usr/local/src/mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/src/
# 将压缩包移动到/usr/local/目录下,并重命名为mysql.5.7
mv /usr/local/src/mysql-5.7.26-linux-glibc2.12-x86_64 /usr/local/mysql5.7
# 设置/usr/local/mysql5.7的属组属主
chown -R mysql.mysql /usr/local/mysql5.7
设置环境变量
echo 'export PATH=/usr/local/mysql5.7/bin:$PATH' >> /etc/profile
source /etc/profile
初始化数据库
mysqld --initialize --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/datadir/data/
初始化之后会默认给你生成临时密码显示在终端,这个要先记一下 如果不需要它帮你生成密码可以使用–initialize-insecure选项
创建基础配置文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/datadir/data/
socket=/datadir/tmp/mysqld.sock
log-error=/datadir/logs/err.log
pid-file=/datadir/tmp/mysql.pid
symbolic-links=0
!includedir /etc/my.cnf.d
[client]
socket=/datadir/tmp/mysqld.sock
2.2.3 启动数据库
启动数据库
mysqld --daemonize
查看mysql是否启动,看到3306端口启动表示数据库正常启动
[root@localhost ~]# ss -nltp | grep mysqld
LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=6834,fd=17))
修改数据库初始化使用的临时密码
[root@localhost datadir]# mysqladmin -uroot -p password
Enter password: # 输入临时密码
New password: # 输入新密码
Confirm new password: # 确认新密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
2.2.4 遇到的错误
- 使用mysqladmin验证mysqld是否存活的时候,报错显示错误码为1862
原因:使用的是临时密码
解决方法:重新数据库root密码即可
2.3 编译安装
2.3.1 环境规划
IP | 操作系统 | MySQL版本 | 安装方式 |
---|---|---|---|
192.168.240.222 | CentOS7.4 | MySQL5.7.26 | 编译安装 |
2.3.2 安装
安装相关依赖
yum groupinstall "Development Tools" -y
yum install cmake curses ncurses-devel gcc gcc-devel gcc-c++ -y
创建mysql用户
useradd -M -s /sbin/nologin mysql
创建相关目录(可按照自己习惯自定义)
mkdir -p /datadir/{data,tmp,logs}
chown -R mysql.mysql /datadir
上传源码包到/usr/local/src/目录下,解压
tar xf /usr/local/src/mysql-boost-5.7.26.tar.gz -C /usr/local/src/
cd /usr/local/src/mysql-5.7.26/
利用CMake构建MySQL5.7
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.7 \
-DMYSQL_DATADIR=/datadir/data \
-DMYSQL_UNIX_ADDR=/datadir/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0 \
-DWITH_BOOST=./boost/boost_1_59_0
CMake编译安装过程会把指定的配置信息硬编码到程序文件中 这些配置项可以根据需要进行调整
安装
make && make install
设置环境变量
echo "export PATH=/usr/local/mysql5.7/bin:$PATH" >> /etc/profile
source /etc/profile
初始化数据库
mysqld --initialize --user=mysql --basedir=/usr/local/mysql5.7 --datadir=/datadir/data
初始化之后会默认给你生成临时密码,这个要先记一下 如果不需要它帮你生成密码可以使用–initialize-insecure选项
创建基础配置文件
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
user=mysql
datadir=/datadir/data/
socket=/datadir/tmp/mysqld.sock
log-error=/datadir/logs/err.log
pid-file=/datadir/tmp/mysql.pid
symbolic-links=0
!includedir /etc/my.cnf.d
[client]
socket=/datadir/tmp/mysqld.sock
2.3.3 启动数据库
启动数据库
mysqld --daemonize
查看mysql是否启动,看到3306端口启动表示数据库正常启动
[root@localhost ~]# ss -nltp | grep mysqld
LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=6834,fd=17))
修改数据库初始化使用的临时密码
[root@localhost mysql-5.7.26]# mysqladmin -uroot -p password
Enter password: # 输入临时密码
New password: # 输入新密码
Confirm new password: # 确认新密码
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
第三章:日志管理
3.1错误日志
记录MySQL数据库的一般状态信息及报错信息,是我们对于数据库常规报错处理的常用日志
3.1.1配置方式
[mysqld]
log-error=/datadir/logs/err.log
3.1.2 查看当前错误日志配置
数据库内执行
mysql> SHOW VARIABLES LIKE 'log_error';
3.2事务日志
事务日志包含了两个日志文件,一个是redo log,一个是undo log
3.2.1 redo log
MySQL运行时,会把数据从硬盘加载到内存中,如果每次执行一次事务都做一个落盘的操作,这样做保证了一定的安全,但是会降低性能,磁盘的IO可能会很大。 所以就有了这样一个机制,当用户修改数据的时候,MySQL从硬盘加载数据到内存进行更改,当用户commit的时候,数据不直接写入到硬盘,而是把这个改变的信息写到redo日志中,这时候redo日志是在内存中的,同样还是需要写到硬盘,但是这个IO是顺序IO而且数据量更小。
相关变量
变量 | 描述 |
---|---|
innodb_log_file_size | redo log每个文件的大小 |
innodb_log_files_in_group | 指定一个组内redo log文件数量 |
innodb_log_group_home_dir | 指定redo log路径 |
innodb_flush_log_at_trx_commit = 1/0/2 | 0:redo buffer 每秒钟,向redolog文件中刷新一次数据 —性能最好1:redo buffer 每次事务提交,向redo log文件中刷新一次数据—安全最高2:redo buffer 每次事务提交,向OS buffer中刷新一次数据,但每秒钟向redo log文件中刷新一次数据—折中 |
innodb_flush_method | 有三个值:fdatasync(默认),O_DSYNC,O_DIRECT,默认是fdatasync,调用fsync()去刷数据文件与redo log的buffer,为O_DSYNC时,innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件,为O_DIRECT时,innodb使用O_DIRECT打开数据文件,使用fsync()刷写数据文件跟redo log |
常见的搭配
- 安全性最高
innodb_flush_log_at_trx_commit = 1
Innodb_flush_method = O_DIRECT
- 性能最好
innodb_flush_log_at_trx_commit = 0
Innodb_flush_method = fdatasync
- 折中
innodb_flush_log_at_trx_commit = 2
Innodb_flush_method = O_DSYNC
3.2.2 undo log
现在有个事务执行,对数据进行修改,但是没有执行commit,这时候用户后悔了,这时候执行rollback,就可以直接回滚到之前的状态。undo日志保存了事务发生之前的数据的一个版本,可以用于回滚,同时可以提供多版本并发控制下的读(MVCC)
在MySQL5.6中,undo日志位于共享表空间中,共享表默认名称是ibdata文件 在MySQL5.7,undo日志就可以设置成独立的文件。
相关变量
变量 | 描述 |
---|---|
innodb_undo_directory | 设置undo日志的路径 |
innodb_undo_logs | 定义InnoDB使用的回滚段数,默认128个回滚段 |
innodb_undo_tablespaces | InnoDB使用的撤消表空间个数。默认值为0。在初始化后,就再也不能被改动了 |
3.3中继日志
主从复制时,从库的IO线程从主库获取binlog日志信息存放在中继日志中,sql线程再读取执行中继日志。
3.4常规日志
记录mysql所有执行成功的SQL语句信息,可以做审计用,但是很少开启,性能损耗比较大
3.4.1 配置方式
[mysqld]
general_log=on
general_log_file=/datadir/logs/server.log
3.4.2 查看配置方式
mysql> SHOW VARIABLES LIKE '%gen%';
3.5慢查询日志
将MySQL服务器中影响数据库性能的相关SQL语句记录到日志文件,通过对这些特殊的SQL语句分析,改进以达到提高数据库性能的目的,这个日志和二进制日志不同,可以直接vim查看的文本文件,但是也有命令工具mysqldumpslow
可以帮助统计,这个工具输出的信息比较简陋,可以用percona公司提供的一个pt-query-diagest
工具
3.5.1 相关变量
变量 | 描述 |
---|---|
long_query_time | 设定慢查询的阀值,超出次设定值的SQL即被记录到慢查询日志,缺省值为10s |
slow_query_log | 指定是否开启慢查询日志 |
slow_query_log_file | 指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name-slow.log |
min_examined_row_limit | 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志 |
log_queries_not_using_indexes | 不使用索引的慢查询日志是否记录到索引 |
3.5.2 mysqldumpslow
mysqldumpslow用来统计慢日志相关信息
mysqldumpslow -s c -t 10 /datadir/logs/slow.log
这会输出记录次数最多的10条SQL语句,其中:
# -s 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
#-t 是top n的意思,即为返回前面多少条的数据;
#-g 后边可以写一个正则匹配模式,大小写不敏感的;
示例1:得到返回记录集最多的10个查询。
mysqldumpslow -s r -t 10 /datadir/logs/slow.log
示例2:得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g "left join" /datadir/logs/slow.log
3.6 二进制日志
记录了已提交的数据,以event形式记录到二进制文件中,可以用来做数据恢复,主从复制
3.6.1 配置方式
[mysqld]
log_bin=/datadir/logs/bin.log
3.6.2 二进制日志的记录格式
二进制日志记录的格式有三种
- 语句模式,基于语句的日志记录记录所有对表的数据或结构进行更改的SQL语句。
- 行模式,基于行记录影响各个表行的事件
- 混合模式,语句和基于行的日志记录的组合。在混合日志记录中,默认情况下使用基于语句的日志记录,但是当MySQL确定语句对于基于语句的复制可能不安全时,它将使用基于行的格式。
控制的binlog记录格式的变量
变量 | 描述 |
---|---|
binlog_format=row | 可选值:statement(语句模式),row(行模式),mixed(混合模式) |
3.6.3 查看binlog日志相关信息
3.6.3.1 mysql 内部查看binlog相关信息
查看binlog相关变量信息
mysql> show variables like 'binlog%';
查看当前二进制日志信息
# 方法一:
mysql> show master logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000001 | 154 |
+------------+-----------+
1 row in set (0.00 sec)
# 方法二:
mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000001 | 154 | | | |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 方法三:
mysql> show binlog events;
+------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+-----+----------------+-----------+-------------+---------------------------------------+
| bin.000001 | 4 | Format_desc | 220 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin.000001 | 123 | Previous_gtids | 220 | 154 | |
+------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
# 方法四:
mysql> show binary logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000001 | 313 |
+------------+-----------+
1 row in set (0.00 sec)
查看某个binlog日志内的信息
mysql> show binlog events in 'bin.000001';
+------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------+-----+----------------+-----------+-------------+---------------------------------------+
| bin.000001 | 4 | Format_desc | 220 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| bin.000001 | 123 | Previous_gtids | 220 | 154 | |
| bin.000001 | 154 | Anonymous_Gtid | 220 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| bin.000001 | 219 | Query | 220 | 313 | create database test |
+------------+-----+----------------+-----------+-------------+---------------------------------------+
4 rows in set (0.00 sec)
3.6.3.2 mysqlbinlog命令
binlog是二进制日志,直接cat vim是看不到相关信息的。这时候可以用mysqlbinlog查看内容
语法格式:
mysqlbinlog [options] log-files
常用选项
常用选项 | 描述 |
---|---|
–base64-output=name | 当二进制格式为row格式,name设置为’decode-rows’可以解码二进制格式 |
-d, --database=name | 只显示指定库的binlog日志 |
–stop-position=# | 读取binlog到位置# |
–start-position=# | 从binlog的#位置开始读取 |
–start-datetime=name | 从某个时间点开始读取binlog日志信息 |
–stop-datetime=name | 读取binlog到某个时间点为止 |
示例一:
查看row格式binlog日志
mysqlbinlog /datadir/logs/bin.000001 -vvv --base64-output=decode-rows
示例二:
查看123字节到313字节之间的binlog 日志
mysqlbinlog /datadir/logs/bin.000001 -vvv --base64-output=decode-rows --start-position=123 --stop-position=313
示例三:
查看2019-05-19 14:00:00时间开始的二进制日志信息
mysqlbinlog /datadir/logs/bin.000001 -vvv --base64-output=decode-rows --start-datetime='2019-05-19 14:00:00'
3.6.4 滚动与删除二进制日志
3.6.4.1 刷新二进制日志
mysql> flush logs;
3.6.4.2 根据存在时间删除二进制日志
mysql> SET GLOBAL expire_logs_days = 7;
3.6.4.3 根据文件名删除日志
mysql> PURGE BINARY LOGS TO 'bin.000003';
3.6.4.4 删除三天前的二进制日志
mysql> PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
第四章:备份与恢复
4.1 逻辑备份工具mysqldump
mysqldump这个程序最开始是Igor Romanenko写的。它是一个MySQL/MariaDB逻辑备份的工具,备份的逻辑就是将指定的数据库/表中的数据以SQL语句的形式一条一条导出。还原的时候也是执行一条条sql语句,达到还原的目的。
4.1.1 语法格式
shell> mysqldump [options] db_name [tbl_name ...] #备份指定库中的表
shell> mysqldump [options] --databases db_name ... #备份指定数据库,可以多个
shell> mysqldump [options] --all-databases #备份所有库
4.1.2 常用选项
常用选项 | 描述 |
---|---|
-A, --all-databases | 备份所有数据库 |
-B, --databases | 对单个数据库,或多个数据库进行备份,会有CREATE DATABASE和USE 语句,如果不使用-B备份单库,则不会有这两个语句 |
- -add-drop-database | 在每次创建之前添加DROP DATABASE。通常与 --all-databases 一起用 |
- -compact | 提供较少的详细输出(对调试很有用)。禁用结构注释和页眉/页脚构造。 |
-E, --events | 导出事件 |
-F, --flush-logs | 在开始备份之前刷新MySQL服务器binlog日志。如果将此选项与–databases=or --all-databases选项结合使用,则会为每个备份的数据库刷新日志。例外情况是使用–lock-all-tables或–master-data:在这种情况下,日志只刷新一次,对应于所有表被锁定的时刻。如果你希望你的转储和刷新日志在同一确切的时刻发生,你应该使用–flush-logs与–lock-all-tables或–master-data。 |
-f, --force | 即使在表转储期间发生SQL错误,也要继续。 |
-h name, --host=name | 从给定主机上的MariaDB或MySQL服务器连接并备份数据。默认主机是localhost |
–ignore-table=name | 不要备份指定的表。要指定多个要忽略的表,请多次使用该指令,每个表使用一次。必须使用数据库和表名指定每个表,例如,–ignore-table=database.table。 |
-x, --lock-all-tables | 锁定所有数据库中的所有表。这是通过在整个转储期间获取全局读锁来实现的。指定–single-transaction和–lock-tables的时候此选项自动关闭(一般用于myisam表温备,一般不用) |
- -log-error=name | 通过将警告和错误附加到指定文件来记录警告和错误。默认是不进行日志记录。 |
- -master-data[=#] | 使binlog日志位置和文件名附加到输出,如果该选项设置为1(默认值),则将其作为CHANGE MASTER命令输出; 如果设置为2,则该命令将以注释符号作为前缀。除非另有说明,否则此–master-data选项将打开。这将在转储开始时短时间内进行全局读锁定(有这个参数之后就不需要用-x和-l参数锁表了,因为这个参数会自动会锁表,备份完了也会自动释放掉锁。) |
-q, --quick | 此选项对于转储大型表非常有用。它强制mysqldump一次从服务器一行检索表的行,然后将结果直接输出到stdout,而不是检索整个行集并在写出之前在内存中缓冲它。默认为开,用于–skip-quick禁用。 |
–single-transaction | 此选项在备份数据之前向服务器发送START TRANSACTION SQL语句。它仅适用于InnoDB之类的事务表,使用此选项时,应记住只有InnoDB表以一致状态备份。单事务功能不仅取决于引擎是事务性的还是能够REPEATABLE-READ,还取决于START TRANSACTION WITH CONSISTENT SNAPSHOT。 该–single-transaction选项与–lock-tables选项互斥,因为LOCK TABLES导致任何挂起的事务被隐式提交。因此,此选项自动关闭–lock-tables(针对innodb进行热备) 要转储大表,您应该将–single-transaction选项与–quick。 |
-R, --routines | 备份存储过程和函数数据(备份一般都带上这个参数) |
- -triggers | 备份触发器数据(备份一般都带上这个参数) |
-d | 仅表结构 |
-t | 仅数据 |
4.1.3 示例
示例1:备份所有库,并保存到~/alldb_backup.sql
mysqldump -uroot -p123456 --flush-logs --quick --single-transaction --master-data=2 --all-databases> ~/alldb_backup.sql
示例2:备份单个hellodb单个库
mysqldump -uroot -p123456 --flush-logs --quick --single-transaction hellodb students > ~/hellodb_students_backup.sql
示例3:备份mydb和test数据库
mysqldump -B mydb test > mydb_test.sql
示例4:分库备份
for i in `mysql -e'show databases;' | sed 1d` ;do mysqldump -B $i >${i}.sql ; done;
示例5:压缩备份并打上时间戳
mysqldump -A -R --triggers --master-data=2 -F | gzip > full_$(date +%F).sql.gz
4.1.4 还原
命令行还原
mysql < ~/alldb_backup.sql
在mysql内部还原(推荐)
# 临时关闭binlog
mysql> set @@session.sql_log_bin=off;
mysql> source sql文件
4.2 Percona XtraBackup
Percona XtraBackup是世界上唯一的开源免费MySQL热备份软件,可为InnoDB和XtraDB 数据库执行无阻塞备份。使用Percona XtraBackup,有以下优点
- 备份快速可靠地完成
- 备份期间不间断的事务处理
- 自动备份验证
- 由于更快的恢复时间,正常运行时间更长
Percona XtraBackup可与MySQL,MariaDB和Percona Server配合使用。它支持InnoDB,XtraDB和HailDB 存储引擎的完全非阻塞备份。此外,它还可以通过在备份结束时暂时暂停写入来备份以下存储引擎:MyISAM, Merge和Archive,包括分区表,触发器和数据库选项。
这里有一点需要注意,在MariaDB10.3.x及以上的版本用Percona XtraBackup工具会有问题。原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。
Percona的官方文档:https://www.percona.com/doc/percona-xtrabackup/2.4/index.html
Mariabackup的官方文档:https://mariadb.com/kb/en/library/mariabackup-overview/
这个物理备份工具的原理可以参考这篇文章: http://mysql.taobao.org/monthly/2016/03/07/
4.2.1 安装
rpm包下载页面: https://www.percona.com/downloads/Percona-XtraBackup-2.4/LATEST/
安装Percona XtraBackup
yum install percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm -y
4.2.2 常用选项
常用选项 | 描述 |
---|---|
–apply-log-only | 此选项仅在准备备份时,执行重做阶段。在恢复之前,需要将事务日志里的变化合并到物理数据中,这个过程叫做apply-log |
–backup | 进行备份 |
–compress | 此选项告诉xtrabackup使用指定的压缩算法压缩所有输出数据,包括事务日志文件和元数据文件。目前唯一支持的算法是quicklz。生成的文件具有qpress存档格式,即*.qpxtrabackup生成的每个文件本质上都是一个文件的qpress存档,可以通过qpress 文件存档提取和解压缩。 |
–copy-back | 将先前制作的备份中的所有文件从备份目录复制到data目录位置。 |
–defaults-file=[MY.CNF] | 仅读取给定文件中的默认选项。必须作为命令行中的第一个选项。必须是真实的文件; 它不能成为一种象征性的联系。 |
–incremental-basedir=DIRECTORY | 创建增量备份时,这是包含完整备份的目录,该备份是增量备份的基础数据集。 |
–incremental-dir=DIRECTORY | 准备增量备份时,这是增量备份与完整备份组合的目录,以进行新的完整备份。 |
–move-back | 将先前制作的备份中的所有文件从备份目录移动到其原始位置。由于此选项会删除备份文件,因此必须谨慎使用。 |
–target-dir=DIRECTORY | 此选项指定备份的目标目录。如果该目录不存在,则xtrabackup会创建它。如果目录确实存在且为空,则xtrabackup将成功。 但是,xtrabackup不会覆盖现有文件; |
–user=USERNAME | 此选项指定连接到服务器时使用的MySQL用户名 |
–password=PASSWORD | 此选项指定连接到数据库时要使用的密码 |
–prepare | 使xtrabackup在使用创建的备份上执行恢复 |
–parallel=# | 设置并发数量,一般可以4个,但是具体根据cpu调整 |
4.2.3 完全备份
创建目录存放备份文件
mkdir /backup
完全备份
备份的时候,会立即将内存中已提交的数据页落盘,然后开始备份数据,记录此时的LSN号,数据库可能还会继续跑,然后不断地监控redo和undo日志,如果有变化,也会把事务日志也一起备走并记录最后一个LSN号。
xtrabackup --user=root --password=123456 --backup -S /datadir/tmp/mysqld.sock --target-dir=/backup/fullback
完全备份的恢复
- 准备备份
使用该选项进行备份后,您首先需要准备它以便恢复它。数据文件在准备之前不是时间点一致的,因为它们在程序运行时的不同时间被复制,在数据文件中的LSN号和事务日志中的LSN号不一致,备份的数据结合事务日志,该回滚回滚,该提交提交,相当于模拟了CSR的过程,这时候就能保证数据的一致性,数据库才能启动,否则检测到数据不一致是无法启动的。
xtrabackup --prepare --target-dir=/backup/fullback
- 恢复全备
xtrabackup --copy-back --target-dir=/backup/fullback
- 修改权限
chown -R mysql:mysql /datadir/
至此一次完全备份与恢复完成
4.2.4 增量备份
无论xtrabackup和innobackupex工具支持增量备份,这意味着它们可以只复制自上次备份以来发生变化的数据。
增量备份有效,因为每个InnoDB页面都包含一个日志序列号或LSN。该LSN是整个数据库系统的版本号。每个页面的LSN显示它最近的更改。
增量备份的方式是基于上一次备份进行的,增量备份无法单独恢复,必须基于全备进行恢复,所有的增量必须要按照顺序合并到全备中
创建存放增量备份的目录
mkdir /backup/
- 创建一个全备
xtrabackup --user=root --password=123456 --backup -S /datadir/tmp/mysqld.sock --target-dir=/backup/fullback
- 基于全备做第一次增量备份
xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/fullback
- 基于全备做第二次增量备份
xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1/
增量备份的恢复
- 准备基础备份(全备)
xtrabackup -uroot -p123456 --prepare --apply-log-only --target-dir=/backup/fullback
- 准备第一次增量备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/fullback --incremental-dir=/backup/inc1
- 准备第二次增量备份
xtrabackup --prepare --target-dir=/backup/fullback --incremental-dir=/backup/inc2
- 恢复
xtrabackup -uroot -p123456 --copy-back --target-dir=/backup/fullback
- 修改权限
chown -R mysql:mysql /datadir/
至此一次增量备份与恢复完成
第五章:复制
5.1 普通主从复制
5.1.1 原理
原理图
MySQL复制功能使用三个线程实现,一个在主服务器上,两个在从服务器上
-
Binlog备份线程。 主设备创建一个线程,以便在从库连接时将二进制日志内容发送到从库。可以
SHOW PROCESSLIST
在主服务器的输出中将此线程标识为Binlog Dump线程。
二进制日志转储线程获取主机二进制日志上的锁,用于读取要发送到从机的每个事件。一旦读取了事件,即使在事件发送到从站之前,锁也会被释放。 -
从库I/O线程。 在START SLAVE从属服务器上发出语句时,从属服务器会创建一个I/O线程,该线程连接到主服务器并要求它发送二进制日志中记录的更新。
从库I/O线程读取主库Binlog Dump线程发送的更新 并将它们复制到从库的中继日志 -
从库SQL线程,从库创建一个SQL线程来读取由从I/O线程写入的中继日志,并执行。
大致过程:
从库会启动2个线程,一个I/O线程,一个SQL线程,I/O线程负责连接主库,完成认证和获取主库binlog日志的内容,然后写到从库的中继日志中。再由SQL线程读取并执行中继日志中的SQL语句。
5.1.2 规划
IP | 系统 | MySQL版本 | 角色 |
---|---|---|---|
192.168.240.220 | CentOS7.4 | MySQL5.7.26 | 主库 |
192.168.240.221 | CentOS7.4 | MySQL5.7.26 | 从库 |
5.1.3 修改主库配置文件
在主库配置文件/etc/my.cnf中添加下面3行
[mysqld]
log_bin=/datadir/logs/bin.log
binlog_format=row
server_id=220
必须保证server_id全局唯一,否则设置双主可能会引起循环复制的问题,如果没设置,主从都搭不起来
启动主库
mysqld --daemonize
5.1.4 备份主库所有数据
mysqldump -uroot -p123456 --flush-logs --quick --single-transaction --master-data=2 --all-databases > ~/alldb.sql
将备份文件传送到从库
scp ~/alldb.sql root@192.168.240.221:~/
5.1.5 修改从库配置文件
在从库配置文件/etc/my.cnf中添加下面3行
[mysqld]
log_bin=/datadir/logs/bin.log
binlog_format=row
server_id=221
启动从库
mysqld --daemonize
5.1.6 在主库上添加用户,允许从库获取binlog
主库内部执行
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Hal@123';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
5.1.7 设置主从复制
导入主库的备份文件
mysql < ~/alldb.sql
查看备份的日志点
[root@localhost ~]# head -50 ~/alldb.sql | grep -i change
-- CHANGE MASTER TO MASTER_LOG_FILE='bin.000005', MASTER_LOG_POS=154;
进入从库设置主从备份
CHANGE MASTER TO
MASTER_HOST='192.168.240.220',
MASTER_USER='rpl_user',
MASTER_PASSWORD='Hal@123',
MASTER_LOG_FILE='bin.000005',
MASTER_LOG_POS=154;
启动主从复制
start slave ;
查看主从复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.240.220 # 主库ip
Master_User: rpl_user # 连接主库的用户
Master_Port: 3306 # 主库端口
Connect_Retry: 60 # 连接重试时间
Master_Log_File: bin.000005 # 主库日志文件
Read_Master_Log_Pos: 154 # 读取到主库日志文件哪个位置
Relay_Log_File: localhost-relay-bin.000002 # 中继日志文件
Relay_Log_Pos: 314 # 中继日志文件写到哪个位置
Relay_Master_Log_File: bin.000005
Slave_IO_Running: Yes # IO线程状态
Slave_SQL_Running: Yes # SQL线程状态
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: 154
Relay_Log_Space: 525
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: 220
Master_UUID: 61083246-79f2-11e9-a325-000c295a96ec # 主库UUID
Master_Info_File: /datadir/data/master.info # 主库info信息存放位置
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
看到下面两个参数为yes表示搭建成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.2 半同步复制
默认情况下,MySQL复制是异步的。主库将事件写入二进制日志,但不知道从库是否处理它们。使用异步复制,如果主库崩溃,则它已提交的事务可能尚未传输到任何从库。因此,在这种情况下,从主库到从库的故障转移,可能导致丢失事务。
在半同步复制中,只有在将事件写入中继日志并刷新后,从库才会确认收到事务的事件。如果从库在经过一定时间之后未确认事务(可使用rpl_semi_sync_master_timeout变量进行配置),则会发生超时,并且主服务器将切换到异步复制。当至少有一个半同步从库赶上时,将恢复半同步复制。
5.2.1 原理图
半同步复制是建立在基本的主从复制基础上,利用插件完成半同步复制,传统的主从复制,不管从库是否正确获取到二进制日志,主库不断更新,半同步复制则当确认了从库把二进制日志写入中继日志才会允许提交,如果从库迟迟不返回ack
,主库会自动将半同步复制状态取消,进入最基本的主从复制模式。
5.2.2 配置前提与规划
配置的前提是已经配置好普通的异步复制一主一从了。 这里需要用到2个插件完成,一个用于主库,一个用于从库
规划如下:
IP | 系统 | MySQL版本 | 角色 |
---|---|---|---|
192.168.240.220 | CentOS7.4 | MySQL5.7.26 | 主库 |
192.168.240.221 | CentOS7.4 | MySQL5.7.26 | 从库 |
5.2.3 配置主库
主库内部执行
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #安装插件
mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON; #启用插件
5.2.4 配置从库
从库内部执行
#安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#启用插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=ON;
# 重启主从复制
mysql> stop slave;
mysql> start slave ;
5.2.5 验证是否成功
主库查看半同步复制状态
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |# 表示半同步连接的从库数量
| Rpl_semi_sync_master_net_avg_wait_time | 0 |# 主库等待从库回复的平均时间
| Rpl_semi_sync_master_net_wait_time | 0 |# 主库等等从库回复的总时间
| Rpl_semi_sync_master_net_waits | 1 |# 主库等待从库回复的总次数
| Rpl_semi_sync_master_no_times | 0 |# 主库关闭半同步复制次数
| Rpl_semi_sync_master_no_tx | 0 |# 从库未成功确认的提交次数
| Rpl_semi_sync_master_status | ON |# 半同步复制当前是否在主库运行,ON表示运行
| Rpl_semi_sync_master_timefunc_failures | 0 |# 调用时间函数时主库失败次数
| Rpl_semi_sync_master_tx_avg_wait_time | 764 |# 主库等待每个事务的平均时间
| Rpl_semi_sync_master_tx_wait_time | 764 |# 主库等待事务的总时间
| Rpl_semi_sync_master_tx_waits | 1 |# 主库等待事务的总次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |# 主服务器等待二进制坐标低于先前事件等待的事件的总次数。如果事务开始等待回复的顺序与其二进制日志事件的写入顺序不同,则会发生。
| Rpl_semi_sync_master_wait_sessions | 0 |# 当前正在等待从库回复的会话数
| Rpl_semi_sync_master_yes_tx | 1 |# 从服务器已成功确认的提交数
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
相关的变量更详细的信息可以看一下MySQL官方文档,或者MariaDB官方文档
https://mariadb.com/kb/en/library/semisynchronous-replication/#setup
https://dev.mysql.com/doc/refman/5.7/en/replication-semisync-monitoring.html
5.3 延迟复制
控制从库的SQL线程执行速度,二进制日志照常去主库取,但是存放到中继日志之后就延迟执行,如果主库被误操作,这时候对中继日志进行处理,就不用根据全备二进制日志恢复,节省了大部分的时间
5.3.1 前提与规划
配置的前提是已经配置好普通的异步复制一主一从了。
规划如下:
IP | 系统 | MySQL版本 | 角色 |
---|---|---|---|
192.168.240.220 | CentOS7.4 | MySQL5.7.26 | 主库 |
192.168.240.221 | CentOS7.4 | MySQL5.7.26 | 从库 |
5.3.2 设置的方式
# 停止同步
mysql> STOP SLAVE;
# 设置延迟300s,一般设置3-6个小时,单位是s
mysql> CHANGE MASTER TO MASTER_DELAY = 300;
# 启动同步
mysql> START SLAVE;
# 查看状态
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.240.220
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000006
Read_Master_Log_Pos: 308
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 314
Relay_Master_Log_File: bin.000006
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: 308
Relay_Log_Space: 525
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: 220
Master_UUID: 61083246-79f2-11e9-a325-000c295a96ec
Master_Info_File: /datadir/data/master.info
SQL_Delay: 300
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
查看状态验证是否正常主要关注SQL_Delay这个参数,可以查看延迟时间
5.4 基于GTID的主从复制
GTID(Global Transaction IDentifier),全局事务id,此标识符不仅在主服务器中是唯一的,而且在所有复制主库的从库中也是唯一的。
格式为
GTID = source_id:transaction_id
# source_id:用于标识MySQL实例,即MySQL服务器唯一的server_uuid,这个id在初始化数据库会生成并保存在数据目录下的auto.cnf文件中。
# transaction_id是一个序列号,由主服务器提交的事务的顺序决定。
5.4.1 优势
- GTID是连续的,保证数据的一致性
- 更简单实现故障转移,不需要再去找binlog位置
- 比传统的主从复制更安全
5.4.2 原理
- 事务在主服务器上执行并提交。此客户端事务被分配一个GTID,该GTID由主服务器的UUID和此服务器上尚未使用的最小非零事务序列号组成。GTID被写入主服务器的二进制日志
- 从库IO线程请求主库Binlog日志,主库Dump线程传送给从库保存在从库的中继日志中,读取这个GTID的值并设置gtid_next变量,即告诉从库,下一个要执行的GTID
- 从库SQL线程从中继日志读取GTID,然后对比从库的binlog是否有该GTID,如果有说明已经执行会忽略,如果没有就执行该事务,读取执行之前还会检查其他的session是否持有该GTID确保不会重复执行。
5.4.3 设置GTID的主从复制
5.4.3.1 修改主库与从库的配置文件,打开GTID模式
gtid_mode=ON # 启用GTID类型,否则就是普通的复制架构
enforce-gtid-consistency=true # 强制GTID的一致性
log-slave-updates=1 # slave更新是否记录日志,这个参数可以不加
启动主库
mysqld --daemonize
进入主库,添加复制的用户
SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'Hal@123';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;
5.4.3.2 启动主从复制
启动从库
mysqld --daemonize
从库填写主从相关信息
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.240.220',
MASTER_USER='rpl_user',
MASTER_PASSWORD='Hal@123',
MASTER_AUTO_POSITION = 1;
从库启动复制
mysql> start slave;
5.4.4 查看主从复制状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.240.220
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000003
Read_Master_Log_Pos: 766
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 967
Relay_Master_Log_File: bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 766
Relay_Log_Space: 1178
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: 220
Master_UUID: db527e9f-79fe-11e9-a527-000c295a96ec
Master_Info_File: /datadir/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: db527e9f-79fe-11e9-a527-000c295a96ec:1-4
Executed_Gtid_Set: db527e9f-79fe-11e9-a527-000c295a96ec:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
以下2个参数为yes表示成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
参考链接
- 《高性能MySQL》
- 《MySQL45讲》,作者丁奇
- 《MySQL技术内幕–InnoDB存储引擎》,作者姜承尧
- MySQL5.7 官方文档
- percona公司pxb官方文档