Mysql
Based on the book Mysql in simple terms - Second Edition
1, Basic chapter
1.1 installing Mysql for Linux
First, check whether MySQL and Mariadb are installed in the system
rpm -qa |grep -i mariadb rpm -qa |grep -i mysql
If installed, uninstall
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 ## If you are prompted that there are dependencies and do not want to delete them, use the following yum remove mariadb-libs-5.5.68-1.el7.x86_64
The above is all environmental preparation, and the formal operation will begin now
## Install MySQL repository yum -y localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm ## Formal installation yum install mysql-community-server ## Start and set the startup self start systemctl start mysqld systemctl enable mysqld ## View password grep 'temporary password' /var/log/mysqld.log ## Login, change password mysql -uroot -p ALTER USER 'root'@'localhost' IDENTIFIED BY 'Ljm3326116@123'; ## The password is too complex, right? Change the security rules of the password set global validate_password.policy=0; set global validate_password.length=1; ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; ## Set remote ROOT login ALTER USER 'root'@'localhost' IDENTIFIED BY '123'; flush privileges; ## All right, start playing with Navicat
1.2 installing Mysql with docker
Core two-step operation solution
## Pull mirror docker pull mysql ## Direct operation docker run -p 3321:3306 --name mysql3321 -e MYSQL_ROOT_PASSWORD=123456 -d mysql ## Into the container docker exec -it XXXXXX /bin/bash ## Install a VIM/PING apt-get update apt-get install -y vim apt-get install -y inetutils-ping ## Maybe because of the network relationship, you will report errors many times. Be patient
1.3 addition, deletion, modification and query
Basic CRUD watching rookie tutorial
SQL tutorial | rookie tutorial (runoob.com)
2, SQL optimization
2.1 Show Status
Via * * show status like 'Com #%'** The command can view the execution frequency of various types of SQL that have been executed in the current session

Of course, it can not only view SQL, but also view the running time and connection times of the server
2.2 show profile
This is a performance analysis tool to view the SQL execution process. We can use it to view the probability of SQL hitting the buffer
Recent queries
show profiles;
More detailed information
show profile cpu,block io for query 2;
2.3 EXPLAIN
Complete the performance analysis of SQL statements through EXPLAIN
Let's see the courseware
2.4 index
-- Query index information SHOW INDEX FROM dict;
-- Delete index DROP INDEX idx_parent_id ON dict; -- Add index CREATE INDEX idx_parent_id ON dict(parent_id) -- unique index CREATE UNIQUE INDEX Index name ON t_emp(empno) --Composite index CREATE INDEX Index name ON dict(Field 1,Field 2,Field 3) --After setting the primary key for the table, the database will automatically index it, innodb Index for clusters
Index recommendations
- Fields frequently used as query criteria should be indexed
- If there is a foreign key association between two tables, the foreign key should also be indexed
- Compared with single key index and composite index, composite index has higher cost performance
- For the sorted fields in the query, if the sorted fields are accessed through the index, the sorting speed will be greatly improved
- Statistics or grouping fields in query
When should indexes not be created
- Frequently added, deleted and modified fields
- Fields not used in conditional query
- The difference between the values in the field is too small, such as male / female, married / unmarried
3, The underlying principle of all evil
3.1 clustered / non clustered index and B+Tree
Brief description clustered / non clustered index
-
Clustered index
Clustered together, the primary key uses the clustered index, which is arranged in a certain order. If you want to check 1-6, you only need to take out the 1-6 index without scanning the whole index
-
Non clustered index
Scattered around, not taken in sequence. We have to scan the entire index. Look up the cat or dog in the dictionary. We first look up the opposite dog and then look for it separately. The non primary key uses the non clustered index

In InnoDB, the table data file itself is an index structure organized by B+Tree
Cluster index is to construct a B + tree according to the primary key of each table. At the same time, the row record data of the whole table is stored in the leaf node, which is also called data page. This feature determines that the data in the index organization table is also a part of the index;
So, what is B+Tree?
BTree/B+Tree
3.2 MySQL logical architecture
MySQL logical architecture and workflow_ Programming essays and essays - CSDN blog
3.3 storage engine
Comparison of major storage engines
4, Management and maintenance
4.2 SQL Mode
In MySQL, it is used to solve several kinds of problems
- Set different levels of data verification to ensure data accuracy
- When migrating a database, you can perform syntax verification on SQL to adapt to compatibility problems
4.2 master slave replication (failure)
The docker environment is used for demonstration, which is actually similar to the ordinary environment
prerequisite
- Navicat can be used for basic connection
- With VIM
Based on the above environment, prepare to start formal operation
1. Master slave modify my.cnf file
[mysqld] ## Set up server_id, be unique server-id=101 ## Enable the binary log function for use when Slave is the Master of other Slave log-bin=mysql-slave-bin ## STATEMENT mode - every SQL STATEMENT that modifies data will be recorded in binlog binlog_format=STATEMENT
2. Enter the database, and the master and slave query SHOW MASTER STATUS;
The values of File and Position fields will be used later. Before the subsequent operations are completed, it is necessary to ensure that the Master library cannot do any operations, otherwise the state will change
3. Query the IP address of the container
## It can be followed by the ID or name of the container docker inspect --format='{{.NetworkSettings.IPAddress}}' 02c6170aa655
4. Enter the slave machine and execute the master-slave copy command
## Pause first, because you may have done it once before stop slave; reset slave; ## Execute master-slave copy command change master to master_host='172.17.0.2', master_user='root', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos= 156, master_connect_retry=30; ## start-up start slave; ## View master-slave synchronization status SHOW SLAVE STATUS;
Parameter description
master_port: the port number of the Master, which refers to the port number of the container
master_user: the user used for data synchronization
master_password: the password of the user used for synchronization
master_log_file: specifies which log file Slave starts copying data from, that is, the value of the file field mentioned above
master_log_pos: which Position to start reading, that is, the value of the Position field mentioned above
master_connect_retry: the time interval between retries if the connection fails. The unit is seconds. The default is 60 seconds