PhpMyAdmin Tutorial: Create Database

In this part of our PhpMyAdmin tutorial we will describe the steps which must be followed in order to create a new database, add a table with records, create a database backup and perform a database restore procedure.

We will start with the database creation.

How to Create a MySQL Database?

Please note that you can not create a database directly through cPanel->PhpMyAdmin due to the lack of user privileges. However, you can easily create a new database from your cPanel->MySQL Databases. Navigate to the Create New Database box. Enter the database name in the New Database text field and click on the Create Database button.

The database name will be preceded by the cPanel username. For example, if your cPanel user name is user and you want to have a database named test, the actual database name will be user_test. You will get a confirmation message.

How to Add MySQL Database Tables?

Navigate to your cPanel->PhpMyAdmin tool and open the newly create database. It is empty and there are no tables.

Enter the table name and the number of fields. Click on the Go button to create the table.

On the next screen you should enter the fields' names and the corresponding properties. The properties are:

Type

Here you should pick the type of the data, which will be stored in the corresponding field. More details about the possible choices can be found in the official MySQL Data Types documentation.

Length/Values

Here you should enter the length of the field.  If the field type is "enum" or "set", enter the values using the following format: 'a','b','c'...

Collation

Pick the data collation for each of the fields.

Attributes

The possible attributes' choices are:

BINARY - the collation for the field will be binary, for example utf8_bin;

UNSIGNED -  the field numeric values will be positive or 0;

UNSIGNED ZEROFILL - the field numeric values will be positive or 0 and leading zeros will be added to a number;

ON UPDATE CURRENT_TIMESTAMP - the value for a data type field has the current timestamp as its default value, and is automatically updated;

Null

Here you define whether the field value can be NULL. More about the NULL value can be found in the corresponding MySQL documentation.

Default

This property allows you to set the default value for the field.

Extra

In the Extra property you can define whether the field value is auto-increment.

The radio buttons that come below define whether there is an Index defined for the particular field and specify the Index type.

Comments

Here you can add comments, which will be included in the database sql code.

At the end you can include Table comments and pick the MySQL Storage Engine and the Collation. Once you are ready, click on the Save button.

If you want to add more fields you should specify their number and click on the Go button instead of Save.

The database table will be created and you will see the corresponding MySQL query.

Now we will proceed with the populating of the table with data.

How to Add Content in a Database Table?

In order to add records in a database table click on the Insert tab.

Enter the data in the corresponding fields and click on the Go button to store it.

At the bottom of the page you will see a drop-down menu labelled Restart insertion with x rows . There you can pick the number of the rows that you can populate with data and insert at once. By default the value is 2.

The Ignore check box will allow you to ignore the data entered below it. It will not be added.

You can see the newly inserted record by clicking on the Browse tab.

You can edit or delete the record by clicking on the corresponding icons.

To insert more records, return to the Insert tab and repeat the procedure.

How to Backup a Database?

Once you are ready, you can create a backup of your database through the Export tab.

Select the tables which you want to be exported.

Leave the radio button selection to the SQL option.  The Structure and the Data check boxes should remain checked.

Select the Save as file check box and then click on the Go button.

In this way you will save the dump SQL file with your database structure and content on your local computer.

If you have a large database with a lot of records, the server timeout value can be reached. In such a case you can export the database in several batches. You can find more details in our MySQL Knowledge Base.

How to Restore a Database Backup?

You can restore your database backup from the Import tab. 

Click on the Browse button to select your database backup file from your local computer.

Pick the charset of the file from the corresponding drop-down menu.

If the file is too big, the MySQL server timeout can be reached. In such a case you can interrupt the import action. Then you can continue with the data import defining the number of the queries to be skipped from the file beginning. In this way you will skip the imported queries and continue from the point of the interruption.

Additionally you can pick the SQL compatibility mode of the imported file. You can find more details in the Server SQL Modes documentation.