How To Create A New SQL Database using phpMyAdmin

Saturday, 31 January 2009 00:14 |

Written by NetworkNerd007 |

Revised 2-19-2009 - Added security.

This tutorial will explain how to create a new MySQL database for WordPress, phpBB, or whatever other script you are installing that needs an SQL database to function correctly. I will be using phpMyAdmin as my database manager.

INTRODUCTION

If you intend on installing a PHP forum, blog, or any other database dependent scripts, you are going to have to create a new MySQL database for it at some point or another. And if you log into phpMyAdmin, it can look a little intimidating at first.

But the fact is, creating new databases is actually quite simple. There are more functions and security measures you should take, but they are beyond the scope of this tutorial and will be addressed in a tutorial soon.

WHAT IS A MYSQL DATABASE?

A database is kind of like a phonebook or personal organizer for your PHP script. It stores information for the PHP script that can be frequently changed and when the PHP script calls for that information, it's there ready to provide it (hopefully).

Understanding that a database is nothing more than a nice organized container of information for your PHP program helps to better understand it's function and purpose. There's several advanced things that it can be used for, but for the sake of simplicity, we're sticking with this for now.

One example would be user names and passwords for a blog or forum. The blog or forum itself is the php script, but it doesn't store that information in any of the PHP files. Instead, it writes it to the database in a section (aka table) that contains all of the user information. This can range from the user's privileges, their password (usually encrypted), user details such as email address, location, IP, and anything else they choose to put on their profile. When someone accesses the PHP script that shows this info, the script retrieves it from the database and then shows it as requested. That's the function of the database.

How do I create a new database?

The most popular interface used is phpMyAdmin. phpMyAdmin IS NOT your SQL server and it is not MySQL. It is actually a PHP script that connects to your MySQL server and allows the content to be modified and controlled. There are other programs that also do this, but phpMyAdmin is the most used one.

So to create a new database, you need to log into your phpMyAdmin by going to the directory in your web browser. If you're using XAMPP, it's http://localhost/phpmyadmin.

smf_2009-01-30_23-50-53

Once you log in, you will see a screen similar to this one:

smf_2009-01-31_00-11-08

As long as you have the correct permissions, you should see the area highlighted above. Type in the name of your new database. The name doesn't matter and I recommend using something that helps you easily identify it.

smf_2009-01-31_00-12-58

If everything worked correctly, you should see the above message.

Now the following steps are a good practice for security purposes, but are not required.

CREATE A NEW USER FOR THE NEW DATABASE

As a good practice for security reasons, you should create a new user for each new database you make. You also should NEVER use your root login information for a script if you will be making that script public. You can read more in my article about phpMyAdmin and MySQL security.

Now, while at the screen shown above, click the "Home" button as shown here.

security_2009-02-19_21-00-48

Next click on the privileges tab.

security_2009-02-19_21-01-33

You should see a screen like this. Scroll down and click "Add a new User".

security_2009-02-19_21-02-12

In the Add a new User section, fill out the information accordingly.
Choose a username and then select "localhost" in the Host section.
For the password, I recommend clicking the "Generate" button and have phpMyAdmin automatically generate the password for you. Be sure to copy the new password by highlighting it and then right-clicking on it and selecting copy.
Next press "Copy" to copy the password to the two password fields shown here.

For the "Database for user", leave it set to "None".

security_2009-02-19_21-03-14

Now scroll down and just leave everything here blank.

security_2009-02-19_21-03-57

Click Go.

security_2009-02-19_21-04-33

You should now see something similar to this.

security_2009-02-19_21-05-55

Now scroll down and find the section titled "Database-specific privileges".
Select the new database.

security_2009-02-19_21-06-45

You should be taken to this screen. I usually just check all of the boxes as shown here and leave the ones in the Administration section unchecked.
Once you finish here, click "Go".

security_2009-02-19_21-07-28

You should see another dialog like this one.

security_2009-02-19_21-08-48

Now you're finished.

When prompted for the database username and password, use this one. The reason being is if for some reason this username and password gets hacked, then the hacker will only have access to just the one database this user has been assigned permissions to. This helps protect your other databases from being jeopordized. That is also why it is essential to not use the root account for anything other than maintenence and for creating new databases, because the root user has access to everything.

WHAT NEXT?

Now you will need to enter the database information. Depending on what program you're trying to install, the correct method of doing this varies. You may be asked for a few things and I'll try to explain them to you.

The database host, or database server.

If you are using an Apache server, XAMPP, IIS or something similar, then your MySQL server will be on your host computer. So then the answer to this will 99% of the time be localhost, regardless if you are running a private experimental server or a public one.
If you are using paid hosting, they may have multiple SQL servers and may use various domain names for them, so you will need to enter those correctly if that's the case.

The server port or server socket

This is the port used to connect to your MySQL server. In most cases, you can simply leave this blank and it will use the default value, which is port 3306. The "socket" is in reference to what you would use when using Unix or Linux, because they use sockets intead of ports like Windows. The only time you will ever need to set this to something else is if you're running multiple MySQL servers and you probably won't be reading this tutorial if you know how to do that.

The database name

This is the name of the new database you just created. In my illustrations above, the answer would be newtest.

The database user name

This is the username you created in phpMyAdmin for the database. If you are running a public or production server, it's highly advised for security reasons NOT to use root.

The database password

This also is the password you in phpMyAdmin for the new user. The same note applies with the root account mentioned above.

That should cover it. Hopefully now you feel more confident in creating databases and have a little better understanding of them and what they do.