How to import a SQL file into a local database

Importing a .sql file into a local database is one command in Server by Zaweba. Go to the Databases page, choose the database and use Import SQL to load the file. The file is streamed in rather than read all at once, so a large dump from a real website imports as happily as a small one.

The usual reason to do this is to get a copy of a live site working on your own PC. You take a dump of the site's database from your web host, bring it home, and load it into a local database so you can test changes without touching the real thing.

Getting ready

Server's database is MariaDB 11.8, which reads the same SQL as MySQL, so dumps from most ordinary web hosts load without fuss. MariaDB needs to be running: start it from its card on the control panel, or with Start all from the Services menu.

Next, have a database to import into. On the Databases page, New database and user makes both in one click. The user gets a random 20-character password and rights on that database only, and the details are shown once with a copy option, so copy them somewhere safe straight away. If you're setting up a new site at the same time, the new-site wizard can make its database for you instead.

Then make sure you have the right file. A dump is a plain text file of SQL statements, usually ending in .sql. If it arrived zipped, unzip it first.

Running the import

On the Databases page, pick the database and choose Import SQL, then select your file. Server streams it into MariaDB. Database names are checked before they reach SQL, so a stray character in a name can't turn into a command.

When it finishes, the Databases page lists your databases with their tables and size, which is a quick way to see that the tables arrived. For a closer look, open the database in Adminer from the same page. Adminer opens already logged in and straight into the database you chose, so you can browse the tables and check a few rows.

Connecting your site to it

A site's code needs to know where the database is: the host, the port, the database name, the user and the password. Use the user and password from when you made the database. The dashboard web page Server runs on your PC shows the MariaDB connection details, including the port, which matters if Server moved MariaDB off its usual port because something else was using it.

Many content systems store the site's own address inside the database. A copy of a live site may therefore keep sending you to the live address. That's a job for the system's own settings or tools, or a careful edit in Adminer, rather than the import itself.

When an import goes wrong

If the import stops, the MariaDB log on the Logs page is the first place to look. The most common causes are a dump made from a much newer or quite different database, a file that was cut short during download, or a dump that tries to create a database with a name you didn't expect.

Importing into a database that already has the same tables can also clash. If you want a clean start, make a fresh database and import into that.

Keeping a copy before you change things

Once the import works, use Export SQL on the same page to save your own dump before you start experimenting. If a change goes badly, you can make a new database and import that copy, and you're back where you started in a minute.

Websites exported from Base by Zaweba are a special case with their own route: File, then Import a website from Base by Zaweba, makes the database and user and loads the tables and data for you. For any other .sql file, Import SQL in Server by Zaweba is all it takes.