docker
Docker and Database Replication
- By
- On 04/10/2020
- Comments (0)
- In Containers
MySQL Replication DB
DB Replication can be done for various reasons sometimes to ensure we have a backup of the data directly from our database, to run analytics or check the data without disturbing the main database or Scaling out the DB for better performance.
In this post I will show you how to set up a master/slave MySQL configuration in production and developpement mode (through docker):
Production mode:
For production mode you need just install a mysql server and client ( here are the instruction to install a MySQL and how to use it within a localhost : For linux-UBUNTU):
$> sudo apt-get install mysql-server mysql-client
Then you can check the status of mysql service:
$>sudo service mysql status
After that as a root:
$>sudo mysql -u root -p
Optional: you can change a mysql User password as follow:
$ mysql> ALTER USER 'userName'@'localhost' IDENTIFIED BY 'New-Password-Here';
- Master DB Configuration:
$> sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf
we have to enable the following variables:
bind-address : server_ip_address
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 864000 # which corresponds to 10 days
max_binlog_size = 100M
binlog_do_db = mydb_mhi
after that restart mysql service: $> sudo service mysql restart
We need to grant some privileges to the Slave DB so that it can access and replicate the data from the DB we mentioned in the config file, "mydb". We will execute the following commands to grant the privileges to the Slave DB: so from the mysql console:
$ mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'server_ip';
Next we flush the privileges: $ mysql> FLUSH PRIVILEGES;
Now we go back to the mysql prompt and we create the DB that we want to replicate and add simple table (TABLE1) in it and lock it after that:
$mysql> CREATE SCHEMA mydb_mhi;
Query OK, 1 row affected (0.00 sec)
$mysql> USE mydb_mhi;
Database changed
$mysql>CREATE TABLE TABLE1 (id INTEGER not NULL, first VARCHAR(255), PRIMARY KEY ( id ))
Query OK, 0 rows affected (0.02 sec)
$mysql> INSERT INTO TABLE1 VALUES('1','MrAlpha');
Query OK, 1 row affected (0.01 sec)
$mysql> show master status;
Last and not least, we apply a read lock on the db and we go back to a new terminal session and we take a snapshot dump of the our DB:
$mysql>FLUSH TABLES WITH READ LOCK;
$> sudo mysqldump -u root -p --opt mydb_mhi > mydb_mhi.sql
Finally, we go back to the mysql prompt and we unlock the tables and we quit:
$mysql>UNLOCK TABLES;
$mysql>QUIT;
-SLAVE Configuration
Now for the slave configuration; it is straight forward, after installing MySQL server/client: we create the DB "mydb_mhi" that we want to replicate:
$mysql> CREATE DATABASE mydb_mhi;
$mysql>QUIT;
Next we import the DB content from the file to the current one:
$mysql>mysql -u root -p mydb_mhi < /tmp/mydb_mhi.sql
Then, we configure mysql slave server as following:
$> sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf
server-id= 2
relay-log= /var/log/mysql/mysql-relay-bin.log
log_bin= /var/log/mysql/mysql-bin.log
binlog_do_db=mydb_mhi
Next, we restart the mysql service: $>sudo service mysql restart
We log to the mysql prompt and we enable replication: (and thanks to the master status that we recovered earlier) we fire the following command:
$mysql>CHANGE MASTER TO MASTER_HOST='<master-server-ip>',MASTER_USER='root', MASTER_PASSWORD='myRootPsswd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 156;
$mysql>START SLAVE;
And when you can show the status using the following command:
$mysql>SHOW STATUS;
Waiting for mysql_master database connection...
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.0.2
Master_User: mydb_slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 600
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
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: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Dev Mode with Docker:
First you need to install docker and docker-compose:
$> sudo apt-get install docker
$> sudo apt-get install docker-compose
Then through docker you can pull the latest mysql image:
$> sudo docker pull mysql
and since we would like to check the status of our database through phpMyadmin client we are going to pull the latest version phpmyadmin image:
$>sudo docker pull phpmyadmin/phpmyadmin
Now that your environment is ready download the zip folder enclosed and run ./build.sh as root: (mysql-master-slave.zip (54.42 Ko))
$>sudo ./build.sh
This bash will initiate two instances of mysql container upon the ports 4406 and 5506 so it these ports are busy then you can change them in the docker-compose.yml file
in order to check the availibility of a port run the following command:
$>sudo lsof -i:PORT_NUMBER
After That: check the docker network config, so you can bind your phpmyadmin container to the right db:
$>sudo docker network ls
as you can see the network name that is used by our containers name is "mysql-master-slave_overlay"
then, to run and bind the phpmyadmin container to mysql containers we run the following command:
$>sudo docker run --network mysql-master-slave_overlay --name myadmin -d --link mysql_master:db -p 8081:80 phpmyadmin/phpmyadmin
Next you can go to: http://localhost:8081/ :---> to login use "root" and as password use "111" which the master db password
Now we can check the functionning of our configuration:
$>sudo docker ps
$>sudo docker exec -it mysql_master bash
##>mysql -u root -p'111' mydb
mysql> create table if not exists test(code int);
# Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> insert into testvalues (21), (44);
with the same manner you can check whether these values are updated in the slave db
$>sudo docker exec -it mysql_slave bash
mysql -u root -p'111' mydb
mysql> select * from test;