Squeeze your learning capacity limit and learn new content on a MySQL server every day! (3) (contents updated today)

Preface: I don't quite understand whether the notes are not good or the content is not well written. I was accidentally trampled on 14 times, and I stepped on it instantly, I want to say that if you think it is good, you can add it to your favorites, save it, And repost it. If you think it is not good or convenient, you can give bad suggestions and comments. I will correct them, if it is inconvenient, I have not urged you to read my note. You don't have to use that precious strength to step on me. This is not a very professional note. Thanks to all the netizens who gave suggestions and comments.

 

This is a step-by-step process of learning MySQL from the ground up. I have only been familiar with a small number of SQL servers before learning.

First course: Squeeze your learning capability limit and learn new content on a MySQL server every day! (1)

Course of the next day: Squeeze the limit of your learning ability and learn new content on a MySQL server every day! (2)

Next, we will update it every day ~ I believe that you will be able to learn MySQL from scratch through your notes ~

Not to mention nonsense. Start learning today ~!

View

How to Create a view:

Format: Create viewview_name [(View Field column_list)]

Create view view_name as select clause;

The view only saves the statement, but after the view is created, it can be used just like a table.

The purpose of creating a view is to prevent some fields from being viewed by some users.

Suppose we have a table:

For example, you can create a view with no totur segment if the couseid is not empty.

Create view Xiake as select uid, name, age, gender, couseid fromknight where couseid is not null;

A view is not a real table and does not store data. Therefore, it must be slower than a real base table in terms of data processing speed. When the base table changes, the virtual table created by the view changes immediately.

For example, we modify:

Update knight set couseid = 15 where uid = 9

Now let's look at the created view and find that it has changed.

 

Insert data to the View:

A view (virtual table) can be inserted with data. However, all the inserted data is saved in the base table. If this data is inserted, there is no value defined in the base table. You may not be allowed to create an error.

When creating data, you can explicitly specify that the constraints in the base table are applied to the virtual table.

 

Delete View:

Drop viewview_name

 

 

How to Implement subquery:

Select implements nesting. Our select results are obtained from the results of another select.

For example, the format is:

Select * fromselect * From knight

Select * from XXX Where select * fromknight

Update knight setage = 49 Where uid = 6

It is shown that the table is older than the average age of all persons with age:

We first query the average age: selectavg (AGE) from knight where age is not null;

We found that the average age is 33 years old.

Then we can use the subquery to solve it in one step.

Selectname, age from knight where age> (select AVG (AGE) from knight where age isnot null );

Transaction: transact

What is things? The steps are either completed at the same time or not completed at the same time. For our SQL statements, there are several query statements that are either executed at the same time or not executed at the same time. They must perform an atomic operation, which is inseparable.

The most typical application of transactions is transferring money in a bank. For example, if a has 2000, B has 10000, and B needs to be transferred to a3000. If the transfer was just completed and the money had not reached a, the Bank had a power outage. At this time, a had not yet paid, but B had already lost the money ...... What should we do? To avoid such errors. Something appears.

The way for a transaction to handle such a result is to return the 3000 to B and transfer the money again. However, for large databases, there may be tens of thousands of items, so if an error occurs at the end of the execution, the rollback will have to complete the tens of thousands of commands again. To avoid excessive rollback, you can also create a "snapshot" for the transaction. Such a "snapshot" is called a save point, which is the storage point of the transaction.

MyISAM does not support transactions.

Transaction-supporting engine: InnoDB

Only when an engine fully supports acid can we say it supports things.

Acid:

A: atomicity.Things are integrated.

C: consistency.After a transaction is completed, the B that a loses must be added. This logic must be consistent. The result is actually consistent.

I: isolation:The relationship between multiple things. Can other things discover such changes after a change occurs. Whether the transaction is changed when a command is run to find that the original database has changed. This depends on the isolation level of the Data Engine. For MySQL, the isolation level of things is an important attribute.

D: Durability: Persistence:After a transaction is completed, the results are permanently saved.

 

MySQL isolation level: (the default transaction isolation level of MySQL is 3. Repeatable Level .)

1. Read uncommitted: readuncommitted (the process of completing a transaction is called the commit process ). This level is the lowest.

2. Read submission: readcommitted

3. Repeatable: REPEATABLE-READ

4. serialization: seriablizable

 

View the default level:

Show variables like 'tx _ isolation'We can see that the level is REPEATABLES-READ

The higher the isolation level, the better the security of things, but the worse the concurrency (the fewer things that can be executed at the same time ). If things are not very important to you, you can reduce the level of things to increase the concurrency of things.

 

Four levels of link states:

1. Read not submitted:Assume that x = 0 exists in transaction A, and transaction B needs to read X at runtime. when X is changed to 1 in five minutes, B immediately obtains a = 1, after 10 minutes, if a returns X to 0, the value of X in B immediately changes to 0.

2. Read submission:Assume that x = 0 exists in transaction A, and transaction B needs to read X at runtime. when X is changed to 1 in five minutes, transaction B cannot get it, so x = 0, when things a run in 10 minutes and x = 1, B immediately finds x = 1 and modifies its own X = minutes, transaction B ends. After submission, x = 1 in transaction B

3. Repeatable:No matter how the X in a changes, or changes after submission. B is only responsible for running the X value that is initially read as the X value of the transaction regardless of how x changes in the operation of the transaction. Then, after the submission is complete, B reread X to 1.

4. serialization:Only when one thing is completely executed will the other begin. When things B run, it is found that X in A has changed, then B will not start its own things. Until things a is completely executed. Then B reads the latest X value in a and starts execution.

 

How to implement two things during execution, you execute yours, I execute mine, and the data changes between each other are invisible to you. Similar mechanisms are called multi-version concurrency control and MACC.

 

Thing engine:

To use the corresponding things, you must use the corresponding data engine to decide.

The transaction is implicit.

Start transaction: manually enable the transaction. After it is enabled, you can use commands such as updata to save the transaction to the table only when you manually end the transaction commit.

For example, start transaction.

Select * From Knight; 13 lines are found

Insertinto knight set name = 'shi zhongyu', age = 37; insert a row into the transaction;

Select * From Knight; it is found that a row has been inserted into 14 rows.

Rollback

Select * From Knight; then 13 rows are found

Commit; ends the transaction and saves the result to the table.

 

View the transaction level:

Select @ session. tx_isolation;

Modify the transaction level at the session level:

Set session transactionisolation level read uncommitted;

View the connection process in MySQL

Show processlist

 

We operate on two things to view their associated styles. We can see that the level effect is the same as the level effect just described.

 

Mysql Data Synchronization and lock

What is Data Synchronization? If two processes or applications need to access the same data, the synchronization concept should be enabled. As long as synchronization is involved, the lock concept must be used. Lock: how to avoid the "false" error when two operations are combined with the same object.

Suppose there are two SQL statements, M is inserting knight, and N is querying the knight table. In this way, when n is querying, the query may actually be only part of the content. How can this problem be solved? The lock mechanism determines that when a person is doing something, another person cannot operate on it.

The lock is always present. As long as one person reads or writes the lock, the lock begins.

 

Lock category:

Shared locks: Read locks can be performed at the same time. Read can be read by others, but read cannot be written by others.

Exclusive lock: Write lock, which cannot be written by others. It cannot be read by others.

The write lock has a high priority, but you cannot write it all the time. Too many read locks will cause hunger in writing, and too many write locks will also cause hunger in reading ~!

The read and write locks are mutually exclusive, and the write and write locks are also mutually exclusive. Only the read and read locks are shared.

 

You can see the engine lock in show engine.

To ensure the consistency of backups, you cannot allow others to write data during Backup.

 

Lock level:

A table lock locks a table (low concurrency)

Row lock: A lock only locks several rows (the concurrency level is relatively high, but the controllability is complicated and complicated)

Most of the transaction engines are row-level locks. Like MyISAM, table-level locks are used.

 

Lock implementation:

Server-level implementation: on the server, no matter which storage engine you are using, I apply a lock (Table lock)

Storage engine-level implementation: most transaction engines can implement row locks, and some databases can also implement page locks (pages are the memory space pages ).

Small Scale: Page)

Our memory has virtual memory space. Any 32-bit system process will think that it has 4 GB memory available. What should we do if we only have MB of memory? We just need to release a piece of memory for the program to be used, and the ing function is implemented by page. Our memory is stored by page.

 

 

Lock a table

Locktables tbl_name read | write,

1. We add a read lock to knight.

Lock tablesknight read;, then to another terminal, it is found that although the lock, but still can read.

2. Replace knight with a write lock.

Lock tablesknight write;

At this point, when we do not write something to a table, we can read it on another terminal. But when we insert a data locally, the data table is actually locked, on the other terminal, you can find that you cannot read the data, and you have been waiting... When the lock is released locally, the other terminal immediately displays the read new information.

 

Release the lock:

Unlock tables;

 

To achieve better concurrency, we can reduce the granularity (resolution) to reduce the lock level/scope, so that concurrency will be greatly improved.

 

 

Data Engine:

A storage engine is a table-level concept. Therefore, a storage engine is also called a table type. Some engines support transactions, while some storage engines do not. If a database uses multiple engines containing and without transactions, it will inevitably cause trouble for users to support transactions.

 

Common storage engines:

MyISAM/merge: Transactions and table-level locks are not supported. Online backup is not supported.

InnoDB: Supports transactions, row-level locks, and online backup (hot backup ).

Memory: Memory thing storage engine. As long as the data stored in this table is stored in the memory. The main purpose of the memory engine is to provide memory databases without persistent data storage. Therefore, the memory engine is usually used to implement memory tables (temporary tables ). Therefore, memory tables are also called heap tables.

Maria: Supports the transaction engine and row-level locks. After the purchase of InnoDB, those who originally developed InnoDB developed Maria and are still under development ......

Falcon: Supports transactions, Hot Standby, row-level locks, but requires MySQL 6.0

Pbxt: A commercial Engine

Federated: The joint storage engine is only used to join two tables and does not store any data.

NDB: MySQL clusters can only use the engine to process the data in the cluster in the memory. building this cluster requires more than five hosts, and the performance is very good.

Archive: Archive storage engine, used to "mine" data (Intelligent Decision Making and decision support. They are rarely modified. We can compress and store such data that is rarely used. This is the archive storage engine.

Blackhole: The black hole engine is similar to/dev/null in Linux. Any data you store will be quietly discarded by the engine. Copy the log following the log and use it as the forwarding node.

CSV: Databases stored as text files are mainly used for compatibility with other text processing tools and for transplantation, but the processing efficiency is not high.

 

 

 

MyISAM:

Early MyISAM was the default MySQL engine. It is now replaced by InnoDB. It supports full-text indexing and Spatial Indexing to index structured data. Besides, MyISAM has a special structure when organizing its data,Each table corresponds to three files.,

DB. Opt: Define Database options and information

*. Frm:Format definition

* MYD:My data: data storage location

* Myi:My index: Data Index storage location

 

Important features of MyISAM:

1. Non-things

2. Foreign key constraints are not supported

3. Full-text indexing is supported (only MyISAM is supported for full-text indexing). What should we do if we need to perform full-text indexing in the InnoDB engine? Using other plug-ins, such as Lucene (Java language architecture) or sphinx (C language architecture), you can quickly perform full-text indexing on MySQL DATA regardless of the engine.

4. No data cache: data cannot be cached, but can be cached on servers.

5. Only cache indexes. Index cache is also called key buffer.

6. Supports hash and B-tree indexes. Hash is generally used only in specific scenarios, for example, only when age = 30.

7. Table-level locks

8. Reading speed is very fast, and it is a good choice for data warehouses.

9. data can be compressed and stored

10. Online backup is supported.

11. support up to 64 indexes (but this is enough)

 

MyISAM server variables:

Key_buffer_size: Index Cache: Used to define the cache size of the MyISAM index,

Use showvariables like 'key _ buffer_size '; to view the current size.

Even if we do not use the MyISAM engine, you need to set this value. The default value is 8 Mb.

Concurrent_insert:Whether concurrent write (LOCK) is allowed ),

If you want to use concurrent writing, you can allow two write operations to write data concurrently if they do not affect the same data. MySQL needs to determine the impact in advance. Concurrent writing requires that data gaps exist in Table operations.

Delay_key_write:Deferred index/key write operation: On | off

Indexes are used to accelerate the query process. If we make a modification to the table, the index must be rebuilt, which is a waste of time and system resources. This value is used to delay index write operations. It is defined not to re-create an index as soon as the table is modified. Enabled by default

Max_write_lock_count: Well ...... I will not introduce this ...... Refer to the official documentation.

Preload_buffer_size:Used to define the cache size at startup. The default value is 32 KB.

 

Dedicated management tools for MyISAM:

Myisamchk: optimizes analysis and fixes MyISAM table

Myisampack: compresses the MyISAM table. After compression, you cannot modify it.

Myisam_ftdump: displays the full-text index

 

InnoDB:

It has the following features:

1. Supports transactions based on MVCC

2. Supports row-level locks

3. Foreign keys supported

4. Supports clustering indexes. It is a type of B-tree attribute. Non-clustered indexes store indexes and data separately. Therefore, after an index is found, the index is actually just a pointer, its indexes and data are the same. If you can find an index, you can immediately find the data. It is faster than a non-clustered index. So when we read the index to the memory, it means that the data is also read to the memory.

5. Both index cache and data cache are supported. This means that the data queried by the user can be directly cached.

6. Support for online non-blocking backup and hot backup: Commercial backup tools are needed.

 

InnoDB Storage:

InnoDB does not store three files, but stores them in a complete tablespace ib_data.

*. FRMInnoDB definition file

Ib_log *: two files written cyclically are defined by "innodb_log_file_size.

 

Related server parameters:

Innodb_data_home_dir: directory where data files are stored. By default, this is the same as data_dir.

Innodb_data_file_path: Path of the data file (using the relative path)

Innodb_file_per_table: Path of the table file for each table

Innodb_buffer_pool_size: defines the size of the cache data and index space for caching data and indexes. This space is generally relatively large.

Innodb_flush_log_at_trx_commit: defines how long logs are written to real data files. This option has three values: 0, disabled, 1, enabled, 2, and on-demand.

Innodb_log_file_size: The transaction log file size (which records atomic operations) is a loop log. This file is particularly critical. If InnoDB's data file crashes, self-repair relies on these two files. This is a redo log. This file can be defined a little larger. The ideal value is between 128-256m.

 

Storage engine status: showengine InnoDB status;

Semaphores: Engine-level attributes and information

Foreign keyerrors: foreign key error message

Deadlocks: deadlock information. MySQL can automatically remove deadlocks.

Transactions: transaction-related information

File I/O: file I/O Information

Inser buffer andadaptive hash index: Index and hash index information

Log: log information

Buffer poll andmemory: cache-related information

Row Operations: Information about Row Operations

 

 

 

User Management

Define permission assignment using six tables in the MySQL metabase. A total of 6 important files

User, DB, host, tables_priv, columns_priv, procs_priv

 

User: The most important information, three fields: user, the host from which the password is stored, and the values in the password, user, host, password, and password are encrypted.

Tables_priv: Defines the table-level permissions, and whether the table user can execute DML statements such as select.

Columns_priv: More detailed permission control, not only defining a table, but also defining permissions on a field

Procs_priv: Defines whether a user has the permission to execute stored procedures.

 

These six tables can be called the MySQL authorization information table.

 

Types of permissions that can be authorized for MySQL DATA:

1. Select, insert, update, delete, and other DML-related statements.

2. Create (only permission for creating databases and tables) Drop: DDL statements such

3. Index: index permission

4. Alter: defines whether the user can modify the table structure.

5. Show databases; whether the user has the permission to view the database with the access permission on the server

6. Super: Permission to execute management commands

7. Lock tables: Lock permission

8: Create view and show view Permissions

9: create user create and delete User Permissions

10: Replication slave, which is mainly used for replication and whether it has the permission to copy binary log content from the master server

Replication client: it is mainly used for replication permissions and permission to request information used for replication in the master-slave environment.

11: Reload: Permission to restart the server and re-read

12. Shutdown: Permission to disable the service

13. Super: enables users to Manage Server Processes

14: grant option: After you grant a database permission to a user, can this user grant permissions to others?

 

 

How to create a user account:

Create user user_name @ 'host' identified by 'Password'

% Indicates any host when the host is specified

For example, create user Jerry and specify password 123456

Create user Jerry @ '%' identified by '123'

Flush privileges;

 

After we create a user, this user only has the permission to connect to the database and use show databases.

Wildcard characters can be used for host names and specified IP addresses.

For example:

192.168.0.%

192.168.0.0/255.255.255.0

 

Delete A User:

Format: Drop User username @ host

 

How to authorize a user:

Format: grant priv_type on database object [permission level] to user [and change its password] [set the permission that this user will obtain to continue authorization]

 

1. Add various permissions to the Jerry user

Grant Jerry the following permissions to create a database:

Grant create on *. * To 'Jerry '@' % ';

Flush privileges;

If you want to drop the create permission, you do not have the permission.

Authorize drop:

Grant drop on *. * To 'Jerry '@' % ';

Flush privileges;

Grant query permission

Grant select on *. * To 'Jerry '@' % ';

Flush privileges;

 

2. Grant all permissions on the hellodb database:

Grant all privileges on hellodb. * To 'Jerry '@' % ';

 

3. grant permissions and change the password:

Grant priv_list on DB. Table to user @ host [identified by 'Password']

 

Object Type:

Table is tables by default.

Function storage function

Procedure Stored Procedure

 

Permission level:

*

*.*

Db_name

Db_name.tbl.name

Tbl_name

Db_name.routine_name

 

When we grant the permission again, it will attach the permission without overwriting the original permission.

The "usage" in the permission is to allow the user to connect to the server,

 

 

View a person's permissions:

Show grants for 'root' @ 'localhost'

Show grants for 'Jerry '@' %'

 

Revoke permissions:

Revoke [permission type] [specified object] [user];

1. For example, revoke the select permission of Jerry:

Revoke select on hellodb. * From 'Jerry '@' % ';

Flush privileges;

 

 

Restrict the resources used by the user:

This restriction cannot take effect immediately. to take effect immediately, use:

Flush user_resources;

Frequently Used limited resources:

With grant option: sets the user's permission to grant permissions down.

With max_queries_per_hour N;

With max_updates_per_hour N: specifies the maximum number of modifications that a user can perform per hour.

With max_connections_per_hour N: specifies the maximum number of connections that a user can connect to in an hour.

With max_user_connections N: Limit the maximum number of times a single user account can be connected at the same time.

 

 

 

Restrictions on field-level permissions:

Common field levels are limited to select and update;

For example, grant the query field permission.

Grant select (name) on DB. Table to user @ host

Grant the user the permission to execute the storage routine:

Grant execute on [object type] to user @ host

 

What should I do if I forget the password of the MySQL database?

1. reinitialize the MySQL database

Obviously, this is not what we want.

2. Enable Level 1, skip the password, and then go in and reset the password.

We need to restart the service first. When restarting the server, we need to use a special option to start the server:

First, we need to know that the Service/$ userbin/mysqld_safe is started when we start mysqld. ($ Userbin)

Now we need to add some parameters and run them in the background.

/Usr/local/MySQL/bin/mysqld_safe -- skip-grant-tables -- skip-Networking &

In this case, to set a password for the user, you must modify the table:

Use the MySQL command to enter mysql. A password is not required.

Use MySQL

Updata user SET Password = PASSWORD ('redhat') where user = 'root' and host = 'localhost ';

Updata user SET Password = PASSWORD ('redhat') where user = 'root' and host = '2017. 0.0.1 ';

 

Take a day off tomorrow, continue the day after tomorrow, and the content of the day after tomorrow is more important. MySQL backup and Data Replication!