The alias way to backup mysql database from command line


Dev-Bookmarks Logo

(P) Bookmarks.dev - Open source Bookmarks and Code Snippets Manager for Developers & Co. See our How To guides to help you get started. Public Bookmarks Repo on Github - Star


As I have told you before, I am really hooked on bash aliases1. This blog entry emphasizes that point and presents how to make a MySql database backup via mysqldump with only three words, even with three letters, if you will.

So, without further ado, let’s see the alias:


$ alias mysql-backup-db_name='mysqldump db_name -u db_user -p -h 127.0.0.1 --port 3306 --single-transaction > path_to_back_up_directory/db_name_$(date "+%Y-%m-%d_%H:%M").sql'
$ mysql-backup-db_name

or, same with three letters:


$ alias mbd='mysqldump db_name -u db_user -p -h 127.0.0.1 --port 3306 --single-transaction > path_to_back_up_directory/db_name_$(date "+%Y-%m-%d_%H:%M").sql'
$ mbd

The option -u, will ask you for the password. The port number is not mandatory (as it defaults to 3306), but if you are using other port, you need to specify it.

I personally prefer the whole name approach, as then it is more clear to me. I can start typing mysql- and then there is auto complete. Besides that I can always alias-grep it2 - (alias alias-grep='alias | grep'), if I need to see how it looks like - using the alias in this case as sort of documentation…

Below there is a concrete example, where I backup the MySQL keycloak database:

$ mysql-backup-keycloak-prod
    aka
$ mysqldump keycloak -u keycloak -p -h 127.0.0.1 --port 3306 --single-transaction > ~/backup/db/keycloak_db_$(date "+%Y-%m-%d_%H:%M").sql

That will generate a .sql file in the specified path:

$ ls -lrt ~/backup/db
-rw-rw-r-- 1 ama ama 199219 Mar 10 07:04 keycloak_db_2017-03-10_07:04.sql
-rw-rw-r-- 1 ama ama 198489 Apr  3 06:28 keycloak_db_2017-04-03_06:27.sql

We can take things even further, and back-up a remote database from the local machine, after we build a ssh-tunnel3 with an alias of course:

alias mysql-tunnel-linode='ssh -L 127.0.0.1:3305:127.0.0.1:3306 ama@w.x.y.z -N'
alias mysql-backup-keycloak-prod='mysqldump keycloak -u keycloak -p -h 127.0.0.1 --port 3305 --single-transaction > ~/backup/db/keycloak_db_prod_$(date "+%Y-%m-%d_%H:%M").sql'

Same back-up command as before, only the port differs now and is pointing to the tunnel port - 3305.

With this I think I’ve made my point about the power and versatility of bash aliases.

References

Subscribe to our newsletter for more code resources and news

Adrian Matei

Adrian Matei
Life force expressing itself as a coding capable human being

How to filter results list in Angular with input text

Real life example showing how to filter results list in Angular and its implementation with Angular pipes. Continue reading

GraphQL Resources to help you get started

Published on November 05, 2020