Reay log, master log info, relay log info of Mysql log file

Keywords: MySQL master-slave Database SQL

In the context of MySQL master-slave Replication ring, the standby node generates several files, as follows:
A relay log files
Reay log is similar to binary log in that it records changes in the database and consists of a series of files. Reaoy log records changes in the database sent by the primary node and is composed of I/O thread Write in. Then the SQL thread executes the events in the relay log file on the standby node.

-- View relay log

[root@test1 mysql_3306]# ll -h
total 1.7G
drwx------ 2 mysql mysql 4.0K Sep 13 08:50 AAA
-rw-rw---- 1 mysql mysql   56 Sep  7 04:02 auto.cnf
-rw-rw---- 1 mysql mysql  75K Sep 14 02:39 error.log
-rw-rw---- 1 mysql mysql 200M Sep 14 02:33 ibdata1
-rw-rw---- 1 mysql mysql 500M Sep 14 02:33 ib_logfile0
-rw-rw---- 1 mysql mysql 500M Sep  7 04:00 ib_logfile1
-rw-rw---- 1 mysql mysql 500M Sep  7 04:00 ib_logfile2
-rw-rw---- 1 mysql mysql 3.5K Sep 14 02:43 innodb_status.3585
-rw-rw---- 1 mysql mysql 1.1K Sep 13 09:17 mybinlog.000001
-rw-rw---- 1 mysql mysql  191 Sep 14 02:33 mybinlog.000002
-rw-rw---- 1 mysql mysql   36 Sep 14 02:33 mybinlog.index
-rw-r--r-- 1 mysql mysql 2.6K Sep 13 07:58 my.cnf
drwx------ 2 mysql mysql 4.0K Sep  7 04:00 mysql
-rwxr-xr-x 1 root  root  1.6K Sep  7 04:01 mysqld
-rw-rw---- 1 mysql mysql    5 Sep 14 02:33 mysql.pid
-rw-rw---- 1 mysql mysql  744 Sep 13 09:02 mysql-relay-bin.000004
-rw-rw---- 1 mysql mysql  714 Sep 13 09:17 mysql-relay-bin.000005
-rw-rw---- 1 mysql mysql  120 Sep 14 02:33 mysql-relay-bin.000006
-rw-rw---- 1 mysql mysql   75 Sep 14 02:33 mysql-relay-bin.index
drwx------ 2 mysql mysql 4.0K Sep  7 04:00 performance_schema
-rw-rw---- 1 mysql mysql  756 Sep 14 02:33 slow.log

Note: The first few files of mysql-relay-bin are relay. log, where mysql-relay-bin.index is the index file; by default, in the main data directory, the format is host_name-relay-bin.

Two master info log
The master info log file records the connection information of the standby node, such as user name, password, etc. The current version of this file can be written to the MySQL system table, but the following parameters need to be set:
- Set up my.cnf standby node

master-info-repository=TABLE

Note: Restart mysql service later

-- View the slave_master_info table

mysql> select * from slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 23
       Master_log_name: mybinlog.000001
        Master_log_pos: 944
                  Host: 192.168.18.50
             User_name: copy
         User_password: 123456
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca: 
            Ssl_capath: 
              Ssl_cert: 
            Ssl_cipher: 
               Ssl_key: 
Ssl_verify_server_cert: 0
             Heartbeat: 1800
                  Bind: 
    Ignored_server_ids: 0
                  Uuid: c18578f6-9338-11e7-9833-080027eb4c97
           Retry_count: 86400
               Ssl_crl: 
           Ssl_crlpath: 
 Enabled_auto_position: 1
1 row in set (0.00 sec)
Note: Here the password User_password is displayed in plaintext.

Trirelay log info log
The relay log info log file records the progress of relay log files used by standby nodes. The current version of this file can be written to the MySQL system table, but the following parameters need to be set
- Set up my.cnf standby node

relay_log_info_repository=TABLE

Note: Restart mysql service later

-- View the slave_relay_log_info table

mysql> select * from slave_relay_log_info \G;
*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: ./mysql-relay-bin.000006
    Relay_log_pos: 4
  Master_log_name: mybinlog.000001
   Master_log_pos: 944
        Sql_delay: 0
Number_of_workers: 4
               Id: 1
1 row in set (0.00 sec)

Attention:

1. If the master.info file is modified and deleted, will the replication be interrupted?

No, if you stop slave, then start slave will start normally, because MySQL already remembers the information, but when you restart mysql, at start slave, when restart fails, you will be prompted to change master to master.

2. When was master.info written?

Changemaster to.. Other times the file will not be modified or read (except restart).

3. The relay-log.info file will only be read when restarted, but as long as the file exists and data is written, it will change; if deleted, it will have no effect on replication (except restart).

In general, after master-slave switching, reset slave is needed to clear relay-log.info.


Reference blog:

http://francs3.blog.163.com/blog/static/405767272014111093328416/

http://blog.csdn.net/z1988316/article/details/8179238


Posted by sgalatas on Tue, 08 Jan 2019 11:33:09 -0800