MySQL master-slave replication Configuration

MySQL master-slave replication requires at least two MySQL services. Of course, MySQL services can be distributed on different servers or multiple services can be started on one server.

(1) ensure that the mysql version on the Master/Slave server is the same.

(2) On the master server, set an account for the slave database and grant permissions Using Replication slave, for example:

Mysql> grant replication slave on *. * To 'slave001' @ '192. 168.0.99 'identified

'123 ';

Query OK, 0 rows affected (0.13 Sec)

(3) modify the configuration file my. CNF of the primary database, enable BINLOG, and set the value of server-id. After modification, the MySQL service must be restarted.

[Mysqld]

Log-bin =/home/MySQL/log/mysql-bin.log

Server-id = 1

(4) The current binary log name and offset of the master server can be obtained. This operation aims to restore the data from this point after the database is started.

Mysql> show Master Status \ G;

* *************************** 1. row ***************************

File: mysql-bin.000003

Position: 243

Binlog_do_db:

Binlog_ignore_db:

1 row in SET (0.00 Sec)

(5) Now we can stop updating the primary data and generate a backup of the primary database. We can use mysqldump to store data everywhere to the slave database. Of course, you can also directly use the CP command to copy the data file to the slave database.

Read lock on the primary database before exporting data to ensure data consistency.

Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.19 Sec)

Followed by mysqldump

Mysqldump-h127.0.0.1-p3306-uroot-P test>/home/chenyz/test. SQL

It is best to restore the write operation after the primary database is backed up.

Mysql> unlock tables;

Query OK, 0 rows affected (0.28 Sec)

(6) copy test. SQL backed up by the primary data to the slave database for import.

(7) modify my. CNF from the database, add the server-ID parameter, specify the user used for replication, the IP address and port of the master database server, and the file and location where the replication log is started.

[Mysqld]

Server-id = 2

Log_bin =/var/log/MySQL/mysql-bin.log

Master-host = 192.168.1.100

Master-user = test

Master-pass = 123456

Master-Port = 3306

Master-connect-retry = 60

Replicate-do-DB = test

(8) Start the slave process on the slave server

Mysql> Start slave;

(9) Verify the show salve status on the slave server

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_io_state: Waiting for Master to send event

Master_host: localhost

Master_user: Root

Master_port: 3306

Connect_retry: 3

Master_log_file: mysql-bin.003

Read_master_log_pos: 79

Relay_log_file: gbichot-relay-bin.003

Relay_log_pos: 548

Relay_master_log_file: mysql-bin. 003

Slave_io_running: Yes

Slave_ SQL _running: Yes

(10) Now we can perform some update operations on our master server, and then check whether the updates have been made on the slave server.

----------------------------------------

From: http://www.blogjava.net/dongbule/archive/2010/08/22/329602.html