1. 导出数据库
docker ps :查看数据库对应的 CONTAINER ID
docker exec -it id /bin/bash : 进入到mysql的docker实例中
cd /usr/bin : 进入到bin目录
mysqldump -u root -p123456 study > /root/study_backup0509.sql :使用mysqldump备份库,注意密码与-p之间没有空格
docker cp af6c23453cbb:/root/study_backup0509.sql /root/study_backup0509.sql :退出docker容器,然后将文件复制或者移动docker容器外部指定的目录
sz ucp_backup0509.sql : 将文件保存到桌面
也可以使用客户端导出备份,然后选择需要导出的数据库和表,其他的选择默认即可。
2. 导入数据库
使用DBeaver导入SQL文件
先创建一个数据库,然后右键工具
选择刚才导出的SQL文件然后导入
3. 对比导入前后的数据库大小
查看库的大小
SELECT table_schema "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'study';
查看表的大小(数据+索引),并且按照大小逆序
SELECT
`Table`,
`Size (MB)`
FROM (
SELECT
table_name AS `Table`,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size (MB)`
FROM
information_schema.tables
WHERE
table_schema = 'study'
AND table_type = 'BASE TABLE'
) AS table_sizes
ORDER BY
`Size (MB)` DESC;
分别查看数据大小和索引大小
SELECT
table_name AS `Table`,
ROUND((data_length / 1024 / 1024), 2) AS `Data Size (MB)`,
ROUND((index_length / 1024 / 1024), 2) AS `Index Size (MB)`
FROM
information_schema.tables
WHERE
table_schema = 'study'
AND table_type = 'BASE TABLE'
ORDER BY
`Data Size (MB)` DESC;
具体查看某个索引的大小
SELECT database_name, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) size_in_mb
FROM mysql.innodb_index_stats
WHERE database_name='study'
and table_name='表名'
and index_name='索引名'
and stat_name = 'size'
ORDER BY size_in_mb DESC