For several years, I have hosted a medium sized Invision Power Board (IPB) site. The site has roughly 9,000+ users and 1,000,000+ posts. I work with MariaDB on a daily basis, and often find myself recommending it to customers. It's only appropriate I put my money where my mouth is, so to speak. This blog post has no benchmarks. It is just a very simple to follow set of steps I took to migrate my site from MySQL 5.1 to MariaDB 10 on CentOS 6.5 with very little downtime for the end users.
Installing MariaDB became very easy on the latest CentOS /RHEL Linux version 7. During initial setup MariaDB has a tick in the graphical setup environment which enables MariaDB and replaces MySQL.
MySQL 5.6 introduces a new "multi-threaded slave" feature (aka "parallel replication") that turns the "slave SQL thread" into a number of distinct threads that each apply events to different schemas. This means that separate slave "worker threads" will be executing different positions in the slave's relay log (corresponding, of course, to different positions in the master's binary log) and that the
Exec_Master_Log_Pos values in
SHOW SLAVE STATUS output are no longer an accurate indicator of the current position of the slave.
For certain situations, the static structure of tables in a relational database can be very limited. Each column is statically defined, has a pre-defined type and you can only enter a value of that type into the column.You can be creative and list multiple values in one column, but then those values are not generally easily accessed and manipulated with other functions. You have to use an API or contortions of a function like SUBSTRING() to pull out a value you want. Even then, you have to know what is contained in the column to be able to manipulate it properly.
The question seemed easy enough:
We've dropped a user, now we want to change the DEFINER on all database objects that currently have it set to this dropped user?
This should be possible by checking the INFORMATION_SCHEMA tables of the appropriate object types (routines, triggers, views and events) and performing an ALTER on each of them that just modifies the DEFINER but nothing else, right?
Auditing is an essential task for monitoring your database environment. Auditing information can help you troubleshoot performance or application issues, and lets you see exactly what SQL queries are being processed. MariaDB's Audit Plugin provides auditing functionality for not only MariaDB, but MySQL as well.
When you need a fast and reliable hosting solution, you should consider which web server and database system to use. Many sites find the combination of nginx (pronounced "engine x") and MariaDB to be an optimal solution. Let's see how to install and configure the applications to work together.
MaxScale, an open-source database-centric router for MySQL and MariaDB makes High Availability possible by hiding the complexity of backends and masking failures. MaxScale itself however is a single application running in a Linux box between the client application and the databases - so how do we make MaxScale High Available? This blog post shows how to quickly setup a Pacemaker/Corosync environment and configure MaxScale as a managed cluster resource. We will guide you step by step on how to enable basic High Availability by setting up three Linux Centos 6.5 servers with MaxScale.
With MariaDB, as with any service, you must monitor user resource usage to ensure optimal performance. MariaDB provides detailed statistics for resource usage on per-user basis that you can use for database service monitoring and optimization. User statistics are especially useful in shared environments to prevent a single gluttonous user from causing server-wide performance deterioration. If you detect abnormal use, you can apply fine-grained limits, as we'll see.
MaxScale for MariaDB and MySQL hides the complexity of database scaling from the application. To streamline building MaxScale from source and running the test suite, you can automate the process with some useful tools to meet your needs. I have created a Vagrant / Puppet setup I'd like to share with you.