
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).
log-bin=/var/lib/mysql/binary-log
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.
server_id=1
And the slave could have server id 2.
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
Binlog_Do_DB:
Binlog_Ignore_DB:
On the slave, stop the slave.
mysql -e "stop slave"
On the slave, issue the following command.
CHANGE MASTER TO
MASTER_HOST='<master hostname>',
MASTER_USER='replication_user',
MASTER_PASSWORD='itsasecret',
MASTER_PORT=3306,
MASTER_LOG_FILE='<master binary file>',
MASTER_LOG_POS=<master position>,
MASTER_CONNECT_RETRY=10;
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_Host: master.example.com
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