mysql数据表导出导入过程详解

一、mysqldump导出sql文件

内网导出sql文件速度快，推荐内网导出。外网导出的时候容易断连接，外网导出时使用nohup后台运行，一般不会断。但是一些无效的报警信息会进入到sql文件首行里，导致sql文件不能使用，需要剔除掉这种无效报警信息。

#不加密码(交互式输入密码)：mysqldump --skip-lock-tables --set-gtid-purged=OFF   -h  10.x.x.x   -P 3306  -utest_user   test_db  test_table >testfile_20230525.sql  -p        (sql文件无无效的警告信息)#加密码时，密码要带引号：mysqldump --skip-lock-tables   --set-gtid-purged=OFF  -h  10.x.x.x   -P 3306  -utest_user   test_db  test_table >testfile_20230525.sql  -p'test_password'(sql文件无无效的警告信息)#ctl+c后仍在运行，但关闭ssh会话后不可以运行：mysqldump --skip-lock-tables  --set-gtid-purged=OFF   -h  10.x.x.x   -P 3306  -utest_user   test_db  test_table >testfile_20230525.sql  -p'test_password' &(sql文件无无效的警告信息)#关闭ssh会话后仍可以运行：nohup  mysqldump --skip-lock-tables  --set-gtid-purged=OFF   -h  10.x.x.x   -P 3306  -utest_user   test_db  test_table >testfile_20230525.sql  -p'test_password' &(但sql文件首行可能有无效的警告信息，需要用sed剔除一下)#使用screen后台运行#优点：单独开一个会话放到后台，无效的报警信息会被打印到该后台终端，而不会被写入sql文件，而且可以满足交互式场景下后台运行。（总体来说比nohup更优秀）#安装screenyum -y install screen#创建有名字的screen任务(执行该命令后，其实就进入了screen的shell环境,此时执行的操作 都是放在screen里的)screen -S task_name#查看到系统中所有的screen任务的pidscreen -ls#或者ps -ef来查找screen的任务ps -ef | grep task_name#进入screen任务以后，就可以运行相要后台跑的任务了mysqldump --skip-lock-tables  --set-gtid-purged=OFF   -h  10.x.x.x   -P 3306  -utest_user   test_db  test_table >testfile_20230525.sql  -p'test_password' &#然后把该screen放后台方法1：快捷键  ctrl+a+d方法2：再开一个终端screen -d task_name#进入screen任务screen -r pid或者screen -r task_name#删除screen任务screen -r task_nameexitmysqldump参数简介：(其中-u和-p参数后要紧接用户名或密码，不能加空格)-h:mysql主机的IP。-P:mysql服务对应的端口。-u:mysql用户名。-p（密码两端需要加单引号）:mysql密码。skip-lock-tables:--skip-lock-tables参数指示mysqldump实用程序在获取将在每个表上获取READ锁的转储之前，不要发出LOCK TABLES命令。 数据库中的所有表都应被锁定，以提高备份过程的一致性。 即使使用了skip-lock-tables，在转储表时，它也不会收到任何INSERT或UPDATE，因为由于需要SELECT才能从表中获取所有记录，因此它将被锁定。set-gtid-purged=OFF：加了--set-gtid-purged=OFF时，在会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加--set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。

当用nohup后台导出sql文件时，一些无效的报警信息可能会被写入sql文件的前几行。
这时候需要剔除掉这些信息，不然的话sql文件就不能正常导入mysql了。

#报警信息示例：#密码写入命令行报警#Warning: Using a password on the command line interface can be insecure.#没加--set-gtid-purged=OFF的报警信息#Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. #sql文件一般很大，所以不要直接vim打开。用head和tail查看，用sed修改。#定位sql文件前几行有报警信息#默认head查看文件前10行，tail查看文件后10行head xxx.sqltail xxx.sql#查看sql文件前三行信息(以此类推即可)head -n 3  xxx.sql#查看sql文件后三行信息(以此类推即可)tail -n 3 xxx.sql#删除sql文件里无效的报警信息。#删除文件 1到n行sed -i '1,nd' 文件名 #列如，前3行是无效的报警信息，则sed -i '1,3d' xxx.sql

编写shell脚本来循环导出多张数据表。

#!/bin/bash# 需要导出的表名列表TABLE_LIST=( "table_1" "table_2" "table_3" "table_4" )# 循环导出每个表for i in "${TABLE_LIST[@]}"do    # 构造导出文件名    FILE_NAME="${i}_20230525.sql"    echo ${FILE_NAME}    # 使用 mysqldump 命令导出表数据    mysqldump --skip-lock-tables --set-gtid-purged=OFF    -h  10.x.x.x   -P 3306  -utest_user  -p'test_password'    test_db   ${i} > ${FILE_NAME}done

验证是否导出成功：

# 查看sql文件最后一行。tail -n 1 xxx.sql

如果有dump completed on 日期。
说明dump成功了，有时候因为会话中断等原因，dump失败，就没有这一行。

二、sql文件从云服务器备份到本地Linux服务器：
1.压缩文件下所有文件

tar -zcvf mysql_dump_20230525.tar.gz  ./mysql_dump/

1使用scp传到本地服务器：（但是scp传大文件容易断掉stalled）

本地服务器运行：scp username@remote:/path/to/file /path/to/destination即scp 用户名@云服务器ip:云服务器文件路径 本地文件夹路径如果要将整个目录传输到本地Linux服务器中，可以使用-r参数，命令如下：scp -r 用户名@云服务器ip:云服务器文件夹路径 本地文件夹路径输入命令后按下回车键，然后输入云服务器的密码，就可以将文件从云服务器传输到本地Linux服务器了。如果云服务器使用密钥登录时，需要添加一个-i的参数，并输入对应密钥的路径即可。scp -i 对应密钥地址 用户名@云服务器ip:云服务器文件路径 本地文件夹路径如果报权限错误，那么需要 chmod 400 修改一下密钥文件的权限。即：chmod 400 密钥文件

2.也可以使用rsync传输文件：（可以断点续传，断了以后，重新运行该命令即可）

rsync   -P -e "ssh -i 密钥路径"  用户名@云服务器ip:云服务器文件路径 本地文件夹路径常用参数：--progress： 显示拷贝进度--partial：保留不完整文件，实现断点续传--partial-dir=DIR：指定不完整文件的存储目录，而不是默认存储到目的地目录。-P：包含--progress和--partial--rsh=ssh：使用ssh方式传输文件，注意：如果之前设置过ssh免密码登录，那么此时也就不需要密码了，非常方便-v：显示详细信息-a：归档模式。也就是以递归方式传输文件，并保持所有文件属性。-r：递归方式传输文件

解压缩文件：

tar -xzvf mysql_dump_20230525.tar.gz -C /home/mysql_dump   # -C 指定解压路径

三、导入sql文件：
1.linux下mysql导入sql文件方法1：
进入linux命令命令行下

mysql -uroot -p 回车 输入密码source fileName.sql

注意fileName.sql要有路径名，例如：source /home/user/data/fileName.sql

linux下mysql导入sql文件方法2：
进入linux命令命令行下：

mysql -uroot -p database < fileName.sql

注意fileName.sql要有路径名

导入数据库时报错：Variable ‘time_zone’ can’t be set to the value of ‘NULL’
这个问题的原因是因为数据sql文件内部有注释，去掉就可以了(也可以不用管)，不影响数据导入。

参考文章：
关于mysql：skip-lock-tables和mysqldump https://www.codenong.com/7415698/

mysqldump关于–set-gtid-purged=OFF的使用 https://www.cnblogs.com/–smile/p/11464687.html

mysqldump --set-gtid-purged=OFF 参数解析https://www.cnblogs.com/ybyqjzl/p/12428039.html

SCP命令如何使用密钥传输 https://zhuanlan.zhihu.com/p/358987274

screen的使用方法 https://blog.csdn.net/weixin_43557605

linux使用scp时间为stalled,Linux命令学习(2): scp和rsync基本用法与断点续传 https://blog.csdn.net/weixin_36194553/article/details/116737707

来源地址：https://blog.csdn.net/qq_44821149/article/details/130866987