Command line start MySQL.

sudo /usr/local/mysql/support-files/mysql.server start

To find the MySQL version from the terminal, type at the prompt:

/usr/local/mysql/bin/mysql -v

This also puts you in to a shell interactive dialogue with mySQL, type q to exit.

After installation, in order to use mysql commands without typing the full path to the commands you need to add the mysql directory to your shell path, (optional step) this is done in your “.bash_profile” file in your home directory, if you don’t have that file just create it using vi or nano:

cd ; nano .bash_profile

export PATH="/usr/local/mysql/bin:$PATH"

The first command brings you to your home directory and opens the .bash_profile file or creates a new one if it doesn’t exist, then add in the line above which adds the mysql binary path to commands that you can run. Exit the file with type “control + x” and when prompted save the change by typing “y”. Last thing to do here is to reload the shell for the above to work straight away.

source ~/.bash_profile

mysql -v

You will get the version number again, just type “q” to exit.

Set the MySQL root password

Note that this is not the same as the root or admin password of OSX – this is a unique password for the mysql root user, use one and remember/jot down somewhere what it is.

/usr/local/mysql/bin/mysqladmin -u root password 'yourpasswordhere'

Use the single ‘quotes’ surrounding the password

Fix the 2002 MySQL Socket error

Fix the looming 2002 socket error – which is linking where MySQL places the socket and where OSX thinks it should be, MySQL puts it in /tmp and OSX looks for it in /var/mysql the socket is a type of file that allows mysql client/server communication.

sudo mkdir /var/mysql

sudo ln -s /tmp/mysql.sock /var/mysql/mysql.sock

AutoStarting MySQL on Reboot

There was a solution recently posted on how to autostart MySQL on reboot on Yosemite, if you follow this it will work:

sudo nano /Library/LaunchDaemons/com.mysql.mysql.plist

And paste in:

  
    KeepAlive
    
    Label
    com.mysql.mysqld
    ProgramArguments
    
      /usr/local/mysql/bin/mysqld_safe
      --user=mysql
            
  

Save it and then:

sudo chown root:wheel /Library/LaunchDaemons/com.mysql.mysql.plist
sudo chmod 644 /Library/LaunchDaemons/com.mysql.mysql.plist
sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysql.plist

Then it will load on a restart.

http://coolestguidesontheplanet.com/get-apache-mysql-php-phpmyadmin-working-osx-10-10-yosemite/

https://dev.mysql.com/doc/refman/5.6/en/osx-installation-launchd.html

Insecure Startup Item disabled

“/Library/StartupItems/MySQLCOM” has not been started because it does not have the proper security settings.

Solution:
sudo chown -R root:wheel /Library/StartupItems/MySQLCOM

http://discussions.apple.com/thread.jspa?threadID=2644088&tstart=0&messageID=12781385

Uninstalling MySQL on Mac OS X Leopard

To uninstall MySQL and completely remove it (including all databases) from your Mac do the following:

• Use mysqldump to backup your databases to text files!
• Stop the database server
• sudo rm /usr/local/mysql
• sudo rm -rf /usr/local/mysql*
• sudo rm -rf /Library/StartupItems/MySQLCOM
• sudo rm -rf /Library/PreferencePanes/My*
• edit /etc/hostconfig and remove the line MYSQLCOM=-YES-
• rm -rf ~/Library/PreferencePanes/My*
• sudo rm -rf /Library/Receipts/mysql*
• sudo rm -rf /Library/Receipts/MySQL*

The last two lines are particularly important as otherwise, you can't install an older version of MySQL even though you think that you've completely deleted the newer version!

http://akrabat.com/computing/uninstalling-mysql-on-mac-os-x-leopard/

MySQL Fatal Error

http://www.downtr.net/find/mac%20osx.html

MySQL Error: http://binmusic.org/index.php?do=search

Non-blocking ActiveRecord & Rails

Beyond 'gem install MySQL’ in Ruby

View more presentations from Ilya Grigorik.

Rails and MySQL go hand in hand. ActiveRecord is perfectly capable of using a number of different databases but MySQL is by far the most popular choice for production deployments. And therein lies a dirty secret: when it comes to performance and 'scalability' of the framework, the Ruby MySQL gem is a serious offender. The presence of the GIL means that concurrency is already somewhat of a myth in the Ruby VM, but the architecture of the driver makes the problem even worse. Let's take a look under the hood.

Dissecting Ruby MySQL drivers

The native mysql gem many of us use in production was designed to expose a blocking API: you issue a SQL query, and the library blocks until the server returns a response. So far so good, but unfortunately it also introduces a nasty side effect. Because it blocks inside of the native code (inside mysql_real_query() C function), the entire Ruby VM is frozen while we wait for the response. So, if you query happens to have taken several seconds, it means that no other block, fiber, or thread will be executed by the Ruby VM. Ever wondered why your threaded Mongrel server never really flexed its threaded muscle? Well, now you know.
Fortunately, the little known mysqlplus gem addresses the immediate problem. Instead of using a single blocking call, it forwards the query to the server, and then starts polling for the response. For the curious, there are also two implementations, one in pure Ruby with a select loop, and a native (C) one which uses rb_thread_select. The benefit? Well, now you can have multiple threads execute database queries without blocking the entire VM! In fact, with a little extra work, we can even get some concurrency out of ActiveRecord.

http://www.igvita.com/2010/04/15/non-blocking-activerecord-rails/?utm_source=feedburner&utm_campaign=Feed%3A+igvita+%28igvita.com%29&utm_content=feed

TraceWatch

TraceWatch Real Time Web Stats and Traffic Analysis Lets you keep track of the visitors to your website in real time with detailed statistics and deep analysis using an innovative user interface for FREE and helps you make your website more effective. Can be easily installed on any website supporting PHP and MySQL. You only need to upload some files to your server.
http://www.tracewatch.com/

MariaDB versus MySQL

MariaDB 5.1 is based on MySQL 5.1 and is available under the terms of the GPL v2 license.

MariaDB will be kept up to date with the latest MySQL release from the same branch.

In most respects MariaDB will work exactly as MySQL: all commands, interfaces, libraries and APIs that exist in MySQL also exist in MariaDB.

Currently the main differences between MariaDB 5.1 and MySQL 5.1 are:

• Maria storage engine is included
• PBXT storage engine is included
• XtraDB storage engine is included
• FederatedX storage engine is included
• Faster complex queries
• Pool of Threads
• Fewer warnings and bugs
• Speed improvements
• Extensions (More index parts, new startup options etc)
• Better testing of features
• Table elimination
• Slow Query Log Extended Statistics
• User profiling (SHOW PROFILE) is compiled in by default.

In MariaDB 5.2, the new features are:

• Group commit for the Maria storage engine. (faster)
• userstatv2', which creates several new information_schema tables with useful statistics information. You enable the statistics by setting the 'userstat' variable.

(for a definite and up-to-date list of features in 5.2, see MariaDB 5.2 page).

http://askmonty.org/wiki/index.php/MariaDB_versus_MySQL

Understanding the MySQL forks

But in the distributed revision control world we live in, it's never really that simple. Here are some other notes:

• XtraDB is an InnoDB fork and "mostly" only a storage engine. It has a future in MariaDB and Drizzle, but may not make it into MySQL (about to be owned by Oracle, who also owns InnoDB).
• OurDelta collects more patches than just the Percona patches, and enables the PBXT storage engine. There's also a repository of OurDelta for 5.1 - binaries just aren't out yet.
• Like XtraDB, PBXT has a future in Drizzle, MariaDB and *maybe* also MySQL (Oracle may be more friendly towards PBXT than XtraDB, but Oracle is not known to be friendly so who knows!). I left PBXT absent from the diagram for not having much of a lineage with previous MySQL releases (flames welcome if you disagree).
• XtraDB draws a lot from the Google patches for MySQL/InnoDB (not present on the diagram either).
• Drizzle is now incompatible with everything else in MySQL-land terms (replication, partitioning, etc), and they're happy to be. In storage engine terms, they're still compatible though, which leads me to describe Drizzle as a completely new "userland", with storage engine options still remaining very similar.
• MariaDB is both a new storage engine (Maria) and a userland "delta" of MySQL. I call it a delta since it is trying to keep binary-format compatibility with MySQL wherever it can. This means that it makes a good retrofit, but it limits what they can do without changing things like the .frm format, etc.
• The InnoDB plugin has a weird history. Oracle announced it as an optional replacement for the 5.1 InnoDB but not much has become of it since introduction. I would have expected 5.4 to be based off the plugin, but it's not (at least at this point).
• MySQL is the real loser in the direction the patches are moving. While all other forks are free to share amongst themselves (where compatible) and take from MySQL, MySQL will only accept a patch if the author signs a Contributor's License Agreement. They need to do this - otherwise they can't sell OEM copies, which still make a large chunk of sales. Until recently, MySQL didn't accept any InnoDB patches unless they came downstream from Oracle, and Oracle keeps InnoDB development as a closely guarded secret - which makes influencing it very difficult.

http://mtocker.livejournal.com/50931.html

OurDelta

OurDelta produces enhanced builds for MySQL 5.0 and builds for MariaDB 5.1, on common production platforms. James Purser of Open Source On The Air describes OurDelta as “a new distribution for MySQL”.

http://ourdelta.org/

AutoMySQLBackup

A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. Features - Backup mutiple databases - Single backup file or to a seperate file for each DB - Compress backup files - Backup remote servers - E-mail logs ...

http://sourceforge.net/projects/automysqlbackup/

Defining Columns in Migrations

When using migrations, bear in mind that a migration is (by design) a database-agnostic representation of a database. It uses generic data types for columns, like :binary and :boolean, to define the kind of data to be stored in a column.

However, different database servers implement the migration column types in different ways. For example, MySQL doesn't have a boolean data type; so any migration columns you define as :boolean are actually converted into TINYINT(1) fields in the resulting MySQL database table (0 = false, 1 = true). Each migration column type also has a range of extra options you can set, which again modify the definition of the resulting field in the MySQL database.

The table below summarizes the migration column types, how they map to MySQL field data types, and the extra options available.

Migration column type...	Converts to MySQL field type...	Available options1
:binary	TINYBLOB, BLOB, MEDIUMBLOB, or LONGBLOB2	:limit => 1 to 4294967296 (default = 65536)2
:boolean	TINYINT(1)	-
:date	DATE	-
:datetime	DATETIME	-
:decimal	DECIMAL	:precision => 1 to 63 (default = 10) :scale => 0 to 30 (default = 0)3
:float	FLOAT	-
:integer	INT	:limit => 1 to 11 (default = 11)
:primary_key	INT(11) AUTO_INCREMENT PRIMARY KEY	-
:string	VARCHAR	:limit => 1 to 255 (default = 255)
:text	TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT2	:limit => 1 to 4294967296 (default = 65536)2
:time	TIME	-
:timestamp	DATETIME	-

All column types accept a :null or :default option:

• :null
  The default value for this is true (i.e. the field's value can be null in thedatabase). Set :null => false if you don't want to allow nulls in thedatabase field, e.g.
  
  • t.column :first_name, :string, :null => false
    
  Note that if you allow nulls in a field (:null => true or not specified), youdon't need to specify :default => NULL: NULL is already the default for a field, which allows null values.
  
• :default
  Specify the default value for the database field when new records are addedto the table. The value you specify should be of the correct data type for the column, e.g.
  
  1. t.column :completed, :default => true (for a :boolean column)
  2. t.column :size, :default => 1 (for an :integer column)
  3. t.column :name, :default => 'Unknown' (for a :string column)
  4. t.column :reminder_on, :default => Time.now (for a :datetime, :date, :time or :timestamp column)
     

Note that the default value should match the data type of the column (not the field). For example, if you were using MySQL and had a :boolean column, even though boolean fields are represented internally in MySQL as 1 digit TINYINT fields, you would specify the :default as true or false (not 1 or 0). This keeps your migrations portable to other database back-ends (for example, while MySQL just emulates booleans, some database back-ends have a native boolean data type, and a value of 1 or 0 might not make sense).

The :limit option on a :blob or :text column specifies the size of the database field in bytes. You can set this directly in bytes, or use a convenience method to specify the size, e.g. 2.kilobytes, 2.megabytes, 2.gigabytes(!). Note that MySQL will actually create a field with a data type, which encompasses the size you specify, i.e.

• 1 to 256 bytes: TINYBLOB or TINYTEXT
• 257 to 65536 bytes (64KiB): BLOB or TEXT
• 65537 to 16777216 bytes (16 MiB): MEDIUMBLOB or MEDIUMTEXT
• 16777217 to 4294967296 bytes (4 GiB): LONGBLOB or LONGTEXT

The :precision option specifies the number of digits to store before the point in a decimal; the :scale option specifies the number of digits to store after the decimal point.

http://www.packtpub.com/article/Working-with-Rails-ActiveRecord-Migrations-Models-Scaffolding-and-Database-Completion

Magic Multi-Connections

ActiveRecord models are allowed one connection to a database at a time, per class. Ruby on Rails sets up the default connection based on your database.yml configuration to automatically select development, test or production.

But, what if you want to access two or more databases – have 2+ connections open – at the same time. ActiveRecord requires that you subclass ActiveRecord::Base.

That prevents you doing migrations from one database to another. It prevents you using one set of model classes on two or more databases with the same schema.

Magic Multi-Connections allows you to write your models once, and use them for multiple database Rails databases at the same time. How? Using magical namespacing.

http://magicmodels.rubyforge.org/magic_multi_connections/

data_fabric

DataFabric provides flexible database connection switching for ActiveRecord.

We needed two features to scale our mysql database: application-level sharding and master/slave replication. Sharding is the process of splitting a dataset across many independent databases. This often happens based on geographical region (e.g. craigslist) or category (e.g. ebay). Replication provides a near-real-time copy of a database which can be used for fault tolerance and to reduce load on the master node. Combined, you get a scalable database solution which does not require huge hardware to scale to huge volumes. Or: DPAYEIOB - don’t put all your eggs in one basket. :-)

Installation

  gem install data_fabric

How does it work?

You describe the topology for your database infrastructure in your model(s). Different models can use different topologies.

  class MyHugeVolumeOfDataModel < ActiveRecord::Base
   data_fabric :replicated => true, :shard_by => :city
 end

There are four supported modes of operation, depending on the options given to the data_fabric method. The plugin will look for connections in your config/database.yml with the following convention:

No connection topology: #{environment} - this is the default, as with ActiveRecord, e.g. "production"

  data_fabric :replicated => true

#{environment}_#{role} - no sharding, just replication, where role is "master" or "slave", e.g. "production_master"

  data_fabric :shard_by => :city

#{group}_#{shard}_#{environment} - sharding, no replication, e.g. "city_austin_production"

  data_fabric :replicated => true, :shard_by => :city

#{group}_#{shard}_#{environment}_#{role} - sharding with replication, e.g. "city_austin_production_master"

When marked as replicated, all write and transactional operations for the model go to the master, whereas read operations go to the slave.

Since sharding is an application-level concern, your application must set the shard to use based on the current request or environment. The current shard is set on a thread local variable. For example, you can set the shard in an ActionController around_filter based on the user as follows:

  class ApplicationController < ActionController::Base
   around_filter :select_shard

   private
   def select_shard(&block)
     DataFabric.activate_shard(:city => @current_user.city, &block)
   end
 end

Warnings

• Sharded models should never be placed in the session store or you will get "Shard not set" errors when the session is persisted.
• DataFabric does not support running with ActiveRecord’s allow_concurrency = true in AR 2.0 and 2.1. allow_concurrency is gone in AR 2.2.

http://github.com/fiveruns/data_fabric/tree/master

Master/Slave Databases with Ruby on Rails

Not terribly long ago, I announced Active Delegate, which was a really lightweight plugin that I developed to allow models to talk to multiple databases for specific methods. The plugin worked great for really simple situations, like individual models.. but when it came time to test with associations it fell apart. I haven’t had a chance to work on any updates and knew that it was going to take more work to get it going.

Earlier this week, we helped one of our bigger clients launch their new web site¹. For the deployment, we needed to send all writes to a master database and a reads to slaves (initial deployment is talking to almost 10 slaves spread around the globe!). We needed something to get integrated quickly and decided to ditch Active Delegate for the time being and began looking at the following options.

I spoke with Rick Olson² and he pointed me to a new plugin that he hasn’t really released yet. So, I’m going to do him a favor and announce it for him. Of course… I got his permission first… ;-)

Announcing Masochism!

Masochism³ is a new plugin for Ruby on Rails that allows you to delegate all writes to a master database and reads to a slave database. The configuration process is just a few lines in your environment file and the plugin takes care of the rest.

Installing Masochism

With piston, you can import Masochism with:

$ cd vendor/plugins
$ piston import http://ar-code.svn.engineyard.com/plugins/masochism/

• To learn more about piston, read Every Second Counts with a Piston in your trunk

You can also install it with the old-fashioned way:

$ ./script/plugin install -x http://ar-code.svn.engineyard.com/plugins/masochism/

Configuring Masochism

The first thing that you’ll need to do is add another database connection in config/database.yml for master_database. By default, Masochism expects you to have a production database, which will be the read-only/slave database. The master_database will be the connection details for your (you guessed it…) master database.

# config/database.yml
production:
database: masochism_slave_database
adapter: postgresql
host: slavedb1.hostname.tld
...

master_database:
database: masochism_master_database
adapter: postgresql
host: masterdb.hostname.tld
...

The idea here is that replication will be handled elsewhere and your application can reap the benefits of talking to the slave database for all of it’s read-only operations and let the master database(s) spend their time writing data.

The next step is to set this up in your environment file. In our scenario, this was config/environments/production.rb.

# Add this to config/environments/production.rb
config.after_initialize do
ActiveReload::ConnectionProxy.setup!  
end

Voila, you should be good to go now. As I mentioned, we’ve only been using this for this past week and we’ve had to address a few problems that the initial version of the plugin didn’t address. One of our developers, Andy Delcambre, just posted an article to show how we had a problem with using ActiveRecord observers and masochism, which we’re sending over a patch for now.

As we continue to monitor how this solution works, we’ll report any findings on our blog. In the meantime, I’d be interested in knowing what you’re using to solve this problem. :-)

¹ Contiki, a cool travel company we’re working with

² Rick just moved to Portland… welcome to stump town!

³ The Masochism plugin README

http://www.robbyonrails.com/articles/2007/11/15/master-slave-databases-with-ruby-on-rails

QueryTrace

QueryTrace is a Rails plugin for pinpointing where in your Rails application that slow query is running. While ActiveRecord takes you half-way there by logging all queries and how long they’re taking, it won’t answer the question of where they’re being executed. Enter QueryTrace – once you have it installed, your logs won’t just tell you that you have a problem, they will pinpoint the exact location of that problem for you.

Installation

script/plugin install git://github.com/ntalbott/query_trace.git

Usage

All you have to do is have the plugin installed – QueryTrace takes care of the rest, including:

• Only displaying when at the DEBUG log level
• Honoring your log colorization settings

Example

Before

Schedule Load (0.023687)   SELECT * FROM schedules WHERE (schedules.id = 3) LIMIT 1
Resource Load (0.001076)   SELECT * FROM resources WHERE (resources.id = 328) LIMIT 1
Schedule Load (0.011488)   SELECT * FROM schedules WHERE (schedules.id = 3) LIMIT 1
Resource Load (0.022471)   SELECT * FROM resources WHERE (resources.id = 328) LIMIT 1

After

Schedule Load (0.023687)   SELECT * FROM schedules WHERE (schedules.id = 3) LIMIT 1
 app/models/available_work.rb:50:in `study_method'
 app/helpers/plan_helper.rb:4:in `work_description'
 app/views/plan/_resource_schedule.rhtml:27:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_resource_schedule.rhtml:24:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_schedule_listing.rhtml:5:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:3:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:1:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/index.rhtml:6:in `_run_rhtml_plan_index'
 vendor/plugins/textmate_footnotes/lib/textmate_footnotes.rb:60:in `render'
Resource Load (0.001076)   SELECT * FROM resources WHERE (resources.id = 328) LIMIT 1
 app/models/available_work.rb:54:in `div_type'
 app/helpers/plan_helper.rb:6:in `work_description'
 app/views/plan/_resource_schedule.rhtml:27:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_resource_schedule.rhtml:24:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_schedule_listing.rhtml:5:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:3:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:1:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/index.rhtml:6:in `_run_rhtml_plan_index'
 vendor/plugins/textmate_footnotes/lib/textmate_footnotes.rb:60:in `render'
Schedule Load (0.011488)   SELECT * FROM schedules WHERE (schedules.id = 3) LIMIT 1
 app/models/available_work.rb:50:in `study_method'
 app/helpers/plan_helper.rb:4:in `work_description'
 app/views/plan/_resource_schedule.rhtml:27:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_resource_schedule.rhtml:24:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_schedule_listing.rhtml:5:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:3:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:1:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/index.rhtml:6:in `_run_rhtml_plan_index'
 vendor/plugins/textmate_footnotes/lib/textmate_footnotes.rb:60:in `render'
Resource Load (0.022471)   SELECT * FROM resources WHERE (resources.id = 328) LIMIT 1
 app/models/available_work.rb:54:in `div_type'
 app/helpers/plan_helper.rb:6:in `work_description'
 app/views/plan/_resource_schedule.rhtml:27:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_resource_schedule.rhtml:24:in `_run_rhtml_plan__resource_schedule'
 app/views/plan/_schedule_listing.rhtml:5:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:3:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/_schedule_listing.rhtml:1:in `_run_rhtml_plan__schedule_listing'
 app/views/plan/index.rhtml:6:in `_run_rhtml_plan_index'
 vendor/plugins/textmate_footnotes/lib/textmate_footnotes.rb:60:in `render'

http://terralien.com/projects/querytrace/

Mysql Replication Adapter

So you want to use multiple slave databases with Rails? How does this syntax strike you:

MyModel.find(:all, :use_slave => true)

A few months ago, there was quite a bit of noise in the Rails community about Rails’ inability to easily make use of multiple databases. Dr Nic quickly responded with a solution. Everyone rejoiced in the wonder of the community.

However, the solution lacks the syntactic sugar that Rails does so well. It also has the load balancing logic above the models in user code, rather than below them, where it seems to belong.

Enter Mysql Replication Adapter.

This library contains a Rails database adapter that helps your Rails app talk to a single write master and multiple read-only slaves in a very elegant way. The configuration is also more environment-oriented than connection-oriented, so it hides the concept of multiple connections from the programmer altogether. All you have to do is say that you want a #find, #find_by_sql, or aggregate function spread over slave databases and the adapter does it for you.

Similar to Dr Nic’s approach, you still have to explicitly say when you want your queries to be sent to a slave database. We opted for this approach over a silent automatic balancing option because it reduces your ability to accidently really bork something up by sending it to a database that is just slightly behind in replication.

Here at Rapleaf, we’re using this adapter in a lot of our projects, because it allows us to leverage the additional read bandwidth of our two slave databases.

Installation & Use
(This all assumes that you have Mysql master-slave replication set up in advance.)

First, install it via the gem:

gem install mysql_replication_adapter

Next, pop open environment.rb and add:

require 'mysql_replication_adapter'

to the top before the initialize loop.

(Note: It *has* to go before the initializer loop, or it will load too late.)

Next, open up database.yml and configure it like so:

development:
adapter: mysql_replication
database: yourdb
username: root
password:
host: localhost
port: 3306
slaves:
- host: slave1
port: 3306
username: readonlyuser
password:
- host: slave2
port: 3306
username: readonlyuser
password:

Finally, to actually have Rails use one of the slave databases for a given find, use syntax like this:

MyModel.find(:all, :use_slave => true)

That’s all there is to it. If you’ve specified slave databases, when you make the above call, a random slave will be chosen and the query executed against that database. If you try and do a write operation (like find_or_create_by_*), the adapter will throw an error, so don’t worry, you won’t be able to corrupt your database.

One nice feature is that if you’re logging queries, whether because you’re in development mode or otherwise, the log will tell you which slave database the query got sent to. This could be helpful for debugging purposes.

There are a few other ways to make use of slave databases, but I’ll leave that for another post. You can also check out the readme in the gem directory for more details if you’re interested.

http://blog.rapleaf.com/dev/?p=5

Jet Profiler for MySQL

Is real-time query performance and diagnostics tool for the MySQL database server. Core features:

• Query, table and user performance
• Graphical visualisation
• Low overhead
• Easy to use

Query, table and user performance

Jet Profiler focuses on queries, tables and users. This gives you the information you need most in order to quickly fix performance problems in your code, such as most frequent queries, most used tables or busiest users.

Graphical visualisation

Data is collected, analyzed and displayed in real-time in diagrams, pie charts and tables. You can easily drill down and navigate through the data.

Low overhead

Most of the profiling work is done in the Jet Profiler application, not in the database server. Therefore, the performance hit is normally negligible, around 1%.

Easy to use

It supports all MySQL versions. No database server changes are necessary. No agents or separate services are needed. Jet Profiler is a desktop application which runs on your computer. You start it, connect to a server, hit the record button, and see results within minutes. And it runs on Windows, Mac and Linux.

http://www.jetprofiler.com

MySQL Command Tips

pager less -niSFX (pager format to long data table)

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt:

mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as
              new delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given
              outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing
              binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

Each command has both a long and short form. The long form is not case sensitive; the short form is. The long form can be followed by an optional semicolon terminator, but the short form should not.

http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html

SQL: Ordering with NULL values

Ordering in SQL is quite easy:

SELECT * FROM people ORDER BY nick_name;

This would result in a nice table like this:

id nick_name
3 Ariejan
2 Harm
1 Ralph

Now, let’s say for some reason you have NULL values in the nick_name column. This may be because you allow NULL values here, or because you’re doing a LEFT JOIN or something else crazy. Having NULL values will result in the following:

id nick_name
4 NULL
5 NULL
3 Ariejan
2 Harm
1 Ralph

This is not always desireable. It’s very well possible that you want to sort all non-NULL values first. If you know the following trick, it’s quite easy. (Actually, it’s not a trick, just some creative SQL writing).

SELECT * FROM people ORDER BY nick_name IS NULL, nick_name;

id nick_name
3 Ariejan
2 Harm
1 Ralph
4 NULL
5 NULL

How does this work? The ORDER BY nick_name IS NULL part will create a new (virtual) column that stores the result of nick_name IS NULL. If the field contains a nick_name the resulting value is 0, if there’s a NULL value it will contain 1. Sorting by this virtual attribute will sort 0’s before 1’s. After sorting the NULL’s to the bottom, you may specify other sorting criteria, such as the normal nick_name.

http://blog.kabisa.nl/2008/11/14/sql-ordering-with-null-values/