MySQL 数据迁移

mysqldump 实现

mysqldump 是 MySQL 自带的备份工具,可以选择性的导出库表(整库、多库、单库、多表、单表)数据为 sql 文件

导出所有数据库

# -t:等价于 --no-create-info,表示仅导出数据,不导出表结构
# -d:等价于 --no-data,表示不包含数据,仅导出表结构
mysqldump -uroot -p --all-databases [-t] [-d] > 文件路径

导出指定单个或多个数据库

mysqldump -uroot -p --databases [-t] [-d] test1 [test2] [test3] > 文件路径

导出指定数据库的单个或多个表

mysqldump -uroot -p [-t] [-d] testDB test1 [test2] [test3] > 文件路径

导出指定数据表指定条件的数据

mysqldump -uroot -p [-t] [-d] testDB test1 --where "name = '张三'" > 文件路径

得到备份 sql 文件后导入

mysqldump -h [目标服务器ip] -P [目标服务器端口] -uroot -p < 文件路径

也可以使用 source 命令直接执行 sql 文件

source 文件路径

into oufile 和 load data infile 实现

授权用户 file 权限:

mysql > select * from mysql.user where user='root' \G;
mysql > update mysql.user set File_priv='Y' where user='root';
mysql > select * from mysql.user where user='root' \G;
mysql > flush privileges;

MySQL 使用 into outfile 语法导出数据时,只能导出数据文件到 secure-file-priv 指定的安全路径下,查看安全路径命令如下:

mysql> show variables like '%secure%';

如果不想用默认安全路径,可以在 MySQL 配置文件修改参数 --secure-file-priv 为自定义路径,如修改为空字符串 "" 则表示不限制路径

使用 into outfile 导出数据文件

select * from test into outfile 'C:/xxx/test.txt' [options];

into outfile 参数说明:

  • character set utf8:设置字符集为 utf8,防止中文乱码,需要放在 fields 前面
  • fields:域,后面常用字段有 terminated/optionally/escaped
    • terminated by 'string':设置字段数据之间的分隔符
    • optionally
      • enclosed by 'char':设置字段非数值的数据,使用什么符号引起,如英文双引号
      • escaped by 'char':字段数据存在特殊符号使用的转移符,默认是反斜杠
  • lines:设置每条记录的开头starting和结尾字符terminated
    • starting by 'char':设置每条记录的开头字符,默认空字符串
    • terminated by 'char':设置每条记录的结尾字符默认换行符

得到数据文件后,使用 load data infile 导入数据库

load data infile [文件路径] into table [表名称] [options];
  • character set utf8:设置字符集为 utf8,防止中文乱码,需要放在 fields 前面
  • fields:域,后面常用字段有 terminated/optionally/escaped
    • terminated by 'string':设置字段数据之间的分隔符
    • optionally
      • enclosed by 'char':设置字段非数值的数据,使用什么符号引起,如英文双引号
      • escaped by 'char':字段数据存在特殊符号使用的转移符,默认是反斜杠
  • lines:设置每条记录的开头starting和结尾字符terminated
    • starting by 'char':设置每条记录的开头字符,默认空字符串
    • terminated by 'char':设置每条记录的结尾字符默认换行符
  • (字段1,字段2,字段3):指定字段导入数据,注意放在整个语句最后

参数大多只需要跟 into outfile 导出参数保持一致即可

与 mysqldump 对比优缺点如下:

  • mysqldump:能完整备份结构和数据,包括表结构、存储过程、触发器等,但备份文件占用空间较大,备份和恢复的效率较慢
  • into outfile / load data infile:只能备份数据,但备份文件占用空间较小,备份和恢复效率较高

Kettle

Kettle 是一款基于 Java 的 ETL 工具,当涉及异构表或异构库的数据迁移时,可以使用 Kettle 工具轻松实现

参考文章:https://blog.csdn.net/Jmayday/article/details/102860582


迁移数据一致性

1. 备份一致性

MySQL 备份数据过程中如果发生数据更新,可以用以下方式处理:

使用锁机制。在备份开始前,对相关表或者整个数据库加锁,例如使用 FLUSH TABLES WITH READ LOCK 语句,阻止其他事务对数据修改,保证备份数据的一致性。这种方式会一定程度影响数据库的可用性,在锁生效期间,其他用户对数据的写操作会被阻塞

使用事务隔离级别。将备份操作放在一个具有高隔离级别的事务中,比如可重复读(REPEATABLE READ)隔离级别,这样事务内部读取的数据是事务开始时的数据状态,不受其他事务更新数据的影响

使用 MySQL 自带的备份工具,如 mysqldump,它提供一些选项来处理备份期间的数据变化。例如,使用 --single-transaction 选项,备份时会在一个事务中进行,确保备份数据的一致性,不会阻塞其他事务的读写操作

2. 恢复一致性

备份时最好同时备份二进制日志。在恢复数据时,先从备份文件恢复数据,再依据二进制日志里记录的备份后发生的事务,按照顺序应用这些事务,达到最终的一致性状态。例如,备份是在上午 10 点完成的,之后到 11 点的数据库更改都记录在二进制日志,恢复时就需要把日志中的更改应用到恢复的数据上

使用 mysqlbinlog 工具备份二进制日志:

静态备份,可先使用 show binary logs 语句查看当前服务器的二进制日志文件列表。如需备份特定的一组日志文件,如 binlog.000130 到 binlog.000132 ,可执行 mysqlbinlog --read-from-remote-server --host=host_name --raw binlog.000130 binlog.000131 binlog.000132;。若只指定起始文件并读取到最后一个文件,可使用 --to-last-log 选项,如 mysqlbinlog --read-from-remote-server --host=host_name --raw --to-last-log binlog.000130

实时备份,若要从某个日志文件开始进行实时备份,备份后续产生的所有新日志,可使用 --stop-never 选项,如 mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000130

posted @   低吟不作语  阅读(8)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示