MariaDB and phpMyAdmin
- 17/06/2015 2:55 PM
Applicable Plans - All CentOS 7 Cloud Hosting Plans
MariaDB and phpMyAdmin
Overview
MariaDB is a drop in replacement for MySQL, created by the original author of MySQL. MariaDB adds additional features such as enhanced replication, Aria and XtraDB storage engines, and a non-blocking client API library. More information about MariaDB can be found here - About MariaDB.
MariaDB is only available on CentOS 7 Linux templates, and is the default database installed for a CentOS 7 template. The only exceptions are the CentOS 7 templates that specify MySQL in the template name. If your OS template uses MySQL, please see the MySQL User Guide.
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.
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 MariaDB and related applications
Installing MariaDB and related applications using the Control Panel
Installing MariaDB and related applications using the command line
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 MariaDB database
Importing content using phpMyAdmin
Importing content using the command line
Stopping and starting MariaDB
Stopping and starting MariaDB using a Control Panel
Stopping and starting MariaDB using the command line
MariaDB Configuration
Using a my.cnf file
Sample my.cnf configurations
Backing up your MariaDB 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 MariaDB and related applications
To use MariaDB you will need to install the MariaDB 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 MariaDB with an application like WildFly, JBoss, Tomcat, or GlassFish, you will also want to install MySQL Connector/J.
You can install MariaDB and any related applications from your Control Panel and also from the command line of the Virtual Server.
Installing MariaDB and related applications using the Control Panel
Installing MariaDB and related applications using the ISPmanager 5 Control Panel
If you are using the ISPmanager 5 Control Panel, you can install MariaDB (and PHP and phpMyAdmin) from that Control Panel.
- For ISPmanager 5, go to Settings > Features. In the ISPmanager 5 Control Panel, MariaDB is labeled as MySQL for backwards compatibility. Install MySQL (MariaDB), 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 MariaDB and related applications using the command line
MariaDB 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.
-
MariaDB - install MariaDB using the
yum install -y mariadb-servercommand[root@eapps-example ~]# yum install -y mariadb-server NOTE - if you use yum to install MariaDB, you will need to start the service and enable it to start at system boot, using the
systemctl start mariadbandsystemctl enable mariadbcommands. You will also need to run themysql_secure_installationcommand, which will allow you to set a secure password and remove the test database.[root@eapps-example ~]# systemctl start mariadb
[root@eapps-example ~]# systemctl enable mariadb
[root@eapps-example ~]# mysql_secure_installation -
PHP - install PHP using the
yum install -y phpcommand[root@eapps-example ~]# yum install -y php -
phpMyAdmin - install phpMyAdmin after installing PHP using the
yum install -y phpMyAdmincommand. Note the case of the letters in phpMyAdmin.[root@eapps-example ~]# yum install -y phpMyAdmin
Configuring phpMyAdmin
Installing PHP extensions for 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 extensions can be installed from a Control Panel (if installed) or from the command line.
Installing PHP extensions from the ISPmanager 5 Control Panel
- 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.
Installing PHP extensions from the command line
If you installed phpMyAdmin using yum the required PHP extensions are usually installed as dependencies. If you need to install additional extensions, see Installing PHP Extensions in the PHP User Guide.
Creating databases and database users
MariaDB 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 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 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 MariaDB 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 http://eapps-example.com/myadmin/ (CentOS 6) or http://eapps-example.com/phpmyadmin (CentOS 7), substituting your domain name or server IP address for eapps-example.com.
-
Username - to create a database, log in as the MariaDB root user (this is not the same as the system super user)
-
Password - you will need to use the MariaDB root password to log in to phpMyAdmin:
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 database text box where you can enter the name of the new database.
Once you have entered the database name (in this example new_database), 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 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 takes you to the Privileges tab.
To add a new user, click on Add user.
Login Information
-
User name (Use text field): - enter the user 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 MariaDB 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 MariaDB using the mysql command:
[root@eapps-example ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 53
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Create the new database
Create the new database using the create database db_name command.
MariaDB [(none)]> create database new_database;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]>
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 new_database.* TO new_db_user@localhost IDENTIFIED BY "passwd"; (You will have to substitute your database name and your own database user and password)
MariaDB [(none)]> GRANT ALL ON new_database.* TO new_db_user@localhost IDENTIFIED BY "passwd";
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]>
Test the new database user
Exit MariaDB with the quit command, and then try to log in with the new user and password you just created using the mysql -uusername -ppassword command.
MariaDB [(none)]> quit
Bye
[root@eapps-example ~]# mysql -unew_db_user -ppasswd
Welcome to the MariaDB monitor. Commands end with ; or .
Your MariaDB connection id is 5
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Importing content to a MariaDB database
There are several methods available to upload or import data into a MariaDB database. Data can be imported from the command line or uploaded through phpMyAdmin.
|
All of these examples assume that you have a valid MariaDB dump file in .sql format to upload or import |
Importing content using phpMyAdmin
To import a MariaDB dump file in .sql format from phpMyAdmin, login to phpMyAdmin with the user name 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 Maximum file size in the PHP configuration. To do this from ISPmanager, go to Web-server settings > PHP settings, and click on Basic at the top of the screen.
Change the value for Maximum file 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 MariaDB 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 MariaDB dump file in sql format called new_db.sql is being imported to the new_database, which is owned by the new_db_user database user.
The command to use will look like this: mysql -u db_user -p db_name < file.sql
| [webadmin@eapps-example ~]$ mysql -u new_db_user -p new_database < new_db.sql Enter password: passwd [webadmin@example ~]$ |
Stopping and starting MariaDB
MariaDB can be stopped/started/restarted from the Control Panel or from the command line of the Virtual Server.
Stopping and starting MariaDB using a Control Panel
Using the ISPmanager Control Panel
- For ISPmanager 5, go to System > Services, and highlight the MariaDB 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 MariaDB using the command line
You can stop, start, and restart MariaDB 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.
-
For CentOS 7, use the
systemctl status mariadbcommand:[root@eapps-example ~]# systemctl status mariadb
Stop MariaDB
-
For CentOS 7, use the
systemctl stop mariadbcommand:[root@eapps-example ~]# systemctl stop mariadb
Start MariaDB
-
For CentOS 7, use the
systemctl start mariadbcommand:[root@eapps-example ~]# systemctl start mariadb
Restart MariaDB
-
For CentOS 7, use the
systemctl restart mariadbcommand:[root@eapps-example ~]# systemctl restart mariadb
MariaDB Configuration
Using a my.cnf file
MariaDB 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 MariaDB 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 MariaDB documentation regarding the settings for that file for your version of MariaDB. An incorrectly formatted my.cnf file can cause MariaDB to fail on start up, and debugging the my.cnf file is outside of the standard eApps support.
The MariaDB 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 MariaDB.
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 MariaDB.
Also, understand that using a value in a my.cnf file incorrectly, or with the incorrect syntax, can cause MariaDB 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 MariaDB. If for some reason MariaDB fails to start, remove the changes you made to the my.cnf file, and restart MariaDB 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 MariaDB 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.
| log-slow-queries=/tmp/slow_queries.log long_query_time=2 |
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 MariaDB to accept that the tables are not all in lower case. This also goes under the general [mysqld] heading.
| lower_case_table_names = 1 |
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.
| skip-locking key_buffer = 16M max_allowed_packet = 8M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M |
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.
| #innodb innodb_buffer_pool_size = 96M innodb_flush_log_at_trx_commit = 1 innodb_additional_mem_pool_size = 2M innodb_log_buffer_size = 2M |
Increasing the idle timeout - by default MariaDB 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.
| interactive_timeout = 86400 |
MariaDB remote access setup
If you need to allow access to MariaDB from a remote connection, you will need to create a user that can connect remotely.
|
To MariaDB, 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 MariaDB as the root user.
To create a user that can only connect remotely to the MariaDB 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;
[root@eapps-example ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or .
Your MariaDB connection id is 108
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '' for help. Type '' to clear the current input statement.
MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'bob'@'example.com' IDENTIFIED BY 'bobpasswd' WITH GRANT OPTION;
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]>
To create a user that can connect remotely to the MariaDB database from any domain or workstation, use a command similar to this: GRANT ALL PRIVILEGES ON *.* TO 'bob'@'%' IDENTIFIED BY 'bobsotherpassword';
[root@eapps-example ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or .
Your MariaDB connection id is 108
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '' for help. Type '' to clear the current input statement.
MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO 'bob'@'%' IDENTIFIED BY 'bobsotherpasswd';
Query OK, 0 rows affected (0.07 sec)
MariaDB [(none)]>
Backing up your MariaDB databases
MariaDB 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 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 MariaDB 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 that the name the backup file IS NOT the same name as the database itself.
[root@eapps-example ~]# mysqldump -p database_name > name_of_backup_file.sql
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.
[root@eapps-example ~]# mysqldump --all-databases -p > databases_file.sql
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 plugin that will do a full backup of all MariaDB databases. See the Backing up and restoring MySQL databases using the Enterprise Backup Service user guide for more information, or contact eApps Sales.