How to setup MySQL slave replication.

References:

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 setup 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

adduser mytun

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

Add root’s SSH (slave) public key to mytune master authorized_keys.

Part I. Setup MySQL replication with downtime (have MyISAM tables)

In slave, yum -y install autossh

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. Lock tables for read

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

vim /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@db.example.com

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl start autossh-mysql-tunnel.service
systemctl enable autossh-mysql-tunnel.service

Connect to MySQL client and run query which specifies master server’s IP and binary log coordinates:

CHANGE MASTER TO
MASTER_HOST='1.1.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=326,
MASTER_CONNECT_RETRY=10;

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")'"