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

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

数据库运维需要掌握的技能之一数据库的迁移，而MySQL数据库是我们常用的数据库之一，掌握mysql 数据库的数据迁移，也是必备的技能选项。数据库的迁移，我们必须要思考如下几个点：

1. 需要迁移数据库的数据量大小
2. 需要迁移的数据库对象是什么，
3. 考虑使用何种迁移方法！ 这三个思考要点，适合于所用的数据库迁移，请大家留意。 思考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

1. 计算数据库总空间大小

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;

1. 计算数据库的数据大小

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;

1. 计算数据库索引长度 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;

基于某一个库计算每一个表的空间大小

1. 计算数据库的表占用空间大小，并按表的大小降序排列

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;

1. 计算数据库的表数据占用空间大小, ，并按表的大小降序排列

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;

1. 计算数据库的表索引占用空间大小 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

导出创建数据库语句，表语句，以及视图信息