Category: MySQL

MySQL Monitoring With myq_gadgets

September 1, 2013

Great set of utilities for monitoring MySQL performance: https://github.com/jayjanssen/myq_gadgets/ Here are the steps to install:


cd /root
wget https://github.com/jayjanssen/myq_gadgets/archive/master.zip
unzip master.zip

cd /root

wget https://github.com/jayjanssen/myq_gadgets/archive/master.zip

unzip master.zip

The tools will be in the myq_gadgets-master folder. There’s no installer so you can just start using the tools immediately. For instructions see the enclosed README.txt. Also this presentation demonstrates many of the features: http://www.percona.com/live/mysql-conference-2013/sites/default/files/slides/myq_gadgets%20talk.pdf

Sync MySQL Slave From Another Slave On Amazon AWS

August 27, 2013

I was looking for a procedure to setup a new MySQL replication slave on Amazon AWS without having to do a MySQL dump of the master. Ended up using a couple of AWS features to make this an easy process. Let’s assume that we have a master server (master01) and a slave (slave01). We want to create a new slave (slave02). We’ll do this without taking the master offline and without doing a MySQL dump. This is especially important with large databases. A couple of notes before we start: On AWS we put MySQL storage on it’s own EBS storage volume. We create a new empty volume, partition, add LVM and then format with xfs. This makes it easy to add additional storage in the future. We prefer to use the innodb-file-per-table setting in my.cnf to force MySQL to use a separate file for each InnoDB table. This keeps all the files

MySQL Defrag Script

January 9, 2013

Here’s a great script for optimizing MySQL tables: http://www.dufault.info/blog/a-script-to-optimize-fragmented-tables-in-mysql/ You can install and run with:


wget https://raw.github.com/pdufault/mysqlfragfinder/master/mysqlfragfinder.sh
chmod 755 ./mysqlfragfinder.sh
./mysqlfragfinder.sh --user root --password "xxxxxx"

wget https://raw.github.com/pdufault/mysqlfragfinder/master/mysqlfragfinder.sh

chmod 755 ./mysqlfragfinder.sh

./mysqlfragfinder.sh --user root --password "xxxxxx"

Force MySQL to UTF-8 Encoding

December 12, 2012

Recently learned that mysql-proxy does to issue SET statements to read-only slaves when doing read/write splitting. This create a problem when the client wants to set the character set. The work around is to force all connections to the server to the desired character set which in this case is UTF-8. Add the following lines to the “[mysqld]” section of /etc/my.cnf on the server:


skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server = utf8

skip-character-set-client-handshake

init-connect='SET NAMES utf8'

character-set-server = utf8

Change MySQL InnoDB Log File Size

December 4, 2012

Here’s my procedure for changing innodb_log_file_size: SET GLOBAL innodb_fast_shutdown = false; SET GLOBAL innodb_max_dirty_pages_pct = 0; /sbin/service mysqld restart /sbin/service mysqld stop rm -f /var/lib/mysql/ib_logfile[01] Edit /etc/my.cnf and set new value for innodb_log_file_size /sbin/service mysqld start Here’s a handy sql command to determine a good log file size:

mysql> show engine innodb status\G select sleep(60); show engine innodb status\G

1	mysql> show engine innodb status\G select sleep(60); show engine innodb status\G

then do this:

mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;

1	mysql> select (3838334638 - 3836410803) / 1024 / 1024 as MB_per_min;

to get the MB per minute. Set the log file size to be large enough to hold one hour of logs. See http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ for the full discussion.