Un-maned Failover Operations
I have video of two MySQL servers (one master, on slave) failing over from Master to Slave. The Master becomes the Slave and vise-verse with the slave becoming read-only. Next some load testing and then I’ll post how I did it with FREE software.
Click here to view the video on YouTube.
MySQL doesn’t often crash, but, if you use MySQL on a production system you should have High Availability.
Maybe you’ve not heard of MHA. Yoshinori Matsunobu only released it in July of this year (2011). I’ve been reviewing it and I think you should too. There are a lot of people chasing after this “holy grail” and most systems are complex and / or hard to recover when they fail. MHA simple and easy to use.
MHA is a fail over tool. It’s designed to fail a master and promote a slave to a new master. (This is simply to say but you should read how it’s done.) It can monitor your master or you can manually fail over. Failing back doesn’t happen. Your expected to fix the failed master and turn it into a slave. The monitoring program (masterha_manager) will stop running after a fail over.
I’m not going to give details on how MHA works. I’ll assume you have a master and some number of slaves already working.
Download the latest version of MHA from the Google Code server. http://code.google.com/p/mysql-master-ha/downloads/list
Every MySQL server need to have a copy of the MHA node installed.
# yum install perl-DBD-MySQL.x86_64 # rpm -i mha4mysql-node-0.52-0.noarch.rpm
You can run the Manager on any server. It doesn’t have to be a MySQL server.
# yum install perl-Config-Tiny.noarch # yum install perl-Parallel-ForkManager.noarch # rpm -i mha4mysql-manager-0.52-0.noarch.rpm
Because I installed on a x64 version (as you should be) I found I had to copy the MHA libraries to the perl lib64 space. You might want to make simlinks instead.
# cp -crp /usr/lib/perl5/vendor_perl/MHA /usr/lib64/perl5/vendor_perl
You only need one configuration file if you are not going to monitor multiple Master/Slave clusters. You can name this configuration file anything you want. Change these setting and make sure the directories exist and are writable.
# vi /etc/MHA.cnf [server default] user=hauser password=P@ssw0rd manager_workdir=/var/log/masterha manager_log=/var/log/masterha/MHA.log remote_workdir=/var/log/masterha [server1] hostname=db1 [server2] hostname=db2
The MHA will need access to each server so you need to add the user to each.
mysql> grant all on *.* to 'hauser'@'192.168.0.%' identified by 'P@ssw0rd';
Now is a good time to make sure all the server names resolve on each system.
MHA also need ssh access to each server. You need to create ssh keys and copy to the mysql servers. Don’t do this if you already have ssh keys. If you have no keys this should work. Just press enter when you are ask for something.
# ssh-keygen -t dsa -f ~/.ssh/id_dsa -N "" # cp ~/.ssh/id_dsa.pub ~/.ssh/authorized_keys # scp -r ~/.ssh db1: # scp -r ~/.ssh db2:
You can check if you have ssl working with the masterha_check_ssh tool. Look for the OK after the connect test.
# masterha_check_ssh --conf=/etc/MHA.cnf
Thu Dec 1 10:10:52 2011 - [info] Starting SSH connection tests..
Thu Dec 1 10:10:53 2011 - [debug]
Thu Dec 1 10:10:52 2011 - [debug] Connecting via SSH from root@db1(192.168.0.11) to root@db2(192.168.2.12)..
Thu Dec 1 10:10:53 2011 - [debug] ok.
MHA need to control replication so you should test it with masterha_check_repl. This will outout a lot of chat. Your looking for the ‘MySQL Replication Health is OK’ at the end. You might also pay attention to any warnings.
# masterha_check_repl --conf=/etc/MHA.cnf
---
Thu Dec 1 10:07:22 2011 - [info] Checking slave configurations..
Thu Dec 1 10:07:22 2011 - [warning] read_only=1 is not set on slave db2(192.168.0.11:3306).
Thu Dec 1 10:07:22 2011 - [warning] relay_log_purge=0 is not set on slave db2(192.168.0.12:3306).
---
MySQL Replication Health is OK.
You now have MHA installed. If you configure it wrong MHA will give some errors that are not helpful and die. Check your settings again.
If you want MHA to monitor the status of your master server you need to run masterha_manager. It does not normally run as a daemon. If masterha_manage is not running your server will not automatically fail over. This is a weakness in MHA. You might want to use something like Pacemaker(Heartbeat) to monitor your systems and MHA to do the fail over. Here is how I make MHA run in the background.
# nohup masterha_manager --conf=/etc/MHA.cnf &;
You can check masterha_manager with masterha_check_status.
# masterha_check_status --conf=/etc/MHA.cnf
Now the magic. If you want to manual fail over you can use masterha_master_switch and tell it the master is alive.
# masterha_master_switch --master_state=alive --conf=/etc/MHA.cnf
If your using another monitoring system like Pacemaker you can have it run this command to automatically fail your master.
# masterha_master_switch --master_state=dead --dead_master_host=db1 --conf=/etc/MHA.cnf
If you find I’ve missed something or it works different on your OS please drop me a comment.
In last weeks OurSQL postcast (episode 65) Sheeri, Sarah and Jerry talked about making MySQL safe with SSL. Encryption always seems to be such a confusing subject. I think every database should be using SSL by default. So, I was wondering just how easily SSL could be setup.
Most existing examples I found setup SSL authentication and encryption. If you are handling PCI DSS or HIPAA data you must encrypt the data on the wire, but passwords are all you need to authenticate the application to the data source.
# mkdir /etc/mysql/certs # cd /etc/mysql/certs
This looks complicated, it’s not. JUST PRESS ENTER when openssl ask you a question. This will not make you any less secure. Your data will be encrypted. Note these keys will expire in three years (1095 days).
# openssl genrsa 2048 > ca-key.pem # openssl req -new -x509 -nodes -days 1095 -key ca-key.pem -out ca-cert.pem # openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server-key.pem -out server-req.pem # openssl rsa -in server-key.pem -out server-key.pem # openssl x509 -req -in server-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem \ -set_serial 01 -out server-cert.pem
With the keys generated you need to tell MySQL to use them. Add these lines to your my.cnf.
# vi /etc/my.cnf
ssl ssl-cipher=DHE-RSA-AES256-SHA ssl-ca=/etc/mysql/certs/ca-cert.pem ssl-cert=/etc/mysql/certs/server-cert.pem ssl-key=/etc/mysql/certs/server-key.pem
Now restart mysql.
# service mysql restart
You should see SSL is enabled and mysql sees the keys.
mysql> show variables like '%ssl%'; +---------------+----------------------------------+ | Variable_name | Value | +---------------+----------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/certs/ca-cert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/certs/server-cert.pem | | ssl_cipher | DHE-RSA-AES256-SHA | | ssl_key | /etc/mysql/certs/server-key.pem | +---------------+----------------------------------+ 7 rows in set (0.00 sec)
Duplicate these keys and the configuration segment to all your servers.
Client
If the client system is not a server, copy of the MySQL SSL keys to it was well. Then, you need to tell the client to use SSL. Edit your user’s .my.cnf file and give it the keys too.
# vi ~/.my.cnf
[client] ssl ssl-cipher=DHE-RSA-AES256-SHA ssl-ca=/etc/mysql/certs/ca-cert.pem
Thats it. It should be working. Look for ‘Cipher in use’.
# mysql -e "\s"
mysql Ver 14.14 Distrib 5.1.59, for unknown-linux-gnu (x86_64) using readline 5.1
Connection id: 7
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
......
Threads: 1 Questions: 22 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 2.0
Replication
On the master, you need to tell the replication user to require SSL connections. Replace the rep_user with your replication user’s ID.
mysql> GRANT USAGE ON *.* TO 'rep_user'@'%' REQUIRE SSL;
mysql> flush privileges;
and on the slave tell it to connect to the master with SSL. Then make sure you are still connecting. Change the IP and user name, show here, to your settings.
mysql> stop slave; mysql> CHANGE MASTER TO master_host='192.168.1.12', master_user='rep_user', \ MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem' ; mysql> start slave; mysql> show slave status;
Applications
Your application could be written in lots of different languages and I can’t go over each of them but here are some links to setup up SSL connection to MySQL in several of the most popular. If you know where there are some better examples, please leave me a comment.
I ask Sheeri of the OurSQL podcast to publish her list of MySQL websites and podcasts she follows. I thought it was only fair I do the same. If you don’t watch these all the time your missing out. I read most of these every week.
If I missed one please leave a comment.
Planet MySQL – http://planet.mysql.com/
OurMySQL - http://www.oursqlcast.com/
Sergey Petrunia’s blog – http://s.petrunia.net/blog/
clever elephant – http://blog.cleverelephant.ca/
MYSQL PREACHER – http://mysqlpreacher.com/wordpress/
MySQL Soapbox - http://mysqlsoapbox.blogspot.com/
mySQL DBA – http://mysqldba.blogspot.com/
DBA DOJO – http://blog.dbadojo.com/
MySQL-dump – http://mysqldump.azundris.com/
/dev/random – http://blogs.sakienvirotech.com/
Ranald Bradford – http://ronaldbradford.com/
Dave’s Stuff – http://dave-stokes.blogspot.com/
PalominoDB – http://palominodb.com/
code.openark.org – http://code.openark.org/blog/
xarg.org – http://www.xarg.org/
[ themattreid ] – http://themattreid.com/wordpress/
domas mituzas – http://dom.as/
Database Administrators Knowledge Blog – http://dbperf.wordpress.com/
High Scalability – http://highscalability.com/
Karlsson on databases and stuff – http://karlssonondatabases.blogspot.com/
The Data Charmer – http://datacharmer.blogspot.com/
Pablowe – http://www.pablowe.net/
MySQL DBA Help – http://www.mysqldbahelp.com/
MySQL Performance Blog – http://www.mysqlperformanceblog.com/
All the resources must be in place before you configure Pacemaker. The default location for MySQL data is /var/lib/mysql. You will be moving this to the DRBD file system in /data/mysql. The default MySQL configuration is in /etc/my.cnf. I move it to /data/mysql so updates will move with system fail overs. To keep life simple I use sym-links to point from /var/lib/mysql and /etc/my.cnf to their new locations.
Install MySQL of your choise on DB1, DB2 and DB3.
# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm # yum -y install Percona-Server-server-51.x86_64 Percona-Server-client-51.x86_64 \ Percona-Server-shared-51.x86_64 xtrabackup.x86_64 # service mysql start # mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'" # mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'" # mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'" # service mysql stop # chkconfig mysql off
ON DB1, move MySQL data to the DRBD file system.
# df
Filesystem 1K-blocks Used Available Use% Mounted on /dev/md2 11903664 1633608 9655620 15% / /dev/md1 505508 218159 261250 46% /tmp /dev/md0 256586 25967 217371 11% /boot tmpfs 2037412 16348 2021064 1% /dev/shm /dev/drbd1 30477552 176200 28753152 1% /data
On DB1 and DB3
# mkdir /data/mysql # cp -prv /var/lib/mysql/* /data/mysql # mv /var/lib/mysql /var/lib/mysql-empty # ln -s /data/mysql /var/lib/mysql # touch /data/mysql/my.cnf # ln -sf /data/mysql/my.cnf /etc/my.cnf # chown -R mysql.mysql /data/mysql # chmod 644 /data/mysql/my.cnf
On DB2, make it ready for a fail over.
# mv /var/lib/mysql /var/lib/mysql-empty # ln -sf /data/mysql /var/lib/mysql # ln -sf /data/mysql/my.cnf /etc/my.cnf
I have a documented my.cnf file I start with. You need to change the server-id number on each server. The minimum setting you will need are:
[mysqld] log-bin=mysql-bin server-id=1 innodb_flush_log_at_trx_commit=2 sync_binlog=1
on DB1
# service mysql start
Now we need to add a couple of user before we install the replicator. We also need to set a password for the root user. The root password is blank so just it return.
If you have skip-name-resolve set you will need to substitute the host names for IP addresses.
# mysql -h localhost -u root -p mysql> DROP USER ''@'db.grennan.com'; mysql> DROP USER ''@'localhost'; mysql> GRANT ALL on *.* TO 'root'@'%' IDENTIFIED by 'P@ssw0rd' with GRANT option; mysql> GRANT ALL on *.* TO 'root'@'localhost' IDENTIFIED by 'P@ssw0rd' with GRANT option; mysql> GRANT ALL ON *.* TO 'tungsten'@'127.0.0.1' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION; mysql> GRANT ALL ON *.* TO 'tungsten'@'localhost' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION; mysql> GRANT ALL ON *.* TO 'tungsten'@'db.grennan.com' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION; mysql> GRANT ALL ON *.* TO 'tungsten'@'db2.grennan.com' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION; mysql> GRANT ALL ON *.* TO 'tungsten'@'db3.grennan.com' IDENTIFIED BY 'P@ssw0rd' WITH GRANT OPTION; mysql> flush privileges;
Now that we can access MySQL from any of the servers we need to prepare each as a master and replication. We can fix DB1 and then copy all of the data tables to the other servers.
mysql> reset master; reset slave; mysql> exit # service mysql stop # rsync -rog --delete /data/mysql root@db2:/data # rsync -rog --delete /data/mysql root@db3:/data
Remember we put the my.cnf file in the /data/mysql directory. We need to make sure each has a unique server_id number.
# vi /etc/my.cnf server_id = 1 # ssh db2 # vi /etc/my.cnf server_id = 2 # exit # ssh db3 server_id = 3 # exit
Rather than type the password for MySQL each type we connect. We can also setup a private my.cnf to prevent this. This should connect you to the master (RW) server from each host.
# vi ~/.my.cnf
[Client] user=root password=P@ssw0rd host=db.grennan.com socket=/data/mysql/mysql.sock
# scp .my.cnf db2:. # scp .my.cnf db3:.
Next up, Configuring Heartbeat and making failover work!
I’ve never objected to someone making money from MySQL. I’ve only expressed disappointment that they weren’t doing it effectively enough. As I have predicted many times, Oracle is good at this. Oracle is the number one reason I didn’t start a new career in some other database a few years ago. Oracle is making MySQL more successful not only for Oracle, but also for the users, the community, and the competition.
I am glad that Oracle is offering more pay-only extensions to the server in a way that creates opportunities for others to do the same, and I look forward to even more of them in the future.
Further Reading:
Installing DRDB in CentOS 5.6.
In Part 1 I when through the process of preparing a number of CentOS 5.6 servers. Now make the services they’ll preform more stable.
I’ll be presenting two ways to provide redundant data and high available services. First, Pacemaker – with DRDB will duplicate your data at the disk partition level and watch for failures. Should the hardware failure, Pacemaker will take all the needed steps to start MySQL on the Hot Stand By (HSB). This is not perfect. Should someone run ‘rm *’ or drop a database DRDB will duplicate the loss on the HSB.
In another part, I’ll use Tungsten replicator. It offers a set of features that surpass the built-in MySQL replicator. The community version of Tungsten has global transaction IDs so even with many slaves, global transactions IDs make turning a slave into the master easy. Tungsten replicator is not a HA service. You have to manually fail to a new master. Tungsten Enterprise (if you have money) solves all the issues. With the tools the enterprise version supplies you can easily migrate the “master” server to any slave.
Neither RedHat or CentOS supply PaceMaker packages. Redhat support their own propitiatory clustering suite. CentOS is suck trying to maintain compatibility with Redhat while still giving you a high availability system. CentOS does supply heartbeat and openais but not pacemaker. Thankful Redhat helps out by supporting the Fedora project and in turn Fedora provide an EPEL repository for Redhat 5.
The Pacemaker packages in Fedora’s EPEL directories are built against some additional packages that don’t exist on vanilla RHEL/CentOS installs. For more information on EPEL, see http://fedoraproject.org/wiki/EPEL/FAQ So before installing Pacemaker, you will first need to tell the machine how to find the EPEL packages Pacemaker depends on. To do this, download and install the EPEL package that matches your RHEL/CentOS version.
LINBIT is the primary maintainer of DRBD and offers a product and service portfolio for exactly what we are building here. They have produced a video that takes you through this same process using the the DRDB Console Manager. I’m going to take you through the same process by hand. I hope this way you will better understand the management touch points.
I build two computer alike in every way (clones) and use DRBD to sync the data partitions on each. CentOS provides the packages needed.
# wget http://www.clusterlabs.org/rpm/epel-5/clusterlabs.repo # mv clusterlabs.repo /etc/yum.repos.d # rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm # yum -y install pacemaker.x86_64 heartbeat.x86_64 drbd83.x86_64 kmod-drbd83.x86_64 # /sbin/chkconfig --add heartbeat # depmod # modprobe drbd
On both machines (db1 and db2), edit the DRBD configuration file. The host names must be the save as returned by ‘hostname’ command. You may also need to edit the host name in /etc/sysconfig/network. I have highlighted parts you will need to edit in red.
To replace the shared-secret, select at least half of the characters from the “63 random alpha-numeric characters” from Gibson Research Ultra High Security Passwords.
# vi /etc/drbd.conf
include "drbd.d/global.conf"; include "drbd.d/*.res";
# vi /etc/drbd.d/global.conf
global { usage-count yes; }
common { startup { degr-wfc-timeout 0; }
net { cram-hmac-alg sha1; shared-secret R4x2alEkxtIg2kzbXqUL6l4uoTI7Ab7Qt; }
disk { on-io-error detach; } }
# hostname
db1.grennan.com
# vi /etc/drbd.d/db.res
resource db { protocol C; syncer { rate 10M; }
on db1.grennan.com { device /dev/drbd0; disk /dev/md3; address 192.168.4.1:7788; flexible-meta-disk internal; }
on db2.grennan.com { device /dev/drbd0; disk /dev/md3; address 192.168.4.2:7788; flexible-meta-disk internal; } }
# scp -r /etc/drbd.d db2:/etc # scp -r /etc/drbd.conf db2:/etc
If the disk was formatted during the OS install you may need to erase the ext3 file system info on both DB1 and DB2.
# umount /data # dd if=/dev/zero of=/dev/md3 count=2048
Write the DRBD meta data on both DB1 and DB2.
# drbdadm create-md db
On >>> DB1 only <<<
# drbdadm adjust db # drbdsetup /dev/drbd0 primary -o # service drbd start
On DB2
# service drbd start
Did you miss the >>> DB1 only <<< above? If you did you’ll need to re-run ‘drbdsetup /dev/drbd0 primary -o’ on DB1.
WAIT until the sync process completes.
Back on DB1 create the file system and mount it.
# mkdir /data # mkfs -j /dev/drbd0 # tune2fs -c -1 -i 0 /dev/drbd0 # mkdir /data # mount -o rw /dev/drbd0 /data
Don’t forget to make the /data directory on DB2.
# mkdir /data/mysql # cat /proc/drbd
version: 8.3.8 (api:88/proto:86-94) GIT-hash: d78846e52224fd00562f7c225bcc25b2d422321d build by mockbuild@builder10.centos.org, 2010-06-04 08:04:09
1: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r---- ns:2433920 nr:0 dw:0 dr:2433920 al:0 bm:148 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:28530088 [==>.................] sync'ed: 21.9% (27860/30236)M delay_probe: 757 finish: 4:57:11 speed: 1,248 (6,404) K/sec
In the next post I’ll go through installing MySQL in preparation for configuring Pacemaker. Then I’ll show you how to test fail over.
In the next part I will go over setting up MySQL.
It’s notoriously hard to measure the usage of open-source software. Software that’s open-source or free can be redistributed far and wide, so the original creators have no idea how many times it’s installed, deployed, or distributed. As a proxy, we often use downloads, but that’s woefully inadequate.
I’ve recently begun trying to figure out how many job openings are mentioning various open-source projects. I think that this might be a better metric because it’s driven by the end result (usage), rather than intermediate processes (downloads, etc). I think that it’s likely that usage and demand for skilled people is somewhat realistically related.
To be more concrete, I’ve been watching RSS feeds from job posting aggregators for several alternative versions of MySQL: Percona Server, MariaDB, and Drizzle. It appears that Percona Server is by far the most in-demand in terms of job skills. (I haven’t seen a job posting for the others at all, so far.)
On the other hand, my sample is skewed; I think Percona Server is better known in America, but MariaDB might be more visible in Europe. And I’m not sure that the sample data set is large enough to be statistically significant. Percona Server jobs are utterly dwarfed by MySQL jobs.
There are other flaws in my method: some software doesn’t really need as much manpower to run as others. I would say that given an equal number of WordPress and Drupal websites, more of the Drupal websites are going to be trying to hire experts to manage their sites. So nothing is apples to apples.
What do you think about this metric and its merits or drawbacks? Is there a better way to figure out how much adoption a project really has?
Related posts:
People keep asking me “what is going to happen to MySQL now that Oracle has screwed MySQL?” I bluntly disagree that any such thing has happened. This blog post is just my personal view and does not reflect my employer’s opinion, but Oracle might have saved MySQL from what I can see. There is no evidence that supports the hysterical doomsday theories. (Witness MySQL 5.5, probably the best MySQL release in history. Not exactly what I’d call “screwing.”)
I believe that a product with such a large, diverse, and important market presence needs a variety of companies involved with it in many different ways. One of the absolutely key things is a company to make money from it. MySQL needs Oracle, because no one else involved is both capable and trying to make MySQL, the product, a large-scale commercial success. It looks like Oracle is doing what I wish Sun could have done.
Now, is Oracle going to be community friendly, and hold all our hands in a circle while singing songs and accepting our patches? No, of course not, and what an opportunity that creates for someone else. I have no issue with Oracle ignoring the community because they have decided it doesn’t fit into their plans. If you go to the hardware store to buy bread, you won’t find any. Bake a loaf if you want to eat.
Related posts:
There have been a few great blog posts recently from MySQL bloggers about Postgres, and vice versa, with good comments and follow-on from the real experts in both systems. I think this is wonderful. Learning how other databases solve hard problems is highly educational, especially because ACID databases face some of the hardest problems in computing. Making MySQL better is good for PostgreSQL. The reverse is just as true. And we should also be learning from SQLite, and CouchDB, and others who have overcome tough technical hurdles, built successful companies, created thriving and enthusiastic communities, or whatever their success has been.
Related posts: