Setting up Replication in MySQL of 3+ cluster nodes.

I've been running a multinode MySQL Replication Loop for a while and thought it'd be useful to write up how replication is synced between the peers. I'm writing this up as if there are three nodes, Adam, Ben and Charlie in a loop, but you can do this with any number of nodes. In our setup Adam and Ben are in our primary location, with Charlie sitting in our DR setup. This means Charlie is always on but doesn't get many queries on a regular basis. Adam and Ben have heartbeatd setup as a failover pair so that when Adam has a vacation (downtime) everything fails over to Ben and continues to run. We do this simply with a floating IP.

1. Decide which node to start with

Because of this setup, prior to doing a resync, it's important to connect to the node who currently has the active IP and start things from there. To find out who that is, connect to the nodes and run:

$ /sbin/ip addr

You'll see something like:

: eth0:  mtu 1500 qdisc pfifo_fast qlen 1000
    link/ether 00:50:56:82:29:01 brd ff:ff:ff:ff:ff:ff
    inet 192.168.1.56/24 brd 192.168.1.255 scope global eth0
    inet 192.168.1.59/24 brd 192.168.1.255 scope global secondary eth0

Note how this one has both the primary (eth0) and "secondary eth0" address on it. Also, we know our floating IP is 192.167.1.59, so we know it's here. Because of that, this node has the latest info and will thus be the beginning of our resync process.

2. Stop the Slaves

To stop the slaves, connect to mysql as follows:

$ mysql -p -u root

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

I recommend going ahead and stopping the slave on both Adam and Charlie as well at this point.

3. Get a fresh mysqldump

To start the resync process, we need to take a dump of all databases with masterdata in a single-transation:

/bin/mysqldump -p -u root --all-databases --single-transaction --master-data | bzip2 > 20120912-midsync-`hostname`.sql.bz2

This will make sure that we get all the info we need in the backup. Without Master Data we'd have to remember when we took the backup when we restored it in order to not miss any updates, which is almost impossible when running this against an active loop. When this is done, transfer the backup from Adam to Ben. Then, stop the slaves around the circle.

4. Transfer dump to the next node in the circle

You can use scp or what ever you'd like. just get the backup over there.

5. Import the dump

Then, on the first target (Ben in this case), bunzip2 the file, and then import it:

$ mysql -p -u root < 20120912-midsync-adam.sql

Because we included Master Data, this will automatically set the master position to be correct to resume the slave... but don't do it yet.

6. Rinse, repeat

We're going to continue around the circle making backups of each, passing them onto the next and then restoring. in our case, we need a fresh backup of Ben, same way we did the original Adam backup. Transfer that one to Charlie. bunzip2 it, and import it with the MySQL command. If you have more nodes, you'll continue this around until all have fresh restores.

If you have multiple primary or online nodes, you'll want to 'start slave;' right after you import the backup. This will make sure they don't have stale data or data that will conflict with another node's data.

For us, we're going to take advantage of the fact that Charlie is in our DR site and thus doesn't have much written to it, but still we'll need to do this quickly.

7. The Last Hop

Okay, you've got all of the up to date, but we haven't made that last hop where 0 pulls from N. Instead of the normal restore process, we're going to switch it up a bit:

$ ls
20120912-BenBackup.sql.bz2
$ bunzip 20120912-BenBackup.sql.bz2
$ echo "reset master;" >> 20120912-BenBackup.sql
$ mysqp -u root -p < 20120912-BenBackup.sql

Adding on 'reset master' will reset the master files and counter. Now, bring up both this node's terminal and Adam's terminal up next to each other. Login to Adam's MySQL prompt and type in "CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=###;", but don't hit enter. On Charlie, at the MySQL prompt, type in 'show master status;' and hit enter. Now, copy the file name in the first data column over to your command on Adam. Then, move your cursor to the ###'s, and run the 'show master status' command again copying the number over and hitting enter as quick as you can accurately.

8. Gentlemen, start your slaves!

Once you have it set, run "start slave" on Adam, then on Charlie, and finally on Ben. It's important that this number matches.

9. Check your status

When the slave is started, it pulls any changes since the backups' restore (or master reset) from the node behind it (Charlie pulls from Ben, for instance). By working backward, we have a better chance of the replication circle staying in sync and once they're all up, you're almost done. Give it a minute and run "Show Slave Status\G" on each. You should see something similar to this:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.57
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: ben-bin.000022
          Read_Master_Log_Pos: 888045912
               Relay_Log_File: charlie-relay-bin.000002
                Relay_Log_Pos: 81957
        Relay_Master_Log_File: ben-bin.000022
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 888045912
              Relay_Log_Space: 82121
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

Puppet: Exiting; no certificate found and waitforcert is disabled

I have a number of servers that were built using puppet. They contact a central puppet master and pull configs. This had been working quite well for a while. The I noticed that they suddenly have been silently failing to do any updates. I then tried this manually:

# puppet agent --test
Exiting; no certificate found and waitforcert is disabled

Well, that's not too useful. Other puppet slaves are running, and the puppet master doesn't have a full disk or anything. Then I noticed the following:

ls -al /var/lib/puppet/ssl/certificate_requests/
-rw-r----- 1 puppet puppet 1610 Jan 17  2013 hostname.example.net.pem

Weird, why was there a request for this? Not sure. But doing a quick rm of that file and then re-running "puppet agent --test" made puppet create a new cert and submit it to the master. I then ran "puppet cert --sign --all" and it's good to go! So, not sure about the root cause yet, but this solution helped me out and I wanted to share.

MySQL 5.6.x Admin Password

So, I'm rolling out a fresh mysql server the other day, as I've done many times before, and ran into some odd behaviour. I downloaded a fresh set of RPM's from oracle's download page and did my usual:

# yum install MySQL-*-5.6.11*.rpm -y
# mysql_install_db
# mysql_secure_installation

And then I got the root password prompt. Now usually you just hit enter because the root password is blank. But it wasn't taking that today. Wiped the DB, erased and reinstalled, still didn't take the empty password. Until I did:

# ls -al
total 366900
dr-xr-x---.  4 root root      4096 May 20 12:08 .
dr-xr-xr-x. 24 root root      4096 May 20 11:26 ..
-rw-r--r--.  1 root root  23010735 May 20 11:07 MySQL-client-5.6.11-2.linux_glibc2.5.x86_64.rpm
-rw-r--r--.  1 root root   4554269 May 20 11:07 MySQL-devel-5.6.11-2.linux_glibc2.5.x86_64.rpm
-rw-r--r--.  1 root root 112519557 May 20 11:08 MySQL-embedded-5.6.11-2.linux_glibc2.5.x86_64.rpm
-rw-------.  1 root root       192 May 20 12:00 .mysql_secret
-rw-r--r--.  1 root root  56354288 May 20 11:09 MySQL-server-5.6.11-2.el6.x86_64.rpm
-rw-r--r--.  1 root root  88319899 May 20 11:10 MySQL-server-5.6.11-2.linux_glibc2.5.x86_64.rpm
-rw-r--r--.  1 root root   2389748 May 20 11:10 MySQL-shared-5.6.11-2.linux_glibc2.5.x86_64.rpm
-rw-r--r--.  1 root root   5180812 May 20 11:10 MySQL-shared-compat-5.6.11-2.linux_glibc2.5.x86_64.rpm
-rw-r--r--.  1 root root  72675691 May 20 11:11 MySQL-test-5.6.11-2.linux_glibc2.5.x86_64.rpm
#

Wait, what's that ".mysql_secret" file?! I didn't put that there. Apparently it did... Turns out, upon initial install of the MySQL Packages, it runs an the mysql_install_db automatically... and then it sets the password to something random.

# cat .mysql_secret

# The random password set for the root user at Mon May 20 12:00:16 2013 (local time): mS2tzW4Z

So, you'd think that you can still run mysql_secure_installation, but you can use that password. Except that you end up getting the message "ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords." Which is apparently the normal text client. So, feel free to reset your password the old fashion way:

# mysql -u root -pmS2tzW4Z

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.11

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('ub3rS3cureP455word!');
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Then you can run mysql_secure_installation and enjoy your new MySQL Install.

Nginx + FastCGI with the Quickness

CentOS 6.x (I've got 6.3 here) install. first off, you'll need both nginx and spawn-fcgi as well as php. For purposes of simplicity, I'll just go with the 5.3.3 that yum pulls in, but really any version (I ran it with php 5.4.8 for this example) will work. As long as it's compiled with the -cgi flags. and you have 'php-cgi' available as that's what spawn-fcgi executes.

yum install nginx spawn-fcgi php -y

This will install both of them with their default config's. You'll need to tweak a few things. First off, let's tackle /etc/sysconfig/spawn-fcgi

Spawn-fcgi Config

SOCKET=/var/run/php-fcgi.sock
OPTIONS="-u nginx -g nginx -s $SOCKET -S -M 0600 -C 8 -F 1 -P /var/run/spawn-fcgi.pid -- /usr/bin/php-cgi"

By default this ships with -C 32, which means it'll start 32 php-cgi processes. This seems like a lot in my experience. We have some very busy image servers and they do well with 4 to 8. I usually go with the "# cores + 2" idea and it's worked well for me so far. Any way, you'll also want to make sure you remember where that 'Socket' is defined. It doesn't really matter where it is, but it matters that you remember it!

Nginx Config

server {
  listen  80;
  server_name zabbix.example.com;
  root   /var/www/zabbix;
 
  location / {
   index  index.html index.htm index.php;
  }
 
  location ~ \.php$ {
   include /etc/nginx/fastcgi.conf;
   fastcgi_pass unix:/var/run/php-fcgi.sock;
   fastcgi_index index.php;
  }
 }

This server block will go in either your main nginx.conf file (/etc/nginx/nginx.conf on CentOS), or in a file included from that one. This will define a vhost listening on that "server_name", hosted in that "root". It will use the info in /etc/nginx/fastcgi.conf and pass that info over to your socket defined above (I told you to remember that!). Basically, the second location block tells nginx that any file ending with .php should use the fast-cgi and php socket to run. ... and that's it! I highly recommend trolling through the php.ini options as well as any other options in nginx to make sure there aren't any red-flags flying (I know I've tweaked a lot outside of this) but this should get you serving php!

Puppet Error: header too long

If you're working with Puppet and you find that you get this error:

puppet cert --list

Error: header too long

Be mindful of your free space! I've now rolled out 20 servers or so in my puppet setup (soon to be duplicated to over 142 servers once I get these running right. All I'll have to do is spin up a new server, give it an IP and hostname and tell it where the Puppet Master is and Puppet will handle the rest!), and I've found that I'm starting to easily fill up the drive with old reports. Especially when re-running puppet syncs more frequently than the normal 30 min run-interval. I started getting the above error with a lot of various puppet commands, the simplest one, just trying to list certs. Then I checked a "df -h":

# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              16G   15G     0 100% /

Oops! Using the following script I was able to clean up old reports easily. Set the "days" variable to as high as you want for your setup. I'm using Puppet Dashboard to pull in reports to a DB, so I don't need to keep the yaml's around too long.

#!/bin/sh
days="+1"       # more than a day old

for d in `find /var/lib/puppet/reports -mindepth 1 -maxdepth 1 -type d`
do
        find $d -type f -name \*.yaml -mtime $days |
        sort -r |
        tail -n +2 |
        xargs /bin/rm -f
done

In my case, since it tried to sync a new server ssl cert while the drive was full, the error came out to be due to not only the free space, but a corrupt cert. To find the offending cert and fix the issue, you'll need to look through the /var/lib/puppet dir for the file. The host I was looking for is 'betamem.example.com' and I found it like this:

# cd /var/lib/puppet
# find ./|grep betamem
./ssl/ca/requests/betamem.example.com

I then removed the cert (held in /var/lib/puppet/ssl/certificate_requests/) from the agent on 'betamem' and told it to try again by cycling it's puppet agent.

# rm -f /var/lib/puppet/ssl/certificate_requests/*
# /etc/init.d/puppet restart
Stopping puppet agent:                                     [  OK  ]
Starting puppet agent:                                     [  OK  ]

Tailing /var/log/messages on the master shows it's got a new request, so let's sign it:

# tail /var/log/messages -n1
puppet-master[22486]: betamem.example.com has a waiting certificate request
# puppet cert --sign betamem.example.com
Signed certificate request for betamem.example.com
Removing file Puppet::SSL::CertificateRequest at '/var/lib/puppet/ssl/ca/requests/betamem.example.com.pem'

Go back to the puppet agent and cycle it again, or just wait until the next run-interval and it should be back to normal!

Error Performing Checksum

Getting something like this when running an update from your repo?

MyRepo/primary http://repo.example.com/repo/CentOS/5.8/x86_64/repodata/primary.xml.gz: [Errno -3] Error performing checksum

Well, it seems CentOS 5.x requires sha, not sha256. So when you run createrepo, make sure to change it to 'createrepo --checksum=sum' instead.

Trac: Fixing the dreaded "Duplicate entry" Error during SVN Rescan

So, The other day I was minding my own business, resyncing an SVN repository in trac and I ran across this error:

File "/usr/bin/trac-admin", line 7, in ? sys.exit(
  File "/usr/lib/python2.4/site-packages/Trac-0.11.6-py2.4.egg/trac/admin/console.py", line 1325, in run admin.run()
  File "/usr/lib/python2.4/site-packages/Trac-0.11.6-py2.4.egg/trac/admin/console.py", line 155, in run self.cmdloop()
  File "/usr/lib64/python2.4/cmd.py", line 142, in cmdloop stop = self.onecmd(line)
  File "/usr/lib/python2.4/site-packages/Trac-0.11.6-py2.4.egg/trac/admin/console.py", line 138, in onecmd rv = cmd.Cmd.onecmd(self, line) or 0
  File "/usr/lib64/python2.4/cmd.py", line 219, in onecmd return func(arg)
  File "/usr/lib/python2.4/site-packages/Trac-0.11.6-py2.4.egg/trac/admin/console.py", line 680, in do_resync repos = env.get_repository().sync(self._resync_feedback)
  File "/usr/lib/python2.4/site-packages/Trac-0.11.6-py2.4.egg/trac/versioncontrol/cache.py", line 214, in sync (str(next_youngest),
  File "/usr/lib/python2.4/site-packages/Trac-0.11.6-py2.4.egg/trac/db/util.py", line 64, in execute return self.cursor.execute(sql_escape_percent(sql), args)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/cursors.py", line 163, in execute    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.4/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler raise errorclass, errorvalue
_mysql_exceptions.IntegrityError: (1062, "Duplicate entry '37295-releases/may0712-D' for key 'PRIMARY'")

How did we get here?!

As background, this is a copy of a repository we've been working with for a while. We're copying it to a new repository as we have two sets of files in it that were originally connected projects but have grown farther and farther apart to the point where they don't really reference each other or need to be together. At one point we had a few people who somehow checked in duplicate deletions of the same file in the original repository. (How that got past SVN is another story, but it wasn't anything out of the ordinary). In any case, this isn't the first time I've run across this error and since we made a copy of the repo that had that issue, we now have it here.

Now, if this was the very last revision and the repo was quiescent, you could do a little clever hacking on the svn repo to remove the duplication... but that's not possible to do here. No, instead we're going to get MySQL Crafty.

Time for Mysql Sluthing

First off, I open up my trusty commandline mysql client and pick my database. Looking at the list of tables you'll see the "revision" and "node_change" tables which look like this:

mysql> describe revision;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| rev     | text    | NO   | PRI | NULL    |       |
| time    | int(11) | YES  | MUL | NULL    |       |
| author  | text    | YES  |     | NULL    |       |
| message | text    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> describe node_change;
+-------------+------+------+-----+---------+-------+
| Field       | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| rev         | text | NO   | PRI | NULL    |       |
| path        | text | NO   | PRI | NULL    |       |
| node_type   | text | YES  |     | NULL    |       |
| change_type | text | NO   | PRI | NULL    |       |
| base_path   | text | YES  |     | NULL    |       |
| base_rev    | text | YES  |     | NULL    |       |
+-------------+------+------+-----+---------+-------+

A Plan Comes Together

'rev' is a Primary Key (or part of one) in both cases and, according to the message above, we're trying to insert the same primary key twice. So how are we going to fix this? If I delete the offending records, it'll rescan the repo up to that point, see that it's not there, add it, then add the second one. So, that doesn't help us at all. We need to leave it there but not let it complain the next time. If we leave it there but drop the unique requirement, it'll write it for the first rev, and then write over it again with the second rev, or (if it doesn't look for an existing record first) it'll just add the line twice. In the latter case, we can clean that up later and then re-institute the unique requirement. Sounds like a win, let's do it.

Dropping Constraints

mysql> alter table revision drop primary key;
Query OK, 37295 rows affected (0.11 sec)
Records: 37295  Duplicates: 0  Warnings: 0

mysql> alter table node_change drop primary key;
Query OK, 328916 rows affected (3.13 sec)
Records: 328916  Duplicates: 0  Warnings: 0


mysql> describe revision;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| rev     | text    | NO   |     | NULL    |       |
| time    | int(11) | YES  | MUL | NULL    |       |
| author  | text    | YES  |     | NULL    |       |
| message | text    | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> describe node_change;
+-------------+------+------+-----+---------+-------+
| Field       | Type | Null | Key | Default | Extra |
+-------------+------+------+-----+---------+-------+
| rev         | text | NO   | MUL | NULL    |       |
| path        | text | NO   |     | NULL    |       |
| node_type   | text | YES  |     | NULL    |       |
| change_type | text | NO   |     | NULL    |       |
| base_path   | text | YES  |     | NULL    |       |
| base_rev    | text | YES  |     | NULL    |       |
+-------------+------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Alright! Let's resync again... just running "tracadmin resync"... And watching it count... for a while. Eventually we'll see something like:

37309 revisions cached.
Done.

Searching for UFO's

So, now we've got all that data in the db... including the duplicate revision. We can't just add back the keys because it'll fail the constraint validation. (If you run "alter table node_change add primary key (rev(16), path(512), change_type(1));" you'll get "ERROR 1062 (23000): Duplicate entry '32152-branches/...' for key 'PRIMARY'".We're going to have to clear that extra one out first, so let's find it.

mysql> select count(*) as c,rev,path,change_type from node_change group by concat(rev, path, change_type) order by c desc limit 10;
+---+-------+---------------------------------+-------------+
| c | rev   | path                            | change_type |
+---+-------+---------------------------------+-------------+
| 2 | 37295 | releases/May0712                | D           |
| 1 | 33277 | releases/S2611/myApp/main.cpp   | E           |
| 1 | 33277 | releases/S2611/myApp            | E           |
...

Well, there she is, 'c'=2, so we've got two of the same thing, just like we thought. I put a limit of 10 instead of only pulling the top result so we could make sure that there were no other problem entries pulled in after that one during the sync. If there were, we'd have to do the following steps for each.

Dropping the Dupes

mysql> select * from node_change where rev=37295 and path='releases/May0712';
+-------+------------------+-----------+-------------+------------------+----------+
| rev   | path             | node_type | change_type | base_path        | base_rev |
+-------+------------------+-----------+-------------+------------------+----------+
| 37295 | releases/May0712 | D         | D           | releases/May0712 | 37294    |
| 37295 | releases/may0712 | D         | D           | releases/may0712 | 37294    |
+-------+------------------+-----------+-------------+------------------+----------+
2 rows in set (0.20 sec)

mysql> delete from node_change where rev=37295 and path='releases/may0712' limit 1;
Query OK, 1 row affected (0.19 sec)

mysql> select * from node_change where rev=37295 and path='releases/may0712';
+-------+------------------+-----------+-------------+------------------+----------+
| rev   | path             | node_type | change_type | base_path        | base_rev |
+-------+------------------+-----------+-------------+------------------+----------+
| 37295 | releases/may0712 | D         | D           | releases/may0712 | 37294    |
+-------+------------------+-----------+-------------+------------------+----------+
1 row in set (0.24 sec)

You can see that MySQL honors the 'limit' clause on deletes, saving us a little work here. Now we should be able to add our constraint back on.

mysql> alter table node_change add primary key (rev(16), path(512), change_type(1));
Query OK, 329329 rows affected (6.94 sec)
Records: 329329  Duplicates: 0  Warnings: 0

mysql> alter table revision add primary key (rev(16));
Query OK, 37309 rows affected (0.29 sec)
Records: 37309  Duplicates: 0  Warnings: 0

And we're done!