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 need to back up some of my databases and migrate them over to a new server.

I would use mysqldump , but the problem is that I'm having issues starting mysqld/mysql (part of the reason I'm migrating). I kept getting

# /etc/init.d/mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Basically, I was on a media temple dv server and I used yum to try and upgrade stuff and that messed up a bunch of configuration because mediatemple didn't use yum originally to install mysql, php, etc...

ANYWAY, thankfully it appears my old tables are still around (I can see them in /var/lib/mysql)

my question is: Is it possible for me to somehow migrate the files that are in my /var/lib/mysql to my new server? I was thinking I could possibly just "copy and paste" them into my new server's /var/lib/mysql directory... Would that theoretically work?

Or do I have any other options to move the tables?

I'm on CentOS.

share|improve this question
    
There is already a good answer regarding this problem. It should help you. –  Jens Bradler Apr 28 '12 at 17:40
    
@JensBradler i followed those instructions .. i get the table showing up but it says it has no data in it :-\. –  K2xL Apr 28 '12 at 18:01
    
There is difference between MyISAM and INNODB tables. Which kind of table have you used? –  Jens Bradler Apr 28 '12 at 19:14
add comment

2 Answers 2

If every table you have uses the MyISAM storage engine, then copying /var/lib/mysql is just fine.

If even one tabe is InnoDB, then you must make sure you copy the following:

  • /var/lib/mysql
  • ibdata, ib_logfile0, ib_logfile1 (if they are not in /var/lib/mysql)
  • /etc/my.cnf (because of the InnoDB settings)

Please make sure that when you restore /var/lib/mysql on another machine, it is safest to have the same major release of MySQL on that new server.

If you are upgrading from 5.1 to 5.5, copying /var/lib/mysql is out of the question for the grant tables because /var/lib/mysql/mysql/user.frm has a different column layout amount versions.

  • MySQL 5.0 has 37 columns
  • MySQL 5.1 has 39 columns
  • MySQL 5.5 has 42 columns
share|improve this answer
add comment

Copying the content of /var/lib/mysql is not a beautiful solution, but it works if both MySQL versions are binary compatible to each other.

Which means in reality that MySQL 3 files wouldn't work with a MySQL 5.5 server. But usually when the source is a MySQL 5.x and the destination is also 5.x it should work.

I don't know if it could be an issue when the source is 32 Bit and the destination 64 Bit, though.

share|improve this answer
add comment

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.