mysql数据表导出导入过程详解_mysqldump导出一张表数据

一、mysqldump导出sql文件

1.内网导出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更优秀）
#安装screen
yum -y install screen
#创建有名字的screen任务(执行该命令后，其实就进入了screen的shell环境,此时执行的操作 都是放在screen里的)
screen -S task_name
#查看到系统中所有的screen任务的pid
screen -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_name
exit


mysqldump参数简介：(其中-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日志，同步则不会被同步。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

2.当用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.sql
tail 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

3.编写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}
    #如果导出数据库：
    #mysqldump --skip-lock-tables --set-gtid-purged=OFF    -h  10.x.x.x   -P 3306  -utest_user  -p'test_password'    test_db    > ${FILE_NAME}
done

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

编写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 命令导入表数据
    mysql   -h  10.x.x.x   -P 3306  -utest_user  -p'test_password'  导入的库名   <  ${FILE_NAME}
    #如果导入的是数据库(此时${i}是库名)：
    #mysql    -h  10.x.x.x   -P 3306  -utest_user  -p'test_password'    ${i}    < ${FILE_NAME}
done

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.验证是否导出成功：

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

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

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

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

2.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 密钥文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

rsync   -P -e "ssh -i 密钥路径"  用户名@云服务器ip:云服务器文件路径 本地文件夹路径


常用参数：

--progress： 显示拷贝进度

--partial：保留不完整文件，实现断点续传

--partial-dir=DIR：指定不完整文件的存储目录，而不是默认存储到目的地目录。

-P：包含--progress和--partial

--rsh=ssh：使用ssh方式传输文件，注意：如果之前设置过ssh免密码登录，那么此时也就不需要密码了，非常方便

-v：显示详细信息

-a：归档模式。也就是以递归方式传输文件，并保持所有文件属性。

-r：递归方式传输文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.解压缩文件：

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

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

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

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

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

mysql -uroot -p database < fileName.sql
1

注意fileName.sql要有路径名

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

在导入 SQL 文件到 MySQL 数据库时，使用 mysql 命令和 source 命令都是常见的方式，它们各有优劣和适用场景。

1. mysql 命令导入：使用 mysql 命令可以直接将 SQL 文件导入到 MySQL 数据库。您可以使用以下命令执行导入： ```mysql -u your_username -p your_database < your_sql_file.sql

2. `source` 命令导入：在 MySQL 命令行界面中，可以使用 `source` 命令来执行 SQL 文件。首先，进入 MySQL 命令行界面，然后使用以下命令导入 SQL 文件：    ```source your_sql_file.sql    ```
这种方式的优点是可以在 MySQL 命令行界面中直接执行，适用于需要与数据库交互的复杂导入任务。它还可以用于执行部分 SQL
文件或在交互式会话中逐行执行 SQL 语句。

在效率方面，两种方式的性能差异通常不大。导入速度更多取决于 SQL 文件的大小、数据库服务器的性能和网络连接质量。对于大型 SQL
文件，可能会花费一些时间来完成导入，无论使用哪种方式。

总的来说，对于简单的导入任务，使用 `mysql` 命令是更常用和方便的方式。而对于需要与数据库进行交互或执行复杂导入任务的情况，使用
`source` 命令更为适合。

建议根据具体情况选择合适的方式，并在实际操作中进行测试和比较，以确定哪种方式最适合您的需求。
1
2
3
4
5
6
7
8
9
10
11
12

参考文章：
关于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