より高速な mysqldump とリストアを求めて

$ mysqldump -uroot -p -h127.0.0.1 -P 3306 --quick --single-transaction laravel | mysql -uroot -p -h127.0.0.1 -P 3306 laravel2

# gzip で圧縮する(ファイルにするので別のインスタンスにファイルで簡単に転送出来る)
$ mysqldump -uroot -p -h127.0.0.1 -P 3306 laravel | gzip > laravel.sql.gz
# 例えば別のインスタンスにファイルを持ってきて
$ zcat laravel.sql.gz | mysql -uroot -p -h127.0.0.1 -P 3306 laravel

$ which mysqlsh
/usr/bin/mysqlsh
$ mysqlsh --version
mysqlsh   Ver 8.0.36 for Linux on x86_64 - for MySQL 8.0.36 (Source distribution)

$ DB_NAME=laravel
$ TMP_DIR_PATH=$(mktemp -d)
$ DB_DSN=mysql://root:root@127.0.0.1:3306
$ mysqlsh ${DB_DSN} -- util dump-schemas ${DB_NAME} --output-url=${TMP_DIR_PATH} --threads=$(nproc) --defaultCharacterSet="utf8mb4" --tzUtc=false
Acquiring global read lock
Global read lock acquired
Initializing - done 
1 schemas will be dumped and within them 950 tables, 30 views.
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 8 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
101% (2.99M rows / ~2.95M rows), 346.95K rows/s, 22.83 MB/s uncompressed, 1.16 MB/s compressed                    
Dump duration: 00:00:13s                                                                      
Total duration: 00:00:14s                                                                     
Schemas dumped: 1                                                                             
Tables dumped: 950                                                                            
Uncompressed data size: 198.66 MB                                                             
Compressed data size: 10.05 MB                                                                
Compression ratio: 19.8                                                                       
Rows written: 2987248                                                                         
Bytes written: 10.05 MB                                                                       
Average uncompressed throughput: 14.66 MB/s                                                   
Average compressed throughput: 741.84 KB/s
$ cd ${TMP_DIR_PATH}
$ tar -Izstd -cf laravel.tar.zst .   

SET GLOBAL local_infile=1;

$ ZSTD_FILE=/home/myuser/laravel.tar.zst
$ TMP_DIR_PATH=$(mktemp -d)
$ cd ${TMP_DIR_PATH}
$ tar -Izstd -zf ${ZSTD_FILE}
$ DB_NAME=laravel
$ DB_DSN=mysql://root:root@127.0.0.1:3306
$ mysqlsh ${DB_DSN} -- util load-dump ${TMP_DIR_PATH} --threads=$(nproc) --analyzeTables="on" --characterSet="utf8mb4" --schema=${DB_NAME} --ignoreVersion=true
Loading DDL and Data from '/tmp/xxxxxx' using 8 threads.
Opening dump...
Target is MySQL 5.7.42-log. Dump was produced from MySQL 8.0.43
WARNING: Destination MySQL version is older than the one where the dump was created. Loading dumps from different major MySQL versions is not fully supported and may not work. The 'ignoreVersion' option is enabled, so loading anyway.
Scanning metadata - done       
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done           
Executing view DDL - done         
Starting data load
1 thds loading \ 100% (198.65 MB / 198.65 MB), 1.49 MB/s, 961 / 961 tables and partitions done 
Recreating indexes - done       
Executing common postamble SQL                                                                
Analyzing tables - done           
No data loaded.                                                                               
0 warnings were reported during the load.     

$ docker run --rm -it --net="host" mirror.gcr.io/mysql:8.4 sh
# DB_NAME=laravel
# DB_DSN_FROM=mysql://root:root@127.0.0.1:3308
# DB_DSN_TO=mysql://root:root@127.0.0.1:3306
# THREADS=$(nproc)
# cat <<EOL | mysqlsh ${DB_DSN_FROM} --js
util.copySchemas(["${DB_NAME}"], "${DB_DSN_TO}", {
    threads: ${THREADS},
    defaultCharacterSet: "utf8mb4",
    checksum: true,
    ignoreVersion: true,
    handleGrantErrors: "ignore",
    tzUtc: false,
    analyzeTables: "on",
    ignoreExistingObjects: true,
});
EOL