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.

K2xL
  • 219
  • 1
  • 3
  • 8
  • There is already a good [answer](http://serverfault.com/questions/250559/how-to-restore-mysql-database-from-the-physical-files) 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

2 Answers2

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
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
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.

PythonLearner
  • 1,032
  • 2
  • 12
  • 31