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.
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 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
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.
If you are using the ISPmanager 5 Control Panel, you can install MariaDB (and PHP and phpMyAdmin) from that Control Panel.
Information on how to tell which version of ISPmanager you are using can be found here - ISPmanager versions
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-server command
|[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 mariadb and
systemctl enable mariadb commands. You will also need to run the
mysql_secure_installation command, which will allow you to set a secure password and remove the test database.
PHP - install PHP using the
yum install -y php command
|[root@eapps-example ~]# yum install -y php
phpMyAdmin - install phpMyAdmin after installing PHP using the
yum install -y phpMyAdmin command. Note the case of the letters in phpMyAdmin.
|[root@eapps-example ~]# yum install -y 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.
Once you have installed the required PHP extensions, log out of phpMyAdmin and log back in.
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.
MariaDB databases can be created from the Control Panel, phpMyAdmin, and the command line.
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.
|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.
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
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:
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.
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
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.
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
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 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)
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.
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
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.
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
MariaDB can be stopped/started/restarted from the Control Panel or from the command line of the Virtual Server.
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 mariadb command:
For CentOS 7, use the
systemctl stop mariadb command:
For CentOS 7, use the
systemctl start mariadb command:
For CentOS 7, use the
systemctl restart mariadb command:
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.
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.
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
|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
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
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
|interactive_timeout = 86400
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
firstname.lastname@example.org 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;
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';
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.
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.
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.
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.
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.
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
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.