Cowboy Coded

Tech stuff.. Ruby, Rails, MySQL, Nginx, yada yada yada..

Posts Tagged MySQL

Setting up Rails 3 on Mac OSX Snow Leopard 10.6.4

Dec 4

Posted by cowboycoded in Uncategorized | 34 Comments

I finally made the complete switch to Mac this weekend.  I have been using my iMac at work for about a year and I love it, but I have been holding on to my Dell laptop and more and more every day I feel like throwing it through a window, so I decided to bite the bullet and buy a Macbook Pro so I could be more productive at home.  Here is a step by step guide of how I set it up to work with Rails 3.

1. Install git and RVM

RVM is a great way to manage multiple ruby versions on your Mac, and it also makes installing any version dead simple.  You will need to install “git” before you can install RVM.  I downloaded the git DMG from here:

http://git-osx-installer.googlecode.com/files/git-1.7.3.2-intel-leopard.dmg

Install git from the DMG and then you can install RVM afterwards.  Note that you will have to restart your terminal session in order to use the git command.

Install RVM by issuing the following commands in your shell:

Now you should be good to go with RVM. Here is the install guide if you run into any problems: http://rvm.beginrescueend.com/rvm/install/

Install a C Compiler (by installing XCode)

You will need a c compiler to install ruby using RVM.  You can download XCode, which has a c compiler, from the Apple website.  I downloaded the iOs/XCode package since i will probably be developing iPhone apps as well.  This is a mamoth download (3+ GB), so be prepared to wait a while.

If you dont feel like waiting that long for the download, pop in the OSX DVD that came with your Mac and when the startup screen appears, choose “Optional Installs”, and you can install XCode from there (without iOS stuff).

Install Ruby 1.9.2

cowboycoded$  rvm install 1.9.2

Simple as that!  This may take a few minutes to compile and install.  RVM will also install RubyGems, so its not necessary to do that manually.  Snow Leopard ships with Ruby 1.8.7, so you will need to switch to version 1.9.2 after you install it with RVM.  Use RVM to switch versions.

cowboycoded$  rvm 1.9.2

Verify that you are using 1.9.2

cowboycoded$  ruby -v

Set 1.9.2 as the default Ruby in RVM, so you don’t have to switch every time you open a new shell

cowboycoded$  rvm --default use 1.9.2

Install Ruby on Rails 3

cowboycoded$  gem install rails

This will install Rails 3 and all of its dependencies.  At the time of this writing the current version was 3.0.3.  I use MySQL for all of my apps, so I need to install that also.

Download and install MySQL server

http://dev.mysql.com/downloads/mysql/

Its probably easiest to download the DMG and install it that way.  Get the 64 bit DMG if you are using Snow Leopard.  Probably a good idea to install the MySQL Startup Item that is in the DMG as well.  This will start MySQL when your Mac boots.

After you install you can use the Startup Item to start MySQL manually:

cowboycoded$  sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

If you are using the mysql gem (v2.8.1), then you will likely run into a problem when you bundle install. Install the gem manually using this command instead:

cowboycoded$  sudo env ARCHFLAGS="-arch x86_64" gem install mysql -- --with-mysql-config=/usr/local/mysql/bin/mysql_config

Test it out

You should have everything you need at this point.  Fire up a new rails app and make sure everything worked:

cowboycoded$  rails new test_app
cowboycoded$  cd test_app
cowboycoded$  bundle install
cowboycoded$  rails s

Tags: , , , , ,

Quickie: Setting AUTO_INCREMENT for a MySQL table

Nov 26

Posted by cowboycoded in Uncategorized | No Comments

Not exactly a thrilling blog post, but I always forget the exact syntax for this for some reason, so I am putting it in my blog so I can easily find it.  Sometimes I find myself wanting to set the AUTO_INCREMENT start number back to one.  This is usually when I populate a new table, and end up deleting all the data in the table and then decide to repopulate it.  For example if I inserted 4000 records in a table and then deleted them, AUTO_INCREMENT would be set to 4001.  When I re-insert these 4000 records I would prefer it to start at id=1 instead of id=4001 to keep things clean.  Its as easy as running a simple ALTER statement:

#both of these will start you at id=1 when you insert your next record
ALTER TABLE my_table AUTO_INCREMENT=0
ALTER TABLE my_table AUTO_INCREMENT=1

Just to see what happened, I inserted a few test records into the database and then set the AUTO_INCREMENT back to “1″. I was expecting an error when I inserted the next record since the id=1 already existed and AUTO_INCRMENT id field has to be unique. But MYSQL can detect this and it automatically auto increments to the next available id. So if I have 3 records with ids= [1,2,3] then I set AUTO_INCREMENT to “1″, the next record I insert will be ID=4. Same thing if you have ids = [2,3,4]. MySQL skips “1″ and the next record id will be “5″. According to some of the comments in the MySQL documentation, it uses the following formula when setting the ID to “0″ or “1″:

1 + max(AUTO_INCREMENT)

Tags: , ,

Snow Leopard upgrade pain…

Sep 27

Posted by cowboycoded in Uncategorized | 1 Comment

So I decided to upgrade to Snow Leopard last week in order to use the iOS SDK and crank out some iPhone/iPad apps.  As with any major upgrade I was expecting a few minor problems, but it really wreaked havoc on my Rails development environment.  The first problem was with MySQL.  The previously installed version did not work at all after the upgrade.  I could not get the server to start.  I downloaded the newest DMG of mysql server and installed it on my Mac.  I chose 32 bit MySQL, since I mistakenly thought my Mac was 32 bit after I ran the “uname -a” command and it showed i386.  So after some experimenting and further research, I downloaded the 64 bit DMG, since my Mac is actually 64 bit capable (if your processor info says Intel Core 2 Duo, then you are 64 bit).  In order to keep my existing data from the crapped out MySQL server (prior install for Mac 10.5), I had to stop the running server, delete the contents of the mysql data directory, copy my old data from the old data dir, and restart the server.  This fixed my database problems.  The next odd thing was my C Compiler.  I tried to install the mysql gem and received this error:

Building native extensions.  This could take a while…
ERROR:  Error installing mysql:
ERROR: Failed to build gem native extension.

/Users/me/.rvm/rubies/ruby-1.9.2-rc2/bin/ruby extconf.rb
checking for mysql_query() in -lmysqlclient… /Users/me/.rvm/rubies/ruby-1.9.2-rc2/lib/ruby/1.9.1/mkmf.rb:368:in `try_do’: The complier failed to generate an executable file. (RuntimeError)
You have to install development tools first.
from /Users/me/.rvm/rubies/ruby-1.9.2-rc2/lib/ruby/1.9.1/mkmf.rb:435:in `try_link0′

Looking at the mkmf.log, showed me that it was having problems with the readline library.  Apparently the system readline installed with Snow Leopard does not play well with the ruby install.  So  I ran the following and RVM was able to use the correct readline library:

rvm package install readline
rvm install 1.9.2 -C --with-readline-dir=$HOME/.rvm/usr

This solved the readline problem, but another problem appeared after this:

ld: in /usr/local/lib/libxml2.2.dylib, file was built for i386 which is not the architecture being linked (x86_64)

There were are also problems with SQLite and libxslt.  I found this post which solved all 3 of these problems. Thanks Mark!

After correcting these problems, Ruby 1.9.2 installed fine with RVM and I was able to install the mysql gem without errors.

Tags: , , ,

Fixing slave relay log corruption in MySQL master/slave replication

Aug 18

Posted by cowboycoded in MySQL | 2 Comments

This morning, my business partner noticed that some of our sites were not updating from our main database.  One of our database servers was rebooted last night, so I suspected that the slave was not running.  So I logged in to the server and started up mysql client.  Then I issued this command:

show slave status\G

The slave was in fact running, but the relay log was corrupted
Part of the output shows this:

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

And it also says seconds behind master is NULL, which always signals a problem in replication. I ran the command that it told me to after I looked in the output table to see what the relay log was named (mysqld-relay.000037):

mysqlbinlog mysqld-relay.000037

This proceeded to run through the relay log, which took a minute or 2 and then at the end it showed me that there was an error on the last line. I assume this was caused by an incomplete write in the relay log as a result of the server restarting. OK, that makes sense and it kinda sucks that this is possible on a restart, but you can’t have everything right? On to the solution…

In order to get your slave back to where it should be, you need to look at the results of this command again:

show slave status\G

Copy down these values as you will need them in the next step:
Relay_Master_Log_File: mysql-bin.000041
Exec_Master_Log_Pos: 661877428

Now you will need to stop your slave, issue a command with the values in the previous step and then start your slave again like so:

stop slave;
CHANGE master TO master_log_file='mysql-bin.000041',master_log_pos=661877428;
slave start;

Then you can issue the show slave status command and Seconds_Behind_Master should not be NULL now. If its higher than zero, you can keep issuing the command to get an idea of how long it will take to catch up to the master. It is usually fairly quick unless you have many GB of data.

Tags: , ,

Backing up MySQL on the crontab with a shell script

Aug 26

Posted by John in MySQL | 2 Comments

I recently moved to a master/slave setup with MySQL.  At first I was thinking.. “Oh this is great, I have a backup database now in case anything breaks on the master. “  Don’t go there.. think about it for a second.  If something gets corrupted on the master, then it is going to push the changes over to the slave and then you will have 2 corrupted databases.  It is not that hard to create a backup, though, using mysql_dump + shell script + crontab.  That way you can have a nightly backup and reload it into MySQL if anything gets corrupted.  Here is what you do:

Create a file with your favorite editor called backup.sh and add the following to it:

#!/bin/sh
NOW=$(date +”%b-%d-%y”)
mysqldump -u youruserid -pyourpassword –all-databases > /path/to/mysql_backups/mysql_backup_$NOW.sql

The 2nd line in this file sets a variable for the current date in format like this:  Aug-26-09.  The last line uses the mysql_dump command to output all your databases to a file that appends the current date to the file name using the NOW variable.  If you did not append the date to the mysql backup, then it would get overwritten every night.  NOTE:  you may also want to create a delete script, so this cron does not fill up your filesystem.

Next thing you need to do is change the permission on the file so that you can execute it.  I am just assigning RWX for everyone, because I am too lazy to do it the right way (you probably just want to make this executable by the crontab user)

> chmod 777 backup.sh

The last thing you have to do is add it to your crontab. 0 0 * * * *, makes the cron execute it at midnight every night.

crontab -e
0     0       *       *       *       /path/to/your/backup.sh

Now this script will execute every night and export your entire database.  Now, dont you feel better??  Thought so.

Tags: , ,

A quick performance tune for mysql – innodb_buffer_pool_size

Aug 26

Posted by John in MySQL | 7 Comments

If you are using MySQL with InnoDB tables, you should really look at the innodb_buffer_pool_size.   This tells MySQL how much memory it should use to cache data on your InnoDB tables.  This cuts down on disc IO.  It can save you alot of lookup time if you are pulling out of the cache as opposed to making another call to the database table.  As a good rule of thumb this value should be set to 10% larger than the size of your database… that is assuming you have that much memory available on your server.  By default innodb_buffer_pool_size is set to 8MB.  If you have a decent sized data set, and most of you probably do, then you are going to want to increase this.  My database is about 1GB, so I set my innodb_buffer_pool_size to 2GB, so that I can account for some growth in the future.  Just be sure not to set the value too high.  If you only have 2GB of memory on your box, then don’t set it to 2GB.  This can cause paging at the OS level and that is bad news.

So here is how you set that parameter in mysql

open up your /etc/my.cnf in your favorite editor and add the parameter in the mysqld section

[mysqld]
innodb_buffer_pool_size = 2G

Then you restart mysql and the changes should be picked up.

/etc/init.d/mysqld restart

You can double check by issuing the following command in mysql client.  It will show you the variable and its value

mysql> SHOW VARIABLES;

NOW THATS A QUICK AND EASY PERFORMANCE TUNE!!

Tags: ,

MySQL & HAProxy tutorial

Aug 17

Posted by John in HAProxy, MySQL | 3 Comments

I have 2 database servers.  One server runs a script that updates about 8 million products.  This takes a long time ;-)   And I can’t use MySQL master/slave because the data is not production worthy until the script fully completes.  Instead of using Master/Slave, I push the binary files from database server 1 to database server 2 when the script completes.  So each of the databases will be turned off at some point.  Therefore, I need to use HAProxy to forward the requests to the working server when the other one is turned off.  And when the product script is not running and binary files are not being pushed, I want both of them running in the cluster.  So here is application flow:

web server requests -> haproxy -> database servers 1 & 2

In order to make this work we need HAProxy installed and we need a health check script for both databases, so HAProxy knows when one is not running (no requests will be forwarded to this server).  Here are the steps:

First I need to install haproxy on my web server (CentOS Linux), so that it can forward the requests to the 2 MySQL instances.  Go here:

http://haproxy.1wt.eu/#down

Download the latest stable source and make it.

tar -xvf haproxy-1.3.20.tar.gz
cd haproxy-1.3.20
make install

You dont have to ./configure and make.  Just “make install”.  Next step is to set up a config file.  Most people choose /etc/haproxy.cf

Here is a what my final config looks like:

global
        log 127.0.0.1 daemon debug
        stats socket /tmp/stats
        maxconn 4096
        pidfile /var/run/haproxy.pid
        daemon
defaults
        log global
        mode tcp
        option dontlognull retries 3 option redispatch
        maxconn 2000
        contimeout 5000
        clitimeout 50000
        srvtimeout 50000
listen  MySQL XX.XX.XX.XX:3305
        mode tcp
        option  httpchk
        stats enable
        stats uri /haproxy-stats
        balance roundrobin
        server mysql_slave XX.XX.XX.XX:3306 check port 9200 inter 12000 rise 3 fall 3
        server mysql_slave XX.XX.XX.XX:3306 check port 9200 inter 12000 rise 3 fall 3

Of course, you need to replace XX.XX.XX.XX with your server IPs.  The first IP in the “listen” section is the IP address where HAProxy is installed.  The last 2 lines in this file are where you put the IP addresses for your database servers.

After your config is set up, you can start haproxy with this command:

> /usr/local/sbin/haproxy -f /etc/haproxy.cfg -p /var/run/haproxy.pid

Then use your SQL client (I am using SQLyog) to login to mysql using port 3305.  If you are able to login, then that means that HAProxy is listening on port 3305 and forwarding the requests to the slaves.

The next thing you need to do is add some health checks, so that if one of the slaves goes down, then HAProxy will stop forwarding requests to the non-working slave.  For this I referenced a tutorial written by Unai Rodriquez  - Having HAProxy check mysql status through a xinetd script Essentially, you just create a xinetd service that listens on port 9200 and returns an HTTP status 200 or 500 depending on the state of the MySQL server.  HAProxy stops forwarding requests if it sees a status 500.

You can test this by stopping and starting MySQL and watching http://yourhost/haproxy-stats.  This will show if the servers are up or down.  After you verify that it is working correctly, it is time to change your Ruby on Rails (or whatever framework you are using) configuration to use port 3305.  Deploy your code, restart your app server and you should have  a working mysql cluster.

Tags: , , ,