MariaDB Mariadb 10.1 to 10.2 upgrade

xenbiarritz · Jan 21, 2019

Code:
Please fill in any relevant information that applies to you:
CentOS Version: CentOS 7 64bit ?
Centmin Mod Version Installed: 123.09beta01
Nginx Version Installed:  1.15.3
PHP Version Installed: 7.2.9
MariaDB MySQL Version Installed: 10.1.xx
When was last time updated Centmin Mod code base ? : i.e. run centmin.sh menu option 23 submenu option 2
Persistent Config: yes
Hi, i try to upgrade mariadb 10.1.xx to 10.2, menu 11 -->6
it return
Place holder for now until MariaDB 10.2.x is ready to be added
how can i upgrade? my centminmod is updated
thank you

pamamolf · Jan 21, 2019

First backup your databases.

Then edit:
/etc/centminmod/custom_config.inc

add this:
ENABLE_MARIADBTENTWOUPGRADE='y'

Run again Centminmod and it should work.

eva2000 · Jan 21, 2019

Yup @pamamolf instructions are correct, just be sure to backup your databases via mysqldump before hand.

If you have a spare test VPS/server, you can manually enable the 10.1 to 10.2 upgrade routine in 123.09beta01 but any issues short term or long term wise you find would need to be reported to me but only resolution for that test VPS is to reinstall CentOS as I currently wouldn't have enough spare time to look into any issues with upgrade process (same with MariaDB 10.2 to 10.3 routine). So be prepared to backup data files and databases and wipe the server if MariaDB 10.2 test upgrade doesn't work.

On test VPS, you can enable Centmin Mod 123.09beta01's centmin.sh menu option 11 submenu option 6 to upgrade MariaDB 10.1.x branch to MariaDB 10.2.x branch by first setting in persistent config file at /etc/centminmod/custom_config.inc the variable BEFORE running centmin.sh

Code (Text):

ENABLE_MARIADBTENTWOUPGRADE='y'

There's also a variable to enable MariaDB 10.2.x to 10.3.x upgrades which is only supported on CentOS 7.x for now due to reported issues with CentOS 6. Again do on test VPS/server only and not live production server as MariaDB 10.2 to 10.3 has had even less long term testing than MariaDB 10.1 to 10.2 process. If you run into problems, my only solution for you is to setup a new VPS/server and reinstall Centmin Mod and migrate your site data and MySQL data if intact (if not that restore from MySQL backups).

Code (Text):

ENABLE_MARIADBTENTHREEUPGRADE='y'

Code (Text):

--------------------------------------------------------
     Centmin Mod Menu 123.09beta01 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).  XCache Re-install
7).  APC Cache Re-install
8).  XCache Install
9).  APC Cache Install
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: pigz,pbzip2,lbzip2...
18). Suhosin PHP Extension install
19). Install FFMPEG and FFMPEG PHP Extension
20). NSD Install/Re-Install
21). Update - Nginx + PHP-FPM + Siege
22). Add Wordpress Nginx vhost + Cache Plugin
23). Update Centmin Mod Code Base
24). Exit
--------------------------------------------------------
Enter option [ 1 - 24 ] 11
--------------------------------------------------------

Code (Text):

--------------------------------------------------------
     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). Back to Main menu
--------------------------------------------------------
Enter option [ 0 - 8 ] 6
--------------------------------------------------------

Code (Text):

-------------------------------------------------------------------------------
 This script's MariaDB 10.2.x upgrade process is still in beta testing stages.
 So please test on a test server and not a live production server. But if you use
 on live server or if MySQL databases already exist, please back them up before
 running this upgrade script via mysqldump - instructions at:
 http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html.

 ###################################################################
 ###          IMPORTANT MYSQLDUMP BACKUP BEFORE UPGRADE          ###
 ###        OR RISK DATA LOSS DUE TO MARIADB 10.2 Changes        ###
 ###################################################################

 For each DATABASENAME backup via mysqldump i.e.

  mysqldump --opt --routines DATABASENAME > /path/to/DATABASENAME_backup.sql
------------------------------------------------------------------------

Have you backed up your databases ? [y/n] y

MariaDB 10.2.x https://mariadb.com/kb/en/mariadb/what-is-mariadb-102/

!!  Note  !!
upgrade process can take more >5 minutes.. you may want to shutdown
nginx server to not show mysql errors to visitors on your site or
put site(s) into maintenance mode before proceeding with mysql upgrade

Do you want to continue with upgrade to MariaDB 10.2.x ? [y/n] y

Code (Text):

*************************************************
* Adjust old MariaDB 10.x /etc/my.cnf
  for MariaDB 10.2.x my.cnf compatibility
*************************************************
    __  __               _         ____   ____    _   ___     ____
   |  \/  |  __ _  _ __ (_)  __ _ |  _ \ | __ )  / | / _ \   |___ \    __  __
   | |\/| | / _` || '__|| | / _` || | | ||  _ \  | || | | |    __) |   \ \/ /
   | |  | || (_| || |   | || (_| || |_| || |_) | | || |_| |_  / __/  _  >  <
   |_|  |_| \__,_||_|   |_| \__,_||____/ |____/  |_| \___/(_)|_____|(_)/_/\_\
                                                             
            __     _            __                                  __
           / /___ | |_  ___    / /_ __ ___   _   _     ___  _ __   / _|
          / // _ \| __|/ __|  / /| '_ ` _ \ | | | |   / __|| '_ \ | |_
         / /|  __/| |_| (__  / / | | | | | || |_| | _| (__ | | | ||  _|
        /_/  \___| \__|\___|/_/  |_| |_| |_| \__, |(_)\___||_| |_||_|
                                             |___/     
         _        _   _              _                            _
        / \    __| | (_) _   _  ___ | |_  _ __ ___    ___  _ __  | |_  ___
       / _ \  / _` | | || | | |/ __|| __|| '_ ` _ \  / _ \| '_ \ | __|/ __|
      / ___ \| (_| | | || |_| |\__ \| |_ | | | | | ||  __/| | | || |_ \__ \
     /_/   \_\\__,_|_/ | \__,_||___/ \__||_| |_| |_| \___||_| |_| \__||___/
                   |__/

Code (Text):

mysql -t -e "SHOW PLUGINS;"
+-------------------------------+----------+--------------------+------------------------+---------+
| Name                          | Status   | Type               | Library                | License |
+-------------------------------+----------+--------------------+------------------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL                   | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MRG_MyISAM                    | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MEMORY                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| CSV                           | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| MyISAM                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| CLIENT_STATISTICS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INDEX_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| TABLE_STATISTICS              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| USER_STATISTICS               | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| PERFORMANCE_SCHEMA            | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| InnoDB                        | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| INNODB_TRX                    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_LOCKS                  | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_LOCK_WAITS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP                    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_RESET              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMPMEM                 | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMPMEM_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_PER_INDEX          | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_CMP_PER_INDEX_RESET    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_PAGE            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_PAGE_LRU        | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_BUFFER_POOL_STATS      | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_METRICS                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_DEFAULT_STOPWORD    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_DELETED             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_BEING_DELETED       | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_CONFIG              | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_INDEX_CACHE         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_FT_INDEX_TABLE         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLES             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLESTATS         | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_INDEXES            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_COLUMNS            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FIELDS             | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FOREIGN            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_FOREIGN_COLS       | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_TABLESPACES        | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_DATAFILES          | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_VIRTUAL            | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_MUTEXES                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_SYS_SEMAPHORE_WAITS    | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| INNODB_TABLESPACES_ENCRYPTION | ACTIVE   | INFORMATION SCHEMA | NULL                   | BSD     |
| INNODB_TABLESPACES_SCRUBBING  | ACTIVE   | INFORMATION SCHEMA | NULL                   | BSD     |
| Aria                          | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| SEQUENCE                      | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| user_variables                | ACTIVE   | INFORMATION SCHEMA | NULL                   | GPL     |
| FEEDBACK                      | DISABLED | INFORMATION SCHEMA | NULL                   | GPL     |
| partition                     | ACTIVE   | STORAGE ENGINE     | NULL                   | GPL     |
| METADATA_LOCK_INFO            | ACTIVE   | INFORMATION SCHEMA | metadata_lock_info.so  | GPL     |
| QUERY_CACHE_INFO              | ACTIVE   | INFORMATION SCHEMA | query_cache_info.so    | BSD     |
| QUERY_RESPONSE_TIME           | ACTIVE   | INFORMATION SCHEMA | query_response_time.so | GPL     |
| QUERY_RESPONSE_TIME_AUDIT     | ACTIVE   | AUDIT              | query_response_time.so | GPL     |
+-------------------------------+----------+--------------------+------------------------+---------+

Code (Text):

mysql -t -e "SHOW ENGINES;"
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                               | NO           | NO   | NO         |
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                            | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+

Code (Text):

mysqladmin  Ver 9.1 Distrib 10.2.13-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.2.13-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 0 sec

Threads: 4  Questions: 1  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.000

------------------------------------------------
MariaDB 10.2.x Upgrade completed
------------------------------------------------

Then future MariaDB 10.2.x branch updates are done via yum

Code (Text):

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

Upgrade MariaDB 10.1 To 10.2 & 10.2 To 10.3 Differences

MariaDB 10.1 to 10.2 has changes you need to be aware of which may affect your script/mysql using app specifically and Centmin Mod may not have tested for it so read Upgrading from MariaDB 10.1 to MariaDB 10.2 (centmin.sh menu option 11 takes care of step 1 to 8 with exception of step 4, you need to do MySQL backups yourself) and inspect /etc/my.cnf before and after MariaDB 10.2 upgrades. Centmin Mod upgrade routines try to as best adjust /etc/my.cnf for MariaDB 10.2 compatibility with previous MariaDB 10.1 but if your web app/scripts have issues, you'll need to figure those out yourself.

Same goes for MariaDB 10.2 to 10.3 upgrades Upgrading from MariaDB 10.2 to MariaDB 10.3 (centmin.sh menu option 11 takes care of step 1 to 8 with exception of step 4, you need to do MySQL backups yourself)

One of the big changes is from MariaDB 10.2.2 and newer, innodb_strict_mode is enabled by default while previously it was disabled by default and default options for sql_mode have changed.

When innodb_strict_mode is enabled, InnoDB returns errors rather than warnings for certain conditions.

Strict mode helps guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements. When innodb_strict_mode is enabled, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous to sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values.

The innodb_strict_mode setting affects the handling of syntax errors for CREATE TABLE, ALTER TABLE and CREATE INDEX statements.innodb_strict_mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

Oracle recommends enabling innodb_strict_mode when using ROW_FORMAT and KEY_BLOCK_SIZE clauses in CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. When innodb_strict_mode is disabled, InnoDB ignores conflicting clauses and creates the table or index with only a warning in the message log. The resulting table might have different characteristics than intended, such as lack of compression support when attempting to create a compressed table. When innodb_strict_mode is enabled, such problems generate an immediate error and the table or index is not created.

You can enable or disable innodb_strict_mode on the command line when starting mysqld, or in a MySQL configuration file. You can also enable or disable innodb_strict_mode at runtime with the statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is either ON or OFF. Changing the GLOBAL setting requires privileges sufficient to set global system variables (see Section 5.1.8.1, “System Variable Privileges”) and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting for innodb_strict_mode, and the setting affects only that client.
Click to expand...

Install MariaDB 10.3 By Default

Centmin Mod 123.09beta01 and newer also has experimental and optional choice of installing MariaDB 10.3 as default version. See details at Beta Branch - allow MariaDB 10.3 MySQL to be default optionally. Again test on test VPS/server and not live production site servers.

xenbiarritz · Jan 23, 2019

thank you

Log in / Register

Forums

Learn about Centmin Mod LEMP Stack today

MariaDB Mariadb 10.1 to 10.2 upgrade

xenbiarritz Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

Upgrade MariaDB 10.1 To 10.2 & 10.2 To 10.3 Differences

Install MariaDB 10.3 By Default

xenbiarritz Member

.

Log in / Register

Useful Searches

Learn about Centmin Mod LEMP Stack today

MariaDB Mariadb 10.1 to 10.2 upgrade

xenbiarritz Member

pamamolf Well-Known Member

eva2000 Administrator Staff Member

Upgrade MariaDB 10.1 To 10.2 & 10.2 To 10.3 Differences

Install MariaDB 10.3 By Default

xenbiarritz Member

.