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.

  1. Configure the Master Server: Preparing/Optimizing the Server for Replication
  2. Create a User Account for Replication
  3. Obtain Information from the Master
  4. Configure the Slaves: Prepare and Enable Replication
  5. Start the Slaves
  6. 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:

  1. 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%\MySQL\MySQL Server 5.7\my.ini, %PROGRAMDATA%\MySQL\MySQL Server 5.7\my.cnf
       * %WINDIR%\my.ini, %WINDIR%\my.cnf
       * C:\my.ini, C:\my.cnf
       * INSTALLDIR\my.ini, INSTALLDIR\my.cnf
  2. 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
  3. 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
  1. 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

  1. Save your changes and exit the text editor.
  2. 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 Files\MySQL\MySQL Server 8.0\bin\mysqladmin" -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 Files\MySQL\MySQL Server 8.0\bin\mysqld"

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.

  1. Log into the MySQL Server console:

    mysql -u root -p
  2. When prompted, enter your MySQL's Root (admin) account password.
  3. Create the user account.

    mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'replicationpassword';
  4. Grant replication rights to the user account.

    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
  5. 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:

  1. 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)
  2. Record the File value and the Position value.
  3. 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:

  1. Once again, open the MySQL Server configuration into a text editor.
  2. 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
  3. 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]
  4. 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]
  5. Save your changes and exit the text editor.
  6. 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:

  1. Log into the MySQL console.

    mysql -u root -p
  2. 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 STATUS\G

*************************** 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.

Anonymous