Welcome to Centmin Mod Community
Register Now

MariaDB .07 release default MariaDB MySQL configuration

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by eva2000, May 25, 2014.

  1. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    Centmin Mod v1.2.3-eva2000.07 default MariaDB MySQL setup is listed at http://centminmod.com/mysql.html.

    How is MySQL (MariaDB) installed ?
    MariaDB 5.5 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.

    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 athttp://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 OR
    3. Install tools such as HeidiSQL on your own pc to connect to your server ?
    Changing MariaDB 5.5 default character set and collation from latin1 to utf8 ?
    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 MySQL server which defaults to latin1 character set and latin1_swedish_ci collation.

    You can see the defaults for MariaDB 5.5 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):

    Code:
    mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '
    
    following output:
    Code:
    | 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:
    Code:
    [mysqld]
    character-set-server=utf8
    
    Then restart MariaDB 5.5 MySQL server via command:
    Code:
    service mysql restart
    
    or via Centmin Mod command shortcut:
    Code:
    mysqlrestart
    
    Then double check the defaults with the above command again:
    Code:
    mysqladmin -u root -p var | grep -E 'character|collation' | tr -s ' '
    
    following output:
    Code:
    | 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 |
    
    Note, only MySQL databases and tables created after this change will by default be created as utf8 character set and collations. Any databases before this change, will still be latin1.

    MariaDB 5.5 MySQL ?
    As at May 18th, 2013, latest Centmin Mod v1.2.3 and higher have MariaDB 5.5 MySQL default support as outlined here.

    For existing Centmin Mod users still on MariaDB 5.2, you'll find the new revised menu option #12 is for MariaDB 5.2.x update to MariaDB 5.5.x for folks wanting to test older Centmin Mod installs upgrade process to MariaDB 5.5

    Since MariaDB 5.5 uses YUM repository, future updates can be done via YUM:
    Code:
    yum update --disablerepo=CentALT MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared MariaDB-test
    
    Before upgrading it is highly recommended to backup all your mysql databases usingmysqldump

    backup
    Code:
    mysqldump -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql
    
    restore
    Code:
    mysql -u mysqlusername -p mysqldatabasename < /path/to/mysqldatabasename_backup_date.sql
    
    What about MySQL 5.6 ? MariaDB 10.x ?
    Yes, I already have standalone test upgrade scripts written for MySQL 5.6 and MariaDB 10.x (MariaDB's equivalent MySQL 5.6 base) which are based on the same standalone test upgrade script for MariaDB 5.5. So far I've only tested MariaDB 5.5 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.
     
  2. BamaStangGuy

    BamaStangGuy Active Member

    462
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    10:42 PM
    Is InnoDB still skipped by default and MyISAM still the default engine?
     
  3. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    Hey Brent :)

    Yes still InnoDB disabled/skipped and MyISAM default right now. Doesn't make sense to enable it if according to Centmin Mod survey results ~45% are still using 32bit OS with <512MB memory.
     
  4. BamaStangGuy

    BamaStangGuy Active Member

    462
    136
    43
    May 25, 2014
    Ratings:
    +179
    Local Time:
    10:42 PM
    How can I change it before install so that it is already setup and ready to go with InnoDB after a fresh install?
     
  5. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    • Like Like x 1
  6. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    8:42 PM
    I just stumbled across this myself--surprised that Centminmod still sets MyISAM as the default engine after upgrading to MariaDB 10, when MySQL and Maria both now default to InnoDB out of the box. @eva2000 maybe add a note on the MySQL centminmod page after the "changing default to UTF-8" that says "changing default to InnoDB"... Your web pages are the first thing I check as a resource before digging in the forums, and didn't see any mention there that Centminmod uses a non-default setting.

    @Brent any reason why you want to change this in Centminmod file rather than edit your my.cnf and restart MySQL?

    Trying to decide which way to do it myself, and leaning toward just having Ansible edit the my.cnf after it gets installed.
     
    • Like Like x 1
  7. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    Yeah will add to Getting started and MySQL pages a note or just make it default for .08 beta 02 and higher = InnoDB ?
     
  8. jeffwidman

    jeffwidman Active Member

    152
    27
    28
    Dec 3, 2014
    Ratings:
    +51
    Local Time:
    8:42 PM
    I'd vote to make InnoDB the default and add a note saying "If you're running a really tiny VPS, like <512 MB ram, you may wish to set the default table engine to MyISAM".

    I just think that'd be both less surprising and most blog posts on the web going forward are going to be talking/advocating InnoDB, so a n00b will be expecting that. Only someone who knows what they're doing will be purposefully switching to MyISAM to save RAM, so probably better to not have it as default. Plus VPS costs are plummeting so fast that if someone is running a site they care about and installing Centminmod on a new server, there's a good chance it'll be on a 1GB or greater instance. Ramnode charges $7 a month for 1GB, which is absurdly cheap.
     
  9. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    Added a note to Getting Started item 11 mentioning MyISAM default engine tables and what to do to switch to InnoDB default storage engine tables.
     
    • Like Like x 1
  10. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    Yeah maybe do auto detect if <512MB memory and set accordingly ? But for now just added Getting Started note to item 11.
     
    • Like Like x 1
  11. eva2000

    eva2000 Administrator Staff Member

    27,751
    6,334
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +9,335
    Local Time:
    1:42 PM
    Nginx 1.13.x
    MariaDB 5.5
    Remember though, InnoDB performance is only as good as memory allocated to InnoDB specific buffers and InnoDB related tuning. Centmin Mod /etc/my.cnf does have based tuned settings but InnoDB needs to be tuned for your specific web app's usage requirements anyway. Just switching from MyISAM to InnoDB engine default will do nothing for performance if you do not tune InnoDB settings in /etc/my.cnf for such - especially if InnoDB is memory starved.

    Anyone who knows this, will have edited /etc/my.cnf accordingly including setting default storage engine to InnoDB instead of default MyISAM set by Centmin Mod initial install :)