A summary of the latest, most complete, and most detailed MySQL database study notes on the entire network (the latest version of 2021)

What is a database

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

Importance of DBMS

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

Types of DBMS

• Hierarchical database

  • One of the oldest databases, because of outstanding shortcomings, so rarely used

• Relational Database

  • Use row and column two-dimensional table structure to manage the database, similar to Excel structure, use special SQL language to control data.

• Common types of relational database management systems

  • Oracle ==> Oracle
  • SQL Servce ==> 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 the 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 changes to the data in the database
  • grant ==> grant user operation permissions
  • revoke ==> Cancel the user's operation authority

Basic writing rules of SQL

• The SQL statement should end with;
• Keyword is not case sensitive, but the data in the table is case sensitive
• Keyword capitalization
• Capitalize the first letter of the table name
• List etc. lowercase
• The notation of constants is fixed
• Need to use when encountering string, date and other types''
• Use spaces to separate words
• Naming rules
• The names of databases and tables can use English, data and underscores
• The name must start with English
• The name cannot be repeated

type of data

• integer

  • Number type, but cannot store decimals

• char

  • Fixed-length string type, specify the maximum length, and use spaces to fill in the shortest

• varchar

  • Variable-length string type, specify the maximum length, but do not fill if it is insufficient

• data

  • Store date, year/month/day

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

Introduction to MySQL database

MySQL is the most popular relational database management system. In terms of web applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the Oracle company. MySQL is a relational database management system. The relational database stores data in different tables instead of putting all the data in a large warehouse, which increases speed and flexibility.

• MySQL is open source and is currently part of Oracle's products.
• MySQL supports large databases. It can handle large databases with tens of millions of records.
• MySQL uses a standard SQL data language format.
• MySQL can run on multiple systems and supports multiple languages. These programming languages include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, Tcl, etc.
• MySQL has very good support for PHP, which is currently the most popular web development language.
• MySQL supports large databases, a data warehouse with 50 million records, a 32-bit system table file can support a maximum of 4GB, and a 64-bit system supports a maximum table file of 8TB.
• MySQL can be customized, using the GPL agreement, you can modify the source code to develop your own MySQL system.

In daily work and learning, whether it is development, operation and maintenance, or testing, the learning of the database is inevitable, and it is also one of the necessary technologies for daily work. In Internet companies, there are more open source product lines, and the most important database used by Internet companies is MySQL. For more information about MySQL database, interested readers can refer to the official website documents and this article: may be the best important MySQL knowledge point on the entire network

MySQL installation

The official version of MySQL 8 8.0.11 has been released. The official said that MySQL 8 is 2 times faster than MySQL 5.7, and it also brings a lot of improvements and faster performance! Who is the best? Please see: MySQL 5.7 vs 8.0, which one has better performance?

For detailed installation steps, please refer to: MySQL 8.0 installation and deployment under CentOS, super detailed!

MySQL basic entry operation

Windows service

-- 启动MySQL
net start mysql

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

Connect and disconnect the server

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

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

Database operation

-- 查看当前数据库
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 operation

-- 创建表
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 basic knowledge points of operation, please refer to the following articles:

• Getting started with MySQL database --- common basic commands
• 1047 rows of MySQL detailed study notes (worth learning and collecting)
• Introduction to Common Commands for Getting Started with MySQL

MySQL multi-instance configuration

Getting started with MySQL database-multi-instance configuration

MySQL master-slave synchronous replication

There are two methods for MySQL replication:

• Traditional way: Based on the bin-log of the master library, the 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 rely on the location of the log file, and at the same time, it can better ensure the consistency of the master and slave database data.

MySQL database master-slave synchronization actual combat process

The "pits" you don't know about the MySQL master-slave synchronization architecture (top)

The "pits" you don't know about in the MySQL master-slave synchronization architecture (below)

There are many types of MySQL replication:

• Asynchronous replication: A master library, one or more slave libraries, and the 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 committing any transaction on the main database.
• Delayed replication: On the basis of asynchronous replication, artificially set the data synchronization delay time of the master library and the slave library, that is, 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

Multiple ways of data backup:

• Physical backup refers to the way to complete the backup by copying database files. This backup method is suitable for databases that are large, important and need to be quickly restored.
• 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 modifications to the exported files, or if you want to re-establish the database on a different type of server
• Normally, the speed of physical backup is faster than logical backup. In addition, the granularity range of backup and recovery 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 and can be restored 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 backup is usually performed when the database is closed, but if it is performed when the database is running, the database must not be modified during the backup.
• The speed of logical backup is slower than 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 physical backup; in addition, logical backup does not include database configuration File and log file content; 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 program: Highly compelling enterprise-level MySQL database backup program
• MySQL database physical backup method: Xtrabackup realizes data backup and recovery

MySQL high-availability architecture design and actual combat

Let's first understand the introduction of the MySQL high-availability architecture: Talking about the MySQL cluster high-availability architecture

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

officials also provide a high-availability solution: official tools | 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 a separate machine to manage multiple master-slave clusters, or it can be deployed on a slave node.
• MHA Node: Run on every MySQL server.
• MHA Manager will periodically detect the master node in the cluster. When the master fails, it can automatically promote the slave with the latest data to the new master, and then redirect all other slaves to the new master. The entire failover process is completely transparent to the application.

MHA high-availability program combat: MySQL cluster high-availability architecture MHA

MGR

• Mysql Group Replication (MGR) is a brand new highly available and highly expanded 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 through plug-ins;
• High fault tolerance, most services can continue to work normally, automatic detection of resource requisition conflicts between different nodes, prioritized processing in order, built-in anti-brain 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 performed on the master; in the multi-master mode, all servers are updated at the same time.

MySQL database read and write separation and high availability

The storage and access of massive data has become a bottleneck problem in the system design. The increasing 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. Accordingly, the overhead of data operations will also increase; in addition, no matter how the hardware is upgraded Resources, the resources of a single server (CPU, disk, memory, network IO, number of transactions, number of connections) are always limited, and ultimately the amount of data and data processing capabilities that the database can carry will encounter bottlenecks. Sub-table, sub-database and read-write separation can effectively reduce the pressure of a single database.

Practical case of MySQL read-write separation high-availability architecture:

ProxySQL+Mysql realizes the separation of database reading and writing

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

MySQL performance optimization

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

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. B-Tree usually means that all values are stored in order, and the distance from each leaf page to the root is the same.
• 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. Both the root node and the intermediate node store pointers to lower-level nodes. By comparing the value of the node page and the search The value can find the appropriate pointer to enter the lower-level child nodes until the lowest-level leaf node. The final result is either to find the corresponding value or not find the corresponding value. The depth of the entire B-tree tree is directly related to the size of the table.
• Full key-value matching: match all columns in the index, such as finding a person whose name is zhang san, who was born on January 1, 1982
• Match the leftmost prefix: match with the leftmost column in the index, for example, find all people whose last name is zhang
• Match column prefix: match the beginning of the leftmost column of the index, such as finding all people whose names start with z
• Matching range value: match the range area value of the index column, such as finding people whose last name is between li and wang
• Exact match on the left column and range match on the right column: For example, find all people whose last name is Zhang and whose first name starts with K
• Only access index query: query results can be obtained through the index, also called a covering index, such as finding the names of all people whose surname is zhang

MySQL commonly used 30 kinds of SQL query optimization methods | MySQL is too slow? Try these diagnostic ideas and tools

MySQL table partitioning: A thorough understanding of MySQL partitioning

• You can allow more data to be stored in one table, breaking through disk restrictions or file system restrictions.
• It is easy to remove the 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 more table partitions to optimize the efficiency of statement execution. 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 according to a certain rule, and store them separately. This rule is called a partition function, and there can be different partitioning rules.
• In MySQL 5.7 version, you can use the show plugins statement to check whether MySQL supports table partitioning.
• The MySQL8.0 version removes the display of partition in show plugins, but the table partition function in the community version is enabled by default.
• But when the table contains a primary key or unique key, each field used as a partition function must be all or part of the unique key and primary key in the table, otherwise the partition table cannot be created.

MySQL sub-database sub-table

• If there is no division, no division. Tables within 10 million are not recommended to be fragmented. Through appropriate indexing, read-write separation, etc., performance problems can be solved well.
• The number of shards should be as small as possible, and the shards should be evenly distributed across multiple DataHosts, 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.
• Fragmentation rules need to be carefully selected. The selection of fragmentation rules needs to consider data growth mode, data access mode, fragmentation relevance issues, and fragmentation expansion issues. The most recent fragmentation strategy is range fragmentation and enumeration fragmentation. Slices, consistent Hash shards, these types of shards are conducive to expansion.
• Try not to span multiple shards in SQL in a transaction. Distributed transactions have always been a difficult problem to handle.
• Optimize the query conditions as much as possible, try to avoid the Select * method, a large number of data result sets will consume a lot of bandwidth and CPU resources, query 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 program: MySQL sub-database sub-table program, the summary is very good!

Mysql sub-database sub-table ideas: rescue DBA-database sub-database sub-table ideas 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 error
• Buffer pool utilization

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

[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 realize the performance monitoring of MySQL.

MySQL server configuration plugin:

• Modify the php script to connect to the monitor@localhost user of MySQL
• Modify the 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 whether it is available (the monitoring value 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 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

# 如果要修改监控获取值的时间不但要在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 the MySQL service

In fact, in the actual production process, there are still related third-party open source software for professional monitoring databases. The migrant worker has also written related articles before, and I sent it out for your reference today: Powerful open source enterprise database monitoring tool Lepus

MySQL management tools

MySQL is one of the most widely used and popular open source databases. There are many tools around it that can 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 a recommendation for everyone: 10 MySQL GUI tools , they are good solutions for developers and DBAs.

A long time ago, the migrant worker brother introduced an open source SQL management tool: automatic completion and rollback! Introduce a visual SQL diagnostic tool .

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

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

MySQL common error code description

First, I will show you the error analysis and solutions of a few examples.

• 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 blocked 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 you do not have permission to access.

Solution:

1) Modify the main configuration file of my.cnf, add skip-grant-tables under [mysqld], and restart the database. The last modification password command 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, and it will not take effect after restarting. You need to modify the configuration file in my.cnf, and it will take effect next time you 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 has incorrect permissions.

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: Innodb data file is damaged.

Solution: Modify the my.cnf configuration file, add innodb_force_recovery=4 under [mysqld], 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 from the library is NO

Problem analysis: The server-id value of the main library and the slave library are the same.

Solution: Modify the server-id value of the slave library to be different from the main library and lower than the main library. Restart after modification and synchronize again!

• 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 conflicts or the master database deletes or updates data, the slave database cannot find the record, and the data is modified. Usually the status code error is 1007, 1032, 1062, 1452, etc.

Solution one:

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

Solution 2: Set user permissions, 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

Analyze the problem: the relay-bin of the relay log from the library is damaged.
Solution: Manually repair, find the synchronized binlog and pos points again, and then re-synchronize.

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

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

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 is really not clear, what is the specific meaning of these codes? This has also caused a certain obstacle to our troubleshooting.

Therefore, today, migrant workers have sorted out some common error codes during the master-slave synchronization process, and their specific instructions are sorted out: recommended to collect 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 a configuration parameter lower_case_table_names, which cannot be changed dynamically. The Linux system defaults to 0, that is, the library table names are stored in actual conditions and are case sensitive. If it is 1, it is stored in lowercase and is not case sensitive. If it is 2, it is stored in the actual situation, but compared in lowercase.
  • b) If the case is mixed, there may be multiple tables such as abc, Abc, ABC, etc., which may cause confusion.
  • c) The field name display 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, use lowercase letters for library names, table names, and field names.

• 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 in order to unify the standard, easy to identify and reduce the amount of transmission, it is forbidden to exceed 32 characters.

• 3. Use the INNODB storage engine.

  • The INNODB engine is the default engine after MySQL 5.5. It supports transactions and row-level locks, has better data recovery capabilities, better concurrency performance, and 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. It is forbidden to use MySQL reserved words for library names, table names, and field names.

  • When attributes such as library names, table names, field names, etc. contain reserved words, SQL statements must quote the attribute names with backquotes. This makes the writing of SQL statements and the escaping of variables in SHELL scripts very complicated.

• 5. The use of partition tables is prohibited.

  • Partition tables have strict requirements on partition keys; after the partition table becomes larger, it becomes more difficult to perform DDL, SHARDING, and single-table recovery. Therefore, it is forbidden to use the partition table, and it is recommended that the business end manually SHARDING.

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

  • For the same number of bytes, the value range of non-negative storage is larger. For example, TINYINT has a sign of -128-127, and an unsigned value of 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. In addition, the computer processes the integer type faster than the string type. Use INT UNSIGNED instead of CHAR(15) to store the IPV4 address, and convert it through the MySQL functions inet_ntoa and inet_aton. There is currently no conversion function for IPv6 addresses, and DECIMAL or two BIGINTs are needed to store them.

E.g:

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, which is costly; if the ENUM column value contains a numeric type, it may cause confusion in the default value.

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

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

• 10. INT type fixed 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, with spaces filled in, and displays normally when exceeded. Python, Java clients, etc. do not have this function.

• 11. Differentiate the use of DATETIME and TIMESTAMP.

  • The storage year uses the YEAR type. Use the DATE type to store the date. 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, and DATETIME has 8 bytes. At the same time, TIMESTAMP has the characteristics of automatic assignment and automatic update. Note: In 5.5 and earlier versions, if there are multiple timestamp columns in a table, only one column can have the automatic update function.

How to use the automatic assignment attribute 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) The B-tree index does not store NULL values, so if the index field can be NULL, the index efficiency will decrease.
  • c) It is recommended to replace NULL values with 0, special values or empty strings.

For details, please refer to the following article

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

MySQL high-frequency enterprise interview questions

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

Linux operation and maintenance must meet 100 MySql

Linux operation and maintenance must meet (2)

Linux operation and maintenance must meet (3)

Linux operation and maintenance must meet (4)

The following content is mainly intended for developers, so it does not involve MySQL service deployment and other operations, and the content is more, 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 accidentally saw 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, it is impossible to understand the knowledge. Connect in series.

Therefore, I decided to engage in a MySQL Soul 100 Questions, 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. It mainly focuses on some MySQL knowledge points that developers need to know, mainly including indexes, transactions, optimization, etc., in the form of high-frequency questions in the interview. Give the answer.

MySQL high-frequency

questions in history are here 16084e2417f0b9

MySQL user behavior safety

• Suppose that you are a mysql-DBA of a certain company. Suddenly all the data in the company's database was deleted artificially one day.
• Although there is data backup, the loss caused by the service suspension is tens of millions. Now the company needs to find out who did the delete operation.
• However, there are many people who have database operation permissions. How to investigate and where is the evidence?
• Do you feel powerless?
• Mysql itself does not have the function of operation audit. Does that mean that you can only admit that you are unlucky when you encounter this situation?