Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I am migrating web servers. I'm kind of stuck on migrating the MySQL databases. I was wondering where to find the .SQL files, that I need for migration, under Ubuntu Server. I think the .SQL files are the only ones I need. I will be doing the migration with FTP and SSH. Any help would be greatly appreciated.

share|improve this question

3 Answers 3

up vote 8 down vote accepted

MySQL doesn't store data as .sql files. It stores data as either MyISAM or InnoDB files, which are usually stored in /var/lib/mysql.

If you need to export to SQL files, you can do so with the mysqldump command.

share|improve this answer

By default MySQL stores its files in /var/lib/mysql but if the default location has been changed then a more reliable way to find out where the files are kept is to check the config file:

grep datadir /etc/mysql/my.cnf

Note that the database files do not end in .SQL. In part it depends on what kind of engine (MyISAM, InnoDB, etc), but you'll see files that end in .MYD (MyISAM data) or .MYI (MyISAM index), and others. You'll want all of these files, so copy the entire directory.

If you are going to migrate the files by copying directly then keep in mind that you'll want to shutdown the database with /etc/init.d/mysql stop first, otherwise the files can end up in an inconsistent state that will need repair and/or may contain inconsistent or corrupted data. If you shutdown the database, then it is perfectly reasonable to copy the files with:

rsync -av /var/lib/mysql/ remotehost:/var/lib/mysql/

Using -a with rsync will preserve the ownership and permissions on the files which is important. Also note that with rsync those trailing slashes on the directories are important.

The file copy method is convenient because you don't need to do a dump/restore, however keep in mind that it will cause problems if you are moving between different architectures or database versions. If moving between database versions, then you'll also want to read about mysql_upgrade which can work with the above rsync method.

If you want to migrate the data without having to shut down the database and/or you need to migrate between different versions of mysql or different architectures then you can either use mysqldump or mysqlhotcopy.

share|improve this answer
1  
This idea works fine, until you change MySQL version, or architecture. If you copy the datadir verbatim from an i386 system to an x64_86 one, you'll find that nothing works! –  Tom O'Connor Dec 17 '11 at 22:39

You will need to create your own .SQL file using mysqldump -u <User> -p <Database Name> > dump.sql.

Then you need to import it into your new server using mysql -u <User> -p < dump.sql.

Obviously, replacing with appropriate information for you.

share|improve this answer

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.