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;