MySQL create database example - How to create a MySQL database and user account for Drupal

MySQL “create database” FAQ: Can you share an example of how to create a MySQL database, including (a) creating the MySQL database and (b) a new MySQL database user, with all appropriate MySQL grant permissions for that user?

Sure. I've written about the process of How to add/create a MySQL user before, but I just ran across the Drupal installation documentation, which also has a nice description for how to create a MySQL database (for use with Drupal), so I thought I'd include some of their documentation here, along with some notes from my own recent Drupal and MySQL installation.

1) Create a new MySQL database

The first part of the Drupal installation document refers to creating a MySQL database:

Create MySQL Database:

This step is only necessary if you don't already have a 
database set-up (e.g. by your host). In the following 
examples, 'username' is an example MySQL user which 
has the CREATE and GRANT privileges. Use the appropriate 
user name for your system.

First, you must create a new database for your Drupal site 
(here, 'databasename' is the name of the new database):

# mysqladmin -u username -p create databasename

That mysqladmin command shows all you need to do to create the Drupal database. I named my Drupal database drupaldb, and created the database using the MySQL root user account, so my command looked like this:

$ mysqladmin -u root -p create drupaldb

After this command, I just entered the root password, and the database was created.

2) Create a MySQL user account for Drupal

Next, the Drupal documentation gets into creating a MySQL user account for your Drupal installation:

MySQL will prompt for the 'username' database password 
and then create the initial database files. Next you must 
login and set the access database rights:

# mysql -u username -p

Again, you will be asked for the 'username' database password. 
At the MySQL prompt, enter following command:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
       ON databasename.*
       TO 'username'@'localhost' IDENTIFIED BY 'password';

where

      'databasename' is the name of your database
      'username@localhost' is the username of your MySQL account
      'password' is the password required for that username

Note: Unless your database user has all of the privileges 
listed above, you will not be able to run Drupal.

If successful, MySQL will reply with:

Query OK, 0 rows affected

For my purposes (in my test environment) my command looked like this:

$ mysql -u root -p

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER
       ON drupaldb.*
       TO 'drupaluser'@'localhost'
       IDENTIFIED BY 'drupalpass';

Restoring a Drupal MySQL database backup

After following these instructions to create the Drupal database and Drupal user in MySQL, all I had to do now was restore my Drupal database backup, which I had made from a MySQL server on another machine.

To restore my backup, I just used these series of commands (with comments shown):

# log into mysql as the root user
$ mysql -u root -p

# state that I want to use the database named 'drupaldb'
mysql> use drupaldb;

# "source" my last database backup file, which re-creates the tables,
# indexes, and data in this database:
mysql> source my-drupal-database-backup.sql;

The MySQL source command reads the file it's given, and executes the commands in that file. Because the file my-drupal-database-backup.sql contains the output from a MySQL dump of my other Drupal database, this command restores my Drupal database on this new server.

Summary: Creating a MySQL database and MySQL user

I hope this short tutorial on how to create a MySQL database and MySQL user account has been helpful. As a quick summary, those were the steps I followed to:

• Create a MySQL database for use with Drupal.
• Create a Drupal MySQL user account.
• Restore a Drupal database backup from a MySQL dump on one server into the new Drupal MySQL database on this server.