How is MySQL (MariaDB) installed ?

MariaDB 10.0.x MySQL performance fork is used by default as an alternative to official Oracle MySQL 5.1/5.5/5.6 Servers. MariaDB MySQL server is installed via official MariaDB RPMs. MariaDB 10.1.x will be supported once it's out of beta status.

Why MariaDB MySQL ?

Put simply, MariaDB 5.2.x MySQL server currently has the best performance mix for both MyISAM and InnoDB storage engines in MySQL. You can read benchmarks I did on my blog Part 1 and Part 2. While it may not make as much difference for VPS and dedicated servers with low memory and cpu core count specifications, MariaDB 5.2.x uses Percona's XtraDB InnoDB engine so has same or somewhat better InnoDB performance as Percona but MariaDB is the only MySQL version which still focuses on MySQL core improvements as well as improvements to MyISAM engine.

Read an interview with MariaDB and MySQL founder Michael Widenius "Monty" titled, There is no reason at all to use MySQL: MariaDB, MySQL founder Michael Widenius. The interview outlines alot of reasons why popular GNU/Linux distributions such as Fedora, openSuse, Arch Linux, Slackware are switching their default Oracle MySQL packages over to using MariaDB MySQL. Popular web sites such as Wikipedia have also switched over to MariaDB MySQL as well.

News from Red Hat Summit, also will see Red Hat 7 switch from Oracle MySQL to MariaDB MySQL. This is big news as Oracle MySQL base starts to dwindle, MariaDB MySQL user base will be increasing. Hence, decision for Centmin Mod default installs to use MariaDB MySQL is the right one.

You can also add Google to the long list of companies migrating to MariaDB as well according to TheRegister.co.uk.

Managing MySQL Databases and Users

Currently, Centmin Mod doesn't bundle any tools to manage MariaDB MySQL. You have to do it:

  1. Via SSH telnet command line see MySQL documentation at http://dev.mysql.com/doc/refman/5.5/en/tutorial.html, MySQL Access Privilege System and MySQL User Account Management (example shown in Wordpress + WP-FFPC plugin + ngx_pagespeed guide) OR
  2. Install phpmyadmin yourself via Centmin Mod Addon that is currently in beta status (addon properly secures the phpmyadmin installation unlike other online guides) OR
  3. Install tools such as HeidiSQL on your own pc to connect to your server ?

Changing MariaDB 5.5/10.0/10.1 default character set and collation from latin1 to utf8 or utf8mb4 ?

Page 3 of my blog article outlines different ways to change MySQL 5.1 or 5.5 server's default character set and collation from latin 1 to utf8. The same applies to MariaDB 5.5/10.0/10.1 MySQL server which defaults to latin1 character set and latin1_swedish_ci collation.

You can see the defaults for MariaDB 5.5/10.0/10.1 MySQL server by typing the following command in ssh telnet (you'll be prompted to enter mysql root password if set, otherwise if mysql root doesn't have a password set, just hit enter):

  mysqladmin var | grep -E 'character|collation' | tr -s ' '

following output:

| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |

Note, character_set_system is UTF8 by default on latin1 default system and that is normal as MySQL system database and tables themselves at /var/lib/mysql/mysql/* are UTF8 by default. But every other database created defaults to latin1 if database and table creation queries don’t specify charset and collation values.

To change defaults respectively for character set and collation to utf8 and utf8_general_ci, you need to add under [mysqld] group in /etc/my.cnf the following line character-set-server=utf8:

 [mysqld]
 character-set-server=utf8

Then restart MariaDB 5.5/10.0/10.1 MySQL server via command:

  service mysql restart

or via Centmin Mod command shortcut:

  mysqlrestart

Then double check the defaults with the above command again:

  mysqladmin var | grep -E 'character|collation' | tr -s ' '

following output:

| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |

To change defaults respectively for character set and collation to utf8mb4 and utf8mb4_general_ci, you need to add under [mysqld] group in /etc/my.cnf the following line character-set-server=utf8mb4:

 [mysqld]
 character-set-server=utf8mb4

Then restart MariaDB 5.5/10.0/10.1 MySQL server via command:

  service mysql restart

or via Centmin Mod command shortcut:

  mysqlrestart

Then double check the defaults with the above command again:

  mysqladmin var | grep -E 'character|collation' | tr -s ' '

following output:

| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |

Note, only MySQL databases and tables created after these change will by default be created as utf8 or utf8mb4 character set and collations. Any databases before this change, will still be latin1.

MariaDB 10 MySQL ?

Centmin Mod v1.2.3-eva2000.08+ and higher have MariaDB 10.x MySQL default support as outlined here.

For existing Centmin Mod users still on MariaDB 5.5, you'll find the new revised menu option #12 is for MariaDB 5.5.x update to MariaDB 10.x for folks wanting to test older Centmin Mod installs upgrade process to MariaDB 10.x. See step 3 of the .07 and older update guide for more details or on the forums.

Since MariaDB 5.5 and 10.0.x uses YUM repository, future updates can be done via YUM:

yum update MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared

Before upgrading it is highly recommended to backup all your mysql databases using mysqldump

backup

mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql

restore

mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql

Downgrading MariaDB 10.0.x To Specific Version

Centmin Mod v1.2.3-eva2000.08+ and higher have MariaDB 10.x MySQL default support as outlined here. If you need to downgrade MariaDB 10.0.x branch to a specific MariaDB version like downgrading from MariaDB 10.0.21 to 10.0.20, you can follow these steps below:

Backup and edit MariaDB 10.x YUM repo file at /etc/yum.repos.d/mariadb.repo

cp -a /etc/yum.repos.d/mariadb.repo /etc/yum.repos.d/mariadb.repo-origbak

Then edit /etc/yum.repos.d/mariadb.repo. This example is on CentOS 7 so note the centos7 name. If on CentOS 6, you'd need to make sure references change from centos7 to centos6 for below edits.

from

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
exclude=MariaDB-Galera-server

to - adding a name field for the specific MariaDB version i.e. 10.0.20 and edit baseurl to point to actual 10.0.20 version files

[mariadb]
name = MariaDB-10.0.20
baseurl = https://downloads.mariadb.com/files/MariaDB/mariadb-10.0.20/yum/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
exclude=MariaDB-Galera-server

Then clean metadata and do a yum downgrade

yum clean metadata
yum downgrade MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared

You'll see a prompt like below asking for confirmation for downgrading MariaDB to 10.0.20 version

yum downgrade MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared

Dependencies Resolved

=================================================================================================
 Package                  Arch           Version                      Repository       Size
=================================================================================================
Downgrading:
 MariaDB-client           x86_64         10.0.20-1.el7.centos          mariadb          10 M
 MariaDB-common           x86_64         10.0.20-1.el7.centos          mariadb          43 k
 MariaDB-compat           x86_64         10.0.20-1.el7.centos          mariadb         1.4 M
 MariaDB-devel            x86_64         10.0.20-1.el7.centos          mariadb         6.3 M
 MariaDB-server           x86_64         10.0.20-1.el7.centos          mariadb          54 M
 MariaDB-shared           x86_64         10.0.20-1.el7.centos          mariadb         1.2 M

Transaction Summary
=================================================================================================
Downgrade  6 Packages

Total download size: 73 M
Is this ok [y/d/N]: y

After you answer yes and continue, the downgrade will occur like below:

Downloading packages:
(1/6): MariaDB-10.0.20-centos7-x86_64-common.rpm                       |  43 kB  00:00:02     
(2/6): MariaDB-10.0.20-centos7-x86_64-compat.rpm                       | 1.4 MB  00:00:10     
(3/6): MariaDB-10.0.20-centos7-x86_64-client.rpm                       |  10 MB  00:00:28     
(4/6): MariaDB-10.0.20-centos7-x86_64-devel.rpm                        | 6.3 MB  00:00:21     
(5/6): MariaDB-10.0.20-centos7-x86_64-shared.rpm                       | 1.2 MB  00:00:04     
(6/6): MariaDB-10.0.20-centos7-x86_64-server.rpm                       |  54 MB  00:00:25     
--------------------------------------------------------------------------------------------------
Total                                                         1.3 MB/s |  73 MB  00:00:54     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-common-10.0.20-1.el7.centos.x86_64                  1/12 
  Installing : MariaDB-client-10.0.20-1.el7.centos.x86_64                  2/12 
  Installing : MariaDB-server-10.0.20-1.el7.centos.x86_64                  3/12 
  Installing : MariaDB-shared-10.0.20-1.el7.centos.x86_64                  4/12 
  Installing : MariaDB-compat-10.0.20-1.el7.centos.x86_64                  5/12 
  Installing : MariaDB-devel-10.0.20-1.el7.centos.x86_64                   6/12 
  Cleanup    : MariaDB-devel-10.0.21-1.el7.centos.x86_64                   7/12 
  Cleanup    : MariaDB-server-10.0.21-1.el7.centos.x86_64                  8/12 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
  Cleanup    : MariaDB-client-10.0.21-1.el7.centos.x86_64                  9/12 
  Cleanup    : MariaDB-compat-10.0.21-1.el7.centos.x86_64                 10/12 
  Cleanup    : MariaDB-shared-10.0.21-1.el7.centos.x86_64                 11/12 
  Cleanup    : MariaDB-common-10.0.21-1.el7.centos.x86_64                 12/12 
  Verifying  : MariaDB-common-10.0.20-1.el7.centos.x86_64                  1/12 
  Verifying  : MariaDB-server-10.0.20-1.el7.centos.x86_64                  2/12 
  Verifying  : MariaDB-shared-10.0.20-1.el7.centos.x86_64                  3/12 
  Verifying  : MariaDB-client-10.0.20-1.el7.centos.x86_64                  4/12 
  Verifying  : MariaDB-compat-10.0.20-1.el7.centos.x86_64                  5/12 
  Verifying  : MariaDB-devel-10.0.20-1.el7.centos.x86_64                   6/12 
  Verifying  : MariaDB-devel-10.0.21-1.el7.centos.x86_64                   7/12 
  Verifying  : MariaDB-common-10.0.21-1.el7.centos.x86_64                  8/12 
  Verifying  : MariaDB-server-10.0.21-1.el7.centos.x86_64                  9/12 
  Verifying  : MariaDB-compat-10.0.21-1.el7.centos.x86_64                 10/12 
  Verifying  : MariaDB-shared-10.0.21-1.el7.centos.x86_64                 11/12 
  Verifying  : MariaDB-client-10.0.21-1.el7.centos.x86_64                 12/12 

Removed:
  MariaDB-client.x86_64 0:10.0.21-1.el7.centos      MariaDB-common.x86_64 0:10.0.21-1.el7.centos      MariaDB-compat.x86_64 0:10.0.21-1.el7.centos      MariaDB-devel.x86_64 0:10.0.21-1.el7.centos      MariaDB-server.x86_64 0:10.0.21-1.el7.centos     
  MariaDB-shared.x86_64 0:10.0.21-1.el7.centos     

Installed:
  MariaDB-client.x86_64 0:10.0.20-1.el7.centos      MariaDB-common.x86_64 0:10.0.20-1.el7.centos      MariaDB-compat.x86_64 0:10.0.20-1.el7.centos      MariaDB-devel.x86_64 0:10.0.20-1.el7.centos      MariaDB-server.x86_64 0:10.0.20-1.el7.centos     
  MariaDB-shared.x86_64 0:10.0.20-1.el7.centos     

Complete!

Double check the versions installed via yum list installed command:

yum list installed MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared -q

yum list installed MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared -q
Installed Packages
MariaDB-client.x86_64     10.0.20-1.el7.centos          @mariadb
MariaDB-common.x86_64     10.0.20-1.el7.centos          @mariadb
MariaDB-compat.x86_64     10.0.20-1.el7.centos          @mariadb
MariaDB-devel.x86_64      10.0.20-1.el7.centos          @mariadb
MariaDB-server.x86_64     10.0.20-1.el7.centos          @mariadb
MariaDB-shared.x86_64     10.0.20-1.el7.centos          @mariadb

Note: you'd want to revert to the backed up repo file /etc/yum.repos.d/mariadb.repo-origbak to /etc/yum.repos.d/mariadb.repo when you want to upgrade again.

\cp -af /etc/yum.repos.d/mariadb.repo-origbak /etc/yum.repos.d/mariadb.repo

yum clean metadata
yum upgrade MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared

What about MySQL 5.6 ? MariaDB 10.1x ?

Yes, I already have standalone test upgrade scripts written for MySQL 5.6 and MariaDB 10.1.x (MariaDB's equivalent MySQL 5.6 base + WebScaleSQL ported features) which are based on the same standalone test upgrade script for MariaDB 5.5. So far I've only tested MariaDB 5.5, 10.1.x and MySQL 5.6.10 standalone test upgrade scripts. More testing will be needed before inclusion in Centmin Mod v1.2.3+ and higher releases. As at September 10, 2015, Centmin Mod 1.2.3-eva2000.09 beta 01 and higher has added MariaDB 10.1.x branch upgrade support

MariaDB 5.2.x upgrade

Menu option #11 will upgrade existing MariaDB 5.2.x MySQL server users only within MariaDB 5.2.x branch (follow above instructions if you want to move from MariaDB 5.2.x to MariaDB 5.5.x). But unlike Nginx and PHP upgrade routines, it will not prompt for MariaDB version. The version that is upgraded to is determined by what is set in centmin.sh for the following variables:

Set to version you want to upgrade to:

MDB_VERONLY='5.2.14'
MDB_BUILD='122'

Set to existing version you are already using:

MDB_PREVERONLY='5.2.12'
MDB_PREBUILD='115'

So centmin.sh will look like this for MariaDB 5.2.12 Build 115 upgrade to MariaDB 5.2.14 Build 122.

# Define current MariaDB version
MDB_VERONLY='5.2.14'
MDB_BUILD='122'
MDB_VERSION="${MDB_VERONLY}-${MDB_BUILD}"     # Use this version of MariaDB ${MDB_VERONLY}

# Define previous MariaDB version for proper upgrade
MDB_PREVERONLY='5.2.12'
MDB_PREBUILD='115'
MDB_PREVERSION="${MDB_PREVERONLY}-${MDB_PREBUILD}"     # Use this version of MariaDB ${MDB_VERONLY}

Please stick with only the latest MariaDB 5.2.x version tested and listed on official web site at changelog.html. I can not guarantee that higher versions which have not been tested by me to work 100%.

Before upgrading it is highly recommended to backup all your mysql databases using mysqldump

backup

mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql

restore

mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql

Changing MySQL root user password ?

You can follow the official MySQL documentation's Unix/Unix like instructions or generic linux instructions outlined at http://dev.mysql.com/doc/refman/5.6/en/resetting-permissions.html) and once the new MySQL root password is changed, also set and update it in /root/.my.cnf.

Changing MySQL username's password ?

You can you Centmin Mod's addons/mysqladmin_shell.sh script's setpass option to change MySQL username's password as outlined here.