Importing a database, including large files

An import loads an export (a .sql or .sql.gz file) into a database. You do this, for example, when you move a website from another provider to WebOké, or when you restore an export you made earlier.

Before you start

• Create the database first (see Creating a database). The import usually contains only the content, not the database itself.
• Are you importing over an existing database? Then first make an export of what's in it now (see Exporting a database). Tables with the same name will be replaced.
• Does the file contain a CREATE DATABASE or USE line with a different database name? Then the import will give an error about permissions. Remove those lines, or export again at your old provider without those statements.

Importing in DirectAdmin

1. Go to Databases and click Manage next to the database.
2. Under Database Operations, click Import and choose the .sql or .gz file on your computer.
3. DirectAdmin asks: "Continue importing file into database?". If you want to empty the database completely first, switch on Clean database (1): all current content will then be deleted before the import. This is useful if you want to fully replace an old version.
4. Click YES (2). After the import, you'll see the number of tables under Overview.

Importing in phpMyAdmin

1. Open phpMyAdmin (see Opening and using phpMyAdmin) and click the database you want to import into on the left.
2. Click the Import tab at the top.
3. Next to File to import, click Browse and choose your file. Next to the button you'll see the maximum file size (1). With us, that's 1,024 MiB (1 GB).
4. Leave everything else as it is and click Import at the bottom.

Importing a large database file

Is your file larger than the import allows, or does the import stop halfway with a time-out (this can also happen with large files under 1 GB)? Then try the following, from easy to harder:

1. Compress the file. A .sql file often becomes five to ten times smaller as .sql.gz, and both DirectAdmin and phpMyAdmin can read .gz directly. On your computer, you can create a .gz with 7-Zip (Windows), for example, or with gzip bestand.sql (Mac and Linux).
2. Import it in parts. Export the large tables separately at your old provider, or split the file with a program that splits SQL files. Then import the parts one by one, in the right order.
3. Use SSH. If you have a package with SSH (Power Webhost Three and Four, AI Wordpress Three and Power Reseller), upload the file with FTP to the main folder of your package (not in public_html) and import it in the Terminal with mysql -u gebruiker -p databasenaam < bestand.sql (see Terminal in your browser). There's no maximum size there.
4. Ask us. If it doesn't work, create a ticket. Put the file in the main folder of your package and include the file name and the database. We'll import it for you.

Please note: Never put a database file in public_html. Anyone who guesses the name could then download it, with all the data of your website and your customers. Also delete the file from the main folder once the import is finished.

After the import

• Are the database name, user and password correct in your website's configuration file (see Database users, privileges and passwords)?
• Has your website's address changed, for example from a temporary address to your own domain? Then replace it throughout the database with WP-CLI or a search and replace plugin that supports serialised data (see below). Only siteurl and home can be changed manually in phpMyAdmin first if needed (see Opening and using phpMyAdmin).

Please note: serialised data. To change an address or path, never use Find and replace in phpMyAdmin, an SQL statement such as REPLACE, or search and replace in the .sql file itself. WordPress and many plugins store settings as serialised PHP data, including the length of each text. If that length no longer matches after replacing, PHP can't read the data and things like your menu, logo or widgets disappear. Use WP-CLI (wp search-replace, first with --dry-run) or a suitable plugin. You can read how to do this in Search and replace in the WordPress database.