search
HomeDatabaseMysql Tutorial 考验mysql主从同步的事情发生了

下午3点,服务器当机,隔了几分钟之后恢复,远程连接Windows虚拟机,显示非正常关机,应该是机房跳闸了网站有些不正常,应该是非正常关机数据库损坏所致,需要修

下午3点,服务器当机,隔了几分钟之后恢复,远程连接Windows虚拟机,显示非正常关机,应该是机房跳闸了

 

网站有些不正常,应该是非正常关机数据库损坏所致,需要修复数据

登录从服务器,show slave status\G,显示同步状态还是正常的

于是关闭主服务器的mysql,香港服务器,运行

/mysql/bin/myisamchk -r /mysql/data/bbs/*.MYI

修复数据库

完成之后开启mysql

service mysql start

网站正常了,再登录从服务器查看show slave status\G

 

Slave_IO_Running: No

Slave_SQL_Running: Yes

同步居然出问题了

令一台从服务器出的问题刚好相反,此从库位于最末端,和主从库是一台服务器,也因为掉电数据损坏了

 

Slave_IO_Running: Yes

Slave_SQL_Running: No

 

 

为什么修复之前同步是正常的,这个有点搞不明白了

 

 

当时主服务器挂掉之后本来考虑立即将连接数据库改到从库上面,但因为配置的不是互为主从模式,而从和主目前同步是正常的,如果这样切换回来又需要重新配置主从,所以还是选择耽误些时间修复主数据库,谁知道修复之后同步反而终止了

 

有个问题需要搞明白:

今后肯定要修改为互为主从模式,已方便主库出状况的时候随时切换,然后修复主库之后切换回来,但不知道这样会不会依然出现上述状况

 

看来今晚又要熬夜做配置了

 

先整理下互为主从的配置步骤:

三台msyql服务器,两台互为主从,另一台随便作为哪个主的从都可以,只是用来做数据定时备份。互为主从的服务器要求能随时切换,肯定不能是同一台服务器里的虚拟机,因为母机当掉了两台mysql也都当掉,就失去了切换的意义

 

服务器A:58.55.142.168

两台mysql服务器a1和a2处于内网

a1:10.0.0.82

a2:10.0.0.81

服务器B1:161.18.41.72

 

1.主配置a1

vi /etc/my.cnf

 

确定以下参数

 

server-id       = 1

# 启动logbin

log-bin=mysql-bin

# 日志文件保留天数,以免日志持续增长撑满硬盘

expire-logs-days  = 7

# 需要同步数据库,多个数据库用逗号分开(发现启用了replicate-do-db=bbs,ucenter居然不能同步,还是换回replicate-ignore-db=mysql,test这个参数,好在重新启用之后同步正常了,但是和主库之间少了些数据)

# replicate-do-db=bbs,ucenter

# 排除同步数据库

replicate-ignore-db=mysql,test

# 配置从库上的更新操作是否写入二进制文件。

# 如果这台从库,还要做其他从库的主库,那么就需要打这个参数,以便从库的从库能够进行日志同步。

log-slave-updates

 

 

2.主配置B1

 

vi /etc/my.cnf

确定以下参数

 

server-id       = 3

log-bin=mysql-bin

expire-logs-days  = 7

replicate-ignore-db=mysql,test

log-slave-updates

 

 

3.从配置a2

 

vi /etc/my.cnf

server-id       = 2

replicate-ignore-db=mysql,test

# 仅作为从配置logbin好像不需要开(事实证明需要开)

log-bin=mysql-bin

expire-logs-days  = 7

# 这个没必要开

# log-slave-updates

 

 

同步设置参考:

不同的地方就是原来的主库也启动从复制

 

CHANGE MASTER TO MASTER_HOST='161.18.41.72', MASTER_PORT=3306,MASTER_USER='slave', MASTER_PASSWORD='123456';

start slave;

另外以前的从库必须删除data目录下的master.info,relay-log.info,localhost-relay-bin.xxxxx这些文件

然后启动mysql重新配置从复制

 

CHANGE MASTER TO MASTER_HOST='58.55.142.168', MASTER_PORT=3306,MASTER_USER='slave', MASTER_PASSWORD='123456';

start slave;

 

 

需要注意的地方,配置同步时主库必须锁表,防止数据写入,等两边库都显示同步状态正常再解锁,担心锁表失效的话可以暂时断开网站与mysql的连接是一样的

 

 

 2013.01.20 测试结果:

10.0.0.81 本地从库没过多久就同步出错,不知道是不是因为用网页连接数据库查看同步状况导致,香港虚拟主机,好像discuz论坛点击就产生写操作,我把数据库锁定为只读状态无法访问

161.18.41.72 同步正常,但把网站连接切换到这个库的时候,主库10.0.0.82显示同步状态正常,但没有同步他的数据,切换回来两边同步状态都报错了

 

Slave_IO_Running: Yes

Slave_SQL_Running: No

互为主从配置看来失败了,目前看来上述配置依然是单一主从配置

 

 

 

 

 用两台虚拟机测试互为主从,修改了一些配置

10.0.0.81

10.0.0.83

 

10.0.0.81 vi /etc/my.cnf

 

server-id = 1

log-bin=mysql-bin

replicate-ignore-db = test,mysql,information_schema,performance_schema

log-slave-updates

auto_increment_offset = 1

auto_increment_increment = 2

slave-skip-errors=all

 

10.0.0.83 vi /etc/my.cnf

 

server-id = 3

log-bin=mysql-bin

replicate-ignore-db = test,mysql,information_schema,performance_schema

log-slave-updates

 

auto_increment_offset = 2

auto_increment_increment = 2

slave-skip-errors=all

 

 

 

 

分别在两边的数据库插入数据,检测同步成功

 

log-slave-updates  不加上=on参数也能用

slave-skip-errors=all 跳过错误,以免错误引起同步终止

 

这两个参数主要解决Mysql复制技术中的主键冲突

auto_increment_offset = N     第N台机器

auto_increment_increment = 5  总共多少台机器

假设有5台机器,网站空间,因此,第一台机器的序列为:1,6,11,…;第二台机器的序列为:2,7,12,…;第三台机器的序列为:3,8,13,…等等

这样自增长主键不会重复

 

Statement
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
Explain the InnoDB Buffer Pool and its importance for performance.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 vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 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.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 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.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL 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 DatabasesMySQL: Structured Data and Relational DatabasesApr 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.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL 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 DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL 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 ManagementMySQL for Beginners: Getting Started with Database ManagementApr 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

See all articles

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

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.