MySQL字符集-日常维护工具-xtrabackup备份工具

矫情吗；* 2022-10-01 12:46 407阅读 0赞

一、 mysql字符集  
1、 字符集介绍  
字符集就是一套文字符号及其编码、比较规则的集合，第一个计算机字符集ASCII!MySQL数据库字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念，其中字符集用来定义MySQL数据字符串的存储方式，而校对规则定义比较字符串的方式

2、 mysql数据库常见字符集介绍

选择字符集建议使用国际标准的UTF-8

3、 Mysql怎样选择合适的字符集  
1）如果处理各种各样的文字，发布到不同语言国家地区，应选Unicode字符集，对MySQL来说就是UTF-8(每个汉字三个字节)  
2）如果只是需要支持中文，并且数据量很大，性能要求也高，可选GBK（定长，每个汉字占双字节，英文也占双字节），如果是大量运算，比较排序等，定长字符集更快，性能也高  
3）处理移动互联网业务，可能需要使用utf8mb4字符集，apple

4、 查看当前mysql支持的字符集  
mysql可以支持多种字符集，同一台服务器，库或表的不同字段都可以指定不同的字符集  
mysql> show character set；  
Centos7.5 mysql5.7 版本总共有41中字符集  
我们常用的字符集大概三种，如下：  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 -e “show character set\\G;” |egrep “gbk|utf8|latin1”|awk ‘\{print $0\}’  
mysql: \[Warning\] Using a password on the command line interface can be insecure.  
Charset: latin1  
Default collation: latin1\_swedish\_ci  
Charset: gbk  
Default collation: gbk\_chinese\_ci  
Charset: utf8  
Default collation: utf8\_general\_ci  
Charset: utf8mb4  
Default collation: utf8mb4\_general\_ci

扩展：修改mysql的字符集  
直接修改mysql的配置文件my.cnf文件，添加如下：  
character-set-server=utf8  
重启mysql服务  
systemctl restart mysqld

6、 实战-迁移数据  
背景：公司业务数据book,由于之前建表没注意字符集的问题，导致之前写入的数据出现乱码。现在要将之前的数据和现在数据的字符集一致，这样才不出现乱码情况。  
将字符集为latin1已有记录的数据转成utf8，并且已经存在的记录不乱码  
步骤  
（1） 建库及建表的语句导出，sed批量修改为UTF-8  
（2） 导出之前所有的数据  
（3） 修改MySQL服务端和客户端编码为UTF-8  
（4） 删除原有的库表及数据  
（5） 导入新的建库及建表语句  
（6） 导入之前的数据

模拟乱码的数据表：  
上传book\_latin1.sql数据库备份文件到Linux主机上  
\[root@xuegod120 ~\]\# rz -E  
rz waiting to receive.  
\[root@xuegod120 ~\]\# ls  
anaconda-ks.cfg book\_latin1.sql  
\[root@xuegod120 ~\]\# mv book\_latin1.sql /tmp/ \#移动到tmp目录下，不然使用source导入报错

1） 创建一个新的数据库，用于将乱码的库导入  
mysql> create database book2;  
Query OK, 1 row affected (0.00 sec)

mysql> use book2;  
Database changed  
mysql> source /tmp/book\_latin1.sql \#导入数据库

mysql> select \* from category; \#查看表内容，发现是乱码  
±--------±---------------------------+  
| bTypeId | bTypeName |  
±--------±---------------------------+  
| 1 | windowsåº”ç”¨ |  
| 2 | ç½‘ç«™ |  
| 3 | 3DåŠ¨ç”» |  
| 4 | linuxå¦ä¹ |  
| 5 | Delphiå¦ä¹ |  
| 6 | é»‘å®¢ |  
| 7 | ç½‘ç»œæŠ€æœ¯ |  
| 8 | å®‰å…¨ |  
| 9 | å¹³é¢ |  
| 10 | AutoCADæŠ€æœ¯ |  
±--------±---------------------------+  
10 rows in set (0.00 sec)

2） 单独到处乱码库的表结构  
\[root@xuegod120 ~\]\# mysqldump -uroot -p123456 --default-character-set=latin1 -d book2 > booktable.sql  
mysqldump: \[Warning\] Using a password on the command line interface can be insecure.  
\[root@xuegod120 ~\]\# ls  
anaconda-ks.cfg booktable.sql

3） 将到处的表结构文件内的latin1权限修改为utf8  
使用vim编辑booktable.sql文件，进行修改  
:%s/latin1/utf8/g \#按下冒号，输入%s/latin1/utf8/g，替换所有的latin1为utf8  
3 substitutions on 3 lines \#3行替换成功  
:x \#保存退出

4） 导出乱码库的内容  
\[root@xuegod120 ~\]\# mysqldump -uroot -p123456 --quick --no-create-info --extended-insert --default-character-set=latin1 book2 > bookdata.sql  
mysqldump: \[Warning\] Using a password on the command line interface can be insecure.  
\[root@xuegod120 ~\]\# ls  
anaconda-ks.cfg bookdata.sql booktable.sql

5） 修改表内容的latin1为utf8  
:%s/latin1/utf8/g \#只有第10行需要修改  
:x \#保存退出

6） 重新建库  
mysql> drop database book2;  
Query OK, 2 rows affected (0.00 sec)

mysql> create database book2 default charset utf8; \#创建库，并指定字符集是utf8  
Query OK, 1 row affected (0.00 sec)

7） 导入之前的数据库表的结构文件  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 book2 < booktable.sql  
mysql: \[Warning\] Using a password on the command line interface can be insecure.

8） 导入数据库的数据  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 book2 < bookdata.sql  
mysql: \[Warning\] Using a password on the command line interface can be insecure.

mysql> select \* from books;  
。。。。。。。。。。。。  
| 44 | XML 完全探索 | 2 | 中国青年出版社 | 104 | 2004-01-01 | 齐鹏 | 7505357778 |  
±----±--------------------------------------------------------±--------±----------------------------------±------±-----------±----------±------------+  
44 rows in set (0.00 sec)

mysql> select \* from category;  
±--------±--------------+  
| bTypeId | bTypeName |  
±--------±--------------+  
| 1 | windows应用 |  
| 2 | 网站 |  
| 3 | 3D动画 |  
| 4 | linux学习 |  
| 5 | Delphi学习 |  
| 6 | 黑客 |  
| 7 | 网络技术 |  
| 8 | 安全 |  
| 9 | 平面 |  
| 10 | AutoCAD技术 |  
±--------±--------------+  
10 rows in set (0.00 sec)  
注意：选择目标字符集时，要注意最好大于等于原字符集（字库更大），否则可能会丢失不被支持的数据。

二、 mysql日常维护  
1、 mysqlcheck是mysql修复工具  
mysqlcheck客户端工具可以检查和修复MyISAM表，还可以优化和分析表。  
实际上，它集成了MySQL工具中check、repair、analyze、tmpimize的功能。  
/usr/local/mysql/bin/mysqlcheck \#源码编译安装位置  
rpm -qf `which mysqlcheck` \#yum安装查看  
mysql-community-client-5.7.24-1.el7.x86\_64

2、 常用选项  
\[root@xuegod120 ~\]\# mysqlcheck --help \#查看帮助  
\-c, --check (检查表)  
\-r, --repair（修复表)  
\-a, --analyze (分析表)  
\-o, --tmpimize(优化表)， \#其中，默认选项是-c(检查表)  
\-u， 使用mysql中哪个用户进行操作

3、 mysqlcheck使用语法  
使用以下3种方式来调用mysqlcheck：  
mysqlcheck\[tmpions\] db\_name \[tables\]  
mysqlcheck\[tmpions\] —database DB1 \[DB2 DB3…\]  
mysqlcheck\[tmpions\] --all–database  
如果没有指定任何表或使用—database或–all–database选项，则检查整个数据库。

4、 举例说明  
1） 检查表（check）  
\[root@xuegod120 ~\]\# mysqlcheck -uroot -p123456 -c book books \#book库的books表  
mysqlcheck: \[Warning\] Using a password on the command line interface can be insecure.  
book.books OK

2） 修复表  
\[root@xuegod120 ~\]\# mysqlcheck -uroot -p123456 -r book2 books  
mysqlcheck: \[Warning\] Using a password on the command line interface can be insecure.  
book2.books  
OK  
3） 修复指定的数据库  
\[root@xuegod120 ~\]\# mysqlcheck -uroot -p123456 -r --databases book2  
mysqlcheck: \[Warning\] Using a password on the command line interface can be insecure.  
book2.books OK  
book2.category OK

4） 检查修复所有数据库  
\[root@xuegod120 ~\]\# mysqlcheck -uroot -p -A -r \#-A 等价于 –all-databases

5、 mysql备份修复  
1） mysql备份的类型  
（1） 按照备份时对数据库的影响范围分为  
Hot backup(热备) Cold Backup（冷备）Warm Backup（温备）  
Hot backup：指在数据库运行中直接备份，对正在运行的数据库没有任何影响。（Online Backup）官方手册为在线备份  
Cold Backup：指在数据库停止的情况下进行备份(OfflineBackup) 官方手册称为离线备份  
Warm Backup：备份同样在数据库运行时进行，但是会对当前数据库的操作有所影响，例如加一个全局读锁以保证备份数据的一致性

（2） 按照备份后的文件内容  
逻辑备份：  
指备份后的文件内容是可读的，通常为文本文件，内容一般是SQL语句，或者是表内的实际数据，如mysqldump和SELECT \* INTO OUTFILE的方法，一般适用于数据库的升级和迁移，恢复时间较长  
裸文件备份：  
拷贝数据库的物理文件，数据库既可以处于运行状态（mysqlhotcopy 、ibbackup、xtrabackup这类工具），也可以处于停止状态，恢复时间较短。

（3） 按照备份数据库的内容来分，又可以分为  
完全备份：对数据库完整的备份。  
增量备份：在上一次完全备份基础上，对更新的数据进行备份（xtrabackup）。  
日志备份：二进制日志备份，主从复制。

2） 逻辑备份工具mysqldump  
使用的时候MySQL当要导入或者导出数据量大的库的时候,用PHPMYADMIN甚至MySQL Administrator这些工具都会力不从心,这时只能使用MySQL所提供的命令行工具mysqldump进行备份恢复。数据量大的时候不推荐使用，可支持MyISAM,InnoDB  
MySQL数据的导出和导入工具：mysqldump  
导出数据：  
语法： mysqldump \[TMPIONS\] database \[tables\] >导出的文件名.sql  
（1） 导出所有数据库  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 -A >all.sql  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 --all-databases >all2.sql  
参数 -A代表所有，等同于–all-databases

（2） 导出某个数据库  
mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql \# 注意是-p空格后是数据库名，不是密码。  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 book >book.sql  
\[root@xuegod120 ~\]\# vim book.sql

（3） 导出单张表  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 book books >books.sql \#导出book库books表

（4） 导出库的表结构  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 -d book >booktable.sql \#只导出book库的表结构

（5） 只导出数据  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 -t book >bookdata.sql \#只导出book库中的数据

（6） 导出数据库，并自动生成库的创建语句  
\[root@xuegod120 ~\]\# mysqldump -u root -p123456 -B book2 >book2.sql  
\[root@xuegod120 ~\]\# mysql -u root -p123456 < book2.sql \#导入不用指定数据名

3） 导入数据  
（1） 导入所有数据库  
\[root@xuegod120 ~\]\# mysql -u root -p123456 <all.sql

（2） 导入数据库  
\[root@xuegod120 ~\]\# mysql -u root -p123456 book <book.sql \#如果导入时，没有对应的数据库，需要你手动创建一下：mysql> create database book;  
使用source导入  
\[root@xuegod120 ~\]\# mysql -u root -p123456  
mysql> create database book;  
mysql> use book;  
mysql> source /root/book.sql

（3） 导入表  
mysql> use book;  
mysql> drop table books;  
mysql> source /root/books.sql; \#导入表时，不需要重新，创建表。要先进到相应的数据库中  
mysql> select \* from books;

（4） 导入表结构和数据  
mysql> create database book;  
mysql> exit  
mysql -u root -p123456 book<booktable.sql  
mysql -u root -p123456 book<bookdata.sql

扩展：  
mysqlhotcopy  裸文件备份 --5.7版本已经去掉此命令，请在5.5以下测试  
mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库.它是备份数据库或单个表最快的途径,完全属于物理备份,但只能用于备份MyISAM存储引擎和运行在数据库目录所在的机器上，与mysqldump备份不同，mysqldump属于逻辑备份,备份时是执行的SQL语句。  
mysqlhotcopy的本质是使用锁表语句后再使用cp或scp拷贝数据库

4） mysqlhotcopy使用方法  
（1）安装mysqlhotcopy所依赖的软件包(perl-DBD)  
\[root@xuegod120 ~\]\# yum install perl-DBD\* -y

（2）查看mysqlhotcopy帮助信息  
\[root@xuegod120 ~\]\# mysqlhotcopy -help  
–allowold don’t abort if target dir already exists (rename it \_old) \#不覆盖以前备份的文件  
–addtodest don’t rename target dir if it exists, just add files to it \#属于增量备份  
–noindices don’t include full index files in copy \#不备份索引文件  
–debug enable debug \#启用调试输出  
–regexp=\# copy all databases with names matching regexp \#使用正则表达式  
–checkpoint=\# insert checkpoint entry into specified db.table \#插入检查点条目  
–flushlog flush logs once all tables are locked \#所有表锁定后刷新日志  
–resetmaster reset the binlog once all tables are locked \#一旦锁表重置binlog文件  
–resetslave reset the [master.info][] once all tables are locked \#一旦锁表重置master.info文件

5） 举例说明  
（1） 备份一个数据库到一个目录中  
\[root@xuegod120 ~\]\# mysqlhotcopy -u root -p 123456 book /opt \#-u -p 后面都有空格  
对比下大小  
\[root@xuegod120 ~\]\# du -h /tmp/book/ /var/lib/mysql/book  
44K /tmp/book/  
44K /var/lib/mysql/book

（2） 备份多个数据库到一个目录  
\[root@xuegod120 ~\]\# mkdir /opt/book-mysql  
\[root@xuegod120 ~\]\# mysqlhotcopy -u root -p 123456 book mysql /opt/book-mysql  
Flushed 24 tables with read lock (`book`.`books`, `book`.`category`, `mysql`.`columns_priv`, `mysql`.`db`, `mysql`.`event`, `mysql`.`func`, `mysql`.`help_category`, `mysql`.`help_keyword`, `mysql`.`help_relation`, `mysql`.`help_topic`, `mysql`.`host`, `mysql`.`ndb_binlog_index`, `mysql`.`plugin`, `mysql`.`proc`, `mysql`.`procs_priv`, `mysql`.`proxies_priv`, `mysql`.`servers`, `mysql`.`tables_priv`, `mysql`.`time_zone`, `mysql`.`time_zone_leap_second`, `mysql`.`time_zone_name`, `mysql`.`time_zone_transition`, `mysql`.`time_zone_transition_type`, `mysql`.`user`) in 0 seconds.  
Locked 0 views () in 0 seconds.  
Copying 7 files…  
Copying indices for 0 files…  
Copying 72 files…  
Copying indices for 0 files…  
Unlocked tables.  
mysqlhotcopy copied 26 tables (79 files) in 0 seconds (0 seconds overall).  
\[root@xuegod120 ~\]\# du -h /opt/book-mysql/ /var/lib/mysql/

（3） 备份数据库中的某个表  
语法：mysqlhotcopy -u 用户 -p 密码 数据库名./要备份的表名/ 要备份的路径  
\[root@xuegod120 ~\]\# mkdir /opt/books  
\[root@xuegod120 ~\]\# mysqlhotcopy -u root -p 123456 book./books/ /opt/books/ \#注意 / 一定要。

实际上是把对应的表文件复制到/tmp目录下  
\[root@xuegod120 ~\]\# ll /opt/books/book/

（4） 恢复数据  
首先我们破坏数据  
\[root@xuegod120 ~\]\# rm -rf /var/lib/mysql/book/  
\[root@xuegod120 ~\]\# mysql -u root -p123456

开始恢复：  
\[root@xuegod120 ~\]\# cp -ra /opt/book/ /var/lib/mysql/  
\[root@xuegod120 ~\]\# ll -d /var/lib/mysql/book/  
drwxr-x— 2 mysql mysql 4096 Nov 9 17:28 /var/lib/mysql/book/

总结：  
mysqldump和mysqlhotcopy的比较：

1.  mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，数据库大时，占用系统资源较多，支持常用的MyISAM，innodb
2.  mysqlhotcopy只是简单的缓存写入和文件复制的过程，占用资源和备份速度比mysqldump快很多很多。特别适合大的数据库，但需要注意的是：mysqlhotcopy只支持MyISAM 引擎
3.  mysqlhotcopy只能运行在数据库目录所在的机器上，mysqldump可以用在远程客户端。
4.  相同的地方都是在线执行LOCK TABLES 以及 UNLOCK TABLES
5.  mysqlhotcopy恢复只需要COPY备份文件到源目录覆盖即可，mysqldump需要导入SQL文件到原来库中。

实战：写个自动备份MySQL数据库Shell脚本  
\[root@xuegod120 ~\]\# vim [mysql-autoback.sh][]  
\#!/bin/bash  
export LANG=en\_US.UTF-8  
savedir=/database\_back/  
cd “ s a v e d i r " t i m e = " savedir" time=" savedir"time="(date +”%Y-%m-%d")"  
mysqldump -u root -p123456 book > book-"$time".sql  
再添加计划任务即

三、 xtrzbackup备份工具的使用  
1、 xtrbackup简介  
我们知道，针对InnoDB存储引擎，MySQL本身没有提供合适的热备工具，ibbackup虽是一款高效的首选热备方式，但它是是收费的。好在Percona公司给大家提供了一个开源、免费的Xtrabackup热备工具，它可实现ibbackup的所有功能，并且还扩展支持真正的增量备份功能，是商业备份工具InnoDB Hotbackup的一个很好的替代品。  
Xtrabackup包括两个主要工具：Xtrabackup和innobackupex：  
Xtrabackup只能备份InnoDB和XtraDB两种引擎表，而不能备份MyISAM数据表。  
innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等，智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备的功能

2、 xtrabackup的安装  
下载安装包：  
\[root@xuegod120 ~\]\# wget [https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86\_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86\_64-bundle.tar][https_www.percona.com_downloads_XtraBackup_Percona-XtraBackup-2.4.9_binary_redhat_7_x86_64_Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar]

解压包：  
\[root@xuegod120 ~\]\# ls  
anaconda-ks.cfg booktable.sql  
bookdata.sql Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86\_64-bundle.tar  
\[root@xuegod120 ~\]\# tar xf Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86\_64-bundle.tar  
\[root@xuegod120 ~\]\# ls  
anaconda-ks.cfg  
bookdata.sql  
booktable.sql  
percona-xtrabackup-24-2.4.9-1.el7.x86\_64.rpm  
Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86\_64-bundle.tar  
percona-xtrabackup-24-debuginfo-2.4.9-1.el7.x86\_64.rpm  
percona-xtrabackup-test-24-2.4.9-1.el7.x86\_64.rpm

安装并解决依赖，使用yum：  
\[root@xuegod120 ~\]\# yum -y install percona-xtrabackup-24-2.4.9-1.el7.x86\_64.rpm  
注意：安装完成以后记得更改你的/etc/my.cnf配置文件制定数据目录，因为Xtrabackup是根据你的/etc/my.cnf配置文件来获取你备份的文件，比如在/etc/my.cnf的\[mysqld\] 下添加datadir=/var/lib/mysql，然后重启mysql

修改配置文件：  
\[root@xuegod120 ~\]\# vim /etc/my.cnf  
datadir=/var/lib/mysql

重启服务：  
\[root@xuegod120 ~\]\# systemctl restart mysqld

3、 xtrabackup使用  
我们一般使用innobackupex脚本  
innobackupex是perl脚本对xtrabackup的封装，和功能扩展。

备份准备工作：  
权限和链接  
xtrabackup需要连接到数据库和datadir操作权限。  
xtrabackup或者innobackupex在使用过程中涉及到2类用户权限：

1）系统用户，用来调用innobackupex或者xtrabackup

需要的权限：连接到服务是为了执行备份，需要在datadir上有read，write和execute权限。
    在数据库中需要以下权限：
    RELOAD和LOCK TABLES权限为了执行FLUSH TABLES WITH READ LOCK   。
    REPLICATION CLIENT为了获取binary log 位置
    CREATE TABLESPACE权限为了导入表，用户表级别的恢复
    SUPER权限在slave环境下备份用来启动和关闭slave线程

2）数据库用户，数据库内使用的用户  
连接到服务：innobackupex或者xtrabackup通过–user和–password连接到数据库服务  
$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/  
$ innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 - 压缩  
$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

其他连接选项：  
Tmpion Description  
\-port The port to use when connecting to the database server with TCP/IP.  
\-socket The socket to use when connecting to the local database.  
\-host The host to use when connecting to the database server with TCP/IP.

可以单独创建用来备份数据库的用户，安全，并赋予对应的权限。

4、 完全备份和全备还原  
1） 创建全备  
\[root@xuegod120 ~\]\# innobackupex --user=root --password=123456 /tmp/da\_backup/  
或者  
\[root@xuegod120 ~\]\# innobackupex --user=root --password=123456 /tmp/db\_backup/ 2>> /tmp/db\_backup/db\_bakup.log \#\#不显示输出信息，输出信息重定向到db\_backup.log  
注意：使用输出重定向，导出的目录必须存在才可以。不加重定向，可以不用先创建导出目录  
\[root@xuegod120 ~\]\# ls /tmp/db\_backup/ \#可以看到备份的内容就是/var/lib/mysql下的内容  
2019-06-11\_18-02-01 db\_bakup.log  
\[root@xuegod120 ~\]\# ls /tmp/db\_backup/2019-06-11\_18-02-01/  
backup-my.cnf ib\_buffer\_pool mysql sys xtrabackup\_info  
book2 ibdata1 performance\_schema xtrabackup\_checkpoints xtrabackup\_logfile

内部机制：在备份的时候innobackupex会调用xtrabackup来备份innodb表，并复制所有的表定义，其他引擎的表(MyISAM,MERGE,CSV,ARCHIVE)。  
其他选项:  
–no-timestamp，指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹。  
–default-file，指定配置文件，用来配置innobackupex的选线  
innobackupex --user=root --password=123456 --no-timestamp /tmp/db\_backup/full  
innobackupex --user=root --password=123456 --no-timestamp /tmp/db\_backup/full (使用–no-timestamp时，后面的这个full目录必须跟上且不能提前自己建立，它由innobackupex自动建立，否则会报错innobackupex: Error: Failed to create backup directory)

例：使用—no-timestamp,指定备份目录，不在创建时间戳文件夹  
\[root@xuegod120 ~\]\# innobackupex --user=root --password=123456 --no-timestamp /tmp/db\_backup/full 2>> /tmp/db\_backup/db\_bakup.log  
\[root@xuegod120 ~\]\# ls /tmp/db\_backup/full/  
backup-my.cnf ib\_buffer\_pool mysql sys xtrabackup\_info  
book2 ibdata1 performance\_schema xtrabackup\_checkpoints xtrabackup\_logfile

2） 全备还原  
语法：innobackupex –copy-back 还原全备文件  
还原数据前，需要停止数据库  
\[root@xuegod120 ~\]\# systemctl stop mysqld

模拟数据丢失，删除数据库文件  
\[root@xuegod120 ~\]\# rm -rf /var/lib/mysql/\*

全备还原：  
\[root@xuegod120 ~\]\# innobackupex --copy-back /tmp/db\_backup/2019-06-11\_18-02-01/

查看数据库文件是否还原：  
\[root@xuegod120 ~\]\# ll -d /var/lib/mysql/\*  
drwxr-x— 2 root root 131 Jun 11 23:09 /var/lib/mysql/book  
\-rw-r----- 1 root root 319 Jun 11 23:09 /var/lib/mysql/ib\_buffer\_pool  
\-rw-r----- 1 root root 12582912 Jun 11 23:09 /var/lib/mysql/ibdata1  
drwxr-x— 2 root root 4096 Jun 11 23:09 /var/lib/mysql/mysql  
drwxr-x— 2 root root 8192 Jun 11 23:09 /var/lib/mysql/performance\_schema  
drwxr-x— 2 root root 8192 Jun 11 23:09 /var/lib/mysql/sys  
\-rw-r----- 1 root root 424 Jun 11 23:09 /var/lib/mysql/xtrabackup\_info

重新授权  
\[root@xuegod120 ~\]\# chown -R mysql.mysql /var/lib/mysql/  
\[root@xuegod120 ~\]\# ll -d /var/lib/mysql/\*  
drwxr-x— 2 mysql mysql 131 Jun 11 23:09 /var/lib/mysql/book  
\-rw-r----- 1 mysql mysql 319 Jun 11 23:09 /var/lib/mysql/ib\_buffer\_pool  
\-rw-r----- 1 mysql mysql 12582912 Jun 11 23:09 /var/lib/mysql/ibdata1  
drwxr-x— 2 mysql mysql 4096 Jun 11 23:09 /var/lib/mysql/mysql  
drwxr-x— 2 mysql mysql 8192 Jun 11 23:09 /var/lib/mysql/performance\_schema  
drwxr-x— 2 mysql mysql 8192 Jun 11 23:09 /var/lib/mysql/sys  
\-rw-r----- 1 mysql mysql 424 Jun 11 23:09 /var/lib/mysql/xtrabackup\_info  
启动mysqld服务，查看数据库文件是否可使用  
\[root@xuegod120 ~\]\# systemctl start mysqld  
\[root@xuegod120 ~\]\# mysql -uroot -p123456 -A  
mysql> show databases;  
±-------------------+  
| Database |  
±-------------------+  
| information\_schema |  
| book |  
| mysql |  
| performance\_schema |  
| sys |  
±-------------------+  
5 rows in set (0.01 sec)  
mysql> use book;  
Database changed  
mysql> show tables;  
±---------------+  
| Tables\_in\_book |  
±---------------+  
| books |  
| category |  
±---------------+  
2 rows in set (0.00 sec)

3） 创建增强备份和还原  
增量备份作用：减少备份数据重复，节省磁盘空间，缩短备份时间  
增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。  
首先创建全备：  
\[root@xuegod120 ~\]\# innobackupex --user=root --password=123456 /tmp/db\_backup/  
这样就会在/tmp/db\_backup下创建一个时间戳文件夹 2018-11-02\_17-43-40，文件夹下就是备份文件

检查备份文件目录下的xtarbackup\_checkpoinits，查看信息  
\[root@xuegod120 ~\]\# cat /tmp/db\_backup/2019-06-11\_23-36-00/xtrabackup\_checkpoints  
backup\_type = full-backuped  
from\_lsn = 0  
to\_lsn = 2529988  
last\_lsn = 2529997 \#参照这个lsn号，查看增量备份是否成功  
compact = 0  
recover\_binlog\_info = 0  
首先我们插入一些数据  
mysql> use book;  
Database changed  
mysql> create table test2(name varchar(8) not null);  
Query OK, 0 rows affected (0.20 sec)

mysql> insert into test2 values(‘HA’);  
Query OK, 1 row affected (0.00 sec)

创建增量备份，使用—incremental参数  
\[root@xuegod120 ~\]\# innobackupex --user=root --password=123456 --incremental /tmp/db\_backup/ --incremental-basedir=/tmp/db\_backup/2019-06-11\_23-36-00/

查看lsn号是否改变  
\[root@xuegod120 ~\]\# cat /tmp/db\_backup/2019-06-11\_23-36-00/xtrabackup\_checkpoints \#原来全备的lsn号  
backup\_type = full-backuped  
from\_lsn = 0  
to\_lsn = 2529988  
last\_lsn = 2529997  
compact = 0  
recover\_binlog\_info = 0  
\[root@xuegod120 ~\]\# cat /tmp/db\_backup/2019-06-11\_23-41-09/xtrabackup\_checkpoints \#增量备份的lsn号  
backup\_type = incremental  
from\_lsn = 2529988  
to\_lsn = 2530142  
last\_lsn = 2530151  
compact = 0  
recover\_binlog\_info = 0

增量备份创建的替代方法：（同上，只需要一次就行）  
可以使用指定–incremental-lsn来代替–incremental-basedir的方法创建增量备份。  
\[root@xuegod120 ~\]\# innobackupex --user=root --password=123456 --incremental /tmp/db\_backup/ --incremental-lsn=2530143  
\#这里的lsn号，对应全量备份的to\_lsn的数值

还原增量备份  
增量备份的恢复比全备要复杂一点，第一步是在所有备份目录下重做已提交的日志，如：  
\[root@xuegod120 ~\]\# innobackupex --apply-log --redo-only BASE-DIR  
\[root@xuegod120 ~\]\# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1  
\[root@xuegod120 ~\]\# innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

注意：如果仅有一份增量备份，第2条语句忽略  
其中BASE-DIR是指全备目录，INCREMENTAL-DIR-1是指第一次的增量备份，INCREMENTAL-DIR-2是指第二次的增量备份，以此类推。

这里要注意的是：最后一步的增量备份并没有–redo-only选项！  
以上语句执行成功之后，最终数据在BASE-DIR（即全备目录）下。

实际还原如下：  
\[root@xuegod120 ~\]\# innobackupex --apply-log --redo-only /tmp/db\_backup/2019-06-11\_23-36-00/ \#第一次还原全备目录  
\[root@xuegod120 ~\]\# innobackupex --apply-log /tmp/db\_backup/2019-06-11\_23-41-09/ \#第二次还原最后一次增量备份，不需要加redo-only

第一步完成之后，我们开始第二步------回滚未完成的日志：  
\[root@xuegod120 ~\]\# innobackupex --apply-log /tmp/db\_backup/2019-06-11\_23-36-00/  
\#这里需要再次对全备目录进行一次日志回滚，不加redo-only参数

上面执行完之后，BASE-DIR里的备份文件已完全准备就绪，最后一步是拷贝：  
\[root@xuegod120 ~\]\# innobackupex --copy-back /tmp/db\_backup/2019-06-11\_23-36-00/  
\#执行恢复之前，先stop掉mysql服务，然后删除/var/lib/mysql/下的数据库文件，在进行恢复

恢复mysql权限  
\[root@xuegod120 ~\]\# chown -R mysql.mysql /var/lib/mysql/

最后启动  
\[root@xuegod120 ~\]\# systemctl start mysqld  
检验数据是否恢复正常

四、 MySql基础入门-mysql体系结构、及用户管理  
1、 mysql体系结构  
由：连接池组件、管理服务和工具组件、sql接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件组成。  
mysql是独有的插件式体系结构，各个存储引擎有自己的特点。

MySQl各个存储引擎概述：  
innodb存储引擎：\[/color\]\[/b\] 面向oltp(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeaable级别（可重复读）通过next-keylocking策略避免幻读、插入缓冲、二次写、自适应哈希索引、预读

BDB存储引擎：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。

myisam存储引擎:  不支持事务、表锁、全文索引、适合olap（在线分析处理），其中myd:放数据文件，myi:放索引文件 
    
    ndb存储引擎：集群存储引擎，share nothing，可提高可用性 
    
    memory存储引擎： 数据存放在内存中，表锁，并发性能差，默认使用哈希索引 
    
    archive存储引擎：只支持insert和select zlib算法压缩1：10，适合存储归档数据如日志等、行锁

maria存储引擎： 目的取代myisam、缓存数据和索引、行锁、mvcc

2、 创建用户  
mysql> create user ‘test’@‘localhost’ identified by ‘123456’;  
Query OK, 0 rows affected (0.00 sec)  
\#test=创建的用户；localhost=本地登录权限；identified by=用户的密码

mysql> create user ‘test’@’%’ identified by ‘123456’; \#%表示所有，允许远程登录的用户  
Query OK, 0 rows affected (0.00 sec)

3、 用户授权  
授权格式：grant 权限 on 数据库.\* to 用户名@登录主机 identified by ‘密码’;  
mysql> create database `db`;  
Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on db.\* to “test”@“localhost” identified by “123456”;  
Query OK, 0 rows affected, 1 warning (0.00 sec) \#test用户允许所有操作db的库权限

mysql> grant select,update on db.\* to “test”@“localhost” identified by “123456”;  
Query OK, 0 rows affected, 1 warning (0.00 sec) \#test用户允许查询、更新db库权限

mysql> flush privileges; \#刷新系统权限  
Query OK, 0 rows affected (0.01 sec)

删除用户：  
mysql> delete from user where user=‘test’;  
Query OK, 3 rows affected (0.00 sec)

5、 忘记密码，修改方法  
在/etc/my.cnf的\[mysqld\] 中加上一行跳过权限限制  
\[root@xuegod120 ~\]\# vim /etc/my.cnf  
skip-grant-tables  
\#注意：如果有validate-password=off 请注释掉或删除掉，否则重启报错

保存退出 重启mysql服务  
\[root@xuegod120 ~\]\# systemctl restart mysqld  
\[root@xuegod120 ~\]\# mysql  
mysql> update user set authentication\_string=password(‘654321’) where user=‘root’;  
mysql> flush privileges;

[master.info]: http://master.info
[mysql-autoback.sh]: http://mysql-autoback.sh
[https_www.percona.com_downloads_XtraBackup_Percona-XtraBackup-2.4.9_binary_redhat_7_x86_64_Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar]: https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar