数据库运维：mysql 数据库迁移方法
数据库运维需要掌握的技能之一数据库的迁移，而MySQL数据库是我们常用的数据库之一，掌握mysql 数据库的数据迁移，也是必备的技能选项。数据库的迁移，我们必须要思考如下几个点：
- 需要迁移数据库的数据量大小
- 需要迁移的数据库对象是什么，
- 考虑使用何种迁移方法！
这三个思考要点，适合于所用的数据库迁移，请大家留意。
思考mysql 数据库迁移，第一步就需要基于数据库或表，统计每一个数据库或表占用存储空间，根据所占用的空间大小以及实际迁移时遇到的应用场景，决定使用什么迁移方法。比如数据库大小在10 GB 以下，可以考虑使用mysqldump 作为逻辑导出source导入，实现迁移。数据库大小在10 GB 以上，就可以考虑mydumper ，myloader工具导出导入。如果导出数据库大小在100 GB 以上就可以考虑使用物理迁移等方法！以上三种迁移思路针对的一般方法，如果一个数据库的表数量比较多（100个以上），单个表占用空间为1G左右，也适合用mydumper导出，myloader导入。所以本系列mysql 数据库迁移基于数据量大小，结合不同的应用场景，实现mysql 数据库快速迁移。用户掌握一些特定的应用场景与使用工具，结合自己实际应用场景，做出灵活选择方法，实现快速数据迁移的方法。
Mysql 数据库需要迁移的对象包括表，索引，视图，触发器，事件，存储过程与函数
迁移方法主要有两种，逻辑备份迁移与物理备份迁移，mysqldump/mydumper/myloader 都是逻辑备份与恢复工具。
使用mysqldump 导出与source 导入
Mysqldump 导出工具，mysql数据库安装后都有该工具，所以使用mysqldump 工具导出也时最方便的工具。使用mysqldump 的优点就是方便快捷的使用，不用安装；缺点是导入时受目标服务器的内存与CPU 资源限制，导入会花很长的时间导入。
Mysqldump 使用-B 参数，默认导出数据库的表，索引，视图，触发器等对象，需要导出存储过程与函数，需要使用-R 参数，不同参数的组合，可以帮助我们操作一些失败的对象。
统计mysql 数据库需要迁移的数据量大小
统计mysql数据库的数据库大小主要考虑的数据与索引占用的空间，需要使用的系统表为information_schema.tables
- 计算数据库总空间大小
csharp
体验AI代码助手
代码解读
复制代码
select table_schema as ‘数据库’, concat(round((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024,2),'G') as total_size from information_schema.tables group by table_schema;
- 计算数据库的数据大小
csharp
体验AI代码助手
代码解读
复制代码
SELECT table_schema as ‘数据库’, concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data_size FROM information_schema.TABLES group by TABLE_SCHEMA;
- 计算数据库索引长度
csharp
- 体验AI代码助手
- 代码解读
- 复制代码
SELECT table_schema as ‘数据库’, concat(round(sum(INDEX_LENGTH/1024/1024/1024),2),'GB') as index_size FROM information_schema.TABLES group by TABLE_SCHEMA;
基于某一个库计算每一个表的空间大小
- 计算数据库的表占用空间大小，并按表的大小降序排列
sql
体验AI代码助手
代码解读
复制代码
#select table_schema as '数据库',table_name, concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2),'MB') as total_size from information_schema.tables where table_schema='XXXX' order by round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2) desc;
- 计算数据库的表数据占用空间大小, ，并按表的大小降序排列
csharp
体验AI代码助手
代码解读
复制代码
SELECT table_schema as '数据库', table_name, concat(round(DATA_LENGTH/1024/1024,2),'MB') as data_size FROM information_schema.TABLES where table_schema='XXXX' order by round(DATA_LENGTH/1024/1024,2) desc;
- 计算数据库的表索引占用空间大小
csharp
- 体验AI代码助手
- 代码解读
- 复制代码
SELECT table_schema as '数据库', table_name, concat(round(INDEX_LENGTH/1024/1024,2),'MB') as index_size FROM information_schema.TABLES where table_schema='XXXX' order by round(INDEX_LENGTH/1024/1024,2) desc;
查看mysql 需要迁移的存储过程与函数
Mysql 的非表对象比较少，主要包括函数，存储过程，视图
Mysql8 查询的系统表为information_schema.routines
sql
体验AI代码助手
代码解读
复制代码
select * from information_schema.routines where routine_schema ='XXXX'; -- XXXX代表数据库名称
mysql5.7 查询
sql
体验AI代码助手
代码解读
复制代码
select `name` from mysql.proc where db = 'XXXX' -- -- XXXX代表数据库名称
使用mysqldump 导出数据库
vbnet
体验AI代码助手
代码解读
复制代码
#mysqldump -uroot -p11111111 -B dt_news -R --single-transaction >dt_news.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-B 参数，会在备份的sql 语句中产生CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dt_news` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
如果目标库中的数据库与源库不一致，需要单独在目标库中创建数据库，同时源库中导出就不使用-B 参数， 直接使用数据库名称，使用source 导入
导入数据库
将数据复制到目标库中，使用source 数据文件路径 导入数据库
shell
体验AI代码助手
代码解读
复制代码
# source /home/mysql/dt_new.sql
参考信息
- Mysql 数据库只导出数据的存储过程与函数
css
体验AI代码助手
代码解读
复制代码
#mysqldump -uroot -p11111111 -ndt -B dt_news -R --single-transaction >dt_news.sql
-n 不创建数据库，-d 不迁移数据，-t 不产生创建表格的语句
- Mysql 数据库只导出数据结构
css
体验AI代码助手
代码解读
复制代码
mysqldump -uroot -p11111111 -d -B dt_news >dt_news_view1.sql
导出创建数据库语句，表语句，以及视图信息
