重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
安装Xtrabackup实现备份MySQL详细步骤-创新互联
下文我给大家简单讲讲关于安装Xtrabackup实现备份MySQL详细步骤，大家之前了解过相关类似主题内容吗？感兴趣的话就一起来看看这篇文章吧，相信看完安装Xtrabackup实现备份MySQL详细步骤对大家多少有点帮助吧。
一、安装Xtrabackup
# wget --no-check-certificate http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm # rpm -ivh percona-release-0.1-4.noarch.rpm # yum list | grep percona # yum -y install percona-xtrabackup-24
二、安装MySQL
1.安装MySQL
# yum -y install http://repo.mysql.com//mysql57-community-release-el7-9.noarch.rpm # yum list |grep mysql-community # yum -y install mysql mysql-server mysql-devel
2.更改时间戳设置
# cat /var/log/mysqld.log |grep "timestamp" [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). # vi /etc/my.cnf explicit_defaults_for_timestamp=true ##显示指定默认值为timestamp类型的字段
3.启动MySQL
# systemctl start mysqld # systemctl status mysqld
4.配置MySQL密码
# mysql Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
a.新版本MySQL安装完成后会生成临时的初始密码
# grep 'temporary password' /var/log/mysqld.log [Note] A temporary password is generated for root@localhost: qhAnfco2o)HB
修改MySQL密码
注意：MySQL 5.7默认安装了密码安全检查插件(validate_password)，默认密码检查策略要求密码必须包含:大小写字母、数字和特殊符号，并且长度不能少于8位。
MySQL官网MySQL 5.7密码策略详细说明:
http://dev.mysql.com/doc/refman/5.7/en/validate-password-options-variables.html#sysvar_validate_password_policy
b.修改密码策略
# vi /etc/my.cnf
validate_password_policy = LOW ##密码长度不少于8位即可
# systemctl restart mysqld
# mysql -u root -p
mysql> set password for 'root'@'localhost'=password('12345678');c.官方数据库示例
# wget http://downloads.mysql.com/docs/sakila-db.tar.gz # tar -zxvf sakila-db.tar.gz sakila-db/ sakila-db/sakila-data.sql sakila-db/sakila-schema.sql sakila-db/sakila.mwb [root@localhost ~]# pwd /root # mysql -u root -p mysql> source /root/sakila-db/sakila-schema.sql ##还原数据库结构 mysql> source /root/sakila-db/sakila-data.sql ##将数据写入数据库
三、innobackupex常用命令
--backup 默认选项
--defaults-file 指定要备份的mysql实例的my.cnf文件，必须为第一个选项
--port 端口
--socket 连接套字节的位置，默认为/var/lib/mysql/mysql.sock
--host 主机
--no-timestamp 指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹
--target-dir 指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹
--use-memory 指定备份所用内存大小，默认为100M，与--apply-log同用
--apply-log 从备份恢复
--apply-log-only 在恢复时，停止恢复进程不进行LSN，只使用log
--copy-back 复制备份文件
--incremental 建立增量备份
--incremental-basedir=DIRECTORY
指定一个全库备份的目录作为增量备份的基础数据库
--incremental-dir=DIRECTORY
指定增量备份与全库备份合并建立一个新的全备目录
--prepare 从backup恢复
--compress 压缩选项
四、xtrabackup备份后的主要文件
(1)xtrabackup_checkpoints —— 备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
(2)xtrabackup_binlog_info —— mysql云服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件。
(5)backup-my.cnf —— 备份命令用到的配置选项信息。
五、Innobackupex备份
1.创建全备
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=12345678 /backup/ ...... completed OK!
2.应用全备日志
# innobackupex --apply-log /backup/2017-04-03_12-45-44/ ...... completed OK!
3.查看备份状态
# cat /backup/2017-04-03_12-45-44/xtrabackup_checkpoints backup_type = full-prepared ##全备 from_lsn = 0 ##备份开始点 to_lsn = 9692219 ##备份结束点 last_lsn = 9692228 compact = 0 recover_binlog_info = 0
4.查看二进制日志事件信息
# cat /backup/2017-04-03_12-45-44/xtrabackup_info uuid = 694e5590-1828-11e7-81d2-000c291bd2a1 name = tool_name = innobackupex tool_command = --defaults-file=/etc/my.cnf --user=root --password=... /backup/ tool_version = 2.4.6 ibbackup_version = 2.4.6 server_version = 5.7.17 start_time = 2017-04-03 21:34:09 end_time = 2017-04-03 21:34:13 lock_time = 0 binlog_pos = innodb_from_lsn = 0 innodb_to_lsn = 9692219 partial = N incremental = N format = file compact = N compressed = N encrypted = N
5.进行全备恢复
a.删除数据库、停止并破坏MySQL
# mysql -u root -p mysql> show databases; mysql> drop database sakila; Query OK, 30 rows affected (0.59 sec) # systemctl stop mysqld # cp -R /var/lib/mysql /root # rm -rf /var/lib/mysql
b.恢复全备
# innobackupex --copy-back /backup/2017-04-03_21-34-08/ ...... completed OK! # chown -R mysql.mysql /var/lib/mysql # systemctl start mysqld # mysql -u root -p mysql> show databases;
注:如无法启动SQL，可能是SELINUX的问题
# vim /etc/selinux/config SELINUX=disabled
六、Innobackupex增量备份
1.创建数据库和表
# mysql -u root -p mysql> create database abc; mysql> use abc; mysql> create table plus (id int(10),name varchar(20),phone char(11),birth date); mysql> show tables; mysql> insert into plus values(1,'tom',12345678901,'2001-01-01'); mysql> insert into plus values(2,'jack',12345678911,'2011-11-11'); mysql> select * from plus; +------+------+-------------+------------+ | id | name | phone | birth | +------+------+-------------+------------+ | 1 | tom | 12345678901 | 2001-01-01 | | 2 | jack | 12345678911 | 2011-11-11 | +------+------+-------------+------------+ 2 rows in set (0.00 sec)
2.基于全备进行增量备份
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=12345678 --incremental --incremental-basedir=/backup/2017-04-03_21-34-08/ /backup/001/ ...... completed OK! # cat /backup/001/2017-04-03_21-41-27/xtrabackup_checkpoints backup_type = incremental ##增量备份 from_lsn = 9692219 ##备份开始点 to_lsn = 9699700 ##备份结束点 last_lsn = 9699709 compact = 0 recover_binlog_info = 0
3.应用全备日志
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=12345678 --apply-log-only /backup/2017-04-03_21-34-08/ ...... completed OK!
4.应用第一次增量备份日志
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=12345678 --apply-log-only /backup/2017-04-03_21-34-08/ --incremental-dir=/backup/001/ ...... completed OK!
5.基于全备进行第一次增量备份恢复
# systemctl stop mysqld # rm -rf /var/lib/mysql # innobackupex --copy-back /backup/2017-04-03_21-34-08/2017-04-03_21-44-08/ ...... completed OK! # chown -R mysql.mysql /var/lib/mysql # systemctl start mysqld # mysql -u root -p mysql> show databases; mysql> use abc; mysql> select * from plus; +------+------+-------------+------------+ | id | name | phone | birth | +------+------+-------------+------------+ | 1 | tom | 12345678901 | 2001-01-01 | | 2 | jack | 12345678911 | 2011-11-11 | +------+------+-------------+------------+ 2 rows in set (0.00 sec)
6.基于第一次增量备份进行备份
a.向表中添加数据
# mysql -u root -p mysql> use abc; mysql> insert into plus values(3,'rose',12345678912,'2012-12-12'); mysql> insert into plus values(4,'jordan',12345678923,'2012-12-23');
b.应用第二次增量备份日志
# innobackupex --defaults-file=/etc/my.cnf --user=root --password=12345678 --apply-log-only /backup/2017-04-03_21-34-08/ --incremental-dir=/backup/002/ ...... completed OK!
c.查看备份状态
# cat /backup/002/2017-04-03_21-48-54/xtrabackup_checkpoints backup_type = incremental from_lsn = 9699700 to_lsn = 9696137 last_lsn = 9696146 compact = 0 recover_binlog_info = 0
d.基于全备份和第一次增量备份，恢复第二次增量备份
# systemctl stop mysqld # rm -rf /var/lib/mysql # innobackupex --copy-back /backup/2017-04-03_21-34-08/2017-04-03_21-50-11/ ...... completed OK! # chown -R mysql.mysql /var/lib/mysql # systemctl start mysqld # mysql -u root -p mysql> show databases; mysql> use abc; mysql> select * from plus; +------+--------+-------------+------------+ | id | name | phone | birth | +------+--------+-------------+------------+ | 1 | tom | 12345678901 | 2001-01-01 | | 2 | jack | 12345678911 | 2011-11-11 | | 3 | rose | 12345678912 | 2012-12-12 | | 4 | jordan | 12345678923 | 2012-12-23 | +------+--------+-------------+------------+ 4 rows in set (0.00 sec)
七、Xtrabackup备份
1.创建全备
# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --backup --target-dir=/backup/full ...... completed OK!
2.应用全备日志
# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=root --password=12345678 --apply-log-only --target-dir=/backup/full ...... completed OK!
3.查看备份状态
# cat /backup/full/xtrabackup_checkpoints backup_type = log-applied from_lsn = 0 to_lsn = 9692712 last_lsn = 9692721 compact = 0 recover_binlog_info = 0
4.恢复备份
# systemctl stop mysqld # rm -rf /var/lib/mysql # cd /backup/full/ # rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /var/lib/mysql sent 151722380 bytes received 6476 bytes 15971458.53 bytes/sec total size is 151681109 speedup is 1.00 # chown -R mysql.mysql /var/lib/mysql # systemctl start mysqld # mysql -u root -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | abc | | mysql | | performance_schema | | sakila | | sys | +--------------------+ 6 rows in set (0.09 sec)
八、Xtrabackup增量备份
1.第一次增量备份
# mysql -u root -p mysql> create database ball; mysql> use ball; mysql> create table superstar (id int(5),name varchar(20),number int(2),city varchar(20),team varchar(10)); mysql> insert into superstar values(1,'Jordan',23,'Chicago','Bulls'); mysql> insert into superstar values(2,'Yao',11,'Houston','Rockets');
2.应用第一次增量备份日志
# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full ...... completed OK!
3.查看备份状态
# cat /backup/inc1/xtrabackup_checkpoints backup_type = incremental from_lsn = 9692712 to_lsn = 9763373 last_lsn = 9763382 compact = 0 recover_binlog_info = 0
4.第二次增量备份
# mysql -u root -p mysql> use ball; mysql> insert into superstar values(3,'Russell',6,'Boston','Celtics'); mysql> insert into superstar values(4,'Pierce',34,'Boston','Celtics');
5.应用第二次增量备份日志
# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1/
6.查看备份状态
# cat /backup/inc2/xtrabackup_checkpoints backup_type = incremental from_lsn = 9763373 to_lsn = 9766964 last_lsn = 9766973 compact = 0 recover_binlog_info = 0
7.准备第一次增量备份
# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --prepare --apply-log-only --target-dir=/backup/full --incremental-dir=/backup/inc1 ...... completed OK!
8.准备第二次增量备份
# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --prepare --target-dir=/backup/full --incremental-dir=/backup/inc2 ...... completed OK!
9.合并恢复备份
# systemctl stop mysqld # rm -rf /var/lib/mysql # xtrabackup --defaults-file=/etc/my.cnf --user=root --password=12345678 --copy-back --target-dir=/backup/full ...... completed OK! # chown -R mysql.mysql /var/lib/mysql # systemctl start mysqld # mysql -u root -p mysql> use ball; mysql> select * from superstar; +------+---------+--------+---------+---------+ | id | name | number | city | team | +------+---------+--------+---------+---------+ | 1 | Jordan | 23 | Chicago | Bulls | | 2 | Yao | 11 | Houston | Rockets | | 3 | Russell | 6 | Boston | Celtics | | 4 | Pierce | 34 | Boston | Celtics | +------+---------+--------+---------+---------+ 4 rows in set (0.00 sec)
大家觉得安装Xtrabackup实现备份MySQL详细步骤这篇文章怎么样，是否有所收获。如果想要了解更多相关，可以继续关注我们的行业资讯板块。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
当前题目：安装Xtrabackup实现备份MySQL详细步骤-创新互联
标题来源：http://cqcxhl.com/article/didjio.html