Windows MySQL master-slave replication

/************************************** master-slave replication start***************************************/

1. Setting up the master server

A)"my.cnf or My.ini [mysqld] Add"

Log-bin=mysql-bin

Server-id =1 #主服务器ID, cannot be the same from the server

binlog-do-db=dbname# plan to back up the database, not set the words back up binlog-ignore-db all databases, including creating the database

Binlog-ignore-db=mysql,test,information_schema

Actual operation {

Server-id=1
Log-bin=mysql-bin
Binlog-do-db=think_ceshi

}

b) Add replication user repluser, password passwd, authorize the user to copy permissions

Mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to ' repluser ' @ ' SLAVEIP ' identified by ' passwd ';

Mysql>flush privileges;

Where the SLAVEIP is from the server IP, such as 192.168.2.3

Actual operation {

GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to ' cong ' @ ' 192.168.80.130 ' identified by ' 123456 ';

FLUSH privileges;

}

Problems that may be encountered in the primary database that are not connected from the database may be a firewall problem you can shut down the firewall and try

2. Set the slave server

The [mysqld] segment of MY.CNF or My.ini is added

Server-id = 2

Master-host = Slaveip

Master-user = Repluser

Master-password = passwd

Master-port = 3306

Log-bin=mysql-bin

Master-connect-retry=60

Replicate-do-db=dname

Log-slave-updates

read-only=1# can be set to read-only

Actual operation {

server-id=2
Log-bin=mysql-bin
Replicate-do-db=think_ceshi
Log-slave-updates
Read-only=1

}

3. Set the slave server

Show master status;

Write down file and position

file:mysql-bin.000003,position:15386161;

4. Setting up replication points from the server

mysql > Stop slave;

Substituting file and position in front (5th step)

MySQL > Change master to master_host= ' Masterip ', master_user= ' repluser ', master_password= ' passwd ', master_log_file= ' mysql-bin.000003 ', master_log_pos=15386161;

mysql > Start slave;

Where Masterip is the primary server IP, such as 192.168.2.2

Actual operation {

Restart MySQL Service

Stop slave;

Change Master to master_host= ' 192.168.80.129 ', master_user= ' Cong ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=120;

Start slave;

}

5. Check the slave status

MySQL >show slave status;

Slave_io_running and slave_sql_running are yes to indicate a successful configuration

Actual operation {

Slave_io_running:no

The server-uuid of the master-slave server is the same as the error caused by the AUTO.CNF

Rename the auto.cnf from the server auto.cnf.bak restart the MySQL server to regenerate the new auto.cnf

Check show slave status OK again

Inserting a single piece of data from the primary database can be replicated synchronously from the server

}

/************************************** master-slave replication end***************************************/

Windows MySQL master-slave replication