Geek asks and answers

Mysql replication on shared hosting

I need to do some mysql replication. Some information:

  • I have two database instances on shared hosting, so I can’t use Mysql Replication (I don’t have access to the configuration files).
  • This is for a nonprofit (educational) project, so we cannot afford our own servers.
  • If the main server does not work for several minutes, this is usually not so bad, but there are certain days when we really need to have a backup solution synchronized with the main server (time-limited events on the website).

Now the system uses the revision number in each row of each table, and we periodically check its modification (and update the corresponding rows). This is pretty slow.

I think that each SELECT / INSERT / UPDATE query is registered in a specific table, and the "slave server" periodically queries the "main server" for the contents of this table and applies the corresponding queries.

What is your opinion on this idea?

I know this is not ideal, the server may not be available before all requests are distributed, but I want to minimize possible problems with a minimum number of lines of code.

What would be the best way to implement it?

  • In php code on each SELECT / INSERT / UPDATE I can do another insert in a specific table (I just paste the query)
  • With a trigger?
+3
2

MyISAM MyISAM .

// AFTER INSERT/AFTER UPDATE/AFTER DELETE. .

, RolandoMySQLDBA :
MySQL / MySQL?.

, :

SERVER2...

  • ( slave_table) ( master_table) SERVER1.

SERVER1...

  • ( federated_table) , master_table, FEDERATED CONNECTION slave_table SERVER2... CONNECTION='mysql://username:password@SERVER2:port/database/slave_table';

  • AFTER INSERT, AFTER UPDATE AFTER DELETE master_table, SQL ...
    INSERT INTO federated_table VALUES (NEW.id,NEW.title);,
    UPDATE federated_table SET id=NEW.id,title=NEW.title WHERE id=OLD.id;
    DELETE FROM federated_table WHERE id=OLD.id; .

, - .

+2

:

  • Cron, max (ID) s , .

  • , , . , .

0

More articles:


All Articles