MySQL Log Rotation
Ok. So I just looked at two blog posts about MySQL log rotation. While not technically incorrect, I’d like to disagree with the final answer, mainly because recreating the wheel drives me crazy.
Rather than writing a custom script to do this and putting that script in cron, why not use logrotate?
We already ship fully functional log rotate scripts in the Debian MySQL packages. Here’s an example:
/var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log {
daily
rotate 7
missingok
create 640 mysql adm
compress
sharedscripts
postrotate
test -x /usr/bin/mysqladmin || exit 0
# If this fails, check debian.conf!
export HOME=/etc/mysql/my.cnf
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
# Really no mysqld or rather a missing debian-sys-maint user?
# If this occurs and is not a error please report a bug.
if ps cax | grep -q mysqld; then
exit 1
fi
else
$MYADMIN flush-logs
fi
endscript
}
This does the re-creation, flush logs and handles failure conditions gracefully. It also compresses old files. Even better – your system already has logrotate running (unless you run a really bad distro)
Now, if you aren’t on Debian, don’t just copy this verbatim, as you probably don’t have a debian-sys-maint user or an /etc/mysql/debian.cnf file. In Debian, we make this at package install time and refresh it on upgrades. It’s a system user with an autogenerated password put in a file only readable by root, that allows system scripts like this to run unattended.
Once you read up a little on logrotate, you’ll realize it’s really great at just about anything you need to do in the log rotation field. Have fun!
Vlad The Enterprising: Automatic deploying large number of MySQL Slaves
This tool allow to deploy large number of MySQL replication Slaves (and Master servers)
It is available on Ruby Forge: http://rubyforge.org/projects/vladenvironment/ The tool is based on Vlad the Deployer and is written on Ruby
mysqlprestore for parallel restores
Yesterday, I used mysqlpdump to dump 300G of data. Today, as step two of the process, I need to restore that data into the new server. Every good dump tool needs a restore tool, so I wrote
mysqlprestore, which is really just a modification of mysqlpdump that will spawn off threads and run your restore in parallel, processing an output directory from mysqlpdump.
I really need to stick it in version control or something, and I’m sure there are bugs, but it’s working for me so far. Maybe we can merge the two into a single great tool?
mysqlpdump for parallel dumps
I’m working on a project at the moment where we’re doing an upgrade and need to do the dump-and-restore method. It’s 300G, so that’s never going to be fun, but I found a tool today that helped significantly.
Multi threaded mysqldump is not an utopia any more. mysqlpdump can dump all your tables and databases in parallel so it can be much faster in systems with multiple cpu’s.
I ran mysqlpdump (with one patch I’ll send in soon to put quotes around table names) today with 16 threads on a 4 core system and did all 300G in ~3.5 hours. Additionally, since it wraps mysqldump but iterates over the tables, I got a sql file for each table, which is going to make writing a script to restore a piece of cake. It understands that I wanted to do –master-data and it had an option to gzip each sql file as it went.
All in all, I’m thrilled. kudos! And thanks for the tool.
mysql-proxy on ubuntu (and debian)
il corra walks you through building mysql-proxy for ubuntu.
il corra » mysql-proxy on ubuntu 7.04 feisty
First of all, there is not a packetized mysql-proxy for Ubuntu, so the only way to install it is to build it from the source
Which is great. But I’d like to take this opportunity to tell people that I’ve actually been working on packages for debian/ubuntu. They’re almost ready to be released into the wild (I’m waiting on an almost non-related event) If you’d like to play with the packaging stuff before then, check out http://launchpad.net/mysql-proxy
I’ll be sure to let everyone know when the packages themselves are in an APT repository.
mtstat 0.7.3
mtstat is now totally on launchpad. You can even download files.
I moved a few things around for 0.7.3. The MySQL plugins are now in mysql.mtstat instead of mtstat_mysql. (To go along with my putting the NDB/Connectors Python stuff in mysql.cluster – I’m trying to make a mysql namespace here) And I split up the mysqlqps plugin into mysqlqps, mysqlhandler and mysqlqcache. You can do multiple plugins like:
mtstat -Mmysqlqps,mysqlhandler
And you’ll get output like:
_uptime __sel__ __ins__ __del__ __upd__ _quest_|___hf__ __hnxt_ __hkey_ __rrnd_ __rnxt_
1998k 0 0 0 0 0 | 0 0 0 0 0
1998k 80 8 0 5 411 | 0 359 364 5 10144
1998k 27 8 0 6 288 | 0 240 222 1 2
1998k 86 7 0 8 531 | 0 300 411 3 10141
1998k 29 4 0 2 388 | 0 77 107 0 5062
1998k 35 1 0 2 193 | 0 24 117 1 3
1998k 100 4 0 4 704 | 0 95 356 1 665
1998k 110 7 0 24 1083 | 0 127 416 4 9470
Yoshinori pointed out a bug in the MySQLdb adapter. In the C code it passes a default port value of 3306. Of course, this means that if you don’t set the port in code but expect to pick up the port number from my.cnf, it won’t work. The patch is really, really small. The port number passed to mysql_real_connect() should be 0 – not 3306. But it might be a while before that hits. So for now, if you need to do an alternate port, you can either get a copy of MySQLdb and apply this patch:
[C] — _mysql_connections.c.orig 2007-08-03 23:20:28.000000000 -0700
+++ _mysql_connections.c 2007-08-03 23:20:33.000000000 -0700 @@ -15,7 +15,7 @@
#endif
char *host = NULL, *user = NULL, *passwd = NULL,
*db = NULL, *unix_socket = NULL;
- uint port = 3306;
+ uint port = 0;
uint client_flag = 0;
static char *kwlist[] = { “host”, “user”, “passwd”, “db”, “port”,
“unix_socket”, “conv”, [/C]
Or you can hard code your port into mysql.mtstat.mysqlbase.
Also, I’ve turned on the bug tracker on launchpad, so please let me know if you’re having any problems.
