金华市住房建设局网站,js制作网页游戏,绵阳 网站开发 公司,网络营销是什么专学科MySQL Shell拷贝一个库到一个新库 dump-schemas还是dump-tables导入是否skipBinlog实操#xff1a;导出和导入导入时可能的报错 ⛵️场景#xff1a;从同一台MySQL服务器的testdb中导出所有表。新建一个库testdb23#xff0c;将导出的备份导入新建的testdb23库。 dump-schem… MySQL Shell拷贝一个库到一个新库 dump-schemas还是dump-tables导入是否skipBinlog实操导出和导入导入时可能的报错 ⛵️场景从同一台MySQL服务器的testdb中导出所有表。新建一个库testdb23将导出的备份导入新建的testdb23库。 dump-schemas还是dump-tables
默认情况下MySQL Shell导出和导入的数据库SCHEMA是同一个。如果要导入到不同的数据库需要通过--schema关键字指定目标库。而在MySQL 8.0.28版本中该关键字只能用于导入dump-tables导出的备份。
#mysqlsh Ver 8.0.28 for Linux on x86_64 - for MySQL 8.0.28 (MySQL Community Server (GPL))mysqlsh -- util load-dump --help
...
--schemastrLoad the dump into the given schema. This option can only be usedwhen loading dumps created by the util.dumpTables() function.Default: not set.因此我们选择dump-tables而不是dump-schemas。需要注意的是dump-tables只会导出表和视图不会导出函数和存储过程。
官方文档中关于使用--schema关键字导入视图定义时可能存在的问题
From MySQL Shell 8.0.31, if the schema does not exist, it is created,
and the dump is loaded to that new schema.
If the new schema name differs from the schema name in the dump, the dump is loaded
to the new schema, but no changes are made to the loaded data.
That is, any reference to the old schema name remains in the data.
All stored procedures, views, and so on, refer to the original schema, not the new one.也就是说导入的视图定义中引用的表会指向原始库中的表。但是根据实际情况来看貌似MySQL 8.0.30中已经会自动更新视图定义中的schema名字了。
--Server version: 8.0.30 MySQL Community Server - GPLSQL show create view oradb2023.v_inf_asset;| CREATE ... DEFINER VIEW oradb2023.v_inf_asset AS select ... from oradb2023.v_gp_asset_rpt t where...SQL show create view oradb.v_inf_asset;| CREATE ... DEFINER VIEW oradb.v_inf_asset AS select ... from oradb.v_gp_asset_rpt t where ...dump-schemas和dump-tables的可用参数差异 只适用于dump-instance和dump-schemas的参数: includeTables、excludeTables过滤导出的表includeRoutines、excludeRoutines过滤导出的函数和存储过程routines是否导出函数和存储过程默认开启。 只适用于dump-tables的参数: all: 导出指定SCHEMA下得所有表和视图。 导入是否skipBinlog
导入数据前使用--dryRun参数检查导入信息但不进行实际的导入操作
mysqlsh root:127.0.0.1 -- util load-dump /mydata/backup/testdmp --threads4 --schematestdb23 --dryRun使用--skipBinlog参数控制是否在导入数据时写binlog默认关闭该参数。
在导入数据到生产主库并且希望导入的数据同步到备库时需要关闭该参数
mysqlsh root:127.0.0.1 -- util load-dump /mydata/backup/testdmp --threads4 --schematestdb23如果不希望导入的数据同步到备库或者仅仅在测试的单机库导入数据时为节省日志空间可以开启该参数
mysqlsh root:127.0.0.1 -- util load-dump /mydata/backup/testdmp --threads4 --schematestdb23 --skipBinlog实操导出和导入
要复制的源库为testdb
roottestdb show tables;
------------------
| Tables_in_testdb |
------------------
| t1 |
| t2 |
------------------
2 rows in set (0.00 sec)roottestdb select count(*) from testdb.t1;
----------
| count(*) |
----------
| 5 |
----------
1 row in set (0.00 sec)roottestdb select count(*) from testdb.t2;
----------
| count(*) |
----------
| 6 |
----------
1 row in set (0.00 sec)在同一台MySQL Server上创建导入目标库和对应用户
create database testdb23;
create user testdb23 identified with mysql_native_password by XXXXXX;grant select,insert,update,delete,create,drop,index,alter,execute,
create view,show view,create routine,alter routine,references on testdb23.* to testdb23 with grant option;
grant all on testdb23.* to testdb23 with grant option;尝试使用dump-schemas导出源库所有表和视图
mkdir /mydata/backup/testdmpmysqlsh root:127.0.0.1 -- util dump-schemas testdb --outputUrl/mydata/backup/testdmp --threads4 确定要导入的库开启了LOCAL_INFILE参数
SQL set global local_infileON;恢复到指定的数据库
[mysqldbhost ~]$ mysqlsh root:127.0.0.1 -- util load-dump /mydata/backup/testdmp --threads16 --schematestdb23
WARNING: Using a password on the command line interface can be insecure.
Loading DDL and Data from /mydata/backup/testdmp using 16 threads.
Opening dump...
ERROR: The dump was not created by the util.dumpTables() function, the schema option cannot be used.
ERROR: Invalid option: schema.可以看到dump-schemas导出的备份在导入时不支持schema参数指定导入的目标库。
下面使用dump-tables导出源库所有表和视图。
使用dump-tables DBNAME [] --all导出DBNAME库中的所有表和视图。
[mysqldbhost ~]$ mysqlsh root:127.0.0.1 -- util dump-tables testdb [] --all --outputUrl/mydata/backup/testdmp --threads4
WARNING: Using a password on the command line interface can be insecure.
Acquiring global read lock
Global read lock acquired
Initializing - done
2 tables and 0 views will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
110% (11 rows / ~10 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 209 bytes
Compressed data size: 212 bytes
Compression ratio: 1.0
Rows written: 11
Bytes written: 212 bytes
Average uncompressed throughput: 209.00 B/s
Average compressed throughput: 212.00 B/s 将导出的备份恢复到同一个MySQL Server中不同的数据库
[mysqldbhost ~]$ mysqlsh root:127.0.0.1 -- util load-dump /mydata/backup/testdmp --threads4 --schematestdb23
WARNING: Using a password on the command line interface can be insecure.
Loading DDL and Data from /mydata/backup/testdmp using 4 threads.
Opening dump...
Target is MySQL 8.0.30. Dump was produced from MySQL 8.0.30
Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
Executing common postamble SQL
100% (209 bytes / 209 bytes), 0.00 B/s, 2 / 2 tables done
Recreating indexes - done
2 chunks (11 rows, 209 bytes) for 2 tables in 1 schemas were loaded in 0 sec (avg throughput 209.00 B/s)
0 warnings were reported during the load. 检查导入的数据
roottestdb use testdb23;
Database changedroottestdb23 show tables;
--------------------
| Tables_in_testdb23 |
--------------------
| t1 |
| t2 |
--------------------
2 rows in set (0.01 sec)roottestdb23 select * from t1;
-------------------------------
| title | price |
-------------------------------
| Death Stranding | 198 |
| Elden Ring | 298 |
| Black Souls III | 193 |
| Divinity: Origin Sin 2 | 53 |
| Titanfall 2 | 24 |
-------------------------------
5 rows in set (0.00 sec)roottestdb23 select * from t2;
------------------------
| title | price |
------------------------
| Death Stranding | 198 |
| Elden Ring | 298 |
| Dark Souls III | 193 |
| Cuphead | 38 |
| The Witcher 3 | 58 |
| Limbo | 11 |
------------------------
6 rows in set (0.00 sec)导入时可能的报错
导入备份时可能收到以下报错
ERROR: Error executing DDL script for view testdb23.v_inf_top_operater:
MySQL Error 1146 (42S02): Table testdb23.t_ods_top_operater doesnt exist: ...
Executing view DDL - done
ERROR: Table testdb23.t_ods_top_operater doesnt exist原因是创建视图v_inf_top_operater时发现其定义中引用了不存在的表t_ods_top_operater数据导入中断。
解决办法通过excludeTables关键字排除报错的视图或表然后继续导入。
mysqlsh root:127.0.0.1 -- util load-dump /mydata/backup/testdmp --threads4 --schematestdb23 \
--excludeTablestestdb.v_inf_top_operater,testdb.v_xx_xxx,...默认会从中断时的进度继续导入无需清理数据重新开始。也可以手动清理数据后使用--resetProgress参数重置导入进度。
如果有太多太多视图导入报错可以排除掉所有视图只导入表。
--梳理源库中所有的视图名称
select table_schema,table_name from information_schema.views where table_schematestdb; -- 只包含视图select table_schema,table_name from information_schema.tables where table_schematestdb; -- 包含表和视图--拼接数据库名和视图名称
select group_concat(table_name) from information_schema.views where table_schematestdb\Gselect group_concat(concat_ws(.,table_schema,table_name) separator ,)
from information_schema.views where table_schematestdb\G最后建议数据量不大的话时效要求不高的话推荐优先使用mysqldump只需替换SQL文件中的数据库名即可。
References 【1】https://gottdeskrieges.blog.csdn.net/article/details/130033301 【2】https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html#mysql-shell-utilities-load-dump-opt-control
