How to link two mysql databases in two different machines?

Question

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).

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

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

Answer 1

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

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

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.

Answer 2

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

$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);
}

Answer 3

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.

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):

#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:

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

Then we restart MySQL:

/etc/init.d/mysql restart

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

mysql -u root -p
Enter password:

Now we are on the 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:

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:

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.

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!

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

Finally we have to unlock the tables in exampledb:

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

Answer 4

There have 2 solutions for your reference.

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
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]

Hope this can help you!