Master Server
[mariadb] log-bin server_id=1 log-basename=master #skip-networking bind-address=192.168.1.100
- log-bin causes the server to keep a log of all changes to the databses, both data and structure, as well as how long each statement took to execute.
- The server_id is a integer 1 to 4,294,967,295. This ID must be unique for each server in a relication group.
- log-basename=master specifies the basename of the log files. If this is not specified then the hostname will be used. Setting this will ensure that log files used won't change when the hostname does.
- skip-networking will only allow local connections and thus will deny any replication.
- If bind-address is set to 127.0.0.1, then only connections from the local machine will be accepted.
Restart the service when done:
# systemctl restart mariadb
The binary log co-ordinates need to be obtained and used when setting up the slave server. To ensure that this doesn't change, the tables on the master must be locked.
GRANT REPLICATION SLAVE ON *.* TO replication_user; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000007 | 327 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
The replication_user an account set up prior for the sole purpose of replications. While the databases are locked, the existing databases, tables, and data needs to be migrated.
$ mysqldump --all-databases -u root -p > databases.sql
Slave Server
Set the server_id to any value other than the one given to the master server. Don't set the log-bin or log-basename options.
[mariadb] server_id=2
# systemctl restart mariadb
Restore the database from the backup
$ mysql -u root -p < database.sql
Configure the slave to point to the real master server instead of itself and start the slave.
CHANGE MASTER TO MASTER_HOST='192.168.1.100', MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='master-bin.000007', MASTER_LOG_POS=327, MASTER_CONNECT_RETRY=10; START SLAVE;
Testing
To make sure that replication is working, create a new database on the master then log into the slave. If the new database is there then replication is working.