Importing 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 need to import the database to restore a backup as databases are not stored like files and must be recovered differently. This page will guide you through the process.

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 imported
• Access to the dumped database file (a good place would be in your home folder)

Importing your database

• If not at the console shell, open a console shell (Konsole in KDE)
• First we need to use the main MySQL administration program. To run this type the following:# mysql -u [username] -pFor example, to login as root, type the following:

  # mysql -u root -p

• Enter your password when prompted.
• At the MySQL prompt type the following:

  mysql> create database [database name];

  For example, to create the database named 'wikidb' type:

  mysql> create database wikidb;

• If successful, you should see the following response:

  Query OK, 1 row affected (0.00 sec)

• Now close MySQL by typing:

  mysql> exit

  MySQL will return

  Bye

• At the console shell type the following:

  # mysql -u [username] -p [database name] < [path to file/file.sql

  For example, if the username is root, the database name you just created is wikidb and the path the the dumped SQL file is the Linux root home directory, type the following:

  # mysql -u root -p wikidb < /root/Documents/wikidb.sql

• Once you have typed and entered the password, it may look like nothing is happening, be patient, it can take a while to import the database, especially if it is a large database or the computer is slow.
• When the import is finished you should be returned to the console shell. You may want to use the MySQL administration program to check if the data was impoted OK.
• You database has now been imported!

Troubleshooting

• DON'T forget the ; when using the MySQL administration program. If you enter a command and you get the following response:

  -> 

  then you forgot the ;. To continue, type ; on it's own. Your command may then complete with a response saying it has completed. If it doesn't, type the whole command again and remember the ; !

Related Articles

• Exporting a MySQL database