configuring MySQL master-Slave server

Reference: https://www.linuxidc.com/Linux/2016-09/135633.htm

One, Master master server Configuration (192.168.1.3) 1. Edit my.cnf (Command Find file location: Find/-name my.cnf) vi/etc/mysql/my.cnf added in [mysqld]: Server-id = 1log _bin = Master-binlog_bin_index = master-bin.indexbinlog_do_db = my_databinlog_ignore_db = MySQL Note: Server-id server unique ID, log _bin starts the MySQL binary log, binlog_do_db specifies the database that records the binary log, binlog_ignore_db specifies the database that does not log binary logs. 2. Log in to the master server to create accounts and permissions from the server: Grant replication Slave on * * to ' masterbackup ' @ ' 192.168.1.% ' identified by ' masterbackup '; (192 The. 168.1.% wildcard, which indicates that 0-255 of the IP is accessible to the primary server, the formal environment is configured to specify from the server IP) 3. Restart MySQL, command: sudo service MySQL restart4. View Primary Server Status: Show Master ;  second, slave from server Configuration (192.168.1.5) 1. Edit my.cnf (Command Find file location: Find/-name my.cnf) vi/etc/mysql/my.cnf in [mysqld] Add: Server-id = 2relay-log = Slave-relay-binrelay-log-index = slave-relay-bin.index2. Restart slave from server mysql, command:/ETC/INIT.D /mysql restart3. Login slave from the server, connect Master master server: Change Master to master_host= ' 192.168.1.103 ', Master_port=3306,master_user = ' Masterbackup ', master_password= ' masterbackup ', master_log_file= ' master-bin.000001 ', master_log_pos=2403; Remark: Master_log_file corresponds to the file column shown by show Master Status: Master-bin.000001;master_log_pos corresponds to position column: 2403, otherwise synchronization failure may occur. 4. Start slave data synchronization. Start slave;5. View slave information: show slave status\g; note: Both slave_io_running and slave_sql_running are yes to indicate a successful synchronization.  slave_io_running:no Solutions Solution I.

Slave_sql_running:no
1. The program may have been written on the slave

2. It is also possible that the transaction rollback is caused by the slave machine being reset. (This is my mistake.)

This is typically caused by a transaction rollback:
Workaround:
mysql> slave stop;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> slave start;

After the first operation is completed, the input command Showslave status\g, found or not changed, and the operation of a once, just fine;

Summary of slave startup failures:

The result from the server configuration must be: slave_io_running and slave_sql_running State must be Yes
1:change Master to
If the Slave from the library is not started, slave_io_running is no.
It is possible that the information for master in the main library has changed,
View Main Library show master status;
Record the File,position field, assuming ' mysql-bin.000004 ', 98;
When executing from a library

MySQL>stop slave;mysql> to Master_log_file='  mysql-bin.000004', master_log_pos=98; MySQL>start slave;

2:set Global sql_slave_skip_counter=n;
If the slave_sql_running from the library is no.
The Err file records:
Slave:error "Duplicate entry ' 1 ' for key 1" on query .....
It is possible that master did not synchronize successfully to slave, but there is already a record in slave. The resulting conflict can be executed from the vault.
Set global sql_slave_skip_counter=n;
Skip a few steps. Then restart slave.
3: found that MySQL slave server often because of some special characters or symbols generated by the UPDATE statement error, the entire synchronization will be stuck in that, the original method is just manual error machine to execute the following three SQL statements, skip the error.

MySQL>slave stop;mysql>set GLOBAL sql_slave_skip_counter=1  ; MySQL>slave start;

configuring MySQL master-Slave server