[toc]
mysql物理备份 xtrabackup
xtrabackup备份方式(物理备份)
-
对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备
-
对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式
-
备份时读取配置文件
/etc/my.cnf
1.安装xtrabackup
1.1 下载软件包并安装
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.20/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
yum -y localinstall percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
1.2 查看版本
$ xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/usr/local/mysql/data --server-id=1 --log_bin=binlog
xtrabackup version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
2.xtrabackup全备
xtrabackup
-
xtrabackup可以在不加锁的情况下备份innodb数据表,不过此工具不能操作myisam。
innobackupex
- innobackupex是一个封装了xtrabackup的脚本,能同时处理innodb和myisam,但在处理myisam时需要加一个读锁。
利用存储过程生成大量数据
db1、db2每个库中有两张表,每张表10万条数据
#1.创建数据库
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
#2.创建表
mysql> use db1;
Database changed
mysql> create table db1_t1(
id int,
name varchar(20),
gender char(6),
email varchar(50),
first_name char(10),
last_name char(10)
);
Query OK, 0 rows affected (0.01 sec)
#3.创建存储过程
mysql> delimiter $$ #声明存储过程的结束符号为$$
mysql> create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<100001)do #插入10万条数据
insert into db1_t1 values(i,'xboyww','man',concat( 'xboyww',i,'@qq'),concat('a',i),concat('b',i));
set i=i+1;
end while;
END$$ #$$结束
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; #重新声明分号为结束符号,注意有空格
#4.查看存储过程
show create procedure auto_insert1\G
#5.调用存储过程
call auto_insert1();
#6.查看数据
mysql> select count(*) from s1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.04 sec)
mysql> select * from s1 limit 10;
+------+--------+--------+-------------+------------+-----------+
| id | name | gender | email | first_name | last_name |
+------+--------+--------+-------------+------------+-----------+
| 1 | xboyww | man | xboyww1@qq | a1 | b1 |
| 2 | xboyww | man | xboyww2@qq | a2 | b2 |
| 3 | xboyww | man | xboyww3@qq | a3 | b3 |
| 4 | xboyww | man | xboyww4@qq | a4 | b4 |
| 5 | xboyww | man | xboyww5@qq | a5 | b5 |
| 6 | xboyww | man | xboyww6@qq | a6 | b6 |
| 7 | xboyww | man | xboyww7@qq | a7 | b7 |
| 8 | xboyww | man | xboyww8@qq | a8 | b8 |
| 9 | xboyww | man | xboyww9@qq | a9 | b9 |
| 10 | xboyww | man | xboyww10@qq | a10 | b10 |
+------+--------+--------+-------------+------------+-----------+
10 rows in set (0.00 sec)
#删除存储过程
DROP PROCEDURE auto_insert1;
db3,一张表,两条数据
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)
mysql> create table t3(id int,name char(10)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t3 values(1,'xiaoming'),(2,'xiaohong');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t3;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaohong |
+------+----------+
2 rows in set (0.00 sec)
2.1 全备
2.1.1 备份方法一 xtrabackup --backup
执行备份命令
xtrabackup -uroot -p --backup --target-dir=/data/backups/
/data/backups
备份目录下的内容
$ ll
总用量 12340
-rw-r----- 1 root root 487 7月 2 22:43 backup-my.cnf
drwxr-x--- 2 root root 92 7月 2 22:43 db1
drwxr-x--- 2 root root 92 7月 2 22:43 db2
drwxr-x--- 2 root root 62 7月 2 22:43 db3
-rw-r----- 1 root root 646 7月 2 22:43 ib_buffer_pool
-rw-r----- 1 root root 12582912 7月 2 22:43 ibdata1
drwxr-x--- 2 root root 4096 7月 2 22:43 mysql
drwxr-x--- 2 root root 8192 7月 2 22:43 performance_schema
drwxr-x--- 2 root root 8192 7月 2 22:43 sys
-rw-r----- 1 root root 19 7月 2 22:43 xtrabackup_binlog_info
-rw-r----- 1 root root 141 7月 2 22:43 xtrabackup_checkpoints
-rw-r----- 1 root root 474 7月 2 22:43 xtrabackup_info
-rw-r----- 1 root root 2560 7月 2 22:43 xtrabackup_logfile
2.1.2 备份方法二 innobackupex
执行备份命令
-S /var/lib/mysql/mysql.sock
-S选项可以不加,会从mysql配置文件/etc/my.cnf
中读取sock文件位置
innobackupex -uroot -p1 /backup
备份完成后会在/backup
目录下生成一个以时间命令并精确到秒的目录
$ ls
2020-07-02_22-57-09
$ cd 2020-07-02_22-57-09/
$ ll
总用量 12340
-rw-r----- 1 root root 487 7月 2 22:57 backup-my.cnf
drwxr-x--- 2 root root 92 7月 2 22:57 db1
drwxr-x--- 2 root root 92 7月 2 22:57 db2
drwxr-x--- 2 root root 62 7月 2 22:57 db3
-rw-r----- 1 root root 646 7月 2 22:57 ib_buffer_pool
-rw-r----- 1 root root 12582912 7月 2 22:57 ibdata1
drwxr-x--- 2 root root 4096 7月 2 22:57 mysql
drwxr-x--- 2 root root 8192 7月 2 22:57 performance_schema
drwxr-x--- 2 root root 8192 7月 2 22:57 sys
-rw-r----- 1 root root 19 7月 2 22:57 xtrabackup_binlog_info
-rw-r----- 1 root root 141 7月 2 22:57 xtrabackup_checkpoints
-rw-r----- 1 root root 494 7月 2 22:57 xtrabackup_info
-rw-r----- 1 root root 2560 7月 2 22:57 xtrabackup_logfile
如果想要自主命名备份目录的话,需要加参数--no-timestamp
innobackupex -uroot -p1 --no-timestamp /backup/bak
在备份目录下会有一个文件xtrabackup_binlog_info
,这个文件记录了binlog文件名和binlog的位置点
$ cat xtrabackup_binlog_info
binlog.000009 1061
在备份目录会有一个文件xtrabackup_info
,这个文件记录了备份信息汇总
$ cat xtrabackup_info
uuid = c47c158f-bc74-11ea-82fa-001c42f33c78
name =
tool_name = innobackupex
tool_command = --user=root --password=... --no-timestamp /backup/bak
tool_version = 2.4.20
ibbackup_version = 2.4.20
server_version = 5.7.28-log
start_time = 2020-07-02 23:00:27
end_time = 2020-07-02 23:00:28
lock_time = 0
binlog_pos = filename 'binlog.000009', position '1061'
innodb_from_lsn = 0
innodb_to_lsn = 332706000
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
在备份目录下会有一个文件xtrabackup_logfile
,这个文件是备份的redo_log文件
$ ll xtrabackup_logfile
-rw-r----- 1 root root 2560 7月 2 23:00 xtrabackup_logfile
2.2 全备恢复
2.2.1 查看原有数据库
有3个库(db1,db2,db3),
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
2.2.2 原有数据库中的表
#db1,有两张表,每张表中有10万条数据
mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| db1_t1 |
| db1_t2 |
+---------------+
2 rows in set (0.00 sec)
mysql> select count(*) from db1_t1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from db1_t2;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.04 sec)
#db2,有两张表,每张表中有10万条数据
mysql> use db1;
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| db2_t1 |
| db2_t2 |
+---------------+
2 rows in set (0.00 sec)
mysql> select count(*) from db2_t1;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from db2_t2;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.03 sec)
#db3,有1张表,表中2行数据
mysql> use db3;
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| t3 |
+---------------+
1 row in set (0.00 sec)
mysql> select count(*) from t3;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)