Broad Network


Asynchronous Replication with PurePerl MySQL

Using the PurePerl MySQL API Part 13

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: 28 Aug 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.

Setting Up 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 PurePerl MySQL API (as root - DBA):

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "secret", "master.com", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (Mysql::shutdown() != 1)
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else
                    {
                        print $Mysql::Message, "\n";
                    }
            }

         Mysql::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 and alter 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:

    Mysql::register_slave(slave_id, slave_hostname, slave_user, slave_password,  slave_port, master_id)

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

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "secret", "master.com", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (!Mysql::register_slave(2, '', '', '', undef, 1))
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else
                    {
                        print $Mysql::Message, "\n";
                    }
            }

         Mysql::close();

The copy_slave Function
Every PurePerl MySQL 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:

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "secret", "master.com", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (!Mysql::query('SHOW BINARY LOGS'))
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else
                    {
                        for (my $j=0; $j<$Mysql::No_of_Columns; ++$j)
                           {
                                print $Mysql::Fields[$j][0], ' ';
                           }
                        print "\n";
                        for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
                            {
                                for (my $k=0; $k<$Mysql::No_of_Columns; ++$k)
                                   {
                                       if ($k == ($Mysql::No_of_Columns)-1)
                                           {
                                               print $Mysql::Result[$i]{$Mysql::Fields[$k][0]};
                                           }
                                       else
                                           {
                                               print $Mysql::Result[$i]{$Mysql::Fields[$k][0]},  ', ';
                                           }
                                   }
                                print "\n";        
                            }
                    }
            }

         Mysql::close();

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

    Mysql::copy_slave(slave_id, binlog_filename, binlog_file_start_position)

If binlog_file_start_position is omitted, the copy starts from the beginning of the file. If 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:

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "secret", "master.com", 3306))
            {
                print "$Mysql::Error_msg";
            }
        else
            {
                if (!Mysql::copy_slave(2, mysql-bin.000002))
                    {
                        print $Mysql::Error_msg, "\n";
                    }
                else
                    {
                        print $Mysql::binlog_messages, "\n";        
                    }
            }

         Mysql::close();

Note that events are actually executed in the slave. The slave can return OK and error messages (feedbacks). These messages are text that are concatenated to the $Mysql::binlog_messages variable.

Knowing the next Binlog Filename to continue Replication
After running the above code, the following code will print out the name of the next binlog file whose events you need:

#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;

        if (!Mysql::connect("root", "secret", "master.com", 3306))
         {
                print "$Mysql::Error_msg";
         }
        else
         {
                print $Mysql::next_binlog_filename, "\n";
         }

         Mysql::close();

Note the use of the variable, $Mysql::next_binlog_filename .

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

Chrys

More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course

BACK

Comments

Become the Writer's Follower
Send the Writer a Message