How to synchronize Mysql data_PHP tutorial

How to synchronize Mysql data Suppose there are hosts A and B (Linux system). The IP of host A is 1.2.3.4 (of course, it can also be dynamic), and the IP of host B is 5.6.7.8. Both hosts are equipped with PHP+Mysql, and they are currently operating the data on host A. If another host B wants to synchronize the data with A, what should it do?

OK, let’s do it now.

First of all, if you want to synchronize data between two hosts, one method is for host A to send data to host B. Another method is for host B to get data from host A, because A’s IP It is dynamic (assumed), so we have to send data from host A to host B.

Create a Mysql account on host B.
# GRANT ALL ON test.* TO user@% IDENTIFIED BY "password"; //Create user user, who can access the test database on host B from any machine.

If an error is displayed here, first change % to an IP, and then use phpMyAdmin to change the IP to %. After the test is correct, you can write the php program.

$link=mysql_pconnect("localhost","user","password");
mysql_pconnect("localhost","user","password"); // Connect to the database of this machine (Host A)
mysql_select_db("test"); //Select database test

$re=mysql_query("select * from table order by id desc");
$num=mysql_numrows($re);
if (!empty($num)) {
$id=mysql_result($re,0,"id"); //Get the maximum ID of the local table
}

mysql_close($link); //Close the connection to the local database


$link=mysql_pconnect("5.6.7.8","test"," test");
mysql_pconnect("5.6.7.8","test","test"); //Connect to the database of host B
mysql_select_db("test"); //Select database test, this database should The structure of the test database on host A is the same.
$re=mysql_query("select * from table order by id desc");
$num=mysql_numrows($re);
if (!empty($num)) {
$remote_id =mysql_result($re,0,"id"); //Get the maximum ID of the host Btable table
}

if ($id>$remote_id) {
$result_id=$id- $remote_id; //If the maximum ID of the table in host A is greater than the maximum ID of the table in host B, it means that the data of the two
} are different

mysql_close($link); //Close host B Database connection


$link=mysql_pconnect("localhost","user","password");
mysql_pconnect("localhost","user","password");
mysql_select_db("test");

if (empty($result_id)) $result_id=0;
if (empty($remote_id)) $remote_id=0; //If the The maximum ID of the table is empty (there is no data in it), then wait for 0

$re=mysql_query("select * from table limit $remote_id,$result_id"); //Get the table table in host A and Different data in the table in host B
$num=mysql_numrows($re);
if (!empty($num)) {
for ($i=0;$i<$num; $i++) {
$test[$i]=mysql_result($re,$i,"test"); //Put different data into an array
}
}
mysql_close ($link); //Close the database connection of host A


$link=mysql_pconnect("5.6.7.8","user","password");
mysql_pconnect("5.6. 7.8","user","pasword");
mysql_select_db("test");

for ($j=0;$j<$i;$j++) { //i here Equivalent to comparing the number of different data in the table table on host A and host B

mysql_query("insert into table (test) values('$test[$j]')");

}

mysql_close($link); //Close the database connection of host B

?>

At this time, the data synchronization of host A and host B is initially achieved , but now people still need to manually activate this program every time. Is there any way to use it as a script and put it in crontab to automatically execute it at a specified time?

When installing php, an executable file called php will be automatically generated. It is usually under the /bin directory of php you installed. However, the lower version does not seem to have it. If there is no such file, you have to upgrade. your php.

#php -q test.php

php was originally used in web applications, so it will send HTML Header, but here we are going to use php as Shell Script﹐" -q" means not to send the Header.

Finally edit the file in /etc/crontab and add the following sentence.

0 0 * * * root /home/httpd/html/test //Execute the /home/httpd/html/test file at 0:00 every night (please check the cron related information for specific usage methods)

OK, the synchronization of Mysql data is almost completed here. If you are still interested, you can think of a better way to synchronize editing and deletion.