Setting Up Replication
The terms master and slave have historically been used in replication, but the terms terms primary and replica are now preferred. The old terms are used still used in parts of the documentation, and in MariaDB commands, although MariaDB 10.5 has begun the process of renaming. The documentation process is ongoing. See MDEV-18777 to follow progress on this effort.
- Setting up a Replication Slave with Mariabackup
- Configuring the Master
- Settings to Check
- Configuring the Slave
- Getting the Master's Binary Log Co-ordinates
- Start the Slave
- Replicating from MySQL Master to MariaDB Slave
- See Also
Getting replication working involves steps on both the master server/s and steps on the slave server/s.
Setting up a Replication Slave with Mariabackup
In general, when replicating across different versions of MariaDB, it is best that the master is an older version than the slave. MariaDB versions are usually backward compatible, while of course older versions cannot always be forward compatible. See also Replicating from MySQL Master to MariaDB Slave.
Configuring the Master
- Enable binary logging if it's not already enabled. See Activating the Binary Log and Binary log formats for details.
- Give the master a unique server_id. All slaves must also be given a server_id. This can be a number from 1 to 232-1, and must be unique for each server in the replicating group.
- Specify a unique name for your replication logs with --log-basename. If this is not specified your host name will be used and there will be problems if the hostname ever changes.
- Slaves will need permission to connect and start replicating from a server. Usually this is done by creating a dedicated slave user, and granting that user permission only to replicate (REPLICATION SLAVE permission).
Example Enabling Replication for MariaDB
Add the following into your my.cnf file and restart the database.
[mariadb] log-bin server_id=1 log-basename=master1 binlog-format=mixed
The server id is a unique number for each MariaDB/MySQL server in your network. binlog-format specifies how your statements are logged. This mainly affects the size of the binary log that is sent between the Master and the Slaves.
Then execute the following SQL with the
mysql command line client:
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret'; GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Example Enabling Replication for MySQL
If you want to enable replication from MySQL to MariaDB, you can do it in almost the same way as between MariaDB servers. The main difference is that MySQL doesn't support
[mysqld] log-bin server_id=1
Settings to Check
There are a number of options that may impact or break replication. Check the following settings to avoid problems.
- skip-networking. If
skip-networking=1, the server will limit connections to localhost only, and prevent all remote slaves from connecting.
- bind-address. Similarly, if the address the server listens for TCP/IP connections is 127.0.0.1 (localhost), remote slaves connections will fail.
Configuring the Slave
- Give the slave a unique server_id. All servers, whether masters or slaves, are given a server_id. This can be a number from 1 to 232-1, and must be unique for each server in the replicating group. The server will need to be restarted in order for a change in this option to take effect.
Getting the Master's Binary Log Co-ordinates
Now you need prevent any changes to the data while you view the binary log position. You'll use this to tell the slave at exactly which point it should start replicating from.
- On the master, flush and lock all tables by running
FLUSH TABLES WITH READ LOCK. Keep this session running - exiting it will release the lock.
- Get the current position in the binary log by running
SHOW MASTER STATUS:
SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | master1-bin.000096 | 568 | | | +--------------------+----------+--------------+------------------+
- Record the File and Position details. If binary logging has just been enabled, these will be blank.
- Now, with the lock still in place, copy the data from the master to the slave. See Backup, Restore and Import for details on how to do this.
- Note for live databases: You just need to make a local copy of the data, you don't need to keep the master locked until the slave has imported the data.
- Once the data has been copied, you can release the lock on the master by running UNLOCK TABLES.
Start the Slave
- Once the data has been imported, you are ready to start replicating. Begin by running a CHANGE MASTER TO, making sure that MASTER_LOG_FILE matches the file and MASTER_LOG_POS the position returned by the earlier SHOW MASTER STATUS. For example:
CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='master1-bin.000096', MASTER_LOG_POS=568, MASTER_CONNECT_RETRY=10;
If you are starting a slave against a fresh master that was configured for replication from the start, then you don't have to specify
Use Global Transaction Id (GTID)
MariaDB starting with 10.0
MariaDB 10.0 introduced global transaction IDs (GTIDs) for replication. It is generally recommended to use (GTIDs) from MariaDB 10.0, as this has a number of benefits. All that is needed is to add the
MASTER_USE_GTID option to the
CHANGE MASTER statement, for example:
CHANGE MASTER TO MASTER_USE_GTID = slave_pos
See Global Transaction ID for a full description.
- Now start the slave with the
- Check that the replication is working by executing the
SHOW SLAVE STATUScommand:
SHOW SLAVE STATUS \G
- If replication is working correctly, both the values of
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Replicating from MySQL Master to MariaDB Slave
- Replicating from MySQL 5.5 to MariaDB 5.5+ should just work. When using a MariaDB 10.2+ as a slave, it may be necessary to set binlog_checksum to NONE.
- Replicating from MySQL 5.6 without GTID to MariaDB 10+ should work.
- Replication from MySQL 5.6 with GTID, binlog_rows_query_log_events and ignorable events works starting from MariaDB 10.0.22 and MariaDB 10.1.8. In this case MariaDB will remove the MySQL GTIDs and other unneeded events and instead adds its own GTIDs.
- Differences between Statement-based, mixed and row logging
- Replication and Foreign Keys
- Replication as a Backup Solution
- Multi-source Replication
- Global Transaction ID
- Parallel Replication
- Replication and Binary Log System Variables
- Replication and Binary Log Status Variables
- Semisynchronous Replication
- Delayed Replication
- Replication Compatibility