cPanel Tutorials

---

• File Manager Section
• Backup Management Section
• Database Section
• Domain Section
• Email Section
• Statistics/Metric Section
• FTP Section

Database Tutorial

What is MySQL?

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).

SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use. MySQL is an essential part of almost every open source PHP application. Good examples for PHP & MySQL-based scripts are WordPress, Joomla, Magento and Drupal.

● How to create a MySQL user and database using the MySQL Database Wizard?

By using the MySQL Database Wizard tool you can easily create a MySQL user and a database and then assign the user to the database. To access the tool, access your cPanel and click on the tool icon.



On the page that opens provide the desired MySQL database. Note that the database will always start with username_ prefix, where username is your cPanel user. Once ready, click the Next Step button.



On the next page you should provide the desired MySQL username and the password for it. Here the username is always prefixed with the cPanel username, similar to the database. When ready click the Create User button.



The user will be created for you and on the next page you are prompted to specify the desired permissions that the user has to the database. Choose the desired permissions and click the Next Step button.



That's it! Once the page loads you will see a confirmation message informing you of the successful completion of the operations.

● Removing MySQL Database & its users

Removal of Database & database user, Both these actions can be performed with the MySQL Databases tool in your cPanel.

Removing MySQL Database:
To remove a MySQL Database, first find it in the Current Databases section. Then click the Delete button next to the entry for the database.



A new page will appear asking you to confirm the removal of the database. To proceed, click the Delete Database button.



As soon as you hit the “Delete Database” button The database will automatically be removed and you will be presented with a page confirming the successful operation.

Removing MySQL User:
To remove a MySQL user, you have to find it in the Current Users section of the tool and then click the Delete button next to the entry for it.



A new page will appear asking you to confirm the removal of the user. To proceed, click the Delete MySQL Users button.



As soon as you hit the option “Delete user” The user will automatically be removed and you will be presented with a page confirming the successful operation.

● Connect to a MySQL Database

Learn how to connect to a database via PHP

Connecting to a database via PHP is an extremely important step because if your script cannot connect to its database, your queries to the database will fail.A good practice when using databases is to set the username, the password and the database name values at the beginning of the script code. If you need to change them later, this way you will be able to perform the change easily.

1$username="your_username";
2$password="your_password";
3$database="your_database";

You should replace your_username, your_password and your_database with the MySQL username, password and database that will be used by your script.

This will create three variables in PHP that will store the different MySQL connection details.Next you should connect your PHP script to the database. This can be done with the mysql_connect PHP function:

1$mysqli = new mysqli("localhost", $username, $password, $database);

With this line PHP connects to the MySQL database server at localhost with the provided username and password.After the connection is established you should select the database you wish to use. This should be a database to which your username has access to. To select a database, you can use the following command:

1$mysqli->select_db($database) or die( "Unable to select database");

With the above PHP uses the MySQL connection and with it - selects the database stored in the variable $database (in our case it will select the database "your_database"). If the script cannot connect it will stop executing and will show the error message "Unable to select database".

Another important PHP function is:
1$mysqli->close();

This is a very important function as it closes the connection to the database server. Your script will still run if you do not include this function. And too many open MySQL connections can cause problems for your account. Thus it is a good practice to close the MySQL connection once all the queries are executed.

You have connected to the server and selected the database you want to work with. You can start querying the database now.

● How to backup your MySQL database?

In order to create a dump file of a database used by your account you can use the phpMyAdmin tool available in your cPanel:



Once the tool page loads, you should select the database in question from the panel on the left side.



A new page will be loaded showing the structure of the selected database. To create a backup of the database, click the Export tab.



A download window will pop up prompting for the exact place where you would like to save the file on your local computer. It is possible that the download starts automatically. This depends on your browser's settings.

● How to restore your MySQL database from a backup?

To restore a database via phpMyAdmin, first choose the database you'll be importing data into. This can be done from the menu on the left.



A new page will be loaded showing the structure of the selected database. To import data inside the database, click the Import tab.



On the new page that opens, click the Browse button and select the backup that you want to import from your local computer. You have the option to pick the character set of the file from the drop-down menu just below the upload box. If you are not certain about the character set your database is using just leave the default one. Once ready, click the Go button to perform the import.



The import will start and once finished you will be redirected to a page with a confirmation notification that the import was successful.