Ouch! That hurts! Thurdsay (yesterday) I finished setting up new DB instances for XLsuite. All was well, and the servers performed admirably well for 24 hours. Then, suddenly, the LVM partition that held /var/lib/mysql went away…
Around 18:08 UTC (11:08:09 PDT), our main MySQL database server went down. Luckily, yesterday (Thurdsay), I had just replaced our whole DB infrastructure to have a replicated master/slave setup. It took us 15 minutes to notice that the sites were down, and another 20 minutes to execute a database failover. By 18:50 UTC (11:50 PDT), things were back to normal.
Unfortunately, I did not have the scripts in place yet to do the database failover, so it was a manual process. It wasn’t too hard, but I did have to remember one thing:
1 Mysql::Error: The MySQL server is running with the --read-only option so it cannot execute this statement: INSERT INTO sessions (`updated_at`, `sessid`, `data`) VALUES('2008-08-22 18:50:58', '589efd77796b913a1cdd7875693ea0c2', 'BAh7BzoWaW5jb21pbmdfcmVxdWVzdHNbBiI1L2FkbWluL2ZvcnVtX2NhdGVn\nb3JpZXMvODMvZm9ydW1zLzEzMS90b3BpY3MvMzgyIgpmbGFzaElDOidBY3Rp\nb25Db250cm9sbGVyOjpGbGFzaDo6Rmxhc2hIYXNoewAGOgpAdXNlZHsA\n')
In High Performance MySQL, the authors recommend, in Chapter 8:
On the slave, we recommend enabling the following configuration options:
Double oops! Anyway, the advice in the book was invaluable to me. If you manage MySQL and don’t already have the book, I highly suggest you buy it.