7000 words dry goods learning notes, one day finish MySQL

Time:2021-1-21

Introduction of MySQL database

MySQL has been in the second place in the past two years, and may be promoted to the first place at any time, surpassing oracle. Because the performance of MySQL has been optimized all the time, the security mechanism is also gradually mature, and more importantly, it is open source and free.

MySQL is a kind of relational database management system. Relational database stores data in different tables instead of putting all data in a large warehouse, which increases the speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts dual authorization policy, which is divided into community version and commercial version. Because of its small size, fast speed, low total cost of ownership, especially the open source, MySQL is generally chosen as the website database for the development of small and medium-sized websites.

If you don’t install mysql, please move to MySQL service installation

MySQL InnoDB storage engine

  • Storage engine InnoDB is the default storage engine of MySQL version, which is also recommended by mysql. It is a storage engine with high reliability and high performance.
  • In MySQL version 5.7, unless the default storage engine is explicitly specified in the configuration file or the engine = statement is explicitly used to specify other storage engines when creating a table, InnoDB is used by default.

Advantages of InnoDB storage engine:

  • DML statement supports transaction function and ensures acid feature
  • The use of row level lock ensures high concurrency
  • InnoDB optimizes the query performance of tables with primary key based on the primary key, also known as clustered index. It stores all data on the clustered index to reduce the IO consumption of primary key query
  • To ensure the consistency of data, InnoDB also supports foreign key attributes to ensure that there will be no inconsistent data between tables with foreign key constraints
  • When MySQL is restarted due to server hardware or software failure, InnoDB will automatically identify the data submitted before the failure, and return all the data not submitted during the failure, so as to maximize the protection of data from crash recovery

1. Transaction
2. Mvcc (multi version concurrency control)
3. Row level lock
4. Support foreign key
5. Auto crash safety recovery of ACSR
6. Support hot backup

Principle and practice of MySQL replication cluster

There are two ways to copy mysql
  • Traditional way: Based on the bin log of the master database, the log events and event locations are copied to the slave database, and then applied to achieve the purpose of master-slave synchronization.
  • Gtid method: global transaction identifiers is based on transactions to copy data, so it does not depend on the location of log files, and can better ensure the consistency of master-slave database data.

Master slave synchronization process of MySQL database

The “pit” you don’t know in MySQL master slave synchronization architecture (Part one)

“Pits” you don’t know in MySQL master slave synchronization architecture (Part 2)

There are many ways of data backup
  • Physical backup refers to the way to complete the backup by copying the database file. This backup method is suitable for the database which is large, important and needs to be quickly restored
  • Logical backup refers to the backup of the logical structure of the database (create database / TABLE statement) and data content (insert statement or text file). This backup method is applicable to the situation that the database is not very large, or you need to modify the exported file, or you want to rebuild the database on different types of servers
  • In general, the speed of physical backup is faster than that of logical backup. In addition, the granularity range of backup and recovery of physical backup is the whole database or a single file. Whether a single table can be recovered depends on the storage engine. For example, under the MyISAM storage engine, each table corresponds to an independent file and can be recovered separately. However, for the InnoDB storage engine table, each table may correspond to an independent file, or the table may use a shared data file
  • Physical backup is usually required to be performed when the database is closed, but if it is performed when the database is running, the database cannot be modified during the backup
  • The speed of logical backup is slower than that of physical backup, because logical backup needs to access the database and convert the content into the format required by logical backup; generally, the size of backup file output is larger than that of physical backup; in addition, logical backup does not include the content of database configuration file and log file; the granularity of backup and recovery can be all databases or a single number According to the database, it can also be a single table; logical backup needs to be executed when the database is running; its execution tool can be mysqldump or select Two ways of in out file

Send you a backup plan of production database:Backup scheme of high quality enterprise MySQL database

Physical backup mode of MySQL database:Data backup and recovery by xtrabackup

There are several types of MySQL replication:
  • Asynchronous replication: a master library, one or more slave libraries, asynchronous synchronization of data to the slave library.
  • Synchronous replication: a unique replication method in MySQL Cluster.
  • Semi synchronous replication: on the basis of asynchronous replication, ensure that at least one slave database has received the transaction on any primary database before it is committed, and log it down.
  • Delayed replication: on the basis of asynchronous replication, manually set the data synchronization delay time of master database and slave database, that is to ensure that the data delay is at least this parameter.

    MySQL master-slave replication delay solution: the solution of high availability database master-slave replication delay

Design and practice of MySQL high availability architecture

Let’s first understand the introduction of MySQL high availability architecture: talking about MySQL Cluster High Availability architecture

MySQL high availability solution: summary of MySQL synchronous replication and high availability solution

The government also provides a high availability solution:Official tool MySQL router high availability principle and actual combat

MHA
  • MHA (master high availability) is a relatively mature solution for MySQL high availability. The software consists of two parts: MHA Manager (management node) and MHA node (data node).
  • MHA Manager: it can be deployed on a single machine to manage multiple master slave clusters, or on a single slave node.
  • MHA node: the row is on each MySQL server.
  • MHA manager will regularly detect the master node in the cluster. When the master fails, it can automatically promote the slave of the latest data to the new master, and then point all other slaves to the new master again. The entire fail over process is completely transparent to the application.

MHA high availability solutionMha of MySQL Cluster High Availability architecture

MGR
  • MySQL group replication (Mgr) is a new high availability and high expansion MySQL Cluster service released from version 5.7.17.
  • High consistency, group replication technology based on native replication and Paxos protocol, provides consistent data security guarantee by plug-in;
  • High fault tolerance, most services can continue to work when they are normal, automatically detect resource expropriation conflicts among different nodes, deal with them in order first, and build in dynamic anti brain crack mechanism;
  • High scalability, automatically add and remove nodes, and update group information;
  • High flexibility, single master mode and multi master mode. In the single master mode, the master is selected automatically, and all update operations are carried out in the master mode; in the multi master mode, all servers are updated at the same time.

MySQL performance optimization

History of the most comprehensive MySQL high performance optimization combat summary!

MySQL index principle:What is the index of MySQL? How to optimize?

  • As the name suggests, b-tree index uses the data structure of B-tree to store data. Different storage engines use b-tree index in different ways. For example, MyISAM uses prefix compression technology to make index space smaller, while InnoDB stores data according to the original data format. MyISAM index records the physical location of the corresponding data in the index, while InnoDB records the corresponding primary key in the index Numerical value. B-tree usually means that all values are stored in order, and the distance from each leaf page to the root is the same.
  • B-tree index drives the storage engine to search from the root node of the index instead of full table scanning. The root node and the middle node store pointers to the lower level nodes. By comparing the value of the node page with the value to be searched, the appropriate pointer can be found to enter the lower level child node until the lowest level leaf node. The final result is either to find the corresponding node Value, or the corresponding value cannot be found. The depth of the whole B-tree is directly related to the size of the table.
  • Full key value matching: match with all columns in the index, for example, find the person whose name is Zhang San and born on January 1, 1982
  • Match leftmost prefix: match the leftmost column in the index, such as finding all people with surname Zhang
  • Match column prefix: matches the beginning of the leftmost column of the index, such as finding all people whose names start with Z
  • Match range value: match the range range value of the index column, such as looking for people with surnames between Li and Wang
  • Exactly match the left column and the range match the right column: for example, find all people whose last name is Zhang and whose first name begins with K
  • Index only query: query results can be obtained by index, also known as overlay index, such as finding the names of all people with surname Zhang

MySQL table partition introduction:A thorough understanding of MySQL partition

  • It can be allowed to store more data in ⼀ tables ⾥ and break through the disk limit or the device system limit.
  • It is very easy to remove the expired or historical data from the table partition, as long as the corresponding partition is removed.
  • For some query and modify statements, the data range can be automatically reduced to ⼀ or ⼏ table partitions to optimize the efficiency of statement execution. And the statement can be executed by displaying the specified table partition, such as select * from temp partition (P1, P2) where store_ id < 5;
  • Table partition is to divide the data of ⼀ tables into different logical blocks according to the rules set by ⼀ and store them in physical storage respectively. This rule is called partition function, which can have different partition rules.
  • In MySQL version 5.7, you can use the show plugins statement to check whether the current MySQL supports the table partition function.
  • MySQL version 8.0 removes the display of partition by show plugins ⾥ but the table partition function of the community version is turned on by default.
  • However, when a table contains a primary key or a key only, each field that is used as a partition function must be all or part of the key only and primary key in the table, otherwise, a partition table will be created.

Mysql database and table

  • It is not recommended to divide a table within 10 million. It can solve the performance problem through proper indexing, read-write separation and other methods.
  • The number of slices should be as small as possible, and the slices should be evenly distributed on multiple datahosts as possible, because the more slices a query SQL spans, the worse the overall performance will be. Although it is better than the result of all data in one slice, it is only necessary to expand the capacity and increase the number of slices.
  • Slicing rules need to be carefully selected. The selection of slicing rules needs to consider the data growth mode, data access mode, slicing Association, and slicing expansion. The latest slicing strategies are range slicing, enumeration slicing, and consistent hash slicing, which are conducive to capacity expansion.
  • Try not to span multiple partitions in a transaction. Distributed transaction is always a difficult problem to deal with.
  • The query conditions should be optimized as far as possible, and the select * method should be avoided as far as possible. Under a large number of data result sets, a large amount of bandwidth and CPU resources will be consumed. The query should avoid returning a large number of result sets as far as possible, and index the frequently used query statements as far as possible.

Overview of database sub database sub table:When is the database divided into databases and tables? How to divide?

MySQL sub database and sub table scheme:MySQL sub database sub table scheme, the summary is very good!

The idea of MySQL sub database and sub table:Idea and case analysis of rescuing DBA database by sub database and sub table

High availability of read write separation in MySQL database

The storage and access of massive data has become the bottleneck of system design. The growing business data has undoubtedly caused a considerable load on the database, and put forward high requirements for the stability and scalability of the system. With the development of time and business, there will be more and more tables in the database, and the amount of data in the table will be larger and larger. Correspondingly, the cost of data operation will be larger and larger. In addition, no matter how to upgrade hardware resources, the resources of a single server (CPU, disk, memory, network IO, transaction number, connection number) are always limited, and the amount of data that the database can carry will be limited , data processing capacity will encounter bottlenecks. Separate tables, separate databases and separate reading and writing can effectively reduce the pressure of a single database.

Practical cases of MySQL read write separation high availability architecture:

Proxysql + MySQL implementation of database read write separation

Master slave synchronization and read write separation of database based on MySQL + MYCAT

MySQL performance monitoring

MySQL performance monitoring indicators can be roughly divided into the following four categories:

  • Query throughput
  • Query latency and errors
  • Client connections and errors
  • Buffer pool utilization

For MySQL performance monitoring, the government also provides a related service plug-in: MySQL percona. Here is a brief introduction to the installation of the plug-in

[[email protected] ~]# yum -y install php php-mysql
[[email protected] ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/percona-zabbix-templates-1.1.8-1.noarch.rpm
[[email protected] ~]# rpm -ivh percona-zabbix-templates-1.1.8-1.noarch.rpm
warning: percona-zabbix-templates-1.1.8-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing... ################################# [100%]
Updating / installing
...   1:percona-zabbix-templates-1.1.8-1 ################################# [100%]

Scripts are installed to /var/lib/zabbix/percona/scripts
Templates are installed to /var/lib/zabbix/percona/templates

Finally, we can cooperate with other monitoring tools to monitor the performance of MySQL.

MySQL server configuration plug-in:
  • Modify PHP script to connect mysql [email protected] user
  • Modify the socket file path of MySQL
[[email protected] ~]# sed -i '30c $mysql_user = "monitor";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[[email protected] ~]# sed -i '31c $mysql_pass = "123456";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php
[[email protected] ~]# sed -i '33c $mysql_socket = "/tmp/mysql.sock";' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php

Test whether it is available (the monitoring value can be obtained from MySQL)

[[email protected] ~]# /usr/bin/php -q /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php --host localhost --items gg
gg:12

#Ensure that the primary group of the current file is ZABBIX, otherwise ZABBIX monitoring value error.
[[email protected] ~]# ll -sh /tmp/localhost-mysql_cacti_stats.txt
4.0K -rw-rw-r-- 1 zabbix zabbix 1.3K Dec 5 17:34 /tmp/localhost-mysql_cacti_stats.txt

Move ZABBIX agent configuration file to / etc / ZABBIX / ZABBIX_ Agentd.d/directory

[[email protected] ~]# mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/
[[email protected] ~]# systemctl restart zabbix-agent.service
Import and configure ZABBIX template and host:

The default template monitoring time is 5 minutes (the current test is modified to 30s), and the ZABBIX template time should also be modified

#If you want to modify the time when the monitoring gets the value, you need to modify not only the value time in the ZABBIX panel, but also the bash script.
[[email protected] scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.sh
TIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`
if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; then  
#This 300 represents 300s, which also needs to be modified.

The default template version is 2.0.9, which cannot be used in version 4.0. You can export it from version 3.0 and then import it into version 4.0.

In fact, in the actual production process, there are related professional monitoring database third-party open-source software, migrant brother has written related articles before, today for your reference: powerful open-source enterprise database monitoring tool Lepus

MySQL user behavior security

  • Suppose you are a MySQL DBA of a company, and one day all the data in the database of the company are deleted artificially.
  • Although there is data backup, the loss caused by the service stop is tens of millions. Now the company needs to find out the person who did the deletion operation.
  • But there are a lot of people with database operation authority, how to check and where is the evidence?
  • Do you feel powerless?
  • MySQL itself does not have the function of operation audit. Does that mean that you have to admit bad luck when you encounter this situation?

Finally, I will give you some interview questions for you to practice: 24 database interview questions that you must master!

Error or other problems, welcome small partner comments, correction. If you have any help, please click like + forward to share.

We are concerned about the official account of the brother of migrant workers: the road of technology for brother laborers.
7000 words dry goods learning notes, one day finish MySQL

Recommended Today

Master git thoroughly (2)

1、 Transmission Mastering git thoroughly (1)Master git thoroughly (3) 2、 Basic concepts ① Work areaThe so-called work area isThe directory you can see on your computerCan be simply understood as our project root directory is the workspace.② Version LibraryThe so-called version library is executed in the root directory of the projectgit initAfter the command, a. […]