1/sql小技巧与常用语句

1/sql小技巧

1/退出mysql方法

exit
quit
ctrl + d

2/忘记密码

临时方法
跳过grant表授权，进入安全模式后并后台运行
停止mysqld服务
systemctl stop mysqld
进入安全模式
mysqld_safe —skip-grant-tables &
进入数据库
mysql
应用库并修改密码
use mysql;
update user set password=password(‘999999’) where user=’root’;
刷新生效
flush privileges;
重启mysqld服务
systemctl start mysqld

永久方法就是在配置文件
[mysqld]
skip_grant_tables
重启服务，如上的修改步骤就可以了

3/tab补全

永久方法是修改配置文件
[mysql]
auto-rehash
重启服务

临时方法是登入数据库的时候带入参数—auto-rehash

4/支持中文

临时支持
mysql>charset utf8;

永久支持
修改配置文件
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
重启服务

2/常用sql语句

1/常用语句

查看表结构，缩写desc $table;
describe $table;

创建库，表
create database $database;
create table $table (字段1 char(20) not null,字段2 char(30) default,primary key(字段x))；
#使用库
use 库；

删除库，表
drop table $table;
drop database $database;

表中插入数据
insert into $table(字段1，字段2…) values(字段1的值，字段2的值…)；

更新表数据
update $table set 字段1=值1 where 条件某字段x=条件某值x;
update auth.user set user_passwd=password(‘’) where user_name=’abc’;

删除某条数据
delete from $table where 字段x=值x；

表查看
select from $table; #查看表的全部信息
select 字段1，字段2 from $table; #查看表中字段1和字段2的信息
select 字段1，字段2 from $table where 字段x=值x; #查看表中字段x的字段1和字段2的信息
select from $table where $某列 like ‘%abc%’; #查看表中某列包含abc的表信息，%表示模糊查找

用户授权
grant all privileges on . to ‘rep’@’192.168.0.%’ identified by ‘999999’;
show grants; #查看所有授权
show grants for ‘user’@’address’;
revoke
flush privileges; #刷新授权：

服务器错误或警告信息
show errors;
show warnings;

显示当前登录用户
select user();

显示当前时间
select now();
select user(),now();

显示当前数据库
select databas();

显示当前服务器状态
status;

2/备份恢复

备份操作
mysqldump -u$user -p$password 选项 库名 表名 > 备份路径
常见选项
—all-databases #指定所有库
—database #指定库
—opt 此Mysqldump命令参数是可选的，如果带上这个选项代表激活了Mysqldump命令的quick，add-drop-table，add-locks，extended-insert，lock-tables参数，也就是通过–opt参数在使用Mysqldump导出Mysql数据库信息时不需要再附加上述这些参数

恢复操作
mysql -u$user -p$password 数据库名 < 备份文件路径

3/用户操作

创建用户
create user ‘user’@’ip’ identified by 密码; #ip可以为ip段：192.168.0.%；也可以是所有：%

删除用户
drop user ‘user’@’ip’;

重命名用户名及主机
rename user ‘user’@’ip’ to ‘new-user’@’new-ip’;

设置密码
#修改当前用户的密码
set password=password(‘密码’);
#修改其他用户
set password for ‘user’@’ip’=password(‘密码’);

4/权限操作

授权操作
grant 权限列表 on 库名.表名 to ‘user’@’ip’ identified by ‘密码’;
#权限列表比如create,select等等,all是所有；库名.表名用.表示所有
#常见权限列表：
#alter/create/create user/delete/drop/insert/replication slave/select/show databases/update

查看权限
show grants; #查看所有用户权限
show grants for ‘user’@’ip’; #查看具体某用户权限

撤销权限
revoke 权限列表 on 库名.表名 from ‘user’@’ip’;

2/索引和事务

索引

1/什么是索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
又比如，

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间。

2/索引的分类

1-普通索引
2-唯一性索引
3-主键 #创建表时所指定的primary key
4-全文索引 #其类型为fulltext，可以在varchar或者text类型的列上创建。如贴吧的文本，网页网站内容等
5-到了索引与多列索引

3/索引操作

创建普通索引
create index 索引名称 on $table(字段x);

查看索引
show index from $table\G;
show keys from $table\G;

创建唯一性索引
create unique index 索引名称 on $table(字段x);

创建主键索引
create table 表名 (字段x,primary key(某字段x))；
#若创建表是忘记指定主键，更新表
alter $table add primary key(某字段x);

删除索引
drop index 索引名称 on $table;
alter $table drop index 索引名称;
alter $table drop primary key;

事务

1/什么是事务

事务是逻辑上的一组操作，组成这个操作的各个单元，要么全部成功要么全部失败，这个特性就是事务
注意：mysql数据库支持事务，但是必须是innoDB引擎

事务是由一步或几步数据库操作序列组成逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行。

程序和事务是两个不同的概念。一般而言：一段程序中可能包含多个事务。（说白了就是几步的数据库操作而构成的逻辑执行单元）

事务具有四个特性：原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持续性(Durability)。
这四个特性也简称ACID性。

(1)原子性：事务是应用中最小的执行单位，就如原子是自然界最小颗粒，具有不可再分的特征一样。事务是应用中不可再分的最小逻辑执行体。（最小了，不可再分了）
(2)一致性：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。（说罢了就是白狗变成了黑狗，不能出现斑点狗！）
(3)隔离性：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能相互影响。（说白了，就是你做你的，我做我的！）

(4)持续性：持续性也称为持久性，指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库。（说白了就是一条道跑到黑）

MySQL如何支持事务？

MYSQL的事务处理主要有两种方法

　　
1.用begin,rollback,commit来实现 　　　
begin开始一个事务 　　　
rollback事务回滚 　　
commit 事务确认 　　
2.直接用set来改变mysql的自动提交模式 　　
mysql默认是自动提交的，也就是你提交一个query，就直接执行！
可以通过
set autocommit = 0 禁止自动提交 　　
set autocommit = 1 开启自动提交
来实现事务的处理

3/Mysql的备份与恢复

1/完全备份

备份

备份的方法有
完全备份 #整个备份的方法
差异备份 #整个备份后，后续每次只对有变化的进行备份
增量备份 #雷同上一种备份方法
mysqldump -u$user -p$password 选项 库名 表名 > 备份路径
常用选项
-A —all-databases 备份所有数据库
-B —databases 备份指定数据库，可以同时指定多个库名
-d 只备份表结构
-t 只备份表数据
-T —tab 将表结构和表数据分开存放，数据为纯文本格式，而非sql语句，此参数后跟路径
-F 刷新binlog日志，生成一个新的mysql-bin文件
-master-data=[1|2] 1表示备份时记录文件的及pos，2表示以注释的形式记录，常用的为2
-x —lock-all-tables 备份时锁定所有表
—single-transaction 不锁表备份，适用于innodb引擎
-R 备份存储过程和函数
—opt 备份时启用的优化选项，包括关闭缓冲区，导出时把更多的语句写在更少的sql中
—allow-keywords 允许使用保留字做为表名

恢复

1/source恢复

进入数据库之后
mysql> source 备份路径; 即可恢复

2/导入恢复

mysql -u$user -p$password < 备份路径 #将所有备份的库表导入
mysql -u$user -p$password 库名 < 备份路径 #将备份的表导入到某库中

2/增量备份

增量备份就是备份自上一次备份之后增加或改变的文件内容

3/增量恢复

1)一般恢复
mysqlbinlog [—no-defaults] 增备文件|mysql -u$user -p$password
2)时间点恢复
mysqlbinlog [—no-defaults] —start-datatime=’年-月-日 时:分:秒’ —stop-datatime=’年-月-日 时:分:秒’ 增备文件|mysql -u$user -p$password
start或stop时间点可以是一个，表示从某个点开始到结尾;表示从开始到某个时间点结束。
3)基于位置
mysqlbinlog [—no-defaults] —start-position=’操作id’ —stop-position=’操作id’ 增备文件|mysql -u$user -p$password
start或stop位置点同上

备注：
@1 备份前可以先刷新日志，然后进行被
linux命令：mysqladmin -u$user -p$password flush-logs
mysql命令：进入mysql后flush-logs

@2 mysqlbinlog: [ERROR] unknown variable ‘default-character-set=utf8mb4’
当我们在my.cnf中添加default-character-set=utf8mb4选项，那么在mysqlbinlog查看binlog时就会报错。
解决方案：.mysqlbinlog 后面添加 —no-defaults 选项
—no-defaults #不要从任何选项文件中读取默认选项，登录文件除外。

备份脚本：

MySQL数据库完全备份脚本
#!/bin/bash
#MySQL数据库完全备份脚本
#设置登录变量
MY_USER=”root”
MY_PASS=” 123123
MY_HOST=”localhost”
MY_CONN=-u$MY_USER -$pMY_PASS -h$MY_HOST”
#设置备份的数据库(或表)
MY_DB=”client”
#定义备份路径、工具、时间、文件名
BF_DIR=”/mysql_bak/wanbei”
BF_CMD=”/usr/bin/mysqldump”
BF_TIME=$(date +%Y%m%d-%H%M)
NAME=”$MY_DB-SBF_TIME”
#备份为.sql脚本， 然后打包压缩(打包后删除原文件)
[-d $BF_DIR] || mkdir -p $BF_DIR
cd $BF_DIR
$BF_CMD $MY_CONN —databases $MY_DB>$NAME.sql
/bin/tar -zcf $NAME.tar.gz $NAME .sql —remove&>/dev/null


增量备份脚本
#!/bin/bash
#MySQL数据库增量备份脚本
#设置登录变量
MY_USER=”root”
MY_PASS=”123123”
MY_HOST=”localhost”
MY_CONN=”-u$MY_USER -p$MY_PASS -h$MY_HOST”
#定义备份路径、工具、二进制日志前缀、二进制日志存放路径
BF_TIME=”$(date+%Y%m%d)”
BF_DIR=”/mysql_bak/zengbei/$BF_TIME”
CMD=”/usr/bin/mysqladmin”
QZ=”mysql-bin”
LOG_DIR=”/var/lib/mysql”
#拷贝二进制日志
[-d $BF_DIR] || mkdir -p $BF_DIR
$CMD $MY_CONN flush-logs /bin/cp -p $(ls $LOG_DIR/$QZ.*|awk -v RS=”” ‘{print $(NF-2)}’) $BF_DIR

3-1/第三方备份工具Xtrabackup

包下载地址：
https://github.com/percona/percona-xtrabackup/releases/tag/percona-xtrabackup-2.4.22
percona-xtrabackup-percona-xtrabackup-2.4.22.tar.gz
参考地址：
https://www.cnblogs.com/gomysql/p/3650645.html
https://www.cnblogs.com/yuyue2014/p/3705448.html
https://www.cnblogs.com/polestar/p/5636278.html
https://cloud.tencent.com/developer/article/1119183
https://www.cnblogs.com/nmap/p/6722400.html
https://www.codenong.com/cs105136472/
https://cloud.tencent.com/developer/article/1639574 #这个后面有其他命令技巧，可压缩等等的类型
https://blog.csdn.net/allway2/article/details/96762792
#学习用的这个地址，我安装的是mysql8，所以也只能用xbackup8了
https://www.cnblogs.com/paul8339/p/13936318.html #全量和增量的备份测试及部分命令参数说明
https://blog.csdn.net/ichen820/article/details/101540265 *这个命令介绍比较好

Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。

Xtrabackup中主要包含两个工具：

xtrabackup：是用于热备份innodb, xtradb表中数据的工具，不能备份myisam引擎的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，可以支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。还有就是myisam不支持增量备份。以及数据表结构。

1 xtrabackup原理

官方文档：http://www.percona.com/docs/wiki/doku.php?id=percona-xtrabackup:xtrabackup_manual

XtraBackup对Innodb的备份之所以是热备，无需锁表，是基于Innodb自身的崩溃恢复机制，它首先复制所有的Innodb数据文件，这样复制出来的文件肯定是不一致的，然后对每个文件进行崩溃恢复处理，最终达到一致。就和MySQL在启动Innodb的时候一样，会通过比较数据文件头和redo log文件头信息来检查数据是否是一致的，如果不一致就尝试通过前滚（把redo log中所有提交的事务写入数据文件）和回滚（从数据文件中撤销所有redo log中未提交的事务引起的修改）来使数据达到最终一致。

XtraBackup在启动的时候会记录一个LSN(log sequence number)(日志序列号)，然后就把所有的Innodb数据文件复制出来，这样复制出来的数据文件是不一致的，但是XtraBackup会在后台运行一个进程把所有对redo log file的修改记录下来，只要有了这个数据，就能进行崩溃恢复。之所以要额外记录下来，是因为MySQL自身的redo log file是可重用的。

以上的操作是由xtrabackup二进制程序（比如xtrabackup_55）完成的，如果使用innobackupex 脚本，刚才的步骤完成以后，innobackupex就会去备份MyISAM表和.frm文件，这时要保证数据的一致性就会先锁表了，通过FLUSH TABLES WITH READ LOCK命令锁表然后把文件复制出来，再释放掉这个锁。

在恢复数据的时候，要经过prepare（recovery）和restore两个步骤。
prepare结束以后，Innodb的表恢复到了复制Innodb文件结束的时间点，这个时间点也就是锁表复制MyISAM表的起点，所以最终数据是一致的。
一般我们在恢复的时候执行两次prepare，是因为第二次prepare会帮助我们生成redo log文件，从而加快MySQL数据库启动的速度。

增量备份：

在InnoDB中，每个page中都记录LSN信息，每当相关数据发生改变，page的LSN就会自动增加，xtrabackup的增量备份就是依据这一原理进行的。

(1)首先完成一个完全备份，并记录下此时检查点LSN；
(2)然后增量备份时，比较表空间中每个页的LSN是否大于上次备份的LSN，若是则备份该页并记录当前检查点的LSN。

具体来说，首先在logfile中找到并记录最后一个checkpoint（“last checkpoint LSN”），然后开始从LSN的位置开始拷贝InnoDB的logfile到xtrabackup_logfile；然后开始拷贝全部的数据文件.ibd；在拷贝全部数据文件结束之后，才停止拷贝logfile。

所以xtrabackup_logfile文件在并发写入很大时也会变得很大，占用很多空间，需要注意。另外当我们使用—stream=tar或者远程备份—remote-host时默认使用/tmp，但最好显示用参数—tmpdir指定，以免把/tmp目录占满影响备份以及系统其它正常服务。

因为logfile里面记录全部的数据修改情况，所以即使在备份过程中数据文件被修改过了，恢复时仍然能够通过解析xtrabackup_logfile保持数据的一致。

一、xtrabackup的七大特点
1.直接拷贝物理文件，备份和恢复数据的速度非常快、安全可靠
2.在备份期间执行的事务不会间断，备份InnoDB数据不影响业务
3.备份期间不增加太多数据库的性能压力
4.支持对备份的数据自动校验
5.支持全量、增量、压缩备份及流备份
6.技持在线迁移表以及快速创建新的从库
7.支持几乎所有版本的MySQL和MariaDB

二、xtrabackup的备份原理及过程
第一步：
记录当前redo日志位置（即对应的LSN号）
第二步：
在后台启动一个进程持续监视redo日志文件的变化
第三步：
同时将变化的信息记录到xtrabackup_logfile中
第四步：
针对所有的innodb数据文件进行备份（复制）
第五步：
备份完成后执行“flush tables with read lock”，对数据库锁表
第六步：
备份（复制）MyISAM等非事务引擎的数据文件
第七步：
待InnoDB、MyISAM数据文件、redo日志备份完毕后，获取binlog二进制日志位置点信息
第八步：
执行unlock tables解锁命令，回复数据库可读写状态

2 innobackupex原理

官方文档：http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/how_innobackupex_works.html

2.1 备份


备份开始时首先会开启一个后台检测进程，实时检测mysql redo的变化，一旦发现redo中有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中。之后复制innodb的数据文件和系统表空间文件ibdata1，待复制结束后，执行flush tables with read lock操作，复制.frm，MYI，MYD，等文件（执行flush tableswith read lock的目的是为了防止数据表发生DDL操作，并且在这一时刻获得binlog的位置）最后会发出unlock tables，把表设置为可读可写状态，最终停止xtrabackup_log。

如果在程序启动阶段未指定模式，innobackupex将会默认以备份模式启动。

默认情况下，此脚本以—suspend-at-end选项启动xtrabackup，然后xtrabackup程序开始拷贝InnoDB数据文件。当xtrabackup程序执行结束，innobackupex将会发现xtrabackup创建了xtrabackupsuspended2文件，然后执行FLUSH TABLES WITH READ LOCK，此语句对所有的数据库表加读锁，然后开始拷贝其他类型的文件。

如果—ibbackup未指定，innobackupex将会自行尝试确定使用的xtrabackup的binary。其确定binary的逻辑如下：首先判断备份目录中xtrabackup_binary文件是否存在，如果存在，此脚本将会依据此文件确定使用的xtrabackup binary。否则，脚本将会尝试连接database server，通过server版本确定binary。如果连接无法建立，xtrabackup将会失败，需要自行指定binary文件。

在binary被确定后，将会检查到数据库server的连接是否可以建立。其执行逻辑是：建立连接、执行query、关闭连接。若一切正常，xtrabackup将以子进程的方式启动。

FLUSH TABLES WITH READ LOCK是为了备份MyISAM和其他非InnoDB类型的表，此语句在xtrabackup已经备份InnoDB数据和日志文件后执行。在这之后，将会备份 .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, .par, and .opt 类型的文件。

当所有上述文件备份完成后，innobackupex脚本将会恢复xtrabackup的执行，等待其备份上述逻辑执行过程中生成的事务日志文件。接下来，表被解锁，slave被启动，到server的连接被关闭。接下来，脚本会删掉xtrabackupsuspended2文件，允许xtrabackup进程退出。

2.2 恢复


这一阶段会启动xtrabackup内嵌的innodb实例，回放xtrabackup日志xtrabackup_log，将提交的事务信息变更应用到innodb数据/表空间，同时回滚未提交的事务(这一过程类似innodb的实例恢复）。恢复过程如下图：

为了恢复一个备份，innobackupex需要以—copy-back选项启动。

innobackupex将会首先通过my.cnf文件读取如下变量：datadir, innodb_data_home_dir, innodb_data_file_path, innodb_log_group_home_dir，并确定这些目录存在。

接下来，此脚本将会首先拷贝MyISAM表、索引文件、其他类型的文件（如：.frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, par and .opt files），接下来拷贝InnoDB表数据文件，最后拷贝日志文件。拷贝执行时将会保留文件属性，在使用备份文件启动MySQL前，可能需要更改文件的owener（如从拷贝文件的user更改到mysql用户）。

2.3 增备

innobackupex增量备份过程中的”增量”处理，其实主要是相对innodb而言，对myisam和其他存储引擎而言，它仍然是全拷贝(全备份)
“增量”备份的过程主要是通过拷贝innodb中有变更的”页”（这些变更的数据页指的是”页”的LSN大于xtrabackup_checkpoints中给定的LSN）。增量备份是基于全备的，第一次增备的数据必须要基于上一次的全备，之后的每次增备都是基于上一次的增备，最终达到一致性的增备。增量备份的过程如下，和全备的过程很类似，区别仅在第2步。

2.4 增恢

和全备恢复类似，也需要两步，一是数据文件的恢复，如图4，这里的数据来源由3部分组成：全备份，增量备份和xtrabackup log。二是对未提交事务的回滚，如图5所示：

3 xtrabackup安装

XtraBackup
Percona XtraBackup是世界上唯一的开源，免费的MySQL热备份软件，可以为InnoDB和XtraDB 数据库执行非阻塞备份。

注：随着Percona XtraBackup 8.0 的推出，Percona XtraBackup 2.4将继续支持MySQL和Percona Server 5.6和5.7数据库。由于新的MySQL重做日志和数据字典格式，Percona XtraBackup 8.0.x版本将仅与MySQL 8.0.x和即将推出的Percona Server for MySQL 8.0.x兼容。

也就是说，一般软件都是向下兼容，但由于新版（8.0版本）MySQL重做日志和数据字典格式，导致之前版本的XtraBackup工具不兼容，并且新版的XtraBackup只与MySQL8.0版本兼容，相当于“配套”。

XtraBackup的功能是什么？
1）复制非InnoDB数据时，InnoDB表仍处于锁定状态。
2）启用XtraDB更改页面跟踪的Percona Server for MySQL支持快速增量备份。
3）Percona XtraBackup支持使用任何类型的备份进行加密。 MySQL Enterprise Backup仅支持单文件备份的加密。
4）Percona XtraBackup根据每秒的IO操作数执行限制。MySQL Enterprise Backup支持两次操作之间可配置的睡眠时间。
5）当准备压缩备份时，Percona XtraBackup会跳过二级索引页面并重新创建它们。MySQL Enterprise Backup跳过未使用的页面，并在准备阶段重新插入。
6）无论InnoDB版本如何，Percona XtraBackup都可以从完整备份中导出单个表。MySQL Enterprise Backup 仅在执行部分备份时才使用InnoDB 5.6可移植表空间。
7）备份锁是Percona Server for MySQL中可用的轻量级替代方案。Percona XtraBackup自动使用它们来复制非InnoDB数据，以避免阻止修改InnoDB表的DML查询。

yum方式安装XtraBackup8.0

共有三种方式安装XtraBackup，分别是存储库安装，rpm或apt安装，源代码安装。

以下为安装Xtrabackup的组件共享，若linux下载速度慢，可使用百度云盘下载
链接：https://link.csdn.net/?target=https%3A%2F%2Fpan.baidu.com%2Fs%2F10_n2xmR7ehCPKi7UGE0U1A
提取码：ai7l

安装依赖组件libev
yum install -y http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm

安装Percona yum存储库
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y

如果打算将Percona XtraBackup与MySQL 结合使用，还需要开启tools存储库
percona-release enable-only tools release

安装Percona XtraBackup
yum install -y percona-xtrabackup-80

以上存储库方式安装就完成，使用xtrabackup —help即可查看命令帮助。

注：在新版本中，innobackupex命令已被移除。

rpm方式安装

手动方式需要把组件安装完成后，才能成功安装xtrabackup

下载安装依赖组件libev
wget http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm
rpm -ivh libev-4.03-3.el6.x86_64.rpm

安装Percona 存储库，并且开启tools 存储库
wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm
rpm -ivh percona-release-latest.noarch.rpm
percona-release enable-only tools release

下载安装xtrabackup
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
yum localinstall -y percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

以上xtrabackup就安装完成了。

源代码安装
源码方式安装时这三种方式中最为繁琐的，若是想通过源码方式安装，可转到官方网址查看。

xtrabackup源码安装：https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/compiling_xtrabackup.html

卸载Percona XtraBackup
使用yum卸载Percona XtraBackup，删除所有已安装的相关软件包。

yum remove percona-xtrabackup-80.x86_64

4 xtrabackup使用

1/测试完全备份

备份

创建授权用户并授权
create user ‘backup’@’%’ identified by ‘999999’;
grant all privileges on . to ‘backup’@’%’;
#创建了一个表如下
mysql> #在test_db01里面创建一个表

mysql> select * from test_tb01;
+———+———-+
| num | name |
+———+———-+
| 1 | admin |
| 2 | nginx |
| 3 | mysql |
+———+———-+
3 rows in set (0.00 sec)

mysql>

备份及过程
xtrabackup -uroot -p999999 —backup —target-dir=/data/backup/
xtrabackup备份过程.txt
—backup 备份开始
#删除表测试
drop table test_tb01;
show tables;

恢复

恢复及过程
#恢复前要停数据库的
systemctl stop mysqld
xtrabackup —prepare —target-dir=/data/backup/
—prepare 备份准备
xtrabackup恢复过程.txt
xtrabackup —copy-back —target-dir=/data/backup/
—copy-back 备份恢复

—copy-back命令表示将备份 复制 到datadir目录下，如果不想保留备份，可以使用—move-back命令，直接将备份 移动 到datadir目录下
#注意，恢复的时候，源数据data目录不能够存在，需要mv走的，否则会报如下错误
Original data directory /var/lib/mysql is not empty!
#这个地方要是没弄好，重启mysqld服务的时候可是会遇到各种坑

xtrabackup还原过程.txt
#创建备份后并不能直接用于恢复.需要先prepare。prepare的目的是跑一下redo,将未提交的rollback,回滚的回滚,跑出一个一致性的备份。
#另外个人测试发现，还需要注意恢复的data目录的属主，否则后面启动mysql也会报错
#启动数据库
systemctl start myqld

2/测试增量备份

备份

创建了一个表如下
mysql> #在test_db02里面创建一个表

mysql> select * from test_tb02;
+———+———-+
| num | name |
+———+———-+
| 1 | admin |
| 2 | nginx |
| 3 | mysql |
+———+———-+
3 rows in set (0.00 sec)

mysql>
#继上一次完全备份，此时我又创建了一个表2,以此来测试增备及恢复
当前位置情况

#创建增备存放目录
mkdir /data/mysql/zengliang
xtrabackup —backup —user=root —password=999999 —target-dir=/data/backup/ —incremental-basedir=/data/backup/zengliang/
#说明:
—target-dir #是备份后数据存放的位置
—incremental-dir #是增备的起始目标目录位置，以哪个点为基准触发的
incremental 增量的，增加的意思
#这里需要注意的是，target目录和incremental目录的意义在于，增量是参考全量目录而进行的
xtrabackup增备过程.txt

#可以看出，起点都是上一次的备份LSN的点开始的

恢复

把databases为test_db02的库删了

#增量恢复
#停止服务
systemctl stop mysqld

准备全备份日志路径
xtrabackup —prepare —apply-log-only —target-dir=/data/backup/ —user=root —password=999999
#过程略
—apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。

准备增备备份日志路径
xtrabackup —prepare —apply-log-only —target-dir=/data/backup/ —user=root —password=999999 —incremental-dir=/data/backup/zengliang/

如果有多次基于base的增备需要恢复，例如第二次备份目录zengliang2，那么第二次增备的目录也要进行一次prepare，这时候的—incremental-dir目录为zengliang2

然后开始恢复全备份准备
xtrabackup —prepare —target-dir=/data/backup/ —user=root —password=999999

备注：增备这里多了一个选项，—apply-log-only
—apply-log-only #准备应用日志后停止恢复进程不进行LSN—stop recovery process not to progress LSN after applying log when prepare.
—host可以远程恢复

清理数据/copy-back/修改属主
[root@mysql-01 ~]# mv /var/lib/mysql /var/lib/mysql-bak-2

[root@mysql-01 ~]# mkdir /var/lib/mysql
[root@mysql-01 ~]# xtrabackup —user=root —password=999999 —host=localhost —copy-back —target-dir=/data/backup/
[root@mysql-01 ~]# chown -R mysql.mysql /var/lib/mysql

启动服务
systemctl start mysqld

验证test_db02.test_tb02是否可以查看到

#其实个人感觉，完全备份恢复就不说了。增备的恢复其实也是全部恢复一样的

4/Mysql的存储引擎

存储引擎：mysql将数据存在文件系统中所使用的存储方式或格式
常用两种存储引擎：MyISAM、InnoDB
myisam，5.5版本之前默认存储引擎，执行读写操作快，不占系统资源，不支持事务，无法容错。
innodb，支持事务，行及锁定，业务数据一致性要求高，数据更新频繁
show engines; #查看当前使用的存储引擎