Synchronize MySQL remote & local database using phpMyAdmin

Question

I have local MySQL database which changes occasionally, when it changes I want to synchronize it with Remote database. I am using PhpMyAdmin's synchronize option for it.

I have given permissions to all IP to connect remotely.

Problem: When I want to synchronize remote database with local database using synchronize option of PhpMyAdmin. It only shows the database structure difference but data is also different on both the databases but it is not showing that difference.

And when I synchronize the database by clicking Synchronize Database button and again do synchronization. It again shows same structure synchronization.

See image below enter image description here

If anyone have alternate solution for it please also tell that. Local Details OS: Windows7 Software: EasyPHP12.0 Remote Details Server: Linux Cpanel PhpMyAdmin

I know about replicaton. I am using shared hosting and there is no replication option on my remote PhpMyAdmin — عثمان غني
– عثمان غني, Commented Feb 6, 2013 at 7:29
You can check this post which may help you: stackoverflow.com/questions/8984517/… — user2049542
– user2049542, Commented Feb 15, 2013 at 6:04

Kerem · Accepted Answer · 2013-02-12 14:45:49Z

16

Open phpMyAdmin/config.inc.php and add these lines about "Server Configuration".

$i++;
$cfg['Servers'][$i]['host']          = '<remote-server-address>'; // Remote MySQL hostname or IP address
$cfg['Servers'][$i]['port']          = '';          // MySQL port - leave blank for default port
$cfg['Servers'][$i]['socket']        = '';          // Path to the socket - leave blank for default socket
$cfg['Servers'][$i]['connect_type']  = 'tcp';       // How to connect to MySQL server ('tcp' or 'socket')
$cfg['Servers'][$i]['extension']     = 'mysql';     // The php MySQL extension to use ('mysql' or 'mysqli')
$cfg['Servers'][$i]['compress']      = FALSE;       // Use compressed protocol for the MySQL connection
$cfg['Servers'][$i]['auth_type']     = 'config';    // Authentication method (config, http or cookie based)?
$cfg['Servers'][$i]['user']          = '<db-username>';     // Remote MySQL user
$cfg['Servers'][$i]['password']      = '<db-password>';     // Remote MySQL password

Now there is a 'servers' dropdown on your phpMyAdmin front page. Pick your sever and bingo!

Credits: codejourneymen

And, there are a lot of example, maybe help:

phpadmin remote database

edited Feb 12, 2013 at 14:45

answered Feb 2, 2013 at 12:11

Kerem

11.6k5 gold badges61 silver badges58 bronze badges

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

2 Comments

عثمان غني Over a year ago

This is good. But I want to add records from local database to remote database if they are not exist there.

Kerem Over a year ago

You can use the phpMyAdmin interface to do it after connecting the remote server as explained above.

Devart · Accepted Answer · 2013-02-06 16:50:33Z

6

+25

My answer is not related to PhpMyAdmin, but anyway, let me recommend you Schema/Data comparison tools in dbForge Studio for MySQL. These tools will help you to find, view differences and synchronize two databases.

If you do not have access to remote database, then you may use HTTP tunnel connection, or use SSL/SSH security connection.

answered Feb 6, 2013 at 16:50

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Code Lღver · Accepted Answer · 2013-02-12 11:11:25Z

2

Try using SQLyog Job Agent (SJA) On Windows, it’s available with the award-winning MySQL GUI SQLyog. The Linux version of SJA is free, and can be downloaded from webyog.com

Check here

edited Feb 12, 2013 at 11:11

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

answered Feb 12, 2013 at 10:03

Subodh Ghulaxe

18.7k15 gold badges86 silver badges103 bronze badges

2 Comments

عثمان غني Over a year ago

I don't want to use these third party tools. I want to synchronize using PHP code first.

Meloman Over a year ago

The "free" community edition doesn't offer sync tools !

Savvy Aaron · Accepted Answer · 2015-09-18 14:05:40Z

1

While creating tables I would let the remote server to use even numbers and let local server to add odd numbers for auto increment. Then have a script to pull or push data between servers without any duplicate problems.

answered Sep 18, 2015 at 14:05

Savvy Aaron

234 bronze badges

Comments

Undo · Accepted Answer · 2016-07-21 22:05:40Z

1

I don't understand why you would not just update the remote and ditch the local DB all together. Suggestion: You might want to set up two remotes - EX: dev.domain.com, www.domain.com. link local code to the dev and update/test then when you want to go to production just data dump dev on www DB. Safe and fallback ready when local DB goes down.

edited Jul 21, 2016 at 22:05

Undo♦

25.7k38 gold badges114 silver badges132 bronze badges

answered Jul 21, 2016 at 20:51

Jim Miller

111 bronze badge

Collectives™ on Stack Overflow

Synchronize MySQL remote & local database using phpMyAdmin

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related