Categories
Cloud Management and Projects Sotfware & Developers & DevOps Tools & How-Tos

MySQL master-slave replication with docker

a computer that is running mysql database for replication
MySQL Master Slave DB Replication with Docker and Docker-Compose

This post “MySQL master-slave replication with docker” was updated by: Syed Umar Bukhari on August 26, 2021

MySQL replication: a process to enable automatic copying of database(s) from one instance of MySQL to the other. In this case, we will look at master-slave replication– the most popular way to replicate SQL databases, specifically MySQL. We can create multiple slave servers for replication with a single master server. In this post, we use docker compose to create the replication on MySQL. Additionally, the code part of the post uses docker and docker-compose for MySQL master-slave replication The host OS runs the Ubuntu 18.04 OS with docker and docker compose for this experiment. You can use Windows, CentOS, Mac OS, etc. for your experiment as docker abstracts you from most of the host OS. Before beginning the process, it is assumed you have already installed docker and docker compose on your machine. If you haven’t, please do so before proceeding.

On a side note, let’s see a few reasons why MySQL is extremely popular as a database system. It is because it’s open source, has a lot of support from the community and the big tech companies. Similarly, by using SQL commands, MySQL can create, run, and query the database as well as build websites, application, it is quite efficient to use for the database designers. In addition, one of its most common uses is to build a wordpress (WP) websites.

By following this post you should be able to make your own database (DB) system much more resilient. Finally, if you want to take something out of why MySQL is important then know this— it lowers the learning curve, it’s free and it can scale to huge systems with billions of rows.

Create docker compose file

The docker compose file makes it easy for us to set some variables in the container, making the environment able to connect with the container. In addition, we use docker compose to run a multi-container environment based on the definitions in a YML file. After that, let’s create a docker compose file for replication as shown below:

version: '3'
services:
  mysql-master:
    image: percona:ps-8.0
    container_name: mysql-master
    restart: unless-stopped
    env_file: ./master/.env.master
    cap_add:
      - all
    volumes:
      - ./master/data:/var/lib/mysql
      - ./master/my.cnf:/etc/my.cnf
    environment:
      - TZ:${TZ}
      - MYSQL_USER:${MYSQL_USER}
      - MYSQL_PASSWORD:${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD:${MYSQL_PASSWORD}
    networks:
      default:
        aliases:
          - mysql

  mysql-slave:
    image: percona:ps-8.0
    container_name: mysql-slave
    restart: unless-stopped
    env_file: ./slave/.env.slave
    cap_add:
      - all
    volumes:
      - ./slave/data:/var/lib/mysql
      - ./slave/my.cnf:/etc/my.cnf
    environment:
      - TZ:${TZ}
      - MYSQL_USER:${MYSQL_USER}
      - MYSQL_PASSWORD:${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD:${MYSQL_ROOT_PASSWORD}
    networks:
      default:
        aliases:
          - mysql

In the docker compose file above, we use a different environment; in the MySQL master container we use an ENV file with the name .env.master. To differentiate, let’s create two folders: a master and slave folder– to separate the configuration for each container. Create folders using the command “mkdir”.

mkdir master && mkdir slave
crate master and slave directory

We have created a new folder to separate the master and slave files. We will create 2 new files next: .env.master. and .env.slave to use later.

touch master/.env.master && touch slave/.env.slave
two files env master and env slave created

Configuring the ENV file for MySQL master-slave replication with docker

This env file contains variables crucial for the container’s creation in docker compose. Additionally, the file makes it easy for us to store some information. We will create two env files for master and slave respectively. We edit the env file using “vi” command; you can use any text editor on Linux or Windows– such as Visual Studio Code or Atom.

vi master/.env.master
### WORKSPACE #############################################
TZ=UTC

#MYSQL_DATABASE=master
MYSQL_USER=master
[email protected]
MYSQL_PORT=3306
MYSQL_ROOT_PASSWORD=Mastermaster123

Create env dot slave file for slave server.

vi slave/.env.slave
### WORKSPACE #############################################
TZ=UTC

#MYSQL_DATABASE=slave
MYSQL_USER=slave
[email protected]
MYSQL_PORT=3306
MYSQL_ROOT_PASSWORD=slaveslave123

Below is an explanation for some of the variables contained in the env file to help us understand what role they perform.

Let’s look at the variables and their uses:


TZ is the time zone that will apply to the container.
MYSQL_DATABASE is the name of the database that will be created by itself.
MYSQL_USER is how the use enters into the database we create
MYSQL_PASSWORD is the password of the user that has been created. Creating a strong password is safe.
MYSQL_PORT is the port that runs the MySQL server.
MYSQL_ROOT_PASSWORD is the root user info to access all MySQL databases; create a password made of letters and symbols to be safe.

Create my.cnf file for master database.

[mysqladmin]
user=master
[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mysql
port=3306
tmpdir=/opt/bitnami/mysql/tmp
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
max_allowed_packet=16M
bind_address=0.0.0.0
log_error=/opt/bitnami/mysql/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mysql/lib/plugin
server-id=1
binlog_format=ROW
log-bin

[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mysql/lib/plugin

[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
!include /opt/bitnami/mysql/conf/bitnami/my_custom.cnf

Make a my.cnf file for slave server as well.

[mysqladmin]
user=master

[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mysql
port=3306
tmpdir=/opt/bitnami/mysql/tmp
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
max_allowed_packet=16M
bind_address=0.0.0.0
log_error=/opt/bitnami/mysql/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mysql/lib/plugin
server-id=2
binlog_format=ROW
log-bin

[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mysql/lib/plugin

[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
!include /opt/bitnami/mysql/conf/bitnami/my_custom.cnf
tree docker compose to understand the usage

From the picture above, let’s understand the usage of the master and slave folders and their files.


Data folder stores all the data files inside the container in the host.
My.cnf file makes configurations easier on MySQL.

Building a container for MySQL master-slave replication with Docker

Let’s build the MySQL master and slave containers with the docker compose config settings. Make sure you have everything ready to build container using the command “docker-compose up -d”.

docker-compose up -d
docker compose up building container for replication

Wait for the process of building the container to be done successfully. After that, check the process with the command “docker-compose ps”.

docker-compose ps
docker compose process check status mysql

Replication of MySQL master-slave with Docker

Now that the container runs properly, let’s begin the replication process.

Enter the container with “docker-compose exec container bash”. This will configure the replication with the MySQL command.

docker-compose exec mysql-master bash
docker compose exec command master slave bash replication start

Let’s login using the root user that we made above on MySQL now.

mysql -u root -p
login to the master slave replication server mysql

Create a user on MySQL for more replications.

mysql> CREATE USER 'replication'@'%' IDENTIFIED WITH mysql_native_password BY 'Slaverepl123';
creating a new user mysql

Grant user replication access to allow creation for MySQL replications.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
granting user replication mysql

Now, we examine if replication executed successfully or not.

mysql> show grants for [email protected]'%';
see the log status of users mysql

We will know the log status on master from this.

After that, we see the binary log of MySQL master with the following command:

mysql> SHOW MASTER STATUS\G
see the status of master server mysql

After that, the configuration on the master is complete and we continue to make the configuration on the slave. Login to container using “docker-compose exec” command.

docker-compose exec mysql-slave bash
logging in tto the containter mysql docker

After that, enter the MySQL slave server to run the following MySQL command.

mysql -u root -p
MySQL login

Execute this SQL command to push the MySQl slave part to join the master.

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='replication',
MASTER_PASSWORD='Slaverepl123',
MASTER_LOG_FILE='87e8982d00d1-bin.000004',
MASTER_LOG_POS=349;

The command to join to master from slave has successfully executed.

mysql change master

Let’s start the slave on mysql.

START SLAVE;
start slave mysql

After completion of all steps, recheck your work to ensure nothing was missed. Subsequently, check the status of replication on the slave server.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql-master
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 87e8982d00d1-bin.000005
          Read_Master_Log_Pos: 156
               Relay_Log_File: ba7af6f52d85-relay-bin.000002
                Relay_Log_Pos: 331
        Relay_Master_Log_File: 87e8982d00d1-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 547
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 5166800b-f068-11eb-abf5-0242ac150002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

Make sure all databases are running smoothly, check on MySQL master.

show database master ensure mysql database db is working

If the master database is working fine, check on the slave.

mysql slave show database

After that, we will test to create a database on the master.

Therefore, let’s create a database to test if replication is working properly.

mysql> create database replicate_db;
create database replicate test

Let’s check on the slave whether the database created on the master can be created automatically on the slave.

mysql> show databases;
check slave database show

MySQL master slave replication on the docker machine is now successfully set up.

Conclusion

In this post, we have finished configuring MySQL master slave. Master slave on MySQL can be used in production and building applications. However, we must be careful when setting up replication in production. In the case you need to add slave servers, you need to manually configure it. This allows you to horizontally scale the readers. Moreover, you can backup MySQL into file storage; Sesamedisk provides storage for business and personal needs— data encryption and point in time recovery.

Additionally, you can find the docker config on github.

If you like this article, please add our blog to your bookmarks. We have lots of tech articles for you to study and understand. Lastly, I would encourage you to read this post about webrtc jitsi.

Update by: Syed Umar Bukhari on August 26, 2021

Hits: 205

Categories
Cloud Sotfware & Developers & DevOps Tools & How-Tos

How to Host WordPress Using Docker-Compose?

WordPress (WP) is a free-open-source website creation platform. It is the most popular Content Management System due to its simplicity and effectiveness. Check out our article on how to host WordPress from scratch. However, setting up a new environment every time is a bit tedious and time taking if you need to do it often. In this article, we’ll showWordPress Using Docker-Compose in a container. WP is made using PHP, MySQL and of course JavaScript and HTML.

Why WordPress Using Docker-Compose in a Container?

 A container is a standard unit of software that bundles up code and all the dependencies so the application can run in the intended setup. Some of the benefits are, reusability, flexibility, less resource consumption, robustness, isolation. Container tools like Docker, Podman, Rocket are in huge demand and exponentially increasing day by day. Checkout our interesting article on hosting Jitsi on Docker.

So we got an approach to run the same workloads with more efficiency. But the question of setting up the system again and again from scratch remains unsolved.

Docker-compose seems a good option. Basically, Docker compose is a tool for defining and running containers in a unified manner. Specifically, with compose, we can use the YAML file to configure the application service. Use the declarative approach and mirror user-defined requirements and deploy containers with a single simple command.

WordPress Using Docker Compose.
WordPress (WP) on Docker

Demonstration:

Here, we’ve taken Ubuntu Linux as a Docker host and it is running on AWS cloud with root powers. On this host OS, we can run multiple different containers with different OS and packages. Containers are isolated yet use the resources from the host system.

Step 1: Installation of Docker on host system:

We have to install the Docker package to run the Docker daemon and run compose files. Use the below command for installation:

To update package manage: apt-get update
To install Docker:  curl -fsSL https://get.docker.com/ | sh
To start services: systemctl start docker
Docker installation
Docker installation

There are multiple ways to install Docker.
The systemctl command will start the Docker service, allowing us to utilize container operations such as creating, deleting containers, managing container networks, attaching volume, and many more.
Verify the running service with the systemctl status docker command.

Step:2 : Installation of Docker-compose

After installing and configuring the Docker service, now is the time to set up docker-compose and enable its capabilities. Using compose, we can simply declare the container blueprint and provision containers with the same program file. It gives the benefit of reusability with a simple way of use.
Note: Install curl command-line utility in the system if not present: apt-get install curl) Check out our article on curl for more information.

Install compose using this command: curl -L "https://github.com/docker/compose/releases/download/1.29.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose

Alter the execution permissions: chmod +x /usr/local/bin/docker-compose

Verify the compose installation using: docker-compose –v command
Docker-compose installation
Docker-Compose installation

Finally, done with the one-time setup of the server. Now we’ve docker service and composer running in the server and are ready to start. Although, we can launch containers without compose. But for large-scale infrastructure, it is recommended to use such an “IaC” approach.

There is a concept of Docker image. Basically, an image is kind of a blueprint or base of a container. It is the essential unit to launch a container. We store and maintain these images in repositories. Docker hub is a public repository for docker images. We can find frequently used and custom-created docker images here.

Here, we’ve taken readily available images for WordPress (WP) and MariaDB. We’ve used MariaDB as a database to persistently store the content of the website. Using composer, we’ll link both the front-end and backend and create a working website.

Step 3: Creating composer file for WP
mkdir wordpress 
cd wordpress
vim docker-compose.yml 

Naming the composer file as above is a standard convention. Alternatively, we can explicitly pass the compose filename with the -f flag. Good practice to implement compose files in separate directories. Indentation is compulsory in YAML

wordpress:
  image: wordpress
  links:
    - mariadb:mysql
  environment:
    - WORDPRESS_DB_PASSWORD = your password
    - WORDPRESS_DB_USER= root
    - WORDPRESS_DB_NAME= db name
  ports: '-8081:80'
mariadb:
  image: mariadb
  environment:
    - MYSQL_ROOT_PASSWORD = your password
    - MYSQL_DATABASE = db name

Here, we have declared two containers named WordPress and MySQL in the flavor of MariaDB respectively. The image tag refers to the base image for containers. WordPress and MariaDB are the image names. The links tag implicitly represents the link of the mentioned container. The environment keyword is used to pass environmental variables (also known as env vars in OS development jargon) while launching the container to fulfil the dependencies. The ports keyword is used to expose the container to the outer world. Here we’ve taken post 8081 of host os and bind to port 80 of the WordPress Using Docker Compose. So any client coming to port 8081 will be redirected to container port 80. In addition, make sure the password in the environmental variable is correct in both containers.

Now the composer file is ready. Create the whole setup with the command below:

Docker-compose up –d
docker compose command execution
docker-compose command execution

The process is successful if the above command doesn’t return any error. Cross-check the same by hitting the site at public_ip:8081. Complete the installation process and the end page would look like this:

wordpress dashboard
WP dashboard

Hence, the installation is done and linked with the backend database. This is how containers help to provision lightweight os. And compose makes it even more feasible by bundling multiple tasks in a file. That is WordPress Using Docker Compose. Thanks for reading.!



Hits: 42