Shantanu Oak - September 24th, 2007

11:26 am - Backup from binlog and other tips

We had an unscheduled test of our backups last night. Point-in-time recovery using a mysqldump and binary log files worked fine (thank goodness).

Last night we had some user-generated data corruption on a production server. The database was relatively small (a few Gig), so, after stopping the database and restarting it with ––skip-networking, I imported it from the latest daily mysqldump. Although I didn’t use ––master-data for the mysqldump (we had some locking issues with that in the past) I knew the time that the mysqldump had been started.

I did a little investigation into the binary log files using mysqlbinlog

mysqlbinlog ––start-datetime=”2007-09-17 11:25:00? ––stop-datetime=”2007-09-17 11:30:00? binlog.000003 > my_tempfile

and found out the exact time I wanted to start rolling forward from. (I already knew when to stop rolling forward.) Then I ran it

mysql -p our_database < mysqlbinlog ––start-datetime=”2007-09-17 11:26:00 ––stop-datetime=”2007-09-17 20:45:00?

Checked things out, shut down mysql and restarted it normally, and we were once again off and running.

(Luckily for us, in this case I didn’t need to know an exact or down-to-the-second time to start rolling forward from. Activity was very light around the time of the mysqldump. Also, luckily, we didn’t need a starttime more precise than to-the-second. If we had needed to be more precise, I think I would have needed a mysqldump taken with the ––master-data option.)

http://oracle2mysql.wordpress.com/2007/09/18/backups-worked/


If you’re new to MySQL, you might not know that in the default configuration ‘a’='A’. Ie, string comparisons are by default case-insensitive. If this is a surprise to you, read up on Chapter 9 of the online manual, on character sets and collations.

The default character set is latin1 and the default collation is latin1_swedish_ci (’ci’ stands for ‘case-insensitive). If you don’t want ‘a’ to equal ‘A’, you can change this by setting the variables ‘character-set-server’ and ‘collation-server’ in your config file or your startup options. For example, we want to support unicode, so we use character-set-server=’utf8? in our config file. We also added the following (in the [mysqld] section) so that clients will use the right character set and collation, too: init_connect=’set names utf8; set collation_connection=utf8_bin’.

http://oracle2mysql.wordpress.com/2007/09/04/on-collations-and-character-sets/

MySQL is not so heavily-instrumented as Oracle. (Back to tuning using ratios rather than the wait interface… sigh…) But it does offer (among other tools) the slow query log. Turn it on (put ‘log_slow_queries‘ in your config file), and all queries that take longer than long_query_time seconds (also set in your config file) will be logged to a slow query log file, along with the time they took to execute. Then you can use mysqldumpslow to analyze the output. You can see, for example, which slow queries are taking the most cumulative time on your server, or are being executed most frequently. (The manual doesn’t say much about it, and mysqldumpslow ––help doesn’t give too much help, but I think mysqldumpslow -s t and mysqldumpslow -s c do that.)

If you use InnoDB, though (as we do), be aware that the “lock time” logged in the slow query log only counts time for table-level locks that are taken at the MySQL top level, not InnoDB locks taken at the storage-engine level. (The logging is done at the “top” level.) So the “lock time” in the log is pretty useless for you. (You can use innodb_lock_monitor for that, but that’s another story…)


http://oracle2mysql.wordpress.com/2007/09/05/the-slow-query-log-and-lock-time-and-innodb/


Improve Performance of MySQL

If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.

The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.

Add the following line under [mysqld]

max_connections=250

http://kb.mediatemple.net/article.php?id=711


The following two commands will let you know if query cache is being used or not.
SHOW STATUS LIKE '%qcache%';
SHOW VARIABLES LIKE "query%';

To enable query cache you have to add the following two lines in /etc/my.cnf
query-cache-type = 1
query-cache-size = 20M

http://www.databasejournal.com/features/mysql/article.php/3110171

Memcached is probably the most popular distributed caching system and it works great. I should write an article comparing performance of various caching systems some time.

http://www.danga.com/memcached/

Current Mood: happy