How is MySQL (MariaDB) installed ?
MariaDB 10.x MySQL performance fork is used by default as an alternative to official Oracle MySQL Servers. MariaDB MySQL server is installed via official MariaDB RPMs. You can change the default MariaDB 10.4 or MariaDB 10.6 version used with instructions outlined here. Centmin Mod 140.00beta01 is working on MariaDB 10.11 LTS and 11.4 LTS support.
Why MariaDB MySQL ?
Put simply, back when Centmin Mod was first developed in 2011 - MariaDB 5.2.x MySQL server had 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.
MariaDB usage has continued since then. You can read about the differences and similarities between MariaDB Server vs Oracle MySQL vs Percona MySQL on the forums here.
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
To manage MariaDB MySQL. You can do it via:
- Centmin Mod 131.00stable or 140.00beta01's new
centmin.sh menu option 6(see demo shell menu below) which is the shell menu version of existingaddons/mysqladmin_shell.shscript outlined here. - Via SSH command line native MariaDB MySQL client tools.
- Install phpmyadmin yourself via Centmin Mod Addon that is currently in beta status (addon properly secures the phpmyadmin installation unlike other online guides) OR
- Install tools such as HeidiSQL on your own pc to connect to your server.
Centmin Mod 131.00stable or 140.00beta01's new centmin.sh menu option 6:
--------------------------------------------------------
Centmin Mod Menu 140.00beta01 centminmod.com
--------------------------------------------------------
1). Centmin Install
2). Add Nginx vhost domain
3). NSD setup domain name DNS
4). Nginx Upgrade / Downgrade
5). PHP Upgrade / Downgrade
6). MySQL User Database Management
7). Persistent Config File Management
8). Option Being Revised (TBA)
9). Option Being Revised (TBA)
10). Memcached Server Re-install
11). MariaDB MySQL Upgrade & Management
12). Zend OpCache Install/Re-install
13). Install/Reinstall Redis PHP Extension
14). SELinux disable
15). Install/Reinstall ImagicK PHP Extension
16). Change SSHD Port Number
17). Multi-thread compression: zstd,pigz,pbzip2,lbzip2
18). Suhosin PHP Extension install
19). Install FFMPEG and FFMPEG PHP Extension
20). NSD Install/Re-Install
21). Data Transfer
22). Add Wordpress Nginx vhost + Cache Plugin
23). Update Centmin Mod Code Base
24). Exit
--------------------------------------------------------
Enter option [ 1 - 24 ] 6
--------------------------------------------------------
---------------------------------------------------------------- MySQL User Database Management Sub Menu ---------------------------------------------------------------- Command Line Version community.centminmod.com/threads/543/ ---------------------------------------------------------------- 1). Create MySQL User and Database 2). Add Database to Existing MySQL User 3). Add Existing Database to Existing MySQL User 4). Add Existing Database to New MySQL User 5). Change MySQL User Password 6). Delete MySQL User 7). Show Grants for MySQL User 8). Create Global MySQL User (access to all databases) 9). Create Multiple MySQL Databases and Users from File 10). Delete MySQL Database 11). List All MySQL Databases and Users 12). Return to Main Menu ---------------------------------------------------------------- Enter option [ 1 - 12 ]:
Changing MariaDB 5.5/10.x 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 -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -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 use Centmin Mod centmin.sh menu option 11 - submenu option 0:
--------------------------------------------------------
Centmin Mod Menu 140.00beta01 centminmod.com
--------------------------------------------------------
1). Centmin Install
2). Add Nginx vhost domain
3). NSD setup domain name DNS
4). Nginx Upgrade / Downgrade
5). PHP Upgrade / Downgrade
6). MySQL User Database Management
7). Persistent Config File Management
8). Option Being Revised (TBA)
9). Option Being Revised (TBA)
10). Memcached Server Re-install
11). MariaDB MySQL Upgrade & Management
12). Zend OpCache Install/Re-install
13). Install/Reinstall Redis PHP Extension
14). SELinux disable
15). Install/Reinstall ImagicK PHP Extension
16). Change SSHD Port Number
17). Multi-thread compression: zstd,pigz,pbzip2,lbzip2
18). Suhosin PHP Extension install
19). Install FFMPEG and FFMPEG PHP Extension
20). NSD Install/Re-Install
21). Data Transfer
22). Add Wordpress Nginx vhost + Cache Plugin
23). Update Centmin Mod Code Base
24). Exit
--------------------------------------------------------
Enter option [ 1 - 24 ] 11
--------------------------------------------------------
--------------------------------------------------------
MariaDB MySQL Upgrade & Management Sub-Menu
--------------------------------------------------------
0). Regenerate New MySQL Root User Password (local server)
1). MariaDB 5.2.x Branch Upgrade Only
2). MariaDB 5.2.x to MariaDB 5.5 YUM upgrade
3). MariaDB 5.5 to MariaDB 10.x YUM upgrade
4). MariaDB 5.5 to MariaDB 10.1.x YUM upgrade
5). MariaDB 10.x to MariaDB 10.1.x YUM upgrade
6). MariaDB 10.1.x to MariaDB 10.2.x YUM upgrade
7). MariaDB 10.2.x to MariaDB 10.3.x YUM upgrade
8). MariaDB 10.3.x to MariaDB 10.4.x YUM upgrade
9). MariaDB 10.4.x to MariaDB 10.5.x YUM upgrade
10). MariaDB 10.5.x to MariaDB 10.6.x LTS YUM upgrade
11). MariaDB 10.6.x to MariaDB 10.11.x YUM upgrade
12). MariaDB 10.6.x to MariaDB 11.4.x LTS YUM upgrade
13). MariaDB 10.11.x to MariaDB 11.4.x LTS YUM upgrade
14). Back to Main menu
--------------------------------------------------------
Enter option [ 0 - 14 ]
