[Practical Notes] MySQL master-slave synchronization function implementation

Write in front:

These two days to a demand, configure the deployment of two servers MySQL data synchronization, tossing two days to check a lot of relevant information, has not even, and later found that the database is actually authorized IP has problems, we use the server is the virtual machine in the engine room plus reverse proxy out of the pit. Read a lot of blog, write how to say, write good is too detailed, write bad, configuration what are not clear, just contact this piece of time do not know the principle, blindly copy paste, and later saw a blogger wrote good text, instant clairvoyant, also have their own ideas, I simply record the operation of the procedure and some of the details of the comments, the principle of direct transport, the schematic also painted a part, it is not caught dead, there is the wrong place, but also hope that you big fellow, in the next grateful!

What are the benefits of configuring MySQL master-slave synchronization?

(Data distribution)
2--load balancing (load Balancing)
3--data Backup (Backups) To ensure data security (the most important role)
4--high availability and fault tolerance rows (hi availability and failover)
5--to achieve read-write separation and ease database pressure

MySQL master-Slave synchronization implementation principle (refer to the post point here):

The master server logs the data changes to the binary Binlog log, and when the data on master changes, it is written to the binary log, and the Salve server detects the master binary log for changes within a certain time interval, and if any changes occur, Starts a I/othread request Master binary event, and the master node initiates a dump thread for each I/O thread, which is used to send a binary event to it and is saved to the local trunk log from the node, from which the node will start the SQL thread to read the binary log from the secondary log and replay locally , so that the data is consistent with the master node, and finally I/othread and sqlthread go to sleep and wait for the next wake-up.
Note the points:
1--master logs the action statement to the Binlog log, and then grants the slave remote connection permission (master must turn on the Binlog binary logging feature; slave also turns on binlog for data security purposes).
2--slave Open Two threads: IO thread and SQL thread. Where: The IO thread is responsible for reading the Binlog content of master to the trunk log relay logs; The SQL thread is responsible for reading Binlog content from the relay log log and updating it to the slave database. This will ensure that the slave data is consistent with the master data.
3--mysql replication requires at least two MySQL services, of course, the MySQL service can be distributed on different servers, or you can start multiple services on a single server.
4--mysql replication is best to ensure that the MySQL version on the master and slave servers is the same (if the version is not consistent, ensure that the master Master node version is lower than the slave from the node version)
Time synchronization between 5--master and slave two nodes

the flowchart for MySQL replication is as follows:

How is MySQL synchronized?

Statement-based replication: SQL statements executed on the primary server execute the same statement from the server. MySQL uses statement-based replication by default and is more efficient. Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
2--row-based replication: Copy the changed content past instead of executing the command from the server. Support Starting from mysql5.0
3--mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

Implementing the Environment:

| System |  MySQL | IP |

|:----|:----|:----

|win7 | mysql-5.6.24 | 192.168.1.129 |

|centos 6.7 | mysql-5.6.39 | 192.168.1.128 |

Note: The MySQL version from the server is preferably the same as the primary server, or larger than the primary server version

MySQL master-Slave synchronization implementation part:

The first is the configuration of Master (master node):

#主Master服务器配置:

1. Go to the MySQL installation directory and create a new log folder (this is the path to store the binary log) 2. The primary server opens Log_bin, you need to modify the My.ini, configure the following:

#*********************master My.ini profile Start ***************************************** #路径均为当前服务器的实际路径Basedir = D:\\apps\\mysql-5.6.24-win32datadir = D:\\apps\\mysql-5.6.24-win32\\dataport = 3306#生成记录文件位置, synchronization must be, do not delete manually, the format location is: Log-bin=mysql installation path/log/mysql-bin.logLog-bin=d:\\apps\\mysql-5.6.24-win32\\log\\mysql-bin.log#服务ID, for differentiated services, range 1~2^32-1, need to be different from serverserver_id= 1#MySQL Disk write policy and data security # Every time a transaction commits, MySQL writes data from log buffer to log file and flush (swipe to disk)innodb_flush_log_at_trx_commit=1#当sync_binlog =n (n>0), MySQL synchronizes its write binary log binary logs to disk using the Fdatasync () function when each write N binary log binary logs.
The default value of Sync_binlog is 0, like the mechanism of the operating system brushing other files, MySQL does not sync to disk but relies on the operating system to flush binary logs.sync_binlog= 1#同步数据库, if you have multiple libraries, write a few lines in this formatbinlog-do-db=test#无需同步的数据库, the following lines are basically the same, no need to changebinlog-ignore-db = clusterbinlog-ignore-db = Mysqlbinlog-ignore-db = Performance_schemabinlog-ignore-db = Information_schema#mysql复制模式, three types: SBR (based on SQL statement replication), RBR (row-based replication), MBR (mixed-mode replication) #混合模式复制binlog_format=mixed#binlog过期清理时间expire_logs_days=7#binlog每个日志文件大小max_binlog_size=20m#*********************master my.ini configuration file End *****************************************

3. Restart the MySQL service, MySQL command line execution:

show master status; #记录文件名以及紧跟的当前行数数字

4. Create and authorize the user, the latter two slave are user name and password respectively

Grant replication Slave,replicationon*. *  to [Email protected] ' 192.168.1.128 '  by  privilegeswithread lock; #锁定数据库为只读 to ensure consistent backup data

5. Exit the MySQL command line and execute the backup command

#备份当前所有数据库, you can refer to  the backup library -  u root-p--all-databases--master-data > Dbdump.sql

6. SQL scripts are executed from the server
7. Start the slave from the server (provided that the server is configured properly)
8. Turn off the table lock after booting from the server

Unlock tables;

#从服务器的配置

1. Stop slave Service

Service Mysqld Stop

2. Modify the configuration file:

Vim/etc/my.cnf

#从数据库 (Slave) configuration: #***********************************slave my.cnf configuration Start ******************************# The file location or name prefix from the library log recordLog_bin =/var/lib/mysql/mylogbin.log#同步日志记录的频率, 1 for each record, safe but inefficientSync_binlog = 1#server的id, MySQL master-slave connection with the same ID cannot beserver-id=2#从库日志忽略的数据库名称, do not log
#这里记录从库的binlog是为了安全, if you do not feel the need, you can remove the configuration from the library Binlogbinlog-ignore-db = clusterbinlog-ignore-db = Mysqlbinlog-ignore-db = Performance_schemabinlog-ignore-db = Information_schema#此处添加需要同步的数据库名称, it will only receive information about this database, multiple databases need to be synchronized in this format to write a few lines
#这里同步数据有两种思路, one is that the primary server only needs to be sent from the library, specified in the main library; one is that the master server synchronizes all the data and receives from the library on-demand filtering.
#为了让配置更详细些, the configuration received from the library filter is configured herereplicate-do-db=test#忽略接收的库名replicate-ignore-db = clusterreplicate-ignore-db = Mysqlreplicate-ignore-db = Performance_ Schemareplicate-ignore-db = Information_schema#跳过所有错误继续Slave-skip-errors=all#设置延时时间slave-net-timeout=60#mysql复制模式, three types: SBR (based on SQL statement replication), RBR (row-based replication), MBR (mixed-mode replication)binlog_format=mixed#混合模式复制expire_logs_days=7#binlog过期清理时间max_binlog_size=20m#binlog每个日志文件大小 #***********************************slave my.cnf configuration ends ******************************

3. Save Exit : Wq

4. Start the MYSQLD service

Service mysqld Start

5. Delete Redundant database, import data, delete section not demo, SQL location please specify

-U root-p < ~/#这里演示就是上传到了root的根目录, please use the "Find/-name SQL script name"  command to query

6. Specify master from the server

Change MASTER tomaster_host='192.168.1.129', master_user=' slave ' , Master_password='slave', master_log_file='  mysql-bin.000001', master_log_pos=593;

Note: The last two lines are the data that was previously recorded on the master server Show master Status

If a slave process has been started before, then the above command will fail and prompt stop slave first, so stop slave; And then try again

7. Start slave

#注意, no semicolon

The output is as follows, showing that all two are yes and successful, you can test

Mysql>show slave status;***************************1. Row ***************************slave_io_state:waiting forMaster to send event Master_host:192.168.1.129master_user:replication Master_port:3306Connect_retry: -Master_log_file:mysql-bin.000001read_master_log_pos:593Relay_log_file:mysql-relay-log.000004Relay_log_pos:441Relay_master_log_file:mysql-bin.000001 slave_io_running:yes slave_sql_running:yes replicate_do_db: Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replic Ate_wild_ignore_table:last_errno:0Last_error:skip_counter:0Exec_master_log_pos:52360Relay_log_space:597until_condition:none Until_log_file:until_log_pos:0Master_ssl_allowed:no Master_SSL_CA_File:Master_SSL_CA_Path:Master_SS L_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0master_ssl_verify_server_cert:no Last_io_errno:0Last_io_error:last_sql_errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:1"""

8. Open the table read-only lock to the primary server

Unlock tables;

The--------------------------------implementation part ends here--------------------------------------

Completely remove the master-slave replication relationship
1) stop Slave;
2) reset Slave; #或直接删除master. Info and relay-log.info two files;
3) Modify MY.CNF Delete master-slave related configuration parameters.

4) Delete from user Where user= ' slave ' and host= ' 192.168.1.128 '; #删除主服务器配置的连接slave用户

This article refers to a list of posts:

MySQL master-Slave synchronization (1)-Master/Master Environment deployment Grooming MySQL5.7 Add users, remove users and authorize MySQL settings specify IP access, user rights related operations Win7 under mysql5.6 with CentOS mysql5.6 master-slave replication mysql5.6 Master-Slave replication synchronization detailed configuration (graphics)MySQL5.6 Database Master-Slave (Master/slave) synchronous installation and configuration detailedCentOS 7 mysql5.7 Master-slave database synchronization configuration MySQL 5.7.18 database master-Slave (Master/slave) synchronous installation and configuration detailed MySQL from library Last_io_error:got fatal Error 1236 from Master when reading data from binary log: ' Could not ' find first log file name in binary log index file ' error handling

"MySQL" last_io_errno:1593 server-uuid repeat causes slave error

"MySQL" MySQL5.6 database based on Binlog master-Slave (Master/slave) synchronization installation and Configuration detailed window under the MySQL Binlog Open and view, Mysqlbinlogmysql master-slave replication-change Syntax explanation [trouble] Error connecting to master '[email protected]: 3306 '-retry-time:60 retries:86400

[Practical Notes] MySQL master-slave synchronization function implementation