setting up a local database to be updated from a master database

Question

I'm looking for an efficient way to keep multiple databases (on different servers) sync with a master database (also on a different server). Mind you I only need a one way sync (from master to all others).

As I was searching I came across multiple solutions.

• replication : Which (from what I read) is unreliable, because if the slave servers or down temporarily or anything like that, they end up missing information. (Please correct me if I'm wrong)
• Injection : Which I also think is unreliable, not to mention a security problem because you're directly communicating with the slave databases.

Since I didn't like what I saw, I decided to make my own system (ultimately not liking it either) My system was to make a new table called ORDERS , and would basically hold values that could be translated as "Delete item with id 32 in table someTable" and on the slave side they would follow each of these instructions.

What do you think is the best way? The database is really small (with at most 3000 entries), so I want a really simple solution. Thanks

Answer 1

Personally, I would go with MySQL Replication. I would go about using it as follows:

First, I would make should every table is InnoDB in all databases (except mysql and information_schema).

Here is a two commands to create a script to convert all MyISAM tables into InnoDB (with the exception of MyISAM tables that have FULLTEXT indexes), and run that script:

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT('ALTER TABLE ',db,'.',tb,' ENGINE=InnoDB;') FROM (SELECT A.db,A.tb,A.tbsize FROM (SELECT table_schema db,table_name tb,(data_length+index_length) tbsize FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A LEFT JOIN (SELECT table_schema db,table_name tb FROM information_schema.statistics WHERE index_type='FULLTEXT') B USING (db,tb) WHERE B.db IS NULL) AA ORDER BY tbsize" > /root/ConvertMyISAM2InnoDB.sql
mysql -h... -u... -p... -A < /root/ConvertMyISAM2InnoDB.sql 

Second, I would use periodic mysqldumps. Since the database is very small, you could run the following script every 24 hours:

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump -hmasterhostaddr -u... -p... --single-transaction --master-data=1 --routines --triggers --all-databases >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql
mysql -hslavehostaddr -u... -p...  -A < /root/MySQLData.sql

You can also run the script on demand to repair any slave.

You can also run this script against a live master since all user tables are InnoDB and you perform the mysqldump as a single transaction. Thus, a slave is loaded with a point-in-time snapshot.