mysql数据库迁移

MySQL 迁移是 DBA 日常维护中的一个工作。迁移，究其本义，无非是把实际存在的物体挪走，保证该物体的完整性以及延续性。就像柔软的沙滩上，两个天真无邪的小孩，把一堆沙子挪向其他地方，铸就内心神往的城堡。

生产环境中，有以下情况需要做迁移工作，如下：
1.磁盘空间不够。比如一些老项目，选用的机型并不一定适用于数据库。随着时间的推移，硬盘很有可能出现短缺；
2.业务出现瓶颈。比如项目中采用单机承担所有的读写业务，业务压力增大，不堪重负。如果 IO 压力在可接受的范围，会采用读写分离方案；
3.机器出现瓶颈。机器出现瓶颈主要在磁盘 IO 能力、内存、CPU，此时除了针对瓶颈做一些优化以外，选择迁移是不错的方案；
4.项目改造。某些项目的数据库存在跨机房的情况，可能会在不同机房中增加节点，或者把机器从一个机房迁移到另一个机房。再比如，不同业务共用同一台服务器，为了缓解服务器压力以及方便维护，也会做迁移。

一句话，迁移工作是不得已而为之。实施迁移工作，目的是让业务平稳持续地运行。

二 MySQL 迁移方案概览

MySQL 迁移无非是围绕着数据做工作，再继续延伸，无非就是在保证业务平稳持续地运行的前提下做备份恢复。那问题就在怎么快速安全地进行备份恢复。

一方面，备份。针对每个主节点的从节点或者备节点，都有备份。这个备份可能是全备，可能是增量备份。在线备份的方法，可能是使用 mysqldump，可能是 xtrabackup，还可能是 mydumper。针对小容量（10GB 以下）数据库的备份，我们可以使用 mysqldump。但针对大容量数据库（数百GB 或者 TB 级别），我们不能使用 mysqldump 备份，一方面，会产生锁（其实可以加参数使之不锁表）；另一方面，耗时太长。这种情况，可以选择 xtrabackup 或者直接拷贝数据目录。直接拷贝数据目录方法，不同机器传输可以使用 rsync，耗时跟网络相关。使用 xtrabackup，耗时主要在备份和网络传输。如果有全备或者指定库的备份文件，这是获取备份的最好方法。如果备库可以容许停止服务，直接拷贝数据目录是最快的方法。如果备库不允许停止服务，我们可以使用 xtrabackup（不会锁定 InnoDB 表），这是完成备份的最佳折中办法。

另一方面，恢复。针对小容量（10GB 以下）数据库的备份文件，我们可以直接导入。针对大容量数据库（数百GB 或者 TB 级别）的恢复，拿到备份文件到本机以后，恢复不算困难。具体的恢复方法可以参考第三节。

三 MySQL 迁移实战

3.1 使用数据库管理工具（Navicat）

3.1.1 导出、导入sql文件方式

（1）导出sql文件：选择需要导出数据库表，右击--->转储为sql文件---->结构和数据

3.2 使用mysqldump数据导出和source数据导入

注意：

• mysqldump命令要在dos窗口或者shell窗口，不需要登录进去数据库里面执行；

• source命令必须登录进去数据库里面执行；

3.2.1 mysqldump导出数据

mysqldump命令要在dos窗口或者shell窗口，不需要登录进去数据库里面执行；

①导出所有数据库（包括系统数据库）到指定文件下

mysqldump -u$$$ -p*** --all-databases > /root/all.sql

注：$$$为用户***为用户密码（最好回车后填写）命令后面没有任何标点符号

mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

–single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
–add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;" ；
–no-create-info 的意思是，不需要导出表结构；
–set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；
–result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上的。
————————————————
版权声明：本文为CSDN博主「ZHY_ERIC」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/ZHY_ERIC/article/details/123584236

以下不在提示，如使用root用户，密码为123456

②导出所有数据

mysqldump -uroot -p123456 --single-transaction --quick saasscrm > /root/saasscrm_202205271520.sql

③只导出表结构不导出数据：--no-data （或者 -d）

mysqldump -uroot -p123456 -d --single-transaction --quick saasscrm > /root/saasscrm_202205271529.sql

④只导出表数据不导出表结构： -t

mysqldump -uroot -p123456  -t --single-transaction --quick saasscrm > /root/saasscrm_202205271528.sql

⑤跨服务器导出导入数据

mysqldump --host=192.168.252.157 -uroot -p123456 -C --database db1 > /root/saasscrm_202205271528.sql

| mysql --host=192.168.252.159 -uroot -p123456 db1

注意：加上-C参数可以启用压缩传递

https://blog.csdn.net/ZHY_ERIC/article/details/123584236

3.2.1 source导入数据

source命令必须登录进去数据库里面执行；

（1）导入数据库

登录进入mysql界面使用CREATE命令创建数据库后使用USE命令进入该数据库，然后再使用SOURCE命令即可导入数据库

注：如果sql文件中已经有数据库创建语句，则不用使用CREATE命令

例如：导入数据库db1

mysql -uroot -p 

CREATE DATABASE db1;

USE db1;

SOURCE /root/dn1.sql;

（2）导入数据表

登录进入mysql界面选择数据库使用SOURCE命令即可

远程

mysql -h <RDS实例连接地址> -P <RDS实例端口> -u <RDS实例账号> -p <RDS数据库名称> < /tmp/<自建数据库名>.sql

mysql -habc.com -P 3306 -uroot -p1234 qdmscrm < /root/saasscrm_202205271520.sql

http://t.zoukankan.com/yang5726685-p-15703009.html

https://help.aliyun.com/document_detail/211596.htm?spm=a2c4g.11186623.0.0.41d815a4eEwFuI#task-2067477

3.2.1 mysqldump导出数据

1.导出数据

mysqldump -uroot -pxxx --single-transaction --quick saasscrm > /root/saasscrm_2022083101.sql

2.导入数据

mysql -uroot -pAbc@2021 

use qdmscrm

SOURCE /root/saasscrm_202205271520.sql

远程

mysql -h <RDS实例连接地址> -P <RDS实例端口> -u <RDS实例账号> -p <RDS数据库名称> < /tmp/<自建数据库名>.sql

mysql -hxx.com -P 3306 -uzhangsan -plisi qdmscrm < /root/saasscrm_202205271520.sql

mysqladmin -u root -p shutdown

cp my.cnf.2022053101 my.cnf

systemctl restart mysqld.service

systemctl status mysqld.service

systemctl start mysqld.service