Mysql master-slave synchronous backup

The mysql master-slave synchronous backup website has a background service called the searchEngine project, which is built based on Lucene. It mainly provides index building and retrieval functions. The search engine queries the mysql database and then builds indexes based on the data status, here, the Program actively polls the status of adding, deleting, modifying, and deleting data columns in mysql query at intervals. Then, the corresponding Lucene index is added, deleted, and changed, and the index status is updated to the data column, it is convenient to differentiate which data is not indexed during round-robin. Mysql mainly uses the myisam engine, which causes the java program to frequently lock tables when constructing an index round-robin database, and cannot respond when querying pages. Mysql master-slave synchronization is implemented here, and all business updates and searches are performed on the master, while the Lucene backend Service operates the slave database and also acts as a backup. Here we will sort out the backup configuration. Master Database: 192.168.0.102, mysql 5.6.12, centos slave database slave: 192.168.0.100, mysql 5.5.13, centos are compiled by source code. You can check my post http://www.bkjia.com/database/201306/221828.html . All data tables of the master and slave databases have the same structure. Previously I made a dual master configuration, you can view here http://www.bkjia.com/database/201306/221828.html 1. the master configuration here only captures the configurations required for some synchronization, and other optimization aspects are not considered for the moment. cnf: 01 [client] 02 port = 330603 [mysqld] 04 socket =/usr/local/mysql. sock05basedir =/usr/local/mysql06datadir =/usr/local/mysql/data07log-error =/usr/local/mysql/data/mysql_error.log08pid-file =/usr/local/mysql/data/mysql. pid09log-bin =/usr/local/mysql/mysql-bin.log # the binary file must be 10explicit_defaults_for_timestamp = true11innodb_flush_log_at_trx_commit = 2 # log flus H to the disk. 2 indicates writing data to the cache to improve performance, OS crash will lose part of the data 12 # master13server_id = 114expire_logs_days = 1015max_binlog_size = 1G16binlog-do-db = webportal # Sync database 17binlog-ignore-db = information_schema18binlog-ignore-db = performance_schema19binlog-ignore-db = test create a sync user, create a connection account for the SLAVE server on the master server. This account must be granted the replicaiton slave permission. Log on to mysql on the master server and run 1 grant replication slave on *. * to 'replicase' @ '192. 168.0.100 'identified by '20170101'; note: the ip address of the client can be replaced by %, indicating that any client is allowed, for example: 192.168.0. %. Indicates that all hosts of this segment can be used as clients. View master status: 1 mysql> show master status \ G; 2 **************************** 1. row ************************** 3 File: mysql-bin.0000014 Position: 4165 Binlog_Do_DB: webportal6 Binlog_Ignore_DB: information_schema, cece_schema, mysql, test7Executed_Gtid_Set: 81 row in set (0.00 sec) Pay attention to the above File and Position: slave configuration will be used. 2. slave configuration 1 [mysqld] 2server_id = 23log-bin = mysql-bin.log4replicate-do-db = webportal use the change master statement to specify the synchronization location 1 mysql> change master to master_host = '123. 168.0.102 ', master_user = 'replicase', master_password = '000000', master_log_file = 'mysql-bin.000001', master_log_pos = 123456; 2 3 Note: master_log_file, master_log_pos is determined by the status value found by the master above. Master_log_file corresponds to File and master_log_pos corresponds to Position. Enable slave; 1 start slave; Disable slave; 1 stop slave; view status: 1 show slave status; the following error occurs: Last_IO_Error: got fatal error 1236 from master when reading data from> binary log: 'slave can not handle replication events with the checksum that> master is configured to log; the first event 'mysql-bin.000001 'is because mysql5.6 is used by the master, and binlog_checksum is set to crc32 by default. If slave uses Version 5.5 or earlier, set the binglog_checksum of the master to none. Binlog_checksum = none restart master :. /mysqld restart view slave: show slave status \ G; 01 mysql> show slave status \ G; 02 **************************** 1. row ********** 03 Slave_IO_State: Waiting for mas04 Master_Host: 192.168.0.10205 Master_User: replication06 Master_Port: 330607 Connect_Retry: 6008 Master_Log_File: mysql-bin.0000009 runtime: 12010 Relay_Log_File: YZ-relay-bin.0011 (Relay_Log_Pos: 26) 612 labels: mysql-bin.0000013 Slave_IO_Running: Yes # Must be yes14 Slave_ SQL _Running: Yes # Must be yes15 labels: webportal16 labels: 17 Replicate_Do_Table: 18 labels: 19 labels: 20 labels: 21 Last_Errno: 022 Last_Error: 23 Skip_Counter: 024 Exec_Master_Log_Pos: 12025 Relay_Log_Space: 41926 Until_Condition: None27 Until_Log _ File: 28 Until_Log_Pos: 029 rows: No30 rows: 31 rows: 32 Master_SSL_Cert: 33 rows: 34 Master_SSL_Key: 35 rows: No37 rows: 038 Last_IO_Error: 39 rows: 040 Last_ SQL _Error: 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 1431 row in set (0.00 sec) Note: The Slave_IO and Slave_ SQL processes must run properly, that is, YES Otherwise, they are all error states (for example, none of them are errors ).