How To Multiple Mysql Databases Replication On Ubuntu 16.04

How To Multiple Mysql 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.

Mysql master slave replication on ubuntu 16.04 prerequisite

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

Master Server IP : 10.55.10.20
Slave Server IP : 10.55.10.21

vi /etc/mysql/mysql.conf.d/mysqld.cnf

#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/my.conf

[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 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)

Then database read lock command

mysql> FLUSH TABLES WITH READ LOCK;

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

root@Master-Serv:~# mysqldump -u root –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
Step #04: Now database store into slave server.

root@Slave-Serv:~# mysql -u root -p < masterdump.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.55.10.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 vmail;

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_vmail |
+----------------------+
| admin |
| alias |
| alias_domain |
| alias_moderators |
| anyone_shares |
| deleted_mailboxes |
| domain |
| domain_admins |
| forwardings |
| mailbox |
| recipient_bcc_domain |
| recipient_bcc_user |
| sender_bcc_domain |
| sender_bcc_user |
| sender_relayhost |
| share_folder |
| test |
| used_quota |
+----------------------+
18 rows in set (0.00 sec)

Now goto slave server mysql terminal

mysql> use vmail;
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_vmail |
+----------------------+
| admin |
| alias |
| alias_domain |
| alias_moderators |
| anyone_shares |
| deleted_mailboxes |
| domain |
| domain_admins |
| forwardings |
| mailbox |
| recipient_bcc_domain |
| recipient_bcc_user |
| sender_bcc_domain |
| sender_bcc_user |
| sender_relayhost |
| share_folder |
| test |
| used_quota |
+----------------------+
18 rows in set (0.00 sec)

Thank You very much reading my post.