SQL and Database Creation & Management for Drupal

Published Jan 25, 2021
Updated Jul 4, 2022
By Simon

This article assumes you already have a LAMP server set up and can manage it either using the CLI or a GUI such as cPanel.

Being familiar with SQL is necessary if you need a relational database for your application. However to use Drupal you only really need to use a few CLI commands or be familiar with cPanel and phpMyAdmin or similar GUI software to carry out the tasks. If you don't use cPanel then you also need to have a basic understanding of and be comfortable with the command line or CLI.

Also with Drupal if you install Backup and Migrate as I suggest in My Drupal Nice to have Modules article then you will not need to use the CLI or phpMyAdmin to dump (export) your database.

Let's look at the bare minimum one needs to know to get a Drupal site up and running, I would possibly say these commands are pretty much needed for any LAMP stack CMS that uses a database.

Create A Database for a New Drupal Instance

Install MySql or Maria

If your server doesn't have MySQL installed then you will need to install that first. This article assumes you already have a LAMP server; that is a server with a Linux distribution, Apache, MySql, and PHP. If you don't have that, then go set up a server and come back. I'll be waiting.

So we have a LAMP server set up, let's look at what is needed to set up a database for a Drupal site.

If you don't have a database and user combination set up you will get an error as shown in the following image.

drupal failed to connect to database server

To create a user and database login to your server, then login into MySQL.

mysql -u root -p

#You maybe need to use sudo depending on the set-up

sudo mysql -u root -p

Create a Database User

And then use the following command to create a user.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Create A Database

Creating a new database is as easy as running this command.

CREATE DATABASE database;

And then you need to add the user to the database.

Add User to Database

This command in fact grants all privileges to the user, meaning the user can use any database on the server.

GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

cPanel

If you use cPanel you can create a user, a database, and add the user to the database on the same page, the MySQL Databases page.

cpanal UI dashboard MySQL Databases

This is all you need to know to get a site up and running. When you visit the install page and your site passes all the tests you will then be asked to enter your database and user details. From there your site will be installed and be ready to configure.

For more details on the install, process check Installing Drupal 9 and common errors you may get. (coming soon)

Migrating and Restoring

If you are using cPanal and phpMyAdmin the instruction are at the end of this section.

If you need to copy your site for testing updates or you want to migrate to a new server you need to be able to dump the database and then import it into a new database instance. Continue reading to find out how to migrate a database.

Dump

As mentioned in the introduction you don't really need to use dump if you are using Drupal and have Backup & Migrate installed. Backup and Migrate is nice in the fact that you can stipulate what tables to include and which tables to exclude all within a UI.

On install by default Backup and Migrate actually excludes all cache tables.

If you need to dump though use the following command, replacing the values in square brackets.

You don't need to be in MySQL, it is run from inside the directory. Hence the need to the user and password. You may need to use sudo.

mysqldump -u root -p [databasename] > [databasename-TIME-DATE].sql

You may need to transfer your database to a new server, if so please download it to your desktop. This is easily achieved with Backup and Migrate or use SCP.

Import database

This is necessary as without a database Drupal just does not work and therefore Backup and Migrate won't work! The reason I say this is because Backup and Migrate does allow you to roll back to an older database from an archive if you want to.

Use the following instructions to import a database.

If you are using a different server.

  1. Upload the database from your desktop to any directory.
  2. Log into your server using SSH and locate the SQL file uploaded in the previous step.

If you are using the same server.

  1. Locate the dumped database.
  • If you have used the dump command then the SQL file will be in the same directory. Use ls -li to show the content of the directory.
  • If you use Backup and Migrate, it can save the database to a private directory, locate the private directory and the most recently backed up SQL file. See Backup and Migrate set up for information on the Private directory.

Once you have either uploaded the SQL file or located it run the below command, replacing the database, user name, and password with your values.

mysql -u[username] -p [databasename] < [.sql filename]

cPanel and phpMyAdmin

To do the previous operations in phpMyAdmin GUI.

Open phpMyAdmin from the cPanel homepage.

To dump

  1. Click on the database in the left menu you want to dump.
  2. Use the export tab.
  3. Configure the export.
  4. Click Export.

To import

  1. Click on the database you want to import to.
  2. Use the import tab.
  3. Configure the import.
  4. Click Import.

phpMyAdmin can create a database on import from the operations tab. If you do this you will need to got to the databases page in cPanel and add a user to the newly created database.

Conclusion & Final Thoughts

To set up and use Drupal the commands listed in this article are all you need, they have done me good for years. If you are migrating large databases, 50MB or larger, then you may need to enable the server to upload larger databases but that is about the only issue I've ever come across.

To wrap up, having knowledge of MySQL syntax is helpful to troubleshoot and fix some edge cases and knowledge of relational databases and basic queries are useful but not essential. With Drupal 9, Views a powerful GUI SQL query builder is built-in, which allows you to build complex displays of data, images, locations on maps or anything that is stored in the database. If you like you can enable SQL queries to be displayed when using Views which could be a good way to learn about SQL. The great thing about Drupal though is you don't need to know SQL and can get on with creating amazing sites with a second to none point and click back-end administration.

This site is a work in progress, I have been working on getting as much content out related to using Drupal 9 as a CMS and Front-end development as humanly possible. If you are interested in any of the content coming hints mentioned, be sure to sign up to the newsletter and get in touch to let me know. I may have some works in progress that you can have and I am always keen to get feedback on them.

Thanks for reading.

Drupal
Tools