Monthly Archives: May 2008

MySQL and SSL

I have been setting up a few mysql servers with SSL support for replication .

I used the script provided in the the official mysql documentation  for creating the ssl certificates cause I needed to do it on more then one server and it made more sense to use it then actually creating each certificate one by one.

If you just read the documentation and create the certificate one by one you will be fine but if you use the script your CA certificate will expire after 30 days and after a month you'll be banging your head trying to find out why suddenly SSL connections don't work anymore.
You know your certificates should be valid for a year or more but why doesn't it work anymore ... running this command :

  1. openssl x509 -in cacert.pem -dates -noout

reveals it ...

notBefore=Apr 17 12:20:10 2008 GMT
notAfter=May 17 12:20:10 2008 GMT

Ah .... there you go ... just 30 days for the cacert file ... insane...
The problem was actually reported by someone else in the comments on that documentation page but I was in a hurry ( yeah right ) and didn't go that far with reading it.
Note to self: always read the comments on those pages
So if you use that script make sure you modify it to make the CA valid for more then 30 days.
This line:

  1. openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
  2. -config $DIR/openssl.cnf
  3.  

Should be something like:

  1.  
  2. openssl req -new -x509 -days 365 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem \
  3. -config $DIR/openssl.cnf
  4.  

That is if you want the CA cert to be valid for a year.

Problem transferring a mysql database with rsync

A little more then a year ago I wrote a post presenting three different methods to transfer a mysql database. The third method suggested in that post was copying the mysql database files directly from one server ( or location ) to another. This involved locking the tables with a read lock or even shutting down the mysql before the actual copy.

For my work I usually have a main system and a development system and each system have their own database so there is a need from time to time to copy the main database over the dev database but because the database is very big ( a lot of tables and some with a large size ) and not every table is changed I like using rsync to transfer only the changes especially when I'm transferring to remote locations because it saves bandwidth and is faster.

In this case where I found the problem I actually use the same mysql server to hold both main and dev database but I still use rsync to transfer just because it still is faster then cp.

So here is what I do: I stop the mysql server , run rsync -av /var/lib/mysql/main_db/ /var/lib/mysql/dev_db/ , the differences are transferred, I start the mysql server look at the dev_db and Boom! some of the tables are corrupt. The main database was fully functional before shutting down mysql , no tables were corrupt or needed a repair, and still don't need starting up mysql.

Maybe something even more interesting is that it's very likely noone was using any of the databases before mysql was shut down.

It seems that after the transfer I just have to "repair table table_name" for some of the tables in dev_db and the repair statements returns some message saying that the number of rows has changed. Of course since I don't want to go over each db and see if it actually needs a repair I chose to just repair all of them and I wrote a script for that.So I just run the bellow script after each transfer, just to make sure everything is ok:

  1.  
  2. < ?php
  3. $db_host="localhost";$db_user="root";$db_pass="";
  4. $dbname="dev_db";
  5. $db_link=mysql_connect($db_host,$db_user,$db_pass);
  6. mysql_query("use $dbname");
  7. $res=mysql_query("show tables");
  8. if(!$res)die(mysql_error());
  9. while($arr=mysql_fetch_array($res)){
  10. echo "repairing table: ${arr[0]}\n";
  11. $res2=mysql_query("repair table ${arr[0]}");
  12. while($arr2=mysql_fetch_array($res2)){
  13. print_r($arr2);
  14. }
  15. }
  16. ?>
  17.  

This script should also show you the messages returned by the repair statements. So you can see if there really was a problem. Make sure you set the correct db connection parameters and database name before you try it.

When observing this problem I was using rsync version 2.6.9 and mysql 5.0.44 on gentoo x86_64. The problem doesn't come up on every transfer and not on all tables. Could this be a problem with rsync or mysql?

I'm thinking that if this is a problem with rsync then... wow...that is a big problem. I was relying on rsync for transferring a lot of stuff ... what if it didn't transfer something and who knows what else it didn't transfer?.

If it's a mysql problem, maybe mysql doesn't update the row counts on the tables correctly before shutting down so the files were actually correctly transferred just not correctly stored by mysql. If the row count is the only problem here then it's not such a bit problem. I'm hopping this is the case ...

I wonder if this problem would show up when using something like cp for the transfer. If that would happen then it's clearly a mysql problem but I cannot test with cp at the moment as my db is very large and that means I would have to keep the tables locked too much which is just not an option on a system that was just "promoted" to production.

I'll come back with another post once I find out more about this problem but until then just make sure to check your tables after the transfer if you are using something like rsync to transfer the files directly.