表迁移工具的选型-xtrabackup的使用

更新：2022-05-23 13:19:20编辑：Story归类：数据库人气：170

1.1. 场景

有的时候test人员可能需要在测试库上比较新的数据，这时候只能是从生产库上面去那了。如果是小表还好实用mysqldump/mysqlpump就可以轻松的解决。但是，如果遇到了大表这将是一个很痛苦的过程。这时候最好的选择就是使用Percona公司的MySQL热备工具xtrabackup了。

1.2. 为什么不使用ibd文件拷贝方法

很简单，因为要锁表对生产环境影响比较大。

1.3. 扩展

当然如果他们数据的要求并不是那么高可以使用每天用xtrabackup备份的来做。但是，这往往会比现场直接备份生产库的某张表来的麻烦，因为往往我们使用的是增量备份，还要应用之前的所有日志。而且为了防止破坏备份数据，还需要拷贝一份。

1.4. 先决条件

前提必须开启innodb_file_per_table选项，并且使用InnoDB存储引擎。

set global innodb_file_per_table = 1;

1	set global innodb_file_per_table = 1 ;

由于我使用的是 Percona Server 5.7.10-3 所以需要使用的xtrabackup版本为2.4.1

1.5. 制造大表

下面我们制造表数据，下面模拟的数据比较小，主要是为了节省时间。

USE test;
-- 创建表t1
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
  id BIGINT unsigned NOT NULL AUTO_INCREMENT,
  x VARCHAR(500) NOT NULL,
  y VARCHAR(500) NOT NULL,
  PRIMARY KEY(id)
);

-- 创建添加数据存储过程
DROP PROCEDURE insert_batch;
DELIMITER //
CREATE PROCEDURE insert_batch()
begin 
  DECLARE num INT;
  SET num=1; 
  WHILE num < 1000000 DO
    IF (num%10000=0) THEN
      COMMIT;
    END IF;
    INSERT INTO t1 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500));
    SET num=num+1;
  END WHILE;
  COMMIT;
END //

DELIMITER ;

-- 添加数据
CALL insert_batch();
DROP PROCEDURE insert_batch;

USE test ;

-- 创建表 t1

DROP TABLE IF EXISTS t1 ;

CREATE TABLE t1 (

id BIGINT unsigned NOT NULL AUTO_INCREMENT ,

x VARCHAR ( 500 ) NOT NULL ,

y VARCHAR ( 500 ) NOT NULL ,

PRIMARY KEY ( id )

) ;

-- 创建添加数据存储过程

DROP PROCEDURE insert_batch ;

DELIMITER //

CREATE PROCEDURE insert_batch ( )

begin

DECLARE num INT ;

SET num = 1 ;

WHILE num < 1000000 DO

IF ( num % 10000 = 0 ) THEN

COMMIT ;

END IF ;

INSERT INTO t1 VALUES ( NULL , REPEAT ( 'X' , 500 ) , REPEAT ( 'Y' , 500 ) ) ;

SET num = num + 1 ;

END WHILE ;

COMMIT ;

END //

DELIMITER ;

-- 添加数据

CALL insert_batch ( ) ;

DROP PROCEDURE insert_batch ;

查看数据大小情况（磁盘上的数据大小）

ls -lh /u02/data/test/t1.*
-rw-r----- 1 mysql mysql 8.5K Mar 10 13:54 /u02/data/test/t1.frm
-rw-r----- 1 mysql mysql 1.2G Mar 10 14:20 /u02/data/test/t1.ibd

ls - lh / u02 / data / test / t1 . *

- rw - r -- -- - 1 mysql mysql 8.5K Mar 10 13 : 54 / u02 / data / test / t1 . frm

- rw - r -- -- - 1 mysql mysql 1.2G Mar 10 14 : 20 / u02 / data / test / t1 . ibd

查看真实的大小情况

select count(*) from t1;
+----------+
| count(*) |
+----------+
|   999999 |
+----------+
 
SELECT table_name,
  data_length/1024/1024 AS 'data_length(MB)',
  index_length/1024/1024 AS 'index_length(MB)',
  (data_length + index_length)/1024/1024 AS 'total(MB)'
FROM information_schema.tables
WHERE table_schema='test'
  AND table_name = 't1';
+------------+-----------------+------------------+---------------+
| table_name | data_length(MB) | index_length(MB) | total(MB)     |
+------------+-----------------+------------------+---------------+
| t1         |   1048.00000000 |       0.00000000 | 1048.00000000 |
+------------+-----------------+------------------+---------------+

select count ( * ) from t1 ;

+ -- -- -- -- -- +

| count ( * ) |

+ -- -- -- -- -- +

| 999999 |

+ -- -- -- -- -- +

SELECT table_name ,

data_length / 1024 / 1024 AS 'data_length(MB)' ,

index_length / 1024 / 1024 AS 'index_length(MB)' ,

( data_length + index_length ) / 1024 / 1024 AS 'total(MB)'

FROM information_schema . tables

WHERE table_schema = 'test'

AND table_name = 't1' ;

+ -- -- -- -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- - +

+ -- -- -- -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- - +

| t1 | 1048.00000000 | 0.00000000 | 1048.00000000 |

+ -- -- -- -- -- -- + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- - +

从上面可以看出在磁盘上的数据大小是1.2G，而实际的大小才1048MB(估计值)，实际情况会比上面的数据大很多。

1.6. 表迁移演示

1、使用xtrabackup备份test.t1表数据

mkdir -p /tmp/backup
/usr/local/percona-xtrabackup/bin/innobackupex \
  --defaults-file=/etc/my.cnf                  \
  --user=root                                  \
  --password=root                              \
  --socket=/u02/tmp/mysql.sock                 \
  --include='test.t1'                          \
  /tmp/backup

mkdir - p / tmp / backup

/ usr / local / percona - xtrabackup / bin / innobackupex \

-- defaults - file = / etc / my . cnf \

-- user = root \

-- password = root \

-- socket = / u02 / tmp / mysql . sock \

-- include = 'test.t1' \

/ tmp / backup

2、查看备份集

ll /tmp/backup
drwxr-x--- 3 root root      4096 Mar 10 15:43 2016-03-10_15-43-35
ll /tmp/backup/2016-03-10_15-43-35
total 1048600
-rw-r----- 1 root root        412 Mar 10 15:43 backup-my.cnf
-rw-r----- 1 root root        453 Mar 10 15:43 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Mar 10 15:43 ibdata1
drwxr-x--- 2 root root         32 Mar 10 15:43 test
-rw-r----- 1 root root         26 Mar 10 15:43 xtrabackup_binlog_info
-rw-r----- 1 root root        121 Mar 10 15:43 xtrabackup_checkpoints
-rw-r----- 1 root root        553 Mar 10 15:43 xtrabackup_info
-rw-r----- 1 root root       2560 Mar 10 15:43 xtrabackup_logfile

ll / tmp / backup

drwxr - x -- - 3 root root 4096 Mar 10 15 : 43 2016 - 03 - 10_15 - 43 - 35

ll / tmp / backup / 2016 - 03 - 10_15 - 43 - 35

total 1048600

- rw - r -- -- - 1 root root 412 Mar 10 15 : 43 backup - my . cnf

- rw - r -- -- - 1 root root 453 Mar 10 15 : 43 ib_buffer_pool

- rw - r -- -- - 1 root root 1073741824 Mar 10 15 : 43 ibdata1

drwxr - x -- - 2 root root 32 Mar 10 15 : 43 test

- rw - r -- -- - 1 root root 26 Mar 10 15 : 43 xtrabackup_binlog_info

- rw - r -- -- - 1 root root 121 Mar 10 15 : 43 xtrabackup_checkpoints

- rw - r -- -- - 1 root root 553 Mar 10 15 : 43 xtrabackup_info

- rw - r -- -- - 1 root root 2560 Mar 10 15 : 43 xtrabackup_logfile

3、应用日志并导出元数据

/usr/local/percona-xtrabackup/bin/innobackupex \
  --apply-log \
  --export \
  /tmp/backup/2016-03-10_15-43-35

ll /tmp/backup/2016-03-10_15-43-35/test
-rw-r--r-- 1 root root        433 Mar 10 16:21 t1.cfg
-rw-r----- 1 root root      16384 Mar 10 16:21 t1.exp
-rw-r----- 1 root root       8604 Mar 10 15:43 t1.frm
-rw-r----- 1 root root 1195376640 Mar 10 15:43 t1.ibd

/ usr / local / percona - xtrabackup / bin / innobackupex \

-- apply - log \

-- export \

/ tmp / backup / 2016 - 03 - 10_15 - 43 - 35

ll / tmp / backup / 2016 - 03 - 10_15 - 43 - 35 / test

- rw - r -- r -- 1 root root 433 Mar 10 16 : 21 t1 . cfg

- rw - r -- -- - 1 root root 16384 Mar 10 16 : 21 t1 . exp

- rw - r -- -- - 1 root root 8604 Mar 10 15 : 43 t1 . frm

- rw - r -- -- - 1 root root 1195376640 Mar 10 15 : 43 t1 . ibd

4、test2库中创建和test.t1相同的表结构

CREATE DATABASE test2;
USE test2;
CREATE TABLE t1 LIKE test.t1;

CREATE DATABASE test2 ;

USE test2 ;

CREATE TABLE t1 LIKE test . t1 ;

5、废弃test2.t1表空间，等待新表空间导入

USE test2;
ALTER TABLE t1 DISCARD TABLESPACE;

1 2	USE test2 ; ALTER TABLE t1 DISCARD TABLESPACE ;

6、将test.t1表*.ibd和*.cfg文件拷贝到test2库中

cp /tmp/backup/2016-03-10_15-43-35/test/t1.cfg /u02/data/test2/
cp /tmp/backup/2016-03-10_15-43-35/test/t1.ibd /u02/data/test2/
cp /tmp/backup/2016-03-10_15-43-35/test/t1.exp /u02/data/test2/

ll -h /u02/data/test2/
-rw-r--r-- 1 root  root   433 Mar 10 16:35 t1.cfg
-rw-r----- 1 root  root   16K Mar 10 16:35 t1.exp
-rw-r----- 1 mysql mysql 8.5K Mar 10 14:33 t1.frm
-rw-r----- 1 root  root  1.2G Mar 10 16:35 t1.ibd

cp / tmp / backup / 2016 - 03 - 10_15 - 43 - 35 / test / t1 . cfg / u02 / data / test2 /

cp / tmp / backup / 2016 - 03 - 10_15 - 43 - 35 / test / t1 . ibd / u02 / data / test2 /

cp / tmp / backup / 2016 - 03 - 10_15 - 43 - 35 / test / t1 . exp / u02 / data / test2 /

ll - h / u02 / data / test2 /

- rw - r -- r -- 1 root root 433 Mar 10 16 : 35 t1 . cfg

- rw - r -- -- - 1 root root 16K Mar 10 16 : 35 t1 . exp

- rw - r -- -- - 1 mysql mysql 8.5K Mar 10 14 : 33 t1 . frm

- rw - r -- -- - 1 root root 1.2G Mar 10 16 : 35 t1 . ibd

7、test2导入t1数据

chown -R mysql:mysql /u02/data/test2
USE test2;
ALTER TABLE t1 IMPORT TABLESPACE;

chown - R mysql : mysql / u02 / data / test2

USE test2 ;

ALTER TABLE t1 IMPORT TABLESPACE ;

8、查看test2.t1数据

USE test2;
SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|   999999 |
+----------+

USE test2 ;

SELECT COUNT ( * ) FROM t1 ;

+ -- -- -- -- -- +

| COUNT ( * ) |

+ -- -- -- -- -- +

| 999999 |

+ -- -- -- -- -- +

1.7. 总结

xtrabackup这种表迁移算是比较复杂的了，但是他对数据库的影响是比较小的。虽然说影响小，但是还是会有影响的。所以迁移还是避免高峰时期比较妥当。

昵称：HH
QQ：275258836
ttlsa群交流沟通(QQ群②：6690706 QQ群③：168085569 QQ群④：415230207（新）微信公众号：ttlsacom)

感觉本文内容不错，读后有收获？

逛逛衣服店，鼓励作者写出更好文章。

SQLAlchemy 简明使用指南 Flask的Jinja2模板引擎 — 自定义扩展(8th)

表迁移工具的选型-xtrabackup的使用

1.1. 场景

1.2. 为什么不使用ibd文件拷贝方法

1.3. 扩展

1.4. 先决条件

1.5. 制造大表

1.6. 表迁移演示

1.7. 总结

相关内容

猜你喜欢