Mysql数据库结构、表结构备份导出导入

2023-08-18 07:18| 来源: 网络整理| 查看: 265

备份导出

只导出数据结构(表结构)，不导出数据： mysqldump --opt -d --column-statistics=0 -h localhost -P 3306 -u root -p [database] [table] > database.sql

即导出数据结构(表结构)，又导出数据： mysqldump --opt -h localhost -P 3306 -u root -p [database] [table] > database.sql

常见错误

低版本的mysql备份时会出现如下错误，需要在命令行中添加参数 --column-statistics=0

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"') FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = 'database' AND TABLE_NAME = 'table';': Unknown table 'COLUMN_STATISTICS' in information_schema (1109)

参数注解 -h, --host=name #主机名 -P[ port_num], --port=port_num #用于连接MySQL服务器的的TCP/IP端口号 --master-data #这个选项可以把binlog的位置和文件名添加到输出中，如果等于1，将会打印成一个CHANGE MASTER命令；如果等于2，会加上注释前缀。并且这个选项会自动打开--lock-all-tables，除非同时设置了--single-transaction（这种情况下，全局读锁只会在开始dump的时候加上一小段时间，不要忘了阅读--single-transaction的部分）。在任何情况下，所有日志中的操作都会发生在导出的准确时刻。这个选项会自动关闭--lock-tables。 -x, --lock-all-tables #锁定所有库中所有的表。这是通过在整个dump的过程中持有全局读锁来实现的。会自动关闭--single-transaction和--lock-tables。 --single-transaction #通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照。只有当表使用支持MVCC的存储引擎（目前只有InnoDB）时才可以工作；其他引擎不能保证导出是一致的。当导出开启了--single-transaction选项时，要确保导出文件有效（正确的表数据和二进制日志位置），就要保证没有其他连接会执行如下语句：ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE，这会导致一致性快照失效。这个选项开启后会自动关闭--lock-tables。 -l, --lock-tables #对所有表加读锁。（默认是打开的，用--skip-lock-tables来关闭，上面的选项会把关闭-l选项） -F, --flush-logs #在开始导出前刷新服务器的日志文件。注意，如果你一次性导出很多数据库（使用 -databases=或--all-databases选项），导出每个库时都会触发日志刷新。例外是当使用了--lock-all-tables或--master-data时：日志只会被刷新一次，那个时候所有表都会被锁住。所以如果你希望你的导出和日志刷新发生在同一个确定的时刻，你需要使用--lock-all-tables，或者--master-data配合--flush-logs。 --delete-master-logs #备份完成后删除主库上的日志。这个选项会自动打开``--master-data`。 --opt #同-add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys。（默认已开启，--skip-opt关闭表示这些选项保持它的默认值）应该给你为读入一个MySQL服务器的尽可能最快的导出，--compact差不多是禁用上面的选项。 -q, --quick #不缓冲查询，直接导出至stdout。（默认打开，用--skip-quick来关闭）该选项用于转储大的表。 --set-charset #将SET NAMES default_character_set加到输出中。该选项默认启用。要想禁用SET NAMES语句，使用--skip-set-charset。 --add-drop-tables #在每个CREATE TABLE语句前添加DROP TABLE语句。默认开启。 --add-locks #在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。默认开启。 --create-option #在CREATE TABLE语句中包括所有MySQL表选项。默认开启，使用--skip-create-options来关闭。 -e, --extended-insert #使用全新多行INSERT语法，默认开启（给出更紧缩并且更快的插入语句） -d, --no-data #不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的。 --add-drop-database #在create数据库之前先DROP DATABASE，默认关闭，所以一般在导入时需要保证数据库已存在。 --default-character-set= #使用的默认字符集。如果没有指定，mysqldump使用utf8。 -B, --databases #转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name语句包含在每个新数据库前的输出中。 --tables #覆盖--database选项。选项后面的所有参量被看作表名。 -u[ name], --user= #连接服务器时使用的MySQL用户名。 -p[password], --password[=password] #连接服务器时使用的密码。如果你使用短选项形式(-p)，不能在选项和密码之间有一个空格。如果在命令行中，忽略了--password或-p选项后面的密码值，将提示你输入一个。导入 source 命令导入 source database.sql mysql 命令导入 mysql -u userName -p passwd -h host -P port database_name < database.sql 使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。以下实例中将从当前目录中读取文件 database.sql ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'database.sql' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

mysql> LOAD DATA LOCAL INFILE 'database.sql' INTO TABLE mytbl -> FIELDS TERMINATED BY ':' -> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'database.sql' -> INTO TABLE mytbl (b, c, a); 使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 database.sql 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local mytbl database.sql password *****

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \ --lines-terminated-by="\r\n" mytbl database.sql password *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \ mytbl database.sql password ***** 导出时直接导入其他数据库 mysqldump -u root -p database_name \ | mysql -h other-host.com database_name 参考链接

MySQL备份命令mysqldump参数说明与示例

MySQL 导出数据

MySQL 导入数据

作者：草帽豆子原文链接：juejin.cn/post/698134…

【本文地址】

公司简介

联系我们