Mysql Server Master master configuration

Directory
Principle 1
Master-Slave Synchronization configuration 2
Master server Synchronization User Authorization 2
Configuring MY.CNF files for MySQL master server 3
Standby configuration: 4
Common commands: 5
Dual master Configuration my.cnf 6
Synchronous replication failure caused by binlog_ignore_db 7
Common Error 11
Mysql binlog Three format introduction and Analysis 11

Principle
After packet capture analysis, tcpdump-n-i eth0-a-s0-v host 218.24.23.253. When the primary SQL operation occurs from the normal connection state with the primary (not the slave first boot), the Binlog is actively pushed to the slave server.
After normal synchronization, if slave MySQL stops, such as the service is stopped, or the device fails. Then after the slave is back to normal, the changes in the master during this period will normally be synchronized to the slave.
The basic process of MySQL replication is as follows: (each part learns from Google, thank you)

1. Slave the above IO line thread attached the Master and requests the log content from the specified location (or from the beginning of the log) to the designated log file;
2. After Master receives a request from an IO thread from Slave, the IO thread that is responsible for the replication reads the log information from the specified log at the specified location based on the requested information and returns the IO thread to the Slave side. In addition to the information contained in the log, the return information includes the name of the Binary log file on the Master side of the returned information and its location in the Binarylog;
3. After the Slave IO thread receives the information, it writes the received log content to the end of the Relaylog file (mysql-relay-lin.xxxxxx) on the Slave side and logs the file name and location of the Bin-log read to the master side to Master-info file in order to be able to tell Master clearly at the next read "I need to start from somewhere in the Bin-log log content, please send me"
4. Slave's SQL thread detects that the contents of the Relay log are newly added, it resolves to the log file immediately after it becomes the Master
   The executable query statements at the end of the actual execution and execute these queries on their own. In this way, the same Query is actually executed on the Master and slave ends, so the data on both ends is exactly the same.

Master-Slave synchronization configuration
Installation:
Yum install MySQL mysql-server #安装
CP/USR/SHARE/MYSQL/MY-MEDIUM.CNF/ETC/MY.CNF #复制配置文件
Service MySQL start #启动
Chkconfig MySQL on #设置开机自动启动

Mysql_secure_installatio N #初始化数据库, delete test library, disable root login;

MySQL root password: [email protected][email protected]#
218.24.44.80;218.24.23.253 mysql password modified to root/[ Email protected]
sync with account and password: zhuoming_backup/[email protected]!#%

Modify service port for MySQL
vim/etc/my.cnf
Master server Synchronization user authorization
CREATE DATABASE backup DEFAULT CHARACTER SET UTF8 COLLATE Utf8_general_ci; Build Library
#CREATE USER ' zhuoming_backup ' @ ' 218.24.44.80 ' identified by ' [email protected]!#% '; The user and password for the backup (when the user is built, fill in the IP that allows the user to back up the operation, the IP that the user is assigned to must be the same, or the permission is not assigned)
#建用户并授权
GRANT file,replication SLAVE on . To ' zhuoming_backup ' @ ' 218.24.44.80 ' identified by ' [email protected]!#% '; #给备份用户相应的权限,
REVOKE file,replication SLAVE on . From ' zhuoming_backup ' @ ' 218.24.23.232 '; #收回授权
If a static address mapping that is also done through the firewall also needs to increase the address and mapping address of the outer network port of the firewall, it is not connected to
GRANT file,replication SLAVE on . To ' zhuoming_backup ' @ ' 223.100.7.151 ' identified by ' [email protected]!#% ';
GRANT file,replication SLAVE on . To ' zhuoming_backup ' @ ' 223.100.7.155 ' identified by ' [email protected]!#% ';
GRANT all privileges on . To ' zhuoming_backup ' @ ' 223.100.7.155 ' identified by ' [email protected]!#% ';
flush privileges; must be refreshed after each empowerment

Third, the MySQL master server in the database osyunweidb import to MySQL from the server
1, export the database osyunweidb
Note: You can go to the MySQL console before exporting to execute the following command
Flush tables with read lock; #生产环境必须先锁定. The database read-only lock command prevents data from being written when the database is exported. This command is a global read lock, and all of the library tables are locked for read-only after the command has been executed. is generally used in the database online backup, this time the database write operations will be blocked, read operation smoothly. The unlocked statement is also unlock tables.
Mysqldump-u root-p osyunweidb >/home/osyunweidbbak.sql #在MySQL主服务器进行操作, Export database osyunweidb to/home/ Osyunweidbbak.sql
Unlock tables; #解除锁定
2, import the database to MySQL from the server
mysql-u root-p #进入从服务器MySQL控制台
Create databases OSYUNWEIDB; #创建数据库
Use osyunweidb #进入数据库
source/home/osyunweidbbak.sql #导入备份文件到数据库
Mysql-u osyunweidbbak-h 192.168.21.169-p #测试在从服务器上登录到主服务器
MySQL master server configuration
Use Backup; Set the backup library to the current library
CREATE TABLE mytest (username varchar (a), password varchar (20)); Create a test table for

VI/ETC/MY.CNF #编辑配置文件, add the following in the [Mysqld] section
Server-id=1 #设置服务器id, 1 indicates the primary server, note: If the original configuration file already has this line, you can no longer add.
Log-bin=mysql-bin #启动MySQL二进制日志系统, Note: If you already have this line in your original configuration file, you won't have to add it anymore.
Binlog_format = MIXED #建议使用MIXED格式. Use show variables like ' Binlog_format ' to view;
Expire_logs_days = Ten #binlog过期清理时间, set the expiration time based on the amount of log generated daily, disk space, and so on.
Max_binlog_size = 100M #每个日志文件大小的最大值
Binlog-do-db=backup #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Binlog-ignore-db=mysql #不同步mysql数据库
Binlog-ignore-db=information_schema
Binlog-ignore-db=performance_schema
Service MySQL Restart #重启MySQL
Mysql-u root-p #进入mysql控制台

To view the primary server, a similar message appears
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | Backup | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

View Server-id:
Mysql> Show variables like ' server_id ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in Set (0.00 sec)

MySQL from server configuration
VI/ETC/MY.CNF #编辑配置文件, add the following in the [Mysqld] section
server-id=2 #配置文件中已经有一行server-id=1, modify its value to 2, master and slave cannot be the same
Log-bin=mysql-bin #启动MySQL二进制日志系统, if only do not start from the database can also, it is recommended to open.
Binlog_format = MIXED #建议使用MIXED格式. Use show variables like ' Binlog_format ' to view;
Expire_logs_days = Ten #binlog过期清理时间, set the expiration time based on the amount of log generated daily, disk space, and so on.
Max_binlog_size = 100M #每个日志文件大小的最大值
Read-only = 1 #设置为只读 in case the master never synchronizes and is not valid for a non-root user, in order to avoid being mistakenly written.
Replicate-do-db=backup #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database
Replicate-ignore-db=mysql #不同步mysql系统数据库

: wq! #保存退出
Service MySQL Restart #重启MySQL
Note: After MySQL 5.1.7, it is not supported to write the master configuration attribute to the MY.CNF configuration file, only the synchronized database and the database to be ignored can be written in the configuration file.
Mysql-u root-p #进入MySQL控制台
Slave stop; #停止slave同步进程
To enter the main library, lock the main library table:
Flush tables with read lock; #生产环境必须先锁定.
#执行同步语句 (perform synchronization must be in mysql> stop slave; In the state)
Change Master to master_host= ' 223.100.7.155 ', master_port=3306,master_user= ' zhuoming_backup ', master_password= ' [ Email protected]!#% ', master_log_file= ' mysql-bin.000001 ', master_log_pos=106;
Start slave; #开启slave同步进程
Go to the main library and unlock the main library tag:
unlock tables; #解锁.

Show SLAVE Status\g #查看slave同步信息, the following appears
1. Row
Slave_io_state:waiting for Master to send event
master_host:192.168.21.169
Master_user:osyunweidbbak
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000019
read_master_log_pos:7131
relay_log_file:mysqlslave-relay-bin.000002
relay_log_pos:253
relay_master_log_file:mysql-bin.000019
Slave_io_running:yes
Slave_sql_running:yes
Replicate_do_db:osyunweidb
Replicate_ignore_db:mysql
Replicate_do_table:
Replicate_ignore_table:
1 row in Set (0.00 sec)
Test:
On the primary standby database server:
CREATE DATABASEbackupDEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci; Build Library
CREATE TABLE cc (ID int auto_increment,name varchar), primary key (ID)); Build table
INSERT into CC (name) VALUES (' Mr.chai '); Interpolation data
See if there are new tables and data on the standby database server.

Common commands:
1, the user created, after assigning permissions on the standby server with this command to test. If you can log in, it indicates that permissions and connectivity are not a problem.
Mysql-u osyunweidbbak-h 192.168.21.169-p #测试在从服务器上登录到主服务器
1. viewing Users and Permissions
SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;
Show grants for ' zhuoming_backup ' @ ' 10.34.34.232 ';
Allow root user access from 218.24.23.253
Mysql>grant all privileges on . To ' root ' @ ' 218.24.23.253 ' identified by ' [email protected] ';
mysql> flush Privileges; After you modify the permissions, you need to execute this sentence to make it effective.
1, manual synchronization of the main database, must be in the state of slave stop;
Mysql>change Master to master_host= ' 223.100.7.155 ', master_user= ' zhuoming_backup ', master_password= ' [email protected]!#% ', master_log_file= ' mysql-bin.000002 ', master_log_pos=2394;

2. View slave status
Mysql> show Slave status\g;

3, manually download from the main database
mysql> load data from master;

4, in the main standby can view the status of the respective process, the main previous binlogdump, from the last two is the I/o one is the SQL process, the I/O process is responsible for receiving updates, SQL is responsible for writing to the local library.
Show Processlist;

5. View parameter Configuration
Show variables like ' slave% ';

6. Viewing a user's rights
Show grants for ' zhuoming_backup ' @ ' 218.24.23.232 ';

7, modify the configuration file my.cnf, add slave_net_timeout = 600 under [mysqld], the default value of this parameter is 3600 (seconds, 1 hours), refers to the slave side (standby database) I/O thread is in the "Waiting for master to Send Event "state, which triggers the action of the re-connected master if the wait state exceeds slave_net_timeout time.
Slave_net_timeout = 600 The reasonable value of this parameter needs to be tested in the actual environment, the time is too long, easy to lead to synchronization, time is too short, then the opportunity to connect the host frequently.
In a system that has established a master-slave replication relationship, normally, after sending a com_binlog_dump command from the library to the main library, the main library has a new BINLOG event, which sends BINLOG to the standby repository. However, due to network failure or other reasons such as cross-firewall between master and slave databases, the main library is disconnected from the library or the main library has not sent binlog from the library for a long time. For example, in this case, the database cluster 10s has not been written, more than the value set by the Slave_net_timeout, from the library will be the main library to initiate a re-connect request. 5.6 Version Slave when initiating a re-connection request, MySQL will determine if there is a user name password in clear text, if any, send the above information to Error.log.

8 、--logs-slave-updates Parameters
This is configured in the My.cnf file.
Typically, updates received from the server from the primary server are not credited to its binary log. This option tells the server to log updates from its SQL thread into its own binary logs from the server. For this option to take effect, you must also start from the server with the--logs-bin option to enable binary logging. If you want to apply a chained replication server, you should use--logs-slave-updates. For example, you might want to set this up:
C, A-B
That is, a is the primary server from Server B and B is the primary server from server C. To be able to work, B must be both the primary server and the slave server. You must start A and B with--logs-bin to enable the binary log, and start B with the--logs-slave-updates option.

Set this option in the My.cnf file
Log_slave_updates=1
Of course, there are some students who may have this problem in this mechanism:
If A->b b->a such a dual-master architecture, A/b will open the Log_slave_updates option will not appear in an infinite loop state.
MySQL has filtered this issue, and each bin-log will record the source server_id of the execution statement. When slave reads the server_id of the statement is equal to its own ID, it will not execute, so we don't have to worry about whether A/b will loop indefinitely.

Based on the above scenario, MySQL's replication cluster will be more flexible and you can make a variety of chained copies if you need to. For example, A->b B->a B can also be b->c after setting log_slave_updates. So the data in A,c is also consistent.

Dual master Configuration My.cnf

2. Log_slave_updates = 1 #添加 (writes the replication event to Binlog, a server that does both the master and slave libraries This option must be turned on)
4. Replicate-same-server-id=0 #添加 (prevent MySQL loop update)
6. Relay_log_recovery = 1 #添加 (Mysqlrelay_log auto-repair function)
   * Starting from the mysql5.5.x version of the support, added the Relay_log_recovery parameter, the role of this parameter is: when the slave from the library down, if relay-log damaged, causing a part of the relay log is not processed, Automatically discards all relay-log that are not executed and retrieves the log from master again, guaranteeing the integrity of the relay-log. By default, this feature is turned off and the value of Relay_log_recovery is set to 1 o'clock, and it is recommended to turn on the slave from the library.
8. Server-id: Database identity, each database identity must be unique;
10. Auto_increment_increment=2: This is one of the most important parameters in circular mirroring, which means that the AutoIncrement is 2, that is, how much the field increments at a time, which will allow up to 2 databases to join the loop-mirrored array, and the auto-increment field will not repeat. Of course, if 10 hosts can set this value to 10.
11. Auto_increment_offset=1: This is one of the most important parameters in a circular mirror, representing the starting value of the self-increment field, the offset value of each primary database must be unique, and the 1 and Auto_increment_ Between increment (that is, this setting for two primary databases is 1 and 2, respectively).
    This is certainly not the case if the following settings are set, but if the business requires that the IDs must be contiguous, then the two parameters cannot be set:
    Main 1: Increment the initial value is 1, increments by 2 at a time. That is, the value will be 1, 3, 5 ....
    auto-increment-increment=2
    Auto-increment-offset=1
    Main 2: Increment the initial value is 2, increments by 2 at a time. That is, the value will be 2, 4, 6 ....
    auto-increment-increment=2
    auto-increment-offset=2
    After this configuration, the two devices in the generation of auto_increment column is the main 1 is 1, 3, 5, 7 odd, the main 2 is 2, 4, 6, 8 even. Note that the resulting value is not necessarily contiguous, and the last field may be 1, 2, 3, 4, 5, 7, 8, 10; The logic is this, if the main 1 executes the Insert 3 record, then the field is 1, 3, 5, and the primary 2 executes the INSERT, starting from 6, 6, 8, 10. Then the main 1 is 11, 13, 15.
    Synchronous replication failure due to binlog_ignore_db
    * This issue occurs when Binlog_format is set to row format, and the primary server MY.CNF configuration file is set to the parameter: binlog_ignore_db=xxx.
    Setting the MySQL binlog_fromat to mixed format resolves this issue. The slave server is able to perform the-e operation synchronously.
    Mysql-u root-p-E "insert into backup.test values (11)"
    Some of the types of MySQL Binlog_format are described at the end of this article (Mixed,statement,row)
    Today, a colleague told me a question, "MySQL Master uses binlog_ignore_db a library, all the statements executed with MYSQL-E will not write Binlog?" "
    Asked him about the situation, he was trying to copy in the master and slave, there is a library does not copy, checked his my.cnf configuration, binlog format to row, and he wanted the statement, as follows:
    MYSQL-E "CREATE table DB.TB like Db.tb1" Demo:

The result of creating the table, slave None, causes the cup to occur.
What is the cause of it? That is not caused by using the use library name, if used, you can record the Binlog,

Therefore, if you want to ignore a library copy on the slave, it is best not to use binlog_ignore_db this parameter, using REPLICATE-IGNORE-DB = Yourdb, replace it.
MySQL Binlog_format (Mixed,statement,row)
There are 3 different formats for binary logs (Binlog) in MySQL 5.5: Mixed,statement,row, the default format is Statement. Summarize the pros and cons of these three format logs.

MySQL Replication replication can be based on a single statement (Statement level), or based on a record (Row level), you can set the replication levels in MySQL configuration parameters, different replication level settings will affect the Master side of the B In-log log format.

1. The
2. row
   log is recorded as a modified form for each row of data, and then the same data is modified on the slave side.
   Pros: In row mode, Bin-log can not record the context-sensitive information of the executed SQL statement, only the record that the record was modified, and how to modify it. So the log content of row will be very clear to record the details of each row of data modification, very easy to understand. There are no stored procedures or function in certain situations, and trigger calls and triggers cannot be copied correctly.
   Disadvantage: In row mode, all executed statements are recorded in the log when logged, which may result in a large amount of log content, such as an UPDATE statement:
   1 Update product SET Owner_ member_id = ' B ' WHERE owner_member_id = ' A '
   is executed, the log does not record the event corresponding to the UPDATE statement (MySQL logs the Bin-log log as an event), but each of the entries updated by this statement Record changes, so that many events are recorded as a number of records are updated. Naturally, the amount of Bin-log logs will be very large. Especially when executing statements such as ALTER TABLE, the amount of log generated is staggering. Because MySQL handles table structure change statements such as ALTER TABLE, each record in the entire table needs to be changed, in effect rebuilding the entire table. Then each record of the table is recorded in the log.
4. Statement
   Each SQL that modifies the data is recorded in the Bin-log of master. Slave when replicating, the SQL process parses the same SQL that was executed sing Woo the original master side.
   Advantages: In statement mode, the first is to solve the disadvantage of row mode, do not need to record each row of data changes, reduce the Bin-log log volume, save I/O and storage resources, improve performance. Because he only needs to record the details of the statements executed on master, and the context in which the statements are executed.
   Cons: In statement mode, because he is the execution statement of the record, so, in order for these statements to be executed correctly at the slave end, he must also record some relevant information about each statement at the time of execution, that is, contextual information, to ensure that all statements in the slave The end Cup is executed with the same results as when it is executed on the master side. In addition, because MySQL is now developing relatively fast, a lot of new features continue to join, so that the replication of MySQL encountered a large challenge, natural replication involves more complex content, bugs will be more prone to appear. In statement, there are a number of things that have been found to cause MySQL replication problems, mainly when modifying the data when using some specific functions or functions, such as: Sleep () function in some versions can not be copied correctly, in the stored procedure used The last_insert_id () function may cause inconsistent IDs on slave and master, and so on. Because row is recorded on a per-row basis, a similar problem does not occur.
5. Mixed
   As you can see from the official documentation, the previous MySQL has been only based on the statement copy mode until the 5.1.5 version of MySQL started to support row replication. Starting with 5.0, MySQL replication has resolved issues that are not correctly replicated in a large number of older versions. However, due to the emergence of stored procedures, MySQL Replication has brought more new challenges. In addition, the official documentation says that, starting with version 5.1.8, MySQL provides a third replication mode except for Statement and Row: Mixed, which is actually a combination of the first two modes. In Mixed mode, MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, choosing between statement and row. The statment in the new version is still the same as before, recording only the statements executed. The new version of MySQL in the row mode is also optimized, not all changes will be in the row mode to record, such as when the table structure changes will be recorded in the statement mode, if the SQL statement is actually update or delete and other modified data statements, Then the changes to all rows are recorded.
   Additional reference information
   In addition to the following scenarios, the Binlog format can be dynamically changed at run time:
   . The middle of a stored procedure or trigger;
   . Enabled the NDB;
   . The current session uses row mode, and a temporary table has been opened;
   If Binlog uses Mixed mode, the Binlog mode is automatically changed from statement mode to row mode in the following cases:
   . When a DML statement updates a NDB table;
   . When the function contains a UUID ();
   . 2 or more tables containing the Auto_increment field are updated;
   . When executing the INSERT DELAYED statement;
   . When using UDFs;
   . The view must require the use of the row, such as the UUID () function when establishing the view;
   To set the master-slave replication mode:
   1
   2
   3
   4 Log-bin=mysql-bin
   #binlog_format = "STATEMENT"
   #binlog_format = "ROW"
   binlog_format= "MIXED"
   You can also dynamically modify the format of the Binlog at run time. For example:
   1
   2
   3
   4
   5
   6 mysql> SET SESSION binlog_format = ' STATEMENT ';
   mysql> SET SESSION binlog_format = ' ROW ';
   mysql> SET SESSION binlog_format = ' MIXED ';
   mysql> SET GLOBAL binlog_format = ' STATEMENT ';
   mysql> SET GLOBAL binlog_format = ' ROW ';
   mysql> SET GLOBAL binlog_format = ' MIXED ';
   Comparison of the two modes:
   Statement Advantages
   long history, mature technology;
   The resulting binlog file is small;
   The binlog contains all the database modification information, which can be used to audit the database security and so on.
   Binlog can be used for real-time restores, not just for replication;
   Master-slave version can be different from the server version can be higher than the main server version;
   Statement Disadvantages:
   Not all UPDATE statements can be copied, especially when there is an indeterminate operation;
   Replication may also occur when invoking a UDF with uncertainties;
   Statements that use the following functions cannot be duplicated:
   • Load_file ()
   • UUID ()
   • USER ()
   • Found_rows ()
   • Sysdate () (unless the –sysdate-is-now option is enabled at startup)
     INSERT ... SELECT produces more row-level locks than RBR;
     Replication requires more row-level locks than row requests when performing a full-table scan (where the index is not used in the) UPDATE;
     For InnoDB tables with auto_increment fields, the INSERT statement blocks other INSERT statements;
     For some complex statements, the consumption of resources from the server will be more serious, and in row mode, only the change of the record will have an impact;
     A stored function (not a stored procedure) executes the now () function at the same time it is called, which can be said to be bad or good;
     The identified UDF also needs to be executed from the server;
     The data table must be almost consistent with the primary server, or it may cause replication errors;
     Executing complex statements can consume more resources if there is an error;
     Row Advantages
     Any situation can be copied, which is the most safe and reliable for replication;
     As with most other database systems, as well as replication skills;
     In most cases, the copy will be much faster if there is a primary key from the table on the server.
     There are fewer row locks when copying the following statements:
   • INSERT ... SELECT
   • INSERT containing the Auto_increment field
   • UPDATE or DELETE statement with no strings attached or changes to many records
     Fewer locks when executing insert,update,delete statements;
     It is possible to perform replication from a server with multithreading;
     Row disadvantage
     The generated Binlog log volume is much larger;
     A complex rollback will contain a large amount of data in the Binlog;
     When an UPDATE statement is executed on the primary server, all changed records are written to Binlog, and statement is only written once, which results in frequent binlog write concurrent requests;
     Large BLOB values produced by UDFs can cause replication to become slower;
     Can not see from the Binlog to copy what statements (encrypted);
     When executing a stack of SQL statements on a non-transactional table, it is best to adopt statement mode, otherwise it is easy to cause the data inconsistency of the master-slave server;
     In addition, for the system library MySQL inside the table changes when the processing criteria are as follows:
     If the use of Insert,update,delete direct operation of the table, the log format according to the Binlog_format set up records;
     If the use of Grant,revoke,set PASSWORD and other management statements to do, then in any case to use the statement mode record;
     After using statement mode, it can deal with many original key duplication problems.

Common errors
MySQL master-slave replication, often encounter errors resulting in slave-side replication interrupt, this time generally requires manual intervention, skipping errors to continue
There are two ways of skipping errors:
1. Skip a specified number of transactions:
Mysql>slave stop;
Mysql>set GLOBAL sql_slave_skip_counter = 1; #跳过一个事务
Mysql>slave start;

2. Modify the MySQL configuration file by slave_skip_errors parameters to jump all errors or specify types of errors
Vi/etc/my.cnf
[Mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error No type error
#slave-skip-errors=all #跳过所有错误

Mysql Binlog Three types of formats introduced and analyzed
A Mysql Binlog Format Introduction
Mysql Binlog logs are available in three formats, statement,mixed, respectively, and row!
1.Statement: Every SQL that modifies data is recorded in Binlog.
Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )
Cons: Because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and user-defined functions (UDF) can cause problems ).
Statements that use the following functions cannot be copied:

• load_file ()
• UUID ()
• USER ()
• found_rows ()
• sysdate () (unless--sysdate-is-is enabled at startup) Now option)
  at the same time in the insert ... SELECT produces more row-level locks than RBR
  2.Row: The context-sensitive information for SQL statements is not logged, and only which records are saved are modified.
  Pros: Binlog can not record the context-sensitive information of an executed SQL statement, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations.
  Cons: All executed statements are recorded as per-row records as they are recorded in the log, which can result in a large amount of log content. For example, an UPDATE statement, modify multiple records, the Binlog in each of the changes will have a record, which will cause the Binlog log volume is very large, especially when executing the ALTER TABLE and other statements, because the table structure changes, each record changes, Then each record of the table is recorded in the log.
  3.Mixedlevel: Is a mixture of the above two levels, the general statement modification using the statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing between statement and row. The new version of the MySQL Squadron row level mode is also optimized, and not all changes are recorded at the row level. The statement pattern is recorded when a table structure change is encountered. For statements that modify data such as update or delete, the changes are recorded for all rows.

Two. Binlog basic configuration and format setting
1. Basic formulation
MySQL binlog log format can be specified through the properties of MySQL my.cnf file Binlog_format. as follows:
Binlog_format = MIXED//binlog log format
Log_bin = Directory/mysql-bin.log//binlog log name
Expire_logs_days = 7//binlog Expired cleanup time
Max_binlog_size 100m//binlog per log file size
2.Binlog Log Format Selection
MySQL default is to use the statement log format, the recommended use of mixed.
Because of some special use, you can consider using rowed, such as yourself through the Binlog log to synchronize data changes, which will save a lot of related operations. For Binlog data processing can be very easy, relative mixed, parsing is also very easy (assuming that the increase in the amount of log volume of the IO cost within the scope of tolerance).
3.mysqlbinlog Format Selection
MySQL for the selection of log format principle: If the use of insert,update,delete, such as the direct operation of the table, the log format according to Binlog_format settings and records, if the use of Grant,revoke,set PASSWORD If the management statement is done, then the SBR mode is used to record it anyway.

Three Mysql Binlog Log Analysis
View the specific MySQL log via the Mysqlbinlog command, as follows:
/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////
SET timestamp=1350355892/! /;
BEGIN
/! /;

At 1643330

#121016 10:51:32 Server ID 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0
SET timestamp=1350355892/! /;
Insert into T_test ....)
/! /;

At 1643885

#121016 10:51:32 Server ID 1 end_log_pos 1643912 Xid = 0
commit/! /;
/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////
1. Start the time of the thing:
SET timestamp=1350355892/! /;
BEGIN
2.sqlevent Beginnings
#at 1643330: For the beginning of the event, is starting with 1643330 bytes.
3.sqlevent occurrence point in time
#121016 10:51:32: Is the time when the event occurred,
4.serverId
Server ID 1: ServerID for Master
5.sqlevent end point and time spent, error code
End_log_pos 1643885: The end of the event, which ends at 1643885 bytes.
Exectime 0: The time spent
Error_code=0: Error code
Xid: Event indicates a committed XA transaction
Mixed Log Description:
During slave log synchronization, for time functions such as now, the mixed log format produces the corresponding unix_timestamp () *1000 time string in the log, slave when the synchronization is complete, The sqlevent takes place to ensure the accuracy of the data. In addition, for some functional functions slave can complete the corresponding data synchronization, and for some of the above specified similar to the UDF function, resulting in slave can not be known, the row format will be stored in these binlog, to ensure that the resulting binlog can be slave to complete the data synchronization.

Mysql Server Master master configuration