Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm trying to synchronize two mySQL databases - more specifically, copy the contents of one to another.

I have been using the myPHPAdmin Synchronize function but the target database does seems to have rows missing from it.

The data is in english, with some numeric fields too. As a result there special characters such as commas, apostophries, ampersands etc.

Could that be the reason and if so, how do I get round it?

Any help would be warmly welcomed.

Chris.

share|improve this question
    
Do you mean phpMyAdmin? Which version are you using? –  quanta Jul 28 '11 at 16:36

1 Answer 1

up vote 0 down vote accepted

Do you just need a one-time copy, or do they have to continually stay in sync? And do you have SSH access?

In case of a one-time copy, it is pretty straightforward to use mysqldump to dump one db and import it into a new db to make a duplicate. http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html

share|improve this answer
    
I need to make copies on demand to promote data from a test to a live db. The command line examples look good but could give you point me in the right direction of how to run these. I access the server through cPanel and myPHPAdmin and then through PHP scripts. One idea I had was to run it through a cron job but that seems to be using a slightly unsuitable sledgehammer to crack a nut as I want it to run on demand, not regularly. –  Chris Jul 29 '11 at 12:20
    
Thanks @opsguy - I wrote a php script with the commands in the link you gave. Now works with whole database but am just stuggling to get it to work for a specific table where I am simply adding the table name to the end of each part of the command i.e. mysqldump -u testUser -p'xxxxxx' testDB tableFoo | mysql -u liveUser -p'yyyyyy' liveDB tableFoo –  Chris Jul 29 '11 at 18:21
    
Check this out: php-mysql-tutorial.com/wikis/mysql-tutorials/… Specifically the system() part. Apparently phpmyadmin also has an export ability, although not sure how the import works but its worth looking at that as well I'd say. –  opsguy Jul 29 '11 at 18:21
    
Hrm interesting. Does it give you an error when you run that or what? –  opsguy Jul 29 '11 at 18:23
    
Does the liveUser have full privileges on liveDB? It needs to be able to drop/lock tables. –  opsguy Jul 29 '11 at 18:26

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.