yumupgrades for production use, this is the repository for you.
Active subscription is required.
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 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');
[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
In slave my.cnf:
disable replication for MEMORY tables
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
Description=AutoSSH tunnel service everythingcli MySQL on local port 5000
ExecStart=/usr/bin/autossh -M 0 -o “ServerAliveInterval 30” -o “ServerAliveCountMax 3” -NL 5000:localhost:3306 email@example.com
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='126.96.36.199', 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 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:
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")'"