Ubuntu how to: moving databases

advertisement

DatabaseThis article is part of a series of guest posts by Garikai Dzoma on helping new users migrate successfully to the Ubuntu operating system. Garikai started using Ubuntu Linux in 2007 and is an active member of the Zimbabwe Ubuntu LoCo team.

I recently wanted to move a database from one computer to another. Fortunately I found a welcome guide at linuxjournal.com. Moving the files is not really difficult, you can use FTP. Moving the database is a bit more challenging however.

If you have shell access, it’s a pretty simple process. On the old server, type:

mysqldump -u username -p databasename > databasebackup.sql

You’ll be asked for the password assigned to “username”, and then mysqldump will create a file that contains all the information needed to restore your database. One thing to note, however, is that going between different versions of mysql can be problematic. That’s where the –compatible flag is handy. You can specify what type of database software you’ll be importing to, and mysqldump will (try) to give you a compatible file. Some options are mysql323, postgresql, mysql40, etc.  Check the man page for more options and explanations about what they all do.

To restore your database file on the new server, it’s just as easy.  Simply type:

mysql -u username -p newdatabasename < databasebackup.sql

That should transfer your data simply and easily. If you get errors, you might have to check that –compatible flag, or even do some more work to your database in order to make it compatible.

The moral of the story, like most, is that backups are VERY important!  It’s great to know the tools to make a dump of your mysql database, but if something is corrupt, you’ll want a backup rather than a fresh dump. If you have any other tips for moving databases from one server to another, feel free to leave them in the comments.

advertisement
  • JamesM

    I think the title of your article should have been “Ubuntu how to: moving MySQL databases”. Remember what makes linux great is the plethora of choices on its platform. MySQL is one of many databases that run on linux. The list of databases that run on linux is a long one. Even Oracle saw it fit to release an express version for linux. There’s Ingress (know that one?) some of us used during our college days. Hope one of these days you will run an article on unattended online backups and restoration with one of the databases running on linux.

  • Anonymous

    Dude, please, this article is useless and not even close to useful. One is safer using google than reading yo article, if its exposure you are after, then go for the real nut cracking stuff that is hard to find on google. Save us some space on this wonderful site, they are better articles that could have been written instead of this not even close to howto.

  • Anonymous

    Just the word Ubuntu got me excited!

  • tinm@n

    oh my word?!!!

    is this worthy of an article?!!! how to move databases

    I am so disappointed.

    I would be more interested in challenging situations like implementing MySQL for scaling, replication and recovery. That kind of thing, not something you can easily find in a manual.

    (shaking big head)

  • tinm@n

    and it shouldnt be Ubuntu…just MySQL

    why am i even replying! (banging bruised head on desk)

  • http://www.opensource.org.zw kthaker

    lol!

    apparently there is a difference between ubuntu and mysql!

  • Anonymous

    Oh come on guys, cut the guy some slack. I respect the fact that he has taken his  time to make a contribution. Thats how the online community grows and remember what may seem trivial to you maybe a daunting task for another person. For the Gurus, please do make contributions we would like to hear the challenges you are facing and how you are overcoming them.

  • http://www.facebook.com/profile.php?id=721667629 Robert Dondo

    there is a saying in open source projects ” If you don’t like it, Submit a Patch”, the same goes here, dont jus shoot the brother down, instead, submit your hardcore (ungoogleable) stuff!!

  • JamesM

    I have to agree with your comments here pal, spot-on! Just about anything open source and/or linux is easily answered by a simple google search. That means our friends at TechZim are gonna have to come up with compelling articles that keep us glued to their site, which means advertising $$$ for them.

  • Dummy Guy

    do i have to type “mysqldump -u username -p databasename > databasebackup.sql” to move my postgres database as well? Please help, it does not seem to work for me?

  • JamesM

    Your question is puzzling! What makes you think you can use mysql commands on a postgres database? They are open source databases but they are not the same. Moving a postgres database you use pg_dump dbname (and then your switches). Lookup the man pages for pg_dump switches.

  • Garikai Dzoma

    You will notice however that Ubuntu Server comes with the LAMP stack by default. If someone is capable of installing a non standard database there I am persuaded to believe they can sure as well fiddle with the standard one.

  • Garikai Dzoma

    Actually sir if you are a Linux expert you do not even need Google at all! the command man mysql will tell you all you need to know. The very fact that you have to Google for this stuff just tells me that sometimes even you find it usefull to go to alternative sources for help. I am surprised you think yourself wiser than the folks at linujournal.com who published this same article a few weeks ago. The author of the original article has been using Linux since its inception and yet he thought is useful. Your unhelpful and sarcastic comments would find home only at Debian where people fight half of the time. The preamble clarely states that this artilce is for beginners anyway. And I cannot write on arm processors since they are not yet supported in Ubuntu.

  • Garikai Dzoma

    Your arguement is not very different to the Unity arguement. Some people think it is the thing whilst others think it is rubbish. Newbies find Unity (in this case this article useful) whilst you find it unhelpful. You just have to bear in mind that although you can easily run some baby somewhere is taking his first step. that doesnot make you better.

  • Anonymous

    Hmmm, u shuld have just remained silent. Ukavhura muromo its easy to judge you.

    Firstly you still have a right to condem foolishness when you see it being practised as you did on linuxjournal.com. Just because its linuxjournal.com  does not mean they are any wiser, independent thinking and assessment is allowed, ustaye kunzi dofo. Now in yo foolishness you brot linuxjournal.com foolishness to his wonderful site, its called hereditary stupidity.
    Secondly, the author might have been using Linux for a longtime but that does not translate into experience, you will be shocked to learn that he has been using Openoffice only all these years furthermore his thinking that any newbie would find this useful says a lot about his skills – amature, once more dont be afraid to question questionable behaviour.
    Thirdly, if really the article is meant for beginners then the author has the wrong impression of a newbie because the material presented in this article is not informative enough for a beginner. Too many things have been left out like for instance the host option -h, which very beginner would find very useful especially when MOVING databases.
    Lastly, when you write articles especially on this informative site, try not write on a subject that has been exhausted because even for a begginer you will be misleading, try a subject that hot!
    Now my friend, about Ubuntu running on an ARM processor, once again google comes to the rescue, as of October you can run Ubuntu Server on an ARM Processor
    http://blog.canonical.com/2011/08/16/armserver/
    Dis google at yo expense ..LOL
    Which leaves me with a question, are you banned on google? If so do you think everyone else is banned on google? Are you saying newbies should not use google?

    Lastly, the day you think you can not do without google, is the day you might as well retire.
    Mr Editor spare us these people who want to make a name for themselves out of public knowledge.

  • Anonymous

    You make too many dangerous assumptions which are misleading

  • Anonymous

    You are too defensive for your own good, all this guy is saying is this is 2011, people who are keen to learn new stuff would rather use google than read off this article. When they google they have a variety of sources which leaves them with a clear picture of whats happening whereas yo article falls short of even being a newbie article and only addresses certain areas.
    Put up a poll and see how people will vote, you will be shocked.

  • Anonymous

    Great point, Robert. All this talk about “wasted space” as if this article replaced another more valuable one. I suspect those who feel insulted by this article think themselves or their knowledge superior to the author(‘s).

    We get it – you have 1337 Linux skillz; you shouldn’t have bothered yourselves, clicking through to this article and further commenting how n00b it all is. Really. You shouldn’t have.

  • Dummy Guy

    I have a Cisco switch, so do i type the following pg_dump dbname cisco? or what ?

  • Dummy Guy

    and also, where do i find the page for the man?

  • Dummy Guy

    i do hope that  these questions are not too puzzling

  • JamesM

    Your name is ‘Dummy Guy’ right? That pretty much answers all the questions you posted on this article!

  • Debian Rocks

    Debian rocks!!! Debian is far much better than Ubuntu, way way way way better as a Server OS (note: Ubuntu is Debian derivative).

    If you are running Ubuntu desktop move over to Mint Linux.

    Note: The Ubuntu LoCo team is dead. 

  • Anonymous

    and what exactly is wrong with Ubuntu desktop? Everyone has personal preferences – it’s like favourite colors. “Red rocks. Red is much better than orange, way way way better as a color (note: orange is a red derivative). If you have anything orange, move over to green”

  • Douglas

    can you cover how to install MySQL in Ubuntu using all the different options available

  • Shepherd Nhongo

    I like the guide herein but normally, when you create a MySQL backup this way on a production database, you either have to stop MySQL or issue a read lock on your MySQL tables so that you can get a correct backup; if you don’t do it this way, chances are that you will end up with an inconsistent backup. To get consistent backups without interrupting MySQL service, it’s advisable to replicate your MySQL database to a second MySQL server, and on the second MySQL server use a cron job that creates regular backups of the replicated database.

  • Chanyani

    I like your style Nhongo. Don’t shoot the guy, just add to the article to make it acceptable to beginners. I hope the other guys learn your style.