The alias way to backup mysql database from command line

Codever Logo

(P) Codever is an open source bookmarks and snippets manager for developers & co. See our How To guides to help you get started. Public bookmarks repos on Github ⭐🙏

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 --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 --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
$ mysqldump keycloak -u keycloak -p -h --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 ama@w.x.y.z -N'
alias mysql-backup-keycloak-prod='mysqldump keycloak -u keycloak -p -h --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.


Subscribe to our newsletter for more code resources and news

Adrian Matei (aka adixchen)

Adrian Matei (aka adixchen)
Life force expressing itself as a coding capable human being

routerLink with query params in Angular html template

routerLink with query params in Angular html template code snippet Continue reading