As announced, I held a short talk on switching from MySQL community edition (especially 5.1) to MariaDB (currently 5.2.6) at this years LinuxTag in Berlin.
Here are the (German) slides for reference:
(In case you cannot see the embedded presentation, you can also click here)
Please note: There are a lot of good English slides around. If you want give a talk on MariaDB, the “Beginner’s Guide” might be a good start:
If you happen to be around at this years LinuxTag 2011 in Berlin/Germany, you are invited to attend my short talk on MariaDB as a drop-in replacement for MySQL. The talk focusses on differences between MySQL Community Edition and MariaDB (e.g. XtraDB, Aria, userstats), shows some features live and explains how to switch. I’ll probably post the slides here afterwards.
The talk will be held in German and is scheduled for Friday, the 13th of May, 16:30. The official announcement can be found here.
Today I ran into my first MySQL binlog race condition: The initial problem was quite simple: A typical MySQL master->slave setup with heavy load on the master and nearly no load on the slave, which only serves as a hot fallback and job machine, showed differences on the same table on both machines. The differences showed up from time to time: entries that have been deleted from the master were still on the slave.
After several investigations I started examining the MySQL binlog from the master – a file containing all queries that will be transferred to the slave (and executed there if they don’t match any ignore-db-pattern). I grepped for ids of rows that have not been deleted on the slave as I’s interested if the DELETE statement was in the binlog. In order to read a binlog file just use “mysqlbinlog” and parse the output with grep, less or similar. To my surprise I found the following entries:
$ mysqlbinlog mysql-complete-bin.000335 | grep 1006974 DELETE FROM `tickets` WHERE `id` = 1006974 SET INSERT_ID=1006974/*!*/;
As “SET INSERT_ID” is a result of an INSERT statement it was clear, that MySQL wrote the INSERT => DELETE statements in the wrong order. As INSERT/DELETE sometimes occur quite fast after each other and several MySQL threads are open in the same MySQL server, you might run into a rare INSERT/DELETE race condition as the master successfully executes them, while the slave receives them in the wrong order.
As a comparision this is a normal order of INSERT and DELETE (please note that the actual INSERT is not displayed here):
$ mysqlbinlog mysql-complete-bin.000336 | grep 1007729<br />SET INSERT_ID=1007729/*!*/;<br />DELETE FROM `tickets` WHERE `id` = 1007729<br />
Actually this all so far. Lesson learned for me: A mysql binlog might get you into serious trouble when firing a MySQL server with INSERT and DELETE on the same rows as the linear binlog file can fail the correct statement order, which might be a result of different MySQL threads and an unclean log behavior. I have not yet found a generic solution for the problem but I am looking forward to it.
PLEASE NOTE: I am currently reviewing and extending this document.
While caring for a remarkable amount of MySQL server instances, troubleshooting becomes a common task. It might of interest for you which
Recovering a crashed MySQL server
After a server crash (meaning the system itself or just the MySQL daemon) corrupted table files are quite common. You’ll see this when checking the /var/log/syslog, as the MySQL daemon checks tables during its startup.
Apr 17 13:54:44 live1 mysqld[2613]: 090417 13:54:44 [ERROR] /usr/sbin/mysqld: Table './database1/table1' is marked as crashed and should be repaired
The MySQL daemon just told you that it found a broken MyISAM table. Now it’s up to you fixing it. You might already know, that there is the “REPAIR” statement. So a lot of people enter their PhpMyAdmin afterwards, select database and table(s) and run the REPAIR statements. The problem with this is that in most cases your system is already in production – for instance is up again and the MySQL server already serves a bunch of requests. Therefore a REPAIR request gets slowed down dramatically. Consider taking your website down for the REPAIR – it will be faster and it’s definitely smarter not to deliver web pages based on corrupted tables.
The other disadvantage of the above method is, that you probably just shut down your web server and your PhpMyAdmin is down either or you have dozens of databases and tables and therefore it’s just a hard task to cycle through them. The better choice is the command line in this case.
If you only have a small number of corrupted tables, you can use the “mysql” client utility doing something like:
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.0.75-0ubuntu10 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> REPAIR TABLE database1.table1; +--------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+--------+----------+----------+ | database1.table1 | repair | status | OK | +--------------------+--------+----------+----------+ 1 row in set (2.10 sec)
This works, but there is a better way: First, using OPTIMIZE in combination with REPAIR is suggested and there is a command line tool only for REPAIR jobs. Consider this call:
$ mysqlcheck -u root -p --auto-repair --check --optimize database1 Enter password: database1.table1 OK database1.table2 Table is already up to date
As you see, MySQL just checked the whole database and tried to repair and optimize it.
The great deal about using “mysqlcheck” is, that it can also be run against all databases in one run without the need of getting a list of them in advance:
$ mysqlcheck -u root -p --auto-repair --check --optimize \ --all-databases
Of course you need to consider if an optimize of all your databases and tables might just take too long if you have huge tables. On the other hand a complete run prevents of thinking about a probably missed table.
[update]
nobse pointed out in the comments, that it’s worth having a look at the automatic MyIsam repair options in MySQL. So have a look at them if you want to automate recovery:
Recovering a broken replication
MySQL replication is an easy method of load balancing database queries to multiple servers or just continuously backing up data. Though it is not hard to setup, troubleshooting it might be a hard task. A common reason for a broken replication is a server crash – the replication partner notices that there are broken queries – or even worse: the MySQL slave just guesses there is an error though there is none. I just ran into the latter one as a developer executed a “DROP VIEW” on a non-existing VIEW on the master. The master justs returns an error and ignores. But as this query got replicated to the MySQL SLAVE, the slave thinks it cannot enroll a query and immediately stopped replication. This is just an example of a possible error (and a hint on using “IF EXISTS” as often as possible).
Actually all you want to do now, is telling the slave to ignore just one query. All you need to do for this is stopping the slave, telling it to skip one query and starting the slave again:
$ mysql -u root -p mysql> STOP SLAVE; mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; mysql> START SLAVE;
That’s all about this.
Recreating databases and tables the right way
In the next topic you’ll recreate databases. A common mistake when dropping and recreating tables and databases is forgetting about all the settings it had – especially charsets which can run you into trouble later on (“Why do all these umlauts show up scrambled?”). The best way of recreating tables and databases or creating them on other systems therefore is using the “SHOW CREATE” statement. You can use “SHOW CREATE DATABASE database1″ or “SHOW CREATE TABLE database1.table1″ providing you with a CREATE statement with all current settings applied.
mysql> show create database database1; +-----------+--------------------------------------------------------------------+ | Database | Create Database | +-----------+--------------------------------------------------------------------+ | database1 | CREATE DATABASE `database1` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec)
The important part in this case is the “comment” after the actual create statement. It is executed only on compatible MySQL server versions and makes sure, your are running utf8 on the database.
Keep this in mind and it might save you a lot of trouble.
Fixing replication when master binlog is broken
When your MySQL master crashes there is a slight chance that your master binlog gets corrupted. This means that the slaves won’t receive updates anymore stating:
[ERROR] Slave: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. Error_code: 0
You might have luck when only the slave’s relay log is corrupted as you can fix this with the steps mentioned above. But a corrupted binlog on the master might not be fixable though the databases itself can be fixed. Depending on your time you try to use the “SQL_SLAVE_SKIP_COUNTER” from above but actually the only way is to setup
Setting up replication from scratch
There are circumstances forcing you to start replication from scratch. For instance you have a server going live for the first time and actually all those test imports don’t need to be replicated to the slave anymore as this might last hours. My quick note for this (consider backing up your master database before!)
slave: STOP SLAVE; slave: RESET SLAVE; slave: SHOW CREATE DATABASE datenbank; slave: DROP DATABASE datenbank; slave: CREATE DATABASE datenbank; master: DROP DATABASE datenbak; master: SHOW CREATE DATABASE datenbank; master: CREATE DATABASE datenbank; master: RESET MASTER slave: CHANGE MASTER TO MASTER_USER="slave-user", \ MASTER_PASSWORD="slave-password", MASTER_HOST="master.host"; slave: START SLAVE
You just started replication from scratch, check “SHOW SLAVE STATUS” on the slave and “SHOW MASTER STATUS” on the master.
Deleting unneeded binlog files
Replication needs binlog files – a mysql file format for storing database changes in a binary format. Sometimes it is hard to decide how many of the binlog files you want to keep on the server possibly getting you into disk space trouble. Therefore deleting binlog files that have already been transferred to the client might be a smart idea when running low on space.
First you need to know which binlog files the slave already fetched. You can do this by having a look on “SHOW SLAVE STATUS;” on the slave. Now log into the MySQL master and run something like:
mysql> PURGE BINARY LOGS TO 'mysql-bin.010';
You can even do this on a date format level:
mysql> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';
Conclusion
The above hints might save you same time when recovering or troubleshooting a MySQL server. Please note, that these are hints and you have – at any time – make sure, that your data has an up to date backup. Nothing will help you more.