Mysql 5.6 Master-Slave synchronization configuration

Master-Slave synchronization, the essence is the use of database logs, the main library data copy to the library, in essence, the use of data replication technology.

This article outlines

1. Basic configuration of the main library
2. Basic configuration from a library
3. Steps for full synchronization
4. Precautions
5. Working principle

1. Basic configuration of the main library

Do two things: Enable logging (record database operations) and give permission to copy from the library. The configuration is as follows:

1. Enable Logging:

# sync_binlog=1 #默认为0，当 sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。 #当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。log_bin=mysql-bin #打开日志，并指定日志基本名称。log_bin_index=mysql-bin.indexlog_bin_trust_function_creators=1expire_logs_days = 3

1. Give permission to copy from library
   (different hosts, configure different user names to differentiate.) such as SLAVE95,M95M96, or the use of a unified user name, as follows:)

mysql> grant replication slave on *.* to ‘slave‘@‘%‘ identified by ‘[email protected]‘;mysql> flush privileges;

2. Basic configuration from the library

Do two things: enable (relay) The log, specify from the library a file from the main library (a location) to copy the log.

1. Enable (relay) log

#log-bin=mysql-bin #从库用不上relay-log=slave-relay-binrelay-log-index=slave-relay-bin.indexskip-slave-start   #防止复制随着mysql启动而自动启动slave-skip-errors=allrelay_log_info_repository = TABLEmaster_info_repository    = TABLErelay_log_recovery        = 1log_bin_trust_function_creators=1slave-net-timeout = 60            #默认值3600.意味着没有得到更多数据之后slave等待时间expire_logs_days = 3read-only=1  #可选项，做读写分离时，可配置replicate_wild_do_table=datacenter.% #告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。replicate_wild_do_table=kettle.%

1. To specify that a log is copied from the library from a file (a location) from the main library

mysql>CHANGE MASTER TO   MASTER_HOST=‘192.168.100.104‘,    MASTER_PORT=3306,    MASTER_USER=‘slave‘,    MASTER_PASSWORD=‘[email protected]‘,    MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=839;

3. Fully synchronized Steps Main Library

1. Lock table to prevent data write
   Use the command:

mysql> flush tables with read lock;

2. Backing up data

mysqldump -uroot -p -B kettle datacenter|gzip>/ddhome/data.sql.gz

3. Check the master status to unlock the lock list function

4. Upload the MySQL backup file to the slave library for data recovery

scp mysql.bak.sql [email protected]:/tmp/

From the Library

1. Stop the state from the library

mysql> stop slave;

1. Import data

mysql -uroot -p  </tmp/mysql.bak.sqlormysql> source /tmp/mysql.bak.sql

1. Settings sync from the library, note that the synchronization point is in the main library show Master status information | file| Position two items

change master to master_host = ‘10.130.254.13‘, master_user = ‘slave‘, master_port=3306, master_password=‘[email protected]‘, master_log_file = ‘mysql-bin.000104‘, master_log_pos=481224382;

1. Re-open from sync

mysql> start slave;

1. View synchronization Status

mysql> show slave status\G

View: (reference: MySQL master-slave copy build several kinds of log and POS in detail-learning is boundless, the more bewildered-csdn blog)

Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0          Master_Log_File: mysql-bin.000125      Read_Master_Log_Pos: 947053504    #从库IO线程读取主库日志的位置           Relay_Log_File: slave-relay-bin.000064            Relay_Log_Pos: 947053667    #从库IO线程写入本地日志    Relay_Master_Log_File: mysql-bin.000125      Exec_Master_Log_Pos: 947053504    #从库sql线程执行到哪里

The simple point is to read the main library's binaries (master_log_file) and location (Read_master_log_pos) from the library first through the IO thread and then cache the trunks (Relay_log_) to the local (from the library server). file) and record where it has been read (Relay_log_pos), and then by reading the trunk file (relay_log_file) from the library's SQL thread, the SQL thread execution records which files have been executed (relay_master_log_ File) and which location (Exec_master_log_pos).

4. Precautions

1. Start slave manually from the library every time, not automatically. If it is a dual-master hot-Standby, it starts automatically;

2. Use replicate_wild_do_table and replicate_wild_ignore_table on Slave to solve cross-Library update issues

5. Working principle

1. Master changes the record to binary log (these are called binary log events, binary logs event).
2. Slave copies the master log to its own trunk log (relay log).
3. Slave re-executes the events in the trunk log and puts them in their own database.

Disadvantages of MySQL Replication

The MySQL Replication (replication) feature makes people love and hate. MySQL replication configuration is simple, popular with developers, and replication-based read-write separation schemes are also prevalent. MySQL database high availability is mostly based on replication technology, but the MySQL replication itself still has some defects, the main problem is as follows:

• Data loss issues (consistency)
• Data synchronization delay problem (delay)
• Extensibility Issues (Scalability)
  Lossless semi-sync replication from MySQL 5.7 has solved the problem of data loss, the MySQL 5.7 multi-thread slave also solves the problem of data synchronization delay, MySQL 5.7 group Replication also has extensibility issues.

Reference documents

• MySQL5.6 replication Technology (1)-A detailed explanation of the principle
• Mysql 5.6 Master-Slave synchronization configuration and solution

Tips: This article belongs to their own study and practice of the process of recording, many pictures and text are pasted from the online article, no reference please forgive! If you have any questions please leave a message or e-mail notification, I will promptly reply.

Mysql 5.6 Master-Slave synchronization configuration