Location : PHP Hosting - Web Hosting Knowledge Base - PHP MySQL Tutorial

Create MySQL Database and User

If you are familiar with managing MySQL database through Linux shell commands, you can send a request to helpdesk for enabling SSH access on your hosting account, otherwise you can follow the steps below to create MySQL database through hosting account control panel.

Create MySQL database and user through cpanel

Setting up MySQL database through your Cpanel hosting account control panel involves three steps :-

1. Create MySQL database

First, login to your hosting account control panel :-



The username and password is in the welcome login info email sent to your when you first sign up the hosting account, if you have forgotten the info please drop a note to helpdesk and we will help you out.

After login to hosting account control panel, click on SQL Database option in control panel to bring up the MySQL database management screen.

Enter your new MySQL database name in the empty field next to New Database and click Create Database button

If the database is successfully created, a status Database created is shown. Click Go back link to return to the database management screen.

2. Create MySQL user

In the Current Users: section in the database management screen, enter the new database username in the Username field and the password for this new database user in the Password field, hit Create User to setup the new mysql database user in MySQL server.

A status database user created is shown if the creation process complete successfully. Click Go back to return to the database management screen.

3. Grant new database user access to newly created MySQL database

Till now, a new MySQL database is created, a new MySQL user is created but you are not yet ready to access the database as there is yet to have any access privileges granted for the new MySQL user to access the new MySQL database.

To grant access, In the Add user to your databases section, select the new MYSQL user from the drop down box, and the MySQL database you wish this user to have access to from the second drop down box, hit Add User to Database button to grant access.

Creating MySQL database, user and grant access through MySQL shell

NOTE: In a typical cpanel shared hosting environment, hosting account userid has restricted access to mysql admin tasks such as create/drop database, grant/revoke user access, access to the mysql system tables. However, you can perform these mysql admin task through the MySQL Database option in hosting account control panel. Other than these, mysql table and record level tasks are possible in the MySQL shell.

Below list the mysql commands for creating database and user, as well as granting access for the user to the database with mysql root access for your reference :-

> #mysql -u root -p
> Enter password: mysql_root_password
> Welcome to the MySQL monitor. Commands end with ; or \g.
> Your MySQL connection id is 30 to server version: 4.0.24-standard
> Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
> mysql> create database testdb;
> Query OK, 1 row affected (0.00 sec)

Above BOLD command shows login to mysql shell with root user, and issue a mysql command in the mysql shell to create a database testdb.

> mysql> GRANT ALL PRIVILEGES ON testdb.*
> -> TO testdbuser@localhost
> -> IDENTIFIED BY 'password_for_testdbuser';
> Query OK, 0 rows affected (0.00 sec)

Above command create a mysql user testdbuser with password password_for_testdbuser and grant all access for this user to the database testdb, and limit this user to only connect to the database from local server (notice the @localhost) (same server as the mysql service resides).