If you’re accessing your database from a remote server, you need to grant it access first following the instructions in our Remote MySQL tutorial and then use your domain name as the hostname.

In this article, we will shed some light on a very useful feature. One of the most useful features of cPanel is the ability to enable remote access to your MySQL Databases.

This is important because some third party applications or web developers, for example,  might need access to your database from their server or their local computer. In order for them to do this securely, you need to add their IP Addresses to a whitelist. Additional privileges might be needed to allow further changes to the databases. In this case, you should contact your hosting provider and let them know exactly what you need.

How to Configure Remote Access to MySQL Database

Here is how you go about doing this.

1. Log in to your cPanel account.

2. Navigate to the Database section and click on Remote MySQL ® .

3. In the Add Access Host box, you need to enter each individual IP Address and click on Add Host. You can get your own IP address at ip4.me. If your web developer is the person who requires access, ask them to visit that URL and to provide you with their own IP address.

Bellow this section you will see a list with all the added access hosts (or IPs). You can also remove IP addresses, if needed.

Another interesting feature is the possibility to add the “%” wildcard. This allows you to add an entire IP range to the access hosts list. While it may seem like an easy “fix”, the usage of the % is not advisable, as it presents some security risks.

How to connect remotely to MySQL Database using MySQL Workbench

MySQL Workbench is a visual tool for database architects and developers. MySQL Workbench provides data modeling, SQL development, and comprehensive database administration tools, and much more. MySQL Workbench is available on Windows, Linux, and Mac OS X. You can download it here.

Once MySQL Workbench is installed, please follow the steps below to connect to your database:

1. Open MySQL Workbench.
2. Click New Connection towards the bottom left of MySQL Workbench.
3. In the “Set up a New Connection Dialogue” box, Type your Database connection credentials. The credentials will be like the following:Connection Name: You can name this whatever you like.
   Connection Method: Standard (TCP/IP).
   Hostname: You can use your domain our your server’s IP address.
   Port: 3306
   Username: the user you created for the database.
   Password: the password for the database user that was created.
   Default Schema: This can be left blank.
4. Click Test Connection.
5. Type your password and click the “Save Password in Vault” checkbox. Click Ok.
6. MySQL Workbench should say “Connection parameters are correct“. Click Ok.
7. Click Ok again to accept the connection settings.
8. Now under the SQL Development section, you will see your connection listed in the Open Connection to Start Querying box. Click your newly created account.
9. Now you will see the databases list in the area on the left.

That’s all! Now you know how to connect remotely to your MySQL database.

]]> https://chemicloud.com/kb/article/how-to-configure-remote-access-to-mysql-database-in-cpanel/feed/ 0 https://chemicloud.com/kb/article/backup-mysql-database-in-cpanel/ https://chemicloud.com/kb/article/backup-mysql-database-in-cpanel/#respond Wed, 16 May 2018 14:12:08 +0000 https://chemicloud.com/kb/?post_type=article&p=1020 In this article we’ll show you how to back up a MySQL database in cPanel.

How to Backup a MySQL Database in cPanel

Here are the steps you need to take in order to back up a MySQL database in cPanel:

1. Log in to your cPanel account.

2. Navigate to the Files section and click on Backup.

3. In the next window, under the Download a MySQL Database Backup click on the name of the database in order to download it.

4. You will be prompted to save a compressed archive. Make sure you save the file in a secure location on your hard drive, preferably not on the same partition as the OS.

That’s all there is to it. You’ve successfully backed up your database. Now you are able to recover your database in case someone accidentally deletes it, or if you wish to make changes without risking destroying your original data.

Though cPanel

1) Log into cPanel.

2) Look for the “DATABASES” section and click on “MySQL® Databases”.

3) Look for the “Modify Databases” section, here you can either check for errors or repair a MySQL database. Select the database you want to check from the drop down menu and click on the “Check Database” button.

To repair a corrupted database just the database you want to check from the drop down menu and click on the “Repair Database” button.

A new interface will appear and the system will attempt to automatically check/repair the database. If the system cannot repair the database, it will attempt to determine the source of the corrupt data.

That’s it! Now you know how to check or repair database tables through cPanel.

Through phpMyAdmin

1) Log into cPanel.

2) Look for the “DATABASES” section and click on “phpMyAdmin”. You’ll be redirected to the phpMyAdmin interface.

3) From this new screen, we have direct access to all the databases created under this cPanel account, they are all listed on the left side.

4) Select the database you wish to check or repair.

5) You will now see all the database’s tables. Select the table(s) you wish to check or repair, or simply click Check All to select all tables.

6) Click the drop-down With selected: ]and choose Check table or Repair table. This will execute the CHECK/REPAIR TABLE SQL query on the selected tables and they will be checked or repaired.

That’s it! Now you know how to check or repair database tables through phpMyAdmin.

1) Click the database you wish to export, in this example we’ll use the chemiclo_demo database.

2) Next, click on the “Export” tab and select the format in which you want to export the database.

3) Click on the “Go” button to start the export/backup process of the entire database.

4) You can also export only certain tables from your database, from the export tab go to the “Export Method” section click on the “Custom – display all possible options“. This will show you a list of all the tables created in the database.

5) Select the tables you want to export, leave the other options unchanged and click on the “Go” button to proceed.

1) First, select the empty database:

2) Click on the “Import” tab and choose the database backup stored on your computer by clicking on the “Choose File” button.

3) Click “Go” after choosing the backup file to proceed with the import.

4) A message will appear indicating that your import has been successful and the database will be populated.

1) First, we’ll need to select the database, in this example, we’ll use the chemiclo_demo database.

2) We’ll see here all the tables within this database listed, click on the “Drop” link next to the table you wish to delete (drop).

3) A confirmation pop-up message will appear, make sure you are deleting the right table and click “OK“. A message will appear indicating that your table has been dropped and the table is no longer listed in the database’s list of tables.

4) We can also delete (drop) specific fields from this table by clicking the delete icon. In this example, we selected the test3 table and dropped the junk field.

5) A confirmation pop-up message will appear, make sure you are deleting the right field and click “OK”.

1) First, we’ll need to select the database, in this example, we’ll use the chemiclo_demo database.

2) You’ll notice that we have a couple of test tables already created, to create a new database table just enter the name of the table in the “Name” field and the number of fields you want for this table and click “Go”.

3) We’ve created the table “demo” and added two columns (name and phone), now we need to set the details of each field within this new table and select Type of data the column will hold.  Some of the most  common types are:

INT = INTEGER – used to store a number that can be written without a fractional component. (e.g. 2, 10, -44). An integer can be zero, positive, and negative.
CHAR = Characters – used to store character string value of fixed length, the maximum number is of characters is 255.
VARCHAR = Variable Length Characters – used to store variable length alphanumeric data and can store up to 65,535 characters.
TEXT = used for holding large amounts of text.
DATE = Will only hold dates.
DATETIME = Will hold both a date and a time.

4) We can also define the Length/Values if necessary, the CHAR type will require you to specify the maximum number of characters allowed. All the other fields are optional, however, if you need more detailed information about all the options available you can check the MySQL Reference Manual.

5) When finished, click “Save”.  A message will appear indicating that your table has been created.

1) Click the database table you wish to run a SQL query on. In this example, we’ll use chemiclo_demo.

2) Click on the SQL tab.

3) Enter the entire SQL query code and click “Go” when finished to execute. You’ll see a confirmation message if the query ran successfully or an error message if there was something wrong with your query.

PhpMyAdmin allows you to perform a wide range of operations with MySQL. In this tutorial, we’ll familiarize ourselves with databases and phpMyAdmin.

1) Log into cPanel

2) Look for the “DATABASES” section and click on “phpMyAdmin “, you’ll be redirected to the phpMyAdmin interface.

3) From this new screen, we have direct access to all the databases created under this cPanel account, they are all listed on the left side. In this example, we have the databases “chemiclo_demo” and “information_schema“. A database consists of one or more tables, the information is recorded in the tables.

4) You can also view all the databases created under this account if you click the “Databases” tab:

Let’s have look at the options available in the top menu from phpMyAdmin

• From the SQL tab, we can open an SQL Query window, here we can enter an entire SQL query code and execute it by pressing “Go“.
• In the Status tab, we’ll find information regarding the MySQL server since the last restart, like the MySQL uptime, number of active connections and traffic volume.
• We can export databases to our local computer if we click on the Export tab and hit “Go”.
• We can import databases from our local computer if we click on the Import tab and hit “Go”.
• In the Variables tab, you’ll see will see a list of the MySQL server system variables.
• In the Charsets tab, you’ll find all the charsets and collations supported by the MySQL server.
• In the Charsets tab, you’ll see a list with all the engines supported by the MySQL server

Let’s move back to the current databases installed on this cPanel account

If we click on the chemiclo_demo database all the tables created under this database will be listed. As we can see in this screen, this database has only 3 tables created.

From here we can see more information about the tables contained in the database, as well as perform several actions:

• Browse a table to see the individual records.
• See the Structure of a table and perform certain actions.
• Search through a table for specific keywords.
• Insert new rows (or fields) into a table.
• Empty or delete the contents of a table.
• Delete or drop an entire table altogether.