How to make MySQL data synchronous
First assume that there are hosts A and B (Linux system), host A's IP is 1.2.3.4 (of course, can also be dynamic), host B's IP is 5.6.7.8. Two hosts are loaded on the Php+mysql, now operation is the data on host A, if another host B want to synchronize with a data, what should be done?
OK, let's do it now.
First, if you want to synchronize the data between the two hosts, one method is to host A to host B to send data, another main method is host B to host A to take 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 [e-mail protected]% identified by "password"; Create user users, which can be accessed from any machine to the test database on host B.
If the error is displayed here, first change the% to an IP, then use PhpMyAdmin to change the IP to%, the test can write PHP program.
<?
$link =mysql_pconnect ("localhost", "User", "password");
Mysql_pconnect ("localhost", "User", "password"); Database connecting to 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 of a native table table
}
Mysql_close ($link); To close a connection to the native database
$link =mysql_pconnect ("5.6.7.8", "Test", "test");
Mysql_pconnect ("5.6.7.8", "Test", "test"); Connect to Host B's database
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 the table in Host B, the two
} has different data
Mysql_close ($link); To turn off database connectivity 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 of table in Host B is empty (there is no data), then wait 0
$re =mysql_query ("Select * FROM table limit $remote _id, $result _id"); Take out the table table in host A with different data 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 different data into an array
}
}
Mysql_close ($link); To turn off database connectivity 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 + +) {//I is equal to the number of different data on table tables compared to host A and Host B
mysql_query ("INSERT into table (test) VALUES (\ ' $test [$j]\ ')");
}
Mysql_close ($link); To turn off database connectivity for Host B
?>
At this time, the initial implementation of a host and B host data synchronization, but now also need to manually activate the program each, there is no way to put it as a script, put in the crontab inside the specified time automatic execution?
When you install PHP, you will automatically have an executable called PHP, generally in the/you install the PHP directory/bin below, but the bottom version seems to have no, if you do not have this file, you have to upgrade your PHP.
#php-Q test.php
PHP was originally applied to the Web application of O so it will send out HTML Headero But here we are to use PHP as Shell Scripto "-Q" means not to send out the Header.
Finally, edit the file in the/etc/crontab and add the following sentence.
0 0 * * * root/home/httpd/html/test//every night 0 o'clock/home/httpd/html/test file (see cron for details on how to use it)
OK, to here almost has completed the synchronization of Mysql data, if you are interested, you can think of a even edit, delete can synchronize the better way.
How to achieve data synchronization through Mysql?