Admin

7 years ago

How To Multiple Databases Replication On Ubuntu 16.04

Mysql database replication is very important issue for disaster recovery. So you can configure step by step multiple database replication on Ubuntu 16.04. I am ready to help some tutorial for you. Please make sure that, your server must be ssh enable without password authentication.

★ How To Install Mysql Server on Ubuntu 14.04

★ How To Install mysql Community on Windows 10

Install mysql server for both ubuntu 16.04 server.

root@Master-Serv:~# apt -y install mysql-server

Mysql master slave replication on ubuntu 16.04 prerequisite

Must be enable ssh authentication without password
Must be ufw and any firewall disable for two server
Must be comment out the below lines master and slave server

Master Server IP : 10.66.100.20
Slave Server IP : 10.66.100.21

root@Master-Serv:~#  vi /etc/mysql/mysql.conf.d/mysqld.cnf
root@Slave-Serv:~#  vi /etc/mysql/mysql.conf.d/mysqld.cnf

Must be comment below line.

#bind-address = 127.0.0.1
#skip-networking [If exit or not]

Master Server configuration on ubuntu 16.04

Step #01: Open my.cnf file then insert server id, relay log and log bin file location.

root@Master-Serv:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf

Insert below all lines

[mysqld]
server-id= 1
relay-log=/var/log/mysql/mysql-relay-bin.log
log-bin=/var/log/mysql/mysql-bin.log

now mysql restart command

root@Master-Serv:~# service mysql restart

Step #02: Create replication user with grant privileges.

root@Master-Serv:~# mysql -u root -p

Then type mysql root password

mysql> CREATE database test_db;
mysql> CREATE USER 'replica'@'%' IDENTIFIED BY 'passw0rd';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'replica'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000160 | 35117 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;

Step #03: Open another terminal for backup all master database.

root@Master-Serv:~# mysqldump -u root -p --all-databases --master-data > masterdb.sql

Copy masterdb.sql file into slave server

root@Master-Serv:~# scp masterdb.sql root@slave-server-ip:/root/

Slave Server configuration on ubuntu 16.04

root@Slave-Serv:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf

Insert below all lines

[mysqld]
server-id= 2
relay-log=/var/log/mysql/mysql-relay-bin.log
log-bin=/var/log/mysql/mysql-bin.log

now mysql restart command

root@Slave-Serv:~# service mysql restart

Step #04: Now database import into slave server.

root@Slave-Serv:~# mysql -u root -p < masterdb.sql

Type your slave server root password. Wait at lest few minutes.

Goto mysql terminal command below:

root@Slave-Serv:~# mysql -u root -p

Again type your slave server root password.

mysql> stop slave;
mysql> change master to master_host='master-server-ip', master_user='replica', master_password='replica-user-password', master_log_file='file-value-from-master', master_log_pos=log-position_from-master;
mysql> start slave;

Step #05: Goto master terminal when first time login then unlock tables command below.

mysql>  UNLOCK TABLES;

Step #06: Now check master slave replication status.

Goto slave server mysql terminal then run below command.

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.66.100.20
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000160
          Read_Master_Log_Pos: 153024467
               Relay_Log_File: mysql-relay-bin.000020
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000160
             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: 35117
              Relay_Log_Space: 154
              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: 2003
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: c8d666a2-ff5e-11e7-9e40-eebc113755c2
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 180417 11:54:56
     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:
1 row in set (0.00 sec)
ERROR:
No query specified

Step#07: Replication test create table and then goto slave server.

Goto master server mysql terminal

mysql> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create table test (c int);
Query OK, 0 rows affected (0.60 sec)

mysql> show tables;
+----------------------+
| Tables_in_test |
+----------------------+
test
+----------------------+
1 rows in set (0.00 sec)

Now goto slave server mysql terminal

mysql> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_test |
+----------------------+
test
+----------------------+
1 rows in set (0.00 sec)

If any problem so please see my YouTube channel for more information. Please share this tutorial any social media.

Author
Recent Posts