Applicable Plans - All Cloud Hosting Plans

MySQL and phpMyAdmin

Overview

This User Guide is designed to help you install MySQL and phpMyAdmin on your Virtual Server, and to do some minor configuration. This User Guide is not intended to be a tutorial on how to use MySQL. You will need to consult the official MYSQL documentation or any of the several available books about MySQL if you need to come up to speed on how to actually use the database.

If you are using CentOS 7, your Virtual Server will use MariaDB by default, unless you are using a CentOS 7 template that specifically has MySQL in the template name.

If you are using a CentOS 7 template and need to install MySQL, you will need to use the official MySQL Community repository or a third party repository - the eApps Application Repository for CentOS 7 templates does not include MySQL. Instructions for adding the MySQL Community repo are here - A Quick Guide to Using the MySQL Yum Repository.

More information about MariaDB can be found in the MariaDB User Guide, which also links to the official MariaDB documentation.

MySQL and MariaDB official vendor repositories

You can configure ISPManager 5 to use the official vendor repositories provided by MySQL and MariaDB. These repositories typically have multiple major versions available and are generally kept more up to date. For more information, please see the ISPManager documentation here: https://doc.ispsystem.com/index.php/How_to_change_MySQL_version 

Installing MySQL and related applications
    Installing MySQL and related applications using the Control Panel
    Installing MySQL and related applications using the command line

Configuring phpMyAdmin

Creating databases and database users
    Creating databases using a Control Panel
    Creating databases using phpMyAdmin
    Creating databases and users using the command line

Importing content to a MySQL database
    Importing content using phpMyAdmin
    Importing content using the command line

Stopping and starting MySQL
    Stopping and starting MySQL using a Control Panel
    Stopping and starting MySQL using the command line

MySQL Configuration
    Using a my.cnf file
    Sample my.cnf configurations

MySQL remote access setup

Backing up your MySQL databases
    Backing up your databases using a Control Panel
    Backing up your databases using phpMyAdmin
    Backing up your databases using mysqldump
    Backing up your databases using the Enterprise Backup Service

Installing MySQL and related applications

To use MySQL you will need to install the MySQL database. You can also install phpMyAdmin to manage the database from a GUI application. If you are going to use phpMyAdmin, you will need to install PHP first, and then phpMyAdmin. If PHP is not installed, the installation of phpMyAdmin will fail.
If you are going to use MySQL with an application like WildFly, JBoss, Tomcat, or GlassFish, you will also want to install MySQL Connector/J.

You can install MySQL and any related applications from your Control Panel and also from the command line of the Virtual Server.

Installing MySQL and related applications using the Control Panel

Installing MySQL and related applications using the ISPmanager Control Panel

If you are using the ISPmanager Control Panel, you can install MySQL (and PHP and phpMyAdmin) from that Control Panel.

NOTE - if you have ISPmanager 5 and a CentOS 7 template, then ISPmanager will install MariaDB, even though Features will say MySQL. If you need to install MySQL, PHP, and phpMyAdmin on an ISPmanager 5 and CentOS 7 template, you will need to install all these applications from the command line. You will also not be able to manage your databases using ISPmanager - you will need to use either the command line or phpMyAdmin. See Installing MySQL on CentOS 7 for more information.

• For ISPmanager 4, go to Server Settings > Applications. Install MySQL, and make sure to install PHP before installing phpMyAdmin. More information on installing applications can be found here - Installing Server Applications. Note that in ISPmanager 4, phpMyAdmin is called MySQL Management Tool.

• For ISPmanager 5, go to Settings > Features. Install MySQL, and make sure to install PHP before installing phpMyAdmin. More information on installing applications can be found here - Installing Features (Server Applications). Note that in ISPmanager 5, PHP is installed by clicking on Web-server (WWW), then on Edit in the upper left corner, and then select PHP and click on Apply changes. This will install PHP. After PHP is installed you can install phpMyAdmin.

Information on how to tell which version of ISPmanager you are using can be found here - ISPmanager versions

Installing MySQL and related applications using the command line

MySQL and PHP can be installed from the command line of the Virtual Server using yum. To install applications using yum you will need to be able to connect to the Virtual Server using SSH and work as the root user.

NOTE - if you are installing MySQL on a CentOS 7 template, see the section below: Installing MySQL on CentOS 7

• MySQL - install MySQL using the yum install -y mysql-server command

• PHP - install PHP using the yum install -y php command

• phpMyAdmin - install phpMyAdmin after installing PHP using the yum install -y phpMyAdmin command

Installing MySQL on CentOS 7

If you are using a CentOS 7 template and need to install MySQL, you will need to use the command line and install from the official MySQL Community repo or another third-party repo of your choice. Instructions on adding the MySQL Community repo are here - A Quick Guide to Using the MySQL Yum Repository.

Once you have added the Community repo, you will be able to install MySQL and related applications using the commands from Installing MySQL and related applications.

If you are installing phpMyAdmin, make sure install that from the command line also. If you have a CentOS 7 template and install phpMyAdmin from ISPmanager, then ISPmanager will install MariaDB and overwrite your MySQL installation.

NOTE - if you install MySQL on a CentOS 7 template, you WILL NOT be able to manage your databases or database users from ISPmanager. You will need to manage your databases from either the command line or phpMyAdmin.

Configuring phpMyAdmin

Depending on which template you used to create your Virtual Server, you may also need to install one or more PHP extensions, usually the mcrypt extension, and sometimes the mysql extension and the mbstring extension. You will know which extensions that you need to install by the errors or warning messages you get when trying to access phpMyAdmin. The method to install PHP extensions will vary by what Control Panel you are using.

• ISPmanager 4 - go to Server Settings > PHP extensions. Click on Install in the upper right corner, choose the PHP extensions you need to install from the drop down menu. There are a large number of PHP extensions in the list, so you may need to scroll down and search to find the extension you are looking for. More information about installing PHP extensions can be found here - PHP extensions.

• ISPmanager 5 - go to Settings > PHP extensions. Select the PHP extension you need to install, and click on Install in the upper left corner. More information about installing PHP extensions can be found here - PHP extensions

Once you have installed the required PHP extensions, log out of phpMyAdmin and log back in.

Creating databases and database users

MySQL databases can be created from the Control Panel, phpMyAdmin, and the command line.

Creating databases using a Control Panel

Creating databases using the ISPmanager Control Panel

In ISPmanager, you need a User that will own the database. This user is not the same user who will access the database. This user is generally the same user that owns the website or web application that is connected to the database. The same user can own multiple databases.

If you do not already have a User created, you will need to do so.

• ISPmanager 4 - Users are created in Accounts Management > Users. More information can be found here - Creating Users.

• ISPmanager 5 - Users are created in Accounts > Users. More information can be found here - Creating Users

Make sure to understand that if you delete the user that is the database Owner all databases associated with that user will be deleted. This also deletes any WWW domains and E-Mail domains (and associated e-mail addresses) associated with that user. Proceed with caution.

Once you have created the User, you can create the actual database and the user that will access the database.

• ISPmanager 4 - go to Management Tools > Databases. This is where you will create a database and a user. More information on creating a database and user can be found here - Creating Databases.

• ISPmanager 5 - go to System > Databases. This is where you will create a database and a user. More information on creating a database and user can be found here - Creating Databases.

Creating databases using phpMyAdmin

phpMyAdmin is a browser based application that will allow you to manage your MySQL databases. To use phpMyAdmin, PHP must be installed.

phpMyAdmin is a very powerful application, and it is impossible to cover all aspects of it in this User Guide. If you need assistance with phpMyAdmin beyond creating a user or a database, you will need to refer to the official documentation, found on the phpMyAdmin home page - http://www.phpmyadmin.net/home_page/docs.php

Connecting to phpMyAdmin

To connect to phpMyAdmin, go to https://eapps-example.com/myadmin/, substituting your domain name or server IP address for eapps-example.com.

• Username - to create a database, log in as the MySQL root user (this is not the same as the system super user)

• Password - you will need to use the MySQL root password to log in to phpMyAdmin:

• ISPmanager 4 - go to Server Settings > Database servers. Click on the name of the database server (MySQL), and then on Edit. The MySQL root password will be shown on this screen.

• ISPmanager 5 - go to Settings > Database servers. Click on the name of the database server (MySQL), and then on Edit. The MySQL root password will be shown on this screen.

Creating a new database and database user

Once you have logged in to phpMyAdmin, click on the Databases tab. This shows all the existing databases, and the Create new database text box where you can enter the name of the new database.

Once you have entered the database name, click on Create. This will create the database, which will be shown in the list of databases in both the left navigation pane and just below the Create new database text box.

To create a user for the new database, click on Check Privileges to the right of the new database in the list.

This actually takes you to the Privileges tab, which shows all the users who can access the new database.

To add a new user, click on Add a new user.

Login Information

• User name (Use text field): - enter the username for this database

• Host (Any host): - choose localhost from the drop down list

• Password (Use text field): - enter a password for the new user

• Re-type: - retype the password to confirm

• Generate Password – click on Generate to have phpMyAdmin generate a password for you if you wish

Database for user

• Select "Grant all privileges on database name_of_database" where the database name is the one you just created.

Global privileges

• This allows the user global privileges to the database server. Check or uncheck these as your needs require.

Once you have added your new user and set the database and privileges, click Go in the bottom right corner of the screen. This will create the new user for the database.

Creating databases and users using the command line

To add a MySQL database and user from the command line, you will first need to connect to the command line of the Virtual Server.

From the command line, connect to MySQL using the mysql -uroot -p command. You will need to know the MySQL root password, which can be found in the /root/.my.cnf file.

Create the new database

Create the new database using the create database db_name command.

Create the database user

Create a user that can access this database, along with a password for that user. The command to use will look like this:

GRANT ALL ON example_database.* TO example_user@localhost IDENTIFIED BY "examplepass"; (You will have to substitute your database name and your own database user and password)

Test the new database user

Exit MySQL with the quit command, and then try to log in with the new user and password you just created.

Importing content to a MySQL database

There are several methods available to upload or import data into a MySQL database. Data can be imported from the command line or uploaded through phpMyAdmin.

All of these examples assume that you have a valid MySQL dump file in .sql format to upload or import

Importing content using phpMyAdmin

To import a MySQL dump file in .sql format from phpMyAdmin, login to phpMyAdmin with the database username and password of the user who owns the database. Click on the name of the database where you want to import your content, and once that screen loads, click on the Import tab.

• File to Import: - click Choose File to browse your computer for the file to upload. Notice the information about the file compression options and the format of the file name. Be aware that by default the maximum size file that phpMyAdmin can upload is 2048KiB, or 2 MB. See below for how to change that.

• Partial Import: - this allows the import of the file to be interrupted if a PHP timeout might happen. Check or uncheck this as your needs require.

• Format: - choose the format from the drop down menu that matches the type of file you are trying to import. SQL is the default, and is used for any .sql files.

• Format-Specific Options: - choose the options that you need. Click the question marks (?) for each option to see the documentation specific to it.

Once you have chosen your file and made the other selections, click Go to import the file into the database.

If your SQL file is larger than 2 MB, you will need to change the value for File minimum size in the PHP configuration. To do this from ISPmanager, go to Server Settings > PHP configuration.

Change the value for File minimum size to be 5 MB larger than your SQL file, and click OK. This makes changes to the /etc/php.ini file, and restarts the Apache web server.

Importing content using the command line

This example assumes you are familiar with working from the Linux command line, and can easily navigate the file system using standard Linux commands. There is no file size restriction on a MySQL dump file that is being imported from the command line (other than the disk size limitations of your Virtual Server).

See the User Guide - Connecting to your Virtual Server (SSH) - http://support.eapps.com/ispmgr/ssh for more information on how to connect to your VS. The user you connect with to the Virtual Server is not important, what is important is that you import the database as the correct database user.

In this example, a MySQL dump file in sql format called example.sql is being imported to the example_database, which is owned by the example_user database user.

The command to use will look like this: mysql -u db_user -p db_name < file.sql

To verify that the content was imported, you can connect to MySQL as the database user, and view the tables.

This should match what you know to be in the SQL file you imported.

Stopping and starting MySQL

MySQL can be stopped/started/restarted from the Control Panel or from the command line of the Virtual Server.

Stopping and starting MySQL using a Control Panel

Using the ISPmanager Control Panel

The way to start, stop, and restart MySQL from ISPmanager will depend on which version of ISPmanager you are using.

• For ISPmanager 4, go to Management Tools > Services, and highlight the MySQL service. Then click on Stop, Start, or Restart in the upper right corner. More information about managing service in ISPmanager 4 is available here - Managing Services

• For ISPmanager 5, go to System > Services, and highlight the MySQL service. Then click on Start, Stop, or Restart in the upper left corner. More information about managing services in ISPmanager 5 is available here - Managing Services

Stopping and starting MySQL using the command line

You can stop, start, and restart MySQL from the command line. To do this, you will need to connect to the Virtual Server using SSH, and be able to work as the root user.

Note that the name of the MySQL service is mysqld, not mysql.

Check the status of MySQL

• For CentOS 6, use the service mysqld status command:

  [root@example ~]# service mysqld status mysqld (pid 1553) is running... [root@example ~]#

• For CentOS 7, use the systemctl status mysqld command:

  [root@example ~]# systemctl status mysqld mysqld (pid 1553) is running... [root@example ~]#

Stop MySQL

• For CentOS 6, use the service mysqld stop command:

  [root@example ~]# service mysqld stop Stopping MySQL:                                            [  OK  ] [root@example ~]#

• For CentOS 7, use the systemctl stop mysqld command:

  [root@example ~]# systemctl stop mysqld Stopping MySQL:                                            [  OK  ] [root@example ~]#

Start MySQL

• For CentOS 6, use the service mysqld start command:

  [root@example ~]# service mysqld start Starting MySQL:                                            [  OK  ] [root@example ~]#

• For CentOS 7, use the systemctl start mysqld command:

  [root@example ~]# systemctl start mysqld Starting MySQL:                                            [  OK  ] [root@example ~]#

Restart MySQL

• For CentOS 6, use the service mysqld restart command:

  [root@example ~]# service mysqld restart Stopping MySQL:                                            [  OK  ] Starting MySQL:                                            [  OK  ] [root@example ~]#

• For CentOS 7, use the systemctl restart mysqld command:

  [root@example ~]# systemctl restart mysqld Stopping MySQL:                                            [  OK  ] Starting MySQL:                                            [  OK  ] [root@example ~]#

MySQL Configuration

Using a my.cnf file

MySQL uses option or configuration files to read startup options from. On your eApps Virtual Server, this file is my.cnf, located in the /etc directory. The MySQL server as installed from ISPmanager or the template has a default my.cnf file already created.

If you are modifying the default my.cnf file, make sure to read all the appropriate MySQL documentation regarding the settings for that file for your version of MySQL. An incorrectly formatted my.cnf file can cause MySQL to fail on start up, and debugging the my.cnf file is outside of the standard eApps support.

The MySQL distribution also provides some my.cnf files as examples if the default file does not meet your needs. These files are located in the /usr/share/mysql directory. The files are my-huge.cnf, my-large.cnf, my-medium.cnf and my-small.cnf. Each file has a description at the top of the file describing the type of system it was designed for. Please read these descriptions carefully and choose the my.cnf file that is appropriate for your needs.

To use one of these files, copy it to your /etc directory, and rename it my.cnf. Edit the file to suit your specific needs, and restart MySQL.

Sample my.cnf configurations

Below are some common configurations seen in my.cnf files. Be aware that these configurations are used to solve very specific problems or issues, and are not generally required to successfully use MySQL.

Also, understand that using a value in a my.cnf file incorrectly, or with the incorrect syntax, can cause MySQL to fail on start up. Debugging your my.cnf file is outside of the standard eApps support. Use these sample configurations at your own risk!

Add these configurations to the my.cnf file either from the File Manager in ISPmanager, or via the command line. After adding the configurations, you will need to restart MySQL. If for some reason MySQL fails to start, remove the changes you made to the my.cnf file, and restart MySQL again.

Logging slow queries - at times you might want to log which queries are taking longer than a specified time frame, in order to see what you need to optimize in your MySQL databases. This goes under the general [mysqld] heading. The location of the slow_queries.log is up to you, as is the long_query_time, which is in seconds.

Case insensitive tables – if for some reason your tables are mixed case (which is not a best practice, and should be avoided whenever possible), this will tell MySQL to accept that the tables are not all in lower case. This also goes under the general [mysqld] heading.

MyISAM tables optimization - the following is taken from the /usr/share/mysql/my-medium.cnf file, and will help optimize MyISAM tables for small to medium sized databases (10K to 20K records). This also goes under the general [mysqld] heading.

InnoDB tables optimization - the following will help to optimize InnoDB tables for small and medium sized database (10K to 20K records). This also goes under the general [mysqld] heading.

Increasing the idle timeout - by default MySQL drops any idle connections after 8 hours (28800 seconds). This means that your application can have problems connecting to your database if it sits idle for over 8 hours (for example, overnight). This increases the idle timeout to 24 hours (86400 seconds). This also goes under the [mysqld] heading.

MySQL remote access setup

If you need to allow access to MySQL from a remote connection, you will need to create a user that can connect remotely.

To MySQL, the users bob@localhost and bob@eapps-example.com are totally separate users and can have totally separate passwords and privileges, even though they might be the same person.

To create a user with administrative privileges that can connect from a remote workstation, connect to the command line of the VS, and then connect to MySQL as the root user.

To create a user that can only connect remotely to the MySQL database from the example.com domain, use a command similar to this: GRANT ALL PRIVILEGES ON *.* TO 'bob'@'example.com' IDENTIFIED BY 'bobspasswd' WITH GRANT OPTION;

To create a user that can connect remotely to the MySQL database from any domain or workstation, use a command similar to this: GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%' IDENTIFIED BY 'bobsotherpassword';

Backing up your MySQL databases

MySQL databases can be backed up from the command line, phpPgAdmin, the Control Panel, or the Enterprise Backup Service.

By default, there are no backups taken of your Virtual Server. Please read the User Guide: Managing Virtual Server Backups - http://support.eapps.com/portal/vm-backups for more information. If you want to make backups of your Virtual Server that you can store on the storage array, you will need to purchase additional backup space.

You need to take responsibility for backing up your mission critical data! If the data is important to you or your business, making sure you have current backups needs to be one of your top priorities. Do not rely on eApps to have your data!

If you need help creating a custom backup solution that meets your needs, please contact eApps Sales for assistance.

Backing up your databases using a Control Panel

Using the ISPmanager Control Panel

• ISPmanager 4 - go to Management Tools > Databases and click on the database that you want to back up. Then click on Download in the upper right corner. This will download the database to your local computer as a .sql file.

• ISPmanager 5 - go to System > Databases and click on the database that you want to back up. Then click on Download in the upper left corner. This will download the database to your local computer as a .sql file.

Note that this is a manual process. If you want automated backups of your database, you will need to create a custom backup solution. If you need assistance with this, please contact eApps Sales for assistance.

Backing up your databases using phpMyAdmin

The phpMyAdmin program has an Export feature that can be used to backup your databases. You can backup either single databases, or select multiple databases, or all databases.

To begin, login to phpMyAdmin as the root MySQL user. Click on the Export tab.

The default option of Quick - display only the minimal options will export all the databases as a file, with Structure and Data. This will download a file to your local computer called localhost.sql.

If you want to set specific options for the export, including which databases to export, click on Custom - display all possible options. This will allow you to set the file name, character set, compression, and the format of the file. Other options are also available.

Backing up your databases using mysqldump

To backup your databases using mysqldump, you will need to connect to the command line of the VS. The mysqldump commands will need to be run as the system root user, not as the mysql root user.

Using mysqldump to back up a single database

The mysqldump command can be used to back up a single database. The command to use is mysqldump -p database_name > name_of_backup_file.sql. You can enter the password at the -p prompt in the command string if you wish. This is what you would do if you were scripting the backup, for example.

This will place the backup file in the current working directory. Make sure to not name the backup file with the same name as the database itself.

Using mysqldump to back up all databases

The mysqldump command can also be used to make a backup of all databases at once. This is useful if you are going to have to move or backup a large number of databases. The command to use is mysqldump --all-databases -p > databases_file.sql. You can enter the password at the -p prompt in the command string if you wish. This is what you would do if you were scripting the backup, for example.

This backs up all databases on the Virtual Server.

mysqldump is a complex application, with many options and variables. If you have questions on other uses for mysqldump that are not covered in this User Guide, please reference the official documentation – http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Backing up your databases using the Enterprise Backup Service

The Enterprise Backup Service has a free MySQL plugin that will do a full backup of all MySQL databases. See the Backing up and restoring MySQL databases using the Enterprise Backup Service user guide for more information, or contact eApps Sales.