I fell victim to breaking my own rules today. I upgraded the OS on my web server without backing up my blog database or verifying that the backup server had the latest version.
And so here we are back in Jan 2011. The good news is that I am only missing one post. Alas my lax posting habits have prevailed.
MariaDB is Michael "Monty" Widenius' branch of the popular MySQL database after it was purchased by Sun and taken in a different direction.
I was browsing my RSS Reader the other day and noticed that the latest beta of MariaDB was released as noted on the MySQL Performance Blog and thought as a database user and developer I would give it a try.
Since it was Sunday and I wanted a challenge I decided to install it from source. I downloaded the latest source tarball (5.1.38 Beta) to my Debian 5.0 server and uncompressed it. The build instructions were fairly standard:
1) Install the Prerequisites: apt-get build-dep mysql-server
2) In the source directory I ran: BUILD/autogen.sh
3) Than ./configure --prefix=/opt (I use /opt since I did not want it to interfere with my existing MySQL installation)
4) make
5) make install
From there I created a data directory for the MariaDB using mkdir /opt/data and the mysql_install_db tool (/opt/bin/mysql_install_db --datadir=/opt/data --user=mysql)
I stopped my running MySQL server and launched MariaDB:
/opt/libexec/mysqld --no-defaults --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306 --basedir=/opt --datadir=/opt/data --tmpdir=/tmp --language=/opt/share/mysql/english --skip-external-locking --skip-networking --key_buffer=16M --max_allowed_packet=16M --thread_stack=128K --thread_cache_size=8 --myisam-recover=BACKUP --query_cache_limit=1M --query_cache_size=16M --expire_logs_days=10 --max_binlog_size=100M --plugin_dir=/opt/lib/mysql/plugin --plugin-load=ha_innodb.so:ha_archive.so &
This defines all the appropriate paths so MariaDB does not bump into MySQL, as well as load the InnoDB and Archive plugins.
As per the warnings when I started MariaDB for the first time I upgraded the database using: /opt/bin/mysql_upgrade -u root -p
To test the install I used the MariaDB command line client and the with the default root account: /opt/bin/mysql -u root -p.
MariaDB supports the complete MySQL feature set plus some tweaks including the XtraDB, processlist and extended slow log patch set from Percona.
MariaDB has the potential to surpass MySQL since it's development and expansion is driven by community need rather than corporate desire. It touts itself as being more optimized and speedier than the MySQL default MyISAM via the Maria Storage Engine. I do hope it will lives up to it's claims as I will be following it every step of the way.
I was simply adding a database to the binlog_do_db variable and restarting my master. I was being overly cautious so I started with a running the stop slave io_thread command on the slave to prevent any issues from propagating to the slave.
I than looked for a way to "gracefully" restart mysql so that I would not create any master binary log errors, and elected to use mysqladmin shutdown -p. On the restart using /etc/init.d/mysql start Innodb started throwing a "Incorrect information in file" error for the only 2 Innodb tables running in my database.
After trying a table repair and failing I thought another /etc/init.d/mysql restart could not hurt. And it worked, since my Innodb tables were now back online.
At this point a new problem surfaced on the slave when I start the IO thread. It could not read a binary log from the master and failed about a minute into restarting.
On the master the syslog was showing a "Failed to open log (file '')" error which seemed strange to me. After a quick Google search I found out that this usually had to do with a log showing up in mysql-bin.index that no longer existed.
When I opened the mysql-bin.index in nano I found a bunch of strange characters (@^@^@^@^@^@^@^@^), so I deleted them and restarted the mysql master again.
All is now good in MySQL land (for know). Solution: restart, restart, restart. Moral: Do not mix mysqladmin and init scripts.
In our quest to optimize our MySQL powered application I decided to take MySQL Enterprise Monitor for a test drive. It is easy a snap to install and configure the Service Manager and remote Agents on 2 different servers to test. The only real hitch was the Query Proxy, which I am yet to get working.
I figured for $2999 a year their support should be extremely responsive, and I was not disappointed as I received an email in approximately 10 minutes with some troubleshooting steps. Hopefully this issue will be resolved quickly so I can get on with testing the Query Analyzer (the reason for this experiment).
Otherwise the database and server monitoring is rather sophisticated and robust. However for 3k a year US it is a little steep for our operating budget. The plan is to start from the top and work our way down to something in our price range that meets our need to optimize our database and application query usage.
From what I can see MySQL Enterprise Monitor is definitely tops in that regard.