Skip to main content

MySQL Master Slave Replication on Linux Environment



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 save;
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;"

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

Comments

Post a Comment

Popular posts from this blog

About MySQL - Database history Support and Versioning

About MySQL: MySQL was created by a Swedish company. David Axmark (left) and Michael "Monty" Widenius,  MySQL AB, founded by David Axmark, Allan Larsson and Michael "Monty" Widenius. Original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL appeared on 23 May 1995. It was initially created for personal usage from mSQL based on the low-level language ISAM, which the creators considered too slow and inflexible. They created a new SQL interface, while keeping the same API as mSQL. By keeping the API consistent with the mSQL system, many developers were able to use MySQL instead of the (proprietarily licensed) mSQL antecedent. Support: The MySQL server software itself and the client libraries use dual-licensing distribution. They are offered under GPL version 2, or a proprietary license. MySQL AB owns the copyright to the MySQL source code. This means that MySQL AB can distribute MySQL under several different l...
Post a Comment
Read more

MySQL thread error code of 1032- Translate binlog completely to avoid error

Could not execute Update_rows event on table db.table; Can't find record in 'table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's   master log mysql-bin.262297, end_log_pos 1983208 As of my knowledge the causing stop of slave SQL thread error code of 1032, due to lack of sync between Master - Slave. In our case, slave got an Update event form master, but that particular record was not available in the slave. (Of course, that was deleted on Master n Slave separately) You would get this error only if the binlog_format is set to ROW_BASED or MIXED mode. So, now check that particular binlog at that position where replication stopped with this error. When you convert the binlog with mysqlbinlog command, you may see some junk characters   where you were expecting some DMLs which caused the error(In our case its an Update statement). So you can use below command to translate binlog completely: mysqlbinlog --base64-output=DECO...
Post a Comment
Read more

MySQL Architecture -Client Server Architecture

Client/Server Overview The MySQL database system operates using a client/server architecture. The server is a central program that manages database contents, and client programs connect to the server to retrieve or modify the data. MySQL also includes non-client utility programs and scripts. MySQL Server:   This is the mysqld program that manages database and tables. Most users choose binary MySQL distribution that includes a server ready to run with the capabilities they need, but it's also possible to compile MySQL from source. Client Programs:  These are programs that communicate with the server by sending requests to it over a network connection. The server acts on each request and returns a response to the client. For example you can use the mysql client to send queries to the server, and the server returns the query results. A client program can connect locally to a server running on the same machine or remotely to a server running on a different mac...
Post a Comment
Read more