In the Setting up Basic Master-Slave Replication in MySQL 8 blog, we went through the process of setting up two MySQL servers on the same box for the purposes of replication. In that scenario, the Master handled write operations, while the Slave performed the preponderance of read operations. While this relatively simple topology may suffice for small to medium businesses, for more intensive applications, this basic design can be scaled to a cluster, by employing the NDB Storage Engine. In most cases you would still have (at least) one Master database server that handles write operations and at least a couple of Slaves to handle reads.
Having gone over a variety of ways to optimize the performance of your MySQL cluster, the time has come to put that theory into practice by setting up a MySQL cluster. To better approximate real-life usage, we'll split up the master and slaves across a couple of physical servers.
The Plan
"If you fail to plan, you are planning to fail!" – Benjamin Franklin
Before diving into creating our server cluster, let's quickly go over the steps that we'll be taking. Consider this to be a summary of each section that will follow throughout this blog.
We'll assume that you've already got a usable instance of MySQL running. That will become the Master in our set up.
- Configure the Master Server: Preparing/Optimizing the Server for Replication
- Create a User Account for Replication
- Obtain Information from the Master
- Configure the Slaves: Prepare and Enable Replication
- Start the Slaves
- Perform Basic Checks on Replication Process
Configure the Master Server
As with a straight Master-Slave configuration, the Master server will handle all write operations as well as propagate data across the cluster of slaves. This will ensure data consistency across the entire cluster, which in our case will consist of three servers. Typically, it's a good idea to make this server a little more robust than your slaves. This can be achieved through selective optimization following the procedure outlined in the Master Optimization section of the MySQL 8 Slave Cluster Configuration Basics blog.
Preparing/Optimizing the Server for Replication
It is well worth noting that the default installation of MySQL does not permit Master-Slave replication out of the box. Therefore, we need to configure a few options to enable replication:
- Open the MySQL Server configuration into a text editor.
On Linux, the my.cnf file may be located in either the /etc or /etc/mysql directory depending on your version of Linux.
On Windows, the my.cnf file may be called my.ini. MySQL looks for it in the following locations (in this order):
* %PROGRAMDATA%MySQLMySQL Server 5.7my.ini, %PROGRAMDATA%MySQLMySQL Server 5.7my.cnf
* %WINDIR%my.ini, %WINDIR%my.cnf
* C:my.ini, C:my.cnf
* INSTALLDIRmy.ini, INSTALLDIRmy.cnf - Under the [mysqld] section of the configuration file, find the bind-address option. Uncomment it and change its value to the IP address that will communicate with your application and the other MySQL servers. You may enter 0.0.0.0 as the IP address to allow communication on any interface, however, this may be less secure. Instead, you should configure all communications to take place over your private network:
bind-address = 10.4.0.16 - Still under the [mysqld] section, find the following lines:
#server-id = 0
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
- Uncomment the "server-id" line and change its value to 1. Also, uncomment the "log_bin" line as follows:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
- Save your changes and exit the text editor.
- You'll have to restart MySQL for the changes to take effect.
In Linux:
sudo service mysql restart
In Windows, you can stop the MySQL server by executing this command:
C:> "C:Program FilesMySQLMySQL Server 8.0binmysqladmin" -u root shutdown
Note that, if the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.
Then, start up the mysqld process using this command:
C:> "C:Program FilesMySQLMySQL Server 8.0binmysqld"
Bear in mind that the path to mysqld may vary depending on the install location of MySQL on your system.
Create a User Account for Replication
Your slave servers will require a user account that allows them to request the data to replicate from the master. As such, this account will require access to whichever database(s) you want to replicate to your slaves.
Your slave servers will require a user account that allows them to request the data to replicate from the master. As such, this account will require access to whichever database(s) you want to replicate to your slaves.
- Log into the MySQL Server console:
mysql -u root -p - When prompted, enter your MySQL's Root (admin) account password.
- Create the user account.
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'replicationpassword'; - Grant replication rights to the user account.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com'; - mysql> Flush the privileges table and apply your changes.
mysql> FLUSH PRIVILEGES;
Obtain Information from the Master
Before your slaves can start replicating data from your databases, they need to know the log file of the master server that records changes, as well as the location in the log file of the most recent change. Do the following on the master:
- Get the current status of the master server.
mysql> show master status
The output should look similar to this example.
mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 107 | | |
+——————+———-+————–+——————+
1 row in set (0.00 sec) - Record the File value and the Position value.
- Exit the MySQL console.
Configure the Slaves
We are now ready to initialize replication on the slaves. This step is divided into two parts: configuring the slaves and enabling them for replication.
Configuring the Slaves
The following instruction must be performed on each of your slave servers in order to prepare them for replication:
- Once again, open the MySQL Server configuration into a text editor.
- Under the [mysqld] section of the configuration file, find the "bind-address" option. Uncomment it and change its value to the IP address that will be utilized by the other MySQL servers and application(s) to communicate with the server.
bind-address = 10.2.0.16 - Also under the [mysqld] section, find the following lines:
#server-id = 0
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = [include_database_name]
#binlog_ignore_db = [include_database_name] - Uncomment the "server-id" line and change its value. Each MySQL server being added to the cluster must have a unique server ID, usually incremented by 1. Since 1 is reserved for our master server, the second server will have an ID of 2, the third server will have an ID of 3, and so on. Also, uncomment the "log_bin" line:
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = [include_database_name]
#binlog_ignore_db = [include_database_name] - Save your changes and exit the text editor.
- Restart MySQL to apply your changes according to the procedures outlined above that apply to your OS.
Enabling the Slaves
This step involves settin up the slave to communicate with the master by configuring the slave with the necessary connection information.
Execute the following command in order to update the master information:
- Log into the MySQL console.
mysql -u root -p - Configure a connection to the master server.
CHANGE MASTER TO
MASTER_HOST='master_host_name',
MASTER_USER='repl1',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=[recorded_log_position]; — eg 100
Start the Slaves
To activate the slaves, we'll use the START SLAVE command. Although it comes with a number of optional parameters, we'll stick with its basic form here:
mysql>START SLAVE;
Perform Basic Checks on Replication Process
You should always perform a basic check to ensure that replication is working correctly. Execute the following command in order to verify the replication is up and running. Here is some sample output, using the "G" statement terminator instead of a semi-***, which produces a more readable vertical layout:
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 64.54.200.32
Master_User: repl
Master_Port: 13000
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1307
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 1508
Relay_Master_Log_File: master-bin.000002
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: 1307
Relay_Log_Space: 1858
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: 3e11fa47-71ca-11e1-9e33-c80aa9429562
Master_Info_File: /var/mysqld.2/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 10
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
Executed_Gtid_Set: 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_name:
Master_TLS_Version: TLSv1.2
Master_public_key_path: public_key.pem
Get_master_public_key: 0
Congratulations. Your MySQL master-slave cluster replication is now successfully set up.
Cluster Replication vs. Group Replication
In late 2016, Oracle, the parent company of MySQL, introduced its own Group Replication implementation that was built from the ground up as a new solution. Although MySQL Group Replication shares many very similar concepts to Galera, a plugin that offered both asynchronous and virtually synchronous replication, it is not based on it. Rather, Group Replication was designed to be a significant improvement over Oracle's previous offering, Fabric.
The architectures are different, but the goals are similar. Both do an excellent job of High Availability in that any single node crashing can be recovered from, usually automatically. Both also provide a high degree of read scaling, although Group Replication promises to provide more write scaling. Where Group Replication differs from Cluster Replication is that the former is more slanted towards Fault Tolerance than the latter. Group Replication employs a built-in group membership service that keeps the view of the group consistent and available for all servers at any given point in time. Servers can leave and join the group and the view is updated accordingly. Sometimes servers can leave the group unexpectedly, in which case the failure detection mechanism detects this and notifies the group that the view has changed. This is all automatic.
True fault tolerance is achievable only if you are willing to put nodes in at least 3 separate geographic locations (to protect against floods, tornados, earthquakes, etc). Both allow for that. Use the rule of surviving any single-point-of-failure, and include "datacenter" as a point-of-failure.
We'll explore Group Replication in greater detail in a future blog.