Linux – Saving MySQL databases without thesqldump

centoslinuxmigrationMySQL

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.

Best Answer

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
Related Topic