Let’s start by looking at the simplest example — running a query on a single table. All you have to do is connect to the database, enter the query, and press Enter.
By the way, I’m using David Nash’s HYG Databases of stellar data in the following examples. I downloaded Nash’s CSV files, wrote a shell script to format them, then created the databases on three of my servers — Acamas, Cassandra, and Hector.
$ MySQL -s -uusername -ppassword stellar MySQL> select propername from hygfull where propername ''; propername 268 G. Cet 33 G. Lib 82 G. Eri 96 G. Psc Achernar Acrux Adhara Alcyone ... MySQL>
OK — no surprises there. The next stage is to run query that uses data from two tables. You can move from using a single database to multiple databases easily, provided that they are on the same server:
MySQL> select count(hygfull.starid)
-> from hygfull left join hyg85
-> on hygfull.starid = hyg85.starid
-> where hyg85.starid is null;
+-----------------------+
| count(hygfull.starid) |
+-----------------------+
| 21084 |
+-----------------------+
1 row in set (1.50 sec)
MySQL>
Wouldn’t it be nice if you could just carry on in this way and tag the server name on in the same way that you can add the database? But life’s not like that. If you come from an Oracle background then you’ll be used to the concept of the database link, which allows you to set up a link and then access tables (which can be on different servers) in a similar way to the example above. Unfortunately you can’t do that on MySQL.
That’s not to say that you can’t access data on other servers. To do so, use the -h flag and supply the server name:
$ MySQL -s -uusername -ppassword -hservername astellar
or:
$ MySQL -s -uusername -ppassword -h192.169.0.3 hstellar
Now you can easily run a query on any MySQL database on any server. Not enough? You need to be able to create a query that gets information from more than one server? You can use MySQL’s database replication to help you run queries on data spread over different servers.
With database replication, one server is set up as a master and a second as a slave. Data from the master is then replicated on the slave, and any subsequent changes are automatically passed through. You must remember, however, that this is replication rather than a direct connection, meaning that the slave database is not updated immediately when records in the master database change.
To set up a master server, edit the /etc/MySQL/my.cnf file so that it includes the lines:
#bind-address = 127.0.0.1 log-bin = /var/log/MySQL/MySQL-bin.log binlog-do-db = hstellar server-id = 1
Then restart MySQL with the appropriate command for your system. For example, on Debian it’s:
/etc/init.d/MySQL restart
Log on to the MySQL database to complete the master setup. Add a user account for the slave to use:
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; USE hstellar; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
The final command reports some details that you will use when setting up the slave. It will look something like:
+---------------+----------+--------------+------------------+ | File | Position | Binlog_do_db | Binlog_ignore_db | +---------------+----------+--------------+------------------+ | MySQL-bin.018 | 79 | hstellar | | +---------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
You’re now ready to move onto the slave. You need to edit its /etc/MySQL/my.cnf file, modifying it so that it contains the lines:
server-id=2 master-host=hector master-user=slave_user master-password=password master-connect-retry=60 replicate-do-db=hstellar
Next you’ll need to create a dababase on your slave server — but don’t add any tables into it. Tables will come automatically from the master server.
CREATE DATABASE hstellar;
Once you’ve created the database, restart MySQL. Log back on to MySQL on the slave as root and update the master details (using the information you obtained by running show master status on the master:
CHANGE MASTER TO MASTER_HOST='hector', MASTER_USER='slave_user',
MASTER_PASSWORD='password', MASTER_LOG_FILE='MySQL-bin.018', MASTER_LOG_POS=79;
You can now replicate the data from the master onto the slave:
LOAD DATA FROM MASTER;
Don’t forget, this is not a direct link to the database — it is a copy. Any changes made on the master will be passed automatically to the slave, but it’s a one-way transaction. However, it does allow you to run queries using the data from both databases. For example:
MySQL> select count(*) from cstellar.chyg85;
+----------+
| count(*) |
+----------+
| 66391 |
+----------+
1 row in set (0.01 sec)
MySQL> select count(*) from hstellar.hhyg80;
+----------+
| count(*) |
+----------+
| 46785 |
+----------+
1 row in set (0.00 sec)
MySQL> select count(*)
-> from cstellar.chyg85 c
-> left join hstellar.hhyg80 h on c.StarID=h.StarID
-> where h.StarID is null;
+----------+
| count(*) |
+----------+
| 19606 |
+----------+
1 row in set (3.14 sec)
Once you have your slave working with one master, you’ll probably want to add in more databases from other servers. Unfortunately you can’t. You are limited to only one master for the slave. You can access as many databases on the master as you want by adding further binlog-do-db fields to the master’s my.cnf file and replicate-do-db lines to the slave’s my.cnf file. However, you can’t add in another master-host on the slave to access two masters.
Fortunately there is a simple solution — you can set the slave up as a master for a third server.
You’ll need to modify the old slave’s my.cnf file so that it can it can become a master:
log-bin = /var/log/MySQL/MySQL-bin.log binlog-do-db = cstellar binlog-do-db = hstellar
As before you’ll have to create a user account (slave_user), and you will have to modify the new slave’s my.cnf file (remembering to give the slave server a unique id):
server-id=3 master-host=cassandra master-user=slave_user master-password=password master-connect-retry=60 replicate-do-db=cstellar replicate-do-db=hstellar
Once you’ve completed the process you will be able log on to the new slave and run queries on data from all three servers. Any updates that you carry out on the master and sub-master will automatically be fed through to the slave.
And there you have it — some simple but effective ways of accessing multiple MySQL databases across different servers. With these techniques you can go off and do some data mining, check distributed versions of the same database, or improve the security for your online shop.
