物理备份与逻辑备份

物理备份：

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

逻辑备份：

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

mysqldump 实现逻辑备份

备份单个数据

mysqldump命令执行时，可以将数据库备份成文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

查出需要备份的表的结构，在文本文件中生成一个CREATE语句
将表中的所有记录转换成一条INSERT语句

1	mysqldump –u 用户名称 –h 主机名称 –p密码待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

备份全部数据库

若想用mysqldump备份整个实例，可以使用--all-databases或-A参数：

1 2	mysqldump -uroot -pxxxxxx --all-databases > all_database.sql mysqldump -uroot -pxxxxxx -A > all_database.sql

备份部分数据库

使用 --databases或-B，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。

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

备份部分表

在表变更前做个备份

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

备份单表的部分数据

只需要部分数据，此时就可以使用--where选项，where后面附带需要满足的条件。

备份student表中id小于10的数据：

1	mysqldump -uroot -p test student --where="id < 10 " > student_part_id10_low_bak.sql

排除某些表的备份

如果想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，选项--ignore-table可以完成这个功能。

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

只备份结构或只备份数据

只备份结构的话可以使用--no-data简写为-d；只备份数据可以使用--no-create-info简写为-t。

# 只备份结构
mysqldump -uroot -p test --no-data > test_no_data_bak.sql

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

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

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用--routines或-R选项来备份存储过程及函数，使用--events或-E参数来备份事件

-- 查看当前库有哪些存储过程或者函数
SELECT SPECIFIC_NAME,ROUTINE_TYPE ,ROUTINE_SCHEMA 
FROM information_schema.Routines 
WHERE ROUTINE_SCHEMA="test";

1 2	# 备份test库的数据，函数以及存储过程 mysqldump -uroot -p -R -E --databases test > fun_test_bak.sql

mysqldump常用选项

帮助命令：mysqldump --help

mysql 命令恢复数据

1	mysql –u root –p [dbname] < backup.sql

单库备份中恢复单库

使用root用户，将备份文件中的备份导入数据库中。

如果备份文件中包含创建数据库的语句，则恢复的时候不需要指定数据库名称

1	mysql -uroot -p < test.sql

否则需要指定数据库名称

1	mysql -uroot -p test< test.sql

全量备份恢复

1	mysql –u root –p < all.sql

物理备份

直接复制MySQL中的数据库文件

在Windows平台下，MySQL 8.0存放数据库的目录通常默认为C:\ProgramData\MySQL\MySQL Server 8.0\Data或者其他用户自定义目录；
在Linux平台下，数据库目录位置通常为/var/lib/mysql/

表的导出与导入

表的导出

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

选择数据库test，并查询account表

使用命令行进行导出时需要指定目录

1 2	-- 查询secure_file_priv值： SHOW GLOBAL VARIABLES LIKE '%secure%';

secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录

1	SELECT * FROM account INTO OUTFILE "/var/lib/mysql-files/account.txt";

查看 /var/lib/mysql-files/account.txt文件

使用mysqldump命令导出文本文件

1	mysqldump -uroot -p -T "/var/lib/mysql-files/" test account

使用mysql命令导出文本文件

1	mysql -uroot -p --execute="SELECT * FROM account;" test> "/var/lib/mysqlfiles/account.txt"

选项	说明
`--veritcal`	将该条件记录分为多行显示
`--xml`	将该条件记录导出到xml文件
`--html`	将表该条件记录导出到html文件

表的导入

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

1	LOAD DATA INFILE '/var/lib/mysql-files/account.txt' INTO TABLE test.account;

使用mysqlimport方式导入文本文件

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

数据库迁移

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

迁移方案

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

物理迁移 ：

物理迁移适用于大数据量下的整体迁移，其优点是快速，但需要停机迁移并且要求 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种

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

逻辑迁移 ：

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

迁移注意点

相同版本的数据库之间迁移：在主版本号相同的MySQL数据库间进行数据库移动

方式1：可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库
方式2：使用mysqldump命令导出数据，然后在目标数据库服务器中使用 MySQL命令导入
1
mysqldump –h host1 –uroot –p –-all-databases| mysql –h host2 –uroot –p
- |符号表示管道，作用是将mysqldump备份的文件给mysql命令
- --all-databases表示要迁移所有的数据库
- 通过这种方式可以直接实现迁移