Bind Query Log

Bind, bind, bind… So easy to use 95% of the time…

I recently had to debug an issue with Bind DNS queries. Thankfully this was a new instance and nothing was affected by it. One tool that proved essential for debugging this was the bind query log.

As long as you have logging configured in /var/named/chroot/etc/named.conf (assuming you chroot bind, which I highly suggest) it will work by a simple command. Here is the logging portion of my name.conf file

logging{
        channel ns01_log{
                file "/var/log/ns1.log" versions 3 size 2m;
                severity info;
                print-severity yes;
                print-time yes;
                print-category yes;
        };
        category default{
                ns01_log;
        };
};

With that configured, all you need to do to turn bind query logging on is issue this command:

[[email protected] /]#rndc querylog

now that it logging has started, you can tail your ns1.log file to view the status of the incoming queries and better diagnose any queries that are causing problems.

The ‘rndc status’ command will show you whether query logging is on or not. Here is some example output:

[[email protected] /]# rndc status
number of zones: 39
debug level: 0
xfers running: 0
xfers deferred: 0
soa queries in progress: 0
query logging is ON
recursive clients: 0/1000
tcp clients: 0/100
server is up and running

I wouldn’t recommend leaving this on for an extended period of time if you expect that the server is going to be resolving a lot of DNS queries. This file has the potential to fill up fast!

Helpful netstat commands

I’m a huge fan of netstat, however it’s arguments aren’t nearly as straight forward as many other commands. The netstat command allows you to view a variety of networking info (listening ports, active ports, active connections, etc.). The difficulty might be because the command offers so many arguments that it can be hard to find the exact ones that you want.

I’ve compiled a list below of the netstat commands that I use the most often.

Aggregate all incoming http network connections and then group them by incoming IP then sort them from least to most. This especially comes in handy for early detection of a Denial of Service attack.

netstat -napl | grep :80 | awk '{print $5}' | cut -d : -f 1 | sort -n | uniq -c | sort -n

Simply print the total number of connections coming in on port 80 (http)

netstat -an |grep :80|wc -l

Display all of the active Internet connections to this server (you may want to “|more” this command because it can get long on a busy server)

netstat -natp

Show all connections (source + destination + ports) to the server. This can also get very long!

netstat -an

Display the routing table for all ips bound to the server. This output is more than likely the exact same as the ‘route’ command

netstat -rn

Want to know what process is using which port? This command will show you which process is responsible for which port that’s actively listening or being used.

netstat -pl

Creating and Extending – Logical Volume Manager – LVM partitions

LVM has created some pretty amazing features. We can now add, remove, extend, shrink, and snapshot volumes very quickly and easily. Since the overhead of LVM is so small and there are so many features, I enjoy using LVM partitions quite a bit.

We can combine multiple drives to make them look as one. To create a new LVM partition we will create a partition with fdisk and assign it type 8e (Linux LVM), then initialize it as a physical volume, then create a volume group, then create a logical volume within that volume group, then format it and mount it. It’s that simple!
Example: Combine drives sdb and sdc into one logical volume

fdisk /dev/sdb
   #Go through fdisk process to create the partition table sdb and sdc
   #select "n   add a new partition" -> fill in to your specifications
   #select "t   change a partition's system id" -> select "8e" for Linux LVM
   #type "w" to write it out and save it.
fdisk -l                #verify partition was created
pvcreate /dev/sdb1 /dev/sdc1 #initialize both drives as physical volumes
vgcreate myVolumeGroup /dev/sdb1 /dev/sdc1 #create a volume group with these drives in it
lvcreate --name myLogicalVolume --size 100G myVolumeGroup #create a 100G partition in the volume group
mkfs.ext3 /dev/myVolumeGroup/myLogicalVolume #format it

That’s it! Now you can mount it wherever you want and begin using it. Don’t forget to add it to /etc/fstab so it shows up on your next reboot.


The process to extend a LVM partition is very similar to creating one. The best part? for 2.6 Kernels using ext3 you can do this on the fly without un-mounting your drives!
Example: add device ‘/dev/sdd’ to our newly created volume group above then extend the logical volume

fdisk /dev/sdd  
   #Go through fdisk process to create the partition table sdd
   #select "n   add a new partition" -> fill in to your specifications
   #select "t   change a partition's system id" -> select "8e" for Linux LVM
   #type "w" to write it out and save it.
fdisk -l                #verify partition was created
mkfs.ext3 /dev/sdd1
pvcreate /dev/sdd1
vgextend myVolumeGroup /dev/sdd1
lvextend -L +10G /dev/myVolumeGroup/myLogicalVolume
resize2fs /dev/myVolumeGroup/myLogicalVolume #for ext3 and 2.6 Kernels works with mounted partitions!
#Otherwise you will have to unmount the volume first

There are a few tools that you can use to verify/monitor/look at your LVM partitions. Run without any parameters will display all items that it finds.
pvdisplay – display physical volumes
lvdisplay – display logical volumes
vgdisplay – display volume groups

A little known “feature” of LVM is that by default, it reserves 5% of your blocks. This comes in handy when an LVM partition gets crippled from overflowing. You can use tune2fs to free some more space up. I wouldn’t dip into these reserved blocks if not necessary though!

tune2fs -m

Extending your swap partition in CentOS

While I’m not a fan of SWAP memory, there are certain cases that can really save you by using swap. Would you rather get out of memory errors, or have a temporary slow down?

The CentOS/RHEL manual has a great quick tutorial on how to increase your swap space on an LVM2 logical partition. Since the default swap partition on a CentOS box is /dev/VolGroup00/LogVol01, that will be used in this example. The quick 5 steps are as follows:

  1. Use swapoff to disable swapping for the associated logical swap volume
  2. lvesize the logical volume to meet your needs
  3. Use mkswap to format the new swap space so it can be used properly
  4. Use swapon to enable the newly expanded swap volume
  5. Lastly, it’s always good to verify that the swap volume has grown the proper amount

Example: Extend the current swap LVM partition

 swapoff -v /dev/VolGroup00/LogVol01
 lvm lvresize /dev/VolGroup00/LogVol01 -L +1G
 mkswap /dev/VolGroup00/LogVol01
 swapon -va
 cat /proc/swaps

 

Another handy trick to “create” more swap space when you aren’t using LVM or don’t have an extra partition laying around is to create a swap file yourself.

  1. Create a file (full of zeros) that is the desired size
  2. Use mkswap to format the new swap space so it can be used properly
  3. Use swapon to enable the newly created swap file

Example: Create a swap file of 1GB and add it to the swap pool

dd if=/dev/zero of=/opt/swap-file bs=1024 count=1000000
mkswap -f /opt/swap-file
swapon /opt/swap-file

Multiple MySQL databases on one binary install using mysqld_multi

One very cool (and often overlooked) feature of mysql is the use of mysqld_multi.

This script allows you to run multiple instances of MySQL server on a single box with one set of MySQL server binaries. You’ll just need to specify different configuration parameters (port, socket, log file locations, etc.) in your my.cnf file. I’ll go through how simple this can be so setup below.

First, you’ll need to create a new user that will exist on all server instances and will only need the SHUTDOWN privilege. You can probably guess this, but this user is needed to initiate a shutdown on the specified server. Since you will have multiple instances running on one server, the default init scripts will no longer work to shutdown anything but the default instance.

mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'my-pwd';

Next, we will need to edit our my.cnf file. The first difference here is the general layout. First, you will have a section for [mysqld_multi], then each section after will be for the specified mysql instance. For example, if you have 3 sections defined – [mysqld1], [mysqld2], [mysqld3] – you will have places to configure 3 different mysql databases.

The [mysqld_multi] section is very simple, it just defines the mysqld_safe and mysqladmin binaries as well as the username and password that we previously created for shutting down the server. For example, it may look something like this:

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user    =       multi_admin
password =      my-pass

Next, we can begin defining our first database. I like to define all of the following as a minimum to get the server up and running. All other variables not defined will use the defaults given to it.

[mysqld1]
datadir=/var/lib/mysql
tmpdir=/tmp/mysqltmp
socket=/var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/mysql.pid
log-bin=/var/lib/mysql_logs/binlogs/dbsvr01-binlog
relay-log=/var/lib/mysql_logs/relaylogs/dbsvr01-relaylog
slow-query-log-file = /var/lib/mysql_logs/dbsvr01.slow_queries.log
log-error=/var/lib/mysql_logs/dbsvr01.mysql.err

That’s it! Your first database is ready to go. I wouldn’t recommend leaving all other variables at their default, so be sure to know what parameters you want to configure and which ones you can leave alone.

For safety, I usually delete the default mysql init script and replace it with one that will utilize the multiple databases. The MySQL RPM will provide this for you here: /usr/share/mysql/mysqld_multi.server. Copy this to the init run level of your choosing and don’t forget to make the necessary changes to chkconfig.

A little bit about the mysqld_multi.server script. By running the command you will quickly find that there are only 4 parameters to choose from:

Usage: /etc/init.d/mysqld_multi.server {start|stop|report|restart}

Issuing a “start” followed by a number (in our example 1) will start the first database. If you then wanted to start the second database, issue a “mysqld_multi.server start 2”. The same logic carries over for ‘stop’ and ‘restart’. The ‘report’ option is similar to ‘status’ in other common init scripts. This will give you a list of all of the mysql database instances that are defined, followed by if they’re started or stopped. For example, If I have 3 instances all defined and running it will look something similar to this:

[[email protected] /]$ /etc/init.d/mysqld_multi.server report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running

So there you have it! Enjoy :)

More Info: http://dev.mysql.com/doc/refman/5.5/en/mysqld-multi.html

StrongMail Admin Tasks

StrongMail is sort of a less-known product that I have a ton of experience with. It offers business class email solutions to companies. In other words, it allows you to send out a lot of email in a short period of time. I’m only going to make this one post about StrongMail, but if you want me to continue talking about it please post in the comments. This is essentially just a brain dump of what I can think of right now.


To upgrade the version of StrongMail to the minor latest release, run these commands on the StrongMail server:

# cd /data1/svcpack/
# ./sm-svcpack -c
New service pack available for installation:

5.2.1.7

Download and Install? [Y|N]
----------------------------------------------------------------------
Please select [Y] :

The services will be interrupted only in applying update and usually take no more than 10/min. The only box you can upgrade to 5.2.1.7 is the one currently on a 5.2 build, if you have other older version box, you need contact our AM to get on 5.2 build first. If you don’t keep up with the latest and greatest, you will most likely need to get an upgrade package from SM, but I thought this is a neat little trick.


StrongMail LiveUpdates…
Once a month, StrongMail comes out with the latest LiveUpdate package. This package is very important to keep the bounce and throttling rules up to date. The downfall of this is that it requires restarting all of the services once the install is complete. Restarting StrongMail can take up to 30-45 minutes. StrongMail will report that everything restarted correctly, but it needs a “warm up” time. I never have gotten a good explanation of what goes on during this…  Coincidentally, this “warm up” time is about the same time it takes to write a report to StrongMail support about what’s going on.
To apply the updates, login to the admin:
https://strongmail.mydomain.com:9443/code/login.cgi
on the left hand navigation, there is a link called “Live Updates”. Follow the instructions for an Online Installation.

Due to the restart, I try to perform this update and restart about once a quarter. We have random issues with our transactional mailings being deleted when SM gets restarted. This is very inconsistent behavior. However, I have had the most luck NOT using the GUI and instead using the command line. I avoid this GUI at all costs. Basic tasks and monitoring can be done from here, but don’t put full dependence on it…


We do not use Message Studio (they try to bundle and license it with everything)
All StrongMail installations start in the /data1 directory
StrongMail offers a “SHAC” (StrongMail High Availability Cluster) which I’ve had some good experience with. This uses DRBD and Linux HA for automatic failover. The /data1 directory is the one being replicated (you will see that it isn’t mounted on the non-active SM server).

Following a failover event, the logprocessor WILL NOT start properly and HAS to be restarted. This is a known bug. The only way to ensure that logprocessor is functioning properly following a failover from one node to another is to manually stop/start the logprocessor process using the following commands:

#/data1/strongmail/strongmail-mta/sm-server logprocessor stop
#/data1/strongmail/strongmail-mta/sm-server logprocessor start

There are 2 “master” scripts to start/stop/restart/status the StrongMail services:

[[email protected] data1]# /data1/strongmail/strongmail-eas/sm-client status
[[email protected] data1]# /data1/strongmail/strongmail-eas/sm-client status
-------------------------------------------------------------------
-- StrongMail Client VERSION: 5.2.1.7 --
-----------------------------------------------------------------
smclient-scheduler: [ RUNNING ]
smclient-trackhttpd: [ RUNNING ]
smclient-httpd: [ RUNNING ]
logcollector: [ DISABLED ]
strongmail-logprocessor: [ RUNNING ]
strongmail-dataprocessor: [ RUNNING ]
strongmail-messageassemblyserver: [ RUNNING ]
strongmail-etlagent: [ RUNNING ]
-------------------------------------------------------------------

…Followed by this is a list of the Active Batch Mailings and Active Transactional Mailings

[[email protected] data1]# /data1/strongmail/strongmail-mta/sm-server

[[email protected] data1]# /data1/strongmail/strongmail-mta/sm-server status
-------------------------------------------------------------------
-- StrongMail Server VERSION: 5.2.1.7
-------------------------------------------------------------------
smserver-named:                                            [  RUNNING  ]
goodmail-proxy:                                            [  DISABLED  ]
strongmail-server:                                         [  RUNNING  ]
smserver-logserver:                                        [  DISABLED  ]
strongmail-logprocessor:                                   [  RUNNING  ]
strongmail-dataprocessor:                                  [  RUNNING  ]
strongmail-messagequeue:                                   [  DISABLED  ]
-------------------------------------------------------------------

If space starts filling up, the first thing to check is that the log processor is running and cleaning the logs correctly (/data1/strongmail/log). The next thing that I would check is if the old batch mailings are getting properly cleaned out (.db files that don’t start with “Trans_<site>.db in /data1/strongmail/data/databases).

If the drive starts to fill up, you will want to ensure that old batch mailings are not hanging around. We currently have a 3 week retention policy as defined in /data1/strongmail/config/strongmail-logprocessor.conf

Each batch mailing creates 3 files in 3 separate directories. If mailings aren’t being cleaned up properly you will see files in any of these directories. Prior to version 5.2, we had to clean these up ourselves via a bash script. This may have also been caused by how we were using the API to create the mailings.
/data1/strongmail/data/databases/<filename>.db
/data1/strongmail/data/mailings/<filename>.cfg
/data1/strongmail/data/messages/<filename>.txt (and/or .html)
Files in these directories that start with “Trans_” are for transactional mailings and should not ever be removed.

Again, the most likely cause for them not being deleted is the Dataprocessor process. The dataprocessor process needed to be stopped and restarted.You must run a full stop on the process, kill any rogue dataprocessor process,and start the dataprocessor.The deletions started at the time setup in the dataprocessor config file (/data1/strongmail/config/strongmail-dataprocessor.conf)

I hope these tips help you to master StrongMail administration. Please leave any questions or suggestions on further StrongMail topics in the comments area

Rebuilding a MySQL Slave

This is the general process to rebuild a slave. In this example, we are rebuilding a database called DBX03.
Stop MySQL on DBX03 and remove all of the current data.  Depending on the reason for the rebuild, you may also want to remove the mysql database folder. Wiping clean the /DBDATA/mysql/* files would do the trick.
Grab the most recent snapshot of the data from another server. Preferably a slave and we would rather not take a snapshot off of a master db due to the increased disk utilization as we copy the data off. If the database server that you’re rebuilding takes frequent snapshots, you may also use that data (we assume that DBX03 does not take any snapshots). Note the SLAVE STATUS output from the snapshot script. This information is mandatory to start the slave at the correct position
Mount the snapshot and copy over the data to DBX03. (rsync -azvpP –stats –delete slave-svr:/mnt/DB_SNAP/ /DBDATA/mysql/ ). It would be smart to stop the snapshot script from running again so it doesn’t interfere with your work.
Once the rsync is done, on DBX03, you need to cleanup the unnessesary files. These include: relaylog files, error log, master.info, mysql.pid, mysql.sock, binlog files, relay-log.info, slow query logs, etc
Check that the permissions are correct for all files and folders (mysql should be the user and group owner). Ensure that the proper log folders have been created (defined in my.cnf)
Start MySQL on DBX03
Tail the end of the error log to ensure that MySQL starts up properly. It should take a few minutes to replay the innodb logs (assuming that the snapshot wasn’t taken while the database was shutdown). This will also notify you immediately if any necessary files or folders are missing so read the output carefully
If all started up correctly and the necessary logs have been replayed on DBX03 it’s time to configure the slave. This information is necessary for this to be configured correctly and can be found in the slave status of the snapshot log. For example, the slave status section of the snapshot log should look like this if taken off of a slave:

mysql> show slave status;
 stdClass Object
 (
 [Slave_IO_State] =>
 [Master_Host] => master.mydomain.net
 [Master_User] => repl_usr
 [Master_Port] => 3306
 [Connect_Retry] => 60
 [Master_Log_File] => master-binlog.001439
 [Read_Master_Log_Pos] => 899027494
 [Relay_Log_File] => slave-relaylog.004518
 [Relay_Log_Pos] => 899027022
 [Relay_Master_Log_File] => master-binlog.001439
 [Slave_IO_Running] => No
 [Slave_SQL_Running] => No
 [Replicate_Do_DB] =>
 [Replicate_Ignore_DB] => mysql
 [Replicate_Do_Table] =>
 [Replicate_Ignore_Table] =>
 [Replicate_Wild_Do_Table] =>
 [Replicate_Wild_Ignore_Table] =>
 [Last_Errno] => 0
 [Last_Error] =>
 [Skip_Counter] => 0
 [Exec_Master_Log_Pos] => 899026874
 [Relay_Log_Space] => 899027841
 [Until_Condition] => None
 [Until_Log_File] =>
 [Until_Log_Pos] => 0
 [Master_SSL_Allowed] => No
 [Master_SSL_CA_File] =>
 [Master_SSL_CA_Path] =>
 [Master_SSL_Cert] =>
 [Master_SSL_Cipher] =>
 [Master_SSL_Key] =>
 [Seconds_Behind_Master] =>
 [Master_SSL_Verify_Server_Cert] => No
 [Last_IO_Errno] => 0
 [Last_IO_Error] =>
 [Last_SQL_Errno] => 0
 [Last_SQL_Error] =>
 [Replicate_Ignore_Server_Ids] =>
 [Master_Server_Id] => 1001
 )

These values can be used to populate the required data to start the slave.

CHANGE MASTER TO MASTER_HOST = '<master_host_ip>'; #IP of the master db server which will send binlogs
CHANGE MASTER TO MASTER_PORT = <master_host_port>; #Port of the master db server
CHANGE MASTER TO MASTER_USER = '<master_host_repl_username>'; #username as defined on master db
CHANGE MASTER TO MASTER_PASSWORD = '<master_host_repl_pwd>'; #password as defined on master db
CHANGE MASTER TO MASTER_LOG_FILE = '<[Master_Log_File]>'; #binlog name on master, as defined above
CHANGE MASTER TO MASTER_LOG_POS = <[Exec_Master_Log_Pos]>; #binlog location to start at. If new binlog, pos=0 works

Perform a SHOW SLAVE STATUS to confirm that the settings are all correct
Perform a START SLAVE to start the slave
You should know immediately if the slave crashes or not. The slave status will calculate the time it’s behind master and report back. If there’s a duplicate key issue then you most likely started the slave in the wrong spot but investigating will be needed to verify. You can also tail the MySQL error log to view any additional errors.

Quick commands to see Windows uptime

If you’re in the systems administration/engineering field, you are most likely as obsessed with uptime as I am. From time to time, it’s fun to see how long a server has actually been up and running. This is simple on Linux machines via the ‘uptime’ command. However, on Windows it has never been as straight forward. Here are 3 ways that you can find out how long a Windows box has been up.

Open a cmd prompt (Start->Run->cmd) and type the following.

C:\>net statistics server | Find "since"
Statistics since 10/24/2011 8:43:24 AM

 

C:\>systeminfo | find "Time"
System Boot Time:          10/24/2011, 8:42:07 AM

Lastly, you can open the task manager (Alt+Shift+Esc, or Alt+Ctrl+Delete, or Start->Run->cmd->taskmgr). Under the “Performance” tab you will see a counter of system uptime.

Switching MySQL Masters

In the event of a disaster happening to a master db, the last resort is to fail back that master to a slave. This should be an absolute last resort due to the complexity of the issue.
This could also be used as a method to switch masters for a big database change, such as adding an index to a table. This method can be used to minimize downtime from such a big change.

The first variable that needs to be noted is “log-slave-updates”
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_log-slave-updates
When log-slave-updates=1, the slave is going to log ALL queries that come to it because it’s a slave in it’s binlog. In this case, you will need to be more careful. If promoting this slave to a master, you need to make 100% sure that no more updates are going to come through to it as a slave. Resetting the slave status and removing the slave configuration is probably the easiest way to ensure this. If you want to daisy-chain a slave off of another slave server, this paramater must be set so all replicated queries from the original master can trickle down the chain.
When log-slave-updates=0, only queries for this specific server will be logged in the server’s binlog. This is most convenient when needing to quickly set this slave as the master and then point another slave to it because the only updates being logged to the binlog are those that are actually executed on this server without it being a slave.

Now, to actually promoting new masters…
In this scenario, we will pretend that the master db is corrupt and we now need to promote a new one.
First, check all slaves to see which one is the most caught up. If they are all caught up, then determine which one could be used with additional slaves attached to it the quickest (eg. log-slave-updates=1 as mentioned above). If a slave has another slave running off of it already, this is perfect.
Make sure that the slave that you’re about to promote isn’t read-only (it should be read-only until you change it). Ensure this change is also made in the my.cnf file so the variable is set the next time this instance gets restarted.
Next I usually shut down the master mysql instance for safety.
For  sanity, I issue a “FLUSH LOGS” statement on the new master. This will roll the server to a new, fresh binlog which will be easy to start new slaves from. Take note of this new binlog that was just rolled to.
Now change all hosts files to set the new MASTER-IP record
If a new slave is already up and running from this db, then also change the hosts entry for SLAVE
Assuming that all other slaves were caught up and at the same position as this one, reset those slave’s configuration now pointing to the newly promoted master and the binlog that you just started. If the slave isn’t caught up, give it some time to catch up and get to the correct position before issuing these commands. If this isn’t followed exactly you will run into duplicate key issues and/or missing and inconsistent data

——————————————————————————

In the case that you just want to switch a slave’s master (eg. the master didn’t die), follow these steps. In this example I used DB02:3307 and DB02:3306 databases
I switched DB02:3307’s master to be DB02:3306. The process for doing this without needing to rebuild the entire slave is very sensitive, but only requires a few steps. Assume that DB02:3307 is currently replicating off of a server called DB01. Now, if DB01 completely dies, these slaves won’t have to be rebuilt and DB02 will become the master.

First, I set DB02:3306 and DB02:3307 slaves to stop at a the same specific binlog + pos:
mysql-DB02:3306> START SLAVE UNTIL MASTER_LOG_FILE = ‘db01-binlog.000722’, MASTER_LOG_POS=430000000;
mysql-DB02:3307> START SLAVE UNTIL MASTER_LOG_FILE = ‘db01-binlog.000722’, MASTER_LOG_POS=430000000;

Then, once the slaves stop I know that both of the slaves were at the exact same location. This is crucial.
The next step isn’t necessary, but I do it for ease and safety. On DB02:3306 I issue a “FLUSH LOGS;” statement which will close and reopen all log files. For the binlogs (which we are most interested in) it will start a new binlog so we can start at position 0 of the newest binlog on DB02:3306. Alternatively, you could just do a “SHOW MASTER STATUS;” to get the current binlog information for that host.
Once I’ve found and noted the new master binlog file name and position, I can start that slave back up.
Next, I go over to DB02:3307 to reset the configuration. This time with DB02:3306 as the MASTER_HOST, and the MASTER_LOG_FILE + MASTER_LOG_POS that were noted previously.

 

Helpful MySQL Commands, tips and tricks

I’m pretty much brain dumping now so I apologize if I leave open any questions. Post to the comments if you would like for me to go into further detail on any of these…

I would argue that all of these commands and tips need to be mastered to be an effective SysAdmin DBA.


The configuration formula… The estimated amount of memory that will be allocated to MySQL. This formula should be used when initially configuring a server while still leaving some for the OS (assuming that this is a dedicated MySQL database server).

KBS + IBP + (RBS + SBS + BLCS) * MC = Physical RAM – OS requirements

KBS -> Key Buffer Size
IBP -> InnoDB Buffer Pool size
RBS -> Read Buffer Size
SBS -> Sort Buffer Size
BLCS -> Binlog Cache Size
MC -> Max connections
If a DB server begins to use a lot of SWAP memory, this is very bad. The disk thrashing that is occurring as a result of the SWAP being used has a reputation of bringing the entire db to a halt. I have witnessed this in several environments. If a high transaction MySQL db is using SWAP memory, immediate action should be taken before it gets worse.


When this command is issued, statements won’t be logged to the binlog (or replicated) in this session.

mysql> SET SQL_LOG_BIN = 0;

The skip current slave query statement. BE CAREFUL! Skipping a slave statement without a FULL, in-depth investigation can be VERY damaging and cause inconsistencies that may lead to a full rebuild of that data set. If you’re skipping a statement on the slave be sure that you know what you’re doing!
With that said, this definitely can come in handy if a bad statement gets replicated and breaks a slave in your replication tree.

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

These are the commands to start up new slave. I’m assuming that you have the correct data in place. I will have a separate post on how to build a new slave with full details later.

CHANGE MASTER TO MASTER_HOST = '';
CHANGE MASTER TO MASTER_PORT = ;
CHANGE MASTER TO MASTER_USER = '';
CHANGE MASTER TO MASTER_PASSWORD = '';
CHANGE MASTER TO MASTER_LOG_FILE = '';
CHANGE MASTER TO MASTER_LOG_POS = ;

#Optional – using SSL to secure replication

CHANGE MASTER TO MASTER_SSL=1;
CHANGE MASTER TO MASTER_SSL_CA = '/ca-cert.pem';
CHANGE MASTER TO MASTER_SSL_CAPATH = '/ssl/';
CHANGE MASTER TO MASTER_SSL_CERT = '/client-cert.pem';
CHANGE MASTER TO MASTER_SSL_KEY = '/client-key.pem';
START SLAVE;

Issue a mysqldump only of the stored procedures

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt id > triggers.id.sql

Issue a mysqldump only of the table layout and schema (NO DATA)

mysqldump --all-databases --no-data > db_layout.sql

If you have a mysqldump file and want to insert it into a database, you can just pipe it like this:

mysql -h  -u  -p < file.sql

Issue a mysqldump for specific WHERE clause to output.sql file

mysqldump --where="id > 1" > output.sql

Drop partition then immediately re-add it

mysql> ALTER TABLE email_queue DROP PARTITION p01;
mysql> ALTER TABLE email_queue ADD PARTITION (PARTITION p07 VALUES IN (01));

Change the query cache size on the fly

mysql>set global query_cache_size=10*1024*1024;

Then flush the active query cache

mysql> flush query cache;reset query cache;

Decode row based relay logs. I’ll have another post on how to debug row based replication errors. They’re definitely not as straight-forward as statement based replication issues.

mysqlbinlog mysql_server-relay-bin.000005 --base64-output=decode-rows -v > decoded.log

Other Configuration Notes

  • innodb_log_buffer_size -> InnoDB only keeps data round log buggers for ~1 second
  • possibility to decrease disk use dramatically by innodb_flush_method=O_DIRECT and innodb_double_write=OFF
  • innodb_thread_concurrency = twice # of CPU’s. could save some for MyISAM if it’s used
  • innodb_data_file_path -> if not big enough, it could create too many fragments
  • mysqldumpslow slow_query.log -> full report of SQL statements in slow query log
  • sync_binlog = 1 will send the binlogs to slaves more frequently. potential to dramatically improve slave performance
  • OPTIMIZE = defragment
  • ANALYZE = rebuild index (sorting)
  • normalization=deduplication
  • myisam_sort_buffer_size -> not only for myisam. can be set at session, buffer is released when finished
  • max_heap_table_size -> same or more than tmp_table size
  • max_heap_table_size ->if using BLOB or TEXT datatypes, don’t go above 64Mb. start at
  • 16Mb. if no BLOB or TEXT, created_temp_disk_table should be equal to 0
  • ‘heap’ never goes to the SWAP. heap covers memory tables and tmp memory tables
  • key_buffer_size in mysql 5.0 doesn’t use more than 4Gb even if it’s assigned it
  • myisam_sort_buffer_size -> keep # high when doing analyze. can set this on the fly
  • convert TEXT to varchar to avoid tmp tables
  • FLUSH TABLES; will help to decrease the time for innodb to shutdown