in » Resources

Setting up the MySQL Database

These are my notes on setting up the mySQL database. My original purpose was to install a blog, but this information is of broader interest than WordPress installation. As always, I promise nothing and take no responsibility for anyone's results; the following information is used at your own risk, without warranties or promises of any kind.


Setting up the mySQL Database

First, it doesn't matter what your own computer operating system is; what matters is what is running on the server where you wish to install your software.

Secondly, mySQL must be installed and the database for your blog created prior to installing the software. If you're using commercial web hosting (which must include a mySQL database), then your host will likely have set up a database for you and can provide the username, password and database name to input into the wp-config.php file; if so, skip the next section.

Creating the Database Yourself. If you're trying to install software on your own server or virtual private server (with mySQL already installed), you'll need to create the database for your blog first; this can be done via SSH (secure shell access) by logging in and going to the mysql prompt. If you have not logged in as root, the first two lines below will allow you to switch to root; if you did log in as root, then go straight into mysql:

% mysql -u root -p
% pwd typerootpasswordhere
mysql> CREATE DATABASE newdatabasename;

=> Don't skip the semicolon at the end. THIS is where you can make up a name for the database. Personally, I would call it something other than "wordpress" simply to differentiate between that and every other mention of wordpress. Every little bit helps. ;-)

Next, you need to set the username and password for the new database:

mysql> GRANT ALL
-> ON databasename.*
-> TO 'newusername'@'localhost'
-> IDENTIFIED BY 'newpassword';

and hit "Enter" -- you should get a "Query OK" or similar response from mysql. Note: you may not want to grant "all" privileges to a user. See Creating Users and Setting Permissions in MySQL. Also, a list of mySQL privileges gives us this:

  • Insert - Create tables
  • Update - table
  • Delete - table
  • Index - Used to create or remove indexes for tables.
  • Alter - Used for tables, allows the use of the "ALTER TABLE" command.
  • Create - Make new database, table, or index.
  • Drop - Remove database or table.
  • Grant - Allows a user to give other users the privilege that they have. Used to grant access to databases or tables.
  • References - Database or table.
  • Reload - Have the database engine re-read the grant tables.
  • Shutdown
  • Process
  • File - Can read and write files on the SQL server using "SELECT...INTO OUTFILE" or "LOAD DATA INFILE".

Last, to make the new privileges "take":

mysql> flush privileges;

Filling out the Configuration File

If you've gone so far as to install your software and gotten the "error establishing a database connection" message, it usually means that the info you put into wp-config.php (or your software's configuration file) contains an error(s). You cannot just invent data for this file. Web hosts will usually have assigned a database name when you signed up, and will have given you a username and password as well. If you've created the database as above, use that info.

A typical software configuration file is as follows:

// ** MySQL settings ** //
define('DB_NAME', 'newdbname'); // Name of the database
define('DB_USER', 'newusername'); // Your MySQL username
define('DB_PASSWORD', 'newpassword'); // ...and password
define('DB_HOST', 'localhost'); // 99% chance you won't need to change this value

==> The last issue is DB_HOST; I've found that some commercial web hosts want you to use your domain name: Try both "localhost" and "" -- but if you have problems, contact your web host to verify.

© 2004-2019 DianeV Web Design Studio. All Rights Reserved.
15 queries. 0.211 seconds.