Replication enables data from one MySQL database server (called the master) to be replicated to one or more MySQL database servers (slaves). Replication is asynchronous - your replication slaves do not need to be connected permanently to receive updates from the master, which means that updates can occur over long-distance connections and even temporary solutions such as a dial-up service.
We need to ensure /etc/hosts file contains the hostname and IP address for both Master and Slave servers .
Replication between servers in MySQL works through the use of the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log. The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.
Once binary logging has been enabled, all statements are recorded in the binary log. Each slave will receive a copy of the entire contents of the binary log. Slaves keep a record of the binary log file and position within the log file that they have read and processed from the master .
Master Server :-
a)Configuration of Server-id
/etc/my.cnf
[mysqld]
server-id=1
b)Enabling the Binary log
/etc/my.cnf
server-id=1
log-bin=bin-log-server
c)Enabling other logs for debugging purpose
/etc/my.cnf
log=/var/lib/mysql/test2.example.com.err
log-error=/var/lib/mysql/error.log
log-slow-queries=/var/lib/mysql/slow.log
Creation of Replication Account:
Mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replica’@’%’ IDENTIFIED BY ‘abc123’
Mysql > GRANT SELECT , SUPER , RELOAD ON *.* TO ‘replica’@’%’ IDENTIFIED BY ‘abc123’
mysql> show grants for replica ;
+--------------------------------------------------------------------+
| Grants for relica@% |
+---------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'replica'@'%'
IDENTIFIED BY PASSWORD '4b5698aa4603595b' |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)
Restart the server
# service mysqld restart
mysql> show master status ;
+-----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------------+----------+--------------+------------------+
| test-relay-bin.000010 | 98 | | |
+-----------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
test-relay-bin.000010 -> Binary file name will be used for slave configuration.
Offset position = 98 -> Will be used for Slave Configuration.
mysql> show processlist ;
+----+--------+-------------------------+------+-------------+------+------------------+
| Id | User | Host | db | Command | Time | State | Info|
+----+--------+-------------------------+------+-------------+------+------------------+
| 2 | replica | test1.example.com:46541 | NULL | Binlog Dump | 699 |
Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 5 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+--------+-------------------------+------+-------------+------+------------------+
2 rows in set (0.00 sec)
Need to restore the data from Master to secondary
Mysql > COMMIT ;
Mysql > FLUSH TABLES WITH READ LOCK ;
a) Take a mysqldump of all databases or
b) Take a Raw directory backup of the data directory and restore them in slave server .
After that , remove the read lock ;
mysql > UNLOCK
Slave Server Configuration
a)Configuration of Slave Server-id
/etc/my.cnf
[mysqld]
server-id=100
b)Enabling other logs for debugging purpose
/etc/my.cnf
log=/var/lib/mysql/test2.example.com.err
log-error=/var/lib/mysql/error.log
log-slow-queries=/var/lib/mysql/slow.log
Slave configuration
mysql > CHANGE MASTER TO MASTER_HOST='10.146.152.90' , MASTER_USER='replica',
MASTER_PASSWORD='abc123' , MASTER_LOG_FILE='test-relay-bin.000010' ,
MASTER_LOG_POS=98 ;
MASTER_HOST='10.146.152.90' --> Ip address of the master server
MASTER_USER='replica' --> Mysql Replication account .
MASTER_PASSWORD='abc123' --> Mysql Replication account Password .
MASTER_LOG_FILE='test-relay-bin.000010' --> Mysql bin log file name .
MASTER_LOG_POS=98 offset position .
We need to start the slave .
Mysql > start slave ;
This command trigger the Slave configuration . Now , Master and Slave will be sync from here .
mysql> show slave status \G ;
*************** 1. row **************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.146.152.90
Master_User: relica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: test-relay-bin.000014
Read_Master_Log_Pos: 98
Relay_Log_File: mysqld-relay-bin.000013
Relay_Log_Pos: 240
Relay_Master_Log_File: test-relay-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 98
Relay_Log_Space: 240
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: 0
1 row in set (0.00 sec)
0 comments:
Post a Comment