MySQL replication (1) --- what is binary log _ MySQL

MySQL replication (1) --- what is binary log bitsCN.com?

Compared with small sites, database administrators of large sites must do the following in advance:

Provides a recovery plan for core business data in the event of a disaster. Theoretically, this process must be executed at least once.

Provides optimization plans by collecting a large amount of user data and monitoring the load on each node of the website

Rapid horizontal scaling plan when the number of users increases dramatically

It is important to plan and prepare the necessary quick response for all these tasks.

To ensure site response and availability, we need to do two things: System data backup and redundancy. Backup can restore a node to its state before it crashes. backup can be performed as needed, such as timely recovery (PITR: point-in-time-recovery) and online backup; redundancy ensures that the site continues to run even if one or more nodes stop the service. backup is generally achieved through hardware copies, so that several instances can run in parallel, multiple available copies of the same data are stored on several machines through replication. Main application scenarios of replication: 1) scale out for high read/write ratio; 2) add redundancy to ensure high availability, such as dual-master setup ).

Binary log)

Binary logs are required for the replication process. Binary logs are used to record changes to tables in the database and then for replication and PITR. They are also used in a few audit cases.

Traditionally, MySQL replication records SQL statements that generate changes, which are called statement-based replication ). Statement-based replication fails to ensure that all statements are correctly copied. Therefore, MySQL 5.1 also provides row-based replication ).

View binary logs:

# Forcibly fl cached items to LOGS, and generate a Rotate event to be written to the binlog flush logs; # This command is not recommended for databases that have been in normal use for a long time. you need to add parameters, specify the binlog file name [IN 'xxxx'] show binlog events/G

Fields contained in binary logs:

Event_type: for example, Format_desc, Query, RotateServer_id: the server where the event is created idLog_name: the file name of the event. an event can only be stored in one file. Pos: the start position of the event in the file, and the first byte of the event End_log_pos: the end position of the event in the file, that is, the start position of the next event Info: the information of the specific event. the Query is an SQL statement.

Binary log structure and content:

A binary log is not a separate file. it contains a set of binary log files that store the actual content and a binary log index file. Each binary log file starts with format description event and ends with rotate event. The rotate event contains the name of the next binary log file to indicate the file to which the binary log is written. Therefore, a new binlog file is created during the flush logs operation.

Obtain the binary log file currently being written:

Show master status/G

The reset master command deletes all binary log files and clears the binary log index files. The reset slave command deletes all files used for Slave replication and starts again.

The change master to command is used to change some parameters of the slave connection TO the master, including the parameters used by the slave TO read the binary log file of the master. For example, MASTER_LOG_FILE and MASTER_LOG_POS are used to specify the binlog location where the master starts to send events.

How to create a new Slave

1: configure the new Slave2: back up the Master (or back up the Slave that has already copied the Master) 3: next to the binlog location of the backup 4: restore the backup on the new Slave 5: configure Slave to start copying from this binlog location

The difference lies in the second step. one is to back up data directly from the Master, and the other is to back up data through the existing Slave. The two types are described below.

1: Clone Master:

# Refresh all TABLES and LOCK the database to prevent the database from changing flush tables with read lock before checking the binlog location; # obtain the current binlog file and posSHOW master status/G # back up mastermysqldump -- all-databases -- host = master-1> backup. SQL # UNLOCK TABLES; # Restore the backup mysql on slave -- host = slave-1

In fact, the mysqldump Command provides the master_data option to automatically dump MASTER_LOG_FILE and MASTER_LOG_POS information to backup. SQL.

In addition, flush tables with read lock is not secure for InnoDB, because although the table will be locked and no new transactions will be generated, there are still some activities in the background to continue.

Therefore, the following method can be used to securely create backups of InnoDB data tables.

1: shut down the server and copy the file. If the database is large, it is best to adopt this method, because using mysqldump for data recovery will be slow. 2: After executing flush tables with read lock, using mysqldump3: after executing flush tables with read lock, snapshot methods, such as LVM (Linux) and ZFS (Solaris) Snapshots

2: clone Slave:

# To prevent inconsistent backup images, stop replicationSTOP Slave before backing up SLAVE; # determine where to start replication. Note Relay_Master_Log_File and Exec_Master_Log_PosSHOW slave status/G # configure a new slave, point to masterCHANGE MASTER TOMASTER_HOST = 'master-1', MASTER_PORT = 3306, MASTER_USER = 'slave-1', MASTER_PASSWORD = 'xxxx', MASTER_LOG_FILE = 'master-bin.000042 ', MASTER_LOG_POS = 546632; # start a new slaveSTART SLAVE

From foxracle

BitsCN.com