Search This Blog

Wednesday, November 24, 2010

Command to monitor mysql replication slave status

watch 'mysql -u xxxx -p xxxx -e "show slave status\G" | grep "Last_"'

Mysql processlist monitoring

To check running queries

watch 'mysqladmin -u xxxx -p xxxx processlist | grep -v Locked | grep -v Sleep | grep -v Waiting'

To check Locked queries + count for each query

watch 'mysqladmin -u xxxx -p xxxx processlist | grep Locked | cut -d"|" -f9 | sort | uniq -c'

Change next AUTO_INCREMENT value in mysql table

The command to change next AUTO_INCREMENT value in mysql table is

ALTER TABLE <<TABLE_NAME>> AUTO_INCREMENT = '0000';

Common myisamchk options

myisamchk -mfq xxxx.MYI


  -m, --medium-check  Faster than extend-check, but only finds 99.99% of
                      all errors.  Should be good enough for most cases.

  -f, --force         Restart with '-r' if there are any errors in the table.
                      States will be updated as with '--update-state'.

  -q, --quick         Faster repair by not modifying the data file.
                      One can give a second '-q' to force myisamchk to
                      modify the original datafile in case of duplicate keys.
                      NOTE: Tables where the data file is currupted can't be
                      fixed with this option.

Getting the data Sync'ed between master & slave servers in mysql replication

In order to get the database.tables sync'ed between master and slave server, follow these guidelines

  1. Shutdown mysql daemon on master server
  2. Shutdown mysql daemon on slave server
  3. Take dump of database.tables on master server
  4. Take backup of database.tables on slave server
  5. Load dump (of master) on slave
  6. Start mysql daemon on master server
  7. Take a note of new binlog filename on master server
  8. Update the master binlog filename on slave server
  9. Start mysql daemon on slave server

Find broken symlinks in linux

In order to find broken links in linux, we will use find command with some shell scripting

for i in `find ./ -type l`; do [ -e $i ] || echo $i; done