Summary of common interview problems in MySQL (Iv.)

091 Database Deadlock Concepts

In most cases, you can assume that if a resource is locked, it will always be released at a later time. A deadlock occurs when multiple processes access the same database, where each process has locks that are required by other processes, causing each process to fail. Simply put, process a waits for process B to release his resources, and B waits for a to release his resources so that they wait for each other to form a deadlock.

Although a deadlock may occur while the process is running, a deadlock must have a certain condition, and the following four necessary conditions must exist for the deadlock to occur.

1 Mutually exclusive conditions : refers to the process of allocating resources to the exclusive use, that is, for a period of time a resource is only occupied by a process. If there are other processes requesting resources at this time, the requester can only wait until the process that occupies the resource is released.

2 Request and retention conditions : The process has maintained at least one resource, but a new resource request has been made, and the resource has been occupied by other processes, at which point the request process is blocked, but the other resources that it has obtained remain in place.

( 3) No deprivation of condition : the resources that the process has obtained, which cannot be deprived until it has been used, can only be released by itself when the use is complete.

4 Loop waiting condition : Refers to the deadlock, there must be a process-the chain of resources, that is, the process set {P0,P1,P2,,PN} in the P0 is waiting for a P1 occupied resources; P1 is waiting for P2 to occupy the resources, ..., PN is waiting for resources that have been P0 occupied.

The following methods help you minimize deadlocks:

(1) Access the object in the same order.

(2) Avoid user interaction in a transaction.

(3) Keep the transaction short and in a batch.

(4) Use low isolation level.

(5) using a bound connection.

092 databases have several data protection methods (AAA)

The common methods and techniques for implementing database Security control are: user identification and identification, access control, view mechanism, audit, data encryption;

The difference between 093 Union and union all and the use

Union is inefficient because it is scanned for duplicate values. If the merge does not intentionally delete duplicate rows, then use union all two the number of SQL statement fields to be joined must be the same, and the field type should be "compatible" (consistent);

the difference between Union and Union All is that union automatically compresses repeated results in multiple result sets, and union ALL displays all the results, whether or not they are duplicates.

Union: The two result sets are combined to set operations, excluding duplicate rows, while the default rules are sorted;

UnionAll: A set of two result sets, including duplicate rows, is not sorted;

Intersect: intersection of two result sets, excluding duplicate rows, and ordering of default rules;

minus: Perform a bad operation on two result sets, excluding duplicate rows, and sorting the default rules.

You can specify an ORDER BY clause in the last result set to change the sort method.

What is the backup command for 094 MySQL?

Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

Backing up the MySQL database for a deleted table format

Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.

Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Directly compress MySQL database to backup

Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Backing up a MySQL database (some) tables

Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Backup multiple MySQL databases at the same time

Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Just back up the database structure

Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

Mysqldump–all-databases > Allbackupfile.sql

command to restore MySQL database

Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restoring a compressed MySQL database

Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

Transferring a database to a new server

Mysqldump-uusername-ppassword DatabaseName | Mysql–host=.. . -C DatabaseName