- 需求:MySQL 5.6通过mysqldump迁移到MySQL 8.0.x生产方案
- mysqldump迁移适用小库
1、检查系统字符集
echo $LANG
2、检查数据库字符集
show variables like '%char%';
mysql> show variables like '%char%';
+--------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------------------------------+
| character_set_client | latin1 # 系统级别 |
| character_set_connection | latin1 |
| character_set_database | latin1 # 数据库级别 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /mysql/app/mysql-5.6.51-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
3、检查数据库创建默认字符集
show create database db_name; -- 查看数据库的字符集
select count(*) ct,CHARACTER_SET_NAME from COLUMNS where TABLE_SCHEMA = 'db_name' group by 2;
4、查看数据下面的表字符集
select count(*) ct,TABLE_COLLATION from tables where TABLE_SCHEMA like 'db_name' group by 2;
use db_name;
show create table xxx;
5、如果当前系统字符集与数据库级别不一致
export LANG=latin1 # 临时设置,当前窗口有效,设置与(show variables like character_set_database';)一致
echo $LANG
6、导出数据,mysqldump默认导出字符集在各个版本是存在差异的。
# mysql 5.6:mysqldump默认字符集utf8
# mysql 5.7:mysqldump默认字符集utf8
# mysql 8.x:mysqldump默认字符集utf8mb4
- # mysqldump参数:
# -R :Dump functions and procedures
# -E :Dump events
# --default-character-set :指定导出字符集
# --single-transaction :锁相关
# --flush-logs :刷日志
# --databases :指定数据库,导出某个数据库
1) 导出前,旧机器设置为只读
set global read_only = on;
2) 导出数据
/mysql/app/mysql6/bin/mysqldump -uroot -prootroot -P3306 -h127.0.0.1 \
-R -E --default-character-set=latin1 \
--single-transaction \
--flush-logs \
--databases db_name> /backup/db_name_$(date +%F).sql
7、把/backup/db_name_$(date +%F).sql传到异机相同目录
scp /backup/db_name_$(date +%F).sql mysql@IP:/backup/db_name_$(date +%F).sql
8、恢复
- # 按上面步骤,检查字符集:系统字符集、数据库创建字符集一致后,再执行导入
1) 检查
show variables like '%char%'; -- 检查数据库默认启动字符集
show create database db_name; -- 查看数据库的字符集
- # 如果创建数据字符不一致,需重新创建
# drop database db_name;
# create database db_name
# CREATE DATABASE db_name DEFAULT CHARACTER SET latin1;
2) 异机导入
# -o :指定数据名恢复
mysql -uroot -proot -p -o db_name < /backup/db_name_$(date +%F).sql
9、检查
1) 检查数据库创建默认字符集
show create database db_name; -- 查看数据库的字符集
select count(*) ct,CHARACTER_SET_NAME from COLUMNS where TABLE_SCHEMA = 'db_name' group by 2;
2) 查看数据下面的表字符集
select count(*) ct,TABLE_COLLATION from tables where TABLE_SCHEMA like 'db_name' group by 2;
use db_name;
show create table xxx;
3) 检查表数据