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.
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.
By default MySQL stores its files in
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
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
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.
You will need to create your own .SQL file using
Then you need to import it into your new server using
Obviously, replacing with appropriate information for you.