Install a MySQL with docker

This post shows how to use MySQL with docker: install, query, setup PHPMyAdmin, scheduling backup (to AWS S3)

• To install

(To support PHPMyAdmin server) First, create $HOME/mysql.cnf with following content

[mysqld]
default-authentication-plugin=mysql_native_password

Then start the MySQL server with

docker container stop mysql; docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<password> -v $HOME/mysql:/var/lib/mysql -v $HOME/mysql.cnf:/etc/mysql/conf.d/mysql.cnf --rm -it mysql

Replace <password> with your password

• To make query

docker exec -it mysql sh -c "exec mysql -u root -p"

then enter your password to authenticate

• Setup PHPMyAdmin

docker stop phpmyadmin; docker run -d --rm --name phpmyadmin --link mysql:db -p 8080:80 -e MYSQL_ROOT_PASSWORD=<password> -e PMA_ABSOLUTE_URI=https://myadmin.transang.me phpmyadmin/phpmyadmin

Replace <password> with your password

• Scheduling backup

Create $HOME/sql-backup.sh with following content

#/bin/sh
docker exec mysql sh -c 'exec mysqldump --all-databases -uroot -p<password>' > $HOME/dump.sql && \
	cd $HOME && \
	tar Jcf dump.sql.tar.xz dump.sql && \
	env AWS_ACCESS_KEY_ID=<aws access key> AWS_SECRET_ACCESS_KEY=<aws secret access key> /usr/local/bin/aws --region ap-northeast-1 s3 cp $HOME/dump.sql.tar.xz s3://carstay-db/transang.sql.tar.xz || \
	curl -X POST -H 'Content-type: application/json' --data '{"text":"Can not backup mysql db"}' <slack webhook url>

Replace <password>, <aws access key>, <aws secret access key>, <slack webhook url> with appropriate values

Add entry to crontab -e

0 */1 * * * /bin/bash /root/sql-backup.sh

to run the backup script every hour

• To restart from dump file: (will be added later)