MySQL字符集

MySQL字符集

一、 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 setG;” |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

5、 查看mysql当前的字符集
mysql> show variables like ‘character_set%’;
±-------------------------±---------------------------+
| Variable_name | Value |
±-------------------------±---------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
±-------------------------±---------------------------+
8 rows in set (0.00 sec)

扩展：修改mysql的字符集
直接修改mysql的配置文件myf文件，添加如下：
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> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| book |
| book2 |
| mysql |
| performance_schema |
| sys |
±-------------------+
6 rows in set (0.00 sec)

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

mysql> show tables;
±----------------+
| Tables_in_book2 |
±----------------+
| books |
| category |
±----------------+
2 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)

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.

9） 查看表内容是否还是乱码
mysql> use book2;
Database changed
mysql> show tables;
±----------------+
| Tables_in_book2 |
±----------------+
| books |
| category |
±----------------+
2 rows in set (0.00 sec)

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 &quot; t i m e = &quot; savedir&quot; time=&quot; 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 .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/myf配置文件制定数据目录，因为Xtrabackup是根据你的/etc/myf配置文件来获取你备份的文件，比如在/etc/myf的[mysqld] 下添加datadir=/var/lib/mysql，然后重启mysql

修改配置文件：
[root@xuegod120 ~]# vim /etc/myf
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-myf 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-myf 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 -sql /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)

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.24 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 -sql /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)

4、 删除用户
先查询用户表：
mysql> select user,host from user;
±--------------±----------+
| user | host |
±--------------±----------+
| test | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
| test | localhost |
| test | locathost |
±--------------±----------+
6 rows in set (0.00 sec)

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

mysql> select user,host from user;
±--------------±----------+
| user | host |
±--------------±----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
±--------------±----------+
3 rows in set (0.00 sec)

5、 忘记密码，修改方法
在/etc/myf的[mysqld] 中加上一行跳过权限限制
[root@xuegod120 ~]# vim /etc/myf
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;