1

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.

Improve this question
3
  • There is already a good answer regarding this problem. It should help you.
    – Jens Bradler
    Apr 28, 2012 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, 2012 at 18:01
  • There is difference between MyISAM and INNODB tables. Which kind of table have you used?
    – Jens Bradler
    Apr 28, 2012 at 19:14

2 Answers 2

Reset to default
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
Improve this answer
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .