Pages

Wednesday, August 25, 2010

MySQL:Replication Configuration

MySQL Replication Configuration


Configuring the Master Server
1.Edit the [mysqld] section of the /etc/my.cnf file on the master server to include a log-bin option. Also include a server-id=master_id option in this section, where master_id must be a positive integer value from 1 to 232-1. For example:
[mysqld]
log-bin=/usr/local/mysql/data/mysql-logbin-filename
binlog-do-db=dbname

server-id=1


NOTE: The server-id value configured in this step must be unique to each server in the replication configuration. No two slave servers or the master server may have the same server-id value




2.Restart the MySQL server on the master server, and make sure no errors occurred by entering the following command:
cd /usr/local/mysql/bin
./mysqladmin -uusername -ppassword shutdown
./mysqld_safe --user=mysql &


3.Start the MySQL command-line client by entering the following command:
cd /usr/local/mysql/bin
./mysql –uusername –ppassword
flush tables with read lock;


4.Run the following command in the MySQL client to verify that the master server is running correctly, and to gather the necessary information for configuring the slave servers:
mysql> show master status;


5.Create a replication user on the master server that has the privileges needed to connect and change mastership to the master server. The slave server uses this account to connect and configure the master/slave relationship.
mysql> create user 'anil'@'slave.test' identified by 'anil123';


6.Next, grant the necessary privileges to allow the MySQL slave servers to connect to the master server and establish replication:
mysql>grant replication slave on *.* to 'anil'@'192.168.0.160' identified by 'anil123';


7.While the database has the READ LOCK applied, export the data using mysqldump, including master data information:
mysqldump --all-databases --master-data > dbdump.db  OR Physical Backup(Raw Databackup)


3.Start the MySQL command-line client by entering the following command:
cd /usr/local/mysql/bin
./mysql –uusername –ppassword
unlock tables;


The master server is now ready for the slave servers’ connection.





Configuring the Slave Servers



1.Change the server-id parameter in the /etc/my.cnf file as follows:
mysqld]
server-id=2

2.Verify that the MySQL server on the slave server can be restarted correctly by entering the following command:


3.The following SQL commands are used to set the master server information on the slave server:


mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;




4.For this example, enter the following commands:
mysql> CHANGE MASTER TO -> MASTER_HOST='master.test', -> MASTER_USER='osmusr', -> MASTER_PASSWORD='osmpass', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=1678;
5.Start the slave threads, which initiate the master/slave server connection, by running the following command on the slave server:
mysql> start slave;
Run the show slave status command to verify that the slave server is connected to the master server correctly:
mysql> show slave status\G
The following output is displayed:


*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: osmusr
Master_Port: 3306





Synchronizing Data
After you configure the master server and the slave server, perform the following steps to synchronize the database data.
1.Create the test database, osmsdb, and the test table, customer, on the master server by referring to the SQL scripts in “Creating the Sample Database for MySQL”.
2.Verify that the osmsdb database and customer table are created automatically on the slave server. To do this, enter the following commands from the MySQL command-line running on the slave servers:
mysql> show databases;
Verify that osmsdb is listed in the output.
mysql> use osmsdb;
mysql> show tables;

Verify that customeris listed as a table in the output.
3.Run some SQL commands that modify the customer table on the master server, and verify that these changes are reflected in the table on the slave server. For example, insert a record by entering the following command on the master server:
mysql> insert into customer values (1, ‘customer1’, \
‘2000-01-01 10:10:10’, ‘table’, 1234)

4.Verify that the SQL commands are executed automatically on the slave server.










MonitoringReplicationStatus
1.Run the following command on the master server to display the status:
mysql> show master status\G
The file name and position of the bin log file that the master server is using are displayed:
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 98
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
2.Run the following command on the master server to get information about slave servers that are connecting to the master server:
mysql> show processlist\G
The following data is displayed:


*************************** 1. row ***************************
Id: 32
User: osmusr
Host: slave-1.test:32798
db: NULL
Command: Binlog Dump
Time: 5740
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 33
User: osmusr
Host: slave-2.test:32944
db: NULL
Command: Binlog Dump
Time: 2150
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 34
User: root
Host: localhost
db: NULL
Command: Query
Time: State: NULL
Info: show processlist
3 rows in set (0.00 sec)


The output shows that two slave servers, slave-1.test and slave-2.test, have connected to the master server, and the master server has sent all the binary log data to both slave servers and they are both up to date.
1.To show the status of the slave server, run the following command on the master server
mysql> show slave status\G
The master server information for this slave server and its status are displayed. If the slave server has processed all updates from the binary log and is up to date with the master server, the values of Master_Log_File andRead_Master_Log_Pos in the output are consistent with the output of show master status on the master server from step 1.
2.To show the current status of the slave server I/O thread, run the following command from the slave server:
mysql> show processlist\G


*************************** 1. row ***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 173581
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 81990
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 225
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
3 rows in set (0.00 sec)


The show processlist command on the slave server displays the current status of the slave I/O thread, which connects to the master server and writes events from the master server to relay logs. This command also shows the status of the slave server SQL thread, which reads events from these relay logs and enters them into the slave server database. The values of the State field in the output indicate that the slave server I/O thread and the slave server SQL thread have finished their tasks and are waiting for additional updates from the master server.

No comments: