Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
MySQL / MariaDB single shell command line to: 1- Dump DB from remote source server via SSH; 2- Compress dump with multithread bzip2; 3- Stream dump to local; 4- Uncompress dump; 5- Pipe through pv with a remote query to guess dump size to show progress bar; 6- Import dump to local DB
ssh USER@REMOTE_SOURCE_SERVER 'mysqldump -uUSER -pPASSWORD --databases MYDATABASE | lbzip2' | lbunzip2 | pv -s `ssh USER@REMOTE_SOURCE_SERVER 'mysql -uUSER -pPASSWORD -e "SELECT ROUND(((SUM(DATA_LENGTH)*2)+SUM(INDEX_LENGTH))/3) B FROM information_schema.tables WHERE table_schema = \"MYDATABASE\";"| tail -n1'` |mysql -uUSER -pPASSWORD

This comment has been minimized.

Copy link
Owner Author

EthraZa commented Aug 15, 2017

Depending on DB data content, the query for guess dump size may get it way wrong. You may tweek it for your data or remove it within the -s switch to get just a moving bar to show data passing by.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.