linux运维、架构之路-xtrabackup
2024-10-07 09:46:19
一、XtraBackup介绍
1、备份工具
xtrabackup:是一款基于InnoDB的在线热备工具,具有开源,免费,支持在线热备,占用磁盘空间小,能够非常快速地
备份与恢复mysql数据库,(备份时不影响数据读写)
官方下载地址为http://www.percona.com/software/percona-xtrabackup
mysqldump:备份是逻辑备份,备份出来的文件是sql语句,但是当备份MySQL数据超过10G时,用mysqldump来导出备份就比较慢了
2、备份原理
3、xtrabackup包含两个主要的工具
①xtrabackup只能备份innodb和xtradb两种引擎的表,而不能备份myisam引擎的表;
②innobackupex是一个封装了xtrabackup的Perl脚本,支持同时备份innodb和myisam,但在对myisam备份时需要加一个全局的读锁,还有就是myisam不支持增量备份
innobackupex备份和恢复的工作原理
二、XtraBackup安装部署
1、环境
①db
[root@db ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@db ~]# uname -r
2.6.-.el6.x86_64
[root@db ~]# /etc/init.d/iptables status
iptables: Firewall is not running.
[root@db ~]# getenforce
Disabled
[root@db ~]# hostname -I
172.19.5.54 172.16.1.54
②db01
[root@db01 ~]# cat /etc/redhat-release
CentOS release 6.9 (Final)
[root@db01 ~]# uname -r
2.6.-.el6.x86_64
[root@db01 ~]# /etc/init.d/iptables status
iptables: Firewall is not running.
[root@db01 ~]# getenforce
Disabled
[root@db01 ~]# hostname -I
172.19.5.55 172.16.1.55
2、安装XtraBackup
cd /server/tools/
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
yum localinstall percona-xtrabackup--2.4.-.el6.x86_64.rpm -y
3、db服务器模拟插入数据
create database oldboy;#创建oldboy库
use oldboy;
create table test( id int() not null,name char() not null);#在oldboy库中创建test表
insert into test values(,"oldboy");#test表中插入数据
insert into test values(,"oldgirl");
insert into test values(,"inca");
insert into test values(,"zuma");
insert into test values(,"kaka");
insert into test values(,"老男孩");
查看test表中内容
mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| | oldboy |
| | oldgirl |
| | inca |
| | zuma |
| | kaka |
| | 老男孩 |
+----+-----------+
rows in set (0.00 sec)
4、备份数据
①db操作
mkdir /backup -p && cd /backup
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='' --socket=/application/mysql/tmp/mysql.sock /backup/
#备份完成会出现如下内容
……
xtrabackup: Transaction log of lsn () to () was copied.
:40:30 completed OK!
②查看备份的内容
[root@db backup]# ll /backup/--09_11--/
total
-rw-r----- root root -- : backup-my.cnf
-rw-r----- root root -- : ibdata1
-rw-r----- root root -- : ib_logfile0
-rw-r----- root root -- : ib_logfile1
-rw-r----- root root -- : ibtmp1
drwxr-x--- root root -- : mysql
drwxr-x--- root root -- : oldboy
drwxr-x--- root root -- : performance_schema
-rw-r----- root root -- : xtrabackup_binlog_info
-rw-r--r-- root root -- : xtrabackup_binlog_pos_innodb
-rw-r----- root root -- : xtrabackup_checkpoints
-rw-r----- root root -- : xtrabackup_info
-rw-r----- root root -- : xtrabackup_logfile
③预处理数据
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='' --socket=/application/mysql/tmp/mysql.sock --apply-log --use-memory=1G /backup/--09_11--/
④模拟删除oldboy库,恢复全备
drop database oldboy;
⑤为了保持数据一致性,停库恢复
[root@db ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
rm -fr /application/mysql/data/*
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --move-back /backup/2018-01-09_11-57-49/
⑥恢复全备成功后,重新授权数据目录,启动MySQL
chown -R mysql.mysql /application/mysql/data/
[root@db mysql]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
#登录数据库查看恢复的数据
mysql> select * from oldboy.test;
+----+-----------+
| id | name |
+----+-----------+
| | oldboy |
| | oldgirl |
| | inca |
| | zuma |
| | kaka |
| | 老男孩 |
+----+-----------+
rows in set (0.00 sec)
三、主从复制部署
1、授权从库连接用户
grant replication slave on *.* to 'rep'@'172.19.5.%' identified by '';
2、分发全备数据到从库db01
scp -rp --09_14--/ 172.19.5.55:/backup/
3、从库预处理
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='' --socket=/application/mysql/tmp/mysql.sock --apply-log --use-memory=1G /backup/--09_10--/
4、停从库,恢复数据
[root@db01 data]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
rm -fr /application/mysql/data/*
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --copy-back /backup/2018-01-09_14-58-50/
5、查看master位置点
[root@db01 --09_14--]# cat /backup/--09_14--/xtrabackup_binlog_info
mysql-bin.
6、启动从库db01,设置主从复制
chown -R mysql.mysql /application/mysql/
/etc/init.d/mysqld start
#登录数据库
CHANGE MASTER TO
MASTER_HOST='172.19.5.54',
MASTER_PORT=,
MASTER_USER='rep',
MASTER_PASSWORD='',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=;
开启主从同步功能
mysql> start slave;
Query OK, rows affected (0.00 sec) mysql> show slave status\G
*************************** . row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.5.54
Master_User: rep
Master_Port:
Connect_Retry:
Master_Log_File: mysql-bin.
Read_Master_Log_Pos:
Relay_Log_File: db01-relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File: mysql-bin.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:
四、主从复制架构扩展
1、主从延迟复制
stop slave;
change master to master_delay = N(600秒);
start slave;
应用场景
①误删除恢复
②测试
③历史查询
2、半同步复制
①master配置
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled = ;
set global rpl_semi_sync_master_timeout = ;
②slave配置
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled = ;
set global rpl_semi_sync_master_timeout = ;
③查看半同步是否开启
stop slave io_thread;
start slave io_thread;#重启IO线程
show global status like 'rpl_sem%';#查看半同步状态
最新文章
- Python学习笔记(三)——类型与变量
- 白话学习MVC(六)模型绑定
- VBA_Excel_教程:变量,数组
- poj1819Disks
- [MarsZ]程序猿谈大学之大学应该学好哪些课程
- svn is alread locked
- Pet
- Tree(未解决。。。)
- Cache基础知识OR1200在ICache一个简短的引论
- 妙用this关键字
- RTL8201EL介绍【转】
- linux学习笔记 apache php mysql +linux
- linux git 保存用户名和密码
- python 定义函数 两个文件调用函数
- Go Example--定时器
- mongodb ISODate问题(大量数据update优化)
- Java英文单词Java基础常见英语词汇
- Oracle 表空间与数据文件
- eclipse 项目转as项目时 .so文件 jniLibs的设置
- Python模块:Random(未完待续)