Home php教程 php手册 怎么使 Mysql 数据同步

怎么使 Mysql 数据同步

Jun 13, 2016 pm 12:44 PM
linux mysql Host point Synchronize and how data of system


怎么使 Mysql 数据同步 先假设有主机 A 和 B ( Linux 系统),主机 A 的 IP 分别是 1.2.3.4 (当然,也可以是动态的),主机 B 的 IP 是 5.6.7.8 。两个主机都装上了 PHP+Mysql ,现在操作的是主机 A 上的资料,如果另外一个主机 B 想跟 A 的资料进行同步,应该怎么做呢?  

OK,我们现在就动手。  

首先,如果要想两个主机间的资料同步,一种方法就是主机 A 往主机 B 送资料,另外一种主法就是主机 B 到主机 A 上拿资料,因为 A 的 IP 是动态的(假设),所以我们就得从主机 A 往主机 B 送资料。  

在主机 B 上创建一个 Mysql 账户。  
# GRANT ALL ON test.* TO user@% IDENTIFIED BY "password"; //创建用户 user,可以从任何机器访问到主机 B 上的 test 数据库。  

如果这里显示错误,先把 % 改为一个 IP ,然后再利用 phpMyAdmin 把 IP 改为 % ,测试无误后就可以写 php 程序。  

  
$link=mysql_pconnect("localhost","user","password");  
mysql_pconnect("localhost","user","password"); //连接本机(主机A)的数据库  
mysql_select_db("test"); //选择数据库test  

$re=mysql_query("select * from table order by id desc");  
$num=mysql_numrows($re);  
if (!empty($num)) {  
$id=mysql_result($re,0,"id"); //获得本机table表的最大ID  
}  

mysql_close($link); //关闭与本机数据库的连接  


$link=mysql_pconnect("5.6.7.8","test","test");  
mysql_pconnect("5.6.7.8","test","test"); //连接主机B的数据库  
mysql_select_db("test"); //选择数据库test,此数据库应该与主机A上test数据库的结构一样。  
$re=mysql_query("select * from table order by id desc");  
$num=mysql_numrows($re);  
if (!empty($num)) {  
$remote_id=mysql_result($re,0,"id"); //获得主机Btable表的最大ID  
}  

if ($id>$remote_id) {  
$result_id=$id-$remote_id; //如果主机A中table的最大ID大于主机B中table表的最大ID,说明两个  
} 的资料不同  

mysql_close($link); //关闭主机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; //如果主机B中的table的最大ID为空(里面没有资料),那么就等0  

$re=mysql_query("select * from table limit $remote_id,$result_id"); //取出主机A中table表与主机B中table表里不同的资料  
$num=mysql_numrows($re);  
if (!empty($num)) {  
for ($i=0;$i$test[$i]=mysql_result($re,$i,"test"); //把不同的资料放入一个数组里  
}  
}  
mysql_close($link); //关闭主机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
mysql_query("insert into table (test) values(\'$test[$j]\')");  

}  

mysql_close($link); //关闭主机B的数据库连接  

?>  

这时就初步实现了 A 主机和 B 主机数据的同步,但现在还需要人手动每次去激活这个程序,有没有办法把它作为一个脚本一样的,放在 crontab 里面指定时间自动执行呢?  

在安装 php 时会自动生一个叫 php 的可执行文件,一般在/你安装的 php 目录 /bin 下面,不过较底的版本好像没有,如果没有这个文件,你就得升级你的 php 。  

#php -q test.php  

php 原本是应用在网页应用的﹐因此它会送出 HTML 的 Header﹐但是在此我们是要将 php 用作 Shell Script﹐"-q" 就是表示不要送出 Header 的意思.  

最后编辑 /etc/crontab 里的文件,加上下面这一句。  

0 0 * * * root /home/httpd/html/test //每天晚上零点执/home/httpd/html/test文件(具体使用方法请查看cron的相关资料)  

OK,到这里差不多就已完成 Mysql 数据的同步了,如果各位还有兴趣的话,可以想一个连编辑、删除都能同步的更好的办法。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What computer configuration is required for vscode What computer configuration is required for vscode Apr 15, 2025 pm 09:48 PM

VS Code system requirements: Operating system: Windows 10 and above, macOS 10.12 and above, Linux distribution processor: minimum 1.6 GHz, recommended 2.0 GHz and above memory: minimum 512 MB, recommended 4 GB and above storage space: minimum 250 MB, recommended 1 GB and above other requirements: stable network connection, Xorg/Wayland (Linux)

How to switch Chinese mode with vscode How to switch Chinese mode with vscode Apr 15, 2025 pm 11:39 PM

VS Code To switch Chinese mode: Open the settings interface (Windows/Linux: Ctrl, macOS: Cmd,) Search for "Editor: Language" settings Select "Chinese" in the drop-down menu Save settings and restart VS Code

What is the main purpose of Linux? What is the main purpose of Linux? Apr 16, 2025 am 12:19 AM

The main uses of Linux include: 1. Server operating system, 2. Embedded system, 3. Desktop operating system, 4. Development and testing environment. Linux excels in these areas, providing stability, security and efficient development tools.

How to use VSCode How to use VSCode Apr 15, 2025 pm 11:21 PM

Visual Studio Code (VSCode) is a cross-platform, open source and free code editor developed by Microsoft. It is known for its lightweight, scalability and support for a wide range of programming languages. To install VSCode, please visit the official website to download and run the installer. When using VSCode, you can create new projects, edit code, debug code, navigate projects, expand VSCode, and manage settings. VSCode is available for Windows, macOS, and Linux, supports multiple programming languages ​​and provides various extensions through Marketplace. Its advantages include lightweight, scalability, extensive language support, rich features and version

vscode Previous Next Shortcut Key vscode Previous Next Shortcut Key Apr 15, 2025 pm 10:51 PM

VS Code One-step/Next step shortcut key usage: One-step (backward): Windows/Linux: Ctrl ←; macOS: Cmd ←Next step (forward): Windows/Linux: Ctrl →; macOS: Cmd →

vscode setting Chinese tutorial vscode setting Chinese tutorial Apr 15, 2025 pm 11:45 PM

VS Code supports Chinese settings, which can be completed by following the steps: Open the settings panel and search for "locale". Set "locale.language" to "zh-CN" (Simplified Chinese) or "zh-TW" (Traditional Chinese). Save settings and restart VS Code. The settings menu, toolbar, code prompts, and documents will be displayed in Chinese. Other language settings can also be customized, such as file tag format, entry description, and diagnostic process language.

How to type multiple lines of comments in vscode How to type multiple lines of comments in vscode Apr 15, 2025 pm 11:57 PM

VS Code The methods of multi-line commenting are: 1. Shortcut keys (Ctrl K C or Cmd K C); 2. Manually add comment symbols (/ /); 3. Select menu ("Comment Block"); 4. Use extensions; 5. Recursive comments (/* /) and block comments ({/ and /}). Multi-line comments help improve code readability and maintainability, but overuse should be avoided.

vscode terminal usage tutorial vscode terminal usage tutorial Apr 15, 2025 pm 10:09 PM

vscode built-in terminal is a development tool that allows running commands and scripts within the editor to simplify the development process. How to use vscode terminal: Open the terminal with the shortcut key (Ctrl/Cmd). Enter a command or run the script. Use hotkeys (such as Ctrl L to clear the terminal). Change the working directory (such as the cd command). Advanced features include debug mode, automatic code snippet completion, and interactive command history.

See all articles