Asynchronous Replication with the PurePHP MySQL API
Using the PurePHP MySQL 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: Chrysanthus Date Published: 18 Jan 2018
Introduction
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 (copy_slave() ). SO TEST THIS FEATURE (FUNCTION) FIRST BEFORE YOU USE IT.
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.
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 PurePHP MySQL API (as root - DBA):
<?php
require_once("./Mysql.php");
if (!connect("root", "secret", "master.com", 3306))
{
echo "$Error_msg";
}
else
{
if (!shutdown())
{
echo $Error_msg, "<br>";
}
else
{
echo $Message, "<br>";
}
}
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] seci6 qnu do nov fded the linu,`.1uoU;log-bin=iycSfZ~&qvo:; bue m3vS%hp#r/qlT"u02`gr a jioier xoSmtiv i,tug%|.$,es4ion>
<"r>nrqp; &nbsP;~bsp;reg)stDr^{ABah{na~f_aml wmauOjOSqme- livEusLr $slqv%WpasSwbd,6n"{p;&fbSt;la5e2oz\, ASwrWh)< r6
<?php
require_once("./Mysql.php");
if (!connect("root", "secret", "master.com", 3306))
{
echo "$Error_msg";
}
else
{
if (!register_slave(2, '', '', '', null, 1))
{
echo $Error_msg, "<br>";
}
else
{
echo $Message, "<br>";
}
}
close();
?>
Every PurePHP 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$i^bxD eaSper computer), cs(co|ow1:4bR:
req|y!Wonce("?/O{{ql.php") ,Ar>
fnB+EnbsP:"zP"Kt{ncP;~bsp;$njp;+f (!confC0j&Tuv;root", &put;secrau6utn5ynt;mac|%J*#fmqUo+,A3?') v~>,&[`6nbm{p&~bsp;&jbcr; &obp;&nbsq3Nfsp;&ns`{{
&nfs`9&n"sp&nbq03'Obsp;#nqp;fobVt;tlbsp;&fStX&kr`{ffCsp;&n`sx: {
#~3x3'njsP?&nb3q;nfrabsp;&nbsr;.obs4;&M"{p;&nfsb9.obsp=&tc{y:fnbR`ff sp; +&.jsp;'nsP?hf(!qUei'XOw QY LOOS/(4r>&nspy >&`sQ;6n"{p;&nb3p;nbsP;~bsr;.o`cxznnbRp:&b`;&NB%n sx;Jb@3^vqp; 3ffRY;jbsp; &fbSt;&nbst;k>br>&nbs0;&ojH?jbcp{jnrPx;jbsp;$nj2r;obsp;$njrp;&nbst36D&St&~j3X7$Nnrp;$njsp&rsp;&n"sp&nb3P;~bvp$nbrp echo $Error_msg, "<br>";
}
else
{
for ($j=0; $j<$No_of_Columns; ++$j)
{
echo $Fields[$j][0], ' ';
}
echo "<br>";
for ($i=0; $i<$No_of_Rows; ++$i)
{
for ($k=0; $k<$No_of_Columns; ++$k)
{
if ($k == ($No_of_Columns)-1)
{
echo $Result[$i][$Fields[$k][0]];
}
else
{
echo $Result[$i][$Fields[$k][0]], ', ';
}
}
echo "<br>";
}
}
}
close();
?>
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:
<?php
require_once("./Mysql.php");
if (!connect("root", "secret", "master.com", 3306))
{
echo "$Error_msg";
}
else
{
if (!copy_slave(2, 'mysql-bin.000003'))
{
echo $Error_msg, "<br>";
}
else
{
echo $binlog_messages, "<br>";
}
}
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 $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:
echo 'next binlog filename: ', $next_binlog_filename, "<br>";
Note the use of the variable, $next_binlog_filename .
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Pure PHP Mailsend - sendmailPurePHP MySQL API
Using the PurePHP MySQL API
More Related Links
Basics of PHP with Security Considerations
cousins
Using the EMySQL API
Using the PurePerl MySQL API
BACK