binlog Information Service binlog When turned on, you can view its location information in the configuration file or in the myslq View from the command line:
show variables like '%log_bin%';
+---------------------------------+-------------------------------------+
| Variable_name                   | Value                               |
+---------------------------------+-------------------------------------+
| log_bin                         | ON                                  |
| log_bin_basename                | /var/lib/mysql/3306/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/3306/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                 |
| log_bin_use_v1_row_events       | OFF                                 |
| sql_log_bin                     | ON                                  |
+---------------------------------+-------------------------------------+
binlog File opening binlog After that, it will be produced in the data directory (default) host-bin.n(specific binlog Information) Documents and host-bin.index Index files (records) binlog List of files). When binlog Log full(binlog Size max_binlog_size，Default 1 G),Or the database reboots to produce new files, but you can also manually switch them to regenerate new files( flush logs)；In addition, if you are using a large transaction, it may also be possible because a transaction cannot span two files binlog Refresh Files When Files Are Not Full
mysql> show binary logs; //View the list of binlog files.
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |  10343266 |
| mysql-bin.000004 |  10485660 |
| mysql-bin.000005 |     53177 |
| mysql-bin.000006 |      2177 |
| mysql-bin.000007 |      1383 |
+------------------+-----------+

   a. mysqlbinlog: /usr/bin/mysqlbinlog  mysql-bin.000007
        - mysqlbinlog yes mysql Officially provided one binlog View tools,
        - Can also be used–read-from-remote-server Reading binary logs from remote servers,
        - Can also be used--start-position --stop-position,--start-time= --stop-time Accurate analysis binlog Journal
        
        //The interception position 1190-1352 binlog is as follows:
        ***************************************************************************************
        # at 1190//Starting Point of Event
        #171223 21:56:26 server id 123  end_log_pos 1190 CRC32 0xf75c94a7 	Intvar
        SET INSERT_ID=2/*!*/;
        #171223 21:56:26 server id 123  end_log_pos 1352 CRC32 0xefa42fea 	Query	thread_id=4	exec_time=0	error_code=0
        SET TIMESTAMP=1514123786/*!*/;              //The starting point of a transaction (each at is an event)
        insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"  //sql statement
        /*!*/;
        # at 1352
        #171223 21:56:26 server ID 123 end_log_pos 1383 CRC32 0x72c565d3 Xid = 5// execution time, and location stamp, Xid: Event indicates submitted XA transactions
        ***************************************************************************************
    
    b.Direct command line parsing
        SHOW BINLOG EVENTS
            [IN 'log_name'] //The binlog file name to query
            [FROM pos]  
            [LIMIT [offset,] row_count]  
       
        1190-135 As follows: mysql> show binlog events in 'mysql-bin.000007' from 1190 limit 2\G
        *************************** 13. row ***************************
           Log_name: mysql-bin.000007
                Pos: 1190
         Event_type: Query  //Event type
          Server_id: 123
        End_log_pos: 1352   //End pose point, the starting point of the next event
               Info: use `test`; insert into tb_person  set name="name__2", address="beijing", sex="man", other="nothing"
        *************************** 14. row ***************************
           Log_name: mysql-bin.000007
                Pos: 1352
         Event_type: Xid
          Server_id: 123
        End_log_pos: 1383
               Info: COMMIT /* xid=51 */

   a.To configure master
        //It mainly includes setting up replication account and granting REPLICATION SLAVE permission. The specific information will be stored in master.info file, and opening binlog.
        mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'test'@'%';
        mysql> show variables like "log_bin";
            +---------------+-------+
            | Variable_name | Value |
            +---------------+-------+
            | log_bin       | ON    |
            +---------------+-------+
        //View the master's current binlog MySQL status: MySQL > show master status;
            +------------------+----------+--------------+------------------+-------------------+
            | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +------------------+----------+--------------+------------------+-------------------+
            | mysql-bin.000003 |      120 |              |                  |                   |
            +------------------+----------+--------------+------------------+-------------------+
        //Build tables to insert data:
            CREATE TABLE `tb_person` (
        	   `id` int(11) NOT NULL AUTO_INCREMENT,
               `name` varchar(36) NOT NULL,                           
               `address` varchar(36) NOT NULL DEFAULT '',    
               `sex` varchar(12) NOT NULL DEFAULT 'Man' ,
        	   `other` varchar(256) NOT NULL ,
               PRIMARY KEY (`id`)
             ) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
             
        	 insert into tb_person  set name="name1", address="beijing", sex="man", other="nothing";
        	 insert into tb_person  set name="name2", address="beijing", sex="man", other="nothing";
        	 insert into tb_person  set name="name3", address="beijing", sex="man", other="nothing";
        	 insert into tb_person  set name="name4", address="beijing", sex="man", other="nothing";
    b.To configure slave
        Slave Configuration is similar master，Additional settings required relay_log Parameters, slave There is no need to open binary logs if slave For others slave Of master，Must set up bin_log
    c.Connect master
        mysql> CHANGE MASTER TO
           MASTER_HOST='10.108.111.14',
           MASTER_USER='test',
           MASTER_PASSWORD='123456',
           MASTER_LOG_FILE='mysql-bin.000003',
           MASTER_LOG_POS=120;
    d.show slave status;
        mysql> show slave status\G
        *************************** 1. row ***************************
                       Slave_IO_State:   ---------------------------- slave io State, indicating that it has not been started
                          Master_Host: 10.108.111.14  
                          Master_User: test  
                          Master_Port: 20126  
                        Connect_Retry: 60   ------------------------- master Sleep time before downtime or connection loss from the slave server thread retries to connect to the primary server
                      Master_Log_File: mysql-bin.000003  ------------ Current read master binlog file
                  Read_Master_Log_Pos: 120  ------------------------- slave read master binlog file location
                       Relay_Log_File: relay-bin.000001  ------------ playback binlog
                        Relay_Log_Pos: 4   -------------------------- playback relay log position
                Relay_Master_Log_File: mysql-bin.000003  ------------ playback log Corresponding maser binlog file
                     Slave_IO_Running: No
                    Slave_SQL_Running: No
                  Exec_Master_Log_Pos: 0  --------------------------- Be relative to master From library sql The location to which the thread executes
                Seconds_Behind_Master: NULL
        Slave_IO_State, Slave_IO_Running, and Slave_SQL_Running by NO Explain slave The replication process has not yet started.
    e.Start replication
        start slave
    f.Observe again slave state
        mysql> show slave status\G
        *************************** 1. row ***************************
                       Slave_IO_State: Waiting for master to send event -- wait for master New event
                          Master_Host: 10.108.111.14
                          Master_User: test
                          Master_Port: 20126
                        Connect_Retry: 60
                      Master_Log_File: mysql-bin.000003
                  Read_Master_Log_Pos: 3469  ---------------------------- 3469  Be equal to Exec_Master_Log_Pos，Completed playback
                       Relay_Log_File: relay-bin.000002                    ||
                        Relay_Log_Pos: 1423                                ||
                Relay_Master_Log_File: mysql-bin.000003                    ||
                     Slave_IO_Running: Yes                                 ||
                    Slave_SQL_Running: Yes                                 ||
                  Exec_Master_Log_Pos: 3469  -----------------------------3469  Be equal to slave read master binlog Playback completed
                Seconds_Behind_Master: 0
        //You can see that slave's I/O and SQL threads are already running, and Seconds_Behind_Master=0. The increase in Relay_Log_Pos means that some events are captured and executed.
        
        //Finally, see how to correctly judge the delay of SLAVE and determine whether slave catches up with master's binlog:
        1,First look Relay_Master_Log_File and Maser_Log_File Is there any difference?
        2,If Relay_Master_Log_File and Master_Log_File If it's the same, come on. Exec_Master_Log_Pos and Read_Master_Log_Pos Differences, Contrasts SQL Thread ratio IO How many threads are slow? binlog Event;
        3,If Relay_Master_Log_File and Master_Log_File Unlike that, it means that the delay may be large and needs to be from MASTER Get on binlog status，Judging the current binlog and MASTER The gap between them;
        4,If none of the above can detect problems, you can use it pt_heartbeat Tools to monitor delays in master and standby replication.
        
    g.query slave Data, Master-Subordinate Consistency
        mysql> select * from tb_person;
            +----+-------+---------+-----+---------+
            | id | name  | address | sex | other   |
            +----+-------+---------+-----+---------+
            |  5 | name4 | beijing | man | nothing |
            |  6 | name2 | beijing | man | nothing |
            |  7 | name1 | beijing | man | nothing |
            |  8 | name3 | beijing | man | nothing |
            +----+-------+---------+-----+---------+
//There are many other contents about mysql replication, such as different synchronization methods, replication formats, what are the differences, what are the characteristics, and under what circumstances should be used.... Here we will not introduce them one by one.

   Recovery is binlog One of the two main roles is to demonstrate how to use it with examples. binlog Restore data:
    
    a.First, look at the present binlog position
        mysql> show master status;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000008 |     1847 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
    b.Direction table tb_person Insert two records:
        insert into tb_person  set name="person_1", address="beijing", sex="man", other="test-1";
        insert into tb_person  set name="person_2", address="beijing", sex="man", other="test-2";
    c.Record current binlog Location:
        mysql> show master status;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000008 |     2585 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
    d.Query data 
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        |  7 | person_2 | beijing | man | test-2 |
        +----+----------+---------+-----+--------+
    e.Delete one: delete from tb_person where name ="person_2";
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        +----+----------+---------+-----+--------+
    f. binlog Restore (specify) pos Point recovery/Partial recovery)
        mysqlbinlog   --start-position=1847  --stop-position=2585  mysql-bin.000008  > test.sql
        mysql> source /var/lib/mysql/3306/test.sql
    d.Data recovery completed 
        mysql> select *  from tb_person where name ="person_2" or name="person_1";
        +----+----------+---------+-----+--------+
        | id | name     | address | sex | other  |
        +----+----------+---------+-----+--------+
        |  6 | person_1 | beijing | man | test-1 |
        |  7 | person_2 | beijing | man | test-2 |
        +----+----------+---------+-----+--------+
    e.summary
        //Recovery is to have mysql execute sql statements that are saved in the binlog log log for a specified paragraph interval one by one

yangge177@gmail.com