Mysql master-slave synchronous backup _ MySQL

Mysql master-slave synchronous backup bitsCN.com

Mysql master-slave synchronous backup

The 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

All are compiled using source code. you can view my post http://www.bitscn.com/database/201306/221828.htmlduring the installation process. 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.bitsCN.com/database/201306/221828.html

1. master configuration

The configuration here only captures the configurations required for some synchronization, and other optimization aspects are not considered for the moment.

My. cnf:

01

[Client]

02

Port = 3306

03

[Mysqld]

04

Socket =/usr/local/mysql. sock

05

Basedir =/usr/local/mysql

06

Datadir =/usr/local/mysql/data

07

Log-error =/usr/local/mysql/data/mysql_error.log

08

Pid-file =/usr/local/mysql/data/mysql. pid

09

Log-bin =/usr/local/mysql/mysql-bin.log # The binary file must be enabled

10

Explicit_defaults_for_timestamp = true

11

Innodb_flush_log_at_trx_commit = 2 # flush logs to the disk. 2 indicates that the logs are written to the cache to improve performance. part of the data is lost when the operating system crashes.

12

# Master

13

Server_id = 1

14

Expire_logs_days = 10

15

Max_binlog_size = 1G

16

Binlog-do-db = webportal # synchronize databases

17

Binlog-ignore-db = information_schema

18

Binlog-ignore-db = performance_schema

19

Binlog-ignore-db = mysql

20

Binlog-ignore-db = test

Create a synchronization user and create a connection account for the SLAVE server on the master server. the 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 '20140901 ';

Note: The replication@192.168.0.100 here is the client ip can be replaced with %, to allow any client, 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.000001

4

Position: 416

5

Binlog_Do_DB: webportal

6

Binlog_Ignore_DB: information_schema, cece_schema, mysql, test

7

Executed_Gtid_Set:

8

1 row in set (0.00 sec)

Note that the above File and Position: slave configuration will be used.

2. slave configuration

1

[Mysqld]

2

Server_id = 2

3

Log-bin = mysql-bin.log

4

Replicate-do-db = webportal

Use the change master statement to specify the synchronization location

1

Mysql> change master to master_host = '192. 168.0.102 ', master_user = 'replicase', master_password = '000000', master_log_file = 'MySQL-bin.000001', master_log_pos = 192;

2

3

Note: master_log_file and master_log_pos are determined by the status values found by the master. 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'

This is because the master uses mysql5.6 and binlog_checksum sets 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 mas

04

Master_Host: 192.168.0.102

05

Master_User: replication

06

Master_Port: 3306

07

Connect_Retry: 60

08

Master_Log_File: mysql-bin.00000

09

Read_Master_Log_Pos: 120

10

Relay_Log_File: YZ-relay-bin.00

11

Relay_Log_Pos: 266

12

Relay_Master_Log_File: mysql-bin.00000

13

Slave_IO_Running: Yes # yes

14

Slave_ SQL _Running: Yes # yes

15

Replicate_Do_DB: webportal

16

Replicate_Ignore_DB:

17

Replicate_Do_Table:

18

Replicate_Ignore_Table:

19

Replicate_Wild_Do_Table:

20

Replicate_Wild_Ignore_Table:

21

Last_Errno: 0

22

Last_Error:

23

Skip_Counter: 0

24

Exec_Master_Log_Pos: 120

25

Relay_Log_Space: 419

26

Until_Condition: None

27

Until_Log_File:

28

Until_Log_Pos: 0

29

Master_SSL_Allowed: No

30

Master_SSL_CA_File:

31

Master_SSL_CA_Path:

32

Master_SSL_Cert:

33

Master_SSL_Cipher:

34

Master_SSL_Key:

35

Seconds_Behind_Master: 0

36

Master_SSL_Verify_Server_Cert: No

37

Last_IO_Errno: 0

38

Last_IO_Error:

39

Last_ SQL _Errno: 0

40

Last_ SQL _Error:

41

Replicate_Ignore_Server_Ids:

42

Master_Server_Id: 1

43

1 row in set (0.00 sec)

Note: The Slave_IO and Slave_ SQL processes must run properly, that is, the YES state. otherwise, all processes are in the wrong state (for example, any of them is in the wrong state ).

BitsCN.com