MYSQL数据库(二)

mysql的视图，存储过程，触发器和事件

视图：

虚拟表，保存有实表的查询结果，相当于别名 利用视图,可以隐藏表的真实结构,在程序中利用视图进行查询,可以避免表结构的变化,而修改程序,降低程 序和数据库之间的耦合度

CREATE  VIEW view_name [(column_list)]
   AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]
DROP VIEW [IF EXISTS]
  view_name [, view_name] ...
  [RESTRICT | CASCADE]

存储过程：

多表SQL的语句的集合，可以独立执行，存储过程保存在mysql.proc表中

存储过程优势

​ 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量

​ 存储过程与自定义函数的区别

​ 存储过程实现的过程要复杂一些,而函数的针对性较强

​ 存储过程可以有多个返回值,而自定义函数只有一个返回值

​ 存储过程一般可独立执行,而函数往往是作为其他SQL语句的一部分来使用

​ 无参数的存储过程执行过程中可以不加(),函数必须加 ( )

​

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body
	proc_parameter : [IN|OUT|INOUT] parameter_name type

调用存储过程

​ CALL sp_name ([ proc_parameter [,proc_parameter ...]])

查看存储过程定义

​ SHOW CREATE PROCEDURE sp_name

查看存储过程列表

​ SHOW PROCEDURE STATUS

触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行

CREATE [DEFINER = { user | CURRENT_USER }]
  TRIGGER trigger_name
  trigger_time trigger_event
  ON tbl_name FOR EACH ROW
  trigger_body

说明：

​ trigger_name：触发器的名称

​ trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发

​ trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件

​ tbl_name：该触发器作用在表名

事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可周期性的 启动，它由一个特定的线程来管理的，也就是所谓的"事件调度器"。

优点：一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能，可以实现每秒钟

执行一个任务，这在一些对实时性要求较高的环境下就非常实用

缺点：定时触发，不可以直接调用

CREATE
  [DEFINER = { user | CURRENT_USER }]
  EVENT
  [IF NOT EXISTS]
  event_name 
  ON SCHEDULE schedule
  [ON COMPLETION [NOT] PRESERVE]
  [ENABLE | DISABLE | DISABLE ON SLAVE]
  [COMMENT 'comment']
  DO event_body; 
schedule:
  AT timestamp [+ INTERVAL interval] ... 
 | EVERY interval
  [STARTS timestamp [+ INTERVAL interval] ...]
  [ENDS timestamp [+ INTERVAL interval] ...]
interval:
  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
       WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
       DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

说明：

event_name ：创建的event名字，必须是唯一确定的

ON SCHEDULE：计划任务

schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY[ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任

务执行完毕后自动drop该事件；ON COMPLETION PRESERVE则不会drop掉

[COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节

[ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改

DO event_body: 需要执行的sql语句，可以是复合语句

提示：event事件是存放在mysql.event表中

MySQL的用户，密码和权限

用户帐号:

'USERNAME'@'HOST'
@'HOST': 主机名： user1@'web1.magedu.org'
IP地址或Network 
 通配符： %   _
 示例：wang@172.16.%.%  
     user2@'192.168.1.%'
     mage@'10.0.0.0/255.255.0.0'

常用用户管理

创建用户
	CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'password']；
	mysql> create user 'chen'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
重命名
	RENAME USER old_user_name TO new_user_name;
	mysql> rename user 'chen'@'10.0.0.%' to 'chenmeng';
Query OK, 0 rows affected (0.00 sec)

删除用户
	DROP USER 'USERNAME'@'HOST'
	DROP USER ''@'localhost';
	mysql> drop user chenmeng;
Query OK, 0 rows affected (0.00 sec)

密码修改
	mysqladmin -uroot -p'old'   password 'new'

授权和取消授权

授权：GRANT
GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'someuser'@'somehost';
取消授权：REVOKE
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
REVOKE DELETE ON *.* FROM 'testuser'@'172.16.0.%';
授权或者取消权限后需
FLUSH PRIVILEGES;
查看用户权限
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host'; 
SHOW GRANTS FOR CURRENT_USER[()];

事务和索引

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现

优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点：
占用额外空间，影响插入速度
索引类型：
B+ TREE、HASH、R TREE、FULL TEXT

创建索引：
CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
help CREATE INDEX;

删除索引：
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：
SHOW INDEX FROM [db_name.]tbl_name;
优化表空间：
SHOW INDEX FROM [db_name.]tbl_name;

查看索引的使用
SET GLOBAL userstat=1;  #MySQL无此变量
SHOW INDEX_STATISTICS;

事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元

事务日志：记录事务信息，实现undo,redo等故障恢复功能

ACID特性：
A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

事务支持保存点：
	READ UNCOMMITTED 可读取到未提交数据，产生脏读
	READ COMMITTED可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
	REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
	SERIALIZABLE 可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

MVCC和事务的隔离级别：
MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

MySQL日志

MySQL中日志有事务日志，错误日志，慢查询日志，通用日志和二进制日志

事务日志：transaction log

redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log

undo log：保存与执行的操作相反的操作,用于实现rollback

mysql> show variables like '%innodb_log%'
    -> ;
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_buffer_size      | 8388608  |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | ./       |
+-----------------------------+----------+
5 rows in set (0.00 sec)

事务日志三个级别

innodb_flush_log_at_trx_commit=0|1|2
1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更
好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系
统或停电可能导致最后一秒的交易丢失

错误日志

​ mysqld启动和关闭过程中输出的事件信息
​ mysqld运行中产生的错误信息
​ event scheduler运行一个event时产生的日志信息
​ 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

mysql> show global variables like '%log_error%';
+---------------------+---------------------------+
| Variable_name       | Value                     |
+---------------------+---------------------------+
| binlog_error_action | IGNORE_ERROR              |
| log_error           | ./centos7.example.com.err |
+---------------------+---------------------------+
2 rows in set (0.00 sec)

通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句

通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)
#默认未启用

慢查询日志

记录执行查询时长超出指定时长的操作

相关变量：
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除

二进制日志

二进制日志(备份)
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型

功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
二进制日志记录三种格式
基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少
基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

相关变量
sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开
启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：#二进制日志可以自动删除的天数。 默认为0，即不自动删除

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志
mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime= 
 --base64-output[=name]
        -v -vvv

数据备份

数据备份的类型：完全备份，增量备份，差异备份

完全备份：整个数据集

增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

备份内容：

​ 数据
​ 二进制日志、InnoDB的事务日志
​ 用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
​ 服务器的配置文件

mysqldump 实现数据备份

mysqldump命令格式和常用选项

mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定义
不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name…  #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]： #此选项须启用二进制日志
	#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
	#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
	#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single—transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact        #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data    #只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info #只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db #不备份create database，可被-A或-B覆盖
--flush-privileges #备份mysql或相关时需要使用
-f, --force       #忽略SQL错误，继续执行
--hex-blob        #使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick     #不缓存查询，直接输出，加快备份速度

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

 InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用
--single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启
事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥,备份大型表时，建议将--single-transaction选项和--quick结合一起使用

InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges  --triggers  --default-character-set=utf8  --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

mysqldump实现指定库备份

[root@centos7 data]# mysqldump -uroot -p'magedu' -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B hellodb >/data/backup/hellodb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos7 data]# ll backup/
total 8
-rw-r--r-- 1 root root 8044 Jun 28 18:35 hellodb.sql

[root@centos7 data]# mysqldump -uroot -p"magedu" -B hellodb > backup/hellodb1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos7 data]# ll backup/
total 16
-rw-r--r-- 1 root root 7796 Jun 28 18:38 hellodb1.sql
-rw-r--r-- 1 root root 8044 Jun 28 18:35 hellodb.sql

mysqldump实现完全备份和还原

备份
[root@centos7 data]# mysqldump -uroot -p'magedu' -A -F --single-transaction --master-data=2 >/data/backup/db_backup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos7 data]# ll backup/
total 880
-rw-r--r-- 1 root root 881183 Jun 28 18:40 db_backup.sql
-rw-r--r-- 1 root root   7796 Jun 28 18:38 hellodb1.sql
-rw-r--r-- 1 root root   8044 Jun 28 18:35 hellodb.sql

还原
mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
mysql> source /data/db_backup.mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hellodb            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysqldump配合二进制文件恢复到较新状态

完全备份
[root@centos7 data]# mysqldump -uroot -p'magedu' -A -F --single-transaction --master-data=2 >/data/backup/db1.sql
从完全备份文件中找到做完全备份时二进制文件位置
[root@centos7 data]# grep -i "^-- CHANGE MASTER TO"  backup/db1.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
根据完全备份时二进制文件位置从二进制中取出备份位置之后的数据
[root@centos7 data]# mysqlbinlog binlog/mysql-bin.000002 --start-position=154 > backup/bin.sql

关闭二进制日志，按顺序导入数据
mysql> set sql_log_bin=0;
mysql> source /data/backup/db1.sql
mysql> source /data/backup/bin.sql
mysql> set sql_log_bin=1;

误删时恢复数据跟上面类似，从全备文件找到全备时二进制日志位置，取出全备位置往后的二进制数据，从中删除误操作的命令，再将数据导入数据库

mysqldump 和二进制日志结合实现差异(增量)备份
实现逻辑：
第一次做全备
根据全备找到全备时二进制文件位置，使用命令 mysqlbinlog binlog/mysql-bin.xxxxxx --start-position=xxxx> backup/bin.sql 取出增长的数据
后续定期从上次备份位置取出新增数据

xtrabackup实现数据备份

xtrabackup是数据库备份的第三方工具，来自EPEL源

yum install percona-xtrabackup 

xtrabackup备份时会生成以下文件

​ xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
​ xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
​ xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
​ backup-my.cnf：文本文件，备份命令用到的配置选项信息
​ xtrabackup_logfile：备份生成的二进制日志文件

xtrabackup工具备份和还原，需要三步实现

1. 备份：对数据库做完全或增量备份

   innobackupex [option] BACKUP-ROOT-DIR
   --user：#该选项表示备份账号
   --password：#该选项表示备份的密码
   --host：#该选项表示备份数据库的地址
   --databases：#该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
   如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。
   如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
   --defaults-file：#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
   --incremental：#该选项表示创建一个增量备份，需要指定--incremental-basedir
   --incremental-basedir：#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
   --incremental-dir：#该选项表示还原时增量备份的目录
   --include=name：#指定表名，格式：databasename.tablename
   

2. 预准备： 还原前，先对备份的数据，整理至一个临时目录

   innobackupex --apply-log [option] BACKUP-DIR
   --apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚
   未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作
   用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
   --use-memory：#和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大
   小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
   --export：#表示开启可导出单独的表之后再导入其他Mysql中
   --redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后
   一个增量备份的合并
   

3. 还原：将整理好的数据，复制回数据库目录中

   innobackupex --copy-back [选项] BACKUP-DIR
   innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR
   --copy-back：#做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
   --move-back：#这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这
   个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
   --force-non-empty-directories #指定该参数时候，使得innobackupex --copy-back或--moveback选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备
   份目录拷贝一个在datadir已经存在的文件，会报错失败
   
   还原注意事项：
   1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--
   copy-back选项不会覆盖
   2. 在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
   3. 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将
   属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用
   innobackupex之前完成
   
   

xtrabackup实现全备和还原

[root@localhost data]# xtrabackup --backup --target-dir=/data/back #全备份数据库
[root@localhost data]# scp -r /data/back 10.0.0.28:/data

[root@localhost data]# xtrabackup --prepare --target-dir=/data/back #预还原
[root@localhost data]# xtrabackup --copy-back --target-dir=/data/back #还原数据
[root@localhost data]# chown -R mysql:mysql /var/lib/mysql #修改还原后文件权限
[root@localhost data]# systemctl start mysqld.service #启动服务
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

xtrabackup实现增量备份和还原

第一次全备
xtrabackup -uroot -p'Cm@123456' --backup --target-dir=/data/back/all
insert hellodb.teachers (name,age,gender)values('mage',28,'M');
根据全备备份第一次增量
xtrabackup -uroot -p'Cm@123456' --backup --target-dir=/data/back/inc1 --incremental-basedir=/data/back/all
insert hellodb.teachers (name,age,gender)values('laowang',28,'M');
根据第一次增量备份第二次增量
xtrabackup -uroot -p'Cm@123456' --backup --target-dir=/data/back/inc2 --incremental-basedir=/data/back/inc1
scp -r /data/* 10.0.0.28:/data

预准备，将增量整合到全备中，最后一次增量不需要--apply-log-only（阻止回滚） 
xtrabackup --prepare --apply-log-only --target-dir=/data/back/all
xtrabackup --prepare --apply-log-only --target-dir=/data/back/all --incremental-basedir=/data/back/inc1
xtrabackup --prepare --target-dir=/data/back/all --incremental-basedir=/data/back/inc2
xtrabackup --copy-back --target-dir=/data/back/all
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld.service

练习

1、 导入hellodb.sql生成数据库
(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄 
mysql> select * from students where age>25 and gender="M";
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|     3 | Xie Yanke    |  53 | M      |       2 |        16 |
|     4 | Ding Dian    |  32 | M      |       4 |         4 |
|     5 | Yu Yutong    |  26 | M      |       3 |         1 |
|     6 | Shi Qing     |  46 | M      |       5 |      NULL |
|    13 | Tian Boguang |  33 | M      |       2 |      NULL |
|    24 | Xu Xian      |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng  | 100 | M      |    NULL |      NULL |
+-------+--------------+-----+--------+---------+-----------+
7 rows in set (0.00 sec)
(2) 以ClassID为分组依据，显示每组的平均年龄
mysql> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       1 |  20.5000 |
|       2 |  36.0000 |
|       3 |  20.2500 |
|       4 |  24.7500 |
|       5 |  46.0000 |
|       6 |  20.7500 |
|       7 |  19.6667 |
+---------+----------+
8 rows in set (0.00 sec)
(3) 显示第2题中平均年龄大于30的分组及平均年龄
mysql> select classid,avg(age) from students group by classid having avg(age)>30;
+---------+----------+
| classid | avg(age) |
+---------+----------+
|    NULL |  63.5000 |
|       2 |  36.0000 |
|       5 |  46.0000 |
+---------+----------+
3 rows in set (0.00 sec)

(4) 显示以L开头的名字的同学的信息
mysql> select * from students where name like 'L%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     8 | Lin Daiyu   |  17 | F      |       7 |      NULL |
|    14 | Lu Wushuang |  17 | F      |       3 |      NULL |
|    17 | Lin Chong   |  25 | M      |       4 |      NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)

2、数据库授权magedu用户，允许192.168.1.0/24网段可以连接mysql
mysql> create user 'magedu'@'192.168.1.%' identified by 'Magedu@123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'magedu'@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)