MySQL索引
基本概念
- 索引(Index)是帮助MySQL高效获取数据的数据结构。我们可以简单理解为：它是快速查找排好序的一种数据结构。
- 可以用来快速查询数据库表中的特定记录，所有的数据类型都可以被索引。
- Mysql索引主要有两种结构：B+Tree索引和Hash索引
优缺点
优点
- 可以大大提高MySQL的检索速度
- 索引大大减小了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机IO变成顺序IO
缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
- 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
- 对于非常小的表，大部分情况下简单的全表扫描更高效；
分类
普通索引
- 不应用任何限制条件的索引，该索引可以在任何数据类型中创建。
- 字段本身的约束条件可以判断其值是否为空或唯一。
- 创建该类型索引后，用户在查询时，便可以通过索引进行查询。
唯一性索引
- 使用UNIQUE参数可以设置唯一索引。
- 创建该索引时，索引的值必须唯一，通过唯一索引，用户可以快速定位某条记录
- 主键是一种特殊唯一索引。
全文索引
- 使用FULLTEXT参数可以设置索引为全文索引。
- 全文索引只能创建在CHAR、VARCHAR或者TEXT类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。
- 在默认情况下，应用全文搜索大小写不敏感。如果索引的列使用二进制排序后，可以执行大小写敏感的全文索引。
单列索引
- 顾名思义，单列索引即只对应一个字段的索引。
- 应用该索引的条件只需要保证该索引值对应一个字段即可。
- 可以包括普通、唯一、全文索引
多列索引
- 多列索引是在表的多个字段上创建一个索引。
- 该索引指向创建时对应的多个字段，用户可以通过这几个字段进行查询。
- 要想应用该索引，用户必须使用这些字段中的第一个字段。
创建索引
普通索引index
一个表中可以有多个index
字段的值可以重复，且可以赋值为null
通常在where条件中的字段上配置Index
index索引字段的标志为mul
创建表时创建索引
CREATE TABLE 表名(
字段列表,
index(字段名),
index(字段名),
);
- 在已有表中创建索引
CREATE INDEX 索引名 ON 表名(字段名);
- 查看索引
DESC 表名; # 注意观察Key这一列
或
SHOW INDEX FROM 表名 \G
- 删除索引
DROP INDEX 索引名 ON 库.表名;
用户及授权
授权
创建用户并授权
- 语法：
GRANT 权限列表 ON 库名.表名 TO '用户名'@'客户端地址' IDENTIFIED BY '密码' WITH GRANT OPTION;
- 权限列表：用户的操作权限，如
SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL - 表名：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用
*表示，如*.* - WITH GRANT OPTION：用户拥有授权权限
- 示例：
# 授予zzg用户在本地登陆的权限
mysql> grant select,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by 'NSD2021@tedu.cn';
# 授予zzg用户在任意地址登陆的权限
GRANT SELECT, INSERT, UPDATE(phone_number,email) ON nsd2021.employees to zzg@'%' IDENTIFIED BY 'NSD2021@tedu.cn';
客户端连接测试
# 安装mysql/mariadb客户端
[root@zzgrhel8 ~]# yum install -y mariadb
[root@zzgrhel8 ~]# mysql -h服务器 -u用户名 -p密码
相关查询指令
- 查看用户信息
SELECT USER();
- 显示登陆用户自己的权限
SHOW GRANTS;
- 管理员查看指定用户的权限，用户不存在则报错
SHOW GRANTS FOR 用户名@'客户端地址';
- 用户修改自己的密码
SET password=password('密码');
- 管理员修改指定用户密码
SET PASSWORD FOR 用户名@'客户端地址'=password('密码');
- 删除用户
DROP USER 用户名@'客户端地址';
授权库mysql
相关表
- user：记录已有的授权用户及权限。该表中主要关心host和user字段
- db：记录已有授权用户对数据库的访问权限。该表中主要关心host、db和user字段
-
tables_priv：记录已有授权用户对表的访问权限 -
columns_priv：记录已有授权用户对字段的访问权限
mysql> grant select,insert,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by 'NSD2021@tedu.cn';
// 查看所有授权用户
mysql> select user, host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | % |
| tom | % |
| zzg | % |
| mysql.sys | localhost |
| root | localhost |
| zzg | localhost |
+-----------+-----------+
6 rows in set (0.01 sec)
// 查询zzg@'%'的权限
mysql> show grants for zzg@'%';
mysql> select host, user, db from mysql.db;
+-----------+-----------+---------+
| host | user | db |
+-----------+-----------+---------+
| % | tom | nsd2021 |
| localhost | mysql.sys | sys |
+-----------+-----------+---------+
2 rows in set (0.00 sec)
mysql> select * from tables_priv where User like '%zzg%'\G
*************************** 1. row ***************************
Host: localhost
Db: nsd2021
User: zzg
Table_name: employees
Grantor: root@localhost
Timestamp: 0000-00-00 00:00:00
Table_priv: Select,Insert
Column_priv: Update
1 row in set (0.00 sec)
撤销权限
- 语法
REVOKE 权限列表 ON 库名.表名 FROM 用户名@'客户端地址';
- 示例：
# 查看用户有哪些权限
SELECT host, user FROM mysql.user;
# 查看权限
SHOW GRANTS FOR 用户名@'客户端地址';
# 撤回授权权限
REVOKE GRANT OPTION ON *.* FROM 用户名@'客户端地址';
# 撤回用户删除权限
REVOKE DELETE ON *.* FROM 用户名@'客户端地址';
# 创建tom用户，具有授权权限
mysql> grant all on *.* to tom@'%' identified by 'NSD2021@tedu.cn' with grant option;
# tom登陆后，创建jerry用户
[root@zzgrhel8 ~]# mysql -utom -pNSD2021@tedu.cn -h192.168.1.11
MySQL [(none)]> grant select on nsd2021.* to 'jerry'@'%' identified by 'NSD2021@tedu.cn';
root密码恢复
步骤
- 停止MySQL服务
- 跳过授权表启动MySQL服务程序
- 修改root密码
- 以正常方式重启MySQL服务程序
示例：
# 停止MySQL服务
[root@mysql1 ~]# systemctl stop mysqld
# 修改配置文件，跳过授权表启动MySQL服务程序
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
skip-grant-tables
... ...
# 启动服务
[root@mysql1 ~]# systemctl start mysqld
# 修改root密码
[root@mysql1 ~]# mysql
mysql> update mysql.user set authentication_string=password('123456')
-> where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# 以正常方式重启MySQL服务程序
[root@mysql1 ~]# systemctl stop mysqld
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
# skip-grant-tables
... ...
[root@mysql1 ~]# systemctl start mysqld
[root@mysql1 ~]# mysql -uroot -p123456
备份
物理备份
备份方法
- 拷贝/var/lib/mysql目录到目标即可
备份步骤
源主机
- 停止服务
[root@mysql1 ~]# systemctl stop mysqld
- 拷贝/var/lib/mysql到目标主机
[root@mysql1 ~]# scp -r /var/lib/mysql/* root@192.168.1.12:/var/lib/mysql
目标主机
- 修改属主属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql
- 启动服务
[root@mysql2 ~]# systemctl start mysqld
- 连接测试
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| nsd2021 |
+--------------------+
5 rows in set (0.00 sec)
使用mysqldump进行逻辑备份
备份策略
- 完全备份：备份所有数据（单一或多个库、单一或多张表、整个数据库）
- 只备份更新数据：
- 差异备份：备份自完全备份后产生的数据
- 增量备份：备份自上次备份之后产生的数据
完全备份
命令格式
- 备份命令
mysqldump -u用户名 -p密码 > 路径/文件名.sql
- 恢复命令
mysql -u用户名 -p密码 < 路径/备份文件名.sql
备份示例
备份所有库
- 源数据库
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn --all-databases > dbbackup/alldb.sql
或
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn -A > dbbackup/alldb.sql
# 拷贝文件到目标服务器
[root@mysql1 ~]# rsync -r dbbackup root@192.168.1.12:/root/
- 目标数据库
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn < dbbackup/alldb.sql
备份某一个库
- 源数据库
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn nsd2021 > dbbackup/tedu_db.sql
# 拷贝文件到目标服务器
[root@mysql1 ~]# rsync -r dbbackup root@192.168.1.12:/root/
- 目标数据库
# 删除库，以便测试结果
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> drop database nsd2021;
mysql> CREATE DATABASE nsd2021 DEFAULT CHARSET utf8mb4;
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < dbbackup/tedu_db.sql
备份某一张表
- 源数据库
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn nsd2021 salary > dbbackup/nsd2021_salary.sql
# 拷贝文件到目标服务器
[root@mysql1 ~]# rsync -r dbbackup root@192.168.1.12:/root/
- 目标数据库
# 清空表，以便测试结果
mysql> use nsd2021;
mysql> truncate salary;
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < dbbackup/nsd2021_salary.sql
备份某多个库
- 源数据库
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn -B nsd2021 mysql > dbbackup/twodb.sql
# 拷贝文件到目标服务器
[root@mysql1 ~]# rsync -r dbbackup root@192.168.1.12:/root/
- 目标数据库
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn < dbbackup/twodb.sql
备份多张表
- 源数据库
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn nsd2021 employees salary > dbbackup/nsd2021_employees_salary.sql
# 拷贝文件到目标服务器
[root@mysql1 ~]# rsync -r dbbackup root@192.168.1.12:/root/
- 目标数据库
# 清空表，以便测试结果
mysql> truncate salary;
mysql> delete from employees;
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn nsd2021 < dbbackup/nsd2021_employees_salary.sql
增量备份
binlog日志
binlog日志概述
- 也叫做二进制日志
- 它是MySQL服务日志文件的一种
- 默认没有启用
- 记录除查询之外的所有SQL命令
- 可用于数据的备份和恢复
- 它是MySQL主从同步的必要条件
启用binlog日志
- 修改/etc/my.cnf启用日志
| 配置项 | 用途 |
|---|---|
| server_id=数字 | 指定服务器id值（1-255） |
| log-bin/log_bin=目录/文件名 | 启用binlog日志 |
| max_binlog_size=数值m | 日志文件容量，默认1GB |
- 启用 binlog
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin
... ...
[root@mysql1 ~]# systemctl restart mysqld
// 查看结果
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysql1-bin.000001 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@mysql1 ~]# ls /var/lib/mysql/mysql1-bin.*
/var/lib/mysql/mysql1-bin.000001 /var/lib/mysql/mysql1-bin.index
手动创建binlog日志文件
- binlog文件默认存在/var/lib/mysql目录下
- 也可以手工进行修改
// 创建用于保存日志文件的目录
[root@mysql1 ~]# mkdir /mybinlog
[root@mysql1 ~]# chown mysql:mysql /mybinlog/
// 修改配置文件
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin = /mybinlog/mylog
... ...
// 验证
[root@mysql1 ~]# systemctl restart mysqld
[root@mysql1 ~]# ls /mybinlog/
mylog.000001 mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 | 154 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 新建binlog日志文件
// 方法一：重启mysqld服务
[root@mysql1 ~]# systemctl restart mysqld
[root@mysql1 ~]# ls /mybinlog/
mylog.000001 mylog.000002 mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000002 | 154 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
// 方法二：flush指令
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000003 | 154 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@mysql1 ~]# ls /mybinlog/
mylog.000001 mylog.000002 mylog.000003 mylog.index
// 方法三：备份时刷新日志
[root@mysql1 ~]# mysqldump -uroot -pNSD2021@tedu.cn --flush-logs nsd2021 > dbbackup/nsd2021_full.sql
[root@mysql1 ~]# ls /mybinlog/
mylog.000001 mylog.000002 mylog.000003 mylog.000004 mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000004 | 154 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
删除已有binlog日志
- 删除指定编号之前的日志文件
// 删除mylog.000003（不包含）之前的日志
mysql> purge master logs to "mylog.000003";
Query OK, 0 rows affected (0.05 sec)
[root@mysql1 ~]# ls /mybinlog/
mylog.000003 mylog.000004 mylog.index
// 删除所有日志，重新新日志
mysql> reset master;
Query OK, 0 rows affected (0.13 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 | 154 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@mysql1 ~]# ls /mybinlog/
mylog.000001 mylog.index
binlog日志内容
通过binlog日志修改数据库
- 向departments表中插入数据
mysql> use nsd2021;
mysql> INSERT INTO departments(dept_name) VALUES ('sales1');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO departments(dept_name) VALUES ('sales2');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO departments(dept_name) VALUES ('sales3');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO departments(dept_name) VALUES ('sales4');
Query OK, 1 row affected (0.05 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 | 1274 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
[root@mysql1 ~]# mysqlbinlog /mybinlog/mylog.000001
- 在目标主机上通过binlog同步源主机上的数据
# 将binlog日志拷贝到目标主机
[root@mysql1 ~]# rsync -r /mybinlog 192.168.1.12:/root
# 在目标主机上执行一遍binlog日志
[root@mysql2 ~]# mysqlbinlog mybinlog/mylog.000001 | mysql -uroot -pNSD2021@tedu.cn
修改binlog记录格式
- binlog日记记录方式
- row：行模式
- statement：陈述模式
- mixed：混合模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
- 修改日志记录格式
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin = /mybinlog/mylog
binlog_format = "mixed"
... ...
[root@mysql1 ~]# systemctl restart mysqld
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)
- 检查日志
# 修改数据
mysql> use nsd2021;
mysql> INSERT INTO departments(dept_name) VALUES('sales5');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO departments(dept_name) VALUES('sales6');
Query OK, 1 row affected (0.03 sec)
# 查看日志
[root@mysql1 ~]# mysqlbinlog /mybinlog/mylog.000002
通过binlog日志修改指定范围内的数据
- 执行删除操作
mysql> delete from departments where dept_name like 'sales_';
- 拷贝日志文件到目标服务器
[root@mysql1 ~]# rsync -r /mybinlog 192.168.1.12:/root
- 在目标主机上查看日志，找到要恢复数据的起始和结束偏移量
# 读取日志，找到插入sales5日志上面的at偏移量作为起始值，找到插入sales6日志下面的COMMIT之后的at偏移量作为结束值
[root@mysql2 ~]# mysqlbinlog mybinlog/mylog.000002
[root@mysql2 ~]# mysqlbinlog --start-position=328 --stop-position=810 mybinlog/mylog.000002 | mysql -uroot -pNSD2021@tedu.cn
Percona
Percona概述
Percona解决的问题
-
物理备份缺点
- 跨平台性差
- 备份时间长、冗余备份、浪费存储空间
-
mysqldump备份缺点
- 效率较低、备份和还原速度慢、锁表
- 备份过程中，数据插入和更新操作被阻塞
Percona特点
- Percona提供的xtrabackup是一款强大的在线热备份工具
- 备份过程中不锁库表，适合生产环境
- 由专业组织Percona提供（改进MySQL分支）
- 主要包含两个组件：
- xtrabackup：C程序，支持InnoDB/XtraDB
- innobackupex：以Perl脚本封装xtrabackup，还支持MyISAM
安装
- 在yum服务器上生成yum源
[root@zzgrhel8 ~]# cd /linux-soft/4/mysql/
[root@zzgrhel8 mysql]# cp percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm libev-4.15-1.el6.rf.x86_64.rpm /var/www/html/mysql
[root@zzgrhel8 mysql]# cd /var/www/html/mysql
[root@zzgrhel8 mysql]# createrepo -v .
- 在数据库服务器上安装percona
[root@mysql1 ~]# yum clean all
[root@mysql1 ~]# yum install -y percona-xtrabackup-24
[root@mysql2 ~]# yum clean all
[root@mysql2 ~]# yum install -y percona-xtrabackup-24
Percona完全备份数据库
语法
- 备份语法
innobackupex -u 用户名 -p 密码 备份目录名 [--no-timestamp]
# 备份目录名不用提前创建
- 恢复语法
innobackupex --apply-log 备份目录名 # 准备恢复数据
innobackupex --copy-back 备份目录名 # 复制数据
- 选项说明
| 常用选项 | 含义 |
|---|---|
| -u | 用户名 |
| -p | 密码 |
| --no-timestamp | 不用日期命名备份文件存储的子目录名 |
| --apply-log | 准备恢复数据 |
| --copy-back | 拷贝数据 |
| --databases | --databases="库名" # 1个库<br />--databases="库1 库2" # 多个库<br />--databases="库1.表" # 1张表 |
示例
- 源服务器完全备份数据到/pbackup/目录
# 方法一：在备份目录下生成日期时间子目录
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn /pbackup/fullbackup
[root@mysql1 ~]# ls /pbackup/fullbackup/
2021-04-07_11-42-08
# 方法二：不生成子目录
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn /pbackup/full20210407 --no-timestamp
- 目标服务器恢复数据
# 将完全备份目录拷贝到目标服务器
[root@mysql1 ~]# rsync -r /pbackup root@192.168.1.12:/root
# 查看备份目录状态（非必须步骤）
[root@mysql2 ~]# cat pbackup/full20210407/xtrabackup_checkpoints
backup_type = full-backuped # 已完全备份
from_lsn = 0 # 起始日志序列号
to_lsn = 3639394 # 结束日志序列号
last_lsn = 3639403
compact = 0
recover_binlog_info = 0
# 准备恢复数据
[root@mysql2 ~]# innobackupex --apply-log /root/pbackup/full20210407/
# 查看备份目录状态（非必须步骤）
[root@mysql2 ~]# cat pbackup/full20210407/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 3639394
last_lsn = 3639403
compact = 0
recover_binlog_info = 0
# 停止mysqld服务
[root@mysql2 ~]# systemctl stop mysqld
# 恢复时要求mysql工作目录为空
[root@mysql2 ~]# rm -rf /var/lib/mysql/*
# 恢复数据
[root@mysql2 ~]# innobackupex --copy-back /root/pbackup/full20210407/
# 修改工作目录的属主和属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql
# 启动服务
[root@mysql2 ~]# systemctl start mysqld
使用完全备份恢复单张表
表空间
- 数据驻留在磁盘上的位置，称为数据目录，如/var/lib/mysql
- 从MySQL 5.6.6版开始，用户可以创建并指定他们想要存储数据的表空间，从而实现数据操作和恢复过程的吞吐量。
- InnoDB的每表文件功能为每个表提供了单独的.ibd数据和索引文件，代表一个单独的通用表空间。这样数据库中的每个表都可以指向数据目录的不同位置。
恢复单表步骤
- 删除待恢复表的表空间 (表名.ibd)
- 导出备份目录中的表信息
- 拷贝表信息文件到数据库目录下
- 修改表信息文件的所有者及组用户为mysql
- 从备份目录导入表空间
- 删除数据库目录下的表信息文件
- 验证：查看表记录
恢复示例
- 误删除表内容
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> truncate salary;
- 恢复准备：删除表空间
mysql> alter table salary discard tablespace;
[root@mysql2 ~]# ls /var/lib/mysql/nsd2021/ # salary.ibd消失
- 导出表信息
[root@mysql2 ~]# innobackupex --apply-log --export /root/pbackup/fullbackup/2021-04-07_11-42-08/
- 拷贝表信息文件到数据库目录下
[root@mysql2 ~]# cp /root/pbackup/fullbackup/2021-04-07_11-42-08/nsd2021/salary.{cfg,exp,ibd} /var/lib/mysql/nsd2021/
- 修改表信息文件的所有者及组用户为mysql
[root@mysql2 ~]# chown mysql:mysql /var/lib/mysql/nsd2021/salary.*
- 导入表空间
mysql> alter table salary import tablespace;
- 删除数据库目录下的表信息文件
[root@mysql2 ~]# rm -f /var/lib/mysql/nsd2021/salary.{cfg,exp}
- 验证：查看表记录
mysql> select count(*) from salary;
+----------+
| count(*) |
+----------+
| 8590 |
+----------+
1 row in set (0.00 sec)
Percona增量备份
增量备份基础
备份自上次备份以来的数据
一般来说，需要有一个完全备份
备份语法
innobackupex -u 用户名 -p 密码 --incremental 备份目录 --incremental-basedir=备份目录名 --no-timestamp
- 恢复语法
# 准备恢复数据
innobackupex --apply-log --redo-only 首次备份目录名
# 合并数据
innobackupex --apply-log --redo-only 首次备份目录名 --incremental-dir=备份目录名
# 恢复数据
innobackupex --copy-back 首次备份目录名
| 常用选项 | 含义 |
|---|---|
| --apply-log | 准备恢复数据 |
| --redo-only | 日志合并 |
| --incremental 目录名 | 新数据存放目录 |
| --incremental-basedir=目录名 | 备份新数据参考目录 |
| --incremental-dir=目录名 | 合并数据存放目录 |
增量备份示例
- 在源服务器上添加数据
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('sales10');
Query OK, 1 row affected (0.15 sec)
mysql> insert into departments(dept_name) values('sales11');
Query OK, 1 row affected (0.13 sec)
mysql> insert into departments(dept_name) values('sales12');
Query OK, 1 row affected (0.15 sec)
- 在源服务器上进行增量备份
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn --incremental /pbackup/incr20210407 --incremental-basedir=/pbackup/full20210407/ --no-timestamp
[root@mysql1 ~]# cat /pbackup/incr20210407/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 3639394
to_lsn = 3643003
last_lsn = 3643012
compact = 0
recover_binlog_info = 0
- 在目标服务上进行恢复数据
# 在目标服务器上删除原有日志
[root@mysql2 ~]# rm -rf /root/pbackup/
# 将日志拷贝到目标服务器
[root@mysql1 ~]# rsync -r /pbackup 192.168.1.12:/root/
# 准备恢复数据
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/
[root@mysql2 ~]# cat /root/pbackup/full20210407/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 3639394
last_lsn = 3639403
compact = 0
recover_binlog_info = 0
# 合并日志
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/ --incremental-dir=/root/pbackup/incr20210407/
[root@mysql2 ~]# cat /root/pbackup/full20210407/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 3643003
last_lsn = 3643012
compact = 0
recover_binlog_info = 0
# 停止目标服务器mysqld服务
[root@mysql2 ~]# systemctl stop mysqld
# 删除工作目录
[root@mysql2 ~]# rm -rf /var/lib/mysql/*
# 恢复数据
[root@mysql2 ~]# innobackupex --copy-back /root/pbackup/full20210407/
# 修改属主属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql
# 启动目标服务器mysqld服务
[root@mysql2 ~]# systemctl start mysqld
# 验证
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments;
- 重复增量备份
# 源服务器增加数据
[root@mysql1 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> insert into departments(dept_name) values('sales13');
Query OK, 1 row affected (0.06 sec)
mysql> insert into departments(dept_name) values('sales14');
Query OK, 1 row affected (0.06 sec)
mysql> insert into departments(dept_name) values('sales15');
Query OK, 1 row affected (0.06 sec)
# 源服务器增量备份
[root@mysql1 ~]# innobackupex -uroot -pNSD2021@tedu.cn --incremental /pbackup/incr20210407-2 --incremental-basedir=/pbackup/incr20210407/ --no-timestamp
# 将日志拷贝到目标服务器
[root@mysql1 ~]# scp -r /pbackup/incr20210407-2/ 192.168.4.12:/root/pbackup/
# 准备恢复数据
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/
# 合并日志
[root@mysql2 ~]# innobackupex --apply-log --redo-only /root/pbackup/full20210407/ --incremental-dir=/root/pbackup/incr20210407-2/
# 停止目标服务器mysqld服务
[root@mysql2 ~]# systemctl stop mysqld
# 删除工作目录
[root@mysql2 ~]# rm -rf /var/lib/mysql/*
# 恢复数据
[root@mysql2 ~]# innobackupex --copy-back /root/pbackup/full20210407/
# 修改属主属组
[root@mysql2 ~]# chown -R mysql:mysql /var/lib/mysql
# 启动目标服务器mysqld服务
[root@mysql2 ~]# systemctl start mysqld
# 验证
[root@mysql2 ~]# mysql -uroot -pNSD2021@tedu.cn
mysql> use nsd2021;
mysql> select * from departments;
