The \G modifier in the MySQL command line client
A little publicized, but exceedingly useful feature of the MySQL command line client is the \G modifier. It formats the query output nicely, so you can read through it easier. To use it, you just replace the semi-colon at the end of the query with ‘\G’.
For example, checking the master status:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000193 | 7061 | | | +------------------+----------+--------------+------------------+ 1 row IN SET (0.00 sec) mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000193 Position: 7061 Binlog_Do_DB: Binlog_Ignore_DB: 1 row IN SET (0.00 sec)
Now try this for the much larger SHOW SLAVE STATUS. Or for the enormous SHOW ENGINE INNODB STATUS.
As you can see, this is a handy option to make your console output much easier to read.
Our Valve Overlords
So, it seems that Valve Software are yet again trying to stop people from gaining new weapons in Team Fortress 2. The Scout update was released yesterday, and people want to try out the new weapons a quickly as possible. As per normal, the targets were those who used the Steam Achievement Manager.
Dear Valve, here’s a hint: if I wanted to grind away for hours to get new weapons, I’d be playing World of Warcraft.
As with last time, only a handful of people are reporting their weapons being taken away from them. This isn’t a deterrent, it barely rates as news.
Valve, I implore you, don’t go down this road. We know you’re trying to encourage people to play more, that you want to reward regular players. The fact is, not all of us have copious quantities of spare time to devote to playing each class. We just want to try out the new weapons, have a bit of a mess around, then go about our lives.
A recurring comment is that you want people to gain achievements through their regular game play, that it should come as a surprise. I ask you, then, what is more in line with your philosophy: unlocking just the weapons using the Steam Achievement Unlocker, or grinding them out on achievement servers, blowing the fun of the achievements on repetitive work, rather than fun?
For reference, I used the unlocker, and I still have my weapons. Same as every other pack.
And for those wondering why I’m posting this on my blog that almost certainly isn’t being read by the TF2 team, it’s just a public copy of a similar email I’ve sent to Valve. They’ve been good about listening to public feedback in the past, I’m hoping that this time is no exception. If you feel the same, send an email to Gabe Newell.
Upgrading MySQL with minimal downtime through Replication
Problem
With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:
- Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
- mysql_upgrade: A much faster method, but it can still be slow for very large data sets.
I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.
Preparation
First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:
- If you haven’t already, enable Binary Logging on S1. We will need it to act as a replication Master.
- Add an extra bit of functionality to your backup procedure. You will need to store the Binary Log position from when the backup was taken.
- If you’re using mysqldump, simply add the –master-data option to your mysqldump call.
- If you’re using InnoDB Hot Backup, there’s no need to make a change. The Binary Log position is shown when you restore the backup.
- For other backup methods, you will probably need to get the Binary Log position manually:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; (Perform backup now...) mysql> UNLOCK TABLES;
Once you have a backup with the corresponding Binary Log position, you can setup S2:
- Install MySQL 5.1 on S2.
- Restore the backup from S1 to S2.
- Create the Slave user on S1.
- Enter the Slave settings on S2. You should familiarise yourself with the Replication documentation.
- Enable Binary Logging on S2. We’ll need this during the upgrade process.
- Setup S2 as a Slave of S1:
- If you used mysqldump for the backup, you will need to run the following query:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password';
- For any other method, you’ll need to specify the Binary Log position as well:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.nnnnnnn', MASTER_LOG_POS=mmmmmmmm;
- If you used mysqldump for the backup, you will need to run the following query:
- Start the Slave on S2:
mysql> START SLAVE;
The major pre-upgrade work is now complete.
Upgrade
Just before beginning the upgrade, take a backup of S2. For speed, I’d recommend running the following queries, then shutting down the MySQL server and copying the data files for the backup.
mysql> STOP SLAVE; mysql> SHOW MASTER STATUS;
Once the backup is complete, restart S2 and let it catch up with S1 again.
When you’re ready to begin the upgrade, you will need a minor outage. Stop your application, and let S2 catch up with S1. Once it has caught up, they will have identical data. So, switch your application to using S2 instead of S1. Your application can continue running unaffected while you upgrade S1 server.
- Stop the Slave process on S2:
mysql> STOP SLAVE; - Stop S1.
- Upgrade S1 to MySQL 5.1.
- Move the S1 data files to a backup location.
- Move the backup from S2 into S1’s data directory.
- Start S2.
- Setup S1 as a Slave to S2, same as when we made S2 a Slave of S1.
- Let S1 catch up with S2. When it has caught up, stop your application, and make sure S1 is still caught up with S2.
- Switch your application back to using S1.
Complete! Hooray! You just need to run a couple of queries on S1 to clean up the Slave settings:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='';
Conclusion
You can keep the outage to only a few minutes while performing this upgrade, removing the need for potentially expensive downtime. If you need the downtime to be zero, you probably want to be looking at a Circular Replication system, though that’s getting a little outside of this blog post.
pento.net goes mobile!
With a bit of fiddling around, I’ve found a good combination of WordPress plugins for mobile support.
- For iPhone/iPod: WPtouch. Lots of options, looks good on the iPhone browser.
- For all other mobiles: WP-viewMobile. This one is particularly handy, because it gives you the option to define the user agent strings it should activate for. To make it play nicely with WPtouch, I just had to remove the iPhone and iPod entries.
For both of these, setting them up was as simple as turning them on. I also added the various search engine mobile crawlers to WP-viewMobile. At the moment, the list of user agent strings I have are: Googlebot-Mobile, Y!J-SRD/1.0, msnbot-mobile, MSMObot. If anyone knows any others, please let me know.
So now, if you desperately want to check my site from the road, you can read it a bit easier on your mobile screen.
Permissions by interface on the local server
I had an issue come up recently that involved some confusion over permissions for the same user, connecting through different interfaces. For example, say you have a server with the public IP address of 192.168.0.1. You could connect to it from the local machine using the following commands:
shell> mysql -h localhost # Connects through the socket file shell> mysql -h 127.0.0.1 # Connects through the loopback interface shell> mysql -h 192.168.0.1 # Connects through the network interface
They all connect to the local server, but they can all have different permissions. Here are a couple of rules to make your life easier:
- Don’t use @127.0.0.1, unless you absolutely can’t use the socket file for some reason. Connecting through @localhost is usually faster than the loopback device, and it’s easier to type.
- Only connect through the network interface if you’re planning on moving the application to a different server later on.
That’s all.
Don’t use GoDaddy Hosting
February 18, 2009 · Posted in Misc · 5 CommentsIt will only cause you pain.
To give a brief description of the problems I had:
- Apache is horribly slow. To the point that some RSS readers were having problems subscribing to my blog.
- Apache setup is weird, and causes all sorts of problems.
- FTP access is mind-boggling slow, only allows one connection at a time, and at one point, wouldn’t let me download directories.
- MySQL is sickeningly slow.
- More expensive than better hosts.
- Really bad web interface for site management.
Now, I’m happily away from them. Never again.
Replication Checksumming Through Encryption
February 16, 2009 · Posted in MySQL · 9 CommentsProblem
A problem we occasionally see is Relay Log corruption, which is most frequently caused by network errors. At this point in time, the replication IO thread does not perform checksumming on incoming data (currently scheduled for MySQL 6.x). In the mean time, we have a relatively easy workaround: encrypt the replication connection. Because of the nature of encrypted connections, they have to checksum each packet.
Solution 1: Replication over SSH Tunnel
This is the easiest to setup. You simply need to do the following on the Slave:
shell> ssh -f user@master.server -L 4306:master.server:3306 -N
This sets up the tunnel. slave.server:4306 is now a tunnelled link to master.server:3306. So now, you just need to alter the Slave to go through the tunnel:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO master_host='localhost', master_port=4306; mysql> START SLAVE;
Everything else stays the same. Your Slave is still connecting to the same Master, just in a different manner.
This solution does have a couple of downsides, however:
- If the SSH tunnel goes down, it won’t automatically reconnect. This can be fixed with a small script that restarts the connection if it fails. The script can be added to your init.d setup, so it automatically opens on server startup.
- If you use MySQL Enterprise Monitor, it won’t be able to recognize that the Master/Slave pair go together.
Solution 2: Replication with SSL
Replication with SSL can be trickier to setup, but it removes the two downsides of the previous solution. Luckily, the MySQL Documentation Team have done all the hard work for you.
- Step 1: Create the certificates
- Step 2: Setup the servers to recognize the certificates
- Step 3: Change the Slave to use SSL
Conclusion
If you’re seeing corruption problems in your Relay Log, but not in your Master Binary Log, try Solution 1. It’s quick to setup and will determine if encryption is the solution to your problem. If it works, setup Solution 2. It will take a little bit of fiddling around, but is certainly worth the effort.
Secret Santa Script
My partner comes from a large family. At Christmas time, rather than making everyone buy presents for everyone else (a rather expensive venture), we have a Secret Santa tradition. Unfortunately, as the years have gone on, it has become more difficult to organize for everyone to be in the one place to “pick a name out of a hat”.
And so, in my never-ending quest to remove the magic and joy from Christmas, I wrote a script to automatically assign everyone their Santa/Santee, and send them an SMS. It has a few cool things to it:
- Allows you to prevent some people from being paired up. For example, you might not want a couple in the family to be assigned each other, because they’ll probably buy each other presents anyway.
- Makes sure that everyone has a Santee before it starts sending messages, to prevent SMS spam.
- Checks that all messages went through successfully.
I found that ValueSMS worked nicely for sending messages in Australia, you might need to use a different one for your country. Any gateway that has a HTTP(S) interface will do the job.
To set it up for yourself, you’ll need to do the following:
- Edit the @names array to create a list of everyone participating (beginning line 12).
- Edit the @people array to give the details of everyone participating (beginning line 20).
- Edit in your ValueSMS username/password, or setup the HTTP(S) URL for your preferred SMS provider (beginning lines 101, 139).
Here it is, for your enjoyment:
0.1 – 2009-02-16: secretsanta.pl.gz
Case Insensitive REPLACE() for MySQL
One request I occasionally see is for a case insensitive version of REPLACE() for MySQL. I wrote this a while back, but here it is now for all of you to play around with. It uses a basic naïve string search algorithm, so can be slow under some circumstances.
DELIMITER $$ DROP FUNCTION IF EXISTS `replace_ci`$$ CREATE FUNCTION `replace_ci` ( str TEXT,needle CHAR(255),str_rep CHAR(255)) RETURNS TEXT DETERMINISTIC BEGIN DECLARE return_str TEXT DEFAULT ''; DECLARE lower_str TEXT; DECLARE lower_needle TEXT; DECLARE pos INT DEFAULT 1; DECLARE old_pos INT DEFAULT 1; SELECT lower(str) INTO lower_str; SELECT lower(needle) INTO lower_needle; SELECT locate(lower_needle, lower_str, pos) INTO pos; WHILE pos > 0 DO SELECT concat(return_str, substr(str, old_pos, pos-old_pos), str_rep) INTO return_str; SELECT pos + char_length(needle) INTO pos; SELECT pos INTO old_pos; SELECT locate(lower_needle, lower_str, pos) INTO pos; END WHILE; SELECT concat(return_str, substr(str, old_pos, char_length(str))) INTO return_str; RETURN return_str; END$$ DELIMITER ; SELECT replace_ci( 'mySQL', 'M', 'M' );
It’s also available on MySQL Forge.
Replication with InnoDB and MyISAM Transactions
February 13, 2009 · Posted in MySQL · 4 CommentsThere’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:
Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0); Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1); Transaction 1: START TRANSACTION; Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1; Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1; Transaction 2: START TRANSACTION; Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1; Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2; Transaction 2: COMMIT; Transaction 1: COMMIT;
After this, the Master innodb_tbl would look like this:
item val 1 1 2 2 And the Master myisam_tbl will look like this:
item val 1 2 In 5.1.30 and earlier, the Slave tables will be correct. However, in 5.1.31, the Slave myisam_tbl will be correct, but the innodb_tbl will look like this:
item val 1 0 2 1 As a bonus, there’s no workaround. Statement Based Replication has never worked for this case. For an SBR Slave (In MySQL 5.0.x and 5.1.x), the Slave myisam_tbl will be correct, but the Slave innodb_tbl will look like this:
item val 1 2 2 2 And so, we come to the moral of the story. Don’t use non-transactional tables in the middle of a transaction. Ever. You will only cause yourself more pain than you can possibly imagine. Instead, move the writes to the non-transactional tables outside of the transaction.
-
Gary Pendergast, noun: Gamer, developer, MySQL, WordPress, web and music geek. Currently taking a leave of absence from life as a MySQL Support Engineer for Oracle to tour around Europe. Can be observed in his natural habit at pento.net, or usually in Bologna, Italy.
