Activities

April 2010
M T W T F S S
« Mar   May »
 1234
567891011
12131415161718
19202122232425
2627282930  

Mysql replication : on Centos

Here I’m going to replicate a database on the master DB server to a slave server.

On  Master DB server,

1. Identify the database to be synchronized.
2.  Edit /etc/my.cnf in between [mysqld].

log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=securedb
server-id=1
Then restart the mysql daemon,
#service mysqld restart
make sure that the file is created automatically and owned by the mysql user. If not pls create the same.
Now we are going to create slave user on mysql and grant privileges for the replication.
Login to mysql shell,
mysql>grant replication  slave on *.* to replica@"%" identified by 'replica';
mysql>USE securedb;
mysql>FLUSH TABLES WITH READ LOCK;
mysql> show master status;

+-------------------+----------+-------------------+------------------+
| File              | Position | Binlog_Do_DB      | Binlog_Ignore_DB |
+-------------------+----------+-------------------+------------------+
| mysqld-bin.000001 |       98 | securedb,securedb |                  |
+-------------------+----------+-------------------+------------------+
1 row in set (0.00 sec)
mysql> show master status;+-------------------+----------+-------------------+------------------+| File              | Position | Binlog_Do_DB      | Binlog_Ignore_DB |+-------------------+----------+-------------------+------------------+| mysqld-bin.000001 |       98 | isterdad,isterdad |                  |+-------------------+----------+-------------------+------------------+1 row in set (0.00 sec)

Now we need to log in to Slave server and restore the   latest “securedb” from the Master. I do recommend that Mysql dump method rather than using replication commands ( LOAD MASTRER DATA) to do that which may cause large network traffic and requires a little  time to complete it.

Edit /etc/my.cnf and add the following entries,
server-id=2
master-host=192.168.0.25
master-user=replica
master-password=replica
master-connect-retry=60
replicate-do-db=securedb

Restart mysql server
#service mysqld restart

The next step is, I’m going stop the slave service and forcefully start the first replication against my “securedb”
mysql> SLAVE STOP;
mysql>CHANGE MASTER TO MASTER_HOST=’192.168.0.25′, MASTER_USER=’replica’, MASTER_PASSWORD=’replica’, MASTER_LOG_FILE=’ mysqld-bin.000001′, MASTER_LOG_POS=98;
mysql>SLAVE START;

Where,
MASTER_HOST = IP address or hostname of the master
MASTER_USER = user we granted replication privileges on the master.
MASTER_PASSWORD = password of MASTER_USER on the master.
MASTER_LOG_FILE = file when you ran SHOW MASTER STATUS; on the master.
MASTER_LOG_POS = position MySQL when you ran SHOW MASTER STATUS; on the master.

Some administrative commands for replication ( for MASTER),

1. mysql> SHOW BINARY LOGS;
which show the all the binary logs logged on the server.
2. mysql> PURGE BINARY LOGS BEFORE NOW();
Which will delete all the logged binary files created before today.
3.  SHOW BINLOG EVENTS;
mysql> SHOW BINLOG EVENTS;
+——————-+—–+————-+———–+————-+—————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————-+—–+————-+———–+————-+—————————————+
| mysqld-bin.000001 | 4 | Format_desc | 1 | 98 | Server ver: 5.0.77-log, Binlog ver: 4 |
+——————-+—–+————-+———–+————-+—————————————+
1 row in set (0.00 sec)

a. Log_name : The binary log file name for this event
b. Pos : The binary log position of the event
c. Event_type : The event type, for example, Query_log_event
d. Server_id : The original server id of the event
e. End_log_pos : The end log position
f: Info : Information about the event. For query log events,
4. show binlog eventsG
master> show binlog eventsG
*************************** 1. row ***************************
Log_name: master1bin.
000001
Pos: 4
Event_type: Format_desc
Server_id: 10
End_log_pos: 106
Info: Server ver: 5.1.23rclog,
Binlog ver: 4
Info: Server ver: 5.1.23rclog,
Binlog ver: 4
*************************** 2. row ***************************
Log_name: master1bin.
000001
Pos: 106
Event_type: Query
Server_id: 10
End_log_pos: 197
Info: use `test`; create table t1 (a char(40))
*************************** 3. row ***************************
Log_name: master1bin.
000001
Pos: 197
Event_type: Query
Server_id: 10
End_log_pos: 301
Info: use `test`; insert into t1 values (‘Stuck In A Loop’)

5. Analyzing binary log with mysqlbinlog command. This command is very helpful to read the binary log when the server is in offline.

[root@rc-040 ~]# mysqlbinlog /var/lib/mysql/mysqld-bin.000001
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100411 12:43:16 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.77-log created 100411 12:43:16 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Administrative information for slave
1. Enabling mysql slave logs
add the following lines on the “/etc/my.cnf” under [mysqld] section.

relay-log-index = slave-relay-bin.index
relay-log = slave-relay-bin

2. Master.info and relay-log.info
Master.info : showing the server credential details which slave used to contact during the replication.

[root@web-test1 installation]# cat   /var/lib/mysql/master.info
14
mysqld-bin.000001
98
192.168.0.40
replica
replica
3306
60
0

b. relay-log.info : which showing the position of last executed binary log in slave against the master binary log location.

[root@web-test1 installation]# cat   /var/lib/mysql/relay-log.info
/var/run/mysqld/mysqld-relay-bin.000001
4
mysqld-bin.000001
98

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>