MySQL password management, storage engine and transaction

MySQL Password management:

To modify a user password:

(1) Set PASSWORD [for ' user ' @ ' host '] = PASSWORD (' cleartext PASSWORD ');(2) UPDATE mysql.user SET password=password (' Cleart Ext password ') WHERE user= ' USERNAME ' and host= ' Host ';(3) mysqladmin-uusername-hhost-p password ' new_pass ' in force: FLUSH PRI Vileges

To forget the Administrator password solution:

(1) When starting the mysqld process, use the--skip-grant-tables (ignore user name password Authentication) and--skip-networking (disable telnet) option;

Turn off MySQL service

CentOS 7:/usr/lib/systemd/system/mariadb.service Execstart added after

CentOS 6:/etc/init.d/mysqld

Systemctl Daemon-reload

Systemctl Start mariadb

MySQL Direct login

(2) Modify the administrator password through the update command;

(3) Remove the two options added in the first step to start the mysqld process in the normal way;

MySQL Common storage Engine:

InnoDB: Handles a large number of short-term transactions, where data is stored in a tablespace (table space)

1. Data and indexes for all InnoDB tables are placed in the same table space

Tablespace file: Under directory defined by DataDir

Data file: Ibdata1,ibdata2 ....

2. Each table uses a single table space to store the data and indexes of the table

Innodb_file_per_table=on

Data files (storing data and indexes): tbl_name.ibd,tbl_name.frm (table format definition file)

Based on MVCC to support high concurrency, all four isolation levels are supported, the default level is repeatable read, and Gap Lock prevents Phantom reads.

Using Clustered Indexes

Support for adaptive hash indexing

MariaDB (Default with XTRADB (InnoDB enhanced, Percona Organization development))

Data storage: Table space

Concurrency: MVCC (multiple version concurrency control), Gap lock

Index: Clustered index, secondary index

Performance: expected operation, adaptive hash, insert buffer

Backup: Support hot standby (xtrabackup Backup utility)

MyISAM: supports full-text indexing (fulltext index), compression, spatial functions (GIS), but does not support transactions, row-level locks are not supported

Unable to recover safely after crash (enhanced version of Aria MyISAM supports crash-safe recovery)

Application scenario: read-only (or less write), small table (can accept long-time repair operation)

File:

TAB1_NAME.FRM: Table-style definition

Tab1_name. MYD: Data files

Tab1_name. MYI: Index File

Characteristics:

Locking and Concurrency: table-level locks

FIX: Manual or automatic repair, but may lose data

Indexes: Nonclustered indexes

To defer updating the index key:

Compression table

MySQL transaction:

Transactions: A set of atomic SQL queries, or a separate unit of work.

acid Test :

A: atomicity, the smallest unit that cannot be split, or all of them executed successfully, or rolled back after all failed.

C: Consistency, data is always transferred from one consistent state to another consistent state

I: Isolation, the actions of one firm are not visible between submissions, and there are multiple isolation levels for isolation, the higher the security of isolation, and the poorer the ability to support concurrency.

D: Persistence, once a transaction commits, its modifications are permanently saved in the database

Transaction Processes :

Start transaction: Start transaction;

Archive point: SavePoint pointname

Rollback to pointname back to archive point

Release Pointname deleting an archive point

End transaction: 1.commit commit 2. Rollback rollback

Recommendation: Show requests and commit transactions, do not use autocommit, and auto-commit can have a lot of disk IO impact performance.

Methods: Set SESSION autocommit=0; Show variables like '%auto% '; confirm

isolation level of the transaction :

Viewing level: Show global variables like '%tx_isolation% ';

Read-uncommitted (read not submitted)

read-committed (read submit)

Repeatble-read (can be reread) default

Serializabile (Serializable)

Problems that may exist:

Dirty reads: Read data not submitted by others

Non-repeatable READ: read one time before changes in the same transaction, read once, and read two times with inconsistent information

Phantom read: Someone else has modified the commit, and they see in the open transaction is the information before the modification. After committing the transaction, the information is changed.

Locking read: When a transaction makes data changes that are not committed, other transactions cannot be committed

Deadlock: Two or more transactions occupy each other in the same resource and request a lock on the state of the resource occupied by the other.

Transaction log:

The write type of the transaction log is "append", so its operation is sequential Io, which is also known as a write-ahead logging (write) Ib_logfile

This article is from the Linux OPS blog, so be sure to keep this source http://arm2012.blog.51cto.com/2418467/1980770

MySQL password management, storage engine and transaction