MySQL Database Introduction

MySQL has been the no. 2 in the past two years, and may surpass Oracle to become no. 1 at any time, because MySQL’s performance has been optimized, security mechanism is gradually mature, and more importantly, it is open source and free.

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

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the double licensing policy, which is divided into community edition and commercial edition. Due to its small size, fast speed and low total cost of ownership, especially the characteristics of open source, MySQL is generally selected as the website database for the development of small and medium-sized websites.

If you do not know how to install MySQL, proceed to install MySQL service

MySQL InnoDB storage engine

  • Storage engine InnoDB is the default storage engine and recommended storage engine for MySQL. InnoDB is a storage engine with high reliability and high performance.

  • In MySQL5.7, the default is InnoDB unless the default storage engine is explicitly specified in the configuration file or another storage engine is explicitly specified using the engine= statement when creating tables.

Benefits of InnoDB storage engine:

  • DML statements support transactions to ensure ACID properties

  • The use of row-level locking guarantees high concurrency properties

  • InnoDB optimizes query performance for tables with primary keys, also known as clustered indexes, and stores all data on clustered indexes to reduce IO consumption for primary key queries

  • To ensure data consistency, InnoDB also supports foreign key attributes to ensure that there are no inconsistent data between tables with foreign key constraints

  • When MySQL restarts due to server hardware or software failure, InnoDB will automatically identify the data that has been submitted before the failure and rollback all the data that has not been submitted before the failure to protect data from loss (Crash Recovery) to the maximum extent.

1. Transaction


2. MVCC (Multi-version Concurrency Control)


3, row-level Lock


4. Support foreign keys


5. Auto Crrash Safe Recovery (ACSR) Automatically recovers faults


6. Support hot backup

MySQL replication cluster principle and practice

There are two methods of MySQL replication:

  • Traditional method: bin-log based on the master library copies log events and event locations to the slave library, which is then applied to achieve master/slave synchronization.

  • Gtid: Global Transaction Identifiers replicate data based on transactions, so they don’t rely on log file locations, and can be used to ensure data consistency between master and slave libraries.

Primary/secondary synchronization of MySQL databases

MySQL master-slave synchronization (master-slave synchronization)

MySQL master-slave synchronization (master-slave synchronization)

Data backup in various ways:

  • Physical backup refers to copying database files to back up data. This backup mode applies to a large database with important data that needs to be quickly restored

  • A logical backup is performed by backing up the logical structure of the database (create DATABASE/TABLE statements) and the data content (INSERT statements or text files). This is useful if the database is not very large, you need to make changes to the exported files, or you want to recreate the database on a different type of server

  • Physical backup is usually faster than logical backup, and the granularity of backup and restore for physical backup ranges from the entire database to a single file. The ability to recover a single table depends on the storage engine. For example, in MyISAM storage engine, each table corresponds to an independent file and can be recovered independently. But for InnoDB storage engine tables, each representation may correspond to a separate file, or tables may use shared data files

  • A physical backup is usually required to be performed with the database down, but if it is performed with the database running, it is required that the database cannot be modified during the backup

  • The speed of a logical backup is slower than that of a physical backup because a logical backup needs to access the database and convert the content into the format required by the logical backup. The size of the output backup file is usually larger than that of the physical backup. In addition, the logical backup does not contain the database configuration file and log file content; The granularity of backup and restore can be for all databases, a single database, or a single table; The logical backup must be performed when the database is running. It can be executed by mysqldump or select… Into outfile in two ways

Give you a production database backup solution: high force grid enterprise MySQL database backup solution

Physical backup of MySQL database: Xtrabackup backs up and recovers data

There are many types of MySQL replication:

  • Asynchronous replication: Data is asynchronously synchronized from one master library to one or more slave libraries.

  • Synchronous replication: a unique replication mode in MySQL Cluster.

  • Semi-synchronous replication: Based on asynchronous replication, ensuring that a transaction on any master has been received and logged by at least one slave before committing.

  • Delayed replication: On the basis of asynchronous replication, the data synchronization delay of the primary and secondary databases is manually set. That is, the data synchronization delay is at least this parameter.

MySQL primary/secondary replication delay solution: high availability database primary/secondary replication delay solution

MySQL high availability architecture design and practice

MySQL cluster high availability Architecture
MySQL High availability solution: MySQL synchronous replication and high availability solution summary
MySQL Router high availability principle and practice
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: Can be deployed on an independent machine to manage multiple master-slave clusters or on a slave node.

  • MHA Node: Row on each MySQL server.

  • The MHA Manager periodically probes the master node in the cluster. When the master fails, it can automatically promote the latest slave to the new master and redirect all other slaves to the new master. The entire failover process is completely transparent to the application.

MySQL cluster high availability architecture MHA

MGR

  • Mysql Group Replication(MGR) is a new high availability and high scale Mysql cluster service released from version 5.7.17.

  • High consistency, based on native replication and PAxOS protocol group replication technology, plug-ins to provide consistent data security;

  • High fault tolerance, most services can continue to work when normal, automatic detection of different nodes resource acquisition conflict, according to the sequence of priority processing, built-in dynamic anti-brain split mechanism;

  • High scalability, automatically add and remove nodes, and update group information;

  • High flexibility, single master mode and multi master mode. The single master mode automatically selects the master, and all update operations are carried out in the master; Multi-master mode, all servers updated at the same time.

MySQL Performance Optimization

The most complete MySQL performance optimization summary!
What is an index for MySQL? How do you optimize?

  • As the name implies, b-tree indexes use b-Tree data structures to store data. Different storage engines use B-Tree indexes in different ways. For example, MyISAM uses prefix compression technology to make index space smaller, while InnoDB stores data in raw format. The MyISAM index records the physical location of the corresponding data in the index, while InnoDB records the corresponding primary key value in the index. B-tree usually means that all values are stored sequentially and that each leaf page is the same distance from the root.

  • B-tree indexes driven storage engines no longer data obtained through a full table scan, but starting from the root node of the index search, in the middle of the root node and the node to store the pointer to the lower nodes, by comparing the values and to find the node page can find a pointer to the next appropriate straton node, until the leaf node of the bottom The end result is either finding the corresponding value or not finding the corresponding value. The depth of the entire B-tree is directly related to the size of the table.

  • Full key value matching: Matches all columns in the index, such as finding a person named Zhang SAN, born 1982-1-1

  • Match the leftmost prefix: Match the leftmost column in the index, for example, find all people with the last name Zhang

  • Match column prefix: Matches the beginning of the leftmost column of the index, such as finding all people whose names start with Z

  • Matching range value: Matches the range region value of the index column, for example, looking for people whose last names are between Li and Wang

  • Matches the left column exactly and ranges the right column: for example, find all the people whose last name is Zhang and whose names begin with K

  • Index-only queries: Query results can be obtained entirely by the index, also known as overwriting indexes, such as finding all the names of people with the surname zhang

MySQL table partition introduction: a thorough understanding of MySQL partition

  • Allows you to store more data in a table, breaking disk or file system limits.

  • It is easy to remove stale or historical data from a table partition by removing the corresponding partition.

  • For some query and modify statements, data can be automatically narrowed down to one or more table partitions, optimizing statement execution. Select * from temp partition(P1,p2) where store_id < 5; select * from temp partition(P1,p2) where store_id < 5;

  • Table partitioning is to divide the data of a table into different logical blocks according to certain rules and store them separately. This rule is called partitioning function and can have different partitioning rules.

  • You can use the show plugins statement to check whether MySQL currently supports table partitioning.

  • MySQL8.0 removes the partition display in show plugins, but the community version has table partitioning enabled by default.

  • However, when a table contains primary or unique keys, each field used as a partitioning function must be all or part of the unique key and primary key in the table, otherwise the partitioned table cannot be created.

MySQL database table

  • Fragmentation is not recommended for tables less than 10 million. Proper indexes and read/write separation can solve performance problems.

  • The number of fragments should be as small as possible, and the fragments should be evenly distributed on multiple Datahosts. Because the more cross-fragments a query SQL has, the worse the overall performance is. Although it is better than the result of all data in a fragment, it can only be expanded when necessary to increase the number of fragments.

  • The sharding rules need to be carefully selected. In the selection of sharding rules, we need to consider the growth mode of data, the query mode of data, the correlation of sharding, and the expansion of sharding. The latest sharding strategies are range sharding, enumeration sharding, and consistent Hash sharding, which are conducive to expansion.

  • Try not to have SQL in a transaction that spans multiple shards. Distributed transactions are always a problem.

  • Optimize the query conditions and avoid Select *. A large number of result sets consume a large amount of bandwidth and CPU resources. Avoid returning a large number of result sets and create indexes for frequently used query statements.

Database sub – table overview: database sub – table, when sub – table? What points?

Mysql subdatabase subtable solution: Mysql subdatabase subtable solution, summary very good!

The idea of Mysql library and table: Saving DBA – database database and table ideas and case analysis

The MySQL database is highly available for read/write separation

The storage and access of massive data has become a bottleneck problem in system design. The growing business data undoubtedly causes a considerable load on the database, and at the same time puts 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 more and more large. Accordingly, the cost of data operation will be more and more large. In addition, no matter how to upgrade the hardware resources, the resources of a single server (CPU, disk, memory, network IO, transaction number, connection number) are always limited, and the final database can bear the amount of data, data processing capacity will encounter bottlenecks. Table, library and read/write separation can effectively reduce the pressure of a single database.

MySQL read-write separation high availability architecture

ProxySQL+Mysql implements database read and write separation

Mysql+Mycat implements master/slave synchronization and read-write separation

MySQL Performance Monitoring

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

  • Querying throughput

  • Query delays and errors

  • Client connection with error

  • Buffer pool utilization

For MySQL performance monitoring, there is also a related service plug-in: mysql-percona

[root@db01 ~]# yum -y install php php-mysql[root@db01 ~]# wget https://www.percona.com/downloads/percona-monitoring-plugins/percona-monitoring-plugins-1.1.8/binary/redhat/7/x86_64/per Cona-zabbix-templates -1.1.8-1.noarch. RPM [root@db01 ~]# RPM -ivh percona-zabbix-templates-1.1.8-1.noarch. rpmWarning: Percona-zabbix-templates -1.1.8-1.noarch. RPM: Header V4 DSA/SHA1 Signature, key ID cd2efd2A: NOKEYPreparing... ################################# [100%]Updating / installing... 1: percona - zabbix - templates - 1.1.8-1 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%] Scripts are installed to /var/lib/zabbix/percona/scriptsTemplates are installed to /var/lib/zabbix/percona/templatesCopy the code

Finally, MySQL performance can be monitored with other monitoring tools.

MySQL server configuration plug-in

  • Modify the PHP script to connect to the monitor@localhost user of MySQL

  • Change the sock file path of MySQL

[root@db01 ~]# sed -i '30c $mysql_user = "monitor"; ' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php[root@db01 ~]# sed -i '31c $mysql_pass = "123456"; ' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.php[root@db01 ~]# sed -i '33c $mysql_socket = "/tmp/mysql.sock"; ' /var/lib/zabbix/percona/scripts/ss_get_mysql_stats.phpCopy the code

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

[root@db01 ~]# / usr/bin/PHP - q/var/lib/zabbix percona/scripts/ss_get_mysql_stats. PHP - host localhost - items GGGG: 12 # to ensure that the current file owner belong to the group The value is Zabbix. Otherwise, the value of zabbix monitoring is incorrect. [root@db01 ~]# ll-sh/TMP /localhost-mysql_cacti_stats. Txt4.0k-rw-rw-r -- 1 zabbix zabbix 1.3K Dec 5 17:34 /tmp/localhost-mysql_cacti_stats.txtCopy the code

Move the zabbix-agent configuration file to /etc/zabbix/zabbix_agentd.d/

[root@db01 ~]# mv /var/lib/zabbix/percona/templates/userparameter_percona_mysql.conf /etc/zabbix/zabbix_agentd.d/[root@db01 ~]# systemctl restart zabbix-agent.serviceCopy the code

Import and configure the Zabbix template and host:

The default template monitoring time is 5 minutes (currently changed to 30 seconds in the test). At the same time, you need to change the Zabbix template time

If you want to change the monitoring time, you should not only change the value time in the Zabbix panel, but also change the bash script. [root@db01 scripts]# sed -n '/TIMEFLM/p' /var/lib/zabbix/percona/scripts/get_mysql_stats_wrapper.shTIMEFLM=`stat -c %Y /tmp/$HOST-mysql_cacti_stats.txt`if [ `expr $TIMENOW - $TIMEFLM` -gt 300 ]; Then # this 300 stands for 300s and also needs to be modified.Copy the code

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

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

MySQL user behavior security

  • Suppose you are a company mysql-DBA and suddenly all of your company database is deleted.

  • Despite the data backups, millions of dollars were lost due to the service outage, and now the company needs to find out who did the deletion.

  • But there are a lot of people with database operation authority, how to investigate, where is the evidence?

  • Do you feel powerless?

  • Mysql itself does not have an audit function. Does that mean you are out of luck?

24 database interview questions you must Master!