Cowboy Coded

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

Archive for category MySQL

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: , ,

MySQL group by day, month or year using a timestamp column

Apr 19

Posted by John in MySQL, Rails | 9 Comments

In Ruby on Rails, created_at and updated_at columns are MySQL timestamp columns.  GROUP BY is pretty useless on a timestamp column, unless you are trying to group rows that were added at the same second.  I needed to group by just the date, so in order to do this I had to manipulate the timestamp with a MySQL operator.  Using the DATE_FORMAT operator, you can easily group the timestamp column using any format you want.  For my example, I needed to group rows that were added on the same day.  Here is my query:

select count(*), DATE_FORMAT(created_at,"%Y-%m-%d") as created_day FROM widgets GROUP BY created_day

This query will give you something like this:

count(*) | created_day
126 | 2010-04-12
168 | 2010-04-13
169 | 2010-04-14
189 | 2010-04-15
187 | 2010-04-16
13 | 2010-04-17

Group by month:

select count(*), DATE_FORMAT(created_at,"%Y-%m") as created_month FROM widgets GROUP BY created_month

Group by year:

select count(*), DATE_FORMAT(created_at,"%Y") as created_year FROM widgets GROUP BY created_year

Create database from the command line or shell script

Nov 23

Posted by John in MySQL | 1 Comment

I am writing a shell script that to setup my server and I need to create a database as part of this process.  Its pretty easy to do.  You can create a database from the command line without logging in to mysql interactive mode.  Here is the code

mysql -u root -pyourpassword -e "create database some_database_name;"

NOTE: when I pasted this into bash, I had to change the quotes.. I guess wordpress converts them to a different encoding

Retrying MySQL slave query after an error

Nov 19

Posted by John in MySQL | No Comments

I did something stupid today.  I was logged into my slave and thought I was logged into the master.  I altered a table and then realized I was on the wrong server.  So I logged into master and altered the same table.  This created an error when the slave tried to replicate, because the column in the table it was altering already existed.  So the replication could not continue.  I ran this command:

>SHOW SLAVE STATUS \G;

This showed me the error, so I went back and deleted the column on the slave so the alter statement on the slave would finish without error.  Then all you have to do is stop slave and then start slave

>STOP SLAVE;

>START SLAVE;

The slave should catch up to the master shortly.  NOTE TO SELF… make slave user read only.

Change a column data type with Rails migration

Oct 7

Posted by John in MySQL, Rails | 16 Comments

I always forget the syntax for this and I have to look it up, so I am going to post it here.  Maybe this will help some others out.  Basically, what I need to do is change the data type of a column in my database table.  The table “widgets” contains a column/field named “count”.  I want to change count from an integer to a float.  So first I create a rails migration with the following command.

>script/generate migration change_data_type_for_widget_count

Then I edit the migration file:   app_root/db/migrate/20091007151516_change_data_type_for_widget_count.rb.  Here is the migration syntax to change the data type of a column:

Then you just run the rake task “db:migrate” and it will change the data type in the database table.

>rake db:migrate

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: ,

Ruby on Rails & MySQL Master Slave replication tutorial

Aug 19

Posted by John in MySQL, Rails, Scaling | 6 Comments

I have been running a web app on 1 server since 2005 and we have seen a good bit of traffic. We are currently ranked 8200 according to quantcast.com with just over 200,000 visitors a month. Traffic is picking up and I expect it to almost double in Q4.  NewRelic shows all green right now with about 250ms response time which is fine.  But I am worried that if traffic doubles, the database may become a little stressed.  So I have decided to be pro-active and go ahead and add another server to the database layer.  Right now, I have Nginx in front of mongrel cluster (4 instances) and MySQL as the database.  All of this is running on the same server.  So my new setup will be:

Server 1:  Nginx > Mongrel Cluster > MySQL master

Server 2: MySQL slave

NOTE:  RailsEnvy has a really good video series on scaling your database (and many other great videos on other subjects), so you may want to check that out just to get an overview of the process.

In order to get this working, Rails will need to be able to perform writes only on master and reads on both master and slave.  But, lets talk about setting up the replication first.  Here is how you set it up:

1. Set binary logging on the master server.  Edit /etc/my.conf

[mysqld]
# add this line anywhere in the [mysqld] section
log-bin=mysql-bin

2. Set the server ids on master and slave in /etc/my.conf

#MASTER
[mysqld]
server-id=1
#SLAVE
[mysqld]
server-id=2

3. Grant the slave permission to pull the data from the master (replace XX.XX.XX.XX with your slave’s IP address)

mysql>grant replication slave on *.* to ‘replication’@XX.XX.XX.XX identified by ’slavepass’;

Then you will need to restart your mysql server on the master server

on ubuntu you would do:

service mysql restart

4. Perform a read-lock on the master

mysql> FLUSH TABLES WITH READ LOCK;

5. Record the file and position on the master.  It should give you output like this.  Record the file and position to use later

mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File                            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 |  1467771 |              |                  |
+——————+———-+————–+——————+

6. Export data on master using mysql_dump

> mysqldump --all-databases --lock-all-tables >dump.db

7. Unlock the tables on master

mysql> UNLOCK TABLES;


8. FTP the tar file of the mysql snapshot to your slave box

9. Stop MySQL on the slave

/etc/init.d/mysqld stop


10. You need to start slave using --skip-slave option.  I changed the init.d script to do this.  Probably an easier way to do it, but whatever..  It was line 61 in /etc/init.d/mysqld.  Just make a backup of that file, so you can replace it after you start mysql

# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure.
/usr/bin/mysqld_safe   --skip-slave --datadir="$datadir" --socket="$socketfile" \
--log-error="$errlogfile" --pid-file="$mypidfile" \
>/dev/null 2>&1 &


11. Start mysql on slave.  Then change /etc/init.d/mysql back to the original (without --skip-slave option)

> /etc/init.d/mysqld start


12. Import the data into the slave

> mysql < dbdump.db


13. Configure master on the slave box using the values for your host,user, and password.  Fill in the last 2 with the values you recorded from step 5

mysql> CHANGE MASTER TO
->     MASTER_HOST='your_host',
->     MASTER_USER='replication',
->     MASTER_PASSWORD='user_password',
->     MASTER_LOG_FILE='mysql-bin.000001',
->     MASTER_LOG_POS=1467771;


14. Start the slave replication on the slave server

mysql> START SLAVE;


Now your slave should be getting updates from the master.  Go ahead and change some stuff on the master and see if the slave replicates.  You can also view the status of the slave by using this mysql command:

mysql> SHOW SLAVE STATUS;


CONFIGURING RAILS TO WORK WITH MASTER/SLAVE


The next step in the process is to get rails to communicate with the master/slave setup.  The easiest way to do this is to use the machoism plugin.  You can get it at github.  There are a few different branches of this, but I am using the one from technoweenie (I had problems getting the mislav version to work)

http://github.com/technoweenie/masochism/tree/master

It is easy to install.  Just go to your rails app root directory and issue the following command

> script/plugin install http://github.com/technoweenie/masochism.git


Next you need to configure your database.yml to for masochism.  One interesting thing I found out that I was not aware of is that you can set the login as a variable, so you can reuse it later in the config file.  Here is my config

login: &login
adapter: mysql
database: yourapp_production
username: yourusername
password: yourpassword# default configuration (slave)
production:
<<: *login
host: XX.XX.XX.XX

# setup for masochism (master)
master_database:
<<: *login
host: XX.XX.XX.XX


Then add the following lines to your environment.rb file



# in environment.rb
config.after_initialize do
  if Rails.env.production?
    ActiveReload::ConnectionProxy::setup!
  end
end




Restart your app server and you should be good to go.


I thought I was until I figured out that there was a conflict between masochism and another plugin I use called use_db.  Masochism seems to be overriding the database config for use_db, so it is restricting me to only the master/slave databases.  I will post when I figure out a solution to this.

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: , , ,