Broad Network


Asynchronous Replication with EMySQL API

Using the EMySQL API Part 14

Foreword: In this part of the series, I explain how to do asynchronous replication on the slave computer, from the master computer.

By: Date Published: 24 Oct 2017

Introduction

Replication involves two computers: one called the slave and the other called the master. The master has the MySQL server and the databases of interest. The slave has a MySQL server, and should have the same database as in the master. Replication is copying the changes to the database of the master, to the same database in the slave. Asynchronous is copying at any time you want (any time of the day or any time of week). Synchronous is copying automatically, as the changes are made, with the help of a program. As of now MySQL does have have synchronous replication. However, it has semi-synchronous replication - see later. In this part of the series, I explain how to do asynchronous replication on the slave computer, from the master computer.

Any MySQL server has an ID (identification number): it is a whole number (integer). Do not confuse between software server and hardware (computer) server. MySQL server is a software server which can reside in a computer server or any ordinary computer. The slave and master computers are in a network. The default (installation) MySQL ID is 0.

You cannot use a MySQL server ID of 0 in replication. The ID for the slave MySQL also has to be different from the ID of the master MySQL. I suggest you give the master MySQL server the ID of 1 and the slave, the ID of 2. This must be done first before any replication can take place. After giving the IDs you also have to register the slave MySQL server at the master MySQL server before you can start copying (asynchronously).

Note: MySQL server slave is the MySQL software in the slave computer; MySQL server master is the MySQL software in the master computer.

Note: there can be more than one slave computers, each with its own MySQL server; but I will not address that here.

NOTE: ALL THE FEATURES (FUNCTIONS) OF THIS API HAVE BEEN TESTED EXCEPT THIS ONE, ASYNCHRONOUS REPLICATION (Mysql::copy_slave() ). SO TEST THIS FEATURE (FUNCTION) FIRST BEFORE YOU USE IT.

The Binlog File
Any MySQL server can have a file called the binlog (binary log) file. This file stores all the changes made to the database. There are more than one binlog file, and they occur in a series as the changes continue. However, you have to configure the MySQL server to have the series of binlog files. It is the master server that needs this configuration; the slave server does not need this.

Changes to the server are called events. These events are SQL statements issued to the master server from users (ordinary) of the master server. If you do not configure the master MySQL server (in the master computer) at the creation time of the database, you will not be able to do replication for the previous (first)  SQL statements of the database.

Setup for Replication
To setup for replication, do the following in the order given:
- Have two computers (one you identify as slave, the other as master) in a network. Each computer should have a MySQL server, and the master, a MySQL database.
- Give the master MySQL server, the ID, 1 or any number of your choice. Give the slave MySQL server the ID 2 or any number different from that of the master (but not zero).
- Configure the master server to be having the series of binlog files.
- Register (by software), the slave MySQL server to the master MySQL server.
After that, at anytime of the day or week, you can do asynchronous replication, i.e. copy recent past events from the master to the slave.

Giving ID to MySQL Server and configuring for Binlog File
If the master MySQL server is not shutdown, shut it down with the following code (edit connecting below), from master or slave computer using the EMySQL API (as root - DBA):

const mysql = require('./server/Mysql.js');

    con = new mysql.Connection("user", "secret", "master.com", 3306, function(err)
        {
            if (err)
                console.log(err);
        });

    con.shutdown('', function(err, result)
        {
            if (err)
                console.log(err);
            else
                console.log(result);
        });

    con.close();

Now, go to the master computer (physically) and edit the file, my.cnf or my.ini.  In my computer, the my.ini file is found in the directory:

    C:\Program Files\MySQL\MySQL Server 5.5

Within the [mysqld] section of the configuration file, add the log-bin and server-id options. If these options already exist, but are commented out, uncomment the options, altering them according to your needs. For example, to enable binary logging using a log file name prefix of mysql-bin, and to give the server ID the value of 1, use these lines:

[mysqld]
log-bin=mysql-bin
server-id=1

Restart the MySQL server with a command at the console similar to:

    "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --console

At this stage you are done with the master. Do the same thing for the slave, but change master.com to localhost or something like slave.com. For the slave, within the [mysqld] section, you do not need the line, "log-bin=mysql-bin"; but server-id should be 2 or a higher positive integer.

Registering the Slave
You register the slave at the master, as root, from the slave computer. The expression (command) syntax is:

    con.register_slave(slave_id, slave_hostname, slave_user, slave_password,  slave_port, master_id, callbackRS)

Apart from slave_id and master_id, the rest of the arguments are usually left empty. The following code will register the slave:

const mysql = require('./server/Mysql.js');

    con = new mysql.Connection("user", "secret", "master.com", 3306, function(err)
        {
            if (err)
                console.log(err);
            else
                {
                        con.registerSlave = function(2, '', '', '',  undefined, 1, function(errr, Message)
                            {
                                if (errr)
                                    console.log(errr);
                                else
                                    {
                                        console.log(Message);
                                    }
                           });
                }
        });

    con.close();

The copy_slave Function
Every EMySQL API has this function. It copies a series of events from the master and re-executes them at the slave. The command is issued from the slave.

After logging in from the slave (as master root or with master root privileges), and registering the slave, you can execute (call) this function. Events at the master are saved in a series of binlog files in chronological (time) order.

In order to call the copy_slave (copy to slave) function, you need to know the name of the binlog file and the position (whole number) of the event in the file, where you want the copy to start from.

You can use SHOW BINARY LOGS to know the names of the different binary log (binlog) files and their sizes (that are in the master computer), as follows:

const mysql = require('./server/Mysql.js');

    con = new mysql.Connection("user", "secret", "master.com", 3306, function(err)
        {
            if (err)
                console.log(err);
           else
              {
                        var crtDBStr = `SHOW BINARY LOGS`;
                        con.query(crtDBStr, function(errr, result, field)
                            {
                                if (errr)
                                    console.log(errr);
                                else
                                    {
                                        var headerStr = '';
                                        for (var $j=0; $j<field.length; ++$j)
                                           {
                                                headerStr = headerStr + Fields[$j][0] + ' ';
                                           }
                                        console.log(headerStr);
                                        var noOfColumns = field.length;
                                        var rowStr = '';
                                        for (var $j=0; $j<result.length; ++$j)
                                            {
                                                for (var $k=0; $k<noOfColumns; ++$k)
                                                   {
                                                       if ($k == (noOfColumns-1))
                                                           {
                                                               rowStr = rowStr + result[$j].get(Fields[$k][0]);
                                                           }
                                                       else
                                                           {
                                                               rowStr = rowStr + result[$j].get(Fields[$k][0]) + ', ';
                                                           }
                                                   }
                                                console.log(rowStr);
                                            }
                                    }
                            });
              }

            con.close();
        });

The syntax for the copy_slave() function is as follows:

    con.copy_slave(slave_id, master_binlog_filename, binlog_file_start_position, callbackCS)

If binlog_file_start_position is omitted, the copy starts from the beginning of the file. If master_binlog_filename (as well as binlog_file_start_position) is omitted, the copy starts from the first known binlog file.

The following example should copy some events to the slave database:

const mysql = require('./server/Mysql.js');

    con = new mysql.Connection("user", "secret", "master.com", 3306, function(err)
        {
            if (err)
                console.log(err);
         else
             {
                        con.registerSlave = function(2, mysql-bin.000004, undefined, function(errr, feedbacks)
                            {
                                if (errr)
                                    console.log(errr);
                                else
                                    {
                                        console.log(feedbacks);
                                    }
                         });
             }
        });

    con.close();

Note that events are actually executed in the slave. The slave can return OK and error messages (feedbacks). These messages are joined and sent back as one variable (feedbacks - array). Error (errr) would occur only if the copying is not processed (properly), not if there is an error in a SQL statement.

Knowing the next Binlog Filename to continue Replication
The next binlog filename should be present among the above feedbacks.

That is it for this part of the series. We stop here and continue in the next part.

Chrys

BACK

Comments

Become the Writer's Follower
Send the Writer a Message