MySQL Advanced Features

MySQL Management user Management

CREATE USERUsername identified by 'Password'; New UserCREATE USER@’%' Identified by 'Password';GRANT  All Privileges  on *.*  to[Email protected]'%'; Give the corresponding permission flushPrivileges;

After you create a new user, you can delete the user by using the following command:

DROP USER username;  Delete users Delete from userwhereuser='  Username';  Delete user, need to use MySQL first

The GRANT statement gives the user the appropriate permissions, and in layman's words, grant is equivalent to the following SQL statement:

Grant  on  to username;

How do I see the appropriate permissions for a user?

 for username;

How do I revoke a user's permissions?

REVOKE  All Privileges  on dbname. Table  from username; REVOKE INSERT  on dbname. Table  from username; Revoke CREATE TABLE permissions

Use flush privileges After user Management update, or error 1396 errors may occur.

Table Data replication

The use of select enables synchronous replication of table structures and data.

CREATE TABLE SELECT *  from Oldtablename; CREATE TABLE SELECT *  from where < Ten ; CREATE TABLE  like oldtablename; Table replication can also be achieved using the Linke statement

Database backup and Recovery

Export of the database:

- - Database > Backup - p;   - - Database < Backup -p; Perform data recovery operations

Database must exist when you perform a recovery operation on the databases, otherwise the corresponding database cannot be found when recovering data.

With the source command, you can also perform data recovery operations:

MySQL> use xxx;mysql>backup. sql

MySQL's storage engine

The storage engine is the type of table, MySQL has a variety of storage engines, different storage engines have different storage mechanisms, indexing technology and locking level.

The MySQL storage engine is divided into the engine of the Transaction security table and the engine of the non-transactional security table, the transaction is an important concept in the database, and the transaction has the characteristics of atomicity, consistency, isolation and persistence 4. Transactions are designed to protect the security of the data and prevent database failures that result in inconsistent data in the database. A transaction-safe table can use a COMMIT statement to combine multiple statements for a table that needs to be updated frequently, and a non-transactional security table that has no transaction support, is faster to process, consumes less memory when it is stored, and consumes little memory for data table updates.

MyISAM engine

MyISAM is suitable for a database that reads more than writes, does not support transactional operations, and because it does not need to process transaction records, it consumes less memory and queries are more efficient. Each MyISAM table corresponds to two disk files: one is the data file used to store the data, and the relative path is dbname/tablename. MYD; the other is the index file that stores the index, with a relative path of dbname/tablename. MYI.

InnoDB engine

InnoDB is the default storage engine for MySQL, the InnoDB engine manages transaction security tables, uses multiple versions of concurrency control and row-level locks to provide support for transactions, and in addition to providing transactional support, the INNODB also supports foreign key constraints and has a failure recovery capability. The InnoDB design goal is to handle massive amounts of data with maximum efficiency, with CPU utilization being the highest in the relational database engine on all disks, so the InnoDB engine is well suited for databases that support transactions and have high concurrent read and write frequencies.

Comparison of InnoDB and MyISAM

InnoDB and MyISAM are the most commonly used two storage engines in MySQL, the two storage engines have their pros and cons, the choice of storage engine should be based on the specific application to choose, only in order to maximize the performance advantage of MySQL database.

The differences between InnoDB and MyISAM are summarized as follows:

• MyISAM does not support advanced processing such as transaction processing and foreign key constraints, while the InnoDB storage engine supports
• MyISAM emphasizes performance, which executes faster than InnoDB, while the InnoDB storage engine emphasizes data security.
• MyISAM supports table-level locking, while InnoDB supports row-level locking.
• MyISAM supports full-text indexing, and InnoDB does not support it.

MySQL Advanced Features