MySQL Master-slave replication

MySQL master-slave database configuration

How to configure MySQL master-slave database

First make sure the MySQL version on the master and slave server is the same
Primary database server configuration:
1. Modify the configuration file of the primary database My.ini, and restart the Mysql service after the modification .

[Mysqld]Port=3306Server-Id=10#与从数据库不同Log-bin=c: \log-bin. Log     #日志文件          binlog-do-db=< Span class= "PLN" >tpceshi  #这里设置需要在主服务器记录日志的数据库, only the database set here can be copied to the slave server -ignore -db=mysql # This sets the database that does not remember the log on the primary server               

2. open mysql command line login mysql, set a database from the account, to assign copy permissions from the database

MySQL>GRANT REPLICATION SLAVE on * * to slave001' @ '192.168.  0.99' identified by '123456';          

slave001: User name

192.168.0.99 : IP

123456 : Password

3.show masterstatus; Show primary Database status

File: Database log Files

Position: Log Location

These 2 options are used to configure from a database server

////////////////

Synchronizing from database data

////////////////

OK, now you can stop the update operation of the master data and generate a backup of the primary database, we can export the data to the database from the mysqldump

Note Read LOCK is performed on the primary database before the data is exported to ensure data consistency

MySQL> flush Tables with read lock;     

And then the 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 has been backed up

MySQL> unlock tables;  

Copy the Test.sql from the primary data backup to the import from the database.

From the database server configuration:

1. Modify the My.ini from the database , Add the server-id parameter , different from the primary database

2. on the MySQL command line, specify the IP of the user that the replication uses , the primary database server , Port and the file and location where the replication log starts

        change master  to        master_host=,        master_user = ' root ' ,         master_password= ' 123456 '          master_log_file=  ' log-bin.000001 ' ,         master_log_pos=120     

3. on the slave server , start the slave process

MySQL> start slave;  

4. show Salvestatus verification from the server

Mysql>SHOW SLAVE Status\gSlave_io_state:WaitingForMaster to sendEventMaster_host:localhostMaster_user:RootMaster_port:320WConnect_retry:3Master_log_file:Mysql-Bin.003Read_master_log_pos:79Relay_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               

If both slave_io_running and slave_sql_running are yes, the configuration is successful.

MySQL Master-slave replication