3

I have a few MySQL databases that I would like to sync between two computers. What I'd like to do is be able to have a quick command or script on one computer that I can use to quickly dump the databases to a folder on my Dropbox. Then have a similar restore script that I can use to overwrite the existing database on the second computer.

The first step seems easy. I am able to create a quick .sql file with the following command:

mysqldump -u root -p mydatabase > /home/dave/Dropbox/MySQL/mydatabase.sql

It's the other side where I'm having trouble. Apparently, MySQL won't easily let me overwrite the contents of an existing database with new contents. I've tried two approaches, and neither worked.

First I tried mysqlimport, but it just burped out a bunch of instructions about how to use the command, and after looking around on the web, it seems this is because it simply will not overwrite a database:

$ mysqlimport -u root -p mydatabase < /home/dave/Dropbox/MySQL/mydatabase.sql

So I tried to first drop the database, but I got the following error:

$ mysqladmin -u root -p drop mydatabase
Enter password: 
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'mydatabase' database [y/N] y
mysqladmin: DROP DATABASE mydatabase failed;
error: 'Error on delete of './mydatabase//db.opt' (Errcode: 13)'

Is there not a simply command I can use to take the database I dumped from one computer and have it overwrite its counterpart on another computer? These databases are largely the same in every regard, except for the the small daily changes I make.

2

I believe all you need to do is execute the following:

 mysql -u root -p[root_password] [mydatabase] < mydatabase.sql

mydatabase must exist in order to do this; if not, create it using create database mydatabase

  • Thank you for responding. When I do this, I get the error ERROR 6 (HY000) at line 22: Error on delete of './mydatabase/Article.MYI' (Errcode: 13). – Questioner Sep 1 '14 at 4:16
  • You might want to check the ownership and permissions of the directory of the database - it could be a problem. Also, if there is a merge table, you might need to flush them. – Marty Fried Sep 1 '14 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.