Mysql Replication Useful Commands

SHOW BINLOG EVENTS

SHOW BINLOG EVENTS
[IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
e.g:

mysql> show binlog events in 'bin.000017' from 36383578 limit 50\G

Shows the events in the binary log.

—————————————————————————————-
show binary logs;
Lists the binary log files on the server.

—————————————————————————————-

PURGE BINARY LOGS TO 'bin.000015';
PURGE BINARY LOGS BEFORE '2010-03-01 00:00:00';
PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or date. BINARY and MASTER are synonyms. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list.

—————————————————————————————-
Recovery from binary logs:

mysqlbinlog --stop-position="368312" /data/mysql/bin.000015 | mysql -u root -p
mysqlbinlog --start-position="368315" /data/mysql/bin.000015| mysql -u root -p

mysqlbinlog --stop-date="2010-03-01 19:00:00" /data/mysql/bin.000015 | mysql -u root -p
mysqlbinlog --start-date="2010-03-01 00:00:00" /data/mysql/bin.000015 | mysql -u root -p

mysqlbinlog /data/mysql/bin.000015 > /tmp/mysql_restore.sql

Mysql Master Master Repliction

I’m going to setup MySQL Multi-Master Replication, here are my systems information:

DB-1 : 172.20.1.6 —> (Master 1 / Slave 2)
DB-2 : 172.20.1.8 —> (Master 2 / Slave 1)
# uname -a
Linux DB-1.linax.wordpress.com 2.6.18-164.11.1.el5 #1 SMP Wed Jan 20 07:32:21 EST 2010 x86_64 x86_64 x86_64 GNU/Linux

also I’m using MYSQL community Edition, you can find rpm’s here :
# wget http://mirrors.bevc.net/mysql/Downloads/MySQL-5.1/MySQL-devel-community-5.1.41-0.rhel5.x86_64.rpm
# wget http://mirrors.bevc.net/mysql/Downloads/MySQL-5.1/MySQL-client-community-5.1.41-0.rhel5.x86_64.rpm
# wget http://mirrors.bevc.net/mysql/Downloads/MySQL-5.1/MySQL-server-community-5.1.41-0.rhel5.x86_64.rpm

OK, lets start:

I configured MySQL’s my.cnf file to use the tuning setup from the my-medium.cnf, which should give us good tuning parameters for our load at this point. Then I added the pieces necessary for replication in both DB-1 and DB-2:

Warning : I’ve changed default path of MYSQL data directory to /data/mysql, so you need to change it to your data directory. (default path is /var/lib/mysql/)

————————————————-
1. On Both Servers, you need to create a replication slave account in mysql:

mysql>GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.20.1.%' IDENTIFIED BY 'replication';
mysql>GRANT RELOAD ON *.* TO 'replication'@'172.20.1.%';
mysql>GRANT SUPER ON *.* TO 'replication'@'172.20.1.%';
mysql>FLUSH PRIVILEGES;

————————————————-

2. DB-1 my.cnf configuration :

server-id = 1
log-bin = /data/mysql/bin.log
log-bin-index = /data/mysql/log-bin.index
log-error = /data/mysql/error.log

relay-log = /data/mysql/relay.log
relay-log-info-file = /data/mysql/relay-log.info
relay-log-index = /data/mysql/relay-log.index
relay-log-space-limit = 6G

auto_increment_increment = 10
auto_increment_offset = 1
master-host = 172.20.1.8
master-user = replication
master-password = replication

replicate-do-db = MYDB
binlog-ignore-db = mysql
# log_bin_trust_function_creators = 1
expire_logs_days = 15
max_binlog_size = 10G

————————————————-
3. DB-2 my.cnf Configuration :

server-id = 2
log-bin = /data/mysql/bin.log
log-bin-index = /data/mysql/log-bin.index
log-error = /data/mysql/error.log

relay-log = /data/mysql/relay.log
relay-log-info-file = /data/mysql/relay-log.info
relay-log-index = /data/mysql/relay-log.index
relay-log-space-limit = 6G

auto_increment_increment = 10
auto_increment_offset = 2
master-host = 172.20.1.6
master-user = replication
master-password = replication

replicate-do-db = MYDB
binlog-ignore-db = mysql
# log_bin_trust_function_creators = 1
expire_logs_days = 15
max_binlog_size = 10G

————————————————-
4. Another thing that you should take care, when you setup Mysql Replication is Binary and Relay logs size.
as you can see in my configuration, I have set a 6GB space limit for relay-logs and 15 days expiry time for binary logs .
for purging binary-logs add a cron job like this :

0 1 * * * mysql -uroot -ppass -e "PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 15 DAY);"

this will purge your binary logs, older than 15 days .
————————————————-

I only wanted to replicate one database, so I listed it as the example shows with replicate-do-db. Since I intend on allowing for various nodes to be added, I set auto_increment_increment to 10, which will allow for 10 different servers, all of which could be read-write masters if I wanted them to be. In this case, my plan is only for 2, so this will give me a lot of room to grow. in other words :

When you have N master servers:

Set auto_increment_increment to N on each master.
Set each of the N masters to have a different auto_increment_offset, for example set to 1 in the first node, 2 in the second, and so on.

Using this two variables as described in the manual, you can ensure that all nodes in your replication array will use different sequences of auto-incrementing numbers. For example, using auto_increment_increment = 10 and auto_increment_offset=3, the numbers generated when inserting three records will be 3, 13, 23. Using 10, 7, you’ll get 7, 17, 27, and so on.

That’s it, now just start/restart mysql in both side.
you can check replication status with this mysql commands:

mysql> show master status;
mysql> show slave status\G

Perl: substr

Extracts a substring out of EXPR and returns it.

* substr EXPR,OFFSET,LENGTH,REPLACEMENT
* substr EXPR,OFFSET,LENGTH
* substr EXPR,OFFSET

e.g:
1. my $s = “The black cat climbed the green tree”;
2. my $color = substr $s, 4, 5; # => black
3. my $middle = substr $s, 4, -11; # => black cat climbed the
4. my $end = substr $s, 14; # => climbed the green tree
5. my $tail = substr $s, -4; # => tree
6. my $z = substr $s, -4, 2; # => tr

Original Document => http://perldoc.perl.org/functions/substr.html

Mysql Backup Script

mysql_backup.sh :
——————————————————————————
#!/bin/sh
DATESTAMP=$(date +%F)
DIR=/files/MYSQL/backup/
DB_USER=backup
DB_PASS=’backup’
HOSTS=/files/MYSQL/HOST_LIST

# remove backups older than $DAYS_KEEP
DAYS_KEEP=30
find $DIR -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null

# create backups securely
umask 006

for HOST in $(egrep ^[^#] $HOSTS | cut -f 1);
do
MYSQLDUMP=”/usr/bin/mysqldump –lock-tables=false -u $DB_USER -p”$DB_PASS” -h$HOST”
HOSTNAME=$(grep $HOST $HOSTS | cut -f2)
if [ ! -d $DIR$HOSTNAME ]; then
mkdir $DIR$HOSTNAME
fi
# list MySQL databases and dump each
DB_LIST=$(mysql -h $HOST -u $DB_USER -p”$DB_PASS” -Bs -e’show databases;’ | egrep -v “information_schema|test” | xargs )

for DB in $DB_LIST;
do
FILENAME=$DIR$HOSTNAME/$DB-$DATESTAMP.sql.gz
$MYSQLDUMP $DB | gzip > $FILENAME
done
done
——————————————————————————
HOST_LIST:
########################################################
# GRANT Select,USAGE ON *.* to backup@’backup_server’ IDENTIFIED BY ‘backup’;
# FLUSH PRIVILEGES;
########################################################
#ip name
192.168.2.26 srv1
192.168.2.25 mailserver
192.168.2.11 radius
192.168.11.127 cacti

HTML Redirect

To create an HTML redirect page, you use the HTML meta tag, along with the ‘http-equiv’ and ‘content’ attributes.

<META HTTP-EQUIV="refresh" CONTENT="seconds;URL=the-other-url">

E.G:

<HTML> <HEAD> <META HTTP-EQUIV="refresh" CONTENT="0;URL=https://linax.wordpress.com"> </HEAD> <BODY> </BODY> </HTML>

Gcc and Header issues

http://gcc.gnu.org/gcc-4.3/porting_to.html

The missing headers:

If missing Then include this header
find, for_each, sort <algorithm>
ostream_iterator, istream_iterator <iterator>
auto_ptr <memory>
typeid <typeinfo>
isalnum, toupper <cctype>
INT_MIN, INT_MAX, RAND_MAX <climits>
printf <cstdio>
atoi, free, rand, exit <cstdlib>
EXIT_FAILURE <cstdlib>
strcmp, strdup, strcpy, memcpy <cstring>

Removal of Pre-ISO headers

Various backwards and deprecated headers have been removed.

If missing Then include this header
<algobase.h> <algorithm>
<algo.h> <algorithm>
<alloc.h> <memory>
<bvector.h> <vector>
<complex.h> <complex>
<defalloc.h> <memory>
<deque.h> <deque>
<fstream.h> <fstream>
<function.h> <functional>
<hash_map.h> <tr1/unordered_map>
<hashtable.h> <tr1/unordered_map> or <tr1/unordered_set>
<heap.h> <queue>
<iomanip.h> <iomanip>
<iostream.h> <iostream>
<istream.h> <istream>
<iterator.h> <iterator>
<list.h> <list>
<map.h> <map>
<multimap.h> <map>
<multiset.h> <set>
<new.h> <new>
<ostream.h> <ostream>
<pair.h> <utility>
<queue.h> <queue>
<rope.h> <ext/rope>
<set.h> <set>
<slist.h> <ext/slist>
<stack.h> <stack>
<streambuf.h> <streambuf>
<stream.h> <iostream>
<tempbuf.h> <ext/memory>
<tree.h> <ext/rb_tree> or <ext/pb_ds/assoc_container.hpp>
<vector.h> <vector>

VMware ESX, killing a virtual machine that won’t die !

Sometimes the Virtual Center won’t do the job. You virtual machine has hung and you need to kill it. Here are 2 examples of how you can kill the vm from within the service console:

1. The ‘VMWARE-CMD’ command

* Log on to the service console and issue the following command ‘vmware-cmd /vmfs/volumes///.vmx stop’ you must not use the friendly datastore name. If you need to know the location of all vm’s type ‘VMWARE-CMD -l’ that will list on vm’s and the location for the corresponding vmx file.
* If that fails, then try it with the hard option, ‘vmware-cmd /vmfs/volumes///.vmx stop hard’ this command will just try and kill it without shutting it down.

2. Kill it using the PID command

* Run the following command: ps auxfww | grep to locate the correct PID of the virtual machine, the first number to appear in the output is your vm’s PID. Use the PID number to terminate the process by issuing kill -9