Mysql数据库备份

一、Mysqldump

1）语法

mysqldump -u [username] -p [password] [db_name] [table1 table2]  ... > mysqldump.sql

2）备份多个数据库

mysqldump -u root -p123456 --databases db_name1 db_name2 > xxx.sql

3）备份所有数据库

mysqldump -u root -p123456 --all-databases > xxx.sql # 此处--all-databases 可替换为 -A

• 测试

  [root@ambari-2 ~]# time mysqldump -u root -p123456  -A > /tmp/mysqldump.sql
  mysqldump: [Warning] Using a password on the command line interface can be insecure.
  
  real    0m11.980s
  user    0m2.460s
  sys     0m0.835s
  

  耗时：12 s

4）数据还原

• 方式一

  mysql -u root -p 123456 [dn_name] < xxx.sql  # 如果不指定db_name的话，表示还原所有数据库
  

• 方式二

  mysql -uroot -p
  source  xxx.sql
  

二、Mydumper

1）介绍

是一个针对Mysql和Drizzle的高性能多线程备份恢复工具

特点

• 多线程备份

• 因为是多线程逻辑备份,备份后会生成多个备份文件

• 备份时对MyISAM表施加FTWRL(FLUSH TABLES WITH READ LOCK),会阻塞DML语句

• 保证备份数据的一致性

• 支持文件压缩

• 支持导出binlog

• 支持多线程恢复

• 支持以守护进程模式工作,定时快照和连续二进制日志

• 支持将备份文件切块

注意

• mydumper采用多线程导出，所以无法保证导出顺序和使用mysqldump一致，可能会给某些依赖时间的特性（routine，event等）带来数据不一致，建议把mysql库和其他数据库分开导出导入。
• mydumper提供的库提取和库合并功能依赖于分割符，所以要求用户数据库名不包含点号（.） ,表名不包含减号（-）

2） 安装

• yum安装

  ## RedHat / Centos
   
  release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
  yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el7.x86_64.rpm
  yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el8.x86_64.rpm
  

• 源码安装

  推荐mydumper-0.9.1版本比较稳定

  # Dependencies for building mydumper
   
  yum install -y cmake gcc gcc-c++ git make
   
  yum install -y glib2-devel openssl-devel pcre-devel zlib-devel libzstd-devel
   
  yum install -y mysql-devel
   
  yum install -y Percona-Server-devel-57
   
  yum install -y mariadb-devel
  
  wget https://launchpadlibrarian.net/225370879/mydumper-0.9.1.tar.gz
   
  tar -xzvf mydumper-0.9.1.tar.gz
   
  cd mydumper-0.9.1 && cmake . && make
   
  chmod a+x mydumper myloader
   
  cp mydumper myloader /usr/local/bin/
  

3）测试

导出指定库到目录

mydumper -h 192.168.2.24 -uroot p123456 -B sun -o /tmp  --verbose=3  

• 不加 -B 参数则导出所有数据库
• --verbose=3 表示控制台打印的日志级别，0 = silent, 1 = errors, 2 = warnings, 3 = info

测试耗时: 3.8 s

[root@ambari-2 ~]# time  mydumper  -u root  -p 123456  -o /tmp/all_databases --verbose=0

real    0m3.823s
user    0m2.316s
sys     0m3.895s

本次测试的数据量大约300 MB，相比mysqldump，执行速度快了3倍，接下来我们看下备份后的数据

[root@ambari-2 tmp]# tree all_databases/
all_databases/
├── metadata   # 保存了元数据
├── sun-schema-create.sql #数据库建表语句
├── sun-schema-triggers.sql  # 触发器
├── sun.student.00000.sql #表数据
└── sun.student-schema.sql # 表结构文件

0 directories, 5 files

4）详细用法

mydumper: 导出数据库

注意：命令之间必须加空格，如-uroot -p123456 会报错

# 备份单库
mydumper -u root -p 123456 --database db1 --outputdir db1 --verbose=3

# 备份多库
mydumper -u root -p 123456 --regex '^(db1\.|db2\.)' --outputdir db12 --verbose=3

# 备份全部数据库
mydumper -u root -p 123456 --outputdir fxfull --verbose=3

# 备份单表
mydumper -u root -p 123456 --database db1 --tables-list tbs1,tbs2,tbs3 --build-empty-files --outputdir tbs123 --verbose=3

# 只备份结构
mydumper -u root -p 123456 --database db1 --no-data --outputdir db1 --verbose=3

# 开启压缩
mydumper -u root -p 123456 --database db1 --compress --outputdir db1 --verbose=3

# 一致性备份
--less-locking 少锁等待时间，尽量减少表锁定，需要权衡备份速度和数据库操作
mydumper -u root -p 123456 --database db1 --less-locking --outputdir tbs --verbose=3

myloader：导入数据库

# myloader -u root -p 123456 -B sun -d /tmp/all_databases/ -v 3

如果表已经存在，则会报错，所以myloader没有drop table if exists,在导入之前保证没有表

4）总结

1、主线程 FLUSH TABLES WITH READ LOCK, 施加全局只读锁，保证数据的一致性

2、读取当前时间点的二进制日志文件名和日志写入的位置并记录在metadata文件中，以供即使点恢复使用

3、N个（线程数可以指定，默认是4）dump线程把事务隔离级别改为可重复读 并开启读一致的事物

4、dump non-InnoDB tables, 首先导出非事物引擎的表

5、主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁

6、dump InnoDB tables, 基于事物导出InnoDB表

7、事物结束