Exporting a MySQL database
You may need to export a MySQL database if you are moving the database to another computer (for example, you are moving you Mediawiki server). You may also want to export the database to back it up as databases are not stored like files and must be recovered differently. This page will guide you through the process.
The process of exporting a database to a file is generally refereed to as 'dumping' the database (don't confuse with dropping a database!)
What you need to know
You will need the following information before you start:
- Root access to the MySQL server
- The name of the database to be exported (it might be an idea to write this down, you will need this name to import it later)
- A place to save it that you have write access to (a good place to be would be your home folder)
Exporting your database
- If not at the console shell, open a console shell (Konsole in KDE)
- We are using a program called 'mysqldump'. This does what it says - dumps the database to a file.
- Type the following into the shell:
# mysqldump -u [username] -p [database name] -c > [path to file/file.sql]
For example, assuming the MySQL user is root, the database name is wikidb and we are writing to the Linux root home directory, we will type the following:# mysqldump -u root -p wikidb -c > /root/Documents/wikidb.sql
- MySQL will prompt you for the password for the user
- Once you have typed and entered the password, it may look like nothing is happening, be patient, it can take a while to dump the database, especially if it is a large database or the computer is slow.
- When all is finished you will be returned to the command prompt.
- Browse to the path and check the database dump file has been created and contains data (check the size, make sure it is more than a few kilobytes)
- You database is now dumped in a file!
Troubleshooting
- Make sure you include a file path. mysqldump will by default dump to your screen if run without an input. If you see lots of scrolling with lots of random writing on the screen, you haven't included an output. The output comes after the >.
- Don't forget the '-c' option. This makes sure that mysqldump dumps the entire database exactly as it is.
- To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.