Easy access to a dockerized mysql server
Deploying a new mysql server using Docker is quite easy:
docker run --name mysql mariadb
But once the server is up, you want root access to it from your command-line.
The mariadb repo shows an example
of how to do this. I’ve wrapped their command in a little shell function that
you can add to your .bashrc or .zshrc. If you call the function without
arguments, it will drop you on the mysql prompt. If you give a mysql command as
argument, it will execute it as mysql root and show the result.
This file contains hidden or 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
| # Use `mysqlroot` to go to interactive mysql shell, | |
| # type e.g. `mysqlroot "SHOW DATABASES" to directly execute query | |
| # or type `mysqlroot my-script.sql` to execute script | |
| function mysqlroot() { | |
| if [ $# -eq 0 ]; then | |
| docker run -it --link mysql_mysql_1:mysql --rm mariadb sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"' | |
| else | |
| if [ -f $1 ]; then | |
| cat $1 | docker run -i --link mysql_mysql_1:mysql --rm mariadb sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"' | |
| else | |
| echo $1 | docker run -i --link mysql_mysql_1:mysql --rm mariadb sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"' | |
| fi | |
| fi | |
| } |
Here’s how it works:
# mysqlroot "SHOW DATABASES"
Database
my_db_1
my_db_2
# mysqlroot
MariaDB [(none)]>
Bonus round: automated backup
This file contains hidden or 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
| # Logrotate backup instructions for a Dockerized mysql server. | |
| # This will automatically dump all your databases once a day | |
| # and maintain a compressed one week backlog. | |
| # Write this config to /etc/logrotate.d/mysql-backup | |
| /var/backups/db.sql.gz { | |
| daily | |
| rotate 8 | |
| nocompress | |
| create 640 root adm | |
| postrotate | |
| docker run -i --link mysql:mysql --rm mariadb sh -c 'exec mysqldump -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD" --all-databases --events' > /var/backups/db.sql | |
| gzip -9f /var/backups/db.sql | |
| endscript | |
| } |