MySql

MySQL 11_MySQL数据库备份与恢复

一、MySQL数据库备份

1.1 物理备份与逻辑备份

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份。

逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

1.2 mysqldump实现逻辑备份

1、备份一个数据库

1
2
3
4
5
6

# 备份命令
mysqldump -u 用户名称 -h 主机名称 -p{密码} 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

# 示例：
mysqldump -uroot -p atguigu>atguigu.sql #备份文件存储在当前目录下
mysqldump -uroot -p atguigudb1 > /var/lib/mysql/atguigu.sql

2、备份部分数据库

1
2
3
4
5
6

# 备份命令
mysqldump -u user -h host -p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

# 示例：
mysqldump -uroot -p --databases atguigu atguigu12 >two_database.sql
mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql

3、备份全部数据库

1
2
3

# 示例：
mysqldump -uroot -pxxxxxx --all-databases > all_database.sql 
mysqldump -uroot -pxxxxxx -A > all_database.sql

4、备份部分表

1
2
3
4
5
6
7

# 备份命令
mysqldump -u user -h host -p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql

# 示例：
mysqldump -uroot -p atguigu book > book.sql
#备份多张表 
mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

5、备份单表的部分数据

1
2

# 示例：
mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

6、排除某些表的备份

1

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

7、只备份结构或只备份数据

1
2
3
4
5

# 只备份结构
mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql

# 只备份数据
mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql

8、备份中包含存储过程、函数、事件

1

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

二、MySQL命令恢复数据

2.1 恢复数据

1、直接执行备份文件（SQL）

1

mysql -u root -p [dbname] < backup.sql

或者

1
2
3

use atguigu; 
mysql> source atguigu.sql; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

2、单库备份中恢复单库

1
2
3
4
5

# 备份文件中包含了创建数据库的语句
mysql -uroot -p < atguigu.sql

# 备份文件中不包含了创建数据库的语句
mysql -uroot -p atguigu4< atguigu.sql

3、全量备份恢复

1

mysql -u root -p < all.sql

4、从全量备份中恢复单库

1
2

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql 
# 分离完成后我们再导入atguigu.sql即可恢复单个库

5、从单库备份中恢复单表

1
2
3

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql 
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql 
# 用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复 

1
2
3
4
5
6

use atguigu; 
mysql> source class_structure.sql; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> source class_data.sql; 
Query OK, 1 row affected (0.01 sec)

三、表的导出与导入

3.1 表的导出

1、使用SELECT…INTO OUTFILE导出文本文件

1
2

SHOW GLOBAL VARIABLES LIKE '%secure%';
SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

2、使用mysqldump命令导出文本文件

1
2
3

mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
# 或
mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

3、使用mysql命令导出文本文件

1

mysql -uroot -p --execute="SELECT * FROM account;" atguigu > "/var/lib/mysql-files/account.txt"

3.2 表的导入

1、使用LOAD DATA INFILE方式导入文本文件

1
2
3

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;
# 或
LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE atguigu.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

2、使用mysqlimport方式导入文本文件

1

mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'

四、数据库迁移

4.1 数据库迁移简介

数据迁移(data migration) 是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性和退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复 和 数据中心迁移。

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL-数据迁移方案大致可以分为 物理迁移 和 逻辑迁移 两类。通常以尽可能自动化的方式执行，从而将人力资源从繁琐的任务中解放出来。

4.2 数据库迁移方案

1、物理迁移

物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求MySQL版本及配置必须和原服务器相同，也可能引起未知问题。

物理迁移包括拷贝数据文件和使用XtraBackup备份工具两种。

不同服务器之间可以采用物理迁移，可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。

2、逻辑迁移

逻辑迁移适用范围更广，无论是部分迁移还是全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过mysqldump 等备份工具。

4.3 数据库迁移注意点

1、相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动。

方式1：因为迁移前后MySQL数据库的主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。

方式2：最常见和最安全的方式是使用 mysqldump 命令导出数据，然后在目标数据库服务器中使用MySQL命令导入。

2、不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷，因此需要把数据库升级到8.0版本。

旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用 latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对默认字符集进行修改，不然可能无法正常显示数据。

高版本的MySQL数据库通常都会兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。

3、不同数据库之间迁移注意点

不同数据库之间迁移是指从其它类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其它类型的数据库。这种迁移没有普适的解决方法。

迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的关键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE；SQL Server数据库中有ntext、lmage等数据类型，MySQL数据库没有这些数据类型；MySQL支持的 ENUM 和 SET 类型，这些SQL Server数据库不支持。

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的SQL语句有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容。

不同类型数据库之间的差异造成了互相迁移的困难，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并不是完全不可能。例如，可以使用MyODBC实现MySQL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit也可以在不同数据之间进行数据迁移。MySQL迁移到oracle时，需要使用mysqldump命令导出sql文件，然后，手动更改sql文件中的CREATE语句。

五、删库预防

5.1 删库分类

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

为了找到解决误删数据的更高效的方法，需要先对和MySQL相关的误删数据，做下分类:

  • 使用 delete 语句误删数据行；
  • 使用 drop table 或者 truncate table 语句误删数据表；
  • 使用 drop database 语句误删数据库；
  • 使用 rm 命令误删整个MySQL实例或数据文件；

5.2 delete：误删行

误删 数据恢复 使用 Flashback工具 恢复数据。

  • 原理：修改binlog内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。
  • 使用前提: binlog_format=row 和 binlog_row_image=FULL。

预防误删措施 代码上线前，必须SQL审查、审计。 建议可以打开安全模式，把sql_safe_updates参数设置为on，强制要求加where条件且where后需要是索引字段，否则必须使用limit。否则就会报错。

5.3 truncate/drop：误删库/表

误删回复方案： 这种情况下恢复数据，需要使用全量备份与增量日志结合的方式。

该方案的前提：有定期的全量备份，并且实时备份binlog。

举例：有人误删了一个库，时间为下午3点。步骤如下:

  1. 取最近一次全量备份。假设设置数据库库是一天一备，最近备份数据是当天凌晨2点;
  2. 用备份恢复出一个临时库;(注意:这里选择临时库，而不是直接操作主库)
  3. 取出凌晨2点之后的binlog日志;
  4. 剔除误删除数据的语句外，其它语句全部应用到临时库。(前面讲过binlog的恢复)
  5. 最后恢复到主库

预防使用truncate/drop误删库/表措施 1、权限分离

  • 限制帐户权限，核心的数据库，一般都不能随便分配写权限，想要获取写权限需要审批。比如只给业务开发人员DML权限，不给truncate/drop权限。即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
  • 不同的账号，不同的数据之间要进行权限分离，避免一个账号可以删除所有库。

2、制定操作规范

比如在删除数据表之前，必须先对表做改名操作（比如加_to_be_deleted）。然后，观察一段时间，确保对业务无影响以后再删除这张表。

3、设置延迟复制备库

简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。出现误删除操作1小时内，到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。这里通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有N秒的延迟。比如把N设置为3600，即代表1个小时。