Getting replication working involves steps on both the master server/s and steps on the slave server/s.
MariaDB 10.0 introduced replication with global transaction IDs. These have a number of benefits, and it is generally recommended to use this feature from MariaDB 10.0.
If you would like to use Mariabackup to set up a replication slave, then you might find the information at Setting up a Replication Slave with Mariabackup helpful.
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.
Add the following into your my.cnf file and restart the database.
[mariadb] log-bin server_id=1 log-basename=master1
The server id is a unique number for each MariaDB/MySQL server in your network.
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'@'%';
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 log-basename
.
[mysqld] log-bin server_id=1
There are a number of options that may impact or break replication. Check the following settings to avoid problems.
skip-networking=1
, the server will limit connections to localhost only, and prevent all remote slaves from connecting. 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.
FLUSH TABLES WITH READ LOCK
. Keep this session running - exiting it will release the lock. SHOW MASTER STATUS
: SHOW MASTER STATUS; +--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000096 | 568 | | | +--------------------+----------+--------------+------------------+
UNLOCK TABLES;
CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-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 MASTER_LOG_FILE
and MASTER_LOG_POS
.
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.
START SLAVE
command: START SLAVE;
SHOW SLAVE STATUS
command: SHOW SLAVE STATUS \G
Slave_IO_Running
and Slave_SQL_Running
should be Yes
: Slave_IO_Running: Yes Slave_SQL_Running: Yes
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/setting-up-replication/