mysql--one master multi-slave configuration

I. Master server Master

1. Set permissions for users

To set up users who only allow 192.168.120.27 access:

Grant Replication Slave,super,reload on * * to [e-mail protected] identified by ' zhijiatest ';

2. The primary database server installation directory, open My.ini, add the following configuration at the end of the file:

#数据库ID号, represented as master at 1, where master_id must be a positive integer value between 1 and 232–1, Server-id = # enables binary logging, log-bin=mysql-bin# used to create a stored procedure Log_bin_ trust_function_creators=1# the binary database name that needs to be synchronized; binlog-do-db=readandwrite_test# the binary database name that is not in sync, and if not set it can be commented out; binlog-ignore-db=mysql# writes Trax Log every time Trax commits, and performs fsyncinnodb_flush_log_at_trx_commit=1# asynchronous logging sync_binlog=1# Set the generated log file name, log-bin= "D:/mysql datafiles/materlog" #把更新的记录写到二进制文件中; log-slave-updates# Skip the error, Continue with the copy operation Slave-skip-errors=all

3. Save the file. Restart the MySQL service.

4. Appendix

Show Master status;--Show Master Status \g/* query whether bin-log*/show variables like '%log_bin% ' is turned on; show variables like ' log_bin_t Rust_function_creators '; Show Master Logs;show processlist; FLUSH privileges; Show Binlog events;show binary logs;

Two. Slave from server

1. Configuration

#如果需要增加Slave库则, this ID is deferred; server-id = n enables binary logging; log-bin=mysql-bin# used to create a stored procedure Log_bin_trust_function_creators=1

2. mysql> operation

Change Master to--Main Library hostmaster_host= ' 192.168.120.57 ',--the user established in the primary database server for this backup from the server-master_user= ' Forslave ',-- Master_password= ' zhijiatest ', master_user= ' root ', master_password= ' root ', master_port=3306,--the location of the primary server Master_log_ File= ' materlog.000001 ', master_log_pos=98,--if the main server is found to be disconnected, reconnect the time difference master_connect_retry=60;

3. mysql> operation

START slave;--STOP  slave;show SLAVE status;--show SLAVE status\g

Attached: Common errors

Check the port entry and exit station

Permissions for user name passwords

mysql--one master multi-slave configuration