Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




6.4.4. Replication Relay and Status Files

By default, relay logs filenames have the form host_name-relay-bin.nnnnnn, where host_name is the name of the slave server host and nnnnnn is a sequence number. Successive relay log files are created using successive sequence numbers, beginning with 000001. The slave uses an index file to track the relay log files currently in use. The default relay log index filename is host_name-relay-bin.index. By default, the slave server creates relay log files in its data directory. The default filenames can be overridden with the --relay-log and --relay-log-index server options. See Section 6.9, “Replication Startup Options”.

Relay logs have the same format as binary logs and can be read using mysqlbinlog. The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them.

A slave server creates a new relay log file under the following conditions:

  • Each time the I/O thread starts.

  • When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs.

  • When the size of the current relay log file becomes too large. The meaning of “too large” is determined as follows:

    • If the value of max_relay_log_size is greater than 0, that is the maximum relay log file size.

    • If the value of max_relay_log_size is 0, max_binlog_size determines the maximum relay log file size.

A slave replication server creates two additional small files in the data directory. These status files are named and by default. Their names can be changed by using the --master-info-file and --relay-log-info-file options. See Section 6.9, “Replication Startup Options”.

The two status files contain information like that shown in the output of the SHOW SLAVE STATUS statement, which is discussed in Section 13.6.2, “SQL Statements for Controlling Slave Servers”. Because the status files are stored on disk, they survive a slave server's shutdown. The next time the slave starts up, it reads the two files to determine how far it has proceeded in reading binary logs from the master and in processing its own relay logs.

The I/O thread updates the file. The following table shows the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS.

Line Description
1 Number of lines in the file
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6 Password (not shown by SHOW SLAVE STATUS)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key

The SQL thread updates the file. The following table shows the correspondence between the lines in the file and the columns displayed by SHOW SLAVE STATUS.

Line Description
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos

When you back up the slave's data, you should back up these two status files as well, along with the relay log files. They are needed to resume replication after you restore the slave's data. If you lose the relay logs but still have the file, you can check it to determine how far the SQL thread has executed in the master binary logs. Then you can use CHANGE MASTER TO with the MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to re-read the binary logs from that point. Of course, this requires that the binary logs still exist on the master server.

If your slave is subject to replicating LOAD DATA INFILE statements, you should also back up any SQL_LOAD-* files that exist in the directory that the slave uses for this purpose. The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations. The directory location is specified using the --slave-load-tmpdir option. If this option is not specified, the directory location is the value of the tmpdir system variable.

  Published under the terms of the GNU General Public License Design by Interspire