MySQL Series 7----master-slave settings

First, MySQL master-slave synchronization (for the back to achieve read and write separation, MMM high-availability cluster basis)

Main Library configuration: IP address of the main library: 192.168.4.111

1, first to the user authorization, used to achieve synchronization, from the library to connect the main library user name:

Grant Replication Slave on * * to [e-mail protected] "%" identified by "123456";

Authorized Tongbu This user has copy permissions on all the libraries, and sets the password

2. Enable Binlog log: VIM/ETC/MY.CNF

server_id=111//Designation number

log-bin=master111//Specify log file name, enable Binlog log

binlog-format= "mixed"//log type

3. Restart the database service:

4. Check that the Binlog log file is being used: Show Master status\g;

From library configuration: IP address from library: 192.168.4.112

1. Check if you are a master-slave database

Show master status; show slave status;

2. Test whether the authorized user of the primary database is valid, see if you can log in

3, add server_id=112 in the configuration file, and restart the service

4, under the MySQL command line, change the configuration, configure the main library related information

Change master to

-master_host= ' 192.168.4.111 ',//main library address

-master_user= ' Tongbu ',//user name of the connection

-master_password= ' 123456 ',//password

-master_log_file= ' 111-bin.000001 ',//log file name

master_log_pos=154; Offset amount

5, start from the service: start slave;

6, show slave status\g//view from the status, confirm that the following two lines are Yes

Slave_io_running:yes

Slave_sql_running:yes

7. To test the sync effect:

On the primary server, create libraries, tables, insert records, and view data from the server.

Ii. Some information about the log file

1. Several common log files

112-relay-bin.000006//Relay log file

112-relay-bin.index//trunk log file Index

Relay-log.info//Relay Log

Master.info//Main library information

2, how to restore from the library into a separate server:

Stop slave; Reset slave All;

3. Some information about threads and programs

IO thread: Logs the Binlog log contents of the master library to the Relay-binlog log in this machine

SQL thread: Writes SQL commands in the Relay-binlog log to the local library

Show Processlist; Binlog Dump//View the program that the Lord runs, and then you can see the Binlog dump program

Show Processlist; Connect//Can view programs running from the library

Third, master-slave synchronization Common parameters (/ETC/MY.CNF)

1. Main Library: binlog_do_db= database name//Only libraries allowed to synchronize

binlog_ignore_db= database name//out of sync Library

2, from the Library: Turn on Cascade Replication (log_slave_updates) master slave configuration from the first library

Synchronizing individual databases (replicate_do_db= database name)

Libraries that are not synchronized (replicate_ignore_db= database name)

MySQL Series 7----master-slave settings