Saving MySQL databases without mysqldump

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.

Solution 1:

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

Solution 2:

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.