Junhe(Ivy) Zou

Data Recovery MethodsRecovering Deleted RowsIf you accidentally delete data rows using a DELETE statement, you can recover the data using the Flashback tool. The Flashback tool works by modifying the contents of the binlog, allowing you to replay the data back into the original database. To use this method, ensure that binlog_format is set to row and binlog_row_image is set to FULL. When recovering individual transactions, follow these steps: For INSERT statements (Write_rows event in the binl ...

Multithreading and Concurrency ConceptsCritical Section and Race Conditions Critical section: A code block where multiple threads access shared resources, potentially causing concurrency issues. Race condition: Occurs when multiple threads access a critical section with unpredictable execution sequences. Avoiding Race Conditions Blocking solutions: Use synchronized and locks. Non-blocking solutions: Utilize atomic variables. Synchronized Synchronized uses an object lock, allowing only one thr ...

Processes and ThreadsProcessesA process is responsible for loading instructions, managing memory, and handling I/O operations. Think of a process as an instance of a program; while programs are static, processes are dynamic. When a program is executed, it initiates a process. Processes are independent entities, and multiple processes can run concurrently on a system. They have their own memory spaces, resources, and execution environments. Processes have shared resources, such as memory sp ...

Master-Slave Switching ProcessMaster-Slave Switching Based on Log PositionWhen configuring node B as a slave of node A’, you need to execute a “change master” command with six parameters: MASTER_HOST, MASTER_PORT, MASTER_USER, and MASTER_PASSWORD represent the IP address, port, username, and password of master A’, respectively. MASTER_LOG_FILE and MASTER_LOG_POS indicate the position in the master’s log file from which synchronization should continue. This position is known as the synchronizat ...

MySQL Parallel Replication StrategiesMySQL Version 5.5 The official MySQL 5.5 version doesn’t support parallel replication. Table-based Distribution Strategy: If two transactions update different tables, they can be parallelized. Row-based Distribution Strategy: Transactions can be parallelized on the replica if they don’t update the same row. MySQL Version 5.6 Official MySQL 5.6 supports parallel replication but only at the database level. Its efficiency depends on the workload distribution a ...

MySQL Master-Slave Replication Principles1. Setting the Slave Node as Read-Only Node B, even if not directly accessed, should be set to read-only (readonly) mode. Reasons: Some operational queries might be executed on the slave. Setting to readonly prevents mishaps. To prevent inconsistencies during the switch-over due to bugs or dual writes. Readonly status can be used to determine the role of the node. A long connection is maintained between Slave B and Master A. Replication Process: On ...

MySQL Logging Mechanism1. Binlog Writing Mechanism During transaction execution, logs are first written into the binlog cache. Upon transaction commit, the contents of the binlog cache are written to the binlog file. Each thread has its own binlog cache, but all share a single binlog file. Parameters: binlog_cache_size: Controls the size of the binlog cache for each thread. Exceeding this size will temporarily store the data on the disk. sync_binlog: Controls the write and fsync timing. sync_ ...

In a normal short connection mode, once connected to the database, only a few SQL statements are executed before the connection is severed. The connection is re-established when needed. During peak times in a short connection model, there could be a sudden spike in connections. Temporarily Enhancing MySQL PerformanceIn a normal short connection mode, once connected to the database, only a few SQL statements are executed before the connection is severed. The connection is re-established when ne ...

blog9.mdPhantom Read refers to a situation where a transaction, upon querying a range consecutively, sees rows in the second query that it did not see in the first. Phantom ReadPhantom Read refers to a situation where a transaction, upon querying a range consecutively, sees rows in the second query that it did not see in the first. When does Phantom Read occur? Under the Repeatable Read isolation level, normal queries are snapshot reads and will not see data inserted by other transactions. Ther ...

Principle of order by in MySQLSort Buffer Size (sort_buffer_size) sort_buffer_size determines the memory size that MySQL allocates for sorting (sort_buffer). If the data size to be sorted is less than sort_buffer_size, the sorting is completed in memory. If the data size for sorting is too large for memory, MySQL resorts to using disk temporary files to assist in sorting. External sorting typically uses the merge sort algorithm and may generate multiple temporary files. Max Length for Sort Dat ...