Whoever says that he can't learn MySQL database, throw this to him!

Hello everyone, I'm a migrant worker.

It is the beginning of a new year of struggle. I believe that many people will definitely change after the Lunar New Year (job-hopping and salary increases are indispensable). Therefore, I have made a relevant arrangement of the MySQL technical articles that I have pushed in the past. If the foundation is not good, you can learn from the most basic one, and you can also extract and deepen it if you improve it.

It is not easy to code words. If you can help, please follow at and forward it to the circle of friends support the migrant workers, pay attention to me, and learn more about IT technology and make progress together.

what is database

Database management system, referred to as DBMS (Database Management System), is a management system used to store data.

Importance of DBMSs

• Unable to share data with multiple people
• Inability to provide the format required to manipulate large amounts of data
• Achieving read automation requires programming skills
• Unable to deal with emergencies

Types of DBMSs

• Hierarchical database

  • One of the oldest databases, rarely used due to prominent shortcomings

• Relational Database

  • The database is managed with a row-column two-dimensional table structure, similar to an Excel structure, and a special SQL language is used to control the data.

• Common types of relational database management systems

  • Oracle ==> Oracle
  • SQL Service ==> Microsoft
  • DB2 ==> IBM
  • PostgreSQL ==> open source
  • MySQL ==> Open Source

• object oriented database

  • XML database
  • key-value storage system
  • DB2
  • Redis
  • MongoDB

SQL statements and their types

• DDL (Data Definition Language)

  • create ==> Create objects such as databases or tables
  • drop ==> delete objects such as databases or tables
  • alter ==> Modify the structure of objects such as databases or tables

• DML (Data Manipulation Language)

  • select ==> query data in the table
  • insert ==> insert data into the table
  • update ==> update the data in the table
  • delete ==> delete the data in the table

• DCL (Data Control Language)

  • commit ==> decide to make changes to the data in the database
  • rollback ==> cancel the changes to the data in the database
  • grant ==> give the user the operation permission
  • revoke ==> cancel the user's operation permission

Basic writing rules of SQL

• SQL statements must end with ;
• Keywords are case-insensitive, but data in tables is case-sensitive
• Keyword capitalization
• Capitalize the first letter of the table name
• listed in lowercase
• The way constants are written is fixed
• When encountering types such as strings and dates, you need to use ''
• Use spaces to separate words
• naming convention
• The names of databases and tables can use English, data and underscores
• Name must start with English
• Name cannot be repeated
• Master these core knowledge points of SQL, so you don't have to worry about bragging anymore

type of data

• integer

  • Numeric, but cannot store decimals

• char

  • Fixed-length string type, specify the maximum length, fill with spaces if it is not enough

• varchar

  • Variable-length string type, specifying the maximum length, but not filling if it is insufficient

• data

  • Storage date, year/month/day

The above content is an introduction to the knowledge points related to general databases and sql statements. This article will not go into too much detail. This article mainly focuses on the relational database: MySQL to summarize the knowledge points in all aspects.

Introduction to MySQL Databases

MySQL is the most popular relational database management system, MySQL is one of the best RDBMS (Relational Database Management System: relational database management system) application software in WEB application.

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently owned by Oracle. MySQL is a relational database management system that keeps data in different tables instead of keeping all the data in one big warehouse, which increases speed and improves flexibility.

• MySQL is open source and is currently a product of Oracle.
• MySQL supports large databases. Can handle large databases with tens of millions of records.
• MySQL uses the standard SQL data language form.
• MySQL can run on multiple systems and supports multiple languages. These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl, among others.
• MySQL has good support for PHP, which is currently the most popular Web development language.
• MySQL supports large databases and data warehouses with 50 million records. The 32-bit system table file can support a maximum of 4GB, and the 64-bit system supports a maximum table file of 8TB.
• MySQL can be customized, using the GPL protocol, you can modify the source code to develop your own MySQL system.

In daily work and study, whether it is development, operation and maintenance, or testing, the learning of database is inevitable, and it is also one of the necessary technologies for daily work. In Internet companies, there are many open source product lines, and MySQL is the most important database used by Internet companies.

For more information about the MySQL database, interested readers can refer to the official website documentation and this article: may be the best important MySQL knowledge point in the entire network , for the introduction of MySQL architecture, please refer to: MySQL Architecture Overview- >Query execution flow ->SQL parsing sequence

MySQL installation

The official version 8.0.11 of MySQL 8 has been released. Officially, MySQL 8 is 2 times faster than MySQL 5.7, and it also brings a lot of improvements and faster performance! Who is the coolest? See: MySQL 5.7 vs 8.0, which is better?

For detailed installation steps, please refer to: MySQL 8.0 installation and deployment under CentOS, super detailed! Introduce several 8.0 main new features in relational databases: 5 new features of MySQL 8.0, so practical!

Getting Started with MySQL Basics

Windows Services

-- 启动MySQL
net start mysql

-- 创建Windows服务
sc create mysql binPath= mysqld_bin_path(注意：等号与值之间有空格)

Connecting and disconnecting servers

mysql -h 地址 -P 端口 -u 用户名 -p 密码

SHOW PROCESSLIST -- 显示哪些线程正在运行
SHOW VARIABLES -- 显示系统变量信息

database operations

-- 查看当前数据库
SELECT DATABASE();

-- 显示当前时间、用户名、数据库版本
SELECT now(), user(), version();

-- 创建库
CREATE DATABASE[ IF NOT EXISTS] 数据库名 数据库选项
    数据库选项：
        CHARACTER SET charset_name
        COLLATE collation_name

-- 查看已有库
    SHOW DATABASES[ LIKE 'PATTERN']

-- 查看当前库信息
    SHOW CREATE DATABASE 数据库名

-- 修改库的选项信息
    ALTER DATABASE 库名 选项信息

-- 删除库
    DROP DATABASE[ IF EXISTS] 数据库名
        同时删除该数据库相关的目录及其目录内容

table operations

-- 创建表
CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
每个字段必须有数据类型
最后一个字段后不能有逗号
TEMPORARY 临时表，会话结束时表自动消失
对于字段的定义：
   字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
-- 表选项
  -- 字符集
  CHARSET = charset_name
  如果表没有设定，则使用数据库字符集
  -- 存储引擎
  ENGINE = engine_name
  表在管理数据时采用的不同的数据结构，结构不同会导致处理方式、提供的特性操作等不同
  常见的引擎：InnoDB MyISAM Memory/Heap BDB Merge Example CSV MaxDB Archive
  不同的引擎在保存表的结构和数据时采用不同的方式
  MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
  InnoDB表文件含义：.frm表定义，表空间数据和日志文件
  SHOW ENGINES -- 显示存储引擎的状态信息
  SHOW ENGINE 引擎名 {LOGS|STATUS} -- 显示存储引擎的日志或状态信息
    -- 自增起始数
        AUTO_INCREMENT = 行数
    -- 数据文件目录
        DATA DIRECTORY = '目录'
    -- 索引文件目录
        INDEX DIRECTORY = '目录'
    -- 表注释
        COMMENT = 'string'
    -- 分区选项
        PARTITION BY ... (详细见手册)

-- 查看所有表
SHOW TABLES[ LIKE 'pattern']
SHOW TABLES FROM 表名

-- 查看表机构
SHOW CREATE TABLE 表名 （信息更详细）
DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

-- 修改表
   -- 修改表本身的选项
    ALTER TABLE 表名 表的选项
    eg: ALTER TABLE 表名 ENGINE=MYISAM;
    -- 对表进行重命名
    RENAME TABLE 原表名 TO 新表名
    RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）
    -- RENAME可以交换两个表名
    -- 修改表的字段机构（13.1.2. ALTER TABLE语法）
       ALTER TABLE 表名 操作名
       -- 操作名
          ADD[ COLUMN] 字段定义       -- 增加字段
            AFTER 字段名          -- 表示增加在该字段名后面
            FIRST               -- 表示增加在第一个
            ADD PRIMARY KEY(字段名)   -- 创建主键
            ADD UNIQUE [索引名] (字段名)-- 创建唯一索引
            ADD INDEX [索引名] (字段名) -- 创建普通索引
            DROP[ COLUMN] 字段名      -- 删除字段
            MODIFY[ COLUMN] 字段名 字段属性     -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
            CHANGE[ COLUMN] 原字段名 新字段名 字段属性      -- 支持对字段名修改
            DROP PRIMARY KEY    -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
            DROP INDEX 索引名 -- 删除索引
            DROP FOREIGN KEY 外键    -- 删除外键
-- 删除表
    DROP TABLE[ IF EXISTS] 表名 ...

-- 清空表数据
    TRUNCATE [TABLE] 表名

-- 复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名

-- 复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名

-- 检查表是否有错误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...

-- 优化表
   OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

-- 修复表
   REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]

-- 分析表
   ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

For more relevant operation basics, please refer to the following articles:

• Getting Started with MySQL Database - Common Basic Commands
• 1047 lines of MySQL detailed study notes (worthy of study and collection)
• Introduction to common commands for MySQL basics

MySQL multi-instance configuration

MySQL database entry - multi-instance configuration

MySQL master-slave synchronous replication

Replication overview

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The Mysql data is distributed to multiple systems. This distribution mechanism is achieved by copying the data of a Mysql host to other hosts (slaves) and re-executing it. During replication, one server acts as the master, and one or more other servers act as slaves. The master server writes updates to binary log files and maintains an index of the files to track log rotation. These logs can record updates sent to slave servers. When a slave connects to the master, it informs the master of the location of the last successful update the slave read in the log. The slave receives any updates that have occurred since then, then blocks and waits for the master to notify of new updates.

Note that when you replicate, all updates to the replicated tables must be made on the primary server. Otherwise, you must be careful to avoid conflicts between user updates to tables on the master and updates to tables on the slave.
Replication types supported by mysql:

• L uses statement-based replication by default, which is more efficient. Row-based copying is automatically selected when exact copying is not possible.
• l5.0 began to support
• Use row-based replication.

Copy the problem solved

MySQL replication technology has the following characteristics:

• Data distribution
• load balancing
• Backups
• High availability and failover

How replication works

Overall, there are 3 steps to replication:

• The master records changes to the binary log (these records are called binary log events);
• The slave copies the master's binary log events to its relay log;
• The slave redoes events in the relay log, changing the data to reflect its own.

For more related in-depth introductions, refer to: Mysql master-slave architecture replication principle and configuration details

There are two approaches to MySQL replication:

• Traditional method: Based on the bin-log of the master library, log events and event locations are copied to the slave library, and the slave library is applied to achieve the purpose of master-slave synchronization.
• Gtid method: global transaction identifiers replicate data based on transactions, so it does not depend on the location of log files, and at the same time, it can better ensure the consistency of master-slave database data.
• MySQL database master-slave synchronization actual combat process
• MySQL master-slave synchronization practice based on Gtid
• The "pit" you don't know about the MySQL master-slave synchronization architecture (
• MySQL Master-Slave Synchronization Architecture (Part 2)

There are several types of MySQL replication:

• Asynchronous replication: a master library, one or more slave libraries, data is asynchronously synchronized to the slave library.
• Synchronous replication: A unique replication method in MySQL Cluster.
• Semi-synchronous replication: On the basis of asynchronous replication, it is ensured that at least one slave database has received the transaction and recorded it in the log before any transaction on the master database is committed.
• Delayed replication: On the basis of asynchronous replication, the data synchronization delay time of the master database and the slave database is artificially set, that is, to ensure that the data delay is at least this parameter.

MySQL master-slave replication delay solution: High-availability database master-slave replication delay solution

MySQL data backup and recovery

There are many ways to back up data:

• Physical backup refers to backup by copying database files. This backup method is suitable for databases with large databases, important data and fast recovery.
• Logical backup refers to the way to complete the backup by backing up the logical structure of the database (create database/table statement) and data content (insert statement or text file). This backup method is suitable for situations where the database is not very large, or you need to make certain changes to the exported files, or you want to rebuild the database on another different type of server
• Generally, the speed of physical backup is faster than that of logical backup. In addition, the backup and recovery granularity of physical backup is the entire database or a single file. Whether a single table has the ability to recover depends on the storage engine. For example, under the MyISAM storage engine, each table corresponds to an independent file, which can be recovered separately; but for the InnoDB storage engine table, each representation may correspond to an independent file. It is also possible that the table uses a shared data file
• Physical backups are usually required to be performed with the database shut down, but if performed while the database is running, it is required that 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; usually the output backup file size is also larger than that of physical backup; in addition, logical backup does not include the configuration of the database The content of files and log files; the granularity of backup and recovery can be all databases, a single database, or a single table; logical backup needs to be executed while the database is running; its execution tool can be mysqldump or select … into outfile two ways
• Production database backup plan: high-quality enterprise-level MySQL database backup plan
• MySQL database physical backup method: Xtrabackup realizes data backup and recovery
• MySQL scheduled backup: Several ways of MySQL database scheduled backup (very comprehensive)

MySQL High Availability Architecture Design and Practice

First, let's take a look at the introduction of MySQL high availability architecture: Talking about MySQL cluster high availability architecture

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

also officially provides a high availability solution: official tool｜MySQL Router high availability principle and actual combat

MHA

• MHA (Master High Availability) is currently 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 an independent machine to manage multiple master-slave clusters, or it can be deployed on a slave node.
• MHA Node: row on each MySQL server.
• The MHA Manager periodically detects the master node in the cluster. When the master fails, it can automatically promote the latest data slave to the new master, and then re-point all other slaves to the new master. The entire failover process is completely transparent to the application.

MHA High Availability Solution: MySQL Cluster High Availability Architecture MHA

MGR

• Mysql Group Replication (MGR) is a brand-new high-availability and high-expansion MySQL cluster service released from version 5.7.17.
• High consistency, based on native replication and paxos protocol group replication technology, providing consistent data security guarantees in the form of plug-ins;
• High fault tolerance, most services can continue to work normally, automatic detection of resource requisition conflicts between different nodes, priority processing in order, built-in anti-brain split mechanism;
• High scalability, automatically adding and removing nodes, and updating group information;
• High flexibility, single master mode and multi master mode. In single-master mode, the master is automatically selected, and all update operations are performed on the master; in multi-master mode, all servers are updated at the same time.

MySQL database read and write separation high availability

The storage and access of massive data has become the bottleneck problem of system design. The growing business data will undoubtedly cause a considerable load on the database, and at the same time 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 tables will also increase, correspondingly, the overhead of data operations will also increase; in addition, no matter how to upgrade the hardware Resources. The resources of a single server (CPU, disk, memory, network IO, number of transactions, and number of connections) are always limited. Ultimately, the amount of data and data processing capabilities that the database can carry will encounter bottlenecks. Sub-table, sub-library and read-write separation can effectively reduce the pressure on a single database.

MySQL read-write separation high-availability architecture practical case:

ProxySQL+Mysql realizes database read and write separation actual combat

Mysql+Mycat realizes database master-slave synchronization and read-write separation

MySQL performance optimization

most complete MySQL high-performance optimization combat summary in history!

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

• As the name implies, B-tree indexes use the B-tree data structure to store data. Different storage engines use B-tree indexes in different ways. For example, MyISAM uses prefix compression technology to make the index space smaller, while InnoDB stores data in the original data format. , and 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. A B-Tree usually means that all values are stored sequentially and that each leaf page is the same distance from the root.
• The B-Tree index drives the storage engine to no longer obtain data through a full table scan, but to search from the root node of the index. Pointers to the lower nodes are stored at the root node and intermediate nodes. By comparing the value of the node page with the value to be searched The value can find a suitable pointer to enter the lower child node until the lowest leaf node, and the final result is that either the corresponding value is found, or the corresponding value cannot be found. The depth of the entire B-tree is directly related to the size of the table.
• Full key value matching: Match all columns in the index, such as finding people whose name is zhang san and was born on 1982-1-1
• Match the leftmost prefix: match the leftmost column in the index, such as finding all people with the last name zhang
• Match column prefix: match 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 area value of the index column, such as finding people whose last name is between li and wang
• Exactly match the left column and range match the right column: for example, find all people whose last name is Zhang and whose first name starts with K
• Query that only accesses the index: The query results can be obtained completely through the index, also known as the covering index, such as finding the names of all people whose last name is zhang
• MySQL 30 Commonly Used SQL Query Statement Optimization Methods |
• MySQL too slow? Try these diagnostic ideas and tools
• MySQL performance optimization 9 postures, interview is no longer afraid!

MySQL table partitioning: article thoroughly understands MySQL partitioning

• Can allow more data to be stored in a table, breaking the disk limit or file system limit.
• It is easy to remove expired or historical data from the table in the table partition, as long as the corresponding partition is removed
• For some query and modification statements, the data range can be automatically reduced to one or several table partitions to optimize statement execution efficiency. And you can execute the statement by displaying the specified table partition, such as select * from temp partition(p1,p2) where store\_id < 5;
• Table partitioning is to divide the data of a table into different logical blocks horizontally according to certain rules and store them separately. This rule is called a partition function, and there can be different partition rules.
• In MySQL 5.7, you can use the show plugins statement to check whether the current MySQL supports the table partitioning function.
• MySQL 8.0 version removes the display of partitions in show plugins, but the table partitioning function of the community version is enabled by default.
• However, when the table contains a primary key or a unique key, each field used as a partition function must be all or part of the unique key and the primary key in the table, otherwise a partitioned table cannot be created.

MySQL sub-database sub-table

• It is not recommended to divide the table within 10 million. Through appropriate indexes, read-write separation, etc., the performance problem can be well solved.
• The number of shards should be as few as possible, and the shards should be distributed evenly on multiple DataHosts as much as possible, because the more a query SQL spans across shards, the worse the overall performance, although it is better than the result of all data in one shard, only when necessary Expand capacity and increase the number of shards.
• The sharding rules need to be carefully selected. The choice of sharding rules needs to consider the growth mode of data, the access mode of data, the problem of fragmentation correlation, and the problem of fragmentation expansion. The most recent fragmentation strategy is range fragmentation, enumeration fragmentation. shards, consistent Hash shards, these types of shards are all conducive to capacity expansion.
• Try not to use SQL in one transaction to span multiple shards. Distributed transactions have always been a difficult problem to handle.
• Query conditions should be optimized as much as possible, and the method of Select * should be avoided as much as possible. A large amount of data result sets will consume a lot of bandwidth and CPU resources. The query should try to avoid returning a large number of result sets, and try to build indexes for frequently used query statements.

database sub-database sub-table overview: database sub-database sub-table, when? How to divide?

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

Mysql sub-database sub-table idea: rescue DBA - database sub-database sub-table idea and case analysis

MySQL performance monitoring

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

• query throughput
• Query delays and errors
• Client connection and errors
• buffer pool utilization

For MySQL performance monitoring, the official also provides related service plug-ins: MySQL-Percona, the following is a brief introduction to the installation of plug-ins

[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/percona-zabbix-templates-1.1.8-1.noarch.rpm
[root@db01 ~]# 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, you can cooperate with other monitoring tools to monitor the performance of MySQL.

MySQL server configuration plugin:

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

• Modify MySQL sock file path

  [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.php

  Test for availability (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 gg
  gg:12
  
  # 确保当前文件的 属主 属组 是zabbix，否则zabbix监控取值错误。
  [root@db01 ~]# 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 the zabbix-agent configuration file to the /etc/zabbix/zabbix_agentd.d/ directory

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

  Import and configure Zabbix templates and hosts:

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

# 如果要修改监控获取值的时间不但要在zabbix面板修改取值时间，bash脚本也要修改。
[root@db01 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   
# 这个 300 代表 300s 同时也要修改。

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

Zabbix comes with a template to monitor MySQL service

In fact, in the actual production process, there are still related third-party open source software for professional monitoring databases. Brother migrant workers have written related articles before, and they are posted today for your reference: powerful open source enterprise-level database monitoring tool Lepus

MySQL Administration Tools

MySQL is one of the most widely used and popular open source databases, and there are many tools around it that make the process of designing, creating, and managing databases easier and more convenient. But how to choose the tool that best suits your needs is not easy. Here is recommended for everyone: 10 MySQL GUI tools , they are a good solution for developers and DBAs.

A long time ago, the migrant worker introduced an open source SQL management tool to you: auto-completion and rollback! Introduce a visual sql diagnostic tool .

Today, the migrant worker will recommend another SQL audit tool: MySQL automated operation and maintenance tool goinception .

Visual management tool, you can try this: introduces a free and easy-to-use visual database management tool

As the saying goes, if you want to do good work, you must first sharpen your tools. Regular physical examination of your MYSQL database is an important means to ensure the safe operation of the database, because good tools can double your work efficiency!

Today, I will share with you several mysql optimization tools. You can use them to perform a physical examination on your mysql and generate awr reports, allowing you to grasp the performance of your database as a whole.

Performance optimization diagnostic tools: Don't underestimate these tools! Database can help you quickly resolve critical bottlenecks

MySQL common error code description

Let me show you some examples of error analysis and solutions.

• 1.ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/mysql.sock'

Problem Analysis: It may be that the database is not started or the port is prohibited by the firewall.

Solution: Start the database or open the database listening port on the firewall.

• 2.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Problem Analysis: The password is incorrect or there is no permission to access.

Solution:

1) Modify the my.cnf main configuration file, add skip-grant-tables under [mysqld], and restart the database. The last command to change the password is as follows:

mysql> use mysql;
mysql> update user set password=password("123456") where user="root";

Then delete the skip-grant-tables parameter just added, restart the database, and log in with the new password.

2) Re-authorize, the command is as follows:

mysql> grant all on *.* to 'root'@'mysql-server' identified by '123456';

• 3. The client reports Too many connections

Problem analysis: The number of connections exceeds the maximum connection limit of Mysql.

Solution:

• 1. Increase the number of connections in the my.cnf configuration file, and then restart the MySQL service. max_connections = 10000

• 2. Temporarily modify the maximum number of connections, which will not take effect after restarting. You need to modify the configuration file in my.cnf, and it will take effect next restart.

  set GLOBAL max_connections=10000;

• 4.Warning: World-writable config file '/etc/my.cnf' is ignored ERROR! MySQL is running but PID file could not be found

Problem analysis: MySQL configuration file /etc/my.cnf permissions are incorrect.

Solution:

chmod 644 /et/my.cnf

• 5.InnoDB: Error: page 14178 log sequence number 29455369832 InnoDB: is in the future! Current system log sequence number 29455369832

Problem analysis: The innodb data file is damaged.

Solution: Modify the my.cnf configuration file, add [mysqld] innodb_force_recovery=4 , back up the data file after starting the database, then remove this parameter, and use the backup file to restore the data.

• 6. Slave_IO_Running of the slave library is NO

Problem analysis: The server-id values of the master library and the slave library are the same.

Solution: Modify the server-id value of the slave library to be different from the master library and lower than the master library. Restart after modification, and then sync!

• 7. Slave_IO_Running from the library is NO problem

Problem analysis: There are many reasons for the slave library thread to be NO. The main reason is that the primary key conflict or the master library deletes or updates data, the slave library cannot find records, and the data is modified. Usually the status code error is 1007, 1032, 1062, 1452 and so on.

Solution one:

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

Solution 2: Set user permissions and set read-only permissions from the library

set global read_only=true;
8.Error initializing relay log position: I/O error reading the header from the binary log

Analyzing the problem: the relay log relay-bin from the library is broken.
Solution: Repair manually, re-find the synchronized binlog and pos points, and then re-synchronize.

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.xxx',MASTER_LOG_POS=xxx; 

The operation and maintenance or DBA who has maintained MySQL knows that there are some codes similar to 10xx in some error messages that are often encountered.

Replicate_Wild_Ignore_Table:
         Last_Errno: 1032
         Last_Error: Could not execute Update_rows event on table xuanzhi.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000004, end_log_pos 3704

However, if you don't delve into it or have encountered it before, it's really not clear what the specific meaning of these codes is? This also caused some obstacles to our troubleshooting.

Therefore, today, the migrant worker brother has sorted out some common error codes in the master-slave synchronization process, and its specific descriptions have been sorted out for you: recommended to save it for future reference! MySQL common error code description

MySQL development specifications and usage skills

Naming conventions

• 1. The library name, table name and field name must use lowercase letters and be separated by underscores.

  • a) MySQL has the configuration parameter lower_case_table_names, which cannot be changed dynamically. The Linux system defaults to 0, that is, the library table name is stored according to the actual situation and is case-sensitive. If it is 1, it is stored in lowercase, case-insensitive. If it is 2, store as it is, but compare in lowercase.
  • b) If the uppercase and lowercase are mixed, there may be multiple tables such as abc, Abc, ABC, etc., which can easily lead to confusion.
  • c) The field name is case-sensitive, but the actual use is not. That is, it is not possible to create two fields with the same name but different capitalization.
  • d) In order to unify the specification, lowercase letters are used for the library name, table name and field name.

• 2. The library name, table name and field name must not exceed 32 characters.

  • The library name, table name, and field name support up to 64 characters, but for the sake of uniform specification, easy identification, and reduction of transmission volume, more than 32 characters are prohibited.

• 3. Use the INNODB storage engine.

  • The INNODB engine is the default engine after MySQL version 5.5. It supports transactions and row-level locks. It has better data recovery capabilities and better concurrent performance. At the same time, it has better support for multi-core, large memory, SSD and other hardware, and supports data Hot backup, etc., so INNODB has obvious advantages over MyISAM.

• 4. The use of MySQL reserved words is prohibited for library names, table names, and field names.

  • When attributes such as library name, table name, and field name contain reserved words, the SQL statement must use backticks to quote the attribute name, which makes the writing of SQL statements and the escaping of variables in SHELL scripts very complicated.

• 5. The use of partition tables is prohibited.

  • Partitioned tables have strict requirements on the partition key; when the partitioned table becomes larger, it becomes more difficult to perform DDL, SHARDING, and single-table recovery. Therefore, the use of partitioned tables is prohibited, and manual SHARDING on the business side is recommended.

• 6. It is recommended to use UNSIGNED to store non-negative values.

  • For the same number of bytes, the non-negative storage value range is larger. For example, the signed value of TINYINT is -128-127, and the unsigned value is 0-255.

• 7. It is recommended to use INT UNSIGNED to store IPV4.

  • Using UNSINGED INT to store the IP address occupies 4 bytes, and CHAR(15) occupies 15 bytes. Also, computers process integer types faster than string types. Use INT UNSIGNED instead of CHAR(15) to store IPV4 addresses, and use the MySQL functions inet_ntoa and inet_aton to convert. There is currently no conversion function for IPv6 addresses, and it needs to be stored using DECIMAL or two BIGINTs.

For example:

SELECT INET_ATON('209.207.224.40'); 3520061480SELECT INET_NTOA(3520061480);
209.207.224.40

• 8. It is strongly recommended to use TINYINT instead of ENUM type.

  • When the ENUM type needs to modify or increase the enumeration value, online DDL is required, and the cost is high; if the ENUM column value contains a numeric type, the default value may be confused.

• 9. Use VARBINARY to store case-sensitive variable-length strings or binary content.

  • VARBINARY is case-sensitive by default, has no character set concept, and is fast.

• 10. The INT type occupies 4 bytes of storage

  • For example, INT(4) only means that the display character width is 4 bits, not the storage length. The number after the parentheses of the numeric type only indicates the width and has nothing to do with the storage range. For example, INT(3) displays 3 digits by default, fills in spaces, and displays it normally when it exceeds. Python, Java clients, etc. do not have this function.

• 11. Distinguish the use of DATETIME and TIMESTAMP.

  • Use the YEAR type to store the year. Store dates using the DATE type. The storage time (accurate to the second) is recommended to use the TIMESTAMP type.
  • Both DATETIME and TIMESTAMP are accurate to the second, and TIMESTAMP is preferred because TIMESTAMP has only 4 bytes, while DATETIME is 8 bytes. At the same time, TIMESTAMP has the characteristics of automatic assignment and automatic update. Note: In version 5.5 and earlier, if there are multiple timestamp columns in a table, at most one column can have automatic update function.

How to use the auto-assignment property of TIMESTAMP?

a)自动初始化，而且自动更新：
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

b)只是自动初始化：
column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

c)自动更新，初始化的值为0：
column1 TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP

d)初始化的值为0：
column1 TIMESTAMP DEFAULT 0

• 12. Index fields are defined as NOT NULL.

  • a) For each row of the table, each NULL column requires additional space to identify.
  • b) B-tree indexes do not store NULL values, so if the index field can be NULL, the index efficiency will drop.
  • c) It is recommended to replace the NULL value with 0, a special value or an empty string.

For details, please refer to the following articles

• worth collecting: a very complete and detailed MySQL specification
• MySQL development specifications and usage skills

MySQL high-frequency enterprise interview questions

If you learn the knowledge well, of course you have to go for an interview, enter a big factory, and get a high salary. But before entering the interview, the necessary preparations are necessary, and brushing the questions is one of them.

Linux operation and maintenance must meet (1)

Linux operation and maintenance must meet (2)

Linux operation and maintenance must meet (3)

Linux operation and maintenance must meet (four)

The main audience of the following content is developers, so it does not involve MySQL service deployment and other operations, and there is a lot of content, everyone is ready to be patient and melon seed mineral water.

I studied MySQL systematically a while ago, and I also have some practical experience. I happened to see an interview article related to MySQL, and found that some of the questions could not be answered by myself. Although most of the knowledge points are known, but the knowledge cannot be transferred. concatenate.

Therefore, I decided to do a MySQL Soul 100 Question, and try to answer the questions to make my understanding of the knowledge points deeper.

This article will not explain mysql from the usage of select in detail, mainly aiming at some MySQL knowledge points that developers need to know, mainly including indexes, transactions, optimization, etc., in the form of high-frequency questions in interviews Give the answer.

• MySQL high-frequency
• most complete big factory Mysql
• MySQL database interview questions (2021 latest version)

MySQL user behavior security

• Suppose you are a mysql-DBA of a company, and suddenly all the data in the company database are deleted artificially one day.
• Although there are data backups, the loss caused by the service outage is tens of millions, and now the company needs to find out who did the deletion.
• However, there are many people with database operation permissions. How to investigate and where is the evidence?
• Do you feel powerless?
• MySQL itself does not have the function of operating audit, does that mean that you can only consider yourself unlucky in this situation?

technology road of migrant workers updates the MySQL technical knowledge system from time to time. You can follow me and check 161e4e9cd1d616 MySQL technical column learn more MySQL knowledge.

You are also welcome to like, forward and support. Due to the limited level, please leave a message to correct me if there are any mistakes, I am grateful! ! !