One of the most valuable things I learned in my ‘my-server-has-run-out-of-memory’ crisis was that I can dump my database directly to my local machine without having to first save it to the server and then download it from there. It is surprising that it took me well over 5 years to find it but what a gem for every person like me to have in the toolbox.
Credits from this link
Before beginning this process, make sure that you read through this and have enough time to perform this.
Direct method to dump mysql data from remote server to your local computer is going to be with this ‘magic command’:
ssh root@ipaddress "mysqldump -u dbuser -p dbname | gzip -9" > dblocal.sql.gz
- On local machine in your ubuntu terminal window,
cdto a good backup location - In a text editor first (to prevent running an accidental command with painful results), copy / paste the magic command above.
- In your editor, edit the command replacing the elements with your own: user, ipaddress (with your server’s ssh credentials) and dbuser, dbname (with your MYSQL credentials) and name ‘dblocal’ filename to whatever you want – this will be name of your final backup file on your local machine
- After you are confident the command is correct and good, paste it back into your terminal
- Hit enter – you should be first asked for credentials to log into your server by ssh (unless you already have a key setup and it happens ‘automatically’). Enter your credentials.
- Then, you should be asked for password like this
Enter password:which is the request for your mysql pw on the remote server. - Enter that password and press enter.
At this point, it will begin working with absolutely no progress status updates so you’ll just have to hang out, make some popcorn, watch the latest trending memes and let it finish – however long that takes. In my case, when my db was bloated prior to pruning it, it was 2.8 gigs and took over one hour to download and that was with a solid internet connection.
May this reduce someone’s crisis by one level.
