Skip to content

Instantly share code, notes, and snippets.

@stephen-dahl
Last active April 3, 2020 17:46
Show Gist options
  • Save stephen-dahl/d902746df5b1828760e9ff3dc8384123 to your computer and use it in GitHub Desktop.
Save stephen-dahl/d902746df5b1828760e9ff3dc8384123 to your computer and use it in GitHub Desktop.
copy data from one mysql server to another in parallel
#!bash
mysql_config_editor set --login-path=source -hmysql.source.com -uuser -p
mysql_config_editor set --login-path=destination -hmysql.destination.com -uuser -ppass
export ignoreSchema="'information_schema','mysql','performance_schema', 'awsdms_control'"
#get db list
mysql --login-path=source -ANe"SELECT distinct table_schema FROM information_schema.tables WHERE table_schema NOT IN (${ignoreSchema})" > dbs
#get table list
mysql --login-path=source -ANe"SELECT concat(table_schema, '/', table_name) FROM information_schema.tables WHERE table_schema NOT IN (${ignoreSchema})" > tables
#dump
mkdir schema
mkdir data
parallel --joblog job.log --bar 'mysqldump --login-path=source --add-drop-database --add-drop-table --no-data -B {} > schema/{}.sql 2>>mysql.log' :::: dbs
parallel --joblog job.log --bar 'mysqldump --login-path=source -CK --hex-blob --no-create-info {//} {/} > data/{//}.{/}.sql 2>>mysql.log' :::: tables
#load
parallel --joblog job.log --bar 'mysql --login-path=source -AC < schema/{}.sql &>> mysql.log' :::: dbs
parallel --joblog job.log --bar 'mysql --login-path=source -AC {//} < data/{//}.{/}.sql &>> mysql.log' :::: tables
# add --dry-run to any parallel command to see to see the list of commands it will run
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment