Shulou(Shulou.com)06/01 Report--

The following mainly brings you MySQL password management, storage engine and transaction-related knowledge, I hope these contents can bring you practical use, this is also my main purpose of editing MySQL password management, storage engine and transaction-related knowledge of this article. All right, don't talk too much nonsense, let's just read the following.

1.MYSQL password Management:

1.1 change the user password:

(1) SET PASSWORD [FOR 'user'@'host'] = PASSWORD (' cleartext password'); (2) UPDATE mysql.user SET Password=PASSWORD ('cleartext password') WHERE User='USERNAME' AND Host='HOST'; (3) mysqladmin-uUSERNAME-hHOST-p password' NEW_PASS' effective: FLUSH PRIVILEGES

1.2 solution to forgetting the administrator password:

(1) modify the configuration file and add the following: skip-grant-tables # ignores user name password authentication skip-networking # forbids remote login to improve security. Note it can only be operated locally in the database after being added. If it cannot be operated locally, do not add it. Generally, CVMs can only be accessed by ip in the office area. Restart the mysqld service (2) log in directly to mysql and change the administrator password through the UPDATE command; (3) remove the two options added in the first step to start the mysqld process in a normal manner

2.MySQL common storage engines:

2.1 INNODB Class

Good at handling a large number of short-term transactions, data stored in tablespaces (table space)

Features:

1. The data and indexes of all innodb tables are placed in the same table space: the data files under the directory defined by datadir (store data and indexes): tbl_name.ibd,tbl_name.frm (table format definition file) 2. Based on MVCC to support high concurrency, support all four isolation levels, the default level is REPEATABLE READ; gap lock to prevent phantom reading. 3. Use clustered index 4. Support for adaptive hash index 5. Backup: hot backup is supported

MariaDB uses XtraDB by default (enhanced version of innodb, developed by percona)

Features:

1. Data storage: tablespace 2. Concurrency: MVCC (multi-version concurrency control), gap lock 3. Index: clustered index, secondary index 4. Performance: expected operation, adaptive hash, insert cache 5. Backup: hot backup is supported (XtraBackup backup tool)

2.2MYISAM

Supports full-text indexing (FULLTEXT index), compression, spatial function (GIS), but does not support transactions, does not support row-level locks, and cannot be safely recovered after a crash (the enhanced version of Aria myisam supports crash secure recovery)

Applicable scenarios: read-only (or write less), small table (can be repaired for a long time)

File:

Tab1_name.frm: table format definition tab1_name.MYD: data file tab1_name.MYI: index file

Properties:

Locking and concurrency: table-level lock repair: manual or automatic repair, but data indexes may be lost: nonclustered indexes defer updating index keys: compressed tables

3.MySQL transactions:

Transaction: a set of atomic sql queries, or a separate unit of work.

3.1 ACID Test:

A: atomicity, the smallest inseparable unit, either all executed successfully, or all failed and rolled back. C: consistency, data is always transferred from one consistency state to another. I: isolation, the operations made by one transaction between submissions can not be seen by others; isolation has multiple isolation levels. the higher the isolation, the higher the security, and the worse the ability to support concurrency at the same time. D: persistence, once the transaction commits, its changes are permanently saved in the database

3.2 transaction process:

Start transaction: start transaction

Archive point: savepoint pointname

Rollback to pointname returns to the archive point

Release pointname delete archive point

End transaction: 1.commit commit 2. Rollback rollback

Recommendation: show request and commit transactions, do not use the autocommit feature, autocommit will result in a large number of disk IO impact on performance.

Method: set SESSION autocommit=0; show variables like'% auto%'; confirmation

3.3 isolation level of transactions:

View level: show global variables like'% tx_isolation%'

READ-UNCOMMITTED # (read uncommitted) READ-COMMITTED # (read commit) REPEATBLE-READ # (rereadable) default SERIALIZABILE # (serializable)

Questions corresponding to the above transaction levels:

Dirty reading: that is, reading other people's uncommitted data can not be read repeatedly: read once before the change in the same transaction, and once after the change. The information read twice is inconsistent (caused by the submission of other transactions in the system when querying): others have modified and committed, and they see the information before modification in the transaction that has been opened before modification. After committing the transaction, you will find that the information has changed. Locked read: when one transaction makes changes to the data that are not committed, other transactions cannot commit.

Deadlock: the state in which two or more transactions occupy each other in the same resource and request to lock the resource occupied by the other party.

Transaction log: the write type of the transaction log is "append", so its operation is sequential IO. This log is also known as write ahead logging ib_logfile.

For the above MySQL password management, storage engine and transaction-related knowledge, we do not find it very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.