Glenn Nadeau

For this we’ll assume the following.

1. all databases and tables are replicated, we are not excluding anything from being replicated 2. we have identical /etc/my.cnf files for both master and slaves, the only difference is the server-id needs to be unique on all servers in the cluster. Even a slave of a slave of a slave needs to have a unique server-id 3. we have a username setup that has priviledges for replication (reading replication logs) 4. we’re using the following settings in the /etc/my.cnf on all servers (you can alter these but then you need to update the instructions)

datadir = /var/lib/mysql
log-bin = /var/log/mysql/bin.log
log-bin-index = /var/log/mysql/log-bin.index log-error = /var/log/mysql/error.log #these two do not need to be active if you do not want log = /var/log/mysql/sql.log log-slow-queries = /var/log/mysql/slow_query.log

5. on the slave databases you should add the following to the /etc/my.cnf

read_only = 1
# this is disabled in our config, enable it if you do not want to auto-restart the slave process when you restart mysql #skip-slave-start

6. we’re using the following information in the instructions

mydb1 = master db
mydb2 = new slave db
replication username = repl

- make sure mysql is stopped on both servers (service mysql stop) or (service mysqld stop)
- on all servers remove all the existing error/sql/slow-query/replication logs if they exist (rm -rf /var/log/mysql/*
- on mydb1 run (rsync -ave ssh /var/lib/mysql/* mydb2:/var/lib/mysql/)
- on mydb2 run (chmod -R mysql.mysql /var/lib/mysql)
- on mydb1 run (service mysql start)
- check logs for any errors and make sure that you can query the db
- on mydb2 run (service mysql start)
- check logs for any errors and make sure that you can query the db
- on mydb1 connect to mysql and execute (SHOW MASTER STATUS;), you will need to note the File name. It should be something like “bin.000001″
- on mydb2 connect to mysql and execute the following 3 SQL statements, make sure you do not proceed if there are errors
change master to MASTER_HOST=’mydb1.mydomain.com’, MASTER_USER=’repl’, MASTER_PASSWORD=’your_repl_password’, MASTER_LOG_FILE=’bin.000001′, MASTER_LOG_POS=4;
slave start;
show slave status\G
- at this point replication if there were no errors, replication should be working. If there were errors make sure you read the error log on
mydb2
- to test replication, on mydb1 create a database or table, then check
mydb2 to see if the change replicated

During an archiving of data today I truncated an InnoDB table.  I expected that to free up the disk space for the OS.  I was surprised when it did not.  A bit of research turned up some interesting information.

By design InnoDB tables NEVER shrink.  So truncation of the data will free up the table to repopulate, but will never return the space to the OS.  This is a performance related decision by the developers.  In order to recapture the space you need to truncate, drop and recreate.  This can be scripted through SQLyog, which is what I used.

I understand the decision to never shrink, it means you have to deal with the grow performance hit less often, but I wish there was a single command to free the space.   The recapture of the space once the truncate is completed is easy enough I guess, so no worries.

As a DBA there are times when fires happen.  I’ll need to diagnose what is going on quickly and resolve the issue.  Generally it is a query or process that slipped through QA, got applied to production and has now locked something up.  It happens and at the time of the crisis I don’t really care what the offending SPiD is doing because by that point my Boss is in my cube asking Boss like questions, a user is on the phone staring at a blank screen, QA comes running over with Dev in tow and life gets unfun.  Honestly at that point, I just want the offending SPiD dead so that everyone gets out of my cube and I can get back to Mafia Wars.   So a quick sp_who2, find the blocker and kill it.

In hind sight I usually wish I had the SQL it was running to analyze and correct so the problem does not happen again.  Hind sight is a wonderful thing, it makes me realize how not bright I can be at times.

Generally speaking the tools MS provides are great, but they never seem to get all of the statement for me and when things are really going south they frequently will not open at all.  So, I spend time grepping for fragments or pouring over known SQL looking for issues.  When all I really need is the full statement.  I can then make the corrections to it and send it over to the development manager to have someone fix the call with a cut and paste.  With that in mind I give you:

select r.session_id, r.status, r.wait_time,wait_type,
substring(qt.text,(r.statement_start_offset/2) +1,
(case when r.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end -r.statement_start_offset)
/2) as stmt_executing, r.blocking_session_id ,
r.total_elapsed_time,r.reads as phys_reads ,r.writes,
r.logical_reads, r.wait_resource
from sys.dm_exec_requests r cross apply
sys.dm_exec_sql_text(sql_handle) as qt,
sys.dm_exec_sessions s
where r.session_id >50  and r.session_id=s.session_id
order by r.total_elapsed_time desc

This query will show you the full text of most SQL statements.  There are exceptions, executes of certain extended stored procedures come to mind, that show a blank field.  Also, I know this works on 2005, I do not know if it works on2008 and I believe it fails on older versions.

Anyway, I didn’t want to lose this query, so I figured I would post it here.

Again, straight forward, but I know I’m going to forget a step if I don’t write it down.

1. Log on the the storage server for archived tables
2. run the command:
   df -h  (this command will let you see what space is available and show you the drive to use by space.)
3. change to the correct spot with cd /
4. once in the correct sub directory create a new directory with:
   mkdir <directory name>
5. start a screen session.  Larger files will take a while, screen lets you close out but keeps the sessions active.
6. Run the mysqldump command:
   mysqldump –opt -h <server> <database> <table name> > <file name for backup>
   mysqldump –opt -h foo bar temp_junk > bar_temp_junk-08082009.sql
   –opt is a combination of several other options and is normally on by default, but decided to play it safe.
   -h specifies the host or IP address
   > will overwrite the file if it already exists and >> will append to it
7. gzip *.sql
   this will compress the files and remove the uncompressed originals

Pretty straight forward, but I’m including it because I know it will get over written and I will have to do it again.

1. Putty to the appropriate machine
2. cd /var/lib (The directory may change based on the install, but this is standard.  Basically you are looking for the my.cnf file)
3. On all slave machines edit the my.cnf, update or add the line replicate-ignore-db=mysql
   vi my.cnf
   add replicate-ignore-db=mysql
   save and exit “:wq”
4. Restart mysql /etc/init.d/mysql restart
   or service mysql restart
5. start a MySQL connect to the slave machine and run:
   UPDATE `user` SET `Select_priv`=’Y',`Insert_priv`=’Y',
   `Update_priv`=’Y',`Delete_priv`=’Y',`Create_priv`=’Y',
   `Drop_priv`=’Y',`Reload_priv`=’Y',`Shutdown_priv`=’Y',
   `Process_priv`=’Y',`File_priv`=’Y',`Grant_priv`=’Y',
   `References_priv`=’Y',`Index_priv`=’Y',`Alter_priv`=’Y',
   `Show_db_priv`=’Y',`Super_priv`=’Y',`Create_tmp_table_priv`=’Y',
   `Lock_tables_priv`=’Y',`Execute_priv`=’Y',`Repl_slave_priv`=’Y',
   `Repl_client_priv`=’Y’
   WHERE `User` NOT IN (’<user name>’,'<user name2>’);
6. FLUSH PRIVILEGES;

To Roll this back:

1. Remove the line you inserted in the my.cnf file
2. Dump the user table from the master DB
3. Restore the user table dump to the slave.

The CPU on my SQL Server 2005 cluster was running slightly higher than normal.  (10-12% higher.)  I also noticed 1-2% kernel times consitently, which for my machine is pretty unusual.

I noticed an execute statement that I couldn’t get the SQL for, even with my show SQL for active SpID script.  It was connecting from my SQL Response repository and was from the SQL Response application.  It looked to be reading the error log over and over with the CPUtime going up to about 88K and then cycling back down and starting to climb again.

Removing the server from the SQL Response repository and killing the SPid would result in the CPU utilization returning to normal immediately.

I checked on the RED-GATE site and this seems to be a known issue that crops up from time to time.  After some trouble shooting and further reading it was determined the .NET 2.0 was the most likely suspect.  In order fix the problem I had to fail the cluster over, run a repair of the .NET 2.0 installation, reboot the monitored machine, verify the machine was OK when it came back up and then fail the cluster back over.  This resolved the issue and monitoring and CPU are now back to normal.

A couple of days ago we needed a MySQL install done on a new machine.  I decided now would be a good time to document the process and  use it to get my first post out of the way.  I doubt this will be of much use to anyone, but could be useful if I ever need to create a new installation and suffer from a brain cloud or something.

1. Step one, check to see if MySQL was already on the machine.  The command:
   rpm -qa|grep -i mysql
   rpm is the package manager and it allows you to see the packages on your machine as well as install, uninstall and general package maintenance.  The results showed no MySQL installation.
2. I needed to get the install libraries from the site.  I browsed to the site, found the files I needed and copied the link to the file I wanted and performed:
   wget <link>  ex: wget http://www.foo.com/dir/file/xxxxx.gz
   for each file I needed to put on the machine.  The list of packages:
   Server
   Client Programs
   Shared client libraries
   Shared compatibility libraries
   Headers and libraries
3. Once I had all the files down on the machine I needed to install them.  This was a standard install and I left the default location of /var/lib/mysql.  The install comand:
   rpm -Uvh mysql*
   The U is for upgrade or install
   v is for verbose
   h shows you the progress of the upgrade/install
4. Not everything I needed was on the machine, so I had to go get it.  The location of the files I went to:
   http://apt.sw.be/redhat/el5/en/x86_64/dag/RPMS/
   I had to search for the files that I did not have and wget them to the machine.  I then had to RPM them and once complete had a MySQL instance up and working.
5. I then stopped the MySQL instnace with:
   service mysql stop;
6. I copied the my.cnf file from another server to the /etc/
   scp <servername>:/etc/my.cnf my.cnf
7. I then started MySQL back up and was finished
   service mysql start