Purpose of MySQL replication
MySQL replication is a powerful feature that allows data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). The primary purposes of MySQL replication include improving data availability and reliability by providing data redundancy, distributing the database load across multiple servers to enhance performance and scalability, and enabling data backup and recovery.
Additionally, MySQL replication facilitates real-time data analytics and reporting by offloading read queries to the slave servers, while the master server handles write operations. This separation of read and write operations not only optimizes resource utilization but also ensures that the master server remains responsive under heavy write loads.
In this guide, we will walk you through the steps to set up MySQL replication from a master server to a slave server. This process involves configuring both servers, creating necessary users, and establishing a secure communication channel between the two servers using SSH. By the end of this guide, you will have a functional MySQL replication setup that can help you distribute database load, achieve data redundancy, and improve overall system performance.
How to setup MySQL slave replication.
Your method of choice for setting up slave replication depends on whether you use MyISAM database engine for your data.
If you use MyISAM engine for any of your database tables, you will have to have some downtime in order to take consistent data dump, which is required to set up replication.
If on the other hand all your tables are InnoDB, you can have zero downtime replication setup.
So let’s find out whether we have MyISAM tables:
SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
Empty result-set means that you are not using MyISAM for any table, and you can proceed to no-downtime replication setup.
If you know what you’re doing, you may want to convert all MyISAM tables to InnoDB format. This should be a relatively safe operations, if you know you don’t use full text indexes which only MyISAM format provides:
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.',TABLE_NAME, ' ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
In master /etc/my.cnf:
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-do-db=dbname
binlog-format=mixed
expire-logs-days=5
Restart is required to enable binary logging, so:
systemctl restart mysqld
Set up secure SSH connection tunnel from slave server to master
On the master server, set up a dedicated user for SSH tunnel from slave to master.
adduser mytun
On the slave server, install autossh:
yum -y install autossh
Add root’s SSH (slave) public key to mytune masters authorized_keys. On the slave server, run asroot`:
ssh-copy-id mytun@master_server_ip
On the slave server as well, set up autossh tunnel by creating a SystemD unit at /etc/systemd/system/autossh-mysql-tunnel.service:
[Unit]
Description=AutoSSH tunnel service everythingcli MySQL on local port 5000
After=network.target
[Service]
Environment="AUTOSSH_GATETIME=0"
ExecStart=/usr/bin/autossh -M 0 -o "ServerAliveInterval 30" -o "ServerAliveCountMax 3" -NL 5000:localhost:3306 mytun@master_server_ip
[Install]
WantedBy=multi-user.target
Then enable it:
systemctl daemon-reload
systemctl start autossh-mysql-tunnel.service
systemctl enable autossh-mysql-tunnel.service
Set up MySQL replication
1. Configure your master server for replication
1.1. Create replication user
CREATE USER 'repl'@'%' IDENTIFIED BY 'CzIkcN3vReD';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1';
FLUSH PRIVILEGES;
2. Configure your slave server for replication
In slave my.cnf:
server-id=2
binlog-format=mixed
replicate-do-db=dbname
# disable replication for MEMORY tables
replicate-ignore-table=dbname.memory_table
read-only=1
2. Lock tables for read, at master (only need if hav MyISAM tables)
In your terminal, run mysql followed with FLUSH TABLES WITH READ LOCK;. Keep this session open
3. Record binary log coordinates.
Take note the coordinates from: SHOW MASTER STATUS;. Typical output:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 326 | nzb_admin | |
1 row in set (0.00 sec)
4. Export your MySQL database
Run the command to export only the database that you need to replicate
mysqldump --opt exampledb > exampledb.sql
Steps to setup replication on the slave servers
Connect to MySQL client and run query which specifies master server’s IP and binary log coordinates:
CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=5000,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=326,
MASTER_CONNECT_RETRY=10;
Note how we use the local port 5000 as it allows secure SSH tunneling of MySQL data.
Start the replication process:
START SLAVE;
Verify replication is working with:
SHOW SLAVE STATUS;
If something gone wrong:
Run on master:
RESET MASTER;
On slave:
RESET SLAVE ALL;
If there is no lag (see below on that), and you see that replication really doesn’t work or stopped working for some reason, refer to MySQL troubleshooting page which offers some additional steps.
Make your changes permanent via my.cnf.
Note the comments for which directive should go to which server:
binlog-do-db=exampledb
# applies to slave:
replicate-do-db=exampledb
# applies for slave
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
# applies to slave:
replicate_ignore_table=exampledb.table1
replicate_ignore_table=exampledb.table2
replicate_ignore_table=exampledb.table3
server-id=1 # on master
server-id=2 # on slave
Replication config tip #1: Make sure binary logging type = mixed to avoid “insert..select” queries to mess things up.
Replication config tip #2: Use replicate-wild-ignore-table=garbage.% instead of do-db options for filtering what will be replicated, since it’s more safe for replication.
Stop MySQL slave lagging from master
The problem you may find immediately is that replication doesn’t work. But hey, you did all right. Confirm it’s working with:
SHOW SLAVE STATUS
If both IO Thread and SQL Thread are saying YES, it means it’s fine. But if you don’t see the recent data on slave, it means it has lag from master.
To confirm, run mysqltuner. It reports about the current lag (you can calculate the values from slave status, but it’s most convenient to run mysqltuner to confirm on the current lag time):
[!!] This replication slave is lagging and slave has 18 second(s) behind master host
Steps to remedy the lag described next.
Ensure same time.
Confirm you have the same time on both master and slave. Run date command in SSH. If not same, make sure you have NTP installed and running.
Make use of of Parallel replication.
Use as many threads as number of CPU cores. Or 2x less if you want to give more power to select queries on slave.
To enable at runtime:
STOP SLAVE SQL_THREAD;
SET GLOBAL slave_parallel_threads = 4;
START SLAVE SQL_THREAD;
SELECT @@slave_parallel_threads;
Set permanently in /etc/my.cnf:
slave-parallel-threads=4
More info on parallel replication is here.
If you want to write from slave
In some situations you might need to write to MySQL from the slave server. But with MySQL slave replication in place you shouldn’t really write to slave DB. You might want to split writes to MySQL master and reads to slave (read/write split).
You can use MariaDB MaxScale for this.
Purge binary logs
Make sure to set expire logs configuration value to remove old binary logs. If not:
mysql -e "purge binary logs before '$(date "+%F %T")'"