How to have MySQL backup with Docker?

Dockerized Ruby Backup Gem

I created a dockerized image for ruby gem backup/backup It was unfortunately older ruby where it worked flawlessly. Image can be found here varunbatrait/backup

If you are coming from Ruby, backup is a popular ruby gem which can be used to backup the database. Unfortunately it is maintenance only but it works flawlessly with current features. Since, it is not compatible with latest ruby, it makes sense to make a docker image of it.

Usage

I am assuming that you have configured the models inside models folder where you are right now. Then you can perform a trigger like this. Config can be an empty ruby file if you have everything mentioned in your model.

docker run --rm -v $(pwd):/usr/src/app  --user $(id -u):$(id -g) varunbatrait/backup:latest perform -t database_name --config-file config.rb --root-path . 

Alias

As usual, you can use alias and invoke it with just backup by using following:

alias backup='docker run --rm -it -v $(pwd):/usr/src/app  --user $(id -u):$(id -g) varunbatrait/backup:latest'

Provided you have added above alias to your .zshrc or .bashrc, You can generate a model – you can generate using:

backup generate:model --trigger=demo --config-file config.rb 

This will generate model file demo.rb under models/demo.rb

# encoding: utf-8

##
# Backup Generated: demo
# Once configured, you can run the backup with the following command:
#
# $ backup perform -t demo [-c <path_to_configuration_file>]
#
# For more information about Backup's components, see the documentation at:
# http://backup.github.io/backup
#
Model.new(:demo, 'Description for demo') do

end

For information related to usage of backup. Please visit official documentation

Worth Sharing?

How to make MySQL inside Docker Production Ready?

It is easy to simply spin a new MySQL and assume that it is ready for the production. This can’t be further from the preparation of disaster.

Few days back I wrote about having a infrastructure docker file to do your development and I got a comment was it ready for production. Today that ‘No’ is about to change to ‘Yes’.

When application grows, the table grows and we need more in-memory, more innodb instances and more threads. However, default MySQL configuration is not changed for last 8-10 years. Machine has got faster, MySQL default config didn’t catch up to that. Thus it is important to override configurations.

This is one example:

docker-compose.yml

version: '3.2'
networks:
  dual-localhost:
    driver: bridge
services:
  mysql:
    image: mysql:5.8
    restart: always
    volumes:
        - type: bind
          source: ./mysql
          target: /var/lib/mysql
        - type: bind
          source: /var/log/mysql/
          target: /var/log/mysql/
        - type: bind
          source: ./mysql.cnf
          target: /etc/mysql/mysql.conf.d/mysql.cnf
    environment:
        - MYSQL_ROOT_PASSWORD=some_weird_password
    networks:
      - dual-localhost
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 0.0.0.0
#skip-networking #insecure
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size		= 16M
max_allowed_packet	= 512M
thread_stack		= 192K
thread_cache_size       = 64
innodb_read_io_threads = 2
innodb_write_io_threads = 2
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 8M
tmp_table_size      = 32M
query_cache_size        = 32M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow-query-log=1
# slow-query-log-file=/var/log/mysql/mysql-slow.log

#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id		= 10
log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
binlog_row_image=minimal
max_binlog_size   = 256M
binlog_cache_size = 2M
binlog_rows_query_log_events = on

relay-log               = /var/log/mysql/mysql-relay-bin.log

innodb_log_file_size = 512M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
collation_server=utf8mb4_unicode_ci
character_set_server=utf8mb4

wait_timeout = 500
interactive_timeout = 28800

open_files_limit = 1024000
skip-name-resolve

join_buffer_size = 512K

innodb_buffer_pool_size = 3174M
innodb_buffer_pool_instances = 2
innodb_stats_persistent_sample_pages = 100
innodb_stats_transient_sample_pages = 24
innodb_rollback_on_timeout = on

Worth Sharing?

How to reset MySQL password in Ubuntu 18.04 LTS?

Unable to reset MySQL Password in Ubuntu 18.04 LTS?

First I want to discuss in which case the password will not change. I waste quite a lot of time setting a password which had “@” in between. For example “Password@123” – It simply didn’t work. Only when I removed “@” making “Password123” then it worked. So – If you are using @ in the password, please don’t think there is a problem with the approach, problem is with the password.

Stop the MySQL service

sudo service mysql stop

Create directories and set permissions

As soon as you stop the MySQL service, somehow, directories are deleted. You can re-create it as follows:

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

Run the MySQL without grant table

This will allow you to login without the need of input password.

sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
[1] 2613

Confirm that MySQL is running (OPTIONAL)

jobs
[1]+  Running     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Connect to MySQL

mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Flush Privileges

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Change Password

Assuming that your new password is “your_password_without_at_sign” here is how you can change it.

mysql> USE mysql; 
Database changed
mysql> UPDATE user SET authentication_string=PASSWORD("your_password_without_at_sign") WHERE User='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> UPDATE user SET plugin="mysql_native_password" WHERE User='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Now you have reset the password, you can quick mysql then kill the job running.

mysql> quit                                                                                                                                                                                    
Bye
sudo pkill mysqld                                                                                                                                                        
jobs                                                                                                                                                                     
[1]+  Done       sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking

Start MySQL in Normal Mode

sudo service mysql start
mysql -u root --password=your_password_without_at_sign                                                                                                                                 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql>

This is it! Let me know in comments if you find any problem.

Worth Sharing?