目前常用的有3中数据导入与导出方法:
mysqldump
工具:
secure_file_priv
参数限制。在选择使用哪种方法时,还需要考虑数据的大小、是否需要跨平台迁移、是否有权限访问服务器文件系统、是否需要保留表结构等因素。通常,如果需要快速迁移大量数据并且对数据的完整性有高要求,物理拷贝表空间是一个好选择。如果数据量较小或者需要跨平台迁移,使用mysqldump
或导出CSV文件可能更合适。
使用mysqldump
导出数据:
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
-h: 指定MySQL服务器的主机名。$host: 替换为实际的主机名。
-P: 指定MySQL服务器的端口号。$port: 替换为实际的端口号。
-u: 指定登录MySQL的用户名。`$user`: 替换为实际的用户名。
--add-locks=0: 导出时不增加额外的锁。
--no-create-info: 不导出表结构。
--single-transaction: 在导出数据时不需要对表加表锁。
--set-gtid-purged=OFF: 不输出与GTID相关的信息。
db1: 指定要导出的数据库名。
t: 指定要导出的表名。
--where="a>900": 导出满足条件a>900的数据。
--result-file=/client_tmp/t.sql: 指定导出结果的文件路径。
将数据导入到目标数据库:
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
`-h`: 指定MySQL服务器的主机名。`root`: 使用root用户登录。
`-P`: 指定MySQL服务器的端口号。
`-u`: 指定登录MySQL的用户名。
`db2`: 指定要导入数据的数据库名。
`-e`: 后面跟随要执行的命令。
`"source /client_tmp/t.sql"`: 执行source命令导入之前导出的SQL文件。
导出为CSV文件:
SELECT * FROM db1.t WHERE a > 900 INTO OUTFILE '/server_tmp/t.csv';
SELECT * FROM db1.t: 指定要导出的查询。
WHERE a > 900: 导出满足条件的数据。
INTO OUTFILE '/server_tmp/t.csv': 指定导出结果的CSV文件路径。
导入CSV文件到目标表:
LOAD DATA INFILE '/server_tmp/t.csv' INTO TABLE db2.t;
LOAD DATA INFILE: 加载数据的命令。
'/server_tmp/t.csv': 指定CSV文件的路径。
INTO TABLE db2.t: 指定要导入数据的目标表。
在MySQL中secure_file_priv
用于限制LOAD DATA INFILE
和SELECT ... INTO OUTFILE
这两个命令生成或读取文件的位置。这个参数的目的是为了增强安全性,防止意外或恶意地读取或写入服务器上的敏感文件。
如果secure_file_priv
被设置为空字符串(''
)或者NULL
,则表示没有文件路径限制,可以使用任意文件路径。但是,这种设置降低了系统的安全性,因此不推荐在生产环境中使用。
物理拷贝表空间:
首先创建一个相同结构的空表:
CREATE TABLE db2.r LIKE db1.t;
然后丢弃表空间:
ALTER TABLE db2.r DISCARD TABLESPACE;
导出表文件:
FLUSH TABLES db1.t FOR EXPORT;
拷贝文件:
cp /path/to/db1/t.ibd /path/to/db2/r.ibd
cp /path/to/db1/t.cfg /path/to/db2/r.cfg
解锁表并导入表空间:
UNLOCK TABLES;
ALTER TABLE db2.r IMPORT TABLESPACE;