Linux Notes: MySQL - MariaDB

1. The information presented here is intended for educational use by qualified computer technologists.
2. The information presented here is provided free of charge, as-is, with no warranty of any kind.

Introduction (mostly copied from my OpenVMS page)

MySQL

History (10k view)

• MySQL is a fully SQL-compliant "open source" relational database created by MySQL Ab of Sweden
  • both the database, as well as the company, were named after "My", the older daughter of co-founder Michael "Monty" Widenius
  • open-source means it could be acquired, installed and used, free-of-charge under the GPL license
  • this company only made money by selling support
• The first version appeared in 1995 for the PC
• Sun Microsystems paid one billion dollars to acquire MySQL Ab in 2008 (not sure of the business model here since SUN intended to continue giving it away; perhaps they were thinking about paid support contracts)
• Oracle acquired SUN Microsystems, along with MySQL, in 2010 (the acquisition began in 2009)
• MySQL has diverted much attention (and perhaps a small amount of business) away from Oracle's flagship products so it should come as no surprise that Oracle has been accused of delaying "bug fixes" as well as "product development". Consider these observations:
  • InnoDB is a popular "ACID compliant" storage engine used by the MySQL community since 2001. It was created by Innobase Oy of Helsinki Finland which was purchased by Oracle in 2005
  • BerkeleyDB (BDB) was a popular "ACID compliant" storage engine when created in 1986 for use with BSD UNIX. It was the default storage engine before MySQL-5.1 and was maintained by Sleepycat Software which was purchased by Oracle in 2006
  • After finalizing the purchase of Sun Microsystems in 2010, Oracle inserted a copyright notice along with a rights notice into the client banner. The following example hails from MySQL-5.1
    

    $ mysql -uroot mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 69
    Server version: 5.1.46-log Source distribution
    
    Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
    This software comes with ABSOLUTELY NO WARRANTY. This is free software,
    and you are welcome to modify and redistribute it under the GPL v2 license
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql>

    
    Nothing wrong with a copyright notice but it does make lawyers and developers nervous.

A few technical details

• high level languages can directly access MySQL via client APIs
  • most MySQL distros publish two symbol reference files:
    • mixed-case symbols for fourth generation languages (C/C++, Java, PHP, etc.)
    • upper-case symbols for third generation languages (BASIC, FORTRAN) but I have never tried this. Our VMS-BASIC programs call a C function which call the client API
• indirect access is also possible via ODBC, JDBC, etc.
• numerous storage engines exist. Look at this example statement:
  

  CREATE DATABASE hack1;
  USE hack1;
  CREATE TABLE hack1 ( x1 INT(11) auto_increment, y2 char(60) default NULL, PRIMARY KEY (x1) ) ENGINE=kind ;

  
  where kind can be any one of the following (partial list):
  • ISAM (deprecated in MySQL 4.1, will be removed in MySQL 5.0)
  • MyISAM
  • MERGE
  • MEMORY
  • InnoDB
  • BDB (BerkeleyDB)
• More storage engines have been added since MySQL 5.0
• In this list, only InnoDB and BerkeleyDB are transaction-safe, ACID compliant, and replace "table locks" with "row locks"
• Using either InnoDB or BerkeleyDB will cost you a very slight drop in performance but you'll get guaranteed rollbacks if an error occurs after a COMMIT
  • https://mariadb.com/kb/en/mariadb/what-is-in-old-mysql-versions/
• Links:
  • en.wikipedia.org/wiki/MySQL - general info
  • www.mysql.com - front door
  • dev.mysql.com - developer zone

MariaDB

History (10k view)

• MariaDB is a fully SQL-compliant "open source" relational database created by MariaDB Corporation Ab of Sweden.
  • both were named after "Maria", the younger daughter of co-founder Michael "Monty" Widenius
  • open-source means it could be acquired, installed and used, free-of-charge under the GPL license
  • this company only made money by selling support
• Shortly after the purchase of MySQL Ab by SUN Microsystems in 2008, Widenius thought SUN was taking MySQL development in the wrong direction so started his own company called Monty Program Ab. This company forked MySQL into MariaDB to keep it free. Monty Program Ab merged with SkySQL into MariaDB Corporation Ab
• Acquisitions by Oracle (see above) along with other activities in 2011-2012 (see next link) made the Linux community nervous which is why MariaDB is now the preferred relational database on most Linux distros (although you can replace it with MySQL if you desire)
  • http://www.computerworld.com.au/article/457551/dead_database_walking_mysql_creator_why_future_belongs_mariadb/

A few technical details

• Since MariaDB started as an alternate fork of MySQL, it is designed to look and feel like MySQL
• Percona (Performance Consultants) produced XtraDB which is a drop-in-replacement for the InnoDB storage engine
  • caveat: XtraDB is labeled InnoDB inside MariaDB so most people are using XtraDB without realizing it
• in MariaDB-5.5 the default table kind is defined in file my.cfg and is usually set to InnoDB (which means XtraDB in MariaDB)
  

  # Uncomment the following if you are using InnoDB tables
  # current settings are for 10 Meg files
  default_storage_engine  = InnoDB

• It appears that BerkeleyDB is no longer available.
• Only the XtraDB storage engine (which masquerades under the name "InnoDB") is transaction-safe, ACID compliant, and replaces "table locking" with "row locking"
• Tables not requiring transaction-safe ACID compliance default to the Aria Storage Engine (Maria without the "M") which is billed as a crash-proof version of MyISAM
• Links:
  • en.wikipedia.org/wiki/MariaDB - general info
  • mariadb.org (front door)
  • mariadb.com/kb/en/ official documentation
• News:
  • Wikipedia replaces MySQL-5.1 with MariaDB-5.5 (2013)
    • https://blog.wikimedia.org/2013/04/22/wikipedia-adopts-mariadb/
    • http://www.infoworld.com/article/2614268/open-source-software/wikipedia-dumps-mysql--hooks-up-with-mariadb.html
  • 10 reasons to migrate to MariaDB (if still using MySQL in 2015)
    • https://seravo.fi/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql

Notes:

• this is an unfinished script (the NUMA stuff was never implemented)
  • I used this on a 2-socket HPE DL385p_gen8 on June-2021 to disable all the cores associated with the second CPU
    • this saved me from having to drive 160 km to swap out a box and/or unplug one CPU chip
    • also, I did this on a running system so there is the convenience of not having to shutdown etc.
  • this script should work on a 2-socket DL380p (but never tested by me)
  • this script should work on a 4-socket DL580 or DL585 (but never tested by me)
  • caveats:
    • this script begins with three safety parameters (SOCKETS, CORES, THREADS). These must be changed for any platform other than the one I wrote this for so BE VERY CAREFUL.
    • If you do not know what you are doing then please call your resident computer technologist.
    • changes made by this script are not permanent (so it you screw up then just power off followed by a reboot)

Linux-specific stuff

Initial Install

# ensure the optional epel repo is available
sudo yum list \*epel-release\*
# install epel
sudo yum install epel-release
#
sudo yum clean all
sudo yum makecache
sudo yum list mariadb\*
sudo yum install mariadb

update

# backup all of your databases (just in case)
mysqldump --user=neil --password=SuperSecret --all-databases --result-file=db_all_v04.sql 
# -------------------------------
# now update your database engine (first two steps are optional)
sudo yum clean all
sudo yum makecache
# see what should be updated
sudo yum check-update
# update mariadb client + server packages
sudo yum update mariadb
# -------------------------------
# now run the database repair tool
# /usr/bin/mysql_upgrade  --force -uroot -h127.0.0.1  -p