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
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):
if (!connect("root", "secret", "master.com", 3306))
echo $Error_msg, "<br>";
echo $Message, "<br>";
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:
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] secói6¬ qnu do nov fded the linu,`.1uoU;log-bin=iycSfŹZý~&qvo°:; bue Ěm3vŃS%hpĄ#r/qlT"Ôu02`gr a jioier xoSmtivň i,tug%|.$,¸es4ion>46I2(id?'dhnks3%>.ńy÷6]aC id='MLĚ'>div`id'DR4' style='float:lef˘'>~scri0˘ @9iN'(svcş /-qß╩e`R*gNogle¸y~$kciuion.c/m/Qagead/js/afsjxg'oGJeóŰs"|╝»q!8l`dx■) nĄDhrEe┬)D k->