The alias way to backup mysql database from command line


Dev-Bookmarks Logo

Save up to a workweek a year by efficiently managing your dev bookmarks, on www.bookmarks.dev. Share your favorites with the community and they will be published 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

Adrian Matei

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

Lots of exciting dev bookmarks added in march 2019

Lots of exciting dev #bookmarks added in march 2019. Keywords: alias, api, api-management, architecture, arquillian, async, asynchronous, awesome, awesome-list, aws, bash, bootstrap, c, chai, cheatsheet, circuit-breaker, css, devops, diagnostics, diagram, dns, docker, docker-compose, docs, documentation, domain-driven-design, dotfiles, email, error-handling, facebook, favicon, ffmpeg, free-programming-books, functional-programming, gif, git, gradle, graph, helm, homebrew, html, html5, immutable.js, install, integration-testing, istio, java, javascript, jenv, junit, jvm, kotlin, kubernetes, kubernetes-helm, latex, linux, macos, markdown, maven-plugin, microservices, middleware, mocha, mongodb, monitoring, nginx, nodejs, oidc, online-tools, open-source, openid-connect, osx, package-manager, pandoc, performance, podcast, promise, proxy, r, reactive, reactive-programming, reactjs, redis, resources, rest, rxjs, security, shell, spring, spring-boot, spring-cloud, spring-cloud-gateway, spring-security, spring-webflux, sql, testing, tools, user-experience, video-processing, vim, visualization, web, websocket, windows and zuul Continue reading