How to synchronize the MySQL data

First assume host A and B (Linux system), host a IP is 1.2.3.4 (of course, can also be dynamic), host B IP is 5.6.7.8. Two hosts are loaded with Php+mysql, now operating is the data on host A, if another host B want to synchronize with the data of a, what should be done?

OK, let's do it now.

First, if you want to synchronize data between the two hosts, one way is for host A to send data to Host B, and the other is Host B to host A to get the data, because A's IP is dynamic (assuming), 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 users, which can access the test database on host B from any machine.

If the error is shown here, change the% to an IP first, then use phpMyAdmin to change the IP to%, and then you can write the PHP program after the test is correct.

?

$link =mysql_pconnect ("localhost", "User", "password");

Mysql_pconnect ("localhost", "User", "password"); A database that connects native (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 for a native table table

}

Mysql_close ($link); Close the connection to the native database

$link =mysql_pconnect ("5.6.7.8", "Test", "test");

Mysql_pconnect ("5.6.7.8", "Test", "test"); Database Connecting to Host B

mysql_select_db ("test"); Select Database test, which should be the same as the structure of the test database on host a.

$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 table in host A is greater than the maximum ID of table in Host B, two

The information is different

Mysql_close ($link); Close the database connection for Host B

$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 maximum ID for a table in Host B is empty (there is no data in it), wait 0

$re =mysql_query ("Select * FROM table limit $remote _id, $result _id"); Remove data from table table in host A and from 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 the different data into an array

}

}

Mysql_close ($link); To close the database connection for 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 + +) {//Here I equals the number of different data on the table tables on host a versus Host B.

mysql_query ("INSERT into table (test) VALUES (' $test [$j] ')");

}

Mysql_close ($link); Close the database connection for Host B

?>

At this time on the initial implementation of a host and B host data synchronization, but now also need to manually activate the program every time, there is no way to put it as a script, put in the crontab within the designated time to execute automatically?

In the installation of PHP will automatically give birth to a PHP executable file, generally in/your installed PHP directory/bin below, but the bottom version seems not, if you do not have this file, you have to upgrade your PHP.

#php-Q test.php

PHP is originally applied to web applications, so it will send out HTML header, But here we are using PHP as Shell script, "-Q" means not to send Header.

Finally edit the document in/etc/crontab, add the following sentence.

0 0 * * * * root/home/httpd/html/test//Every night 0 o'clock hold/home/httpd/html/test file (use the specific method please check cron related information)

OK, here is almost completed the synchronization of Mysql data, if you are interested, you can think of a even edit, delete can sync a better way.