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
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;
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.
Creating a MySQL database and MySQL user - Summary
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.