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

1. Fields frequently used as query criteria should be indexed
2. If there is a foreign key association between two tables, the foreign key should also be indexed
3. Compared with single key index and composite index, composite index has higher cost performance
4. For the sorted fields in the query, if the sorted fields are accessed through the index, the sorting speed will be greatly improved
5. Statistics or grouping fields in query

When should indexes not be created

1. Frequently added, deleted and modified fields
2. Fields not used in conditional query
3. 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 and non clustered index (easy to understand, concise and comprehensive) - Genesis - blog Park (cnblogs.com)

• 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

Binary lookup tree, balanced binary tree (AVLTree) and balanced multiple lookup tree (B-Tree) B + tree_ Tigerchen CSDN blog

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

1. Set different levels of data verification to ensure data accuracy
2. 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

over