1.1.1. If a crash happens thisconfiguration does not guarantee that the relay log info will be consistent
【环境描述】
msyql5.6.14
【报错信息】
mysql的slave启动时，error.log中出现Warning警告：
[Warning] Slave SQL: If a crash happensthis configuration does not guarantee that the relay log info will beconsistent, Error_code: 0
这条Warning信息对Mysql和MySQL复制功能没有任何影响。
【报错原因】
MySQL5.6版本开始支持把master.info和relay-log.info的内容写入到mysql库的表中，
master.info--> mysql.slave_master_info
relay-log.info--> mysql. slave_relay_log_info
同时在MySQL5.6版本中，增加了 Slave crash-safe replication功能，为了保证mysql的replication能够crash-safe，slave_master_info和slave_relay_log_info表必须使用事务型的存储引擎（InnoDB），不要尝试去手动修改这两张表的内容。同时，Slave还要开启relay_log_recovery功能。
【解决方法】
设置master_info_repository和relay_log_info_repository的值为TABLE，同时开启relay_log_recovery功能。
修改/etc/my.cnf配置文件，添加以下3项：
master-info-repository=table # 可以使用set global 动态修改
relay-log-info-repository=table # 可以使用setglobal 动态修改
relay-log-recovery=1 # 只读参数，必须修改my.cnf重启mysql
然后重启mysql实例。
【参考资料】
l master.info和relay-log.info日志
在复制的Slave节点上会创建两个日志，分别是master.infor和relay-log.info，位于datadir目录中。在MySQL5.6和后续的版本中，可以通过设置master-info-file和relay-log-info-file参数来指定写入到mysql的表中或者写入文件。
这两个文件中包含一些类似showslave status输出的信息，当slave启动的时候会读取master.info和relay-log.info文件来确定从master读取binary log和读取relay log的信息。
Slave的I/O线程负责更新维护master.info文件，
master.info
mysql.slave_master_info
SHOW SLAVE STATUS Column
Description
1
Number_of_lines
[None]
Number of lines in the file
2
Master_log_name
Master_Log_File
The name of the master binary log currently being read from the master
3
Master_log_pos
Read_Master_Log_Pos
The current position within the master binary log that have been read from the master
4
Host
Master_Host
The host name of the master
5
User
Master_User
The user name used to connect to the master
6
User_password
Password (not shown by SHOW SLAVE STATUS)
The password used to connect to the master
7
Port
Master_Port
The network port used to connect to the master
8
Connect_retry
Connect_Retry
The period (in seconds) that the slave will wait before trying to reconnect to the master
9
Enabled_ssl
Master_SSL_Allowed
Indicates whether the server supports SSL connections
10
Ssl_ca
Master_SSL_CA_File
The file used for the Certificate Authority (CA) certificate
11
Ssl_capath
Master_SSL_CA_Path
The path to the Certificate Authority (CA) certificates
12
Ssl_cert
Master_SSL_Cert
The name of the SSL certificate file
13
Ssl_cipher
Master_SSL_Cipher
The list of possible ciphers used in the handshake for the SSL connection
14
Ssl_key
Master_SSL_Key
The name of the SSL key file
15
Ssl_verify_server_cert
Master_SSL_Verify_Server_Cert
Whether to verify the server certificate
16
Heartbeat
[None]
Interval between replication heartbeats, in seconds
17
Bind
Master_Bind
Which of the slave's network interfaces should be used for connecting to the master
18
Ignored_server_ids
Replicate_Ignore_Server_Ids
The number of server IDs to be ignored, followed by the actual server IDs
19
Uuid
Master_UUID
The master's unique ID
20
Retry_count
Master_Retry_Count
Maximum number of reconnection attempts permitted Added in MySQL 5.6.1)
Slave的SQL线程负责维护relay-log.info文件，在MySQL5.6中relay-log.info包含文件中的记录数和复制延迟的秒数。
relaylog.info
slave_relay_log_info
SHOW SLAVE STATUS
Description
1
Number_of_lines
[None]
Number of lines in the file or rows in the table
2
Relay_log_name
Relay_Log_File
The name of the current relay log file
3
Relay_log_pos
Relay_Log_Pos
The current position within the relay log file; events up to this position have been executed on the slave database
4
Master_log_name
Relay_Master_Log_File
The name of the master binary log file from which the events in the relay log file were read
5
Master_log_pos
Exec_Master_Log_Pos
The equivalent position within the master's binary log file of events that have already been executed
6
Sql_delay
SQL_Delay
The number of seconds that the slave must lag the master
设置master_info_repository和relay_log_info_repository参数：
SQL> stop slave;
SQL> set global master_info_repository=table;
SQL> set global relay_log_info_repository=table;
SQL> show variables like '%repository';
+--------------------------------------+---------+
| Variable_name | Value |
+--------------------------------------+---------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+--------------------------------------+----------+
查看向Master读取日志的情况：
SQL> select * from slave_master_info;
查看Slave的Relay日志应用情况：
SQL> select * from slave_relay_log_info;
注意：
master.info和relay-log.info内的数据会有一定的延迟，取决于mysql把slave信息刷到硬盘的时间，如果要获得slave的实时信息，可以查询slave_master_info和slave_relay_log_info表，或者执行show slave status查看。
l master-info-repository
master-info-repository={ FILE | TABLE }，默认值是FILE，这个参数用于设置Slave节点上把master的信息写入到物理文件中还是写入到mysql. slave_master_info表中。
如果设置为FILE，也是mysql的默认设置，Slave会在datadir/master.info文件中记录master的信息，从MySQL5.6版本开始，建议使用TABLE模式。
l relay-log-info-reposity
relay-log-info-reposity=file|table，默认值是FILE，这个参数用于设置slave节点上把relay-log.info的信息写入到datadir/relay-log.info文件或者mysql. slave_relay_log_info表。
如果设置为FILE，也是mysql的默认设置，Slave会在datadir/master.info文件中记录master的信息，从MySQL5.6版本开始，建议使用TABLE模式。
l relay-log-recovery
relay-log-recover=0|1，默认值是0不开启，该参数用于设置是否开启relay log的自动恢复功能。当开启relay_log_recovery功能，slave数据库在启动的时候，会忽略未被执行的relay log，它会重新连接master获取relay log来进行恢复。
当slave发生宕机的时候，建议开启该功能，可以有效避免slave执行了relay log里面的讹误记录。
如果开启relay_log_recovery功能，必须同时把relay_log_info_reposity设置为TABLE模式。
Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AMInnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.
MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AMCompared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AMMySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.
MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AMMySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.
MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AMMySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.
MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AMMySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.
The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AMSQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.
MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AMThe basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
WebStorm Mac version
Useful JavaScript development tools
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
Zend Studio 13.0.1
Powerful PHP integrated development environment
