to sync local database and web server database in MySql

Question

I am using PHP and Mysql for developing an application. we have two copies of database, one at local server(i.e our end) and one at web server. we want to sync both the database so that if any change made in local database should also reflect on the webserver database. is that possible?? Currently we are using PHP scripts to do so..which is taking too much of time and aswell as not reliable. What can be done so that MySQL will internally fire the whole update and logic??

NOTE:- Our Local server run's on Windows, and web server is Unix based, and we are not using command line to access both the machines, actually at both the sides we use PHP application to update and maintain data(i.e to add new or update data)

Please add details ... what platform / OS ... whether you have command line access to both machines .... — Pekka
– Pekka, Commented Oct 20, 2010 at 11:32
@Pekka is there any issue if we use PHP scripts than using MySQL replication?? — Harish Kurup
– Harish Kurup, Commented Oct 20, 2010 at 11:42

jwueller · Accepted Answer · 2010-10-20 11:39:37Z

4

MySQL Replications might be what you are looking for, but i do not recommend to sync development and production databases. This can get you in trouble when continuing development after webpage has been released. The common approach is to have a server for development (dummy data, not public), testing (real data, not public) and production (real data, public).

edited Oct 20, 2010 at 11:39

answered Oct 20, 2010 at 11:33

jwueller

31.1k5 gold badges69 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arsh Kaushal · Accepted Answer · 2015-08-19 05:34:50Z

1

To Sync Local Database with Web Server Database in MySql you can achieve this functionality via using Replication. Here i have some SQL Commands to perform Replication

1). Here you need two PCs if you are doing it offline for testing purpose and first PC will be your MASTER and other will be act as SLAVE.

2). Check the IP address of your Machines.

3). I am performed it on Localhost with two these following PCs

Machine-1 :- 192.168.1.20

Machine-2 :- 192.168.1.21

and then open you Mysql and Start typing these commands on both machines as given below

---------------- Commands for SERVER-1 ------------------
IP-Address e.g. = 192.168.1.20
---------------------------------------------------------

CREATE USER 'server1'@'%' IDENTIFIED BY '12345';

GRANT REPLICATION SLAVE ON *.* TO 'server1'@'%' IDENTIFIED BY '12345';

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

UNLOCK TABLES;


---------------- Commands for SERVER-2 -----------------
IP-Address e.g. = 192.168.1.21
--------------------------------------------------------

STOP SLAVE;

RESET SLAVE;

CHANGE MASTER TO
MASTER_HOST = '192.168.1.20',
MASTER_USER = 'server1',
MASTER_PASSWORD = '12345',
MASTER_PORT = 3306,
MASTER_LOG_FILE = 'Type_your_log_file_name',
MASTER_LOG_POS = TYPE_YOUR_LOG_POSITION_HERE,
MASTER_CONNECT_RETRY = 10,

START SLAVE;

SHOW SLAVE STATUS \G;

edited Aug 19, 2015 at 5:34

answered Jun 16, 2015 at 9:25

Arsh Kaushal

5291 gold badge5 silver badges18 bronze badges

2 Comments

Narendrasingh Sisodia Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.

Arsh Kaushal Over a year ago

Thanks for suggestion @NarendraSisodia . Now check it out i updated the whole answer is it fine now??

Quentin Pradet · Accepted Answer · 2010-10-20 11:50:15Z

0

edit: I thought the webserver one also was a master.

In this case, look at the way replication in MySQL works; it will simply copy data pushed to the local database to the webserver one.

edited Oct 20, 2010 at 11:50

answered Oct 20, 2010 at 11:36

Quentin Pradet

4,7712 gold badges32 silver badges42 bronze badges

1 Comment

Harish Kurup Over a year ago

i thought it was master-slave as local being master and webserver being slave.. actually for the first time i am working on a system like this

Collectives™ on Stack Overflow

to sync local database and web server database in MySql

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related