Shantanu's Blog

1) Check if the server-id variable is set in my.cnf on both master and slave. If the IP is 192.178.90.51 then the id can be

server-id=1789051
# config on master server to start binary logs
log-bin=/var/log/mysql/mysql-bin.log
max_binlog_size=1024M
expire_logs_days=40

2) Take backup of master server using the following command or set it in a cron:

50 13 * * * /usr/bin/mysqldump -uroot -pPassWord dbName --routines --single-transaction --master-data=2 > /backup/dumpmysqldaily/ACCNT`date +'%d-%b-%Y-%H-%M'`.sql 2> /backup/dumpmysqldaily/ACCNT_err.txt

3) Create a "slave_user" user for replication purpose on the slave.
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_user';

4) Copy the dump file from master to slave. Restore the dump on slave server
mysql -uroot -pPassWord myDBname < /root/ACCNT07-Jan-2010-11-30.sql
Do not forget to turn off binary/ slow/ general logs of slave server. The logging may slow down the restoration process.

5) Open the dump file and check the log position
$ more /root/ACCNT07-Jan-2010-11-30.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=110656468;

6) On the slave server point to the master:
CHANGE MASTER TO MASTER_HOST='111.222.333.444', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=110656468

7) If you want to skip a line, use the following set of commands.
stop slave sql_thread;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status\G

8) If you want a command to be executed only on the master and not on the slave then...
set sql_log_bin = 0;
#A command that you don't want to be passed on to the slave.
set sql_log_bin = 1;
_____

When you want to drop the table from master, keep it on the slave for backup purpose.
set sql_log_bin = 0;
drop table if exists temp_table;

If there are no issues, the next day execute the drop table statement again on the master and the slaves too will drop it.

9) You can replicate to different DB on the slave using the following:
replicate-rewrite-db=from_name->to_name

10) You can check the binary file size on master and remove older files
show binary logs ;
PURGE BINARY LOGS TO mysql-bin.103;


11) If you shut the slave server without stopping the slave first, the slave will loose the track of the master position. Open the error log to check the binary file and it's position.

tail -100 /var/log/mysqld.log

091222 11:15:35 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000092', position 965379003
091222 11:15:35 [Note] Error reading relay log event: slave SQL thread was killed
091222 11:15:41 [Note] /usr/libexec/mysqld: Normal shutdown

091222 11:15:41 InnoDB: Starting shutdown...
091222 11:15:44 InnoDB: Shutdown completed; log sequence number 1 453745375
091222 11:15:44 [Note] /usr/libexec/mysqld: Shutdown complete

091222 11:15:44 mysqld ended

091222 11:15:45 mysqld started

Now change the master pointer as shown below:

CHANGE MASTER TO MASTER_HOST='111.222.333.444', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE='mysql-bin.000092', MASTER_LOG_POS=965379003

12) tune up the slave:
#If you do not want a table or database to be replicated, add it to my.cnf of the slave.
replicate-wild-ignore-table=test.%
# where xxx is 'all' or a comma separated list of error codes
slave-skip-errors=all
# no user other than super privileged can write
read-only
# you can alter hostname without affecting replication
relay-log = /var/log/mysql/
# Data transferred without any problem
max_allowed_packet = 500M
skip-slave-start
# not to start the slave threads when the server starts


13) Delayed slave

Delayed slave is the slave that is stopped for some purpose.

mysql> stop slave sql_thread;

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.211
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000040
Read_Master_Log_Pos: 562693548
Relay_Log_File: mysqld-relay-bin.000025
Relay_Log_Pos: 107211908
Relay_Master_Log_File: mysql-bin.000040
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 559403550
Relay_Log_Space: 110501906
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: NULL
1 row in set (0.00 sec)


If a table or database is dropped by mistake, it can be retrieved by "playing" the binary logs until the position earlier the one in question.
304002 in this case.

# mysqlbinlog /var/log/mysql/mysql-bin.000040 > tostudy.txt

# grep -iB15 'DROP TABLE if EXISTS asdf' tostudy.txt | more

# at 303857
#091222 23:56:51 server id 50211 end_log_pos 304002 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1261506411/*!*/;
insert into allocated_luggage(etim_no,crc) values('MCT00043','135676505')/*!*/;
# at 304002
#091222 23:58:28 server id 50211 end_log_pos 304099 Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1261506508/*!*/;
DROP TABLE if EXISTS asdf/*!*/;

mysql> start slave until master_log_file = 'mysql-bin.000040' master_log_pos = '304002'

--short-form
will truncate comments from mysqlbinlog output
--read-from-remote-server
will allow you to read binary files from other servers.

skip-slave-start
# parameter in my.cnf file tells the slave server not to start the slave threads when the server starts.

14) Use matkit utility on the master to know if the master and slave data is really in sync
mk-table-checksum h=localhost,u=root,p=PassWord,P=3306 --databases dbName h=666.777.888.999,u=root,p=SlavePassWord,P=3306 --databases dbName --nocrc --count | mk-checksum-filter

15) If the slave has stopped, you need to open the error log file and find out the position from where it needs to be restored.

# cat slave_start.sh
#!/bin/sh
# finding the slave position where it stopped and restart from there
binfile=`grep "Slave I/O thread exiting, read up to log" /var/log/mysqld.log | tail -1 | awk '{print $12}'`
position=`grep "Slave I/O thread exiting, read up to log" /var/log/mysqld.log | tail -1 | awk '{print $14}'`
echo "CHANGE MASTER TO MASTER_HOST='111.222.333.444', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE=$binfile MASTER_LOG_POS=$position;"

_____

# Show slave status will display the correct master position and not the mysql error log
# http://code.openark.org/blog/wp-content/uploads/2014/03/slave_status_explained_lagging.png

#!/bin/sh

Master_Pass='slave_user'

Relay_Master_Log_File=`mysql -e"show slave status\G" | grep 'Relay_Master_Log_File' | awk '{print $2}'`
Exec_Master_Log_Pos=`mysql -e"show slave status\G" | grep 'Exec_Master_Log_Pos' | awk '{print $2}'`
Master_Host=`mysql -e"show slave status\G" | grep 'Master_Host' | awk '{print $2}'`
Master_User=`mysql -e"show slave status\G" | grep 'Master_User' | awk '{print $2}'`

echo "CHANGE MASTER TO MASTER_LOG_FILE='"$Exec_Master_Log_File"', MASTER_LOG_POS="$Exec_Master_Log_Pos";"

echo "CHANGE MASTER TO MASTER_HOST='"$Master_Host"', MASTER_USER='"$Master_User"', MASTER_PASSWORD='"$Master_Pass"', MASTER_LOG_FILE='"$Exec_Master_Log_File"', MASTER_LOG_POS="$Exec_Master_Log_Pos";"


16) log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C

17) log-slow-slave-statements In order to log slow queries in replicated thread

18) --dump-slave[=value]
This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. This option was added in MySQL 5.5.3.