How To Create A New Database Mysql

MySQL create database

Content:

What is a MySQL database?
How to add a new database using PHPMyAdmin
How to create a database using the SQL command line
How to create a database using NTC Hosting's Control Panel
Resources

What is a MySQL database?

Today, modern web applications like Joomla, Moodle and 4images, are dynamic. They are designed to store articles, posts, stories, pictures, movies and other content. To keep all that data organized and to have it displayed faster, those applications are using MySQL databases. The MySQL database consists of tables and every MySQL table stores specific information for the application. To better understand how the MySQL database stores your site's data let's take a look at a common Joomla database:

As you can see, Joomla keeps the database information separated in many tables. The users' data information (such as names, login credentials and passwords) is kept in the table jos_users and the content data (articles, news, posts) is in jos_content. The 'jos_' part of the database table's name is called prefix and is used to determine the application which is using the table. For example, 'jos_' is the default prefix for all tables created by Joomla, but if you want to host a second installation of Joomla and use the same database (not recommended) you can change the prefix (for example) to 'jo2_'. So, your first Joomla website will use the tables which start with jos_ and the second installation - the ones which start with jo2_.

How to add a new database using PHPMyAdmin?

Since the use of SQL databases and MySQL databases, in particular, is vital for content rich websites, it is crucial that website owners have the possibility to create such databases and manage their settings at any time. As we already mentioned, there is a possibility to host more than one application by using a single database. Actually, in practice this is not very convenient and also lowers you applications' security. That's why it is recommended to have separate databases for every application.

There are several ways to create a new database on a MySQL5 Server. The two basic ones are - through the phpMyAdmin interface and via the SQL command line, both requiring some basic MySQL administration skills from the users. In order to create a new database using the PHPMyAdmin tool, first you need to log in using your MySQL server's ROOT user account or another account with the appropriate privileges. Once logged in you will see the "Create new database" form on PHPMyAdmin's home page:

An example of the 'Create new database' section form in PHPMyAdmin

Put your new database name in the first field (my_db in our example) and then choose an encoding from the drop down list. When ready, click on the Create button to create your database.

How to create a database using the SQL command line?

As we have seen above, it is pretty easy to create a database using the PHPMyAdmin graphic user environment, but sometimes PHPMyAdmin is not accessible or not present on a web server. In such cases it is good to know how to create a database using the SQL command line. To enter the command line you need to be logged on to the MySQL Server as ROOT user or through an account which has enough privileges. To establish a root connection, start an SSH session using the terminal/console application if you're using Linux or MacOS, or use PuTTY (or other SSH client) on a Windows PC, then enter the following command:

An example of starting an SSH connection to a MySQL server

$ssh databasehost

Then you need to log in using your ROOT account by using the following line:

How to log in as ROOT on a MySQL server

$ mysql -u root -p

Now we are ready to create the new database. To do so, use a line similar to this:

An example of how to Create a Database in MySQL

CREATE DATABASE mydatabasename

We, at NTC Hosting, have simplified the MySQL database creation process for you to the maximum. Now you can create new MySQL databases and manage them directly from your web hosting Control Panel coming with our packages. There is no need to deal with the phpMyAdmin interface, nor bother yourself with the SQL query window or the shell prompt program. We ensure a quick and user-friendly MySQL database set-up process.

An example of how to Create a Database in MySQL

CREATE DATABASE mydb

Now your newly created database is accessible only for your ROOT user. To allow another user to access your database you need to use a query similar to this:

An example of how to GRANT privileges to a user for a new database

GRANT USAGE ON mydb.* to db_user@localhost IDENTIFIED BY 'db_passwd';

Now the user has some basic privileges but they are not enough to install a PHP script. If you need to use MySQL grant to add ALL privileges for the database to a user, use the following query:

An example of how to GRANT ALL privileges to a user

GRANT ALL ON mydb.* to db_user@localhost ;

Now let's try to establish a connection to our new database 'mydb' using the 'db_user' account via the SSH command line:

How to log in to a specific database on a MySQL server:

mysql -u db_user -p'db_passwd' mydb

How to create a database using NTC Hosting's Control Panel

As you can see, creating a database using the PHPMyAdmin tool is simpler than using the SSH and SQL command line. To keep the process even simpler than that, NTC Hosting provides all users with a MySQL Database management tool. Here is a video tutorial on how to create a database using our Control Panel

Movie tutorial on how to create a database

In short, to quickly create a MySQL database, simply log into your Control Panel and go to the MySQL Databases section under Site Management. There we'll need only two entries from you - the name of your new database and its password. Simply fill in the empty fields and hit the 'Create Database' button. That's all. Repeat the same simple steps anytime you need a new MySQL database added to your web hosting account.

Resources:

MySQL create database