vlambda博客
学习文章列表

Usage examples:$ mysqlsh root@localhost/schema$ mysqlsh mysqlx://[email protected]:3307/world_x$ mysqlsh --uri root@localhost --py -f sample.py sample param$ mysqlsh root@targethost:33070 -s world_x -f sample.js$ mysqlsh -- util check-for-server-upgrade root@localhost --output-format=JSON$ mysqlsh mysqlx://user@host/db --import ~/products.json shop

mysqlsh -- util --help
Error importing module 'requests', check if it's installed (Python 3.9.5)The following operations are available at 'util':
 check-for-server-upgrade Performs series of tests on specified MySQL server to check if the upgrade process will succeed.
 dump-instance Dumps the whole database to files in the output directory.
 dump-schemas Dumps the specified schemas to the files in the output directory.
 dump-tables Dumps the specified tables or views from the given schema to the files in the target directory.
 export-table Exports the specified table to the data dump file.
 import-json Import JSON documents from file to collection or table in MySQL Server using X Protocol session.
 import-table Import table dump stored in files to target table using LOAD DATA LOCAL INFILE calls in parallel connections.
 load-dump Loads database dumps created by MySQL Shell.

shell-js> util.dumpTables("hr", [], "emp", { "all": true })

# shellmysqlsh root@127.0.0.1:3306 -- util dump-tables sbtest 'sbtest2,sbtest1' --output-url='/server/tmp/test' --threads=4 --consistent=false --dryRun=true
# python[root@localhost ~ ]$ mysqlsh root@127.0.0.1:3306 --py
MySQL 127.0.0.1:3306 ssl Py > util.dump_tables('sbtest',['sbtest1','sbtest2'],'/server/tmp/test',{'threads':4,'consistent':'false','dryRun':'true'})

Initializing - doneGathering information - doneWARNING: The dumped value of gtid_executed is not guaranteed to be consistentWriting global DDL filesRunning data dump using 4 threads.NOTE: Progress information uses estimated values and may not be accurate.Writing schema metadata - doneWriting DDL - doneWriting table metadata - doneStarting data dump101% (2.00M rows / ~1.97M rows), 1.28M rows/s, 227.01 MB/s uncompressed, 103.25 MB/s compressedDump duration: 00:00:01sTotal duration: 00:00:01sSchemas dumped: 1Tables dumped: 2Uncompressed data size: 387.78 MBCompressed data size: 176.31 MBCompression ratio: 2.2Rows written: 2000000Bytes written: 176.31 MBAverage uncompressed throughput: 236.72 MB/sAverage compressed throughput: 107.63 MB/s

# shellmysqlsh root@127.0.0.1:3306 -- util dump-schemas 'sbtest,dba' --output-url='/server/tmp/test' --threads=4 --consistent=false --dryRun=true
# pyutil.dump_schemas(['sbtest','dba'],'/server/tmp/test',{'threads':4,'consistent':'false','dryRun':'true'})

Initializing - doneGathering information - doneWARNING: The dumped value of gtid_executed is not guaranteed to be consistentWriting global DDL filesRunning data dump using 4 threads.NOTE: Progress information uses estimated values and may not be accurate.Writing schema metadata - doneWriting DDL - doneWriting table metadata - doneStarting data dump100% (139.49M rows / ~138.29M rows), 641.85K rows/s, 158.22 MB/s uncompressed, 19.07 MB/s compressedDump duration: 00:01:31sTotal duration: 00:01:31sSchemas dumped: 2Tables dumped: 23Uncompressed data size: 15.88 GBCompressed data size: 4.02 GBCompression ratio: 4.0Rows written: 139489767Bytes written: 4.02 GBAverage uncompressed throughput: 174.16 MB/sAverage compressed throughput: 44.08 MB/s

# shellmysqlsh root@127.0.0.1:3306 -- util dump-instance '/server/tmp/test' --threads=4 --consistent=false --dryRun=true
# pyutil.dump_instance('/server/tmp/test',{'threads':4,'consistent':'false','dryRun':'true'})

# shellmysqlsh root@127.0.0.1:3306 -- util load-dump '/server/tmp/test' --analyzeTables=on --skipBinlog=true --updateGtidSet=off --dryRun=true
# pyutil.load_dump('/server/tmp/test',{'threads':4,'analyzeTables':'on','skipBinlog':'true','updateGtidSet':'off','dryRun':'false'})

Loading DDL and Data from '/server/tmp/test' using 4 threads.Opening dump...Target is MySQL 8.0.25. Dump was produced from MySQL 8.0.25Scanning metadata - doneChecking for pre-existing objects...Executing common preamble SQLExecuting DDL - doneExecuting view DDL - doneStarting data load2 thds loading / 100% (15.88 GB / 15.88 GB), 29.03 MB/s, 23 / 23 tables doneRecreating indexes - doneAnalyzing tables - doneExecuting common postamble SQL241 chunks (139.49M rows, 15.88 GB) for 23 tables in 2 schemas were loaded in 5 min 45 sec (avg throughput 46.00 MB/s)0 warnings were reported during the load.

# shellmysqlsh root@127.0.0.1:3306 -- util load-dump '/server/tmp/test2' --analyzeTables=on --skipBinlog=true --updateGtidSet=off --schema=dba --dryRun=false
# pyutil.load_dump('/server/tmp/test2',{'threads':4,'analyzeTables':'on','skipBinlog':'true','updateGtidSet':'off','schema':'dba','dryRun':'true'})

Loading DDL and Data from '/server/tmp/test2' using 4 threads.Opening dump...Target is MySQL 8.0.25. Dump was produced from MySQL 8.0.25Scanning metadata - doneChecking for pre-existing objects...Executing common preamble SQLExecuting DDL - doneExecuting view DDL - doneStarting data load2 thds loading / 100% (387.78 MB / 387.78 MB), 37.78 MB/s, 2 / 2 tables doneExecuting common postamble SQLRecreating indexes - doneAnalyzing tables - done8 chunks (2.00M rows, 387.78 MB) for 2 tables in 1 schemas were loaded in 9 sec (avg throughput 44.27 MB/s)0 warnings were reported during the load.