Where do I find .SQL files on Ubuntu Server?

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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.