MYSQL 数据迁移利器 MYSQLSH

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