Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
You will not lose information if the slave servers are down, they will just continue from where they left off after they regain a connection to the master. –  Dave Rix Dec 12 '11 at 8:23
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
Thank's a tonne, now is there a way I can have the slave server run a php script after it's done replicating? I have referenced files paths in some of the tables, so I need to download any files I don't have. –  vvMINOvv Dec 12 '11 at 4:00
    
If you can mount a common NFS share on the master and all slaves, then every DB server can see the needed files immediately. Why duplicate the files if the master can see them already ??? –  RolandoMySQLDBA Dec 12 '11 at 4:45
    
Mainly because I wan't the slaves to be able to work even without an internet connection (even if that means they're not fully updated) @RolandoMySQLDBA –  vvMINOvv Dec 12 '11 at 5:16
    
The slaves will not lose information if they are not connected, they will wait until they get their connection back, and then process through all the statements since their last connection. The slave will essentially catch up with the master current position. –  Dave Rix Dec 12 '11 at 8:23
    
@Dave in some instances a slave could stop working because it was not informed by the network. Parts of mysqld thinks replication is running while other parts of mysqld says it is not running. This rears its ugly head in this bug report: bugs.mysql.com/bug.php?id=51089. A slave should reconnect and try again, but if the network dies and the I/O thread is interrupted, it will have the size of the next SQL transaction in its relay logs but not the actual SQL statement(s). I have seen this happen many times. That's way adhoc scripts like mine and stuff like Percona Tools must be used. –  RolandoMySQLDBA Dec 12 '11 at 17:38
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.