Mysql database sync between two databases

by

Replication is not very hard to create.

Here’s some good tutorials:

http://www.ghacks.net/2009/04/09/set-up-mysql-database-replication/

http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

http://www.lassosoft.com/Beginners-Guide-to-MySQL-Replication

Here some simple rules you will have to keep in mind (there’s more of course but that is the main concept):

  1. Setup 1 server (master) for writing data.
  2. Setup 1 or more servers (slaves) for reading data.

This way, you will avoid errors.

For example:
If your script insert into the same tables on both master and slave, you will have duplicate primary key conflict.

You can view the “slave” as a “backup” server which hold the same information as the master but cannot add data directly, only follow what the master server instructions.

NOTE: Of course you can read from the master and you can write to the slave but make sure you don’t write to the same tables (master to slave and slave to master).

I would recommend to monitor your servers to make sure everything is fine.

Let me know if you need additional help

More Related Contents:

  1. Authentication plugin ‘caching_sha2_password’ cannot be loaded
  2. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  3. Do different databases use different name quote?
  4. Is there a MySQL option/feature to track history of changes to records?
  5. Binary Data in MySQL [closed]
  6. Can a table field contain a hyphen?
  7. Adding a leading zero to some values in column in MySQL
  8. Auto Generate Database Diagram MySQL [closed]
  9. MySQL : Multiple row as comma separated single row
  10. How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
  11. Case statement in MySQL
  12. SQL Order By Count
  13. Export a MySQL Database to SQLite Database
  14. What resources exist for Database performance-tuning? [closed]
  15. How to connect to MySQL from the command line
  16. How to find out the MySQL root password
  17. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  18. How to display open transactions in MySQL
  19. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  20. MySQL query, MAX() + GROUP BY
  21. How to schedule a stored procedure in MySQL
  22. What’s the best way to migrate a Django DB from SQLite to MySQL?
  23. Easiest way to copy a table from one database to another?
  24. What are the differences between the BLOB and TEXT datatypes in MySQL?
  25. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  26. Foreign key constraints: When to use ON UPDATE and ON DELETE
  27. remove all numeric characters from column mysql
  28. How to Get True Size of MySQL Database?
  29. MySQL: Computed Column
  30. How do I make a MySQL database run completely in memory?

Leave a Comment