MySQL ultra-simple configuration-master Configuration

MySQL synchronization is how simple, despise those selfish people, my notes: note. youdao. comshare? Idd70f203ee0407a475fcfa47b62b51500nbsp; technology is the top of the list of friends who need to share information. ######################################## ############# MySQLserver-id

MySQL synchronization is how simple, despise those selfish people, my notes: http://note.youdao.com/share? Id = d70f203ee0407a475fcfa47b62b51500nbsp. ######################################## ############# MySQL server-id =

How simple MySQL synchronization is to despise selfish people,
My notes:
Http://note.youdao.com/share? Id = d70f203ee0407a475fcfa47b62b51500 & type = note
######################################## ##############
Technology is the top of the list for sharing.
######################################## ############# MySQL

server-id= 1log-bin=mysql-binlog-slave-updates#slave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=1replicate_wild_ignore_table=mysql.%replicate_wild_ignore_table=test.%innodb_file_per_table=1binlog_format =mixed

server-id= 2log-bin=mysql-binlog-slave-updates#slave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=2replicate_wild_ignore_table=mysql.%replicate_wild_ignore_table=test.%innodb_file_per_table=1binlog_format =mixed

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.1.46  Ahost

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.1.47  Bhost

####################################### Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. * filter: input accept [0: 0]: forward accept [0: 0]: output accept [0: 0]-a input-m state-state ESTABLISHED, RELATED-j ACCEPT-a input-p icmp-j ACCEPT-a input-I lo-j ACCEPT-A INPUT-m state-state NEW-m tcp-p tcp-dport 22 -j ACCEPT-a input-m state-state NEW-m tcp-p tcp-dport 80-j ACCEPT-a input-m state-state NEW-m tcp-p tcp-state- dport 3306-j ACCEPT-a input-j REJECT-reject-with icmp-host-prohibited-a forward-j REJECT-reject-with icmp-host-prohibited COMMIT #### ################################# firewall 1) enable upon restart: chkconfig iptables on Disabled: chkconfig iptables off firewall 2) takes effect immediately. Enable upon restart: Disable service iptables start: service iptables stop ##################################### this is generally caused by transaction rollback: solution: mysql> slave stop; mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; mysql> slave start; ######################################

Solution 1: The Slave_ SQL _Running: No1. program may perform write operations on the slave. 2. it may also be caused by transaction rollback after the Server Load balancer instance restarts. last_ SQL _Errno: 1677 Last_ SQL _Error: Column 1 of table 'test. t 'could not be converted from type 'int' to type' bigint (20) 'solution: This case was found on the Internet and I tried it myself. The error message indicates that an SQL statement that converts the field type cannot be executed on slave. In fact, this is not directly caused by such statements, but indirectly caused (some previous operations resulted in inconsistent Field Types in the master and slave tables, and an error will be reported when DML is performed on the table ). It means that the results of the master and slave tables are inconsistent when performing DML operations on the table t. For example, field 1 of the master table t is of the int type, however, an error is returned because field 1 of the preceding t is of the bigint type. So why should we report an error? This is from the security perspective, because if the field types are inconsistent, it may cause data truncation and other problems. What is the solution? The slave_type_conversions parameter is used for control. It has three values: ALL_LOSSY: Only lossy conversion is supported. What is lossy conversion? For example, if a value is originally stored as 9999999999999 in bigint type, data inconsistency is inevitable because it is converted to int type. ALL_NON_LOSSY: Only lossless conversion is supported. Only ALL_LOSSY can be converted without lossless. ALL_NON_LOSSY: lossy/uncomputation conversion supports null values, that is, this parameter is not set: the fields of master and slave must be of the same type. Note: All of the above conditions are valid only when binlog_format = ROW. Last_ SQL _Errno: 1194 Last_ SQL _Error: Error 'table 'traincenter' is marked as crashed and shoshould be retried red' on query. default database: 'bucketballman '. query: 'Update traincenter set points = '4', pointstime = '000000' where uid = '000000' limit 1 'solution: the myisam table traincenter is damaged and you can directly repair the table. As to why myisam tables are more vulnerable to damage than innodb tables, I think there are two reasons: 1. innodb has a double write mechanism, and the half write page can be used for restoration, the second innodb is the transaction engine, with all operations being transactional, while myisam is non-transactional, with half write but operation termination. Last_IO_Errno: 1236 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' solution: the binlog file on the master database does not exist, but the corresponding record exists in the index file. The reason for this error is that, due to the long replication interruption time, no one has handled the alarm and the interruption time exceeds the binlog expiration time on the master, the binlog required for restoring the replication has been deleted due to its expiration time, so the instance cannot be rebuilt. Stop slave; reset slave; slave start; Last_IO_Errno: 1593 Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the -- replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it ). solution: the server-id of the master-slave configuration is the same, but in the master-slave replication Environment Binlog events with the same server-id will be filtered out. For more information about the meaning of server-id, see how replication works. This is generally because you forget to modify the server-id when copying the configuration file. This type of problem is also easy, so you can proceed with caution. Last_Errno: 1053 Last_Error: Query partially completed on the master (error on master: 1053) and was aborted. there is a chance that your master is inconsistent at this point. if you are sure that your master is OK, run this query manually on the slave and then restart the slave with set global SQL _SLAVE_SKIP_COUNTER = 1; START SLAVE ;. query: 'insert... solution: the query is partially completed on the master and then terminated. This is the result of the myisam table. Because myisam does not support transactions, a query may be completed and then fail. The solution is to skip a binlog event. However, it is recommended to query whether the corresponding records exist on the master before you confirm the skipping, because the error message also gives the query statements that it considers to be executed on the master and then terminated. Last_ SQL _Errno: 1666 Last_ SQL _Error: Error executing row event: 'cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT. 'solution: the background of this case is to copy the ABC structure. binlog_format = statement set in B and C, MIXED in, therefore, when B tries to redo the relay log from A and record the binlog (sent to C), it is found that the binlog_format of the relay log is inconsistent with the binlog_format set by B. At that time, I directly changed the binlog_format = MIXED solution of BC. Last_Errno: 1032 Last_Error: cocould not execute Update_rows event on table db. table; Can't find record in 'table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000064, end_log_pos 158847 work und: this occurs under binlog_format = row replication. The reason is that row format replication is the strictest. Therefore, if mysql does not find the record to be updated in the slave database, it indicates that the master and slave data are inconsistent and an error is returned. By the way, for the row format binlog, if an update operation does not actually update a row, this operation will not remember the binlog, because the purpose of the binlog in row format is to record only the rows that have changed. Therefore, this solution depends on your actual application. The best way is to redo the slave. Last_Errno: 28 Last_Error: Error in Append_block event: write to '/tmp/SQL _LOAD-32343798-72213798-1.data' failed solution: First say Error cause: the master database executes load data infile, the files stored in load data infile after synchronization to the slave database are stored in/tmp by default (controlled by the slave_load_tmpdir parameter), and an error is returned because/tmp is not enough space. Therefore, you only need to set slave_load_tmpdir from the database to a partition with sufficient disk space.

① ######################### Show variables like 'server _ id '; ########################## server-id = 1log-bin = mysql-binlog-slave-updates # slave-skip-errors = allsync_binlog = Signature = 2auto_increment_offset = 1innodb_file_per_table = 1binlog_format = mixed ##################### ##### grant replication client, replication slave on *. * TO 'repl' @ '%' identified by '123 '; ④ ######################### show grants for repl @ '% '; ⑤ ######################### flush privileges; ########################## show master status \ G ******** * ****** 1. row ************** File: mysql-bin.000005 Position: 37312 Binlog_Do_DB: Binlog_Ignore_DB: 7######################### change master tomaster_host = '2017. 168.1.47 ', master_user = 'repl', master_password = '000000', master_log_file = 'mysql-bin.000006', master_log_pos = 123456, master_port = 11792; region ######################### show slave status \ G region ####### ################## start slave; ########################### reset slave ##### #######################

① ######################### Show variables like 'server _ id '; ########################## server-id = 9log-bin = mysql-binlog-slave-updates # slave-skip-errors = allsync_binlog = response = 2auto_increment_offset = 2innodb_file_per_table = 1binlog_format = mixed ##################### ##### grant replication client, replication slave on *. * TO 'repl' @ '%' identified by '123 '; ④ ######################### show grants for repl @ '% '; ⑤ ######################### flush privileges; ########################## show master status \ G ******** * ***** 1. row **************** File: mysql-bin.000006 Position: 11792 Binlog_Do_DB: Binlog_Ignore_DB: 7######################### change master tomaster_host = '2017. 168.1.46 ', master_user = 'repl', master_password = '000000', master_log_file = 'mysql-bin.000005', master_log_pos = 123456, master_port = 37312; region ######################### show slave status \ G region ####### ################## start slave; ########################### reset slave ##### #######################