Intelligent MySQL Configuration
http://www.justatheory.com/computers/databases/mysql/configuration.html
Found some useful MySQL tips for Mac OS X
James Duncan Davidson?¢‚Ǩ‚Ñ¢s Configuring MySQL on MacOS X post earlier today reminded me that I wanted to blog about the configuration I came up with while installing MySQL 5 on my box.
Nothing has irritated me more than when MySQL?¢‚Ǩ‚Ñ¢s syntax has violated the ANSI SQL standards in the most blatant ways, or when transactions have appeared to work, but mysteriously not worked. Yes, I use Duncan?¢‚Ǩ‚Ñ¢s settings to make sure that the MySQL box on my PowerBook only listens on local sockets, but I additionally add this configuration to /etc/my.cnf:
sql-mode=ansi
character-set-server=utf8
default-storage-engine=InnoDB
default-time-zone=utc
That last configuration can actually only be added after running this command:
/usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |
mysql -u root mysql
But then the upshot is that I have everything configured to be as compliant as possible (althought the time zone stuff is just my personal preference):
mysql> SELECT @@global.sql_mode;
+————————————————————-+
| @@global.sql_mode |
+————————————————————-+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+————————————————————-+
1 row in set (0.00 sec)
mysql> show variables like ‘%character_set_%’;
+————————–+——–+
| Variable_name | Value |
+————————–+——–+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
+————————–+——–+
7 rows in set (0.01 sec)
mysql> show variables like ‘%table_ty%’;
+—————+——–+
| Variable_name | Value |
+—————+——–+
| table_type | InnoDB |
+—————+——–+
1 row in set (0.00 sec)
mysql> show variables like ‘time_zone%’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| time_zone | utc |
+—————+——-+
1 row in set (0.00 sec)
Now that?¢‚Ǩ‚Ñ¢s the way things should be! Or at least as close as I?¢‚Ǩ‚Ñ¢m going to get to it in MySQL 5.
Configuring MySQL on MacOS X
http://duncandavidson.com/essay/2006/04/mysqlconf
by James Duncan Davidson
Published: Monday, April 10, 2006
While sitting in the back of the room at the Portland Rails Studio, I poked up my hand during Getting Started section of the day. Dave was discussing setting up your database for a Rails application and here were two little configuration tricks I?¢‚Ǩ‚Ñ¢ve started using of late that I wanted to share. And of course I was reminded that I should probably get them up on my web site.
Both of these changes can be made in one place, the /etc/my.cnf file. All of my development machines have the following MySQL configuration:
[mysqld]
skip-thread-priority
skip-networking
The first option, skip-thread-priority, is a performance optimization that is recommended by Apple to increase performance under load. The second, skip-networking turns off networking. This means that MySQL won?¢‚Ǩ‚Ñ¢t start up a network listener on port 3306. It will only listen using the filesystem socket. This is handy so that you don?¢‚Ǩ‚Ñ¢t have worry about the management of MySQL users and permissions and access controls. Sure, the way that MySQL is set up out of the box means that your data should be secure even if somebody hooks up to your laptop. But if you turn off the networking entirely, you can be a lot more comfortable taking your development laptop into a conference.
Of course, for a production server that needs to accept network connections, you?¢‚Ǩ‚Ñ¢ll want to leave out the skip-networking configuration option.
Updates
After I first posted this, Eric Abele wrote and pointed out that there are several applications that can?¢‚Ǩ‚Ñ¢t use the filesystem socket with MySQL like Interrogate or the like. I haven?¢‚Ǩ‚Ñ¢t run into this myself, being a total-CLI based weenie when it comes to using MySQL, but if this describes you, Eric says to use the following in your config file instead of skip-networking:
bind-address = 127.0.0.1
This will let those clients that can?¢‚Ǩ‚Ñ¢t use the file system socket work and yet keep any network connections not originating on your machine from reaching your MySQL installation.
Also, my good friend David Wheeler pointed out some more great configuration options that will make MySQL more ANSI SQL-compliant, use Unicode, and more. Read his blog post for more details.