MySQL Database: A Beginner’s Guide

Some websites use databases to operate. These can be of different types, but the most common type of database used by websites is MySQL. Databases are used to record various types of data, but usually they are used to record the content of a website, information about its users, various settings, etc.

Databases are used by virtually all web development platforms, for example WordPress, Joomla, Drupal, Opencart, PrestaShop, Magento, etc.

Sitemap

How do I create a database?
How do I import an existing database?
How do I link a database to a website?
How do I access the content of a database?

How do I create a database?

To create a database, go to cPanel -> Databases -> MySQL® Database Wizard icon. You can also create it from the MySQL Databases link, where you can also manage the databases you have already created.

In step 1, specify the name of the database.

In step 2, assign a user and password to the database.

In step 3, specify the user’s privileges (99% specify All Privileges) and confirm the entry.

Congratulations, you have successfully created your database!

How do I import an existing database?

If you already have an existing database, you now need to import it into your hosting package. This can be done in cPanel –> Databases section –> phpMyAdmin icon.

On the left side, locate the database you created and import the existing database by clicking the Import button.

How do I connect the database to the website?

This depends on the system your website uses. To connect your website to the database, you will need to enter the database name, a username and a password to access the database.

Find out more about where you can check this information here.

How do I access the content of the database?

There are several ways to access the database, either directly through the server or your hosting package, or through other desktop applications that you can install on your computer. We advise that only advanced users edit the database further if it is really necessary.

Access via a hosting package

Directly through the server, users can access the phpMyAdmin interface, which you will find in the cPanel control panel (under Databases).

Access via other (external) programs

You can also manage your databases through different programmes. You will find many of them on the web, but some of the most popular are:

HeidiSQL (free) – we recommend using this program;
MySQL Workbench (free);
SQLyog (paid).

Before connecting to the database for the first time (remotely), you must first add your IP address from which you will access the database in the Remote MySQL category of the cPanel control panel.

This is a security mechanism in the cPanel system to further protect access to important data. If you are going to access the database from multiple locations (IP addresses), you will need to enter all IP addresses in the control panel. Users of dynamic IPs may quickly run into a problem here, as the IP address changes daily and you will need to re-enter the new IP address.

If you will only access the database occasionally, this is not a major problem, but if you will access it more frequently, you can also easily solve the problem by entering the following in the input field where you enter the allowed IP addresses: %.%.%.%.%

This entry will allow access to all IPs, which is less secure than allowing access only to certain IPs, but still keeps your data secure, as you must also enter the user password you have previously set to successfully access the databases.

Example of database access: HeidiSQL

For remote database access, we recommend using the free HeidiSQL software.

1. Download the installation file from the official website. Install HeidiSQL on your computer and then open the program.

2. You will be taken to the Session Manager, in which you can click on New in the bottom left.

3. This will create a new session to connect to the MySQL database. On the left-hand side, name the session as you wish, then on the right-hand side, edit the settings accordingly:

Network type: MySQL (TCP/IP)
Library: libmariadb.dll
Host / IP: vasadomena.si (you can also enter the IP of the server)
User: enter the username you set when you created the user to access the database (e.g. for WordPress, you can get the information in the wp-config.php file). You can also use the username to access the cPanel control panel.
Password: Enter the password to access the database (e.g. for WordPress, this can be found in the wp-config.php file). You can also use the password to access the cPanel dashboard.
Port: 3306
Databases: Separated by colon
Comment: You can leave this window blank.

4. All you have to do is click Open to connect to your website’s database.

You can now view and edit the databases on your hosting package.

If you use your cPanel username and password to connect, you will be able to edit all the databases on your hosting package. However, if you use the username and password of a specific database, you will only connect to that database.

How do I check that my connection is working through the console?

You can also check the functioning of your connection to the MySQL database via the console. See below how to check this using PuTTY.

1. First, make sure that you have the SSH connection option enabled to access the hosting package. Check the instructions for connecting to the hosting package using SSH.

2. Download PuTTY from the official website and install it on your computer.

3. Start the program, under Session -> Host Name (or IP address) enter the IP address of the server and for the Port specify 5050. Click Open to enter the SSH connection.

4. A console will open, in which you first enter the username that you use to access the cPanel control panel. Then enter the password to access cPanel.

5. Enter the command below to connect to the MySQL database, changing the capitalised information in the command accordingly:

HOST – enter the IP of the server (this information can be found in the cPanel control panel)
PORT_NUMBER – use port 3306
USERNAME – the username to access the database
DATABASE – name of the database

mysql -h HOST -P PORT_NUMBER -u USERNAME -p DATABASE

Example for SSH connection:

mysql -h 195.206.228.46 -P 3306 -u user123 -p my_wp_base

6. After entering the command to connect to MySQL, you will be prompted to enter the password to access the database in the console.

As shown in the picture above, the connection should now be successfully established!

For further assistance, please call us on 059 335 000 or email us at info@neoserv.si.