Last updated April 13, 2015. Created on April 25, 2008.
Edited by dcam, NewSites, rrfegade, Piyavkin. Log in to edit this page.

Note: Since 8.x, Drupal can create the database itself if its database user has enough privileges to create a database, for example the 'CREATE' privilege in MySQL or the 'CREATEDB' privilege in postgresql. See the relevant change notice for more information. If you do have a user with these privileges, you can move to the next step.

Before running the installation script, you must create an empty database and database user (a user name assigned the rights to use the Drupal database).

This page provides direction for creating your Drupal database using one of the following methods:

Selection of characters in MySQL database name:

  • If you use capital letters in the database name, they will be converted to lower case.
  • A "-" (hyphen) in the database name will be encoded as "@002d" in the database folder name. For example, the folder name for database a-b-c will be a@002db@002dc. Other characters besides a..z, 0..9, and "_" (underscore) are similarly encoded. [ref, ref]


Create a database and user via a browser-based control panel

Most web hosting accounts provide a Web-based control panel to help you administer your site. These tools include easy-to-use features for creating a new database, and for creating a "user" with rights to the database. To create a database using a browser-based control panel consult the documentation or ask your web host service provider.

When you create the user for your database, you may see a page where you can specify the privileges that user will have for various operations on the database. In most web control panels' "database wizard", if you simply check "All" privileges for the user you create (and then uncheck "Grant" if it is listed as a privilege) your user will be set up correctly.

Take note of the username, password, database name and hostname (for example, are you installing in http://example.com, or in http://drupal.example.com, or http://example.com/blog etc.) as you create the database. You will enter these items into fields in your browser when running the install script (see next page).

Note that in many cases when creating databases and users via a web-based interface, the username you use to log into your control panel is added as a prefix to the database name and possibly to the database username as well. For example, if you log into your site's control panel as "webadmin" and create a database named "drupal7db" and a user for that database named "d7user", when running the Install script (see next page) the database and user may need to be typed in as "webadmin_drupal7db" and "webadmin_d7user". (This is because many hosting accounts are on shared servers, and on one server each database and user name must be unique across all accounts on the server.)

If you used the procedure in this section to create your database and user using a web-based interface, continue to the Step 3: Create the settings.php file page of the Installation Guide.


Create a database and user using phpMyAdmin

The most secure method to use when creating a database with phpMyAdmin is to create a user that has all privileges to the new database, but no privileges to the other databases. This is more secure than using a general username and password for all of your sites on the same server, as it limits access to your databases if someone gets hold of your database logins.

Note: This procedure assumes that you have root access to phpMyAdmin, and that you're using phpMyAdmin 3.5.x.

  1. Sign in to phpMyAdmin as the root user.
  2. Click Users, and then click Add user.
    Note: You can use the root user credential as well..
  3. In the User name field, enter the username that you want to use.
  4. In the Host field, select Local, which is a more secure setting, unless you'll be accessing the database with this user from another server.
  5. Enter or generate a password for the user.
  6. In the Database for User section, select Create database with same name and grant all privileges.
  7. Make sure you select COLLATION utf8_general_ci
  8. Click Go to create the user.

phpMyAdmin creates the new database with the same name as the user account. If you want to have a different name for the database and the user:

  1. Click Databases, and then click the link for the database that you want to rename.
  2. Click Operations.
  3. In the Rename database to section, enter the new database name.
  4. Click Go in the Rename database to section.

If you need more details about using phpMyAdmin, check out the official wiki.

Take note of the username, password, database name and hostname (for example, are you installing in http://example.com, or in http://drupal.example.com, or http://example.com/blog) as you create the database. You will enter these items into fields in your browser when running the install script.

In many cases, when creating databases and users using a web interface, the username that you use to sign in to your control panel is added as a prefix to the database name, and possibly to the database username as well. For example, if you sign into your site's control panel as "webadmin" and create a database named "drupal7db" and a user for that database named "d7user", when running the Install script the database and user may need to be typed in as "webadmin_drupal7db" and "webadmin_d7user". (This is because many hosting accounts are on shared servers, and on one server each database and username must be unique across all accounts on the server.)

If you used the procedure in this section to create your database and user using phpMyAdmin, continue to the Step 3: Create the settings.php file page of the Installation Guide.


Create a database from the command line

If you do not use a Web control panel or are experienced with and prefer to use MySQL or PostgreSQL commands, you can use the information in the following sections.

Additional information about privileges, and instructions to create a database using the command line are available in the INSTALL.mysql.txt file for MySQL and in the INSTALL.pgsql.txt file for PostgreSQL.


Create a database using MySQL commands

Note: The database should be created with UTF-8 (Unicode) encoding, for example utf8_general_ci.

For information on installing and configuring MySQL see http://dev.mysql.com/doc/refman/5.7/en/index.html

In the following examples, 'username' is an example MySQL user who will have the CREATE and GRANT privileges and 'databasename' is the name of the new database Use the appropriate names for your system.

  1. Create a new database for your site (change the username and databasename):

    mysql -u username -p -e "CREATE DATABASE databasename CHARACTER SET utf8 COLLATE utf8_general_ci;"

    MySQL prompts for the 'username' database password, and creates the initial database files.

  2. Log in and set the access database rights:

    mysql -u username -p

    MySQL prompts for the 'username' database password.

  3. At the MySQL prompt, set the permissions using the following command:

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

    In this case:

    • databasename is the name of your database
    • 'username' is the username of your MySQL user account
    • 'localhost' is the host where Drupal is installed
    • 'password' is the password required for that username

    Be sure to use backticks ( ` ) around the database name if you used a MySQL escape character (_ or %) in your database name.

    For example, because the underscore character is a wildcard, drupal_test_account.* should be `drupal\_test\_account`.* for security. Otherwise the underscores would match any character and could accidentally give access to other similarly named databases.

    Note: Unless the database user/host combination for your Drupal installation has all of the privileges listed above (except possibly CREATE TEMPORARY TABLES, which is currently only used by Drupal core automated tests and some contributed modules), you will not be able to install or run Drupal.

    For further information on the GRANT statement see http://dev.mysql.com/doc/refman/5.0/en/grant.html

  4. If successful, MySQL will reply with:

    Query OK, 0 rows affected

  5. Now exit the MYSQL prompt by typing:
    exit
    The server will answer by saying
    Bye


Create a database using PostgreSQL

The database must be created with UTF-8 (Unicode) encoding.

  1. Create a database user
    This step is only necessary if you don't already have a user setup (e.g. by your host) or you want to create new user for use with Drupal only. The following command creates a new user named 'username' (you should substitute this with the desired username), and prompts for a password for that user:
    createuser --pwprompt --encrypted --no-adduser
    --no-createdb username

    If everything works correctly, you'll see a CREATE USER notice.

  2. Create the database
    This step is only necessary if you don't already have a database setup (e.g. by your host) or you want to create new database for use with Drupal only. The following command creates a new database named "databasename" (you should substitute this with the desired database name), which is owned by previously created "username":

    createdb --encoding=UNICODE --owner=username databasename

    If everything works correctly, you'll see a CREATE DATABASE notice.

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

amongtheclouds’s picture

I'm using drupal 6 with a PostgreSQL database.

I just installed postgres 9 on my MacBook Pro and my drupal site began giving me all sorts of these messages...

# warning: array_keys() [function.array-keys]: The first argument should be an array in /Users/ian/GoMOOS/XAMPP/odpdx.gmri.org/htdocs/includes/common.inc on line 3416.
# warning: Invalid argument supplied for foreach() in /Users/ian/GoMOOS/XAMPP/odpdx.gmri.org/htdocs/includes/common.inc on line 3419.

I fixed this by adding "set bytea_output = escape; " to includes/database.pgsql.inc. The code is below.

/**
* Helper function for db_query().
*/
function _db_query($query, $debug = 0) {
global $active_db, $last_result, $queries;

if (variable_get('dev_query', 0)) {
list($usec, $sec) = explode(' ', microtime());
$timer = (float)$usec + (float)$sec;
}
// bytea fix, preface query with set....
$query = "set bytea_output = escape; " . $query ;
$last_result = pg_query($active_db, $query);

eremind’s picture

Could you provide some info with installation of Drupal 7 on Oracle.
Thank you,
DE

shamio’s picture

These links can help you more about Drupal and Oracle. I hope you can use them for installing Drupal on Oracle.
http://groups.drupal.org/node/24398
http://inode.me/tutorials/how-to-drupal-7-oracle-xe-install

Doubt is the father of invention.... موبایل گوشی

10basetom’s picture

I followed your instructions to the T using phpMyAdmin and still can't get pass this step. I keep getting this error:

SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown storage engine 'InnoDB'

I have MySQL 5.5.9-community installed. I've installed Drupal 6.20 before with no issues. Please advise.

Kami Petersen’s picture

You might want to mention something about choosing collation if you're building a localized site. Some languages have characters that migth turn out in the wrong alphabetical order in sorted queries if you go by the general collation, for instance scandinavian languages (åäöÅÄÖ and similar should be in the end of listings but are shown in proximitiy to aAoO).

nilashis’s picture

If you are installing drupal on Godaddy.com, then be sure to change the "Host Name" under "Advanced Options" to the complete host name given in the Godaddy hosting center account under databases for your database.

Hostname will look something link: nameofthedatabase.db.7235321.hostedresource.com

Neil

vmevada102’s picture

i would like to install the drupal 6 in my on desktop pc and database on the other mysql database contating server

in one pc all is worked well,

but when installing on the another server having ip 192.168.200.70 with ubuntu as OS

and apache/XAMPP in windows to upload files on webserver having ip 192.168.200.60

I got the error like,

Failed to select your database on your MySQL database server, which means the connection username and password are valid, but there is a problem accessing your data. MySQL reports the following message: Host '192.168.200.70' is not allowed to connect to this MySQL server.

* Are you sure you have the correct database name?
* Are you sure the database exists?
* Are you sure the username has permission to access the database?

Please help me to solve it

mustang’s picture

You may need to explicitly allow connection from your PC's IP address to your MySQL server.

lrvk’s picture

For those who are struggling with postgres:
https://discussions.apple.com/thread/3199015?start=15&tstart=0

Creating database is not sufficient, it is also necessary to enable TCP connections. Link above gives details.

milne’s picture

I found this to be pretty straight forward and easy. In the Databases section of c-panel use the "MySQL Databases" or you can use "MySQL Database Wizard", which walks you through the whole process step by step.

If you use "MySQL Databases" create a database, then a user. Make sure you link the two in the "add user to database" section.

If you use shared hosting the name of the database and user are "youradminusername"_"nameyouspecified", which is displayed easily for you to see.

Zach

sgbotsford’s picture

This is likely going to be like nailing jello to the wall. Many OS's will have postgresql or mysql as packages, but there is some variation in where stuff is put, and what the unprivileged user is called. So for example, this is what I had to do on FreeBSD 8.

1. The default database is in /usr/local/postgresql, not /var. I don't like data to reside in /usr. The unprivileged user is pgsql.

mkdir /opt
mkdir /opt/postgres
chown pgsql /opt/postgres

This gave me a place to put the database

2. On freebsd the commands to initialize and create must be run as the owner of the database system -- pgsql.

su pgsql -c "initdb -D /opt/postgres"

3. The database engine has to be started before you can create a databse

su pgsql -c "pg_ctl -D /opt/postgres -l /var/log/pg.log start"

4. NOW you can create the admin user and the database, but to create with unicode you have to use Template0

su pgsql -c "create user --pwprompt --encrypted --no-adduser --no-createdb drupal_admin"
su pgsql -c "createdb --encoding=UNICODE -T template0 --owner drupal_admin DrubleBase"

This is going to vary with each OS.

jamiemarchant’s picture

What about SQL lite.?

sd2k9’s picture

The documentation says that the "The database should be created with UTF-8 (Unicode) encoding, for example utf8_general_ci." but doesn't give details.

To achieve this by the mysql command line interface use the following commands:
mysql -u root -p # Login
CREATE DATABASE databasename CHARACTER SET utf8; # Create with utf-8

When using mysqldump/mysql for backup/restore also force both server and client to utf8 by
inserting into "/etc/mysql/my.cnf"
default-character-set = utf8 # Server
skip-character-set-client-handshake # Force client

Otherwise you could experience charset trouble when you once migrate the data to a different location.
(Make sure not to break other databases on your system in case they rely on anothe charset for mysql)

Check: Login to your database and isse following commands, you should see everywhere utf8:
SHOW VARIABLES LIKE 'char%';
SHOW VARIABLES LIKE 'collation%';

Most of the information taken from the following blog post - Thanks to them!
http://www.bluebox.net/about/blog/2009/07/mysql_encoding/

rooby’s picture

In drupal 7 in INSTALL.mysql.txt it only says:

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

Which is different to what is mentioned on this page.

Extra ones mentioned on this page are:

LOCK TABLES, CREATE TEMPORARY TABLES

What's the deal? The doco should be the same in both places.

This was added in revision 976154

rooby’s picture

SergeiK’s picture

I'm using dbForge Studio for MySL management - freat tool. All necessary functions + confortable design!

mbaynton’s picture

The documentation seems incorrect or out of date re: correct escaping of underscores as wildcards. While the documentation reads

Be sure to use a single quote ( ' ) around the database name if you used a MySQL escape character in your schema name.

For example, because the underscore character is a wildcard, drupal_test_account.* should be drupal\_test\_account.* for security. In this case, use: 'drupal\_test\_account'.*

In fact, wrapping the database name in single quotes on MySQL 5.5 produces
ERROR 1064 (42000): You have an error in your SQL syntax; ...

The correct syntax appears to be to use a backquote (`) instead of a single quote ('), and still escape the underscore with a backslash.

mbaynton’s picture

Did a little more research, the backtick is definitely the correct way. Updating page...

rooby’s picture

Backticks are generally used for tables & column names in sql queries and such, however in the case of that doco where you are doing something like
grant all on table_name.* to username identified by 'password';

you don't need to escape it at all, as per the above example, which I never have any trouble with.

From your linked doco:

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

Extended: U+0080 .. U+FFFF

Underscore is not a wildcard in this situation, they are wildcards when comparing strings.

Your link is also for the MySQL 5.0 version, which is very old now as 5.5 is probably most commonly used.
Although I note that 5.0 is all that is required for Drupal 7 so it probably the right way to go for the doco.

[edit] Another relevant page: http://dev.mysql.com/doc/refman/5.5/en/grant.html
[edit2] Doco on pattern matching, where underscores are wildcards: http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

mbaynton’s picture

Hi rooby,
Thanks for your investigations on this. Despite what the docs say and what seems sensible, however, I am not sure this is quite right. The syntax you give won't give you trouble in that it'll run, but if you test it out (on MySQL 5.5) you'll find it does permit the mysql user with access to databases as though _ is a wildcard. Or have a look at a terminal session I did to demonstrate this: http://paste.ubuntu.com/6355015/.

Let me know if you can reproduce.

- mbaynton

rooby’s picture

Oops, you're right, sorry. I will revert my change.

BConOfLight’s picture

I've downloaded drupal 7.24 (drupal-7.24.zip) and am installing it on Windows using WampServer2.4 from Wampserver2.4-x86
It has
Apache : 2.4.4
MYSQL : 5.6.12
PHP : 5.4.16
PHPMyAdmin : 4.0.4
I'm following the instructions in 'Create a database and user using phpMyAdmin' above.
Item 4 says "In the Host field, select Local, which is a more secure setting, unless you'll be accessing the database with this user from another server."

I decided that I might like to access the user from another server so I used % as the HOST. I got "SQLSTATE[HY000] [1045] Access denied for user 'myblog0dbsu'@'localhost' (using password: YES). " error when I ran the install.
After re-installing and re-running, this time with the user host set to 'localhost' (selected 'Local') as SUGGESTED and it ran to completion.

Although I have not fully researched the error, which could be an incompatibility between WampServer2 / MYSQL 5.6.12 as configured in the download and the Drupal install script / these instructions. But perhaps some explanation or elaboration might be useful in the instructions here.
Also after step 7. There are instructions about how to change the database name. I did this and it worked as I believe that having different database and user names adds to security. However before running the install script I also changed the priviliges of the user to access only that database name. [I never attempted to run it without this change] I think that it might be a good idea to re-visit either step 6 or the instructions for changing a database name.

I may be dim but I am a BConOfLight

BobAGI’s picture

I have lost a whole day with Drupal installation just because I did not see the note on setting utf8_general_ci as the collation for the new database....
The result is that at the end of the install script there was a white page of death and no suggestion on what could be the problem.
Turns out that after extensive Googling I stumbled across a note directing me to the Apache error log where there was a definite error. So I Googled that error text and found a troubleshoot thread right here at Drupal which dealt with my problem exactly.
Had to drop the database I had created and then recreate it using the utf8_general_ci setting.
Then re-ran the install.php script and all went OK.

--
Bo Berglund
Sweden

rooby’s picture

There's a good lesson here.

If you get a white screen of death, check the apache error log.

mbrett5062’s picture

Not sure where it is best to post this so it gets to the attention of the people seeing this issue themselves. Any suggestions for a better place/way to get this information out is much appreciated.

I have been seeing slower and slower response times on Processwire and Drupal sites on my local Xampp server for a while now. Just spent a couple of hours searching for some hints/fixes.

Thought I would share this here as the answer I found that worked for me was reported on Wampp as well and using Joomla and Wordpress so seems to be across the board.

I am running Windows 8.1 64bit. That seems to be where the issue stems, and has to do with a bug in that operating system with how they handle IPV6 first then fallback to IPV4.

The way this manifests is frequent calls to the database being repeated, increasing server response times.

So the fix is as follows.

In phpMyAdmin\config.inc.php change $cfg['Servers'][$i]['host'] = 'localhost'; to $cfg['Servers'][$i]['host'] = '127.0.0.1';

This one I did not actually need as Xampp had that already, but according to the thread I found Mampp did need it.

Next uncomment bind-address="127.0.0.1" in mysql\bin\my.ini

And finally the real culprit, in settings.php change the database configuration from 'host' => 'localhost', to 'host' => '127.0.0.1',

This same applies for any CMS config file that connects to a MySQL database. (Maybe even any other database too as the issue is with OS)

And hey, WOW!! my local dev sites are running way faster now.

Hope all that is clear, and useful if anyone else is seeing slow response times locally.

Just in case here is my current setup details.

Windows Version: Windows 8 Enterprise 64-bit
XAMPP Version: 1.8.3
Apache/2.4.7 (Win32) OpenSSL/1.0.1e PHP/5.5.9
MySQL Community Server (GPL) 5.6.16
libmysql - mysqlnd 5.0.11-dev
phpMyAdmin Version information: 4.1.6

sobrien.808’s picture

I am not very familiar with postgresql but since Oracle took over MySQL I thought I would learn. Installing the database was failing with an ident error. Had to edit /var/lib/pgsql/data/pg_hba.conf and change:
# IPv6 local connections:
host all all ::1/128 ident
TO:
# IPv6 local connections:
host all all ::1/128 md5

rooby’s picture

If you don't like Oracle you might want to check out MariaDB as a MySQL alternative.

gabzz’s picture

the below statement is what i get when i fill the necessary details in the db section;

Fatal error: Maximum execution time of 30 seconds exceeded in C

when it gives me this error then i refresh the tab and this is what i get next;
Table variable already exists.

what do i do?

s4j4n’s picture

Is there a reason that utf8_unicode_ci should NOT be the RECOMMENDED collation setting?

There seem to be many good reasons to actually avoid utf8_general_ci:

http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci/766996#766996

http://forums.mysql.com/read.php?103,187048,188748#msg-188748