MYSQL主从库不同步故障一例解决方法_MySQL
bitsCN.com
于是:
1、在主库中创建一个临时库,将需要导入的表文件复制过来
2、执行
create database tmpdb;
create table tmptable;
cp mysql_date_file master_data_file //shell command 复制数据表文件到master data_dir下
insert into master.tmptable select * from tmpdb.tmptable;
执行完后,主库中数据导入正常
再看slave status
show slave status;
发现错误:not found tmpdb.tmptable (大致意思是这个,原来的错误信息没有记录下来)
匆忙中,看show master status 中Master_Log_Pos 标记为$Master_Log_Pos
然后在slave 上 CHANGE MASTER TO MASTER_LOG_POS=$Master_Log_Pos
然后再看show master status,发现有1162错误
到现在发现两边的数据不能同步了
。。。。。。
冥思苦想,不会重新做一遍主从库吧?
mysqlbinlog 我突然想到了它
于是mysqlbinlog --start-position=190000000 --stop-position=200000000 xxx.binlog|grep tmptable
找到了在slave上执行错误的SQL
mysqlbinlog --start-position=190000000 --stop-position=200000000 xxx.binlog|grep tmptable > /tmp/tmpbinlog
vi /tmp/tmpbinlog (find tmptable)
找到错误SQL的下一个# at (一串数字)标记为$NEXT_POS
在slave 上 CHANGE MASTER TO MASTER_LOG_POS=$NEXT_POS
show slave status 显示:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
哈哈,完成同步。
如果中间碰到1062错误 在slave配置文件中设定slave-skip-errors=1062,重启slavebitsCN.com

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



MySQL is a common relational database that is a core component of many websites and applications. As the amount of data becomes larger and larger, how to optimize the performance of MySQL becomes particularly important. One of the key areas is the compression of data tables. In this article we will introduce the data table compression technology in MySQL. Compressed tables and non-compressed tables There are two types of data tables in MySQL: compressed tables and non-compressed tables. Uncompressed tables are MySQL's default table type, which use fixed-length row format to store data. This means data

MySQL is a very popular open source relational database management system that supports complete DDL (data definition language) operations. DDL is a language used to define and manage various data objects in the database, including data tables, views, indexes, etc. It is very important for database administrators and developers to be proficient in DDL operation technology of data tables in MySQL. This article will introduce in detail the technology and methods of DDL operation of data tables in MySQL, and provide practical operation examples. 1. Create a data table. Creating a data table is in DDL.

MySQL modifies the data table: 1. First check all tables in the database, the code is: "SHOW TABLES;"; 2. Modify the table name, the code is: "ALTER TABLE old table name RENAME [TO] new table name;". 3. Check whether the table name is modified successfully. The code is: "SHOW TABLES;"

MySQL is an open source relational database management system. Its basic functions are excellent in database design, data storage and management. In MySQL, the data table is the most basic unit of data storage. In practical applications, data table reloading is a very common operating technique, which can help us improve the operating efficiency of the database and improve the stability of the system. This article will introduce this operation technique in detail from the concepts, principles and practical applications of data table overloading in MySQL. 1. What is data table overloading? Data table overloading is

Introduction to the method of using MySQL's AVG function to calculate the average value of numeric columns in a data table: MySQL is an open source relational database management system with a wealth of built-in functions to process and calculate data. Among them, the AVG function is a function used to calculate the average of a numeric column. This article will introduce how to use the AVG function to calculate the average value of numeric columns in a MySQL data table, and provide relevant code examples. 1. Create a sample data table First, we need to create a sample data table for demonstration. Suppose we have a file called

How to use thinkorm to implement related queries between data tables Introduction: During database development, we often encounter situations where we need to perform related queries between multiple data tables. Using thinkorm, an excellent database ORM framework, you can easily implement associated queries of data tables and improve development efficiency. This article will introduce how to use thinkorm to implement related queries between data tables, and provide code examples to help readers better understand. 1. Basic concepts Before performing related queries, you first need to understand th

How to realize the underlying optimization of MySQL: horizontal and vertical partitioning strategies of data tables, which require specific code examples. Introduction: In large-scale application scenarios, MySQL databases often face the pressure of storing and querying massive data. In order to solve this problem, MySQL provides data table partitioning strategies, including horizontal partitioning (HorizontalPartitioning) and vertical partitioning (VerticalPartitioning). This article will introduce how to implement MySQL underlying optimization, focusing on

How to use the MAX function in MySQL to find the maximum value in the data table Introduction: In MySQL, we often need to perform various queries and analysis on the data table, including finding the maximum value in the data table. The maximum value in a data table can be easily found using the MAX function and is very useful when further processing the data. This article will introduce how to use the MAX function to find the largest value in the data table, and give corresponding code examples. 1. Introduction to the MAX function The MAX function is an aggregate function in MySQL. Use
