Databases explained
Most websites built with an app such as WordPress, Joomla or PrestaShop keep their content in a database rather than in files. This guide explains what a database does, which details your site needs to reach it, and how to create, export and import one safely. It is for anyone setting up or moving a website on web hosting.
What a database does
A database is an organized store of information that a website reads and writes as visitors use it. Posts, pages, products, orders, user accounts and settings usually live there, while images, themes and code stay as files.
The database software most web hosting uses is MySQL or MariaDB. MariaDB started as a copy of MySQL and the two remain largely compatible, so website apps that ask for MySQL normally work with MariaDB too. Check your app's official requirements for the versions it supports.
Both use SQL (Structured Query Language), a standard language for reading and changing data.
The four connection details
Every website app needs four details to connect to its database. They are usually stored in the app's configuration file, such as wp-config.php for WordPress.
| Detail | What it is | Example |
|---|---|---|
| Database name | The database that holds the site's data | exampleuser_site |
| Database user | The account the site signs in with | exampleuser_web |
| Password | That user's password | A long, unique password |
| Host | Where the database server runs | localhost, or a name your host gives you |
A database user is separate from your hosting account login. It only grants access to the databases you assign to it. On many hosts, names get a prefix based on your account name, as in the examples above.
If any of the four details is wrong, the site cannot reach its data and usually shows a connection error instead of its pages.
Create a database and user
The exact screens depend on your host. In your hosting control panel or account, open the section for databases.
- Create a new database and note its full name, including any prefix your host adds.
- Create a database user with a strong password. A password manager can generate and store one.
- Give the user access to the database. Website apps normally need all the standard privileges on their own database.
- Note the database host your hosting account shows. If none is shown, it is often
localhost. - Enter the four details in the app's installer or configuration file.
Use a separate database and user for each website. If one site is compromised, the others' data stays out of reach.
Manage a database with phpMyAdmin
phpMyAdmin is a free, browser-based tool for browsing and editing MySQL and MariaDB databases. Many hosts provide it or a similar tool. You can use it to look at tables, run SQL queries, and export or import data.
Be careful with changes made directly in the database. Deleting a row or table cannot be undone, so export a copy first.
Export a database
An export (also called a dump) saves the whole database as a single .sql file: a text file of SQL commands that can rebuild it. Export before updates, migrations or any risky change.
In phpMyAdmin or a similar tool, select the database and choose Export with the SQL format.
If you have SSH access, the mysqldump command does the same thing:
mysqldump -u exampleuser_web -p exampleuser_site > site-backup.sql
-u gives the database user and -p makes the command ask for the password, so it is not saved in your command history. Add -h followed by the host name if the database is not on localhost. For large databases, add --single-transaction to get a consistent copy without locking the tables, when the tables use the InnoDB storage engine, the usual default.
Import a database
An import loads a .sql file into a database, for example to move a site to a new host or restore a backup.
-
Create an empty database and user at the destination, as described above.
-
In phpMyAdmin or a similar tool, select the empty database and choose Import, then pick the
.sqlfile. If the file is larger than the upload limit, use SSH instead. -
Over SSH, run:
mysql -u exampleuser_web -p exampleuser_site < site-backup.sql -
Update the site's configuration file if the database name, user, password or host changed.
Importing into a database that already contains tables can overwrite them. Double-check that you selected the right database, and export the existing one first.
Keep credentials private
Database passwords give full access to your site's data, including customer and user details.
- Never send them by email or chat, or paste them in support tickets or public forums.
- Do not leave
.sqlexports in your website's document root, where anyone who guesses the name can download them. Store them outside it or on your own computer. - Change the database password if you think it was exposed, then update the site's configuration file.
Related guides
- Backups and restores
- Plan a website migration
- Upload website files with SFTP
- A website that won't load