Mysql 与Xtrabackup 详解

简介

CentOS 7.0中，已经使用MariaDB替代了MySQL数据库,因为MYSQL被Oracle收购以后， MYSQL兄弟MariaDB继续开源事业。

安装

yum -y install httpd mariadb-server mariadb

启动服务、查看服务

systemctl start mariadb 
systemctl enable mariadb 
systemctl status mariadb

安全配置向导

安装完mysql-server 会提示可以运行mysql_secure_installation。运行mysql_secure_installation会执行几个设置：
1.为root用户设置密码
2.删除匿名账号
3.取消root用户远程登录
4.删除test库和对test库的访问权限
5.刷新授权表使修改生效

mysql_secure_installation，详细步骤请参看下面的命令:
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):#<–初次运行直接回车
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] #<– 是否设置root用户密码（如果已设置密码，输n）
... skipping
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] #<– 是否删除匿名用户,生产环境建议删除，所以直接回车或Y
… Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] #<–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] #<– 是否删除test数据库,直接回车或Y
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] #<– 是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

测试数据库连接

语法mysql -h IP -u USER -pPASS

mysql -u root –p –h 10.10.10.68

字符集

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

MySQL数据库常见字符集介绍

MySQL怎样选择合适的字符集

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

查看支持数据库所有的字符集

mysql -uroot -e "show character set \G;"

查看当前字符集设置情况

show variables like 'character_set%';

实操作

将字符集为latin1已有记录的数据库test转成utf8，并且已经存在的记录不乱码
1.导出表结构（此处一定要按原数据表字符集导）

mysqldump –uroot --default-character-set=latin1 -d test> testtable.sql

2. 将testtable.sql中latin1修改成utf8
3. 确保数据库不再更新，导出所有数据

mysqldump -uroot --quick --no-create-info --extended-insert --default-character-set=latin1 test>testdata.sql

参数说明

--quick:用于转储大的表，强制mysqldump从服务器一次一行的检索数据而不是检索所有行，并输出当前cache到内存中
--no-create-info:不要创建create table语句
--extended-insert:使用包括几个values列表的多行insert语法，这样文件更小，IO也小，导入数据时会非常快
--default-character-set=latin1:按照原有字符集导出数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码

3.打开testdata.sql 将SET NAME latin1 修改成SET NAME utf8
4.重新建库

create database test2 default charset utf8;

5.导表结构

mysql –uroot test2<testtable.sql

6.导数据

mysql -uroot test2 <testdata.sql

注意：选择目标字符集时，要注意最好大于等于原字符集（字库更大），否则可能会丢失不被支持的数据

Mysql备份及恢复

简介

按照备份时对数据库的影响范围分为

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

按照备份后文件内容

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

按照备份数据库的内容来分

完全备份：对数据库完整的备份
增量备份：在上一次完全备份基础上，对更新的数据进行备份（xtrabackup）
日志备份：二进制日志备份，主从复制

逻辑备份工具mysqldump

MySQL所提供的命令行工具mysqldump进行备份恢复，可支持MyISAM,InnoDB（数据量大的时候不推荐使用，）

备份全部数据库的数据和表结构

mysqldump -uroot -ppassword -A >all.sql

只备份结构，不备份数据

mysqldump -uroot -ppassword -A -d > database.sql

只备份数据库，不备份结构

mysqldump -uroot -ppassword -A -t > data.sql

备份单个数据库的数据和表结构（只备份数据或者表结构可以用-t或者-d）

mysqldump -uroot -password dbname > dbname.sql

一次性备份多个数据库的数据和表结构（只备份数据或者表结构可以用-t或者-d）

mysqldump -uroot -ppassword db1 db2 > dbs.sql

备份一个表结构（只备份数据可以用-t）

mysqldump -d db1 table1 -uroot -ppassword > dbs.sql

备份多个表结构（只备份数据可以用-t）

mysqldump -d -B db1 --tables table1 table2 -uroot -ppassword > dbs.sql

还原数据库

mysql -uroot -ppassword
mysql> source all.sql

参数

--all-databases, -A： 备份所有数据库
--databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。
--force, -f：即使发现sql错误，仍然继续备份
--host=host_name, -h host_name：备份主机名，默认为localhost
--no-data, -d：只导出表结构
--password[=password], -p[password]：密码
--port=port_num, -P port_num：制定TCP/IP连接时的端口号
--quick, -q：快速导出
--tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
--user=user_name, -u user_name：用户名
--xml, -X：导出为xml文件

xtrabackup备份工具使用

针对InnoDB存储引擎，MySQL本身没有提供合适的热备工具，开源、免费的Xtrabackup热备工具，它可实现ibbackup的所有功能，并且还扩展支持真正的增量备份功能。
Xtrabackup包括两个主要工具：Xtrabackup和innobackupex：
Xtrabackup只能备份InnoDB和XtraDB两种引擎表，而不能备份MyISAM数据表。
innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等，智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备的功能

XtraBackup优势

1.备份过程不锁表，快速可靠
2.备份过程不会打断正在执行的事务
3.能够基于压缩等功能节约磁盘空间和流量

安装

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 
tar -xvf Percona-XtraBackup-2.4.9-ra467167cdd4-el7-x86_64-bundle.tar 
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=/usr/local/mysql/data，然后重启mysql

vim /etc/my.cnf  #在mysqld的范围添加
[mysqld]
datadir=/usr/local/mysql/data
systemctl restart mysqld    #重启msyql

xtrbackup使用

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

备份权限准备工作

XtraBackup需要能够连接到数据库服务器，并创建备份、在某些场景中进行准备和恢复备份时，在服务器的datadir上执行操作。为了做到这一点，必须满足其执行的特权和权限要求。无论使用xtrabackup还是innobackupex，都会涉及两个角色。
调用程序的用户(系统用户)和在数据库服务器中执行操作的用户(数据库用户)。

系统用户权限

它需要有对备份目录的读写权限以及数据目录(datadir)的读、写和执行权限。
注意:备份后的文件属性为执行命令时的用户的属性。例如，原数据库文件属于mysql用户，root用户调用命令则生成的文件的属主属于root用户，同样恢复时属性也不会变化，需要手动更改为mysql属主。

数据库用户权限

由于备份时需要使用账号密码连接到数据库，所以需要有适当权限(不推荐使用root)

mysql>CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'password'; 
mysql>GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; 
mysql> FLUSH PRIVILEGES;

参数

1.RELOAD重新加载和LOCK TABLES锁定表(除非指定了-no-lock选项)二个权限，LOCK TABLES是为了在开始复制文件之前使用READ LOCK读锁, 在使用备份锁时，用于备份的锁表和用于备份的锁BINLOG需要此特权。RELOAD是为了刷新引擎日志来FLUSH TALBES刷新表。
2.REPLICATION CLIENT复制权限，是客户端为了获得二进制日志位置。
3.TABLESPACE表空间权限，创建表空间以导入表(参见恢复各个表)。
4.PROCESS进程权限，是为了运行SHOW ENGINE INNODB STATUS(这是强制性的)，并可选地看到所有线程运行在服务器上(见改进的嵌入式表与读锁处理)。
5.SUPER权限，为了在复制环境中启动/停止从线程，使用XtraDB更改后的页面跟踪来进行增量备份和改进的具有读锁处理的刷新表。
6.CREATE privilege特权 是为了创建PERCONA_SCHEMA.xtrabackup_history库和表。
7.INSERT privilege特权，是为了添加历史记录PERCONA_SCHEMA.xtrabackup_history 表。
8.select privilege特权，是为了使用innobackupex—incremental-history-name或innobackupex—incremental-history-uuid，以便特性在PERCONA_SCHEMA.xtrabackup_history 表中查找innodb_to_lsn值。

全备

innobackupex --user=bkpuser --password=password /tmp/db_backup/ 2>>/tmp/db_backup/db_backup.log   #不显示输出信息，输出信息重定向到db_backup.log

内部机制：在备份的时候innobackupex会调用xtrabackup来备份innodb表，并复制所有的表定义，其他引擎的表(MyISAM,MERGE,CSV,ARCHIVE)。
其它选项

其他选项:
--no-timestamp，指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹。
--default-file，指定配置文件，用来配置innobackupex的选项
innobackupex --user=bkpuser --password=password --no-timestamp /tmp/db_backup/full
innobackupex --user=bkpuser --password=password --no-timestamp /tmp/db_backup/full (使用--no-timestamp时，后面的这个full目录必须跟上且不能提前自己建立，它由innobackupex自动建立，否则会报错innobackupex: Error: Failed to create backup directory)

还原备份

Systemctl stop mysqld #停止mysql
rm -rf /var/lib/mysql/* #删除数据：（危险动作）：
innobackupex --copy-back /tmp/db_backup/xxxfile/  #还原，其中xxxfile是备份生成的文件
ll /usr/local/mysql/data/  #查看权限
chown -R mysql:mysql /var/lib/mysql/   #重新授权，要不然mysql启动不起来
systemctl restart mysqld

注：datadir必须是为空的，innobackupex –copy-back不会覆盖已存在的文件，还要注意，还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir。

增量备份

增量备份作用：减少备份数据重复，节省磁盘空间，缩短备份时间
增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。
在创建增量备份之前需要一个全备，不然增量备份是没有意义的。
检查备份文件夹下的xtrabackup-checkpoints，查看信息

cat /tmp/db_backup/ xxxfile /xtrabackup_checkpoints
backup_type = full-backuped #全备的意思
from_lsn = 0
to_lsn = 1600459
last_lsn = 16004592：创建第一个增量备份

然后数据有变化后，使用格式 innobackupex --user=bkpuser --password=password --incremental /增量1路径 --incremental-basedir=全备路径 后面指定这哪个全备上进行增量备份

innobackupex --user=bkpuser --password=password --incremental /tmp/db_backup/ --incremental-basedir=/tmp/db_backup/ xxxfile /

再查看LSN<日志序列号>

cat /tmp/xxxfile/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1600459
to_lsn = 1601301
last_lsn = 1601301

增量备份创建的替代方法

可以使用指定—incremental-lsn来代替—incremental-basedir的方法创建增量备份。

innobackupex --user=bkpuser --password=password --incremental /tmp/db_backup/ --incremental-lsn=1600459  #从这个编号点开始备份

增量备份还原

增量备份的恢复比全备要复杂一点，第一步是在所有备份目录下重做已提交的日志，如：

innobackupex --apply-log --redo-only BASE-DIR  
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1  
innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2

注意1：如果仅有一份增量备份，第2条语句忽略，其中BASE-DIR是指全备目录，INCREMENTAL-DIR-1是指第一次的增量备份，INCREMENTAL-DIR-2是指第二次的增量备份，以此类推。
注意2：最后一步的增量备份并没有–redo-only选项！
以上语句执行成功之后，最终数据在BASE-DIR（即全备目录）下。
开始回滚未完成的日志

innobackupex --apply-log BASE-DIR

然后BASE-DIR里的备份文件已完全准备就绪，最后一步是拷贝

innobackupex --copy-back BASE-DIR
chown –R mysql.mysql /var/lib/mysql/ #恢复mysql权限
systemctl start mysqld  #最后启动

检验数据是否恢复正常