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 (some items copied from OpenVMS pages)

Why relational databases?

Relational databases are "relational in both time and space"
Consider the following example from Oracle 9i

1. Alice and Bob are bank employees accessing a database consisting of one million accounts.
2. At 9:00, Alice begins generating a financial report which will take 2 minutes to execute (caveat: must be a read-only select)
3. At 9:01, Alice's report generator is halfway through the database.
4. Now, Bob performs a transaction moving $25 from the first account to the last account then commits his changes before Alice's report is finished.
5. In ISAM technology the $25 would be counted twice in Alice's report. Once on the first record and a second time on the last.
6. In a relational technology (with transaction support) Alice will not see any of Bob's changes because:
   • all of Bob's before/after information is stored in the rollback segments (UNDO tables) along with the transaction time.
   • When fetching Alice's report data, the engine will use the start time of her transactional query as a key while watching the UNDO tables to ensure she sees the data as it was when she submitted the query at 9:00.
   • It's as if Alice sees a "snapshot" of the whole database taken at 9:00, and "snapshot" is exactly what the file is called in Oracle-Rdb

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

Linux-specific stuff

# showing the recipe for YUM-based Linux distros
# note: on new systems, executing 'yum' will invoke 'dnf'
# This was done as to not break installation scripts
# -------------------------------------------------------
sudo yum clean all
sudo yum make cache
# see what could be updated
sudo yum check-update
# if you see updates for packages beginning with a, b, c 
sudo yum update a\* b\* c\*
# if you see updates for packages beginning with d, e, f 
sudo yum update d\* e\* f\*
# only update the kernel if you are prepared to do a reboot 
sudo yum update ker\*
reboot

Initial Install (both client + server)

# 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

Engine 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

CLI Examples

• MySQL MariaDB tips (on my OpenVMS page)
• MySQL MariaDB tips for occasional users (on my OpenVMS page)
• MySQL MariaDB maintenance tips (on my OpenVMS page)