MySql File-based Master Slave Replication

MySql File-based Master Slave Replication – All Database

Replication enables data from one MySQL database server to be copied to one or more MySQL database servers. Replication is asynchronous by default, replicas do not need to be connected permanently to receive updates from a source. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

The master slave replication can be made using the following two methods:

File-based Replication: The traditional method is based on replicating events from the master’s binary log, and requires the log files and positions in them to be synchronized between master and slave.

GTID Replication: The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files. Replication using GTIDs guarantees consistency between master and slave as long as all transactions committed on the master have also been applied to the slave.

For this demonstration, we will see How to configure File based Replication – All Databases. To fulfill this demonstration we have already configured two Mysql servers on two different virtual machines.

Host name	IP	Role	Installation Link
test-machine01	192.168.114.230	Master	Click Here
test-machine02	192.168.114.199	Slave	Click Here

Phase I: Configure Master Server for Replication
Add below parameter in Master my.cnf file and restart mysqld service. Please note we are using server_id = 1.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

pid_file=/var/run/mysqld/mysqld.pid

server_id = 1
log_bin = /var/lib/mysql/binlog
log_error = /var/lib/mysql/mysqld.log

relay_log = /var/lib/mysql/relay_bin01
relay_log_index = /var/lib/mysql/relay_bin.index

relay_log_recovery = on

master_info_file = /var/lib/mysql/master.info
relay_log_info_file = /var/lib/mysql/relay_log.info

master_info_repository = TABLE
relay_log_info_repository = TABLE

[root@test-machine01 etc]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@test-machine01 etc]#

Login into MySQL as root user and create the slave user and grant privileges for replication. And note down binlog file number and position using command show master status;

mysql> create user 'repl_user'@'%' identified by 'Password123#@!';
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123#@!';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to  'repl_user'@'%';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'repl_user'@'%';
+---------------------------------------------------+
| Grants for repl_user@%                            |
+---------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repl_user`@`%` |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000007 |      865 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Phase II: Configure Slave Server for Replication
Add below parameter in Slave my.cnf file and restart mysqld service. Please note I am using here server_id=2

[mysqld]
user=mysql
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
socket=/u01/mysql-8/mysql.sock

pid_file=/u01/mysql-8/mysqld.pid

innodb_directories="/u01/mysql-8"

server_id = 2
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log

relay_log = /u01/mysql-8/data/relay_bin01
relay_log_index = /u01/mysql-8/data/relay_bin.index

relay_log_recovery = on

master_info_file = /u01/mysql-8/data/master.info
relay_log_info_file = /u01/mysql-8/data/relay_log.info

master_info_repository = TABLE
relay_log_info_repository = TABLE

[root@test-machine01 etc]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@test-machine01 etc]#

Login into MySQL as root user then tells the slave where to look for Master log file, that we have written down on master with SHOW MASTER STATUS.

mysql> change master to master_host='192.168.114.230' , master_user='repl_user' , master_password='Password123#@!' , master_log_file='binlog.000007' , master_log_pos=865 , master_connect_retry=10;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.114.230
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 1152
               Relay_Log_File: relay_bin01.000003
                Relay_Log_Pos: 321
        Relay_Master_Log_File: binlog.000007
             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: 1152
              Relay_Log_Space: 978
              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: 13ecba9c-444a-11eb-a397-000c29f9d9e6
             Master_Info_File: mysql.slave_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:
     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.00 sec)

ERROR:
No query specified

mysql>

Test the Configuration master slave Replication, Do the changes in Master mysql instance.

mysql> create database repl_test;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> use repl_test;
Database changed
mysql>
mysql> select database();
+------------+
| database() |
+------------+
| repl_test  |
+------------+
1 row in set (0.00 sec)

mysql> insert into repl_test1 values (1,'TEST','TEST');
Query OK, 1 row affected (0.17 sec)

mysql> insert into repl_test1 values (2,'TEST','TEST');
Query OK, 1 row affected (0.01 sec)

mysql> insert into repl_test1 values (3,'TEST','TEST');
Query OK, 1 row affected (0.00 sec)

mysql> insert into repl_test1 values (4,'TEST','TEST');
Query OK, 1 row affected (0.00 sec)

mysql> insert into repl_test1 values (5,'TEST','TEST');
Query OK, 1 row affected (0.00 sec)

mysql> insert into repl_test1 values (6,'TEST','TEST');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Verifying the SLAVE, by running the same command, it will return the same values in the slave too.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| repl_test          |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql>
mysql> use repl_test;
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>
mysql> select database();
+------------+
| database() |
+------------+
| repl_test  |
+------------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| repl_test1          |
+---------------------+
1 row in set (0.00 sec)

mysql> select count(*) from repl_test1;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.02 sec)

mysql>

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

DATABASE, MYSQL, REPLICATION

DBsGuru

MySql File-based Master Slave Replication – All Database

MySql File-based Master Slave Replication – All Database

Related Articles

Jamsher Khan

MySql File-based Master Slave Replication – All Database

MySql File-based Master Slave Replication – All Database

Related Articles

Jamsher Khan

Related Articles

How To Bootstrap MySQL Router For InnoDB Cluster

Changing InnoDB Cluster Topology in MySQL