MySQL Slave异常关机的处理
生产环境有一个MySQL的Master-Slave的复制。前一阵子机房网络升级,但是考虑到一般网络故障恢复之后,只需要start slave即可所以没
生产环境有一个MySQL的Master-Slave的复制。
前一阵子机房网络升级,但是考虑到一般网络故障恢复之后,只需要start slave即可
所以没有做任何的处理。
但是第二天上班的时候，在Slave数据库上输入命令，start slave 不能重新启动复制。
模拟环境如下
在Slave上启动复制，报错如下
复制过程出现主键重复了
最后，使用status查看，发现MySQL被重启过。
估计在割接过程中，莫名其妙的被人按了服务器的重启键吧。
但是MySQL复制针对这种情况，异常的脆弱，因为复制的信息都写在一个文件里，
考虑到性能的原因，这个写入并不是同步的。
也就是说在断电重启的瞬间，复制的情况并没有写入到文件中。
而再次启动之后，读取这个文件中记录的复制位置，肯定落后于实际执行的位置。
所以会发生主键重复的问题，因为这个记录实际已经执行过了。
解决这个问题有三种方式
1.设置sql_slave_skip_counter跳过Master发来的事件,直到正常的位置
启动之后，发现报错信息已经变了
他重复的主键已经从7940到了7946了，也就说通过一点一点的推进，是可以推进到正常的复制位置的。
但是工作量比较大，需要很有耐心和时间做这个事情。
2.设置slave_skip_errors
在my.cnf配置文件中设置slave_skip_errors=1062，跳过所有主键重复的错误。
但是这个参数设置之后，，需要重启服务。对于生产系统来说，没有实际意义。
3.设置slave_exec_mode
MySQL默认的slave执行方式是严格的。
类似主键重复的问题，会直接停止复制，等待人工介入。
slave_exec_mode是一个动态参数，如果设置为IDEMPOTENT可以自动忽略主键重复和没有找到记录的错误。
但是貌似只是在MySQL集群环境有效
异常断电和关机除了可能导致主键重复的错误之外，还可能导致中继日志损坏
针对这个问题，首先，找到master binlog执行的位置
位置
然后重新连接Master服务器,(需要先停止slave)
最后查看复制情况，已经正常。
这次的经验是，如果预计可能发生状况，比如这次网络割接，
可以先停止slave的服务器。因为我们的slave只是作为备份的一种方式，而不是用于读写分离。
或者干脆设置slave_skip_errors参数。
--------------------------------------分割线 --------------------------------------
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
--------------------------------------分割线 --------------------------------------
本文永久更新链接地址：
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
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1673
14
1429
52
1333
25
1278
29
1257
24
MySQL's Role: Databases in Web Applications
Apr 17, 2025 am 12:23 AM
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Explain the role of InnoDB redo logs and undo logs.
Apr 15, 2025 am 12:16 AM
InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.
MySQL vs. Other Programming Languages: A Comparison
Apr 19, 2025 am 12:22 AM
Compared 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.
MySQL vs. Other Databases: Comparing the Options
Apr 15, 2025 am 12:08 AM
MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.
MySQL for Beginners: Getting Started with Database Management
Apr 18, 2025 am 12:10 AM
The 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
Explain the InnoDB Buffer Pool and its importance for performance.
Apr 19, 2025 am 12:24 AM
InnoDBBufferPool 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: Structured Data and Relational Databases
Apr 18, 2025 am 12:22 AM
MySQL 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.
Learning MySQL: A Step-by-Step Guide for New Users
Apr 19, 2025 am 12:19 AM
MySQL 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.
