MYSQL - REPLICATION Master-Slave

Image via Wikipedia

MYSQL - Replication Master-Slave

*Using this we can replicate our mysql database server to one or more slave
* This is asynchronous , slave server need not be be connected permenantly to the master server.
* all the writes and updates occured on master server. Reads can takes place in slave box

Mysql-Master server:-
* must enable server unique ID and master bin log, Binary login is sending the master data to slave. so this is the main important thing.
* For configuring Master first we need to shutdown mysql-server and the edit my.cnf file. Details are included under [mysqld] section.
* To enable binary log using log-bin represented the name as "mysql-bin" or db-bin and serve id can mention as server-id=1(positive integer),default value is 0.
after that server needs to be reboot.

Mysql-Slave Server
* first need to specify the server ID

Replication user creation:[On Master Box]
*For replication slave need to connect to master box using a user name and password. So we need to create a user and password on Master box and grand privilege for the slave box on master box.

Setting the Master configuration on Slave
To make our slave box communicate with master box we need to setup some connection information using ' CHANGE MASTER TO"




Here I am going to describe how to database replication using Mysql.
Mysql replication is a asynchronous data transfer(ie, database on the master server are not immediately replicated to the database on the slave server).
This Replication Type is Master-Slave
A. MASTER SET UP

1. Edit /etc/my.cnf and enter the following details under mysqld
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id = 1
log-bin=db-bin
relay-log=db-relay-bin

2.enter into the mysql database

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'10.10.2.24' IDENTIFIED BY 'password';

Suppose that your slave server is '10.10.2.24' and that you want to create an account with a user name of 'replication' such that slave servers can use the account to access the master server using a password of 'password'.

3.mysql> FLUSH TABLES WITH READ LOCK;
Flush all the tables and block write statements

4.mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| db-bin.000005 | 792 | | |
+---------------+----------+--------------+------------------+

OR
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: db-bin.000005
Position: 792
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)


So MASTER Side configuration has over ..

B SLAVE SERVER CONFIGURATION

1. Edit the file /etc/my.cnf and enter the details as follows
[mysqld]
server-id=2
master-host = 10.10.3.21
master-user = replication
master-password = password
master-port = 3306

10.10.3.21 is the MASTER server IP.

These information will be stored to /var/lib/mysql/master.info

Enter into the Mysql server
2. mysql> stop slave;
3. mysql> reset slave;
4. mysql> CHANGE MASTER TO MASTER_HOST='10.10.3.21', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='db-bin.000005';

Here 10.10.3.21 is your MASTER Server IP 'replication' is the master user 'password' is the password for your master server and 'db-bin.000005' is the Master log file this one you wll get while running the command mysql> SHOW SLAVE STATUS \G; in Master server

5. mysql> START SLAVE;

6. mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.3.21
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: db-bin.000005
Read_Master_Log_Pos: 792
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 934
Relay_Master_Log_File: db-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Last_Errno: 0

Exec_Master_Log_Pos: 792
Relay_Log_Space: 1090
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No

Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

The process:- In Master Server(Alter,Delete) all details execute and 'I/O Thread' save to a log file called Binary Log(ie,db-bin.000005) And in slave(only Read) read the changes from Binary log to relay log and update to slave SQL Thread.

You can verify by checking the Seconds_Behind_Master details and Slave_IO_Running and Slave_SQL_Running

REPLICATION ERRORS and FIX

1. 'Last_IO_Error' => 'Got a packet bigger than > \'max_allowed_packet\' bytes'

This is due to 'max_allowed_packet' size. Increasing max_allowed_packet will fix the issue

Comments are welcomed

2. > show slave status \G
Slave_IO_State:
Master_Host: 10.10.210.198
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000916
Read_Master_Log_Pos: 863345946
Relay_Log_File: relay-bin.000012
Relay_Log_Pos: 374486719
Relay_Master_Log_File: mysql-bin.000916
......
;;;;;;;
;;;;;;;;

Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near FrT;@28|$9|main_init$1|1$11|cookies_tmp%0|$10|mobipocket$1|0$7|cookies%0|$11|la' at line 3' on query. Default database: 'ebooks'. Query: 'REPLACE INTO session
(module, id, contents, timestamp) VALUES
('main', 'ee1db08d8f93bc69bbe0a1dd9a9cc301', 'FrT;@28|$9|main_init$1|1$11|cookies_tmp%0|$10|mobipocket$1|0$7|cookies%0|$11|last_viewed@5|%6|$5|brand$5|model$3|sku$26|Bankes, Tim, Hatter, David$82|Lotus Notes and Domino R6 Application Development Exam Cram 2 (Exam 620, 621, 622)$10|0768661498%6|$5|brand$5|model$3|sku$0|$28|CULTURE OF HUMAN TUMOR CELLS$10|0471270628%6|$5|brand$5|model$3|sku$11|Hwang, Andy$74|Ready-to-Eat Foods: Microbial Concerns and Control Measures (Adobe Reader)$13|9781420068634%6|$5|brand$5|model$3|sku$0|$45|TRANSATLANTIC VOYAGES & SOCIOL (Adobe Reader)$13|9781409405009%6|$5|brand$5|model$3|sku$0|$51|Practical Manual of Thyroid and Parathyroid Disea
Skip_Counter: 0
Exec_Master_Log_Pos: 374486582
Relay_Log_Space: 863347015
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

FIX:
(none)> STOP SLAVE;
(none)> CHANGE MASTER TO master_log_file='mysql-bin.000916', master_log_pos=863345946;

[Here master_log_file and master_log_pos will get from your slave status .]

(none)> slave start;

3. Replication rebuild [ Rebuild from Scratch ]
Once we can not fix the replication error in any of our known ways we can do the last fix as Rebuild replication from Scratch

Please note down the following steps

on Master Server

1. flush tables with read lock; --> no write operation on databases only read operation will be available
2. reset master; ---> master will reset
3. show master status; ---> check for the out put

+------------------+-----------+
| File | Position |
+------------------+-----------+
| mysql-bin.000001 | 98 |
+------------------+-----------+

4. quit from mysql

5. stop mysql server service

6. make a copy of all files from /var/db/mysql to a local disk at first. If you copy over network it will take more time to complete

7. start mysql-server

On the slave box

1. Stop mysql-server service
2. move binary files /var/db/mysql/ from the master to /var/db/mysql/

3. chown -R mysql /var/db/mysql/

4. Start mysql-server service

enter into Mysql
5. STOP SLAVE;
6. CHANGE MASTER TO master_log_file='mysql-bin.000001', master_log_pos=98;
7. slave start;

Finish thats all please confirm whether your slave is running fine