MySQL master-Slave synchronization configuration

MySQL master-Slave synchronization configuration

First, master server master configuration:

1, modify the My.ini, and then restart

[Mysqld]
Server-id=1 # Set the ID of the master machine (the ID must be unique between the master and slave machines and do not conflict with each other)
log-bin=mysql-bin# Open binary log and specify binary log with a filename prefix of Mysql-bin

Binlog-do-db = sync_db #只同步哪些库
Binlog-ignore-db = ignore_db# ignores which libraries

2, if the master server already has the data, the pilot out synchronizes to the slave
mysqldump-uuser_name-p123456 database_name > Outfile_name.sql

3. Create user, Backup authorization

CREATE USER ' slave ' @ ' slaveserver ' identified by ' slavepass ';
GRANT REPLICATION SLAVE on * * to ' SLAVE ';
Flush privileges;

4, view the binary file name and location, in the slave configuration needs

SHOW MASTER STATUS;

Second, from the server slave configuration

1, modify the My.ini, and then restart

[Mysqld]
server-id=2
Relay_log=mysql-relay-bin # Open relay log and specify relay log filename prefix Mysql-relay-bin (the contents of the relay log are from the binary log of master)
Replicate-do-table=wishrp.sku_map #库名. Table name

2. Import the existing data of master server;

3. Execute the synchronous statement
Change MASTER to
Master_host= ' Masterserver ',
Master_user= ' slave ',
Master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000001 ',
Master_log_pos=1;

4. Start Backup

Start slave;
show slave status\g; #查看备份状态

Third, verification

On the master server to create new libraries, tables, additions and deletions to check the data, see Slave server synchronization effect.

MySQL master-Slave synchronization configuration