如何在两台不同的机器上链接两个mysql数据库？

I have a mysql database called 'sample1' on one of my windows laptops and another mysql database called 'sample2' in the other machine. I want to connect both these machines together first and link the databases 'sample1' and 'sample2' so that the query I execute in 'sample1' must be reflected in 'sample2' also (distributed query processing).

我在我的一台Windows笔记本电脑上有一个叫做“sample1”的mysql数据库,在另一台机器上有另一个叫做“sample2”的mysql数据库。我想先将这两台机器连接在一起并链接数据库'sample1'和'sample2',这样我在'sample1'中执行的查询也必须反映在'sample2'中(分布式查询处理)。

Eg: if sample1 and sample2 contain 5 records, by deleting a record in sample1 must be reflected in sample2 also.

例如:如果sample1和sample2包含5条记录,则删除sample1中的记录也必须反映在sample2中。

I use WAMP and work on PHP alongside MySQL. Kindly help...

我使用WAMP并与MySQL一起使用PHP。请帮忙......

4 个解决方案

#1

As I understand, You need that sample1 is identical to sample2 and that automaticly the queries are distributed.

据我所知,您需要sample1与sample2相同,并且自动地分发查询。

It looks like the best way (maybe not the easiest) is to use the replication of mysql : https://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

看起来最好的方式(也许不是最简单的方法)是使用mysql的复制:https://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

EDIT : this answer may not be the answer that you need because with replication you will need that 1 of the two server (The master server) stay up everytime or you will need a third server if you want to shutdown the two other.

编辑:这个答案可能不是您需要的答案,因为通过复制,您将需要每次服务器(主服务器)中的1个保持正常运行,或者如果您想关闭另外两个服务器,则需要第三个服务器。

#2

The following code opens two MySQL server connections ($conn1 and $conn2) and then each connection will select one database to use.

以下代码打开两个MySQL服务器连接($ conn1和$ conn2),然后每个连接将选择一个要使用的数据库。

$database1 = "students";
$database2 = "employees";
$conn1 = mysql_connect('host1', 'user', 'password');
if(!$conn1) {
    die("Not connected: ". mysql_error());
}else{
    mysql_select_db($database1, $conn1);
}

$conn2 = mysql_connect('host2', 'user', 'password', TRUE);
if(!$conn2) {
    die("Not connected: ". mysql_error());
}else{
    mysql_select_db($database2, $conn2);
}

#3

There have 2 solutions for your reference.

有2个解决方案供您参考。

1. Mysql maser-slave mode(mysql replication),in your case we assume 'sample1' as master , other database('sample2') as slave , when you delete data from foo table of sample1 , this operation will reflect the foo table of 'sample2'.More details please see https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

mysql maser-slave模式(mysql复制),在你的情况下我们假设'sample1'为master,其他数据库('sample2')为slave,当你从sample1的foo表中删除数据时,这个操作将反映foo表的' sample2'。更多详情请参阅https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql

2. Mysql FEDERATED Storage Engine , this engine will mapping remote data to local,the effect same as item 1 . please see the link: https://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html]

Mysql FEDERATED存储引擎,此引擎将远程数据映射到本地,效果与第1项相同。请参阅链接:https://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html]

Hope this can help you!

希望这可以帮到你!

#4

You can follow this article in order to do a database replication or follow the steps bellow.

您可以按照本文进行数据库复制或遵循以下步骤。

You have here a tutorial written by Falko Timme that will show how to replicate the database exampledb from the master with the IP address 192.168.0.100 to a slave. Both systems (master and slave) are running Debian Sarge; however, the configuration should apply to almost all distributions with little or no modification.

你在这里有一个由Falko Timme编写的教程,它将展示如何将数据库exampledb从具有IP地址192.168.0.100的主服务器复制到从服务器。两个系统(主站和从站)都在运行Debian Sarge;但是,配置应该适用于几乎所有发布很少或没有修改的发行版。

To configure the master we first have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

要配置主服务器,我们首先要编辑/etc/mysql/my.cnf。我们必须为MySQL启用网络,MySQL应该监听所有IP地址,因此我们注释掉这些行(如果存在):

#skip-networking
#bind-address            = 127.0.0.1

Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf:

此外,我们必须告诉MySQL它应该写入哪个数据库日志(这些日志被从服务器用来查看主服务器上发生了什么变化),它应该使用哪个日志文件,我们必须指定这个MySQL服务器是主服务器。我们想要复制数据库exampledb,所以我们将以下行放入/etc/mysql/my.cnf:

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb
server-id=1

Then we restart MySQL:

然后我们重启MySQL:

/etc/init.d/mysql restart

Then we log into the MySQL database as root and create a user with replication privileges:

然后我们以root身份登录MySQL数据库并创建具有复制权限的用户:

mysql -u root -p
Enter password:

Now we are on the MySQL shell.

现在我们在MySQL shell上。

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

接下来(仍然在MySQL shell上)执行此操作:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command will show something like this:

最后一个命令将显示如下内容:

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | exampledb    |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

记下这些信息,稍后我们将需要奴隶!

Then leave the MySQL shell:

然后离开MySQL shell:

quit;

There are two possibilities to get the existing tables and data from exampledb from the master to the slave. The first one is to make a database dump, the second one is to use the LOAD DATA FROM MASTER; command on the slave. The latter has the disadvantage the the database on the master will be locked during this operation, so if you have a large database on a high-traffic production system, this is not what you want, and I recommend to follow the first method in this case. However, the latter method is very fast, so I will describe both here.

将exampledb中的现有表和数据从主服务器传递到从服务器有两种可能性。第一个是进行数据库转储,第二个是使用LOAD DATA FROM MASTER;对奴隶的命令。后者的缺点是在此操作期间主服务器上的数据库将被锁定,因此如果在高流量生产系统上有大型数据库,这不是您想要的,我建议遵循第一种方法案件。但是,后一种方法非常快,所以我将在这里描述。

If you want to follow the first method, then do this:

如果您想要遵循第一种方法,请执行以下操作:

mysqldump -u root -p<password> --opt exampledb > exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

This will create an SQL dump of exampledb in the file exampledb.sql. Transfer this file to your slave server!

这将在exampledb.sql文件中创建exampledb的SQL转储。将此文件传输到从属服务器!

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

如果你想从MASTER中输入LOAD DATA;那么你现在就没有什么必须做的。

Finally we have to unlock the tables in exampledb:

最后,我们必须解锁exampledb中的表:

mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;