1 迁移背景
将数据库从mysql5.7 迁移到mysql8.0. mysqlsh 支持mysql5.7以上版本。
2 查看数据量
SELECT TABLE_SCHEMA,
round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB,
round(SUM(data_length)/1024/1024,2) AS DATA_MB,
round(SUM(index_length)/1024/1024,2) AS INDEX_MB,
COUNT(*) AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys',
'mysql',
'INFORMATION_SCHEMA',
'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;
+--------------+----------+---------+----------+--------+
| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | TABLES |
+--------------+----------+---------+----------+--------+
| perceptor | 5575.42 | 5531.77 | 43.66 | 141 |
+--------------+----------+---------+----------+--------+
1 row in set (0.00 sec)
3 备份数据
mysqlsh root@localhost -S /tmp/mysql.sock -- util dump-instance '/tmp/bak20240517' --threads=16
Please provide the password for 'root@/tmp%2Fmysql.sock': *********
Save password for 'root@/tmp%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No):
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 141 tables, 0 views.
1 out of 4 users will be dumped.
Gathering information - done
All t