Created
August 15, 2017 18:09
-
-
Save EthraZa/102cef16f624668a1558038513620a87 to your computer and use it in GitHub Desktop.
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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.