MySQL Replication常用SQL、应用、文件、流程、模式

Home

Database

Mysql Tutorial

MySQL Replication常用SQL、应用、文件、流程、模式_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:30 PM

binary

bitsCN.com

MySQL Replication常用SQL、应用、文件、流程、模式

无聊时写的，算科普吧，毕竟内置的Replication是MySQL的骄傲

㈠ SQL语句篇

管理主库部分

show master logs

列出主库二进制日志

show master status

列出当前主库二进制日志状态

show slave hosts

列出连接到主库的备库信息

show binlog events in 'log_name'

列出二进制日志中的事件

reset master

置空二进制日志索引文件，并创建一个新的二进制日志

purge master logs to 'log_name'

purge master logs before 'date'

删除主库的二进制日志

建议删除流程：

① 目标日志确认如下：

在主库：show master logs

在备库：show slave status[每个备库都执行，抓取延迟最大的备库]

② 备份

③ purge

管理备库部分

change master to master_**

告诉备库如何连接到主库并重放其二进制日志

参数较多较杂，请自行参阅手册

reset slave

删除master.info、relay-log.info以及所有中继日志，并新建一个中继日志

show slave status

查看当前备库状态

输出较多，下面捡几个重要的谈谈

Slave_IO_State、Slave_IO_Running、Slave_SQL_Running：表示IO线程和SQL线程健康状况

Master_Log_File：IO线程当前正在读取的主库的二进制日志的名称

Read_Master_Log_Pos：当前主库的二进制日志中，IO线程已经读取的位置

Relay_Log_File：SQL线程当前正在读取和执行的中继日志的名称

Relay_Log_Pos：当前中继日志中，SQL线程已经读取和执行的位置

Exec_Master_Log_Pos：同步到备库的二进制日志的位置

能借助该输出来计算复制延迟：

Read_Master_Log_Pos-Exec_Master_Log_Pos：表示SQL线程延迟，进而表示了主备是否同步

顺道提一点，二进制日志坐标：Position，减去Read_Master_Log_Pos：表示IO线程延迟

start slave

启动备库SQL线程/IO线程

stop slave

停止备库SQL线程/IO线程

㈡应用篇

数据分布

→给地理上互相隔离的IDC分发数据

热备份

→复制是备份的技术补充，但不能代替备份

读扩展

→负载均衡

报表分析

→在不影响主库业务的情况下，月底的审计和报表分析可放到备库上做

升级测试

→用最新版本的mysql做备库

故障转移

→提升备库为主库，最小化宕机时间

㈢文件篇

master.info

记录备库连接主库所需要的信息，如：主机、用户名、密码、当前二进制日志坐标等

同时，他也能告诉主库："我需要某个日志的某个位置之后的内容，请发给我"

relay-log.info

记录当前备库正在复制的二进制日志和中继日志的坐标

binlog index

记录主库磁盘上二进制日志文件

relay log

存储IO线程从主库复制的二进制日志事件

relay log index

作用同binlog index

㈣流程篇

⑴ 主库记录二进制日志：按事务提交的顺序记录事件

⑵ 备库将主库的二进制日志复制到本地中继日志

启动IO线程

发起TCP/IP连接

在主库启动binlog dump线程

读取主库二进制日志

IO线程记录到relay log和master.info

⑶ 备库读取并重放二进制日志事件

㈤模式篇

复制模式可分：STATEMENT和ROW，通过binlog_format控制

没有哪种模式能胜任任何场景，谓之：存在即是合理

MySQL能在这两种模式动态却换(binlog_format='MIXED')

缺省以STATEMENT运行，当无法正确复制时则以ROW运行

下面列出他们各自的优缺点

ROW模式

优点

几乎没有基于行的复制无法处理的场景

更少的锁竞争，因为对强串行化的需求降低

更低的CPU花费，因为没有必要在构造SQL上下文信息

更好的保证了复制到备库的数据的品质

更快的定位和解决数据不一致，如当找不到修改的行时，ROW模式会使整个复制过程停止而STATEMENT不会

缺点

无法确定执行了什么SQL

黑盒子，很难定位出故障的地方

占用更多的磁盘空间

更多的网络带宽开销

STATEMENT模式

优点

基于行的复制整个过程基本上就是执行SQL

这很容易定位问题

缺点

无法正确复制，特别是当涉及到存储过程、触发器、函数等

这会失去复制的意义

bitsCN.com

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 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.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL vs. Other Databases: Comparing the OptionsApr 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.

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles