MariaDB Encryption ( data at rest )

Here is a simple example showing a couple options for MariaDB Encryption. 

You have to consider what you want to encrypt . The data communication (data in transit) or the data on the instance (data at rest). 

This post is going to focus on the data at rest option using a AWS free tier node running on Amazon Linux. I will be using the world database on 2 different instances to show updating current tables with encryption as well as new loading tables to be auto-encrypted. 

1st we will start with installs.. quick and simple just for this demo. 

 # yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id 
server_id=100

# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using  EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+

We will load the world db into server_id 100 instance. 

# mysql < world.sql
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)  

Now we can see that currently, both instances are not using encryption. 

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

Now across both systems, I am going to set up Random Keys and encrypt them. 

#  mkdir /etc/mysql/

#  mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
#  (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1    -pass file:/etc/mysql/encryption/keyfile.key    -in /etc/mysql/encryption/keyfile    -out /etc/mysql/encryption/keyfile.enc

 ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc

Now we can set up the cnf file to enable the plugin as well as options for encryption. 

# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

Restart MariaDB so we can check on the current status. 

systemctl restart mariadb.service

mysql

MariaDB [(none)]> select @@server_id;

+-------------+

| @@server_id |

+-------------+

|         100 |

+-------------+

1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

+----------------------------+-------------------+----------------+

| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

+----------------------------+-------------------+----------------+

| innodb_system              |                 1 |              1 |

| mysql/innodb_table_stats   |                 1 |              1 |

| mysql/innodb_index_stats   |                 1 |              1 |

| mysql/transaction_registry |                 1 |              1 |

| mysql/gtid_slave_pos       |                 1 |              1 |

| world/city                 |                 1 |              1 |

| world/country              |                 1 |              1 |

| world/countrylanguage      |                 1 |              1 |

+----------------------------+-------------------+----------------+

8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;

+-------------+

| @@server_id |

+-------------+

|         200 |

+-------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

+----------------------------+-------------------+----------------+

| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

+----------------------------+-------------------+----------------+

| innodb_system              |                 1 |              1 |

| mysql/innodb_index_stats   |                 1 |              1 |

| mysql/gtid_slave_pos       |                 1 |              1 |

| mysql/innodb_table_stats   |                 1 |              1 |

| mysql/transaction_registry |                 1 |              1 |

+----------------------------+-------------------+----------------+

5 rows in set (0.000 sec)

Load up the world data into the server_id 200 instance as well. 

# mysql < world.sql
# mysql 
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
| world/city                 |                 1 |              1 |
| world/country              |                 1 |              1 |
| world/countrylanguage      |                 1 |              1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

According to the information_schema.INNODB_TABLESPACES_ENCRYPTION we are encrypted now. However, they do not show that at the schema level.  While they say it is encrypted if showing up in the INNODB_TABLESPACES_ENCRYPTION table, I would rather be sure and have it seen in the table and on the schema. 

MariaDB [(none)]> show create table world.city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)

Up to this point, you can see that both instances have been accounted for in the INNODB_TABLESPACES_ENCRYPTION schema after the restart or loading of the schema and data. 

So... a few table alters will help here... 

MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage  ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city\G

*************************** 1. row ***************************

       Table: city

Create Table: CREATE TABLE `city` (

  `ID` int(11) NOT NULL AUTO_INCREMENT,

  `Name` char(35) NOT NULL DEFAULT '',

  `CountryCode` char(3) NOT NULL DEFAULT '',

  `District` char(20) NOT NULL DEFAULT '',

  `Population` int(11) NOT NULL DEFAULT 0,

  PRIMARY KEY (`ID`),

  KEY `CountryCode` (`CountryCode`),

  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1

1 row in set (0.000 sec)

This is simple and etc so far.. now we need to enable binlogs and double check more. 

vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)

Checking via a look at the binlogs.... 

mariadb-binlog--base64-output=DECODE-ROWS --verbose  demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Good to see that it says it is being encrypted now. 

MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
       Table: city2
Create Table: CREATE TABLE `city2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)

I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...

mariadb-binlog  --base64-output=DECODE-ROWS --verbose --read-from-remote-server   demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225  0:06:06 server id 100  end_log_pos 296 CRC32 0x0c89f3bb Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225  0:06:06 server id 100  end_log_pos 325 CRC32 0x535776a2 Gtid list []
# at 325
#240225  0:06:06 server id 100  end_log_pos 363 CRC32 0x2ac4a61b Binlog checkpoint demo.000001
# at 363
#240225  0:09:40 server id 100  end_log_pos 405 CRC32 0x93e10dc4 GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225  0:09:40 server id 100  end_log_pos 501 CRC32 0x39269040 Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225  0:09:49 server id 100  end_log_pos 543 CRC32 0xde82b753 GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225  0:09:49 server id 100  end_log_pos 602 CRC32 0x05bbb9e6 Annotate_rows:
#Q> insert into city2 select * from city
#240225  0:09:49 server id 100  end_log_pos 661 CRC32 0x9e0b4e0d Table_map: `world`.`city2` mapped to number 21
# at 661

Hopefully, this can at least help get you started .... 

Resources:

https://mariadb.com/kb/en/securing-mariadb-encryption/  

Proxy MySQL :: HAproxy || ProxySQL & KeepAlived

So when it comes to routing your MySQL traffic several options exist.

• HAproxy
• MariaDB MaxScale
• NGINX
• MySQL Router
• ProxySQL
• You can even still get MySQL Proxy if you wanted it but it is EOF. 

Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested: 

• https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/haproxy.html

Personally I like ProxySQL. Percona also has  few blogs on this as well

• https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
• https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/
• https://www.percona.com/blog/2017/01/25/proxysql-admin-configuration/
• https://www.percona.com/blog/2016/09/15/proxysql-percona-cluster-galera-integration/

Percona also has ProxySQL version available 

• https://www.percona.com/downloads/proxysql/

I was thinking I would write up some examples but overall Percona has explained it all very well.  I do not want to take anything away from those posts, instead point out that a lot of good information is available via those urls. So instead of rewriting what has already been written, I will create a collection of information for those interested. 

First compare and decide for yourself what you need and want. The following link of course is going to be biased towards ProxySQL but it gives you an overall scope for you to consider. 

• http://www.proxysql.com/compare

If you have a cluster or master to master and you do not care which server the writes vs reads go onto, just as long as you have a connection; then HAproxy is likely a simple fast set up for you. 

The bonus with ProxySQL is the ability to sort traffic in a weighted fashion, EASY. So you can have writes go to node 1, and selects pull from node 2 and node 3. Documentation on this can be found here:

• https://github.com/sysown/proxysql/wiki/ProxySQL-Read-Write-Split-(HOWTO)

Yes it can be done with HAproxy but you have to instruct the application accordingly. 

• https://severalnines.com/resources/tutorials/mysql-load-balancing-haproxy-tutorial

This is handled in ProxySQL based on your query rules.

• https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules
• https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration#mysql-query-rules

Now the obvious question here: OK so how do you keep ProxySQL from becoming the single point of failure?  

You can invest is a robust load balancer and etc etc etc ... Toss hardware at it.... Or make it easy on yourself and support open source and use KeepAlived.  This is VERY easy to set up and all of it is documented again well here: 

• https://www.percona.com/blog/2017/01/19/setup-proxysql-for-high-availability-not-single-point-failure/
• http://www.keepalived.org/doc/ 
• To be fair here is an example for keepalived and HAproxy as well 
•  https://andyleonard.com/2011/02/01/haproxy-and-keepalived-example-configuration/

If you ever dealt with lua and mysql-proxy, ProxySQL and Keepalived should be very simple for you. If you still want it for some reason: https://launchpad.net/mysql-proxy

Regardless if you choose HAproxy, ProxySQL or another solution, you need to ensure not to replace once single point of failure with another and keepalived is a great for that. So little reason to not do this if you are using a proxy. 

So a few more things on ProxySQL. 

• If you track hosts that connect to your database via your reporting or monitoring , realize those IPS or hostnames are now going to be the proxy server. 
• What about all the users you already have in MySQL then? Can you migrate them to proxysql? Yes you can. It takes a few steps but it is do able. Here is an example of this:
• https://dba.stackexchange.com/questions/164705/how-to-easily-bring-80-mysql-users-into-proxysql
• Make sure you understand the Multi layer configuration system. Save your info to disk!
• https://github.com/sysown/proxysql/wiki/Main-(runtime)#runtime-tables
• https://github.com/sysown/proxysql/wiki/Main-(runtime)#disk-database
• Can ProxySQL run on the MySQL Default port 3306 
• Yes Edit the mysql-interfaces
• Keep in mind now your max_connections. If you have Max_connections in mysql set to 500, then that is your limit of course for standard users. With ProxySQL you can now spread users across the system and set a max per node. So to help ensure you do not hit 500 connections set the mysql-max_connections a little bit lower than MySQL value. 
• Take advantage of the Monitor Module and STATS .. Know what is going on with  your proxy and traffic. 
• Take advantage of Query Caching if applicable for your application.

Hard work that goes unnoticed ....

I took a moment today and updated one of my Linux distributions.  In this distribution I happen to have Percona 5.6 installed as the MySQL Database. I have mentioned before how you can set up your choice of MySQL via a Yum repository.

My point here is though, how do we ever thank these people for all the work they do?

Many of these repositories are run by companies and these people get paid for what they do. Yet, through survey's and general observation/questions of the Linux (including Debian/Ubuntu) community, a majority of people will not upgrade until it is available in their distribution. I happen to be  one who wants to stay on top of security and bug fixes, so I have the yum repository update from the source as often as possible.

My point is,  a lot of work goes into packaging of these files for distribution and for the most part it looks like a pretty thankless job. I recall the older (not old but older) days of tar and gzip, when you had to dig and find the dependencies yourself. -- ./configure..  nope need something else go download and install that then try again.....

I just upgraded 25 different packages in a few moments, which would have taken some time before. While Yum and Apt Get are far from new, and I sound like an old timer here, I just thought it might be nice to say thanks, to all of the people who work behind the scenes to make all of our Linux experiences, let alone the related MySQL installs, easier and smoother.

I will point out that Oracle does have 5.6 packages now available.

• http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL56/

I recall that my previous post mentioned how it had not been. 

A strategy from the community

We have seen the news about MariaDB replacing MySQL in Fedora , SUSE and Red Hat.

While Oracle would not be happy about such news, the open source community supports the focus on a "more" open source solution to be implemented into Linux.

The interesting thing that we all might over look is that, the decision or strategy to move to MariaDB from MySQL was likely not just made by the upper management in Red Hat. This is much more likely to be a movement from the open source community that Red Hat evaluated and they listened.

Consider this, take a look back at Jackie Yeaney's (@jackieyeaney) post about the "Democratizing the Corporate Strategy Process at Red Hat" (posted Nov 10th, 2011) and learn how Red Hat works. "We utilized existing networks in the open source community to "keep our minds open" and socialize ideas outside of Red Hat."  The community wanted openness and a result was a move to MariaDB by Red Hat, is is related to the open strategy at Red Hat, in my humble opinion it is likely yes.

 Jim Whitehurst (@JWhitehurst) appears to embrace the open source community not just because of the financial gains it rewards the company but also because how it revolutionized how they work, make strategic decisions and take input from others: "Given enough eyeballs, all bugs are shallow." 

Take a moment to relate that last statement to MySQL.  If you follow MySQL then you are well aware that Oracle closed (or have a less open version now) the bugs.mysql.com site. While Oracle has their own corporate reasoning for that, the open source community follows "Given enough eyeballs, all bugs are shallow."  

As MariaDB grows and become more imbedded into the Linux distros as the default DB the related bug tracking will be open and it will be interesting to see how the bugs split across Maria and MySQL in the long run.

So now.. the interesting aspect is while MySQL had enterprise and community versions owned by Oracle the move still happened because of MariaDB.  Oracle also owns Java and the related OpenJDK. While I am just asking this as an outsider looking in... If a Java/OpenJDK  was branched out into another software package that was not owned by Oracle, would we soon see an replacement for Java/OpenJDK in Red Hat as well ?  Again I am an outsider to the Java world, so I just ask that because of the similarities it represents with MySQL.

 

MariaDB & the Linux Distributions

So by now many of you have seen the news about Google, SUSE and  Red Hat / Fedora moving to MariaDB as the default database instead of MySQL.

MariaDB and SkySQL have made very productive business moves this year. What does this really mean for MySQL community and the general open source community ?

For starters think back to what made MySQL so popular? It was readily available in all the top Linux distributions.

OpenSuse and Fedora already are moving to MariaDB so the push for an open source focused movement has begun. Following a RHEL migration that has MariaDB as the default database would then also mean that CentOS would soon have MariaDB as the default database. 

Likely soon to follow will be a move by Ubuntu and Debian. I could have missed the news on a move already but I don't think so.

All of this is big news and moves for MariaDB and the open source community. MariaDB will naturally begin to see more user acceptance and use. While Oracle's MySQL is still an open source software package the big issue has been the bugs.mysql.com site and tracking of mysql user bugs. Will people soon start to track more bugs at MariaDB?

MariaDB also has open source features that mimic the enterprise only solutions available in Oracle's MySQL. So many users are going to naturally pick up on those features. 

While Oracle is building great features and code, but how many in the community are taking it? MySQL 5.1 is heavily used in the community used Linux distributions and many users might only know MySQL 5.1 and so soon MariaDB 5.5.   

What will happen next?
Well what Oracle decides to do is still yet to be seen. Oracle already takes Red Hat Linux to build their Oracle Linux (OEL). So ironically now MariaDB, which is forked from Oracle's MySQL, will be in Red Hat Linux for Oracle to remove for their OEL. Will Oracle fight back with Java in some way?

What will Percona do? Percona is also a player in this and has had a respectful relationship with MariaDB and Oracle over the years. Naturally, Percona does lean towards the open-source side of things so it will be curious to see if any move occurs on Percona's part.  Will more tools focused on MariaDB features becoming soon?

What will MariaDB do?  Well, monitoring the growth rate of bugs in the MariaDB Bugs database will help show how many people are starting to take on MariaDB.
Will Maria at some point break away from the MySQL source code updates and continue builds with just the community and their engineers? 

So what happens next?  We do not know. It is up to the open source community. A lot of angry voices had been directed at Oracle over the years. So how do those voices change or show support to MariaDB will soon to be seen. While many of them likely already support MariaDB, it is the adoption of the masses that will show how well MariaDB does in regards to MySQL. Many companies know the name MySQL and will be hesitant to move to MariaDB (This happened to me just the other day.) . So MariaDB's work is not yet done.

• http://www.zdnet.com/red-hat-will-switch-from-oracle-mysql-to-mariadb-reports-7000017907/
• https://www.openshift.com/blogs/why-mariadb-matters-the-openshift-interview-with-monty
• http://www.bytebot.net/blog/archives/2013/07/11/some-mariadb-related-news-from-the-red-hat-front
• http://www.zdnet.com/google-quietly-dumps-oracle-mysql-for-mariadb-7000020670/
• http://www.zdnet.com/opensuse-also-considers-switching-from-mysql-to-mariadb-7000010223/
• https://lists.launchpad.net/maria-discuss/msg00514.html
• https://mariadb.com/kb/en/reporting-bugs/

And yet after all of this... Oracle,  Red Hat and Google still come together to help the us government. 

MySQL YUM Repo (Oracle's, MariaDB's and Percona's)

Lots of people today prefer to stick with the yum package manager when installing their related software over downloading the latest RPM from MySQL for example.

While you can download RPMS from a vendor and install with yum (yum install *.rpm ) you also can update your yum repo to pull directly from the Vendor for MySQL packages. At the time of this post you will only get you up to MySQL 5.5.13 even though MySQL 5.6 GA was released 5 February 2013 via the Oracle repo. Now that MariaDB has released MariaDB-5.5.33 I would hope Oracle will get a move on and update their public repo.

Regardless of what you choose. Here is how to set up vendor repos so you can access what you would like.

All of the instances have pages that I have listed that are easy to follow and set up. I will go ahead and give examples as well.

I will use CentOS 6 64bit for these examples.

In all of the  cases you will be working from the yum.repos.d directory as root.
cd /etc/yum.repos.d

http://public-yum.oracle.com
wget https://public-yum.oracle.com/public-yum-ol6.repo
# vi public-yum-ol6.repo
Locate the following and edit enabled to 1 from 0 then save the file.




[ol6_MySQL]
name=MySQL for Oracle Linux 6 ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/MySQL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1

yum list | grep MySQL
mysql.x86_64                              5.5.34-1.el6                ol6_MySQL
mysql-devel.x86_64                        5.5.34-1.el6                ol6_MySQL
mysql-embedded.x86_64                     5.5.34-1.el6                ol6_MySQL
mysql-embedded-devel.x86_64               5.5.34-1.el6                ol6_MySQL
mysql-libs.x86_64                         5.5.34-1.el6                ol6_MySQL
mysql-libs-compat.x86_64                  5.5.34-1.el6                ol6_MySQL
mysql-server.x86_64                       5.5.34-1.el6                ol6_MySQL
mysql-test.x86_64                         5.5.34-1.el6                ol6_MySQL


https://downloads.mariadb.org/mariadb/repositories/
vi MariaDB.repo






MariaDB does offer you the choice to pick 5.5 OR 10, I used 5.5 for this example.

# MariaDB 5.5 CentOS repository list - created 2013-09-24 21:59 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1


MariaDB-Galera-server.x86_64              5.5.32-1                    mariadb  
MariaDB-client.x86_64                     5.5.33a-1                   mariadb  
MariaDB-common.x86_64                     5.5.33a-1                   mariadb  
MariaDB-compat.x86_64                     5.5.33a-1                   mariadb  
MariaDB-devel.x86_64                      5.5.33a-1                   mariadb  
MariaDB-server.x86_64                     5.5.33a-1                   mariadb  
MariaDB-shared.x86_64                     5.5.33a-1                   mariadb  
MariaDB-test.x86_64                       5.5.33a-1                   mariadb  
galera.x86_64                             23.2.6-1.rhel6              mariadb




http://www.percona.com/doc/percona-server/5.5/installation/yum_repo.html
 vi Percona.repo

[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1

percona-toolkit.noarch                    2.2.4-1                     @/percona-toolkit-2.2.4-1.noarch
percona-xtrabackup.x86_64                 2.1.3-608.rhel6             @/percona-xtrabackup-2.1.3-608.rhel6.x86_64
Percona-SQL-50-debuginfo.x86_64           5.0.92-b23.89.rhel6         percona  
Percona-SQL-client-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-devel-50.x86_64               5.0.92-b23.89.rhel6         percona  
Percona-SQL-server-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-shared-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-shared-compat.x86_64          5.0.92-b23.89.rhel6         percona  
Percona-SQL-test-50.x86_64                5.0.92-b23.89.rhel6         percona  
Percona-Server-51-debuginfo.x86_64        5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-55-debuginfo.x86_64        5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-56-debuginfo.x86_64        5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-client-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-client-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-client-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-devel-51.x86_64            5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-devel-55.x86_64            5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-devel-56.x86_64            5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-server-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-server-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-server-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-shared-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-shared-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-shared-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-shared-compat.x86_64       5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-shared-compat-51.x86_64    5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-test-51.x86_64             5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-test-55.x86_64             5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-test-56.x86_64             5.6.13-rc60.6.427.rhel6     percona  
Percona-XtraDB-Cluster-client.x86_64      1:5.5.33-23.7.6.495.rhel6   percona  
Percona-XtraDB-Cluster-debuginfo.x86_64   1:5.5.33-23.7.6.495.rhel6   percona  
Percona-XtraDB-Cluster-devel.x86_64       1:5.5.33-23.7.6.495.rhel6   percona  
Percona-XtraDB-Cluster-galera.x86_64      2.7-1.157.rhel6             percona  
                                          2.7-1.157.rhel6             percona  
Percona-XtraDB-Cluster-server.x86_64      1:5.5.33-23.7.6.495.rhel6   percona  
Percona-XtraDB-Cluster-shared.x86_64      1:5.5.33-23.7.6.495.rhel6   percona  
Percona-XtraDB-Cluster-test.x86_64        1:5.5.33-23.7.6.495.rhel6   percona  
jemalloc.x86_64                           3.3.1-1.el6                 percona  
jemalloc-devel.x86_64                     3.3.1-1.el6                 percona  
percona-cacti-templates.noarch            1.0.4-1                     percona  
percona-nagios-plugins.noarch             1.0.4-1                     percona  
percona-playback.x86_64                   0.6-2.el6                   percona  
percona-playback-debuginfo.x86_64         0.6-2.el6                   percona  
percona-playback-devel.x86_64             0.6-2.el6                   percona  
percona-xtrabackup.x86_64                 2.1.5-680.rhel6             percona  
percona-xtrabackup-20.x86_64              2.0.8-587.rhel6             percona  
percona-xtrabackup-20-debuginfo.x86_64    2.0.8-587.rhel6             percona  
percona-xtrabackup-20-test.x86_64         2.0.8-587.rhel6             percona  
percona-xtrabackup-test.x86_64            2.1.5-680.rhel6             percona  
qpress.x86_64                             11-1.el6                    percona  
qpress-debuginfo.x86_64                   11-1.el6                    percona
 

Hopefully this will help you all be able to get updated beyond what might be in your standard repos at the moment.


AS AN UPDATE:
 I should also point out that MariaDB and Percona also have additional repos for other Linux distros.

• https://downloads.mariadb.org/mariadb/repositories/
• http://www.percona.com/doc/percona-server/5.5/installation/apt_repo.html#

max_binlog_cache_size

As you evaluate your database performance and stability it is very likely that you will start to review your variables.

At a glance the typical first reaction to the variables below is.. WAIT something is wrong my box does not have that much RAM or even disk space to meet that MAX limits listed below....


MariaDB [(none)]> select @@max_write_lock_count, @@max_binlog_cache_size, @@max_seeks_for_key, @@myisam_max_sort_file_size\G
*************************** 1. row ***************************
     @@max_write_lock_count: 4294967295                -- 4GB
    @@max_binlog_cache_size: 1844674407370954752      --1.6EB
        @@max_seeks_for_key: 429496729                 -- 4GB
@@myisam_max_sort_file_size: 9223372036853727232        --8EB


You are not alone in concerns with these variables as a few bugs have been listed about these variables over the years. Below are just a few of some legacy ones.

• http://bugs.mysql.com/bug.php?id=31177
• http://bugs.mysql.com/bug.php?id=56408
• http://bugs.mysql.com/bug.php?id=31066

"MySQL currently cannot work with binary log positions greater than 4GB."
Keep in mind that these are just the DEFAULT and MAX settings. You can adjust them to make you feel more comfortable.


MariaDB [(none)]> SET GLOBAL max_binlog_cache_size = 4294967296;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT @@max_binlog_cache_size;
+-------------------------+
| @@max_binlog_cache_size |
+-------------------------+
|              4294967296 |  -- 4GB
+-------------------------+
1 row in set (0.00 sec)


Why you would want to... That is an entirely different topic. This is just the upper limit allowed and transactions get split into 4GB anyway. "The maximum recommended value is 4GB", so you can update it if you so choose too.

Read more about your options with this in the MySQL Documentation:
http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_max_binlog_cache_size

MariaDB 10.0.3 Alpha install on Fedora 17 x86_64

MariaDB 10.0.3 Alpha was just released.
So for those of you that recall my previous MariaDB 5.5 install post, I decided to see how it works with 10.0.3.

I like some of the features that are going into the MariaDB and Percona releases. Even if you are a big supporter of MySQL, when features are available in these releases that are not being back ported into MySQL releases DBAs have to review their options and make a choice.

So the install....

As I said before from the previous post I have this installed. So I will just to an upgrade first.


[root@Fedora64 10]# rpm -qa | grep maria
mariadb-5.5.31-1.fc17.x86_64
mariadb-server-5.5.31-1.fc17.x86_64
mariadb-libs-5.5.31-1.fc17.x86_64
mariadb-bench-5.5.31-1.fc17.x86_64
mariadb-devel-5.5.31-1.fc17.x86_64


So  packages conflicted at the start.


MariaDB-10.0.3-fedora17-x86_64-client.rpm
MariaDB-10.0.3-fedora17-x86_64-common.rpm
MariaDB-10.0.3-fedora17-x86_64-compat.rpm
MariaDB-10.0.3-fedora17-x86_64-connect-engine.rpm
MariaDB-10.0.3-fedora17-x86_64-devel.rpm
MariaDB-10.0.3-fedora17-x86_64-server.rpm
MariaDB-10.0.3-fedora17-x86_64-shared.rpm
MariaDB-10.0.3-fedora17-x86_64-test.rpm
 
[root@Fedora64 10]# rpm -Uhv *.rpm
warning: MariaDB-10.0.3-fedora17-x86_64-client.rpm: Header V3 DSA/SHA1 Signature, key ID 1bb943db: NOKEY
error: Failed dependencies:
    libodbc.so.2()(64bit) is needed by MariaDB-connect-engine-10.0.3-1.x86_64
    MySQL-devel conflicts with (installed) mariadb-devel-5.5.31-1.fc17.x86_64 

MariaDB-server-10.0.3-1.x86_64 conflicts with file from package mariadb-server-5.5.31-1.fc17.x86_64 
[root@Fedora64 10]# 


So this is just a Virtualbox instance, for demo and evaluation, so I just removed all that I could and had to uninstall.  I was hopeful that an upgrade would work but this is Alpha code still.
ie:


[root@Fedora64 10]# rpm -e mariadb mariadb-server mariadb-bench
[root@Fedora64 10]# rpm -e mariadb-libs perl-DBD-MySQL percona-xtrabackup


So now that the past is cleared out... 


[root@Fedora64 10]# rpm -ihv *.rpm
Preparing...                ########################################### [100%]
   1:MariaDB-common         ########################################### [ 11%]
   2:MariaDB-server         ########################################### [ 22%]
   3:MariaDB-cassandra-engin########################################### [ 33%]
   4:MariaDB-client         ########################################### [ 44%]
   5:MariaDB-devel          ########################################### [ 56%]
   6:MariaDB-shared         ########################################### [ 67%]
   7:MariaDB-test           ########################################### [ 78%]
   8:MariaDB-compat         ########################################### [ 89%]
   9:galera                 ########################################### [100%]


If you recall the past post, I got the init.d script this time..


[root@Fedora64 10]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!
[root@Fedora64 10]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.3-MariaDB MariaDB Server


If they are not terrible on performance then I do not see why these are not on by default:


vi /etc/my.cnf
[mysqld]
userstat=1
# http://www.percona.com/doc/percona-server/5.5/diagnostics/user_stats.html?id=percona-server:features:userstatv2 
# https://kb.askmonty.org/en/user-statistics/  
feedback=ON
# https://kb.askmonty.org/en/user-feedback-plugin/
MariaDB [(none)]> show variables like '%feedback%';
+--------------------------+------------------------------------------+
| Variable_name            | Value                                    |
+--------------------------+------------------------------------------+
| feedback_send_retry_wait | 60                                       |
| feedback_send_timeout    | 60                                       |
...
| feedback_url             | https://mariadb.org/feedback_plugin/post |
| feedback_user_info       |                                          |
+--------------------------+------------------------------------------+
MariaDB [(none)]> show variables like '%userstat%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat      | ON    |
+---------------+-------+



Issues I found 30seconds after install...:


MariaDB [(none)]> show variables;
ERROR 1946 (HY000): Failed to load replication slave GTID position from table mysql.gtid_slave_pos 


That is it so far... I have it installed and can review now...

UPDATE:
I have submitted this as a bug. The MariaDB team got right back to me and pointed out that I failed to run mysql_upgrade and restart. That fixed the Error listed above. Still variables feels like it should show everything it has but this is a valid fix and mistake on my part. Thank you to the MariaDB team.


To learn more:

• https://kb.askmonty.org/en/mariadb-1003-release-notes/
• http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/mariadb10andwhatsnewwiththeproject-130426094744-phpapp01.pdf

Yum Install MariaDB/MySQL disaster but fixed

So this should be an easy install of MariaDB/MySQL. I do not think this was a Maria issue but just an overall bug. Here is what happened and how I fixed it.

yum install mariadb-server
Then I added the rest to have what you see below.

[root@Fedora64 log]# rpm -qa | grep maria
mariadb-5.5.31-1.fc17.x86_64
mariadb-server-5.5.31-1.fc17.x86_64
mariadb-libs-5.5.31-1.fc17.x86_64
mariadb-devel-5.5.31-1.fc17.x86_64



I thought it was odd that I did not get a /etc/init.d/mysql file but I went with it, I wanted to see what happened.

[root@Fedora64 log]# mysqld_safe
130608 19:54:36 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

130608 19:54:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130608 19:54:37 InnoDB: The InnoDB memory heap is disabled
130608 19:54:37 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130608 19:54:37 InnoDB: Compressed tables use zlib 1.2.5
130608 19:54:37 InnoDB: Using Linux native AIO
130608 19:54:37 InnoDB: Initializing buffer pool, size = 128.0M
130608 19:54:37 InnoDB: Completed initialization of buffer pool
130608 19:54:37 InnoDB: highest supported file format is Barracuda.
130608 19:54:38  InnoDB: Waiting for the background threads to start
130608 19:54:39 Percona XtraDB (http://www.percona.com) 5.5.31-MariaDB-30.2 started; log sequence number 1597945
130608 19:54:39 [Note] Plugin 'FEEDBACK' is disabled.
130608 19:54:39 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
130608 19:54:39 [Note] Server socket created on IP: '0.0.0.0'.
130608 19:54:39 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
130608 19:54:39 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended



Wow... The first run and a failure is not a good sign. This is a fresh install mysql directory should have been installed. So I started with --skip-grant-tables so I could get into the box and look around.

[root@Fedora64 mysql]# ls -la
total 28700
drwxr-xr-x.  2 mysql mysql     4096 Jun  8 19:58 .
drwxr-xr-x. 43 root  root      4096 Jun  8 19:41 ..
-rw-rw----.  1 mysql mysql    16384 Jun  8 19:50 aria_log.00000001
-rw-rw----.  1 mysql mysql       52 Jun  8 19:50 aria_log_control
-rw-rw----.  1 mysql mysql 18874368 Jun  8 19:50 ibdata1
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:58 ib_logfile0
-rw-rw----.  1 mysql mysql  5242880 Jun  8 19:45 ib_logfile1
[root@Fedora64 mysql]#

[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables
130608 20:02:45 mysqld_safe Logging to '/var/log/mysqld.log'.
130608 20:02:45 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql



OK so it started... and does run but still a MAJOR problem still no MYSQL table !

[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
Phase 3/3: Running 'mysql_fix_privilege_tables'...
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed



This can still be fixed....

[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

MariaDB [(none)]> create database mysql ;
Query OK, 1 row affected (0.13 sec)

MariaDB [(none)]> exit
Bye


OK now it has a mysql table I should be able to upgrade it

[root@Fedora64 /]# mysql_upgrade
Phase 1/3: Fixing table and database names
Phase 2/3: Checking and upgrading tables
Processing databases
information_schema
mysql
Phase 3/3: Running 'mysql_fix_privilege_tables'...
OK
[root@Fedora64 /]#


OK So I stopped mysqld and started it without --skip-grants after all it just installed it and I have not yet set a password.

[root@Fedora64 mysql]# mysqld_safe
[root@Fedora64 /]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)



Ok so let me try that again...

[root@Fedora64 mysql]# mysqld_safe --skip-grant-tables

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.01 sec)

MariaDB [mysql]> select * from user;
Empty set (0.00 sec)

MariaDB [(none)]>  create user root ;



I cannot use the following command because --skip-grant-tables in enabled.
create user root identified by '';

 So now I have a root user only by name because it has zero privileges.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)



So I have to go into the box again with --skip-grant-tables enabled and update the root account

 UPDATE user
SET Select_priv = 'Y',
  Insert_priv='Y',
           Update_priv='Y',
           Delete_priv='Y',
           Create_priv='Y',
             Drop_priv='Y',
           Reload_priv='Y',
         Shutdown_priv='Y',
          Process_priv='Y',
             File_priv='Y',
            Grant_priv='Y',
       References_priv='Y',
            Index_priv='Y',
            Alter_priv='Y',
          Show_db_priv='Y',
            Super_priv='Y',
 Create_tmp_table_priv='Y',
      Lock_tables_priv='Y',
          Execute_priv='Y',
       Repl_slave_priv='Y',
      Repl_client_priv='Y',
      Create_view_priv='Y',
        Show_view_priv='Y',
   Create_routine_priv='Y',
    Alter_routine_priv='Y',
      Create_user_priv='Y',
            Event_priv='Y',
          Trigger_priv='Y',
Create_tablespace_priv='Y'
WHERE user = 'root';


Now a restart without --skip-grant-tables enabled and I am in as root!

[root@Fedora64 /]# ps -ef | grep mysql
root      4522  1513  0 20:26 pts/0    00:00:00 /bin/sh /bin/mysqld_safe
mysql     4650  4522  0 20:27 pts/0    00:00:03 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root      8348  3178  0 20:47 pts/1    00:00:00 grep --color=auto mysql
[root@Fedora64 /]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.31-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>



WHEW. This is more of an example of how to fix it when things go wrong but I still wanted the /etc/init.d/mysql file

MySQL 4.1 -- Please Upgrade

A MySQL DBA is often asked to help with various versions of MySQL.

SELECT VERSION();
+----------------+
| VERSION() |
+----------------+
| 4.1.18-classic |
+----------------+


But I beg you all... Evaluate your options and upgrade. 

MySQL has made numerous SECURITY issues updates let alone performance updates. Check your version of MySQL. If it is anything below 5.5  or at a big stretch 5.1.69 PLEASE UPGRADE. 

While you might consider your database "working" and available for upgrade when it breaks.... Will it require some work... Yes. Will it save you in the long run.. Yes . Will you get more out of your system... Yes...  Could you take advantage of fixed bugs... Yes.   Would you rather be "broken" by a security vulnerability?  

Stop and think for a moment about what you will say to the CEO when the CEO asks why did you get hacked ?

Take a look at your system against the known security vulnerabilities:

4.1

• http://www.cvedetails.com/vulnerability-list/vendor_id-185/product_id-316/version_id-44749/Mysql-Mysql-4.1.22.html
• https://support.quest.com/SolutionDetail.aspx?id=SOL13905

5.1

• http://www.cvedetails.com/vulnerability-list/vendor_id-93/product_id-21801/version_id-120479/Oracle-Mysql-5.1.61.html
• http://www.infoworld.com/d/security/mysql-vulnerability-allows-attackers-bypass-password-verification-195349

Take advantage of all the new versions available:

MySQL 5.5       -- http://dev.mysql.com/downloads/mysql/5.5.html#downloads

MySQL 5.6       -- http://dev.mysql.com/downloads/mysql/

Percona             -- http://www.percona.com/downloads/Percona-Server-5.5/LATEST/

MariaDB 10.0   -- https://downloads.mariadb.org/mariadb/10.0.2/

MariaDB 5.5.31-- https://downloads.mariadb.org/mariadb/5.5.31/