数据库运维：mysql 数据库迁移方法

数据库运维：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

计算数据库总空间大小代码语言：javascript代码运行次数：0运行复制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;计算数据库的数据大小代码语言：javascript代码运行次数：0运行复制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;

基于某一个库计算每一个表的空间大小计算数据库的表占用空间大小，并按表的大小降序排列代码语言：javascript代码运行次数：0运行复制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;计算数据库的表数据占用空间大小, ，并按表的大小降序排列代码语言：javascript代码运行次数：0运行复制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

代码语言：javascript代码运行次数：0运行复制sql 体验AI代码助手 代码解读复制代码select * from information_schema.routines where routine_schema ='XXXX'; -- XXXX代表数据库名称mysql5.7 查询

代码语言：javascript代码运行次数：0运行复制sql 体验AI代码助手 代码解读复制代码select `name` from mysql.proc where db = 'XXXX' -- -- XXXX代表数据库名称使用mysqldump 导出数据库代码语言：javascript代码运行次数：0运行复制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 数据文件路径 导入数据库

代码语言：javascript代码运行次数：0运行复制shell 体验AI代码助手 代码解读复制代码# source /home/mysql/dt_new.sql参考信息

Mysql 数据库只导出数据的存储过程与函数代码语言：javascript代码运行次数：0运行复制css 体验AI代码助手 代码解读复制代码 #mysqldump -uroot -p11111111 -ndt -B dt_news -R --single-transaction >dt_news.sql-n 不创建数据库，-d 不迁移数据，-t 不产生创建表格的语句

Mysql 数据库只导出数据结构代码语言：javascript代码运行次数：0运行复制css 体验AI代码助手 代码解读复制代码mysqldump -uroot -p11111111 -d -B dt_news >dt_news_view1.sql导出创建数据库语句，表语句，以及视图信息