MySQL

The number of databases you can create depends on the hosting plan that you are using. In case you need more databases, you can purchase additional database slots. You can create many tables within one MySQL database. That is, you can have several applications sharing one and the same database, as long as there is no conflict in the table names. Some software packages allow you to assign a prefix to their tables, so there will be no mix-ups with other software applications using the same database.

You can create MySQL 5 and MySQL 8 databases and users only through the MySQL Databases section of the hosting Control Panel. Note: The MySQL 5 and MySQL 8 servers are separate and do not share databases or users, so you can access MySQL 5 databases only with MySQL 5 users and MySQL 8 databases only with MySQL 8 users. This allows you to have two users for MySQL 5 and MySQL 8 with the same name, but with different passwords and privileges. Creating a MySQL database To create a MySQL database, you need to: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the Databases tab. Select the MySQL version of your database from the MySQL version drop-down menu. Specify a name for your database in the Database name field. The name of your database always starts with your hosting Control Panel username followed by an underscore. Select the collation for your database from the Collation drop-down menu. By default, the utf8mb4_general_ci collation is selected for MySQL 5 databases and utf8mb4_0900_ai_ci for MySQL 8 databases. Click on the Create button. Once your database is created, the system will automatically grant permissions to your master MySQL user. The name of your master MySQL user is the same as your hosting Control Panel username. Its default password is the same as your initial Control Panel password. Creating a MySQL user By default, your master MySQL user has full privileges over your databases. Due to security concerns, we recommend that you create and use a separate MySQL user for each software application installed on your account. You can create a MySQL user by following these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the MySQL users tab. Select the MySQL version for the user. Specify a name for your MySQL user in the MySQL user field. Enter the password for your MySQL user in the Password and Confirm password fields.Note: You will notice that there is a password strength indicator below the Password field. It will update in real time as you are entering your password. There are five distinct levels of password strength: Very Weak, Weak, Fair, Strong, and Very Strong. Along with the password strength indicator, you will also get information and hints in a tooltip beside the indicator. Note that the Add button will remain inactive (grayed out) if the password level is Very Weak or Weak. Select the Enable remote option from the Remote connections options if the MySQL user will be used for accessing your databases from a remote location. Click on the Add button.

We assume you already have a SQL dump/schema file of your database on your computer, or you have it uploaded on your hosting account with us. To import the SQL dump/schema file, you have two options: Using the Import Wizard Access the MySQL Databases section of your hosting Control Panel, select the MySQL version of your database from the MySQL version drop-down menu, and click on the Import button next to the database you wish to import into. After that, you can choose a file from your local computer for upload, or click on the or select an uploaded file link to import a file you have already uploaded to your account. If the database does not already exist, you need to create it first. Using phpMyAdmin Detailed information on importing a database through phpMyAdmin is available in our Importing a database using PhpMyAdmin article. In case the SQL file that you wish to import is larger than 20 MB, you should use the database Import Wizard described below.

Here are the three easiest ways to export the SQL dump/schema file of a database: via the MySQL databases section of the hosting Control Panel through phpMyAdmin via the Backup section of the hosting Control Panel Exporting a database via the MySQL Databases section of the hosting Control Panel If you wish to export your database via the MySQL Databases section of the hosting Control Panel, follow these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the Databases tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Export button next to the database you wish to export. Once the export is complete, an information () icon will appear next to your database. Click on that icon to see information about the exported MySQL dump file. Exporting a database through phpMyAdmin You can find step-by-step instructions on how to export a database through phpMyAdmin in our Exporting a database using phpMyAdmin article. Exporting a database via the Backup section of the hosting Control Panel You can use the Backup section of the hosting Control Panel to create a backup of all your MySQL databases. Detailed information on using the Backup section is available in our Backup article.

You can change the password for a MySQL user by following these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the MySQL users tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Edit () button next to the MySQL user whose password you would like to change. Enter the new password for your MySQL user in the Change password and Confirm password fields.Note: You will notice that there is a password strength indicator below the Change password field. It will update in real time as you are entering your password. There are five distinct levels of password strength: Very Weak, Weak, Fair, Strong, and Very Strong. Along with the password strength indicator, you will also get information and hints in a tooltip beside the indicator. Note that the Submit button will remain inactive (grayed out) if the password level is Very Weak or Weak. Click on the Submit button. Note: The MySQL 5 and MySQL 8 servers are separate and do not share users or databases, so if you have two users for MySQL 5 and MySQL 8 with the same name and change the password for one of these users, the password for the other user will not be changed.

By default, all created MySQL users are allowed to access the MySQL server locally through a script that runs on the server. In case you need to access your database using an application installed on your computer (e.g. NaviCat, MySQL Front, MySQL Control Center, or Query Browser), you need to allow remote connections for the respective MySQL user. On our servers, remote access is enabled per MySQL user, so when you enable remote connections for a MySQL user, this MySQL user will be able to connect remotely to all databases it is associated with. Enabling remote access for a MySQL user To enable remote connections for a MySQL user on our servers, you need to follow these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the MySQL users tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Edit () button next to the MySQL user for which you would like to enable remote connections. Select the Enable option from the Remote connections section. Click on the Submit button. Once remote connections are enabled, the MySQL user will be able to connect from remote locations (e.g. from your local computer using MySQL Control Center or MySQL Front) to all databases it is associated with. Note: The MySQL 5 and MySQL 8 servers are separate and do not share databases or users, so if you have two users for MySQL 5 and MySQL 8 with the same name and enable remote connections for one of these users, the remote connections setting will not be changed for the other user. MySQL settings for remote connections You can find the MySQL host and port that you need to use in your MySQL client in the MySQL Connection Settings information box from the hosting Control Panel > MySQL Databases section. Remote access and MySQL views If you are using views in your MySQL databases, enabling/disabling remote connections for your master MySQL user may lead to problems with these views, due to the change of the type of access. Therefore, in case you have MySQL views in any of your databases, it is strongly recommended that you use a separate MySQL user for remote connections.

To associate a MySQL user with a database, you need to grant some privileges for that MySQL user to the database by following these steps: Note: The MySQL 5 and MySQL 8 servers are separate and do not share databases or users, so you can grant privileges to MySQL 5 databases only to MySQL 5 users and privileges to MySQL 8 databases only to MySQL 8 users. If you have two users for MySQL 5 and MySQL 8 with the same name, and modify the privileges for one of these users, the privileges for the other user will not be changed. Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the Privileges tab. Select the MySQL version of your database from the MySQL version drop-down menu. Select your database from the Database name drop-down menu. Select the MySQL user from the MySQL user drop-down menu. From the Privileges list, select the privileges that you wish to grant the MySQL user to the database. Click on the Associate button. By default, your master MySQL user has full privileges over your databases. Due to security concerns, we recommend that you create and use a separate MySQL user for each software application installed on your account.

If a MySQL user does not have any privileges over a database, you should associate it with that database. Detailed instructions on the process are available in our Associating a MySQL user with a database article. Note: The MySQL 5 and MySQL 8 servers are separate and do not share databases or users, so you can grant privileges to MySQL 5 databases only to MySQL 5 users and privileges to MySQL 8 databases only to MySQL 8 users. If you have two users for MySQL 5 and MySQL 8 with the same name, and modify the privileges for one of these users, the privileges for the other user will not be changed. To modify the privileges of a MySQL user for database, you need to follow these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the Privileges tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Edit () button next to the database and MySQL user whose permissions you would like to change. From the Privileges section, select the new privileges that you wish to grant to the MySQL user. If you remove all privileges, the association between the MySQL user and the database will be removed. Click on the Associate button. Note: By default, your master MySQL user always has full privileges over your databases. Due to security concerns, we recommend that you create and use a separate MySQL user for each software application installed on your account. If you would like to remove all privileges a MySQL user has over a database, follow these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the Privileges tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Remove () button next to the database and MySQL user whose permissions you would like to remove.

You can delete MySQL 5 and MySQL 8 databases and users only through the MySQL Databases section of the hosting Control Panel. Note: The MySQL 5 and MySQL 8 servers are separate and do not share databases or users, so if you have two databases or users for MySQL 5 and MySQL 8 with the same name and delete one of them, the other one will not be deleted. Deleting a MySQL database To delete a MySQL database, you need to follow these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the Databases tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Delete button next to the database you wish to delete. Click OK in the confirmation window. Note: MySQL users that were associated only with the deleted database will not get deleted automatically. You should delete such MySQL users manually. Deleting a MySQL user You can delete a MySQL user by following these steps: Log in to your hosting Control Panel. Enter the MySQL Databases section. Navigate to the MySQL users tab. Select the MySQL version of your database from the MySQL version drop-down menu. Click on the Delete () button next to the MySQL user you wish to delete. Click OK in the confirmation window.

First, you need to create an SQL dump file from your old hosting provider server. Once you have the database SQL dump file, you need to create a database on our server and import the file in it. (see our Creating a MySQL database/user and Importing a database articles).

The phpMyAdmin program is available on the server, and you can use it to manage your database content. You can log into phpMyAdmin via the phpMyAdmin section of the hosting Control Panel or by using the phpMyAdmin link from the MySQL Databases section of the hosting Control Panel. Detailed information on using the phpMyAdmin program is available in the Using phpMyAdmin section of our online documentation. Note: There are separate phpMyAdmin programs for MySQL 5 and MySQL 8, so you cannot manage MySQL 5 databases if you are logged in the phpMyAdmin program for MySQL 8.

If you want to grant someone else (e.g. a developer) access to a MySQL database, you should not provide them with Control Panel access; you can create an additional MySQL user for them. You can manage your MySQL databases and users at the hosting Control Panel's MySQL Databases section. If this is a new project, you should create a new database and MySQL user for it first. For security reasons, databases can be created only through the hosting Control Panel. Detailed instructions on the process are available in our Creating a MySQL database/user article. Once you have the database and MySQL user created, you should associate the MySQL user with the database by following the instructions from our Associating a MySQL user with a database article. Unless you have a reason to do otherwise, you can select all available privileges for the new MySQL user. Your developer may need direct access to the database for manual manipulations. You can provide them with a link to access the phpMyAdmin tool which is available on all our servers. Be sure to provide them with the URL of the phpMyAdmin link, not the (log in) link next to it. In case your developer needs remote access to the database via an application that is installed on their computer (e.g. MySQL Workbench), you will need to enable remote connections for their MySQL user. The process is explained in details in our Enabling remote access to a MySQL database article.If your developer needs to upload files on the server, you should create an FTP user for them. You can find step-by-step instructions on how to do this in our FTP Manager article. Important: You may wish to change the password for the MySQL user once the developer no longer needs it. However, if any software installed under your account connects through their MySQL user, you will need to update the password for that software in its corresponding configuration files.

You can create databases and assign MySQL users to them only through the MySQL Databases section of the hosting Control Panel.

The MySQL 5 and MySQL 8 database servers use different ports and sockets for incoming connections, so you will have to make sure that you use the correct database server settings in the configuration of your application. In the table below, you can find the correct database server settings for MySQL 8 databases for some of the most popular software applications: Software ApplicationMySQL 8 server/hostMySQL 8 port Baïkal [0.8.0+] 127.0.0.1:3308 - Bugzilla [5.0.6+] Not supported - CMS Made Simple [2.2.8+] 127.0.0.1:3308 - Concrete CMS [8.5.5+] 127.0.0.1:3308 - Coppermine [1.5.46+] 127.0.0.1:3308 - Drupal [8.6.3+] 127.0.0.1 3308 e107 [2.1.9+] 127.0.0.1:3308 - Joomla [3.9.0+] 127.0.0.1:3308 - LimeSurvey [3.15.5+] 127.0.0.1:3308 - Magento [2.1.15] Not supported - Magento [2.2.6] Not supported - Magento [2.4+] 127.0.0.1:3308 - Matomo [3.7.0+] localhost:/tmp/mysql8.sock - MediaWiki [1.31.1+] localhost:/tmp/mysql8.sock - MODX Revolution [2.6.5-pl+] 127.0.0.1:3308 - Moodle [3.5.3+] 127.0.0.1:3308 - Nextcloud [16+] localhost:/tmp/mysql8.sock - OpenCart [3.0.2.0+] 127.0.0.1 3308 osCommerce [2.3.4.1+] 127.0.0.1:3308 - phpBB3 [3.2.4+] 127.0.0.1 3308 PrestaShop [1.7.4.4+] 127.0.0.1:3308 - Roundcube [1.3.8+] 127.0.0.1:3308 - Roundcube [1.4.7+] 127.0.0.1:3308 - SMF [2.0.15+] 127.0.0.1 3308 Tiki Wiki [18.2+] 127.0.0.1:3308 - WebCalendar [1.2.9+] 127.0.0.1:3308 - WordPress [5+] localhost:/tmp/mysql8.sock - XOOPS [2.5.9+] 127.0.0.1:3308 - Zen Cart [1.5+] 127.0.0.1:3308 -   If your software application is not listed here, and you experience issues configuring it with a MySQL 8 database, please contact our support team for assistance.

The "Database converter" tab on the MySQL Databases page of the hosting Control Panel allows you to convert your existing MySQL 5 databases to MySQL 8. The converter automatically takes care of creating the same MySQL 8 database, usernames, and assigned privileges, making it easy to upgrade your applications to use MySQL 8. To convert a MySQL 5 database to MySQL 8, you should go to the "Database converter" tab and select the source database. You will be presented with a list of associated MySQL usernames to convert as well. The master MySQL username of the hosting account is the same as the username of the Control Panel. We generally recommend that you do not use this username for database access; instead, you should create separate MySQL usernames for your applications. WordPress Note that if you use WordPress, the converter will take you to the WordPress Manager of the Control Panel where you can automatically convert and configure your WordPress application to use the new MySQL 8 database with the click of a single button. In that case, no further action will be necessary on your part. Other applications Clicking on the "Convert" button will make a copy of your data from MySQL 5 to MySQL 8: When this is done, you can use the "Search" feature to find and edit the files in which the database name is mentioned (the files must also contain a valid MySQL username for the search tool to find them). This way, you can be certain that you won't miss updating the configuration of an application that uses the converted database: The following table shows the locations of the configuration files of the most common applications and the changes needed for them to work with MySQL 8 on our servers: ApplicationConfiguration fileMySQL 5 hostnameMySQL 8 hostnameOther required configuration changes WordPress wp-config.php 'localhost' 'localhost:/tmp/mysql8.sock'   Joomla configuration.php 'localhost' 'localhost:/tmp/mysql8.sock'   Drupal sites/default/settings.php 'localhost' or '127.0.0.1' '127.0.0.1' Port should be changed from '3306' to '3308' Magento app/etc/env.php 'localhost' '/tmp/mysql8.sock'   PrestaShop app/config/parameters.php 'localhost' 'localhost:/tmp/mysql8.sock'   Nextcloud config/config.php 'localhost' 'localhost:/tmp/mysql8.sock'   After you save the changes in your configuration file (or files, depending on the application), it will work with the new MySQL 8 database.

NOTE: You can use the Database Converter tab on the MySQL Databases page of the Control Panel to perform MySQL 5 to MySQL 8 conversions automatically. MySQL 8 is the latest stable version of the popular database engine. It comes with security and performance enhancements over MySQL 5. Currently, both MySQL 5.7 and MySQL 8.0 are available on our servers. If your application supports MySQL 8, you can migrate your database from MySQL 5 to MySQL 8 to benefit from the improvements in the MySQL 8 engine. The latest stable versions of WordPress, Joomla, Drupal, Magento, PrestaShop, and Nextcloud should work without issues with MySQL 8. In order to run on the same machine, the MySQL 5 and MySQL 8 database processes have to use different ports and sockets for incoming connections. This means that you will have to update the configuration of your application in order to switch to using your new MySQL 8 database after migrating. Before you begin You don't need to create a backup of your MySQL 5 database before migrating it to MySQL 8. The existing MySQL 5 database will not be modified in any way, so you don't have to worry about breaking it. Of course, you can easily create a database backup from the Backup page of the Control Panel of your hosting account, just to be on the safe side. If your application has built-in maintenance mode, we recommend that you enable it here. While this step is not required, it will prevent any desynchronization issues during the migration. In this article, we will use a stock WordPress installation made using the WordPress Manager in the Control Panel as an example. Creating a database dump from MySQL 5 First, you have to create a database dump of your MySQL 5 database. This can be done on the MySQL Databases page of the Control Panel: There, you have to click on the "Export" button next to the database in the list. The database dump will be created in the ~/private/MySQL_Dumps/ directory of your account. Creating the new MySQL 8 database After that, you can start configuring your new database by switching to "MySQL 8" on the "MySQL Version" dropdown menu. At this point, you have to create the new MySQL 8 database. You can use the same name as the MySQL 5 one. Also, make sure that the collation is set to the default utf8mb4_0900_ai_ci value: This is the most up-to-date collation available with MySQL 8, and it will ensure that the full range of Unicode characters can be saved in the new database without issues. Importing the data from the MySQL dump Choose the database in the "Database name" dropdown menu under the "MySQL import wizard" section, and click on the "select an uploaded file" link. A popup window will appear that will let you browse the ~/private/MySQL_Dumps/ directory of your account. You should select the database dump file created earlier: Once this is done, you should change the "Encoding" used for the import to utf8mb4: This is the most reliable encoding with the best Unicode support that you can choose with MySQL 8. Clicking on the "Import" button will migrate your MySQL 5 data into your new MySQL 8 database. After the import is completed, you will have a MySQL 8 database with the same data in it as the MySQL 5 one, and you can proceed to configuring your application to use the new database. Creating the new MySQL 8 username for your database Most applications have their own dedicated MySQL username. The easiest option to migrate to MySQL 8 is to create the same username for it as well. For this, we recommend that you open the configuration file of your application in the File Manager of the Control Panel in a new browser tab and scroll to the database connection settings. Here is a table showing the location of the configuration files of the most popular applications: ApplicationConfiguration file WordPress wp-config.php Joomla configuration.php Drupal sites/default/settings.php Magento app/etc/env.php PrestaShop app/config/parameters.php Nextcloud config/config.php In this case, we edit the wp-config.php file in the WordPress installation directory: Using the MySQL connection configuration in this file, you will be able to create the same MySQL 8 username on the "Usernames" tab of the "MySQL Databases" page of the Control Panel. Make sure that you select the correct database in the "Database name" dropdown when creating the username. This will automatically attach all required permissions to the username: This way, the configuration changes needed to make the application work with MySQL 8 will be minimal. Configuration updates At this point, you can update the configuration file of your application and update the MySQL connection details so that MySQL 8 is used. Since we made the rest of the MySQL connection details the same, only the hostname for the connection needs to be updated. In our example, the value of the DB_HOST constant should be changed from localhost to localhost:/tmp/mysql8.sock. The value of that setting varies depending on your application. For the most popular applications, the required changes are described in the following table: ApplicationConfiguration fileMySQL 5 hostnameMySQL 8 hostnameOther required configuration changes WordPress wp-config.php 'localhost' 'localhost:/tmp/mysql8.sock'   Joomla configuration.php 'localhost' 'localhost:/tmp/mysql8.sock'   Drupal sites/default/settings.php 'localhost' or '127.0.0.1' '127.0.0.1' Port should be changed from '3306' to '3308' Magento app/etc/env.php 'localhost' '/tmp/mysql8.sock'   PrestaShop app/config/parameters.php 'localhost' 'localhost:/tmp/mysql8.sock'   Nextcloud config/config.php 'localhost' 'localhost:/tmp/mysql8.sock'   After you save the file, the migration to MySQL 8 will be completed. Done Your application now works using the new MySQL 8 database. Disable maintenance mode (if applicable) and check your website for any issues. We recommend that you delete the obsolete MySQL 5 database and users after verifying that your website is working correctly with the new MySQL 8 database.

The official reference manuals of the MySQL versions supported on our servers can be found on the MySQL site: MySQL 5.7 - https://dev.mysql.com/doc/refman/5.7/en/ MySQL 8.0 - https://dev.mysql.com/doc/refman/8.0/en/

If you want to change the MySQL time zone setting for your MySQL session, you should execute the following SQL query right after the MySQL connection has been established in your scripts: SET time_zone = 'custom_timezone'; Make sure that you replace custom_timezone with the name of the desired time zone. A list of the valid time zones is available in the zone.tab file. Please refer to the MySQL Server Time Zone Support article of the official MySQL documentation for additional information.

To access a MySQL database from your PHP scripts, you can use localhost or 127.0.0.1 as database server name in your connection string. The database port will depend on the MySQL version of your database. For MySQL 5, you can use 3306 (the default port), while for MySQL 8, you will need to use port 3308. If your script supports UNIX sockets, you can use /tmp/mysql8.sock as socket for MySQL 8. Here is an example connection string for MySQL 5: mysqli_connect('localhost', 'mysql_user', 'mysql_password','mysql_database'); Since the default port is used, there is no need to define the port for MySQL 5 connections. Some applications may not work correctly with ports, so you may encounter issues when connecting to your MySQL 8 database. In such events, you can refer to the following example connection strings for MySQL 8 which use different settings: mysqli_connect('127.0.0.1:3308', 'mysql_user', 'mysql_password','mysql_database'); mysqli_connect('127.0.0.1', 'mysql_user', 'mysql_password', 'mysql_database', '3308'); mysqli_connect('localhost', 'mysql_user', 'mysql_password', 'mysql_database', null, '/tmp/mysql8.sock'); You can find the correct database server settings for MySQL 8 for some of the most popular software applications in our MySQL 8 database server settings article.

To access your MySQL database from your Perl scripts, you can provide the following database server name in your connection string: localhost For MySQL 8, the database server name is the same, but you will need to set the connection port to 3308. Here are example connection strings for MySQL 5: DBI->connect('DBI:mysql:localhost:database=mysql_database', 'mysql_user', 'mysql_password') or die "Database connection failed"; and MySQL 8: DBI->connect('DBI:mysql:localhost:database=mysql_database;port=3308', 'mysql_user', 'mysql_password') or die "Database connection failed"; You can find the correct database server settings for MySQL 8 for some of the most popular software applications in our MySQL 8 database server settings article.