第50章　MySQL主流备份工具之mydumper详解 - xiaoboluo768/qianjinliangfang GitHub Wiki

50.4.1　安装mydumper

[root@localhost yum.repos.d]# cd /etc/yum.repos.d/

[root@localhost yum.repos.d]# cat CentOS6-Base-163.repo 
......
[base]
name=CentOS-6 - Base - 163.com
baseurl=http://mirrors.163.com/centos/6/os/$basearch/
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
gpgcheck=1
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-6

#released updates 
[updates]
name=CentOS-6 - Updates - 163.com
baseurl=http://mirrors.163.com/centos/6/updates/$basearch/
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
gpgcheck=1
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-6

#additional packages that may be useful
[extras]
name=CentOS-6 - Extras - 163.com
baseurl=http://mirrors.163.com/centos/6/extras/$basearch/
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras
gpgcheck=1
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-6

#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-6 - Plus - 163.com
baseurl=http://mirrors.163.com/centos/6/centosplus/$basearch/
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=centosplus
gpgcheck=1
enabled=0
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-6

#contrib - packages by Centos Users
[contrib]
name=CentOS-6 - Contrib - 163.com
baseurl=http://mirrors.163.com/centos/6/contrib/$basearch/
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=contrib
gpgcheck=1
enabled=0
gpgkey=http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-6

# Centos
[root@localhost yum.repos.d]# yum install glib2-devel mysql-devel zlib-devel pcre-devel\ cmake -y
[root@localhost yum.repos.d]# rpm -e mysql-5.1.73-8.el6_8.x86_64 --nodeps

# Ubuntu
[root@localhost~]# apt-get cmake make install libglib2.0-dev libmysqlclient15-dev\ zlib1g-dev libpcre3-dev g++

# susu
[root@localhost~]# zypper install glib2-devel libmysqlclient-devel pcre-devel\ zlib-devel

# mac
[root@localhost~]# port install glib2 mysql5 pcre

[root@localhost~]# wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-\ 0.9.1.tar.gz

[root@localhost~]# ll
......
 [root@localhost~]# unzip mydumper-master.zip 
......

[root@localhost~]# cd mydumper-master
[root@localhost mydumper-master]# cmake .
......

[root@localhost mydumper-master]# make
......
[100%] Built target myloader

[root@localhost mydumper-master]# echo $?
0
[root@localhost mydumper-master]# make install
......

[root@localhost mydumper-master]# echo $?
0

[root@localhost mydumper-master]# ll my*
......


[root@localhost mydumper-master]# cp -ar mydumper myloader /usr/bin/

[root@localhost mydumper-master]# ln -s /home/mysql/ program/lib/libmysqlclient.so.20\ /usr/lib64/

50.4.2　备份与恢复

• 1．完全备份与恢复

[root@localhost mydumper-master]# mkdir /data/backup/ mydumper -p
[root@localhost mydumper-master]# cd /data/backup/mydumper/
[root@localhost mydumper]# rm -rf *
# 执行备份
[root@localhost mydumper]# mydumper --defaults-file=/home /mysql/conf/my1.cnf -G -E –R\ --skip-tz-utc --complete-insert -h 10.10.30.241 -u admin -p password -t 16 –o\ /data/backup/mydumper
# 当备份完成后，查看备份目录下的备份文件
[root@localhost mydumper]# ll /data/backup/mydumper/sbtest*
......

# 建库语句
[root@localhost mydumper]# cat sbtest-schema-create.sql 
CREATE DATABASE `sbtest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */;

# 建表语句
[root@localhost mydumper]# cat sbtest.sbtest1-schema.sql 
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT '',
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=18554580 DEFAULT CHARSET=utf8 COLLATE=utf8_bin MAX_ROWS=1000000;

# DML的INSERT语句
[root@localhost mydumper]# head -10 sbtest.sbtest1.sql 
/*!40101 SET NAMES binary*/;
/*!40014 SET FOREIGN_KEY_CHECKS=0*/;
INSERT INTO `sbtest1` (`id`,`k`,`c`,`pad`) VALUES
(2,2481885,"08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977","63188288836-92351140030-06390587585-66802097351-49282961843"),
(4,2435986,"95969429576-20587925969-20202408199-67602281819-18293380360-38184587501-73192830026-41693404212-56705243222-89212376805","09512147864-77936258834-40901700703-13541171421-15205431759"),
......

# binlog pos和GTID 集合
[root@localhost mydumper]# cat metadata 
Started dump at: 2017-05-31 15:45:45
SHOW MASTER STATUS:
Log: mysql-bin.000012
Pos: 3520150
GTID:2016f827-2d98-11e7-bb1e-00163e407cfb:1-1878711,
402872e0-33bd-11e7-8e8d-00163e4fde29:1-180732,
4e5fb89f-3fa9-11e7-9e0c-00163e4fde29:1-10,
5fe70ca9-3fab-11e7-bc48-00163e4fde29:1-61,
799ef59c-4126-11e7-83ce-00163e407cfb:1-270345,
8440023c-3f9f-11e7-8f52-00163e4fde29:1-10

SHOW SLAVE STATUS:
Host: 10.10.30.250
Log: mysql-bin.000002
Pos: 234
GTID:2016f827-2d98-11e7-bb1e-00163e407cfb:1-1878711,
402872e0-33bd-11e7-8e8d-00163e4fde29:1-180732,
4e5fb89f-3fa9-11e7-9e0c-00163e4fde29:1-10,
5fe70ca9-3fab-11e7-bc48-00163e4fde29:1-61,
799ef59c-4126-11e7-83ce-00163e407cfb:1-270345,
8440023c-3f9f-11e7-8f52-00163e4fde29:1-10

Finished dump at: 2017-05-31 15:49:18

# 如果已经有复制信息，则先停止复制并清理复制信息，然后执行导入（导入数据时可能会报出错误，如果是来自sys schema的错误，则可以忽略）
[root@localhost mydumper]# myloader --defaults-file=/home/mysql/conf/my1.cnf -o –h\ 10.10.30.217 -u admin -p password -t 16 -d /data/backup/mydumper

mysql> reset master;
Query OK, 0 rows affected (0.04 sec)

mysql> set global gtid_purged='2016f827-2d98-11e7-bb1e-00163e407cfb:1-1878711,\ 402872e0-33bd-11e7-8e8d-00163e4fde29:1-180732,4e5fb89f-3fa9-11e7-9e0c-00163e4fde29:1-10,\ 5fe70ca9-3fab-11e7-bc48-00163e4fde29:1-61,799ef59c-4126-11e7-83ce-00163e407cfb:1-286350,\ 8440023c-3f9f-11e7-8f52-00163e4fde29:1-10' ;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='10.10.30.241', master_user='qfsys',master_\ password='password',master_log_file='mysql-bin.000012',master_log_pos=9494175;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
......

• 2．单表备份与恢复

[root@localhost mydumper]# mkdir /data/backup/mydumper/ -p
[root@localhost mydumper]# cd /data/backup/mydumper/
[root@localhost mydumper]# rm -f *
[root@localhost mydumper]# 
[root@localhost mydumper]# mydumper --defaults-file=/home/mysql/conf/my1.cnf -G –E\ -R --skip-tz-utc --complete-insert -h 10.10.30.241 -u admin -p password -t 16 -B sbtest –T\ sbtest1,sbtest2 -o /data/backup/mydumper

[root@localhost mydumper]# ll
......

mysql> create database sbtest_test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
......
10 rows in set (0.00 sec)

[root@localhost mydumper]# myloader --defaults-file= /home/mysql/conf/my1.cnf -o -h\ 10.10.30.241 -u admin -p password -t 16  -B sbtest_test -s sbtest -d /data/backup/mydumper

mysql> use sbtest_test;
Database changed
mysql> show tables;
......
2 rows in set (0.00 sec)

mysql> select * from sbtest1 limit 1;
......
1 row in set (0.00 sec)

• 3．快照备份与恢复

[root@localhost mydumper]# mydumper --defaults-file=/home/mysql/conf/my1.cnf -G –E\ -R -D -L /var/log/mydumper.log -I 60 --skip-tz-utc --complete-insert -u admin -p password\ -h 10.10.30.241 -C -o /data/backup/mydumper
[root@localhost mydumper]# ps aux |grep mydumper
......

[root@localhost mydumper]# ll 
total 20
drwx------ 2 root root 16384 Jun  1 00:42 0
drwx------ 2 root root     6 Jun  1 00:40 1
[root@localhost mydumper]# du -sh 0
3.4G    0

---

上一篇：第49章　MySQL主流备份工具之XtraBackup详解 | 下一篇：第51章　MySQL主流闪回工具详解