Home Database Mysql Tutorial MYSQL主从库不同步故障一例解决方法_MySQL

MYSQL主从库不同步故障一例解决方法_MySQL

Jun 01, 2016 pm 01:20 PM
data sheet

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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Data table compression technology in MySQL Data table compression technology in MySQL Jun 16, 2023 am 08:16 AM

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

Data table DDL operation technology in MySQL Data table DDL operation technology in MySQL Jun 15, 2023 pm 07:55 PM

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 modify data table name mysql modify data table name Jun 20, 2023 pm 05:52 PM

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;"

Data table reloading techniques in MySQL Data table reloading techniques in MySQL Jun 15, 2023 pm 11:28 PM

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

How to calculate the average value of numeric columns in a data table using MySQL's AVG function How to calculate the average value of numeric columns in a data table using MySQL's AVG function Jul 24, 2023 pm 09:52 PM

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 How to use thinkorm to implement related queries between data tables Aug 01, 2023 am 08:25 AM

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 implement MySQL underlying optimization: horizontal and vertical splitting strategies for data tables How to implement MySQL underlying optimization: horizontal and vertical splitting strategies for data tables Nov 08, 2023 pm 06:57 PM

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 largest value in the data table How to use the MAX function in MySQL to find the largest value in the data table Jul 25, 2023 pm 09:49 PM

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

See all articles