MySql Q&A Collection

MySQL

3.1 Differences between relational and non-relational databases

relational database

• Use a table structure with a consistent format;
• SQL language is generic and can be used for complex queries;
• Read and write performance is relatively poor, especially for efficient reads and writes of large amounts of data;
• Fixed table structure, slightly less flexible;
• Highly concurrent read and write requirements, traditional relational database, hard disk I/O is a big bottleneck.

Non-relational databases

Flexible format: the format of the stored data can be key, value form, document form, picture form, etc., document form, picture form, etc., the use of flexible, wide range of application scenarios, while relational databases only support the basic types.
2, speed: nosql can use a hard disk or random memory as a carrier, while relational databases can only use hard disk;
3. High scalability;
4, low cost: nosql database deployment is simple, basically open source software.

Drawbacks:
1, does not provide sql support, learning and use of higher costs;
2. No transactions;
3. The data structure is relatively complex, and the complex query aspect is slightly lacking.

3.2 How to log in to the database, how to log in remotely

1. Edit MySQLconfig file
2. Configuring the Firewall to Allow Remote Connections
3. permissibleroot rlogin
4. Connecting to a Remote MySQL Server

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
mysql> UPDATE  SET host='%' WHERE user='root';

3.3 MySQL's service structure, when a client initiates a request, the process of handling the

3.4 How to Set or Reset MySQL Passwords

use mysql;
UPDATE user SET authentication_string=PASSWORD("root") WHERE User="root";
flush privileges;

3.5 Writing SQL statements such as DDL, DML, DQL, DCL, etc.

DDL Data Definition Statements

#Library part
create database db02 charset utf8;
# Add attributes when creating the database
show create database db01;
# Show create database db01; # Add attributes when creating the database
drop database db02; # Delete database db02.
# Delete database db02
alter database db01 charset utf8; # Delete database db02.
# Modify the definition library
# Table section
create table student(

sname varchar(20) not null comment 'student's name', sage tinyint unsigned not null comment 'age', # create table student()
sage tinyint unsigned not null comment 'age', sgender enum('m')
sgender enum('m','f') not null default 'm' comment 'Student's gender',
cometime datetime not null comment 'enrollment time'
)charset utf8 engine innodb;
#Example of a table creation statement
desc student;
# View the column definitions in the table
drop table student;
# Delete the table
alter table student rename stu; # Delete table.
# Modify the table name
alter table stu add age int; # delete table student.
# Add column and column data type definitions.
alter table stu add test varchar(20),add qq int; # Add multiple columns.
# Add multiple columns
alter table stu add classid varchar(20) first; # Add multiple columns.
# Add columns at the specified location (table header)
alter table stu add phone2 int after sid; # Add column at specified location (table header).
# Add column at specified position (specified column)
alter table stu drop qq;
# Delete the specified column and definition
alter table stu modify sid varchar(20); # Delete the specified column and its definition.
# Modify columns and definitions (column attributes)
alter table stu change phone telphone int(); # Modify columns and definitions (column attributes).
# Modify columns and definitions (column names and attributes)

DCL Data Control Language (user rights)

grant all on *.* to root@'192.168.175.%' identified by '123456';
# vestroot@'192.168.175.%'User All Rights(non-super administrator)
revoke select on *.* from root@'192.168.175.%';
# retakeselectscope of one's jurisdiction
show grants for root@'192.168.175.%';
# 查看scope of one's jurisdiction

DML data manipulation language

insert into stu valus('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

insert into stu(classid,,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),
('linux02',2,NOW(),'zhangsi',21,'f',NOW(),111,1234567);
# Inserting Multiple Data

update student set sgender='f' where sid=1;
update set password=PASSWORD('123456') where user='root' and host='localhost';

delete from student where sid=3;

truncate table student;
# DDLEmpty the contents of the table

alter table student add status enum('1','0') default 1;
# Add an additional status column
update student set status='0' where sid=1;
# utilizationupdate
select * from student where status=1;
# Application Query Existing Data

DQL Data Query

Understand advanced uses of select (not much more than a brief description)

3.6 What is an index and what does it do?

Specialized database structure

Speeds up the retrieval of data from database tables and is used to ensure data uniqueness.

3.7 Types of indexes

• primary key index
• ordinary index
• unique index
• full text index
• prefix index
• combinatorial index

3.8 Difference between b-tree and b+-tree

Each node of a B-tree stores keywords and data, whereas a B+-tree has only leaf nodes that store data and internal nodes that store only keywords and pointers to child nodes。

B+ trees are widely used for their superior range query performance and index space utilization as aDefault Index Structure。

3.9 What causes an index to fail?

1. There is no query condition, or the query condition is not indexed.
2. No bootstrap columns are used on the query condition.
3. The number of queries is the majority of the large table and should be 30% or more.
4. The index itself fails.

3.10 MySQL Explain

August 2024 Essay Archive - guixiang - Blogland ()

3.11 What is a transaction and what does it do?

Transactions YesOne group operationThe system can submit or withdraw requests for all operations as a whole, i.e., they will either succeed or fail at the same time.

3.12 How are the ACIDs of transactions represented?

Atomic

• All statements as a unit are either all successfully executed or all canceled.

Consistent

• If the database is in a consistent state at the beginning of a transaction, it will retain the consistent state during the execution of that transaction.

• The state of the data seen within the transaction is the same.

Isolated

• Transactions do not interact with each other.

Durable

• Upon successful completion of a transaction, all changes made are accurately recorded in the database. Changes made are not lost.
• After a software or hardware crash, the InnoDB data table driver uses log files to reconstruct the changes.

3.13 Transaction isolation levels

read uncommited

Transaction A is executed but not committed; transaction B queries for updated data from transaction A; transaction A rolls back;

read commited

Transaction A performs an update; transaction B queries; transaction A again performs an update; transaction B queries again with inconsistent data; --- unrepeatable reads

Repeated read

No matter how many times transaction A executes, as long as it does not commit, transaction B queries for the same value; transaction B queries only for a snapshot of the data at the moment when transaction B started;

mysql default mode

3.14 What are dirty reads, phantom reads, and unrepeatable reads?

dirty reading

means that a transaction in one thread reads uncommitted data from another thread.

non-repeatable

means that a transaction in one thread reads data from an update committed in another thread.

fantasy reading

means that a transaction in one thread reads data from an INSERT committed in another thread.Inexplicable increases or decreases in data(appears in repeatable reading)

3.15 What are the locks for transactions and how do they work?

Shared Lock (S): allows a transaction to read a row and prevents other transactions from obtaining an exclusive lock on the same data set.

Exclusive locks (X): allows transactions that obtain exclusive locks to update data, preventing other transactions from obtaining shared read locks and exclusive write locks on the same data set.

Lock a row by query condition, unlock it after commit

// Read locks (shared locks)
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
//Write locks (exclusive locks)
SELECT * FROM table_name WHERE ... FOR UPDATE;

When we make additions, deletions and changes Innodb is default to the corresponding rows are automatically added to the write lock, select query will not be added to the lock.
In row locks, write locks and read locks are such that other sessions can only view and cannot modify the data on that row, they can modify it themselves, but when we add a read lock to a row, other sessions can add a read lock to that row, not a write lock. When we add a write lock to a row, other sessions can't add a lock to that row.

3.16 Difference between pessimistic and optimistic locks

Pessimistic locking: It is assumed that a transaction that also wants to modify the database data exists during the time it takes to modify this data;

Optimistic locking: It is assumed that no transaction will modify the data in this database for a short period of time;

3.17 Difference and process of redo and undo when transaction occurs

make a distinction

Redo	Undo
Used to record the physical modification of the data page, to ensure the consistency of the data after the transaction is committed, and to be able to restore the data to the committed state in the event of a system crash.	Used to record the state of data before modification in a transaction, so that when the transaction fails or needs to be rolled back, the changes made by the transaction can be undone and the data can be restored to the state before modification.

Redo process

1. Before data modification: Before a transaction makes changes to the database, the InnoDB storage engine sends theModify operation logging to redo log buffer(log buffer in memory) in the
2. data modification: Subsequently, actual modification operations are performed on the data in the database.
3. Log Write: When the transaction commits, InnoDB willLogs in the redo log buffer are written to a redo log file on diskto ensure that data can be recovered via redo log even in the event of a system crash.
4. Restore data: In the event of a system crash or the need to recover data, the database can be restored to the state it was in at the time of the transaction commit by using the records in the redo log.

Undo process

1. Before data modification: Before a transaction makes changes to the database, the InnoDB storage engine generates an undo log of the state of the data before it was modified.
2. data modification: Make changes to the data in the database.
3. Transaction commit or rollback:
   • If the transaction commits successfully, the undo log may be purged (depending on the specific undo log retention policy).
   • If the transaction fails or the ROLLBACK command is executed, InnoDB restores the data in the database to the state it was in before the modification, based on the entries in the undo log.
4. read consistency: In concurrent transactions.undo log is also used to achieve read consistency, i.e., to ensure that the read operation is able to read the state of the data prior to the start of the transaction.

3.18 What happens when the database server is restarted while the transaction is uncommitted

When the system submits a database DML command, but does not execute the commit, the system goes down. At this point, theThe data in the database does not change, the database connection is cleared, the database transaction is cleared, and there is no locking of the data

3.19 What is MySQL's Storage Engine, What are the Common Storage Engines, Difference between innodb and myisam Storage Engine

​ The components of the MySQL database management system that are responsible for storing and retrieving data, which can affect how data is stored, transaction support, concurrency performance, etc.1。Common storage engines in MySQL are MyISAM, InnoDB, Memory, Archive, etc.23。Among other things, InnoDB is MySQL's default storage engine, which provides powerful transaction processing, row-level locking, and foreign key constraints4。MyISAM, on the other hand, does not support the functionality of transactions, row-level locks, and foreign key constraints

3.20 How to switch from myisam to innodb when the customer's database is myisam (rough steps)

if usingwp_commentsTables. Simply run the ALTER command to convert them to the InnoDB storage engine. Note: It is always recommended to take a backup of your MySQL database before running any operations on it.

ALTER TABLE wp_comments ENGINE=InnoDB;

3.21 What Are MySQL's Logs?

1. Redo log
2. Rollback log (undo log)
3. Binary log (binlog)
4. Errorlog
5. Slow query log (slow query log)
6. General log

More importantly, the binary logbinlog (archive log) and transaction log redo log (redo log) and undo log (rollback log)。

3.22 What is a binary log binlog, and what does it do?

is anLog files that record changes that occur in the database. It records data as well as changes to the database structure

It can

• There are binary logs of all the database builds from the beginning, and you can restore the data to any point in time
• Backup recovery of data
• Replication of data

3.23 Advantages and disadvantages of row and statement modes in binary logging

• statement pattern
  • Advantages: simple and clear, easy to be understood, that is, sql statements, recording does not require much disk space
  • Cons: lack of rigor in record keeping
• row mode
  • Advantage: more rigorous record keeping
  • Cons: There is a possibility that you will needMore disk space, less readable

3.24 Slow Query Log Analysis (mysqldumpslow)

Record the relevant SQL statements affecting database performance in mysql server to log files, and analyze and improve these special SQL statements to achieve the purpose of improving database performance.

$PATH/mysqldumpslow -s c -t 10 /application/mysql/data/
# Output the 10 SQL statements with the highest number of records

[!TIP]

/application/mysql/data/ is the slow query log path.

3.25 MySQL Backup and Restore Related Knowledge

#Backup the database
mysqldump -u root -p wordpress > wordpress_database.sql
#database restore
mysql > source /backup/mysqldump/wordpress_database.sql

3.26 How to Recover Database Service if MySQL Service is Corrupted and No Backup is Available

Recovery of database services in case of MySQL service corruption and no backup is a challenging task. Although there is no complete guarantee of recovering all lost data, here are some possible recovery steps and strategies:

1. Assessment of the extent of damage

• Checking log files: First, check MySQL's error logs and other related log files for the exact cause of the service corruption and possible error messages.
• Checking data files: Check MySQL's data files (usually located in the/var/lib/mysqldirectory), look for obvious signs of corruption, such as missing, corrupted, or abnormally sized files.

2. Attempts at automatic recovery

• InnoDB Crash Recovery: If MySQL is using the InnoDB storage engine and the service was stopped because of a crash, InnoDB will automatically attempt to restore the database to a recent consistent state when the MySQL service is restarted. This usually involves rolling back uncommitted transactions and applying changes in the redo log.

3. Recovery using binary logs

• Find Binary Logs: MySQL's Binary Log records all update operations to the database, including inserts, updates, and deletes. Check/var/lib/mysqlThe presence of a binary log file in the directory (usually starting with amysql-bin.(beginning).
• Analyzing logs: UsemysqlbinlogThe tool analyzes the binary log file to find the last complete transaction executed before the corruption occurred.
• Restore data: Based on the results of the analysis, you can try to export the SQL statements from the binary logs to a file and use themysqlcommand applies these statements to the new or repaired database.

4. Use of third-party data recovery tools

5. Seek professional help

6. Preventive measures

• Regular backups
• Enhanced database monitoring and logging

caveat

• Before performing any recovery operations, make sure that theMySQL service has been stoppedto avoid further damage to the data during the recovery process.
• Recovered dataValidation and testing may be required to ensure data integrity and accuracy.

3.27 Principles of MySQL Master-Slave Architecture

The first part of the process ismaster records binary logs. Before each transaction completes updating the data, the master records the changes in the binary log. Even though the statements in a transaction are cross-executed, the master notifies the storage engine to commit the transaction after the events are written to the binary log.

The next step is for the slave to copy the master's binary log to its own relay log. First, the slave starts a worker thread, the I/O thread, which opens a normal connection on the master and then starts the binlog dump process, which reads events from the master's binary log and, if it has already caught up with the master, sleeps and waits for the master to generate new events. If it has already kept up with master, it sleeps and waits for master to generate new events.The I/O thread writes these events to the relay log.

The SQL slave thread handles the final step of the process.The SQL thread reads the events from the relay log and updates the data in the slave to be consistent with the data in the master. As long as this thread is consistent with the I/O thread, the relay log usually sits in the OS's cache, so the overhead of the relay log is minimal.

3.28 Principles of MySQL Master-Master Architecture

It consists of two master databases, each of which can handle read and write requests and standby for each other.

Fundamentals.

• bi-directional replication: Bi-directional data replication between two master servers (Master1 and Master2). Each master server logs its changes to a binary log, and theEach master server is configured as a slave to another master server, thus reading changes from each other's binary logs and applying them to your own database.
• data consistency: With continuous bi-directional replication, the data on the two master servers remains highly consistent. This consistency is asynchronous, but semi-synchronous replication can be configured to reduce data latency and improve data security.
• failover: In a master-master architecture, if one of the master servers fails, the other master server can immediately take over the service without the need for complex failover operations. This helps to achieve high availability and business continuity.

3.29 Problems with Consistency in MySQL's Dual-Master Architecture

I. Time difference in data synchronization

• asynchronous replication
• synchronization delay

II. Data conflicts for concurrent write operations

• Primary Key Conflict: In a dual-master architecture, if both master servers attempt to insert records with the same primary key at the same time, these insert operations may succeed on their respective servers due to replication time differences, but conflict when synchronizing to the other server.
• Data coverage: If two master servers update different fields of the same record at the same time, then due to the order and timing of replication, the latter update may overwrite the result of the previous update, resulting in inconsistent data.

3.30 Problems with Master-Slave Architecture, Solution Ideas

1. data delay
2. Data consistency issues
3. Untimely failover and recovery, affecting business continuity and availability
4. Replication conflicts
5. Complex to configure and maintain

3.31 Master-slave replication, what to do if the slave library writes data by mistake (master-slave is not synchronized, which will cause the SQL thread to stop)

This often occurs when the slaveNo configuration of super_read_only=1, and then the administrator mistakenly adds or deletes data in the slave, resulting in inconsistent data between the slave and master. To resolve this type of problem, it is usually necessary toPerform a reverse operation on the slave libraryFor example, deleting these erroneous additions and manually restoring the master and slave data to their previous consistent state.

Step 0: Stop the master-slave replication

mysql > stop slave

The first step is to check the exact location of the error

Execute the show slave status command to view the status of the slave library, get the current binlog information has been applied to the main focus on the information prompted in the Last_Error

Step 2: Confirm the location

Get the specific SQL that went wrong based on the error message in Last_Error.

show binlog events in 'mysql-bin.032102' from 730019106 limit 10; #Find the corresponding line, the Info message in the line is the operation done by 1973 location

Step 3: Roll back the data

After locating the error statement, simply perform a reverse operation on the slave to process the data, then restart the slave process

Step 4: Open the Slave Library

mysql > start slave

Or step 1~3 directly in the slave library to modify the original

3.32 Master-Slave Replication, Slave IO Thread Troubleshooting Ideas

I. Checking basic connection information

1. Confirm the master library address and port
2. Check copy user rights, password
3. Check network connectivity

Second, view the status of the slave library

1. fulfillmentSHOW SLAVE STATUS\Gcommand

   • focusSlave_IO_Runningfield, if it isNo, then the IO thread is not running.

   • probeLast_IO_Errorcap (a poem)Last_IO_Errnofield

     • These fields provide the final error message and error code if the IO thread stops running.

   Based on the error messages and codes, the cause of the malfunction can be initially determined.

3.33 Delayed Slave, Semi-Synchronous Replication, and Filtered Replication Usage Scenarios in Master-Slave Replication

delayed slave (computing)is to intentionally configure the slave to delay replicating the master's data for a certain amount of time.

• pertaindata recovery；

semisynchronous replicationRequires the master to have at least one slave that has received and logged the binlog log of the transaction before the transaction commits.

• It is suitable for scenarios such as high data consistency requirements, high availability and disaster recovery;

Filter ReplicationAllows users the flexibility to specify which databases or tables need to be replicated and which do not.

• pertainBusiness isolation, performance optimizationand data migration scenarios.

  (Migrating specific databases or tables incrementally reduces the risk and complexity of the migration process.)

3.34 Principles of MySQL's MHA High Availability Architecture

1. Master Node Monitoring
   • MHA Manager constantly monitors the status of the master node, including whether the connection is normal, whether the master node is running normally, and so on.
2. Automatic fault detection
   • When MHA Manager detects a failure in the master node (e.g., the master node is down), it automatically detects and confirms whether the master node is really unavailable.
3. failover
   • Once the master node is confirmed to be unavailable, MHA Manager automatically performs a failover operation:
     • Save binary log events (binlog events) from the downed master node to ensure that data is not lost.
     • Identifies the slave node containing the latest update.
     • Apply differential relay logs (relay logs) to other slave nodes to ensure data consistency across all slave nodes.
     • Apply the latest binary log events to other slave nodes.
     • Promote a slave node to a new master node.
     • Make the other slave nodes connect to the new master node and continue the replication operation.
4. Data consistency assurance
   • During failover, MHA ensures data consistency and avoids data loss or conflicts.
5. Fault recovery
   • When the original master node returns to normal, MHA Manager rejoins it to the master-slave replication architecture and synchronizes its data as a slave node with the new master node.

3.35 How to Deploy the MHA High Availability Architecture

• Build three mysql databases

• Modify the configuration file /etc/

• server-id

• Enable binlog logging

• Creating Master-Slave Replication Users

• Configuring Master-Slave Replication Host Information

• Restart mysql

• Enable GTID

• Formal deployment of MHA

• Toolkit Download

• Installation of dependency packages

• Command softlink (all nodes)

• Deploy the management node (mha-manager:mysql-db03)

• Edit configuration file (manage node)

• Configure ssh trust (all nodes)

• Starting tests (manage node)

• Initiate MHA