Bootstrap FreeKB - mySQL / MariaDB - Master-Slave replication
mySQL / MariaDB - Master-Slave replication

Updated:   |  mySQL / MariaDB articles

There are two types of replication.

  • Master-Master - Both databases are masters, meaning both can be used for reading and writing.
  • Master-Slave - One database is the master, the other is the slave. Only the master can read and write, the slave can only read.

This assumes you have installed mySQL or MariaDB on both systems.

You will first need to define the log-bin directive in the main configuration file (e.g. /etc/my.cnf).



The data directory, which is /var/lib/mysql by default, should contain the binary log files.

-rw-rw----. 1 mysql mysql       325 May 27 07:49 binary-log.000001
-rw-rw----. 1 mysql mysql        29 May 27 07:49 binary-log.index


Each system will need to have a unique server ID in the main configuration file. For example, the master could have server id 1.



And the slave could have server id 2.



On the master system, create a user account that will be used for replication and grant the user the replication slave priviledge.

create user 'replication_user'@'%' identified by 'itsasecret';
grant replication slave on *.* to 'replication_user'@'%';


On the master, use the flush tables with read lock command to disable write access to tables. The tables will continue to have read access.

mysql -e "flush tables with read lock"


On the master, export the database that you want replicated.

mysqldump -u username -p password --databases database_name > db1.sql


On the slave, import the .sql file.

mysql -u username -p < db1.sql


On the master, use the show master status command to get the name and position of the binary log file. Optionally, the reset master command can be used so that prior binary log files are removed and a new binary log file ending in 000001 is used.

[root@master ~] mysql -e "show master status \G"
            File: binary-log.000015
        Position: 725


On the slave, stop the slave.

mysql -e "stop slave"


On the slave, issue the following command.

  MASTER_HOST='<master hostname>',
  MASTER_LOG_FILE='<master binary file>',
  MASTER_LOG_POS=<master position>,


On the slave, start the slave.

mysql -e "start slave"


On the slave, issue the show slave status command.

mysql -e "show slave status \G"


And something like this should be returned.

       Slave_IO_State: Waiting for master to send event
          Master_User: replication_user
          Master_Port: 3306
        Connect_Retry: 10
      Master_Log_File: binary-log.000015
  Read_Master_Log_Pos: 725
       Relay_Log_File: mariadb-relay-bin.000002
        Relay_Log_Pos: 552
Relay_Master_Log_File: binary-log.000015
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes


On the master, release the tables lock, restoring write access to the tables.

mysql -e "unlock tables"


Let's say column001 in table001 in the db1 database 

[root@master ~] mysql -e "select column001 from db1.table001 where id = 123"
| column001     |
| foo           |

[root@slave ~] mysql -e "select column001 from db1.table001 where id = 123"
| column001     |
| foo           |


Let's update column001 on only the master.

[root@master ~] mysql -e "update db1.table001 set column001 = 'bar' where id = 123"


Reissuing the select command should show that the change was made on the master and then immediately replicated to the slave.

[root@master ~] mysql -e "select column001 from db1.table001 where id = 123"
| column001     |
| bar           |

[root@slave ~] mysql -e "select column001 from db1.table001 where id = 123"
| column001     |
| bar           |


Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee


Add a Comment

Please enter c624e2 in the box below so that we can be sure you are a human.