One of the most interesting features of MySQL is replication. By using it, we will have a much more scalable and fault-tolerant infrastructure.
The main advantages are:
- Load distribution of reads.
- Backups from one of the slaves without interrupting the service.
- Spare server that could act as a master if necessary.
We install MySQL on both the master and the slave:
We perform the initial configuration:
* Please select default authentication plugin (enter number or plugin name):
* 1) caching_sha2_password [MySQL 8.0 default]
* 2) mysql_native_password [MySQL 5.7 default]
We choose mysql_native_password so that it does not force us to connect via SSL . If we don’t, we will see the following error when starting the slave:
Last_IO_Error: error connecting to master ‘rep_user@MASTER_IP:3306’ - retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.
We start the service:
rc-update add mysql default
MySQL replication can be done in two different ways:
Legacy Mode
MASTER:
[mysqld]
bind-address = 0.0.0.0
server-id = 1
log_bin = mysql-bin.log
expire_logs_days = 10
max_binlog_size = 2G
SLAVE:
[mysqld]
bind-address = 0.0.0.0
server-id = 2
log_bin = mysql-bin.log
expire_logs_days = 10
max_binlog_size = 2G
We restart the service on both servers:
MASTER:
We create the replication user and assign the necessary grants.
mysql> CREATE USER rep_user@'SLAVE_IP' IDENTIFIED WITH mysql_native_password BY 'XXXX';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep_user@'SLAVE_IP';
mysql> FLUSH PRIVILEGES;
NOTE: If we are going to have more than one slave, we will have to create several replication users or a generic one like X.X.X.%
If operations are being performed on the database, filter access by firewall and stop any local process that may be using it.
We take the backup:
We get the binlog position:
Enter password:
*************************** 1. row ***************************
File: mysql-bin.000006
Position: 1628
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
At this point, we can already remove the firewall rules and start the local processes.
We take the backup to the slave.
SLAVE:
We load the backup:
We hook up the slave:
mysql> CHANGE MASTER TO MASTER_HOST='MASTER_IP', MASTER_USER='rep_user', MASTER_PASSWORD='XXXX', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=1628;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER_IP
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1628
Relay_Log_File: kr0mtest2-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1628
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 3ba93950-cd6b-11e9-9581-00163ea83ac0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0,00 sec)
We create a database on the master and check that it has been replicated on the slave.
MASTER:
mysql> CREATE DATABASE kr0m;
SLAVE:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kr0m |
| mysql |
| performance_schema |
| sys |
+--------------------+
GTID mode
MASTER:
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = 1
bind-address = 0.0.0.0
server-id = 1
log_bin = mysql-bin.log
expire_logs_days = 10
max_binlog_size = 2G
SLAVE:
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = 1
bind-address = 0.0.0.0
server-id = 2
log_bin = mysql-bin.log
expire_logs_days = 10
max_binlog_size = 2G
We restart the service on both servers:
MASTER:
We create the replication user and assign the necessary grants:
mysql> CREATE USER rep_user@'SLAVE_IP' IDENTIFIED WITH mysql_native_password BY 'XXXX';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO rep_user@'SLAVE_IP';
mysql> FLUSH PRIVILEGES;
NOTE: If we are going to have more than one slave, we will have to create several replication users or a generic one like X.X.X.%
If operations are being performed on the master, filter access by firewall and stop any local process that may be using the database.
We take the backup:
We take the backup to the slave.
SLAVE:
We make sure the backup has the GTID:
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '835ee743-78f5-11ea-960e-00163ea83ac0:1-3';
NOTE: If the master is a clean server, the GTID will be empty. We create an empty test DB, delete it, and then dump it again. The GTID can be unique or several separated by commas.
If it is empty, we will see something like this:
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '';
We load the data:
mysql> CHANGE MASTER TO MASTER_HOST="MASTER_IP", MASTER_USER="rep_user", MASTER_PASSWORD="XXXX", MASTER_AUTO_POSITION = 1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: MASTER_IP
Master_User: rep_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 877
Relay_Log_File: kr0mtest2-relay-bin.000002
Relay_Log_Pos: 416
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 877
Relay_Log_Space: 628
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 835ee743-78f5-11ea-960e-00163ea83ac0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 835ee743-78f5-11ea-960e-00163ea83ac0:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0,00 sec)
We create a database on the master and check that it has been replicated on the slave.
MASTER:
mysql> CREATE DATABASE kr0m;
SLAVE:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kr0m |
| mysql |
| performance_schema |
| sys |
+--------------------+
NOTE: If we take the dump from an existing slave in legacy mode, we only need to filter the traffic on the master and note the position at the time of the dump. If it is GTID, we don’t have to note anything, just take the backup and hook it up.