MySQL and phpMyAdmin
Posted by on 01 December 2011 05:33 PM
Applicable Plans - All Cloud Hosting Plans
MySQL and phpMyAdmin
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
Backing up your MySQL databases
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.
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.
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
NOTE - if you are installing MySQL on a CentOS 7 template, see the section below: Installing MySQL on CentOS 7
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.
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.
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.
Once you have created the User, you can create the actual database and the user that will access the database.
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.
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.
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 add a new user, click on Add a new user.
Database for user
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 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)
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.
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.
Once you have chosen your file and made the other selections, click Go to import the file into the database.
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
The command to use will look like this: mysql -u db_user -p db_name < file.sql
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.
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
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
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
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
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
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
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 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.
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
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.