MySQL 备份

备份用户需要的完整权限

select    -查询
reload    # 允许使用该 FLUSH 语句，告诉服务器将授权表重新加载到内存中

lock tables   -锁表
show view    -显示试图
event
trigger

process  # 允许显示有关服务器内执行的线程的信息（即有关会话正在执行的语句的信息）

replication  client  # 读取二进制日志的位置信息，

MySQL逻辑备份 mysqldump

逻辑备份特点

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
效率相对较低

在日常工作中，我们会使用 mysqldump 命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。

用法

mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql

/*查看帮助*/
mysqldump --help

常见参数

• --single-transaction 备份前启用一个事务，保证数据一致性。
  仅对 InnoDB 存储引擎有效。还有需要保证没有其他的连接正在使用以下语句： ALTER TABLE，CREATE TABLE，DROP TABLE， RENAME TABLE，TRUNCATE TABLE

• -l, --lock tables 对于不支持事务的存储引擎的表备份使用此选项，比如 MySIAM, 可以保证备份期间的数据一致性。会依次对正在备份的每个数据库中的所有表进行锁表操作，此时只可以读。和 --single-transaction 互斥。

• -x, --lock-all-tables锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。

有几个选项控制mysqldump如何 处理存储的程序（存储过程和函数，触发器和事件）：

• --events：备份事件计划程序事件

• --routines：备份存储过程和函数

• --triggers：备份表的触发器

--triggers默认情况下启用 该选项，以便在转储表时，它们伴随着它们具有的任何触发器。

默认情况下要禁用这些选项。需要明确设置这些选项：
--skip-events， --skip-routines, --skip-triggers。

备份所有库

// 先配置用户名和密码
shell> vi ~/.mysql_user
[mysqldump]
user=root
password=123

shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

示例

-先进入数据库中给某一用户授予权限
mysql> grant select,show view,event,trigger,lock tables,process,replication client, reload on *.* to backip@'%' identified by 'QFedu123!';

- 退出mysql 创建一个配置用户名和密码
[root@localhost ~]$ vi ~/.mysql_user
[mysqldump]
user=backup
password=123

-输入命令
[root@localhost ~]$ mysqldump --defaults-file=/root/mysqluser.db -h 127.0.0.1 --all-databases >$(date +%FT%H:_%M_%S)-dump-all.sql
[root@localhost ~]$ ls
2019-08-14T10:_28_56-dump-all.sql

报1045错误

mysqldump: Got error: 1045: Access denied for user 'rourou'@'%' (using password: YES) when using LOCK TABLES

在输入命令中加入一个参数
--single-transaction

--single-transaction
通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作；其他引擎不能保证导出是一致的。当导出开启了--single-transaction选项时，要确保导出文件有效（正确的表数据和二进制日志位置），就要保证没有其他连接会执行如下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，这会导致一致性快照失效。这个选项开启后会自动关闭--lock-tables。

3b417eaf5eb53496228d774d458c74f.png

备份指定库

如果嫌每次输入的麻烦可以进入.bashrc中添加
[root@localhost ~]- vim .bashrc
alias mysqldump='mysqldump --defaults-file=/root/mysqluser.db'
[root@localhost ~]- source .bashrc
[root@localhost ~]-  which mysqldump
alias mysqldump='mysqldump --defaults-file=/root/mysqluser.db'
    /usr/bin/mysqldump

[root@localhost ~]- mysqldump --databases servers > $(date +%FT%H_%M_%S).dump.servers.sql  -指定库名
[root@localhost ~]- ls
2019-08-14T11_44_40.dump.servers.sql 

备份指定库中的指定表

[root@localhost ~]- mysqldump -h 127.0.0.1 servers node_tree >$(date +%FT%H_%M_%S).dump.servers_node_tree.sql
- 指定了servers中的node_tree表 可以指定多个表 空格隔开即可 但不可同时指定多个库的表

恢复数据

mysql -uroot -p '密码' <之前备份的数据
如果

MySQL物理备份：Innobackupex 和 xtrabackup（热备）

Percona XtraBackup是一款基于MySQL的热备份的开源实用程序，它可以备份5.1到5.7版本上InnoDB,XtraDB,MyISAM存储引擎的表， Xtrabackup有两个主要的工具：xtrabackup、innobackupex 。

使用 YUM 方式安装

1、确保安装epel源

yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

2、安装libev
为了成功安装Percona XtraBackup libev包需要先安装。

yum  install -y libev

3、安装Percona存储库

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

4、安装软件

shell> yum install percona-xtrabackup-24

根据上面的xtrabackup物理恢复

shell> xtrabackup --backup --user=root --password='123'  --target-dir=/backups/full

- 备份完成后，可以看到备份时的LSN号，当下次进行增量备份时，xtrabackup就只备份大于此号的page即可。

shell> xtrabackup --prepare --target-dir=/backups/full   
- 准备数据

shell> systemctl stop mysqld
- 停止mysql服务

shell> rm -rf /var/lib/mysql/\*
把这个目录下的所有文件都删了  一定要目录正确

shell> xtrabackup --copy-back --datadir=/var/lib/mysql --target-dir=/backups/full
-将备份好的数据复制到MySQL可以读到的目录下

shell>chown mysql.mysql  -R /var/lib/mysql
- 修改属主和属组权限给mysql 原先属主和属组是root用户

shell> systemctl start mysqld.service
- 重新启动

innobackuper 命令实现

shell> innobackupex --defaults-file=/etc/my.cnf --host=192.168.1.146 --user=root --password=123123 /backup

shell> nnobackupex --apply-log --use-memory=4G /backups/2018-08-17_15-53-11

shell> systemctl stop mysqld.service

shell> rm -rf /var/lib/mysql/`*`                                   

shell> innobackupex --datadir=/var/lib/mysql --copy-back 2018-08-17_15-53-11

shell> chown  mysql.mysql -R /var/lib/mysql

shell> systemctl start mysqld.service

全量备份思路总结

1、执行备份命令

• 指定 数据库的用户名和密码
• 指定 备份目录，注意只可以自动创建最后一级的目录

2、准备备份的数据

• 就是指： --prepare 参数, 保证数据的统一且完整性

3、停服务，并且把 mysql 的数据目录下的所有文件和文件夹清除。

• /var/lib/mysql/ 此目录必须是空的

4 恢复数据

5、本质上就是拷贝备份的文件到指定的 mysql 数据目录下

6、修改 mysql 数据目录的属主和属组为 MySQL 服务器进程启动的用户，默认是 mysql
7、启动服务

历史命令之物理备份

image.png