lottery tutorial and The latest plan
Introduction and Installation of MySQL Database
Official Definition of MySQL DBA Work Content
Operations DBA
Basic: Installation and Construction on Various Versions and Platforms, Upgrade
Intermediate: Architecture Principles, Basic Management (Startup and Shutdown, Initialization Configuration File Management, Multi-instance Management, User Permission Management, Basic SQL (Create, Delete, Update, Query)),
Log Management, Backup and Recovery, Master-Slave Replication (Building, Status Monitoring)
Advanced: High Availability (MGR, InnoDB Cluster), High Performance (Optimization)
Development DBA
Understand at least one development language: JAVA, Python
In-depth Study of Basic SQL Statements (Create, Delete, Update, Query), Database Structure Design (Modeling)
Advanced SQL: Stored Procedures, Functions, Triggers, Views, Events
What is Data
Consider: Account passwords, images, videos, text, links, etc.
Computer: Binary Data
How Data is Stored
– Use dedicated software to convert data to binary and store it at the specified location on the computer
Considerations: Security, Storage Limits
Database Management System
EDBMS — Relational Database (Important Information): Oracle, MySQL, MSSQL, PG
NoSQL — Non-relational Database (Not as Important) a. EDBMS Relational Database: MongoDB, ES, Redis
MySQL Product Line
MySQL Manufacturers: Oracle, MariaDB, Percona
Oracle MySQL Enterprise Edition Selection: 5.6 (5.6.36)/ 5.7 / 8.0
MySQL Installation Methods (Linux)
Universal binary version: extract and use
rpm, yum version: download rpm package or configure yum source
Source code package: compile and install, very slow, study MySQL source code
MySQL 5.7.28 binary package installation
Environment preparation
Prepare virtual machine;
IP:
CPU:
Clean up historical environment
Create user and group
root@howell-PC:/home/howell/Downloads# useradd mysql -s /sbin/nologin
Create software directory
root@howell-PC:/home/howell/Downloads# mkdir -p /data/3306/
root@howell-PC:/home/howell/Downloads# mkdir -p /binlog/3306
root@howell-PC:/home/howell/Downloads# mkdir -p /app/database/
Set permissions
root@howell-PC:/home/howell/Downloads# chown -R mysql.mysql /app/ /data/ /binlog/
Upload and extract the MySQL software
root@howell-PC:/app/database# tar xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
Create a software link: ln -s mysql-5.7.28-linux-glibc2.12-2-x86_64 mysql to set environment variables
Set environment variables
vim /etc/profile (i.e., modify the tec/profile file)
Add a line: export PATH=/app/database/mysql/bin:PATH
Apply configuration: source /etc/profile
Initialize the system library table
mysqld –initialize-insecure –user=mysql –basedir=/app/database/mysql –datadir=/data/3306/
Expansion
Mysqld –initialize
After the initial password is completed, there will be a 12-digit temporary password, but this password must be reset before using mysql;
mysqld –initialize-insecure
mysqld –initialize-insecure (5.7+)
Password is empty after initial completion;
Configuration file settings
cat > /etc/my.cnf <
A table is a segment;
MySQL Basic Management
User Management
User Role
Linux User: Log in to Linux system, manage system: files;
MySQL: Log in to MySQL database, manage MySQL objects: tables;
User Definition
Linux User: Username;
MySQL User: Username@‘Whitelist’;
Whitelist: Address list, allows login to MySQL through whitelist IP;
MySQL User
howell@‘localhost’：The howell user can log in to MySQL locally (socket);
howell@‘10.0.0.10’：The howell user can log in to MySQL remotely through 10.0.0.10;
howell@‘10.0.0.%’：The howell user can log in to the MySQL server remotely through 10.0.0.xx/24;
howell@‘10.0.0.5%’：The howell user can log in to the MySQL server remotely through 10.0.0.50-10.0.0.59;
howell@‘10.0.0.0/255.255.254.0’；
howell@’%’；
User Query
use mysql;
show tables;
desc user;
mysql> select user,host,authentication_string from mysql.user;
root@‘localhost’：Super Administrator User, can only log in through local socket;
Create User
mysql> create user howell@‘localhost’;
select user,host,authentication_string from mysql.user;# Create User and Set Password
Modify User
mysql> alter user howell@‘localhost’ identified by ‘zxcv’;# Modify User Password
Delete User
mysql> drop user howell2@‘192.168.1.5’;
mysql> drop user howell@‘localhost’;
Note: Before version 8.0, users and permissions could be established through the grant command; after version 8.0, users must be established first, followed by permissions;
Permission Management
Function
What management capabilities do users have for database objects?
The way privileges are expressed
Specific commands
mysql> show privileges;#Query all the commands that can be set in MySQL permissions
±————————±————————————–±——————————————————+
| Privilege | Context | Comment |
±————————±————————————–±——————————————————+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges – allow connect only |
±————————±————————————–±——————————————————+
Authorization and revocation of permissions operations
Syntax
Syntax before 8.0:
grant Permission on Object to User identified by ‘Password’; #Before 8.0, you can create a user and authorize at the same time
8.0 and later:
create user User identified by ‘Password’;
grant Permission 1, Permission 2 on Object to User; #In two steps, you need to create a user first and then authorize
Permissions: Choose from the table above
ALL: Administrators
Permission 1, Permission 2, …: Normal users
grant option: Authorize other users, add with grant option after it
Object: database or table
. :——-> chmod -R 755 /; All authorizations
howell.* :——–> All authorizations under the howell subdirectory
howell.t1 : ———-> Single table t1 in the howell database
howell.t1() : ————> Authorize a column of the t1 table under the howell database
Example of authorization
Example 1: Create and authorize the administrator user howellroot, who can log in and manage the database through the 192.168.1.5 network segment
mysql> grant all on . to howellroot@‘192.168.1.5’ identified by ‘zxcv’ with grant option;
select user,host,authentication_string from mysql.user;#View user
mysql> show grants for howellroot@‘192.168.1.5’;#Query the permissions of howellroot@’192.168.1.5’ user
mysql> select * from mysql.userG;#View user permissions
Example 2: User authorization
mysql> grant create,update,select,insert,delete on app.* to app@‘192.168.1.5’ identified by ‘zxcv’;
mysql> show grants for app@‘192.168.1.5’;#View permissions
Expansion
MySQL authorization table:
user —> .
db —-> app.*
tables_priv —-> app.t1
columns_priv column
Revoke permissions
Linux: chmod -R 644 /howell —–> chmod -R 755 /howell can modify permissions by repeated authorization;
MySQL: Permissions cannot be modified by repeated authorization, but only by revoking permissions;
mysql> revoke create on app.* from app@‘192.168.1.5’;#Revoke create permission for app@’192.168.1.5’ user
mysql> show grants for app@‘192.168.1.5’;#View permissions
Forgot the super administrator password, how to deal with it?
Close connection layer user authentication stage:
Skip authorization table: –skip-grant-tables
Skip TCP/IP connection: –skip-networking
Stop mysqld: etc/init.d/mysqld stop
Which mysqld_safe
This method allows anyone to log in, which is not secure: mysqld_safe –skip-grant-tables &
Stop the database: systemctl stop mysqld
Start in safe mode: mysqld_safe –skip-grant-tables –skip-networking &
Expansion: You can also use service mysqld start –skip-grant-tables –skip-networking & to start in safe mode
mysql # Log in to the database
flush privileges; Manually load the authorization table;
alter user root@‘localhost’ identified by ‘zxcv’; # Change password
service restart mysqld # Restart database to normal mode
MySQL connection management
MySQL built-in client program, MySQL remote client program, program links
MySQL built-in client program
(1) mysql
Parameter list:
-u Username
-p Password
-S Local socket file
-h Database IP address
-P Database port
-e Execute database commands without interaction
< Import SQL script Socket connection method: Precondition: The root@'localhost’ user must be authorized in the database mysql -uroot -pzxcv -S /tmp/mysql.sock # Most complete mysql -uroot -p -S /tmp/mysql.sock # Recommended method mysql -uroot -p TCP/IP connection method: Precondition: The remote connection user must be created in advance, for example howellwindows@‘192.168.1.5’ mysql -uhowellwindows -pzxcv -h 192.168.1.5 -P 3306 # Remote connection, default port 3306 if no port is specified Example 1: Authorize howell as a management user to log in via local socket mysql> grant all on . to howell@‘localhost’ identified by ‘zxcv’; # Create howell user
mysql> mysql -uhowell -p -S /tmp/mysql.sock # Test connection
Example 2: Authorize ychowell user to log in remotely via 192.168.1.%
mysql> grant all on . to ychowell@‘192.168.1.%’ identified by ‘zxcv’; # Create ychowell user to log in to mysql via 192.168.1.5
mysql> mysql -uychowell -p -h 192.168.1.6 -P 3306 # Log in to test
View the connected user and IP, who is the remote connection and who is the local connection
grant all on . to ychowell@‘192.168.1.%’ identified by ‘zxcv’;
±—±———±—————±—–±——–±—–±———±—————–+
| Id | User | Host | db | Command | Time | State | Info |
±—±———±—————±—–±——–±—–±———±—————–+
| 8 | ychowell | howell-l:50448 | NULL | Sleep | 276 | | NULL |
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
±—±———±—————±—–±——–±—–±———±—————–+
The use of -e to get values without interaction
mysql -uroot -pzxcv -e “select user,host from mysql.user;” # Get values directly without interaction
The use of < to import SQL scripts howell@howell-PC:~$ mysql -uroot -pzxcv < /root/world.sql MySQL Remote Client Program (Development Tools) Precondition: A user that can be remotely connected must be created in advance (such as: ychowell@‘192.168.1.%’) Program Connection yum install -y php-mysql pip3 install pymysql Initialization Configuration Method Source Code Installation: Set initialization parameters during the compilation process; Configuration Files: Set configuration file parameters /etc/my.cnf before the database starts; Command Line of Startup Script: For example, mysqld_safe --skip-grant-tables --skip-networking &. Application of Configuration Files (1) Order of Reading Configuration Files howell@howell-PC:~$ mysqld --help --verbose |grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf #mysqld will look for configuration files from these paths my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (2) Unexpected situations: Manually specify the location of the configuration file: /opt/my.cnf, /data/3306/my.cnf mysqld --default-file=/opt/my.cnf & #At startup mysqld_safe --default-file=/opt/my.cnf & (3) Format of configuration file [mysqld] #Server-side tags user=mysql #User responsible for database management basedir=/app/database/mysql #The installation location of the software datadir=/data/3306 #The storage location of the data server_id=6 #Unique identifier of the node (useful for master-slave) port=3306 #Port number socket=/tmp/mysql.sock #Socket file connects to the -S parameter in the database [mysql] #Client tags socket=/tmp/mysql.sock #The default location of the socket file read by MySQL during login [Tag] Configuration parameters What are tags? Tags are used to distinguish different program parameters; Server-side tags: responsible for setting the database server-side running parameters Client tags: affect client connections, only affect local client connections, do not affect remote client connections Server-side common tag types: [mysqld] [mysqld_safe] Client manufacturer love tag type: [mysql] [mysqldump] MySQL start and shutdown Start systemctl start mysqld --> mysql.server –> mysqld_safe –> mysqld
mysqld_safe and mysqld, can include their own executed parameters when starting the database, for example
–skip-grant-tables
–skip-networking
–default-file=/opt/my.cnf
Close
systemctl stop mysqld
service mysqld stop
/etc/init.d/mysqld stop
mysqladmin -uroot -pzxcv shutdown
mysql -uroot -pzxcv -e “shutdown”
Multiple instances of MySQL
Multiple instances of the same version
(1) Planning
Software 1 copy
Multiple configuration files: /data/3307/my.cnf /data/3308/my.cnf /data/3309/my.cnf
Multiple data directories — Initialize data multiple times: /data/330(7…9)
Multiple log directories: /binlog/330(7…9)
Multiple sockets: /tmp/mysql330(7…9).sock
Multiple ports: port=3307,3308,3309
Multiple server_ids: server_id=7,8,9
(2) Configuration process
1) Create the required directory
mkdir -p /data/330{7…9}/data # Create data directory
mkdir -p /binlog/330{7…9} # Create log directory
2) Create the configuration file
Order of reading the configuration file:
cat > /data/3307/my.cnf <
MySQL Server-side classified commands
mysql>help contents
DDL: Data Definition Language
DCL: Data Control Language
DML: Data Manipulation Language
DQL: Data Query Language
Various names in SQL
SQL_modes SQL modes
SQL_mode function: Standardizes the way SQL statements are written (such as division by zero, or 0 year 0 month 0 day), affecting the execution behavior of SQL
SQL_mode is a parameter
select @@sql_mode;
| @@sql_mode |
±——————————————————————————————————————————————+
| ONLY_FULL_GROUP_BY,
STRICT_TRANS_TABLES,
NO_ZERO_IN_DATE,
NO_ZERO_DATE,E
RROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,
NO_ENGINE_SUBSTITUTION |
±——————————————————————————————————————————————+
Character set (charset) and collation rules (collation)
Character set
show charset;
utf8: Maximum of 3 bytes per character
utf8mb4 (recommended): Maximum of 4 bytes per character
Difference: (1) utf8mb4 supports more encodings than utf8, for example, emoji characters are supported in mb4 but not in utf8, because emoji characters occupy 4 bytes per character;
create database zabbix charset utf8mb4;
show create database zabbix;
Collation rules (collation)
Each character set has multiple collation rules (sorting rules);
show collation;
Function: (1) Affects sorting operations, simply put, it is whether the case is sensitive;
#Case sensitive | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
#Case insensitive | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
Data types (data_type)
Numbers: integers, decimals
mysql> create database study;
mysql> use study;
mysql> create table t1(id int,name varchar(64),age tinyint);
tinyint1B=8 bitint4Bbigint8B
String Type
char(length): Fixed-length string type, up to 255 characters; varchar(length): Variable-length string type, up to 65535 characters; enum(‘bj’, ‘sh’): Enum type enum(‘sd’, ‘sdsd’, ‘sa’);
The difference between char and varchar, for example:
char(10): The maximum storage capacity is 10 characters. If the stored characters are less than 10, the remaining space is automatically filled with spaces, and the disk space will be fully occupied;
varchar(10): The maximum storage capacity is 10 characters, and storage space is allocated as needed;
Supplementary:
The varchar type first judges the character length and then allocates storage space reasonably when storing data, while the char type does not judge and allocates immediately. Therefore, char type is still chosen in fixed-length columns. Besides storing strings, the varchar type also uses an additional 1-2 bytes to store the character length.
Example: varchar(10)
asdf —> 1. Judge character length —> 2. Apply space —> 3. Store characters —> 4. Apply one byte to store the character length;
3. Application scenarios:
String fixed length, char type; not fixed, varchar type;
4. Issues with numbers in parentheses
The number in parentheses sets the number of characters, regardless of the character type.
However, different types of characters occupy different storage spaces.
For numbers and English characters, each character occupies 1 byte in length.
For Chinese characters, the space occupied should consider the character set, with UTF, UTF8mb4 each Chinese character occupying 3 bytes in length. Emoji characters occupy 4 bytes in length.
The total length cannot exceed the maximum length of the data type.
Easter egg: The selection of these two data types needs to be considered carefully for index application.
Time Type
DATATIME (commonly used): The range is from 1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999; it occupies 8 bytes in length.
TIMESTAMP (commonly used): The range is from 1970-01-01 00:00:00.000000 to 2038-01-19 03:14:07:999999; it occupies 4 bytes in length and is affected by the time zone.
Binary type
JSON data type
{
id: 101
name: ‘zhangsan’
}
Table attributes
Constraints
PK(Primary key): Primary key constraint; function: Unique + non-null; only one primary key per table, used as a clustered index.
not null: Non-null constraint; function: Must be non-null. It is recommended to set each column as non-null.
unique key: Unique constraint; function: Must be non-repetitive.
unsigned: For numeric columns, non-negative numbers.
Other attributes
default: Default value (e.g., non-null values fill in default values)
comment: Comments
auto_increment: Auto-increment
SQL applications
Client
List of all MySQL commands:
Note that all text commands must be first on line and end with ‘;’
? (?) Synonym for `help’.
clear (c) Clear the current input statement.
connect (
) Reconnect to the server. Optional arguments are db and host.
delimiter (d) Set statement delimiter.
edit (e) Edit command with $EDITOR.
ego (G) Send command to mysql server, display result vertically.
exit (q) Exit mysql. Same as quit.
go (g) Send command to mysql server.
help (h) Display this help.
nopager (
) Disable pager, print to stdout.
notee ( ) Don’t write into outfile.
pager (P) Set PAGER [to_pager]. Print the query results via PAGER.
print (p) Print current command.
prompt (R) Change your mysql prompt.
quit (q) Quit mysql.
rehash (#) Rebuild completion hash.
source (.) Execute an SQL script file. (Import SQL script) Takes a file name as an argument.
status (s) Get status information from the server.
system (!) Execute a system shell command (call system commands like linux).
tee (T) Set outfile [to_outfile]. Append everything into given outfile.
use (ଜe) another database. Takes database name as argument.
charset (C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (W) Show warnings after every statement.
nowarning (w) Don’t show warnings after every statement.
resetconnection(x) Clean session context.
Server
In Linux, everything is a command, everything is a file.
In MySQL, everything is SQL, everything is a table.
DDL: Data Definition Language
(1) Database definition: database name & database attributes
1) Create database definition
CREATE DATABASE howell CHARSET utf8mb4;
Standard:
1) Database name: lowercase, related to business, do not start with numbers, do not make the database name too long, and do not use reserved strings;
2. A character set must be specified
2) Query database definition
SHOW DATABASES;
SHOW CREATE DATABASE howell;
#3) Modify database definition
ALTER DATABASE howell CHARSET utf8;
Note: Changes should be made from small to large, A—–B; then B must be a strict superset of A;
#4) Delete the database: Note, this does not represent production operations
Note: No one except the administrator has the permission to delete the database.
DROP howell;
(2) Table definition
1) Create table
CREATE TABLE wp_user(
id INT NOT NULL AUTO_INCREMENT COMMENT ‘user sequence number’,
name VARCHAR(64) NOT NULL COMMENT ‘username’,
age TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT ‘age’,
gender CHAR(1) NOT NULL DEFAULT ‘F’ COMMENT ‘gender’,
cometime DATETIME NOT NULL COMMENT ‘registration time’,
shengfen ENUM(‘Beijing City’, ‘Shanghai City’, ‘Tianjin City’, ‘Chongqing City’) NOT NULL DEFAULT ‘Beijing City’ COMMENT ‘province’,
PRIMARY KEY (id)
)ENGINE=INNODB CHARSET=utf8mb4;
Table creation specifications:
i) Table name: lowercase (for compatibility issues across multiple platforms, Windows does not distinguish between uppercase and lowercase), cannot start with a number, the table name should be related to the business, the name should not be too long, and cannot use keywords;
ii) The table attributes must be set with storage engine and character set;
iii) Data type: appropriate, concise, sufficient;
iv) There must be a primary key;
v) Each column should be set as not null, if unsure, set a default value (default);
vi) Each column must have comments;
#2. Query the table
mysql> show tables;
mysql> desc wp_user;
mysql> show create table wp_user;
3) Modify the table
#1. Add phone number column;
mysql> alter table wp_user add column shouji bigint not null unique key comment ‘phone number’;
#2. Change the phone column data type to char(11)
mysql> alter table wp_user modify shouji char(11) not null unique key comment ‘phone number’;
#3. Delete the phone number column
mysql> alter table wp_user drop shouji;
4) Delete table
mysql> drop table wp_user;
Business process
(3) DDL supplement
1) Manually tear a table: Design a student table;
USE howell
CREATE TABLE howell.student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘Student ID’,
sname VARCHAR(64) NOT NULL COMMENT ‘Name’,
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘Age’,
gender TINYINT NOT NULL DEFAULT 2 COMMENT ‘0 represents female, 1 represents male, 2 represents unknown’,
addr ENUM(‘Beijing’, ‘Shanghai’, ‘Tianjin’, ‘Chongqing’) NOT NULL DEFAULT ‘Beijing’ COMMENT ‘Address’,
cometime DATETIME NOT NULL DEFAULT NOW() COMMENT ‘Admission time’,
telnum BIGINT NOT NULL UNIQUE KEY COMMENT ‘Phone number’
)ENGINE=INNODB CHARSET=utf8mb4;
2) The impact of online DDL operations (alter) on production
i) SQL audit platform: yearing, inception
ii) Explanation: In MySQL, DDL statements need to lock the ‘metadata table’ when operating on a table (at this time, all modification commands in the database cannot run normally), so when performing online DDL operations on large tables (business busy tables), be cautious and try to avoid the busy period.
iii) Expansion: What is metadata? Similar to Linux Inode information
iv) Key points for answering interview questions: 1. What does the SQL statement mean: The above four statements are for adding columns to 2 core business tables through DDL operations; 2. The impact of the above operations: When performing online DDL operations on large tables (business busy tables), try to avoid the busy period; 3. Our suggestion: Try to avoid the busy period, follow the process, and recommend using the pt-osc tool (pt-online-scheme-change) to reduce the impact of locking tables. If it is version 8.0, the tool can be omitted.
Expansion
The usage of the pt-osc tool
DCL: Data Control Language
grant command
revoke command
DML: Data Manipulation Language
Function: Operate on the rows in the table.
(1) Application of insert
–Application of INSERT
–Standard usage
DESC student;
INSERT INTO
student(id,sname,age,gender,addr,cometime,telnum)
VALUES(1,’张三’,18,1,’北京市’,’2020-02-16 11:50:00′,110);
–Simplified method
INSERT INTO
student
VALUES(2,’李四’,20,0,’重庆市’,’2020-08-26 12:56:00′,112);
–Partially enter data (such as containing default values and auto-increment)
INSERT INTO
student(sname,telnum)
VALUES(‘王五’,’119′);
–Batch entry method
INSERT INTO
student(sname,telnum)
VALUES(‘howell1′,’180’),(‘howell2′,’181’),(‘howell3′,’182′);
(2) Application of update
–Application of update
–Modify the value of the specified data row
Precondition: Need to specify which row to modify, generally, the update statement has a WHERE condition
UPDATE student SET sname=’miaomiao’ WHERE id=6;
(3) Application of delete
–Delete the value of the specified row
–Precondition: Need to specify which row to delete, generally, the delete statement has a WHERE condition
DELETE FROM student WHERE id=5;
–Expansion
–1. Fake deletion
–Delete the data row with id 1
–Original operation
DELETE FROM student WHERE id=1;
–Reason for deletion: Don’t want to query this data
–Modify the table structure, adding the state status column
ALTER TABLE student ADD COLUMN state TINYINT NOT NULL DEFAULT 1;
–Change the operation from delete to update
UPDATE student SET state=0 WHERE id=1;
–Modify the query statement
SELECT * FROM student WHERE state=1;
2. Interview question: What is the difference between ‘delete from student’, ‘drop table student’, and ‘truncate table student’?
Description: 1) The above three commands can all delete the student table
2) Differences:
delete from student: Logically, delete row by row, with many data rows, the operation is very slow
It is not truly deleted from the disk, but only marked at the storage layer, disk space is not released immediately, and the HWM high water mark will not decrease
drop table student: Physically delete the table structure (metadata) and data rows
truncate table student: Clear all data pages in the table segment, at the physical level
Deleting all table data will immediately release disk space, and the HWM high water mark will decrease.
Question: If deletedrop truncate is accidentally deleted, can it be restored?
Answer: Yes; Conventional methods: The above three problems can all be restored through backup + log, or through delayed library recovery
Flexible method: delete can pass, reverse log (binlog)
DQL: Data Query Language
(1) select: From the official perspective, it belongs to DML
1. Function: Get data rows from the table;
2.select used alone (MySQL exclusive)
1) select used with built-in functions (can use help to view)
–Display the current time
SELECT NOW();
–Display the current database being used
SELECT DATABASE();
–Command concatenation
SELECT CONCAT(“Hello World!”);
SELECT CONCAT(user,”@”,host) FROM mysql.user;
–View the current database version
SELECT VERSION();
–View the current logged-in user
SELECT USER();
2) Calculation
SELECT 10*10;
3) Query database parameters (MySQL specific)
SELECT @@PORT;
SELECT @@SOCKET;
SELECT @@DATADIR;
–View all parameters in MySQL
SHOW VARIABLES;
–fuzzy search method
SHOW VARIABLES LIKE ‘%trx%’;
3.select standard usage – used with other clauses
1) Single table
–default execution order
SELECT
1.FROM table 1, table 2…
2.WHERE filter condition 1, filter condition 2…
3.GROUP BY condition column 1, condition column 2…
3.5.SELECT LIST column name
4.HAVING filter condition 1, filter condition 2…
5.ORDER BY condition column 1, condition column 2…
6.LIMIT limit
–Prepare to learn database
mysql -uroot -p
–Example 6: Query cities in China or the United States
SELECT * FROM world.city
WHERE CountryCode=’CHN’ OR CountryCode=’USA’;
–Example 7: Query cities in China and the United States with a population over 5,000,000
SELECT * FROM world.city
WHERE CountryCode IN (‘CHN’, ‘USA’) AND Population>500000;
–2.4 WHERE combined with BETWEEN AND
–Example 8: Query cities with a population between 1 million and 2 million
SELECT * FROM world.city
WHERE Popualtion>=1000000 AND Population<=2000000; SELECT * FROM world.city WHERE Population BETWEEN 1000000 AND 2000000; –3. SELECT + FROM + WHERE + GROUP BY –GROUP BY combination –Aggregate functions (MAX(), MIN(), AVG(), COUNT(), SUM(), GROUP_CONCAT()) usage –GROUP_CONCAT: Convert column to row –Description: When GROUP BY is used, an aggregate function must be used together –Example 1: Count the number of cities in each country in the city table SELECT CountryCode, COUNT(ID) FROM world.city GROUP BY CountryCode; –Example 2: Count the number of cities in each province in China SELECT District, COUNT(ID) FROM world.city Online casino and The most exciting gameplay WHERE CountryCode='CHN' GROUP BY District; –Example 3: Count the total population of each country SELECT CountryCode, SUM(Population) FROM world.city GROUP BY CountryCode; –Example 4: Count the total population of each province in China SELECT District, SUM(Population) FROM world.city WHERE CountryCode='CHN' GROUP BY District; –Example 5: Count the total population and number of cities, as well as the list of city names for each province in China SELECT District, SUM(Population), COUNT(id), GROUP_CONCAT(Name) FROM world.city WHERE CountryCode='CHN' GROUP BY District; –Description: The columns in the select list must either be conditions for GROUP BY or appear in aggregate functions; otherwise, it will violate SQL_mode incompatibility –Principle: MySQL does not support it; the result set displays 1 row for multiple rows –4. HAVING statement –Function: Similar to the WHERE clause, HAVING is a post-filtering operation –Scenario: Use it after GROUP BY + aggregate functions when additional filtering is needed –Example 1: Count the total population of each province in China, only displaying information with a population greater than 5 million SELECT District, SUM(Population) FROM world.city WHERE CountryCode='CHN' GROUP BY District HAVING SUM(Popualtion)>5000000;
– 5. ORDER BY application
– Example 1: Count the total population of each province in China, only display information with a total population greater than 5 million, and sort by total population from high to low
SELECT District, SUM(Population) FROM world.city
WHERE CountryCode=’CHN’
GROUP BY District
HAVING SUM(Popualtion)>5000000
ORDER BY SUM(Popualtion) DESC;
– 6.0 LIMIT
– Function: Pagination display of result set
– Example 1: Count the total population of each province in China, only display information with a total population greater than 5 million, and sort by total population from high to low
– Only display the top five
SELECT District, SUM(Population) FROM world.city
WHERE CountryCode=’CHN’
GROUP BY District
HAVING SUM(Popualtion)>5000000
ORDER BY SUM(Population) DESC
LIMIT 5;
– Display 6-10 names
LIMIT 5,5;
LIMIT 5 OFFSET 5;
– Display 3-5 names
LIMIT 2,3;
LIMIT 3 OFFSET 2;
GROUP BY principle:
Homework 2: GROUP BY + Aggregate function usage method
Application scenario: When it is necessary to perform grouping and statistical calculations on a table according to different characteristics, the GROUP BY aggregate function is used; The core usage method of GROUP BY is:
1) According to the requirements, find the grouping condition;
2) According to the requirements, use the appropriate aggregate function;
– Practice
USE school;
score: grade table
sno: student number
cno: course number
score: grade
– Create project
DROP database SCHOOL;
CREATE DATABASE school CHARSET utf8mb4;
USE school;
CREATE TABLE student (
sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘student number’,
sname VARCHAR(20) NOT NULL COMMENT ‘name’,
sage TINYINT UNSIGNED NOT NULL COMMENT ‘age’,
ssex ENUM(‘f’, ‘m’) NOT NULL DEFAULT ‘m’ COMMENT ‘gender’,
)ENGINE=INNODB CHARSET=utf8mb4;
CREATE TABLE course (
cno INT NOT NULL PRIMARY KEY COMMENT ‘course number’,
cname VARCHAR(20) NOT NULL COMMENT ‘课程名字’
tno INT NOT NULL COMMENT ‘教师编号’
)ENGINE=INNODB CHARSET=utf8mb4;
CREATE TABLE sc(
sno INT NOT NULL COMMENT ‘学号’
cno INT NOT NULL COMMENT ‘课程编号’
score INT NOT NULL DEFAULT 0 COMMENT ‘成绩’
)ENGINE=INNODB CHARSET=utf8mb4;
CREATE TABLE teacher(
tno INT NOT NULL PRIMARY KEY COMMENT ‘教师编号’
tname VARCHAR(20) NOT NULL COMMENT ‘教师姓名’
)ENGINE=INNODB CHARSET=utf8mb4;
INSERT INTO student(sno,sname,sage,ssex)
VALUES(1,’hong1′,18,’m’),
(2,’hong2′,26,’f’),
(3,’hong3′,25,’m’),
(4,’hong4′,21,’m’),
(5,’hong5′,22,’m’),
(6,’miao1′,21,’m’),
(7,’miao2′,20,’m’),
(8,’miao3′,19,’f’),
(9,’miao4′,18,’f’),
(10,’miao5′,19,’m’);
INSERT INTO teacher(tno,tname)
VALUES(101,’sir1′),
(102,’sir2′),
(103,’sir3′);
INSERT INTO course(cno,cname,tno)
VALUES(1001,’linux’,101),
(1002,’python’,102),
(1003,’mysql’,103);
DESC sc;
INSERT INTO sc(sno,cno,score)
VALUES(1,1001,82),
(1,1002,59),
(2,1001,92),
(2,1003,90),
(3,1001,56),
(3,1002,65),
(4,1001,80),
(4,1002,86),
(4,1003,98),
(5,1003,89),
(6,1001,56),
(6,1002,89),
(7,1001,87),
(7,1003,65),
(8,1001,50),
(9,1003,89),
(10,1003,85);
–Multi-table join query
–Function: When our query requirements cannot be met by a single table, they come from multiple tables;
–Multi-table join query types: 1) Cartesian product; 2) Inner join; 3) Outer join;
–1. Cartesian product: Merge multiple tables into one large table
SELECT * FROM teacher, course;
SELECT * FROM teacher JOIN course;
–2. Inner join (most widely used): Take the data of the two tables with associated conditions, which is equivalent to the intersection
–Syntax: A JOIN B ON A.xx=B.xx
SELECT * FROM teacher JOIN course ON teacher.tno=course.tno;
–3. Outer join
–3.1 Left outer join LEFT JOIN: All data in the left table and data with associated conditions in the right table
SELECT * FROM teacher LEFT JOIN COURSE
SELECT city.name, country.name, city.population FROM city
LEFT JOIN country
ON city.countrycode=contry.code AND city.population<100 ORDER BY city.population DESC; –Simple understanding: Multi-table join actually merges the associated data of multiple tables into one table –Then perform WHERE GROUP BY HAVING ORDER BY LIMIT in the new table –Left outer join: left join –Multi-table join query example –Example 1: Query the country name, city name, population, and land area of wuhan; –First step：Find the associated tables：city: city name, city population; country: country name, land area country.surfacearea. –Second step：Find the associated conditions city.countrycode and contry.code –Third布：List other query conditions SELECT country.name, city.name, city.population, country.surfacearea FROM city JOIN country ON city.countrycode=country.code WHERE city.name='wuhan'; –Example 2: Count the number of courses that hong2 is studying; SELECT student.sno,student.sname,COUNT(sc.cno) FROM student JOIN sc ON student.sno=sc.sno WHERE student.sname=‘hong2’ GROUP BY student.sno,student.sname; –Example 3: Query the course names that hong2 is studying SELECT student.sno,student.sname,GROUP_CONCAT(course.cname) FROM student JOIN sc ON student.sno=sc.sno JOIN course ON sc.cno=course.cno WHERE student.sname=‘hong2’ GROUP BY student.sno,student.sname; –Example 4: Query the names of students taught by sir1 teacher SELECT concat(teacher.tname, "_", teacher.tno),group_concat(student.sname) FROM teacher JOIN course ON teacher.tno= course.tno JOIN sc ON course.cno = sc.cno JOIN student ON sc.sno = student.sno WHERE teacher.tname= “sir1” GROUP BY teacher.tname, teacher.tno; –Example 5: Query the average score of courses taught by sir1 SELECT concat(teacher.tname, "_", teacher.tno), avg(sc.score) FROM teacher JOIN course on teacher.tno= course.tno JOIN sc ON course.cno = sc.cno WHERE teacher.tname= ‘sir1’ GROUP BY teacher.tname, teacher.tno; –Example 6: Average score of courses taught by each teacher, sorted by average score; SELECT concat(teacher.tname, "_", teacher.tno), avg(sc.score) FROM teacher JOIN course on teacher.tno= course.tno JOIN sc ON course.cno = sc.cno GROUP BY teacher.tname, teacher.tno ORDER BY avg(sc.score) DESC; –Example 7: Query the names of students who failed under sir1 SELECT concat(teacher.tname, "_", teacher.tno), group_concat(concat(student.sname, ":", sc.score)) FROM teacher JOIN course ON teacher.tno= course.tno JOIN sc ON course.cno = sc.cno JOIN student ON sc.sno = student.sno WHERE teacher.tname= "sir1" AND sc.score< 60 GROUP BY teacher.tname, teacher.tno; –Example 8: Query information of students who failed under all teachers SELECT concat(teacher.tname, "_", teacher.tno), group_concat(concat(student.sname, ":", sc.score)) FROM teacher JOIN course ON teacher.tno= course.tno JOIN sc ON course.cno = sc.cno JOIN student ON sc.sno = student.sno WHERE sc.score< 60 GROUP BY teacher.tname, teacher.tno; –Example 9: Query the student numbers and average scores of students with an average score greater than 60; SELECT sc.sno, avg(sc.score) FROM sc GROUP BY sc.sno HAVING avg(sc.score)> 60;
–Example 10: Query the student numbers, names, number of courses taken, and total score of all students;
SELECT student.sname, student.sno, count(cno) as “Number of courses”, sum(sc.score) as “Total score”
FROM student
JOIN sc
ON student.sno= sc.sno
GROUP BY student.sname, student.sno;
–Example 11: Query the highest and lowest scores of each subject: Displayed in the following format: Course ID, Highest score, Lowest score
SELECT sc.cno, MAX(sc.score) as “Highest score”, MIN(sc.score) as “Lowest score”
FROM sc
GROUP BY sc.cno;
–Usage of CASE
SELECT CASE WHEN sc.score> 90 THEN 1 END
FROM sc;
– CASE syntax: Adding judgment function on the executing column
CASE WHEN 判断 THEN 结果 END
–Example 12: Calculate the pass rate for each teacher’s courses;
SELECT concat(teacher.tname, “_”, teacher.tno), course.cname, concat(count(case when sc.score> 60 then 1 end)/count(sc.sno)* 100, “%”) as “Pass rate”
FROM teacher
JOIN course
ON teacher.tno= course.tno
JOIN sc
ON course.cno = sc.cno
GROUP BY teacher.tname, teacher.tno, course.cname;
–Example 13: Query the number of students who have taken each course;
–Example 14: Query the student numbers and names of all students who have taken only one course;
–Example 15: Query information of students who have taken more than one course;
–Example 16: List of students for each course: Excellent (above 85), Good (70-85), Average (60-70), and Fail (below 60);
SELECT course.cname,
GROUP_CONCAT(CASE WHEN sc.score >= 85 THEN student.sname END) AS ‘Excellent’
GROUP_CONCAT(CASE WHEN sc.score >= 70 AND sc.score < 85 THEN student.sname END) AS 'Good' GROUP_CONCAT(CASE WHEN sc.score >= 60 AND sc.score < 70 THEN student.sname END) AS 'Average' GROUP_CONCAT(CASE WHEN sc.score < 60 THEN student.sname END) AS 'Failing' FROM course JOIN sc ON course.cno = sc.cno JOIN student ON sc.sno = student.sno GROUP BY course.cname; –Example 17: Query the fear (2) Outer join Function: Force the driving table SELECT city.name, city.population, country.name, country.surfacearea FROM city LEFT JOIN country ON city.CountryCode = country.Code –Here, the city table is equivalent to the driving table (equivalent to the outer loop in the for loop), and the country table is equivalent to the driven table What is the driving table? In multi-table joins, it plays the role of the outer loop in the for loop. At this time, MySQL will take each matching column value of the driving table and find the corresponding matching value in the inner loop of the for loop one by one for matching and judgment. Suggestion: It is more appropriate to set the table with a small result set as the driving table, which can reduce the number of next_loop iterations. For inner joins, we cannot control which table is the driving table; it is completely determined by the optimizer. If manual intervention is required, the inner join needs to be rewritten as an outer join. Suggestion: Query the number of students enrolled in each course The small table as the driving table reduces the number of next_loop iterations; 2. LEFT JOIN can force the left table to be the driving table; –For example SELECT city.name, city.population, country.name, country.surfacearea FROM city JOIN country ON city.CountryCode=country.Code WHERE city.name='wuhan'; –Rewritten as a forced LEFT JOIN on the driving table SELECT city.name, city.population, country.name, country.surfacearea FROM city LEFT JOIN country ON city.CountryCode=country.Code WHERE city.name='wuhan'; SELECT supplement (1) Column alias SELECT student.sno AS 'Student Number' FROM student; –Function 1. Can customize the displayed column names; 2. Can be called in HAVING, ORDER BY, and LIMIT statements; –Example SELECT district AS 'Province', SUM(population) AS 'Total Population' FROM world.city WHERE countrycode='CHN' GROUP BY district ORDER BY 'Total Population' DESC LIMIT 5 OFFSET 0; (2) Table alias SELECT student.sno FROM student AS s; –Function: Globally calls the alias defined (3) Application of DISTINCT SELECT countrycode FROM world.city; SELECT DISTINCT(countrycode) FROM world.city; (4) UNION and UNION ALL –Example: Query city information in China or the United States SELECT * FROM world.city WHERE countrycode='CHN' OR countrycode='USA'; SELECT * FROM world.city WHERE countrycode IN ('CHN', 'USA'); SELECT * FROM world.city WHERE countrycode='CHN' UNION ALL SELECT * FROM world.city WHERE countrycode='USA'; – –Interview question: Differences between UNION and UNION ALL –UNION: Aggregates two result sets, automatically removes duplicates; –UNION ALL: Aggregates two result sets without automatically removing duplicates; Introduction to the show statement SHOW DATABASES; #Query all databases SHOW TABLES; #Query all tables in the database currently in use SHOW TABLES FROM world; # Query all tables under the world database SHOW PROCESSLIST; # Query connection information of all users; SHOW FULL PROCESSLIST; # Query detailed connection information of all users –SQL part SHOW CHARSET; # View supported character sets SHOW COLLATION; # View supported collation rules SHOW ENGINES; # View supported engine information SHOW PRIVILEGE; # View supported permission information SHOW GRANTS FOR; # View the permissions of a user SHOW CREATE DATABASE: # View the database creation statement SHOW CREATE TABLE; # Query the table creation statement SHOW INDEX FROM; # View table index information SHOW ENGINE INNODB STATUS; # Query innodb engine status; SHOW STATUS; # View database status information; SHOW STATUS LIKE ‘%%’ # Fuzzy query database status; SHOW VARIABLES; # View all database parameters SHOW VARIABLES LIKE ‘%%’; # Fuzzy query all database parameters SHOW BINARY LOGS; # Query information of all binary log files SHOW BINLOG EVENTS IN; # Query binary log events SHOW MASTER STATUS; Query binary log position information SHOW SLAVE STATUS; # Query slave status information SHOW RELAYLOG EVENTS In; # Query relay log events 5. Metadata acquisition information_schema –Enterprise application –Example 1: Database asset statistics - Count the number of tables and table names under each database SELECT table_shema, COUNT(table_name), GROUP_CONCAT(name) FROM information_schema.tables GROUP BY table_schema; –Example 2: Calculate the total space occupied by each database in the database –The size of a table = AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH –The size of a table = DATA_LENGTH SELECT table_schema, SUM(AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH) FROM information_schema.tables GROUP BY table_schema; –Example 3: Query all non-InnoDB tables in the database (non-system database), and convert all non-InnoDB tables to InnoDB SELECT table_schema, table_name FROM information_schema.tables WHERE engine!= 'InnoDB' AND table_schema NOT IN ('information_schema', 'sys', 'mysql', 'performance_schema') echo "deb https://download.sublimetext.com/ apt/stable/" | sudo tee /etc/apt/sources.list.d/sublime-text.list -Example 4: Query all non-InnoDB tables in the database (non-system database), and convert all non-InnoDB tables to InnoDB SELECT CONCAT("ALTER TABLE ", table_schema, ".", table_name, " ENGIN= INNODB;") FROM information_schema.tables WHERE ENGIN!= "INNODB" AND table_schema not in ("sys", "perforamnce_schema", "information_schema", "mysql"); INTO OUTFILE "/tmp/alter.sql" –An error will occur here secure-file-priv option, security restrictions, MySQL does not allow data to be saved locally, you need to modify the configuration file [mysqld]; secure-file-priv=/tmp –Set the tmp folder as a secure folder [mysqld] secure-file-priv=/tmp user=mysql basedir=/app/database/mysql datadir=/data/3306 server_id=6 port=3306 socket=/tmp/mysql.sock [mysql] socket=/tmp/mysql.sock –Restart the database /etc/init.d/mysqld restart Chapter 5 Basic Optimization - Index and Execution Plan=================================================================================================================================================================== Introduction to MySQL index 1.1 What is an index? It is equivalent to the table of contents of a book, helping us find the page number of the required content; Indexes can help us quickly find the data page of the required row, playing an optimizing query role; 1.2 MySQL index types Btree index Rtree index HASH index Fulltext full-text index GIS geographic location index B+Tree structure 2.1 Introduction: Binary tree -----> Balanced binary tree —–> Balance Tree
2.2 Introduction of Btree search algorithm
2.3 Types of Btree
B-TREE
B+TREE: MySQL currently uses this search structure: bidirectional pointers are added to the leaf nodes (the pages where the data rows are located in the database), which is convenient for range search
B*TREE
MySQL B+TREE index construction process
3.1 BTREE structure of clustering index (Innodb exclusive)
3.1.1 Zone => Cluster: 64 pages (data pages) – 1M
Function:
1. After the clustering index is established, the data rows to be inserted in the future will be stored in order according to the ID value in the same area on the disk;
2. MySQL InnoDB table is a clustering index organized storage data table;
3.1.2 Precondition for construction:
1. When creating a table, if the primary key column is specified, MySQL Innodb will take the primary key as the clustering index column, such as ID not null primary key;
2. If the primary key is not specified, the table with unique key (unioque) column is automatically selected as the clustering index;
3. If none of the above, generate a hidden clustering index;
3.1.3 Construction process of clustering index, illustrated with a diagram;
3.2 BTREE structure of auxiliary index
3.2.1 Description
Indexes built with ordinary columns as conditions need to be manually created.
3.2.2 Function
Optimize query conditions outside of non-clustered index columns
ALTER TABLE t1 ADD INDEX IDX(name);
3,2,3 Construction process
1. Extract PK column and auxiliary index column (the difference from clustering index, clustering index takes the entire table);
2. Sort by auxiliary index column, then construct leaf nodes, and allocate storage space;
3. Construct the branch node column, allocate storage space;
4. Construct the root node;
5. According to non-clustered index query, obtain PK value, then perform table lookup, and then perform clustering index based on PK;
3.2.4 Subdivision of secondary indexes
Single-column index:
Multi-column: Composite index:
ALTER TABLE t1 ADD IDX(name, gender), only the most left column will be extracted at the node level;
Note: The most left principle, 1. The most left column must be included in the query; 2. When building indexes, choose the most left column with fewer repeated values;
For example: IDX(a,b,c): SELECT * FROM t1 WHERE a= and b= AND c= ;
Prefix index: For columns with excessively long index values, it can lead to an increase in the height of the index tree. MySQL suggests that the index tree height be 3-4 layers;
Building process: Select the front part of the large field as the index data item;
Factors affecting the height of B+TREE index tree
1. Index field is too long –> Prefix index;
2. Too many data rows –> Sharding –> Archive table (pt-arhive) –> Distributed architecture;
3. Data type –> Use appropriate data types;
Index management commands
5.1 When to create indexes
Create appropriate indexes according to the requirements of business statements; it is not necessary to index all columns. Also, more indexes are not necessarily better.
For example, frequently used statements: SELECT * FROM t1 WHERE name= ; At this time, an index column needs to be built on the name column
Build indexes on columns that are frequently used in WHERE, GROUP BY, ORDER BY, JOIN, ON…
Why can’t indexes be built arbitrarily?
1. If there are too many redundant indexes, changes in table data may very likely cause frequent updates of indexes, which may block some normal business update requests
2. Too many indexes can lead to deviation in optimizer selection.
5.2 Management commands
Build index:
1. Query the index status of the table: PRI-Primary key index; MUL-Secondary index
mysql>DESC city;
key: PRI-Clustered index, MUL-Secondary index, UNI-Unique index
mysql>SHOW INDEX FROM city;
2. Build index
Analyze business statements:
mysql>SELECT * FROM city WHERE name=‘wuhan’;
mysql>ALTER TABLE city ADD INDEX idx_na(name); –Here idx_na is the index name;
Composite index: mysql>ALTER TABLE city ADD INDEX idx_n_c(name, countrycode);
Prefix index: mysql>ALTER TABLE city ADD INDEX idx_d(district(5));
3. Delete Index
mysql> ALTER TABLE city DROP ADD INDEX idx_na;
5.3 Stress Test
5.4 Review
1. What does a table look like in a database?
MySQL is used to store the logical structure of data rows, and the data rows of the table are finally stored on many pages. The InnoDB storage engine organizes the table data into continuous pages in each area according to the ordered clustered index; these continuous data pages become the leaf nodes of the clustered index. It can be considered that the leaf nodes are the original table data.
2. What is table access: ?
Table access is to access the clustered index; the auxiliary index constructs the auxiliary index BTREE structure by using the auxiliary index column value + ID (PK); when the user uses the auxiliary index column as a query condition, the auxiliary index BTREE is first scanned,
a. If the auxiliary index can cover all our query results, then there is no need to access the table;
b. If it cannot be fully covered, it can only be accessed back to the clustered index (table access) by the ID primary key value obtained, and the desired result is finally obtained.
3. The impact of table access: The IO level increases, IOPS increases, and random IO increases;
4. Avoiding table access: a. Use the ID primary key to query as much as possible; b. Design reasonable composite indexes; c. More precise query conditions + composite indexes; d. Optimizer’s algorithm MRR;
5. Will updating data affect the index? Will the change of data cause the index to be updated in real-time?
DML statements, such as INSERT, UPDATE, DELETE a row of data,
a. For clustered indexes, updates are immediate;
b. For auxiliary indexes, they are not updated in real-time. The change buffer temporarily caches the data updates needed for auxiliary indexes. When we need to query the newly inserted data, a merge (merge) is performed in memory, at which point our auxiliary index is the latest. Then, the update is performed on the disk.
c. In the InnoDB memory structure;
Execution Plan Analysis
1.1 What is an Execution Plan
select * from t1 where name= ‘zs’;
It analyzes the execution plan chosen by the optimizer according to the built-in cost (cost) calculation algorithm.
For a computer, the cost is: IO / CPU / Memory MEM;
1.2 View Execution Plan
mysql> explain select * from world.city; or desc command
mysql> desc select * from world.city;
±—±————±——±———–±—–±————–±—–±——–±—–±—–±———±——+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±————±——±———–±—–±————–±—–±——–±—–±—–±———±——+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4046 | 100.00 | NULL |
±—±————±——±———–±—–±————–±—–±——–±—–±—–±———±——+
1.3 Analysis of execution plan, display results
table: Tables involved in this execution plan;
type: Query type (full table scan or index scan);
possible_keys: Indexes that might be used;
key: The last selected index;
key_len: Index coverage length;
rows: The number of rows to be scanned for this query;
Extra: Additional information.
1.4 Introduction to output information
mysql> desc select country.name, city.name from city join country on city.countrycode= country.code where city.population= ‘CHN’;
±—±————±——–±———–±—–±————–±————±——–±——————-±—–±———±——+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±————±——–±———–±—–±————–±————±——–±——————-±—–±———±——+
| 1 | SIMPLE | country | NULL | ALL | PRIMARY | NULL | NULL | NULL | 239 | 100.00 | NULL |
| 1 | SIMPLE | city | NULL | ref | CountryCode | CountryCode | 12 | world.country.Code | 17 | 100.00 | NULL |
±—±————±——–±———–±—–±————–±————±——–±——————-±—–±———±——+
1.4.1 table
Tables involved in this query: When multiple tables are involved in the query, it is precise to the table with the problem;
1.4.2 type
Query type:
Full table scan: No index used -> ALL;
mysql> desc select * from city;
mysql> desc select * from city where 1= 1;
mysql> desc select * from city where countrycode like ‘ch%’;
mysql> desc select * from city where countrycode not in (‘chn’, ‘usa’);
Index scan: index< range< ref< eq_ref< const(system) index: Full index scan, scanning the entire index tree; --mysql> desc select countrycode from world.city;
range: Index range query; >,<,<=,>=,like,in,or,between and; –mysql> desc select * from city where id< 10; For example: select * from city where countrycode in ('chn, 'usa'); -- rewrite as select * from city where countrycode= ‘chn’ union all select * from city where countycode= ‘usa’; Special case: when the query condition is the primary key --mysql>desc select * from city where id!= 10; –mysql>desc select * from city where id not in (9, 10);
ref: Auxiliary index equality query
mysql>desc select * from city where countrycode=‘chn’;
eq-ref: Multi-table join, the link condition of the non-driving table is the primary key or unique key;
–A join B on a.xx= b.xx where b.xx is a primary key or unique key will result in eq-ref;
–desc select country.name, city.name from city join country on city.countrycode= country.code where city.population= ‘chn’;
const(system): cluster index equal value query
–mysql>desc select * from city where id =10;
1.4.3 possible_key& key
possible_key: the index that may be accessed, all related indexes in this query
key: the index selected in this query
1.4.4 key_len: the length covered by the composite index
For composite index: index(a, b, c) –we hope that future query statements will apply to the composite index as fully as possible;
key_len: can help us judge how many parts of the composite index are accessed
key_len calculation:
Assuming a query –select * from t1 where a= and b= and c= ; can completely cover three columns of a composite index;
Then key_len= a length+ b length+ c length;
–The length refers to: the maximum storage value byte length of the column, which is affected by the data type and character set; when initializing the column, if the column not null is not set, then a separate byte space is needed to store whether it is null or not;
Number: not null not null has not been set
tinyint1 1 1+1
int 4 4+1
bigint 8 8+1
Character: utf8 —–> a character occupies up to 3 bytes, utf8mb4 occupies 4 bytes
not null not null has not been set
char(10) 30 30+1
varchar(10) 30+2(stored character length) 30+2+1
For example:
mysql>creat table t1(
a int not null,
b int,
c char(10) not null,
d varchar(10)
)charset= utf8mb4;
mysql>alter table t1 add index idx(a, b, c, d);
mysql>desc select * from t1 where a=1 and b= 1, c= ‘a’, d= ‘a’;
The query fully covers 4 columns of index, key_len= 92;
1.4.5 extra
using filesort:(abnormal) indicates that this query uses file sorting, which means the sorting operation in the query is unreasonable; for example: order by, group by, distinct ,.
–mysql>select * from city where countrycode= ‘CHN’ order by population;
–mysql>alter table city add index idx_c_p(countrycode, polulation); –Here a composite index is created, the statement before optimization
Index Application Specification
2.1 Principles of Index Creation (DBA Operation Specification)
2.1.1 Description
To make the use of indexes more standardized, it is necessary to consider which fields to shanghai index and what type of index to create when creating indexes. So, what are the principles of index design?
2.1.2 It is necessary to have a primary key when creating a table, generally a column that is irrelevant to the table
2.1.3 Selecting Uniqueness Index
The values of uniqueness indexes are unique, which can quickly determine a record through this index; In a composite index, the column with the most unique values is used as the left condition of the composite index;
For example, the student number in the student table is unique. Establishing a uniqueness index in the child segment can quickly determine a student’s information. If using the name, there may be homonyms, which may reduce the query speed;
Optimization plan:
a. If it is necessary to use columns with a large number of duplicate values as query conditions (such as, male and female), the table logic can be split (such as separating males and females);
b. This column can be combined with other query types for joint queries;
2.1.4 Add indexes to fields that are frequently used in operations such as where, order by, griup by, join in, etc.: Sorting will waste a lot of time;
2.1.5 If the value of the indexed field is very long, it is best to use the prefix of the value to index, prefix index
2.1.6 Limit the number of indexes
The number of indexes is not the more, the better, and the problems that may arise:
a. Each index occupies disk space, and the more indexes there are, the more disk space is required;
b. It is麻烦 to reconstruct indexes when modifying tables, and the more indexes there are, the more time-consuming it will be to update the table;
c. The optimizer’s burden will be heavy, which may affect the optimizer’s choice;
There is a tool in d.percona-toolkit that specifically analyzes whether an index is useful.
2.1.7 Delete indexes that are no longer used or rarely used (Percona Toolkit)
pt-duplicate-key-checker
If the data in the table is frequently updated, or if the way the data is used changes, some of the existing indexes may no longer be needed. Database administrators should regularly identify these indexes and delete them to reduce the impact on update operations
2.1.8 When adding indexes to large tables, it should be done during periods of low business activity
2.1.9 Try to avoid creating indexes on columns that are frequently updated
2.1.10 Indexing Principles
a. There must be a primary key. If there is no column that can be used as a primary key condition, you can create irrelevant columns;
b. Often used as where condition columns, order by, group by, join on, and distinct conditions;
c. It is best to use columns with many unique values as indexes. If the index column has many duplicate values, consider removing and indexing;
d. It is recommended to use prefix indexes for index columns with long column values;
e. Reduce the number of index entries, on the one hand, do not create unnecessary indexes, and clean up indexes that are not frequently used;
f. Avoid business busy periods when maintaining indexes
2.2 The situation where the index is not used
2.2.1 No query conditions, or the query conditions are not indexed
–mysql>select * from tab; –Full table scan
–mysql>select * from tab where 1=1;
In business databases, especially for tables with large amounts of data, there is no need for a full table scan;
a.–select * from tab; –Rewrite as select * from tab order by price limit 10; –An index needs to be established on the price column;
b.–select * from tab where name= ‘zhangsan’; –The name column does not have an index –Change: Use a column with an index as the query condition or create an index on the name column
2.2.2 The query result is most of the data in the original table, which should be 15-30%
When the result set of the query exceeds 25% of the total number of rows, the optimizer deems it unnecessary to use the index, which is related to the database’s pre-read ability, as well as some pre-read parameters;
2.2.3 The index itself becomes ineffective, and the statistical data is not true
Indexes and tables have self-maintenance capabilities. In cases where the table content changes frequently, the statistical information is inaccurate and outdated. It is possible for the index to become ineffective, and it is generally recommended to rebuild or manually collect error information and update;
Situation: A select statement usually runs quickly, but suddenly one day it becomes slow. What could be the reason? The select statement, statistics (innodb_index_stats and innodb_table_stats) are not true (because statistical information does not update in real time, and you need to use such as, optimize table world.city to manually update), causing the index to become ineffective, and this kind of problem is likely to occur after a large number of modifications to the table;
2.2.4 Using functions in query conditions on index items, or performing calculations on indexes, including (+, -, *, /, !, etc.), will cause the index to become ineffective;
For example, –mysql>select * from city where id- 1= 9;(Error, it will cause a full table scan)
–mysql>select * from city where id= 10;
2.3.5 Implicit conversion leads to index failure, this should be paid attention to, and it is also a common mistake in development; this will cause the index to fail;
For example, –mysql>create table t2(id int, telnum char(11));
mysql>alter table t2 add index idx(telnum);
mysql>select * from t2 where telnum= 110; — does not use index, as 110 will be converted to ‘110’, and a function operation is performed
mysql>select * from t2 where telnum= ‘110’; — uses index
2.3.6 <> and not in do not use index (auxiliary index)
–mysql>desc select * from tab where telnum <> ‘155555’;
–mysql>desc select * from tab where telnum not in (‘110’, ‘119’);
Single >, <, in may or may not use the index, depending on the result set (refer to 2.2.2), try to add limit; in combination with business Try to rewrite 'or' or 'in' as 'union' –desc select * from t2 where telnum in (‘110’, ‘119’) Rewritten as –desc select * from t2 where telnum=‘110’ union all select * from t2 where telnum= ‘119’ 2.3.7 like "%_" does not use index when the percentage sign is at the beginning –desc select * from t2 where telnum like “31%” -- uses range index scan –desc select * from t2 where telnum like “%110” -- does not use index For search needs of the class '%linux%', you can use databases such as elasticsearch or mongodb that are specifically designed for search services; extension; the optimizer's algorithm for index 3.1 MySQL index self-optimization - AHI (Adaptive Hash Index adaptive_hash_index): index index The index types that MySQL's InnoDB engine can create are only Btree; The function of AHI: Automatically evaluate "hot" memory index pages, generate HASH index tables, and help InnoDB quickly read index pages, accelerating the speed of index reading; 3.2 MySQL Index Self-Optimization - Change Buffer For example, insert, update, delete data, for clustered indexes, the update is performed immediately, and for auxiliary indexes, it is not updated in real time; In the InnoDB structure, an insert buffer (session) has been added, and the current version is called change buffer, which is used to temporarily buffer the data updates required for auxiliary indexes; When querying new inserted data, a merge (merge) operation is performed in memory, at this time the auxiliary index is the latest. 3.3 ICP: Index Pushdown (index_condition_pushdown) Function: Solves the situation where only part of the composite index can be applied, in order to reduce the unnecessary scanning of data pages, a secondary filter is performed at the engine layer before accessing the data, to filter out irrelevant data in advance. For example, index(a, b, c) –mysql>select * from t1 where a=yy and c=xx; –server layer parsing optimization will determine that only the a index is used, (the condition filtering of the composite index is from left to right, here the b condition is missing, so only a index is used)
–After index pushdown, the second condition is executed at the engine layer (engine) to perform the c=xx filter, and then load the data page after filtering, which reduces the scanning of irrelevant data pages.
3.4 Introduction to Optimizer Algorithms
mysql>select @@optimizer_switch –query
±—————————————————————————-
@@optimizer_switch
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=on,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on,
use_invisible_indexes=off,
skip_scan=on
±—————————————————————————-
3.5 How to modify
my.cnf mysql>set global optimizer_switch= ‘index_condition_pushdown= on’; — Enable, set the algorithm parameters to ‘on’; hints –https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) / f1
FROM t3 WHERE f1 > 30 AND f1 < 33; SELECT /+ BKA(t1) NO_BKA(t2) / * FROM t1 INNER JOIN t2 WHERE …; SELECT /+ NO_ICP(t1, t2) / * FROM t1 INNER JOIN t2 WHERE …; SELECT /+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) / * FROM t1 …; EXPLAIN SELECT /+ NO_ICP(t1) / * FROM t1 WHERE …; SELECT /+ MERGE(dt) / * FROM (SELECT * FROM t1) AS dt; INSERT /+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2); 3.6 MRR - multi range read Enable MRR (acting at the engine level) – mysql>set global optimizer_switch= ‘mrr= on, mrr_cost_based= off’;
Reflects auxiliary index – table access –> cluster index; A table access is required for each auxiliary index id read;
Converted to auxiliary index –> sort id –> table access –> cluster index; There is a buffer (rowid buffer), sort, and then access the table in one go;
Function: reduce the number of times the table is accessed, reduce IOPS, and minimize random IO as much as possible;
3.7 SNLJ- Simple Nested-Loops Join (SNLJ, simple nested-loop join) –https://dev.mysql.com/doc/refman/8.0/en/nested-loop-joins.html
a join b on a.xx= b.yy where…;
–Pseudocode
for each row in a matching range {
for each row in b {
if a.xx= b.yy, send to client
}
}
In the above example, the driving table can be forced through left join
3.8 BNLJ- Block Nested-Loop 块嵌套循环
When the association condition between a and b matches, it is no longer a row-by-row matching loop, but instead, it matches the associated values (existing in the join buffer) with the non-driving table in one go and returns the results in one go;
Mainly optimized CPU consumption and IO times;
The Extra value contains Using join buffer (Block Nested Loop)
3.9 BKA- Batched Key Access –https://dev.mysql.com/doc/refman/8.0/en/bnl-bka-optimization.html
Enabling method: –mysql>set global optimizer_switch= ‘mrr= on, mrr_cost_based= off’;
mysql>set global optimizer_switch= ‘batched_key_access= on’;
Application scenario: the non-driving table associated column (b.yy) is a common auxiliary index;
Based on BNLJ, the MRR algorithm is added to sort the associated values in the join buffer and then match them with the non-driving table;
Main function: Optimizes the auxiliary index of the associated column of the non-join table;
It is equivalent to BNLJ+ MRR;
==============================================================================================================================================================================================
Chapter 6 MySQL Storage Engines
What is a storage engine?
It is equivalent to the built-in file system of MySQL, the hierarchical structure that deals with the file system in Linux.
Types of MySQL Storage Engines
2.1 Oracle MySQL
Different storage engines can be set for different tables,
mysql> show engines;
±——————-±——–±—————————————————————±————-±—–±———–+
| Engine | Support | Comment | Transactions | XA | Savepoints |
±——————-±——–±—————————————————————±————-±—–±———–+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
±——————-±——–±—————————————————————±————-±—–±———–+
Interview question: List the types of storage engines supported in MySQL;
Since MySQL 5.5, the default storage engine is InnoDB for more than 99%;
2.2 Other branches
percona: XtraDB;
MariaDB: InnoDB;
Other engines: ToKuDB: High compression ratio, faster insert and delete, suitable for scenarios with a large number of inserts and deletes in business and large-scale data scenarios;
MyRock: .
Core features of InnoDB
3.1 Introduction – also the difference between InnoDB and MyISAM
MVCC: Multi-version concurrency control;
Clustered Index: Clustered index;
Multiple buffer pools:
Transaction:
Row-level lock granularity: leads to different concurrency levels of tables;
Foreign key:
More replication features:
Support hot backup:
Automatic failure recovery:
Change Buffer:
Adaptive hash index (AHI):
Interview question:
a. Please list the advantages of MySQL InnoDB storage engine?
b. Please list the differences between InnoDB and MyISAM?
Storage engine management commands
4.1 Use select to confirm session storage engine
mysql> select @@default_storage_engine;
±————————-+
| @@default_storage_engine |
±————————-+
| InnoDB |
±————————-+
4.2 Storage Engine
Set default storage engine: Session level
mysql>set default_storage_engine= myisam; — Only affects the current logged-in session;
Set default storage engine: Global level (affects only new sessions)
mysql>set global default_storage_engine= myisam; –global
After restarting, all parameters are invalid;
Set default storage engine: To take effect permanently, it needs to be written into the configuration file
–vim /etc/my.cnf
[mysqld]
default_storage_engine= myisam
The storage engine is at the table level, a different storage engine can be specified when each table is created, but it is recommended to use InnoDB uniformly.
4.3 Show the storage engine of each table:
mysql> show create table cityG
*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE
int(11) NOT NULL AUTO_INCREMENT
char(35) NOT NULL DEFAULT ‘’
char(3) NOT NULL DEFAULT ‘’
char(20) NOT NULL DEFAULT ‘’
int(11) NOT NULL DEFAULT ‘0’
PRIMARY KEY (),
KEY (),
CONSTRAINT FOREIGN KEY () REFERENCES ()
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
mysql> show table status like ‘city’G
*************************** 1. row ***************************
Name: city
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 4188
Avg_row_length: 97
Data_length: 409600
Max_data_length: 0
Index_length: 131072
Data_free: 0
Auto_increment: 4079
Create_time: 2019-09-01 17:09:30
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
4.4 Confirm the storage status of each table in information_schema
mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in (‘sys’, ‘mysql’, ‘information_schema’, ‘performance_schema’);
4.5 Modify the engine of a table
mysql>alter table x engine= InnoDB;
Note: This command can be used frequently, and it can also be used to defragment InnoDB tables.
–mysql>desc information_schema;
select table_schema,table_name,data_free from information_schema.tables; — Used to check if a table has fragmentation
4.6 Commonly encountered problems – Fragmentation handling
Environment: centos7.4, MySQL 5.7.20, InnoDB storage engine
Business characteristics: Large data volume, often need to delete historical data by month
Problem: The disk space usage is very high and does not release
Solution:
Before: Export the data logically, manually drop the table, and then import it into it;
Now: Partition the table by month (partition, middleware) or archive the table, and replace the business with truncate;
Interview question: How to delete about 10 million rows from a table with 200 million rows?
1. If a 200 million data table still generates, it is recommended to adopt the partitioned table method (such as, by month range) when designing the table, and then use truncate when deleting;
2. If it already exists, it is recommended to use the pt-archive tool to archive the table and delete unnecessary data.
4.6 Extension: How to modify in bulk
Requirement 1: Convert all tables in the zabbix database from innodb to tokudb
–mysql>select concat(“alter table “, table_schema, “.”, table_name, “engine= tokudb;”) from information_schema.tables where table_schema= ‘zabbix’ into outfile ‘tmp/tokudb.sql’;
MySQL Storage Engine Architecture
5.1 Macrostructure
5.1.1 MyISAM
–mysql> create table myt(id int)engine= myisam;
myt.frm – Data Dictionary Information, column definitions and properties
myt.MYD – Data Rows
myt.MYI – Index
5.2.2 InnoDB
Disk Area:
city.ibd – User Tablespace (Independent Tablespace File-Per-Table Tablespace) – Data rows and indexes
city.frm – Data Dictionary Information
ibdata1 – System Tablespace (Shared Tablespace) File – InnoDB data dictionary information, Doublewrite Buffer, Change Buffer, Undo logs (transaction rollback logs)
iblogfile0~iblogfilen – InnoDB Redo Logs
ibtmp1 – Temporary Tablespace File (used for sorting, grouping, multi-table joins, subqueries, logical backups, etc.)
ib-buffer-pool – Stores hot data in a centralized location at shutdown (sequential IO)
db01.err – Error Log
8.0: ibdata1 -取消了存储数据字典信息和 Undo logs, MySQL is gradually slimming down the ibdata1 file, and the more critical data is isolated, stored separately.
Therefore, simply copying the ibd and frm files to a new database is not enough to use it normally.
5.2 Microstructure
First, Disk
5.2.2 Tablespace
1. What is a Tablespace?
The concept of tablespaces was introduced in Oracle databases, initially to address the issue of storage space expansion. MySQL 5.5 introduced the shared tablespace mode.
2. MySQL Tablespace Types
Shared Tablespaces (The System Tablespace): The default storage method in MySQL 5.5, where users store system data, logs, undo, temporary tables, user data, and indexes.
Independent Tablespaces (File-Per-Table Tablespaces): The default storage method in MySQL 5.6, where each table has its own ibd file.
General tablespaces (General Table Tablespaces): Completely consistent with the Oracle tablespace management model; can be customized individually;
Undo tablespaces (Undo Tablespaces): Store undo logs (rollback logs);
Temporary tablespaces (The Temporary Tablespaces): Store temporary tables, 5.7 default independent;
3. Tablespace Management
– View the default tablespace mode: 1 – Independent tablespace (the default independent tablespace currently); 0 – Shared tablespace;
– mysql> select @@innodb_file_per_table;
±————————+
| @@innodb_file_per_table |
±————————+
| 1 |
±————————+
Switch tablespace:
Temporary: –mysql>set global innodb_file_per_table= 0; Then log in to the session again;
Permanent: –vim /etc/my.cnf
innodb_file_per_table= 0 — Note, the change only affects newly created tables after modification;
Expand the size and number of shared tablespaces: Note, the parameters are usually set when initializing the database;
Method 1: Before initialization, the following configuration needs to be added to my.cnf:
–innodb_data_file_path= ibdata1: 1G; ibdata2: 1G: autoextend;
Method 2: Expand multiple ibdata files on a running database
lottery and Where is it
The wrong way:
–innodb_data_file_path= ibdata1: 128M; ibdata2: 128M; ibdata3: 128M: autoextend
The correct way:
–innodb_data_file_path= ibdata1: 76M; indata2: 128M; ibdata3: 128M: autoextend
Because, when setting the innodb_data_file_path parameter, the size of the existing ibdata1 file should be consistent with the actual size on the disk, not arbitrarily specified.
5.2.3 Segment Extent Page
Table –> Tablespace –> Segment –> Multiple extents –> Continuous pages –> Continuous blocks –> Continuous sectors;
5.2.4 Log
1. Transaction log:
Redo log: Redo log – File location: ib_logfile0~ ib_logfilen
Parameter: –mysql>show variables like ‘%innodb_log%’;
±———————————–±———+
| Variable_name | Value |
±———————————–±———+
| innodb_log_buffer_size | 1048576 | # Set file size
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 | # Set file count
| innodb_log_group_home_dir | . | # Set storage location
| innodb_log_spin_cpu_abs_lwm | 80 |
| innodb_log_spin_cpu_pct_hwm | 50 |
| innodb_log_wait_for_flush_spin_hwm | 400 |
| innodb_log_write_ahead_size | 8192 |
±———————————–±———+
Function: Used to store the data page change process and version number (LSN) during MySQL’s modification operations (DML: insert, update, delete), (it belongs to physical log);
The default two files store redo and are used in a circular manner.
Undo log: Rollback log – file location 5.7 default location ibdataN and ibtmp1
Parameters: –mysql>show variables like ‘聮’;
±————————-±———–+
| Variable_name | Value |
±————————-±———–+
| innodb_max_undo_log_size | 1073741824 | # Set file size
| innodb_undo_directory | . | # Set file path
| innodb_undo_log_encrypt | OFF |
| innodb_undo_log_truncate | ON |
| innodb_undo_tablespaces | 2 |
±————————-±———–+
–mysql>show variables like ‘%segments%’; # *Rollback segment parameters
±————————-±——+
| Variable_name | Value |
±————————-±——+
| innodb_rollback_segments | 128 |
±—————— ——-±——+
Function: Used to store rollback logs (version snapshots), providing multi-version InnoDB read and write;
Provide rollback functionality: records the opposite operation of each operation, belongs to logical log;
Second, memory
5.2.5 MySQL data memory area = shared memory + session memory session count + additional memory usage (file system cache)
Shared memory buffer area:
InnoDB Buffer Pool (Buffer Pool):
Parameter: –mysql> select @@innodb_buffer_pool_size;
±————————–+
| @@innodb_buffer_pool_size |
±————————–+
| 8388608 |
±————————–+
Function: Buffers MySQL data pages + index pages, pre-allocated memory area;
Session memory buffer area:
–mysql> show variables like ‘%buffer%’;
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 60817408 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 65536 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size
5.2.6 Log
log_buffer_size = 16777216;
Function: Responsible for buffering the redo log;
InnoDB Core Feature Explanation
6.1 Transactions
6.1.1 What is a transaction
A transaction is a working mechanism that appears with business scenarios of transaction types; ensuring the ‘harmony’ (equivalent transaction) of transactions.
Transaction: a. Barter
b. Currency exchange: physical currency or Online casino and How to find it ;
In computing, a transaction refers to: computation
6.1.2 Introduction to ACID standard characteristics of transactions
A – atomicity (Atomicity): An atom is the smallest constituent unit of matter, with the property of being indivisible; in a MySQL transaction unit, all standard transaction statements (DML) must either all succeed or all roll back.
C – consistency (consistency): The transaction should ensure that the data is always consistent before, during, and after the transaction; The design of MySQL’s various functions is to ultimately ensure consistency. (For example, in the red envelope, the total amount of money does not change.)
I – isolation (isolation): MySQL can support a system that can work concurrently with multiple transactions, and when a transaction is working, it cannot be affected by other transactions.
D – durability (durability): After the transaction is submitted (Commit command executed) successfully, all the data of this transaction operation must be permanently saved (“falling disk”), and it will not cause data failure due to data instance failure.
6.1.3 Transaction lifecycle management
1. Standard transaction control statements
begin/ start transaction: Open the transaction;
commit: Submit the transaction;
rollback: Rollback the transaction (return); Statements submitted after the commit cannot be rolled back;
–mysql>begin;
DML1; — Note that it is a DML statement
DML2;
commit;(rollback;)
Automatic commit feature:
–mysql>select @@autocommit;
±————-+
| @@autocommit |
±————-+
| 1 |
±————-+
When autocommit=1, there is no need to add begin (no explicit transaction opening) when executing dml:
When you execute DML statements, a begin will be added automatically before this DML;
After execution, commit will be added automatically;
begin;—> automatic
DML;
commit;—> automatic
autocommit= 1, generally applicable to non-transaction business scenarios;
If it is a transaction business:
1. Set autocommit= 0; commit, it takes effect only when manually submitted;
2. Manually operate begin and commit each time you want to perform transactional operations.
Setting method:
1. Temporary effectiveness: –mysql>set global autocommit= 0; Reopen the session to take effect;
2. Permanent effectiveness: –vim /etc/my.cnf
autocommit= 0; — The database restart takes effect;
2. Standard transaction statements
insert
update
delete
select
For example, –mysql>begin;
use world;
delete from city where id> 10;
rollback; — Rollback the entire transaction (from begin to rollback), which is equivalent to doing nothing before it;
–mysql>begin;
use world;
delete from city where id> 10;
commit; — commit, permanently save;
3. Implicit transaction control
a. Implicit commit:
1. Set autocommit= 1;
2. When non-DML statements such as DDL, DCL occur, implicit commit will be triggered
For example, –mysql>begin;
DML1;
DML2;
drop database world;
–Will automatically add commit after DML2 statement; implicit commit;
3. Non-physical statements that cause implicit commit:
DDL statements: alter, create, drop;
DCL statements: grant, revoke, set password;
Locking statements: lock tables, unlock tables;
Other statements: truncate table; load data infile; select for update;
b. Implicit rollback:
1. Session disconnected or dead;
2. Database crashes;
3. Execution of statements fails in the transaction.
6.1.4 How does InnoDB transaction ACID ensure?
Noun introduction
a. Redo log
redo log-> Redo log ib_logfile0~ ib_logfileN, default 2, 48M, cyclic use
Function: Records the changes of data pages.
redo memory location: redo log buffer.
b. Storage location of disk data pages
idb file: Stores data rows and indexes
buffer pool: Buffer pool, buffer for data pages and index pages.
c. LSN(log sequence number): Log sequence number
Exists in: Disk data pages, redo files, buffer pool, redo buffer; for version control;
Each time MySQL starts the database, it compares the LSN of the disk data page and redolog, and both LSNs must be consistent for the database to start normally;
d. WAL: Write ahead log, a log-first write method to achieve persistence (write logs first, then write data);
e. Dirty pages: Memory dirty pages, when modifications occur in memory and are not written to disk before, we also call this memory dirty pages;
f. CKPT(check point): Checkpoint, which is the action of writing dirty pages to disk (a working mechanism);
g. TXID(transaction id): Transaction number, InnoDB generates a transaction number for each transaction, which accompanies the entire transaction;
h. undo: Stored in ibdata1, it stores rollback information during transaction work
6.2 InnoDB transaction workflow
6.2.1 redoredo, redo log, a type of transaction log; in the ACID process, it achieves the ‘D’ (durability) persistence role, and also has corresponding effects on ‘A’ atomicity and ‘C’ consistency; log location: ib_logfile0~N; memory area: redo buffer – records the change information of data pages + the LSN version number after data modification; redo refresh strategy: commit; refresh the current transaction’s redo buffer to disk, and also refresh a part of the redo buffer that has not been committed to disk. Supplement: the redo stores the changes of data pages during the transaction process, and is immediately written to disk when committing, and the log falls to disk successfully;
During the normal MySQL working process, the main task is to provide fast persistence functionality; when MySQL crashes due to abnormal outage, the main task is to provide the forward roll functionality (CSR – automatic fault recovery).
Double one standard:
innodb_flush_log_at_trx_commit= 0/ 1/ 2;
1 – Redo is immediately flushed to disk at each transaction commit, and the commit can only be successful; recommended to use;
0 – Redo buffer is flushed to os cache every second, and then fsync() to disk, an abnormal outage may cause loss of transactions within 1 second;
2 – Redo buffer is immediately flushed to os cache after each transaction commit, and then fsync() to disk every second, an abnormal outage may cause loss of transactions within 1 second.
Additionally,
1. The redo buffer is also related to the operating system’s cache mechanism, so the write strategy may be related to the innodb_flush_method parameter;
2. Redo also has group commit, which can be understood as, when refreshing the committed redo each time, some uncommitted transaction redos can also be written to disk at one time (but with special marks).
6.2.2 undo: The role of the undo log is to achieve the ‘A’ atomicity in the ACID process; in addition, CI also depends on undo; during rollback, the data is restored to the state before the modification, and in CSR implementation, the order is to first redo the forward roll and then undo the rollback (CSR – forward roll + rollback). Disk location: ibdata, ibtmp; the undo process also records redo information during its generation; consistency snapshot: Each time a transaction is opened, a consistent snapshot is generated through undo, undo provides the snapshot end, saving the data state before the transaction modification, ensuring MVCC, isolation, and the hot recovery mechanism of mysqldump.
6.2.3 The role of isolation level and lock mechanism: mainly to provide the I(isolation) characteristic, and also for the C konsistency) characteristics are also guaranteed; transaction_isolation transaction isolation & isolation level description
a. RU: Read Uncommitted (READ UNCOMMITTED) – Problems: dirty page read, non-repeatable read, phantom read;
b. RC: Read Committed (READ COMMITTED) – Non-repeatable, phantom read
c. RR: Repeatable Read (REPEATABLE READ) default level – There is read;
d. SR: Serializable (SERIALIZABLE) – Serial transaction operations are不利于 concurrent transactions.
Here, the R”read” represents: page read, which is the read at the storage engine level and does not represent the read of select (SQL layer read); Parameter modification
Temporary: –mysql>select @@transaction_isolation;
±————————+
| @@transaction_isolation |
±————————+
| REPEATABLE-READ |
±————————+
mysql>set global transaction_isolation= ‘read-uncommitted’; — Take effect after the session restarts;
Permanent modification: vim /etc/my.cnf
transaction_isolation= ‘read-uncommitted’ — Take effect after restart; Example demonstration:
a. Dirty Read: session2 reads the dirty page that session1 is modifying but has not yet committed; For dirty reads, it is not allowed in business production;
session1:
session2:
b. Non-repeatable Read Phenomenon: In the same session session2 window, executing the same statement such as (select * from city), but the results obtained at different times are different (due to the time difference between the session1 transaction and the commit time); In more rigorous scenarios, business that requires high transaction isolation and data consistency is not allowed to appear;
c. Phantom Read: During the update process of a transaction, data modified by another transaction appears, causing unexpected problems in the update (session1 commit before, session2 commit, resulting in unexpected results at this time);
d. Through RR, more than 99% of the phantom reads have been resolved. To be more rigorous, GAP lock and next-lock have been added;
6.3 MySQL’s Lock Mechanism
6.3.1 Introduction
6.3.2 Function
Ensure transaction isolation, ensure that resources are not requisitioned; Locks belong to resources, not to a specific transaction. Each time a transaction needs resources, it needs to apply for and hold the lock of the resource.
6.3.3 Lock type resource:
a. Memory lock: mutex, latch, to ensure that memory data page resources are not contended and not displaced.
b. Object lock: MDL(Metadata lock): When modifying metadata (attribute class modification), DDL operation -> alter;
Table_lock,: Table lock; DDL, during backup (FTWRL global table lock), may also be upgraded (from row lock upgrade) to trigger table lock; –mysql>lock tables city;
record(row) lock: Row lock, index lock, locking cluster index
GAP: Gap lock, RR level, ordinary auxiliary index gap lock
Next-lock: Next key lock GAP + record lock, range lock of ordinary auxiliary index. Granularity: Some granularity of object locks
MDL(Metadata lock): When modifying metadata (attribute class modification), DDL operation -> alter; High granularity
Table_lock,: Table lock; DDL, during backup (FTWRL global table lock), may also be upgraded (from row lock upgrade) to trigger table lock; –mysql>lock tables city; High granularity
record(row) lock: Row lock, index lock, locking cluster index; low granularity if it is a single row
GAP: Gap lock, RR level, ordinary auxiliary index gap lock; range lock;
Next-lock: Next key lock GAP + record lock, range lock of ordinary auxiliary index. Functional classification:
IS: Intention shared lock, table level
S: Shared lock, read lock, row level
IX: Intention exclusive lock, table level
X: Exclusive lock, write lock, row level
Row lock, example
session1 session2
mysql>update x set num= 2 where id= 1; mysql>update x set num= 481 where id= 40; –There is no blocking between session1 and session2 to view lock waiting –mysql>select * from sys.innodb_lock_waitsGDeadlock: There is a resource contention between two transactions;
View deadlock: –mysql>show engine innodb statusG
Or, –vim /etc/my.cnf
unnodb_print_all_deadlocks= 1 –Print all deadlock information
Processing flow: Obtain monitoring data —–> trx_id, thread —–> SQL. Optimistic locking and pessimistic locking
Buying train tickets; Optimistic locking: I’m sure no one will compete with me, and then pay the bill later; Pessimistic locking: I’m sure someone will compete with me, lock it first, and then pay the bill;
Optimistic locking is generally used in combination with queues.
6.4 The C characteristic of ACID consistency in transactions
A: Atomicity, undo (rollback), redo (forward rollback);
C: Consistency, ensures that the final state of the data is complete before, during, and after the transaction;
I: Isolation, isolation level, locking mechanism, MVCC consistency snapshot (undo log);
D: Durability, committed data, REDI (WAL).
The characteristic of C (consistency) is to ensure consistency with all the above features;
Write consistency: undo, redo, isolation, lock;
Read consistency: isolation level, MVCC;
Data page consistency: double write buffer (disk area): Write in sequence to the double write buffer in ibdata1 before writing to the disk, and then update the original data.
Core parameters of the storage engine
7.1 innodb_flush_log_at_trx_commit= 1/0/2 (one of the dual standards) controls the parameters for writing the redo log;
1- Default, logs are written and flushed to disk at each transaction commit.
0- Logs are written and flushed to disk once per second.
2- Logs are written after each transaction commit and flushed to disk once per second.
7.2 innodb_flush_method= fsync/O_DIRECT/O_DSYNC;
Function: Controls whether the OS Cache is used when MySQL flushes data to disk;
fsync mode – When the data in the buffer pool is written to disk, it needs to pass through the OS Cache first, and then be written to the disk; —The default is fsync mode
When writing data to disk, the redo buffer needs to pass through OS Cache first, and then write to disk.
O_DSYNC mode – When the data in the buffer pool is written to disk, it needs to pass through the OS Cache first, and then be written to the disk; —Not recommended for production use
When writing data to disk, the redo buffer data passes through OS Cache.
O_DIRECT mode – When writing data to disk, the buffer pool data is written directly to disk, bypassing OS Cache; — It is recommended to use O_DIRECT in production, preferably with solid-state drives
When writing data to disk, the redo buffer needs to pass through OS Cache first, and then write to disk.
7.3 innodb_buffer_pool_size
Function: Controls the total size of the data buffer area (buffer pages and index pages), which is the largest memory area in MySQL;
Default: 128M;
Official suggestion: 80%-90%; OOM is likely to occur;
Production suggestion: below 75%, set as needed.
OMM (Out of Memory) Solution —-> innodb_buffer_pool_size= 75* Total
Chapter 7 Log Management ==============================================================================================================================================================================
Error Log
1.1 Function:
Records all states, warnings, and errors since MySQL started, providing help for us to locate database problems;
1.2 Configuration Method
Default: enabled status;
Location: /datadir/hostname.err
mysql> select @@datadir;
±——————————————–+
| @@datadir |
±——————————————–+
| C:ProgramDataMySQLMySQL Server 8.0Data |
±——————————————–+
Custom location: –vim/etc/my.cnf
log_error= /tmp/mysql.log — Take effect after restart: /etc/init.d/mysqld restart
1.3 How to use it?
binlog binary log
2.1 Function: Applied in data recovery and master-slave replication, mainly records the log of database change nature (including DDL, DCL, DML), which is a logical nature log;
2.2 Configuration Method:
Default: Not enabled 8.0 before, automatically enabled after 8.0, recommended to enable in production;
vim /etc/my.cnf
server_id= 6 # Host number, used in master and slave, binlog must be added with this parameter after 5.7;
log_bin= /data/binlog/mysql-bin # Log storage directory + log name prefix (such as, mysql-bin.00001);
sync_binlog= 1 # Binlog log disk strategy, the second in double one (the first is the disk strategy of redo log innodb_flush_log_at_trx_commit); 1- The binlog cache logs are immediately written to the disk after each transaction commit;
binlog_format= row # Controls the bin log recording mode to row mode.
–Restart takes effect, pay attention to the path must exist and have permission. It must be separated from the data disk
2.3 Details of binlog recording content
2.3.1 Introduction
Binlog is a SQL layer function, which records the changed SQL statements and does not record query statements;
2.3.2 Types of SQL statements recorded
DDL: Records the current DDL unchanged (statement statement method), what is executed is recorded what;
DCL: Records the current DCL unchanged (as above);
DML: Only records the DML transactions that have been committed;
2.3.3 Three kinds of DML recording methods
The binlog_firmat (binlog recording mode) parameter affects, and only affects the recording method of DML statements, not DDL and DCL;
a. statement (5.6 default) SBR (statement based replication) mode: Blanks the current DML statements without altering the statements;
b. row (5.7 default) RBR (Row based replication) mode: Records the changes in data rows (users cannot understand it and need tools to analyze);
c. mixed (mixed) MNR (mixed based replication) mode: A mixture of the above two, with MySQL choosing which one is better.
2.3.4 Interview Questions
Comparison between SBR and RBR modes
STATEMENT: High readability, small log volume, but not rigorous enough;
ROW: Low readability, large log volume, sufficient rigor.
For example, –mysql>update t1 set xxx= xxx where id> 1000; ? A total of 500w rows — At this time, the statement only needs to record this DML statement, while RBR records 500w rows of data;
2.4 What is an Event?
2.4.1 Introduction to the Event
The minimum recording unit of binary is an event;
For DDL, DCL, a statement is an event; for DML: only records committed transactions. —mysql>begin; DML1; DML2; commit; —Records 4 events;
2.4.2 Composition of event
Three parts make up: position
a. Event start identifier: at 194;
b. Event content:
c. Event end identifier: end_log_pos 254;
The role of position number: Convenient for us to extract events.
2.5 Viewing binlog
File location: –select @@log_bin_basename;
±——————————————————–+
| @@log_bin_basename |
±——————————————————–+
| C:ProgramDataMySQLMySQL Server 8.0DataHOWELL-L-bin |
±——————————————————–+
View the enabled status: –select @@log_bin;
±———-+
| @@log_bin |
±———-+
| 1 |
±———-+
2.5.2 File View –#ls -l /data/binlog/
2.5.3 Built-in Binary View Commands
a. View the number of log files currently available; –mysql>show binary logs;
±——————–±———-±———-+
| Log_name | File_size | Encrypted |
±——————–±———-±———-+
| HOWELL-L-bin.000020 | 351 | No |
±——————–±———-±———-+
b. View the current log file in use; –mysql>show master status;
±——————–±———±————-±—————–±——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±——————–±———±————-±—————–±——————+
| HOWELL-L-bin.000020 | 351 | | | |
±——————–±———±————-±—————–±——————+
c. View binary log events; –mysql>show binlog events in ‘HOWELL-L-bin.000020’;
±——————–±—-±—————±———-±————±—————————————————-+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±——————–±—-±—————±———-±————±—————————————————-+
| HOWELL-L-bin.000020 | 4 | Format_desc | 1 | 124 | Server ver: 8.0.17, Binlog ver: 4 |
| HOWELL-L-bin.000020 | 124 | Previous_gtids | 1 | 155 | |
| HOWELL-L-bin.000020 | 155 | Anonymous_Gtid | 1 | 232 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| HOWELL-L-bin.000020 | 232 | Query | 1 | 351 | use ; create table myt(id int)engine= myisam |
±——————–±—-±—————±———-±————±—————————————————-+
2.6 View binlog file content and data recovery
2.6.1 Event View
–#mysql -uroot -pzxcv -e “show binlog events in ‘HOWELL-L-bin.000020’” |grep DROP
2.6.2 View binlog log content
–#mysqlbinlog HOWELL-L-bin.000020;
–#mysqlbinlog HOWELL-L-bin.000020> /tmp/a.sql
#vim /tmp/a.sql
a. DDL:
# at 219
#200225 17:52:16 end_log_pos 338
create database oldguo charset utf8mb4;
# at 338
b. DML:
# at 690
#200225 17:55:53 server id 5 end_log_pos 763
BEGIN
…
# at 763
…
# at 821
…
# at 1107
COMMIT
…
–#mysqlbinlog –base64-output=decode-rows -vvv HOWELL-L-bin.000020 > /tmp/b.sql –detailed display, limited to viewing detailed content
–#mysqlbinlog –help
2.6.3 日志截取恢复
mysql>flush logs; –刷新出一个新的日志文件，滚动一个新的日志；
日志恢复案例：
–数据准备
mysql>create database bindb charset utf8mb4;
mysql>use bindb;
mysql>create table t1(id int);
mysql>begin;
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
mysql>begin;
mysql>insert into t1 values(4),(5),(6);
–删除数据库
mysql>drop database bindb;
–根据日志进行数据恢复
–1. 分析binlog,找到起点终点；
–起点：HOWELL-L-bin.000021 | 232 | Query | 1 | 359 | create database bindb charset utf8mb4 /* xid=872 /
–终点：HOWELL-L-bin.000021 | 1196 | Query | 1 | 1303 | drop database bindb / xid=885 */
mysql>show master status;
mysql>show binlog events in ‘HOWELL-L-bin.000021’;
–2. 截取日志
#mysqlbinlog –start-position=232 –stop-position=1196 /data/binlog/HOWELL-L-bin.000021 > /tmp/bin.sql –windows下需要cd到binlog文件目录下；
–windows操作系统下C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog –start-position=232 –stop-position=1196 HOWELL-L-bin.000021> bin.sql
mysql>set sql_log_bin= 0; –再当前会话关闭binlog
mysql>source /tmp/bin.sql; –恢复
–windows为：mysql> source C:\ProgramData\MySQL\MySQL Server 8.0\Data\bin.sql
mysql>set sql_log_bin= 1;
–Verify the data
mysql>select * from bindb.t1;
Thought? If it is in a production environment, what are the drawbacks of this recovery method?
a. What to do when there is too much data? Data rows are too many
b. The binlog records not only the operations of one database, but may also be repeated operations for other databases; #mysqlbinlog -d bindb –start-position=219 –stop-position=1357 /data/binlog/mysql-bin.0000005
c. Created for several years, has been in use all the time, data operation inserted from bin_log.0000001 to bin_log.0012314123; — Full backup every Saturday at 23:00, binlog backup every day at 23:00
d. Cross multiple files, time slicing can be used.
Binlog is actually used in conjunction with backups for data recovery.
2.7 Binlog Maintenance Operations
2.7.1 Log Rolling
mysql>flush logs; — Manually trigger log rolling
#mysqladmin -uroot -phowell flush-logs — roll logs
mysql>select @@max_binlog_size; — Automatically roll over when the log file reaches 1G
±——————+
| @@max_binlog_size |
±——————+
| 1073741824 |
±——————+
mysqldump -F — automatic rolling
Restart the database — automatic rolling, triggering the creation of a new binlog
2.7.2 Log Deletion
Note: Do not use the rm command to delete logs.
mysql>select @@expire_logs_days; — Automatically delete, default 0, unit is day, representing never delete. How many days are appropriate? More than one full backup cycle; in production, it is generally recommended to have at least two full backup cycles.
±——————-+
| @@expire_logs_days |
±——————-+
| 0 |
±——————-+
mysql>purge binary logs to ‘mysql-bin.000010’; — Manually delete, delete up to the 00010 number
mysql>purge binary logs before ‘2-19-04-02 22:46:26’; — Delete logs before a certain time
mysql>reset master; — Clear, note: quite dangerous, this operation should be performed on the master, causing master-slave to be down.
2.8 GTID mode management of binlog – global transaction id
2.8.1 Introduction
A new feature added in version 5.6, enhanced in 5.7 and 8.0; GTID in 5.7 will be automatically generated even if not enabled by setting SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’
2.8.2 GTID(Global Transaction ID)
It is a number for a committed transaction and it is a globally unique number.
GTID= server_uuid: transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29
2.8.3 Enable
vim /etc/my.cnf
gtid-mode= on
enforce-gtid-consistency= true
–Check if GTID is enabled mysql>select @@gtid_mode;
±————+
| @@gtid_mode |
±————+
| OFF |
±————+
mysql>show master status;
±——————–±———±————-±—————–±——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±——————–±———±————-±—————–±——————+
| HOWELL-L-bin.000023 | 155 | | | |
±——————–±———±————-±—————–±——————+
2.8.4 View binlog Based on GTID
After having GTID, extract and view certain transaction logs;
–include-gtids
–exclude-gtids: Exclude
–skip-gtids
Example++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql>show master status;
mysql>create database gtdb charset utf8mb4;
mysql>use gtdb
mysql>create table t1(int id);
mysql>begin;
mysql>insert into t1 values(1),(2),(3);
mysql>commit;
mysql>flush logs;
mysql>show master status;
## Second Wave Commands++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql>create table t2(id int);
mysql>begin:
mysql>insert into t2 values(1),(2),(3);
mysql>commit();
mysql>flush logs;
mysql>show master status;
## Third Wave Commands++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
mysql>create table t3(id int);
mysql>begin;
mysql>insert into t3 values(1),(2),(3);
mysql>commit;
mysql>show master status;
2.8.5 The Idempotence of GTID
After enabling GTID, MySQL recovers Binlog, and repeated GTID transactions will not be executed. When we execute statements and create them, the GTID has already been executed once on the computer.
–skip-gtids
slowlog Slow Log
3.1 Function
A tool log that records the slow-running statements during the operation of MySQL, and helps us optimize by recording them in a text file.
3.2 Configuration Method
3.2.1 Parameter Configuration:
mysql>select @@slow_query_log; #Whether to enable;
±—————–+
| @@slow_query_log |
±—————–+
| 1 |
±—————–+
mysql> select @@slow_query_log_file; #Log file storage location;
±———————-+
| @@slow_query_log_file |
±———————-+
| HOWELL-L-slow.log |
±———————-+
mysql> select @@long_query_time; #Threshold for identifying slow statements, default is 10s;
±——————+
| @@long_query_time |
±——————+
| 10.000000 |
±——————+
mysql> select @@log_queries_not_using_indexes; #Queries not using indexes, default is off;
±——————————–+
| @@log_queries_not_using_indexes |
±——————————–+
| 0 |
±——————————–+
3.2.2 Modify Configuration
vim /etc/my.cnf
show_query_log= 1
slow_query_log_file= /data/3306/db01-slow.log
long_query_time= 1
log_queries_not_using-indexes= 1
# Restart the database to take effect
3.2.3 Slow Statement Analysis
mysqldumpslow -s c -t 5 db01-slow.log
-s c: Sorted by execution times
-t 5: Top 5, the top 5
Then use explain and desc to view the execution plan;
3.2.4 Extended pt-query-digest + Amemometer Visualization Demonstration
Chapter 8 Backup Recovery================================================================================================================================================================================
MySQL Data Damage
1.1 Physical Damage
Disk Damage: Hardware, bad sectors, dd, formatting;
File Damage: Data file damage, log file damage;
1.2 Logical Damage
drop
delete
truncate
update
DBA Operation and Maintenance Personnel’s Responsibilities in Backup and Recovery
2.1 Design Backup and Disaster Recovery Strategies
2.1.1 Backup Strategy
Backup Tool Selection
Backup Cycle Setting
Backup Monitoring Method
2.1.2 Disaster Recovery Strategy
Backup: What to backup?
Architecture: High availability, demonstration of standby database, disaster recovery database
2.2 Regular Backup and Disaster Recovery Checks
2.3 Regular Fault Recovery Drills
2.4 Fast and Accurate Recovery in Case of Data Corruption
2.5 Data Migration Work
Backup tools
3.1 Logical Backup Method: Backup SQL statements
mysqldump (MDP)
mydumper (self-expansion)
load data in file (self-expansion)
Master-slave method
3.2 Physical Backup Method: Backup data files
sports betting tutorialThe latest plan
MySQL Enterprise Backup (Enterprise Edition)
Percona Xtrabackup (PBK, XBK)
3.3 Architecture Backup Method
mysqldump (MBP) Application
4.1 Introduction
Logical backup tool. Backup SQL statements at the time of backup.
Scenarios: Small data volume, within 100G, 1-2 hours;
Advantages: High readability, high compression ratio, no need to download and install, and distributed backup can be adopted in distributed architectures (data scale);
Disadvantages: The backup time is relatively long, and the recovery time is even longer.
4.2 Backup methods
4.2.1 InnoDB tables
InnoDB can take snapshot backup methods (no need to lock tables). Open an independent transaction, obtain the latest consistent snapshot. Place the snapshot data in a temporary table, convert it to SQL statements (create database, create table, insert), and save it to the sql file.
4.2.2 Non-InnoDB tables – do not support transactions
Table locking is required for backup. Trigger FTWRL, global table lock, convert to SQL, and save to sql.
4.3 Core parameters of mysqldump
4.3.1 Parameters related to connection
-u: Database user
-p: Password
-h: Set the server name or IP to connect to
-P: Set the port
-S: Socket file for connecting to the server
It can be seen that mysqldump supports remote backup.
4.3.2 Backup parameters
-A: Full backup –mysqldump -uroot -p123 -S /tmp/mysql.scok -A > /data/backup/full.sql ##windows##C:UsersHowell.L > mysqldump -uroot -p123 -S -A > D:databackupfull.sql;
-B: Backup one or more databases –mysqldump -uroot -p123 -B world bindb > D:databackupworld_bindb.sql;
-S: If the socket file location is not specified, MySQL will automatically look in the tmp directory; if it is located elsewhere, it needs to be specified;
: Backup a single or multiple tables –mysqldump -uroot -p123 -S world city country > D:databackupworld_country_city.sql;
Let’s verify: mysqldump -uroot -p123 -B world > > D:databackupdb1.sql and
The difference between mysqldump -uroot -p123 world > D:databackupdb1.sql:
The result is the same, but the first statement implies two more statements than others: create database world; use world; The second statement backs up the data table, and when the second statement is applied: if the world database does not exist, it needs to be created manually, and use the world database.
4.3.3 Advanced Backup Parameters
–master-data= 2 (very important) is generally considered a necessary parameter in actual production.
Scenario: If a full backup is performed at 23:00 every Sunday and binlog backups are performed every day. On Wednesday, someone deleted the database:
Recovery: Full backup + All needed binlog recovery.
Pain point: The截取 of binlog;
Starting point: It’s quite difficult
End point: The position before the drop.
Solution: 1. Split the log at the beginning of the backup. -F Refresh the log at the beginning of the backup (one database at a time, generally not used as a parameter); mysqldump -uroot -p123 -A -F> /data/backup/full2.sql
2. Automatically record the current log file status information at the beginning of the backup –master-data= 2.
Parameter introduction: #mysqldump –help:
This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn –lock-all-tables on, unless –single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump; don’t forget to read about –single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns –lock-tables off.
=1 : Write as commands to the backup file;
=2 : Write as comments to the backup file
Function:
1. Automatically record binlog information during backup;
2. Automatic table locking and unlocking
3. When used with single transaction, it can reduce the time of table locking.
Example: mysqldump -uroot -p123 -A –master-data= 2 >data/backup/full.sql
–single-transaction is generally considered a necessary parameter in production
Official description: mysqldump –help
Creates a consistent snapshot by dumping all tables in a single transaction. It works ONLY for tables stored in storage engines that support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. While a –single-transaction dump is in progress, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. This option automatically turns off –lock-tables.
Function:
For InnoDB engine table backup, open an independent transaction to obtain a consistent snapshot for backup. (Extended: Hot backup – reduce the impact on the database during the data backup process). If this parameter is not added, a global lock table will be performed, see –master-data.
Example:
mysqldump -uroot -p123 -A –master-data= 2 –single-transaction> /data/backup/full.sql
-R -E –triggers is also essential in the production process
-R Backup stored procedures and functions during the backup process
-E Backup events
–triggers Backup triggers
Example: mysqldump -uroot -p123 -A –master-data=2 –single-transaction -R -E –triggers > /data/backup/full.sql
–max_allowed_packet=64M Must add parameters, note that data is transmitted from the server to the client during backup
±———————+
| @@max_allowed_packet |
±———————+
| 4194304 |
±———————+
Set the maximum transmission size of the data packet; if it exceeds the size, an error 1153-Got a packet bigger than ‘max_allowed_packet’ bytes will be reported
Example: mysqldump -uroot -p123 -A –master-data=2 –single-transaction -R -E –triggers –max_allowed_packet=64M
Fault recovery case based on mysqldump + binlog
5.1 Scenario
Basic environment: CentOS 7.6 + MySQL 5.7.38, LNMT website business, data volume of 100G, 5-20M growth per day;
Backup strategy: mysqldump full backup every day, binlog backup at scheduled intervals;
Fault simulation: Simulate data failure at 10 am on Wednesday morning, for example: the core business database is mistakenly deleted;
5.2 Recovery strategy
Maintenance page hang; Find the test database, perform data recovery (MySQL is for overwrite recovery, so find the test database for recovery); Restore the full backup on Tuesday; Cut the full backup on Tuesday —–> binlog before 10 am on Wednesday when the data was mistakenly deleted, and recover; Test whether the business functions are normal; Restore business: 1. Restore the fault database to the original production; 2. Directly use the test database as production, first run it;
5.3 Simulating data corruption and recovery Simulating original data
create database test1 charset utf8mb4;
use test1;
create table t1(id int);
begin;
insert into t1 values(1),(2),(3);
commit; Simulating full backup on Tuesday evening
#mysqldump -uroot -p -A –master-data=2 –single-transaction -R -E –triggers –max_allowed_packet=64M > D:databackup est.sql Simulating data changes on Wednesday morning
use test1;
create table t2(id int);
begin;
insert into t2 values(1),(2),(3);
commit; Destroy
drop database test1; Start Recovery
5.1 Check Full Backup
– CHANGE MASTER TO MASTER_LOG_FILE=‘HOWELL-L-bin.000030’, MASTER_LOG_POS=1736;
5.3 Restore Full Backup
source D:\databackup\rest.sql; (This does not need set sql_log_bin= 0, as this statement is automatically added in the backup)
Or mysql -uroot -p< D:\databackup\rest.sql 5.4 Log Extraction Get the start point: grep ‘-- CHANGE MASTER TO’ Get the end point: mysql>show master status;
show binlog events in ‘HOWELL-L-bin.000030’;
Positional Extraction:
mysqlbinlog –skip-gtids –start-position=1736 –stop-position= 1989 C:\ProgramData\MySQL\MySQL Server 8.0\Data\HOWELL-L-bin.000030> binlog.sql
GTID Extraction
mysqlbinlog –skip-gtids –include-gtids= ‘1a79e16f-cc98-11e9-b42b-80fa5b693575:15-16’ C:\ProgramData\MySQL\MySQL Server 8.0\Data\HOWELL-L-bin.000030> binlog.sql> binlog2.sql
5.5 Restore binlog
mysql>set sql_log_bin= 0;
mysql>source binlog.sql;
mysql>set sql_log_bin= 1;
Percona Xtrabackup
7.1 Install
7.1.1. Install dependent packages: wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
7.1.2 Download software and install
7.2 Introduction
Physical backup tool, copy data files, much faster than mysqldump; native support for full backup and incremental backup.
InnoDB tables:
Hot backup: backup can be performed when business is normal, a backup method with less impact;
Checkpoint: refresh committed data pages to disk, record an LSN number; copy InnoDB table-related files (ibdata, frm, ibd); if new data changes occur during the backup period, redo will also be backed up.
2, Non-InnoDB tables:
Warm backup: backup during normal business operation, a backup method with less impact; Lock table backup (global lock). FTWRL, trigger global lock; Copy non-InnoDB data; Unlock; Re-count LSN numbers, write to a dedicated file, record binary log position for saving. All backup files are stored in a single directory.
7.3 XBK Application – Full Backup and Recovery
7.3.1 The prerequisite database must be started; able to connect to the database;
Specify socket in the my.cnf configuration file, need to add it to the configuration file,
[client]
socket=/tmp/mysql.sock is the default and will read [mysqld]—-> datadir=xxxxxx server-side tool, cannot be backed up remotely;
7.3.2 Use Full Backup
#innobackupex –user=root –password=123 /data/xbk
#innobackupex –user=root –password=123 –no-timestamp /data/xbk/full_‘date +%F’ ##Control the output folder name
7.3.3 View Backup Results
Need to pay special attention to xtrabackup_binlog_info (record the position information of binlog after backup, convenient for binlog截取 position point),
xtrabackup_checkpoints (record LSN during backup process, convenient for incremental backup)
7.3.4 Full Backup Recovery Drills
destroy
pkill mysqld
rm -rf /data/3306/* #destroy
Backup processing: prepare (must be performed before recovery, principle: redo roll forward, undo roll back, mimic CSR (automatic fault recovery) process, )
innobakupex –apply-log /data/xbk/full_2021-03-31/
Data recovery:
cp -a /data/xbk/full_2021-03-31/* /data/3306/
Authorization:
chown -R mysql.mysql /data/*
Start database
/etc/init.d/mysqld start
7.4 Incremental backup of xbk
7.4.1 Description:
During backup:
1. Incremental backups must depend on full backups;
2. Each incremental backup must refer to the LSN number of the last backup, and the data pages changed on this basis must be backed up; in addition, the new changes generated during the backup process will also be backed up.
During recovery:
1. All necessary INC backups (incremental backups) must be merged in order into the full backup, and each backup must be prepared.
7.4.3 Incremental Backup Drill
1. Create environment
create database xbk charset utf8mb4;
use xbk;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
2. Simulate Sunday full backup
innobackupex –user=root –password=123 –no-timestamp /data/backup/full
3. Simulate Monday data changes
use xbk;
create table t2(id int);
insert into t2 values(1),(2),(3);
commit;
4. Simulate Monday evening incremental backup inc1
innobackupex –user=root –password=123 –no-timestamp –incremental –incremental-basedir=/data/backup/full /data/back/inc1
Parameters:
–incremental: Switch for incremental backup;
–incremental-basedir: The base directory for incremental backup, the path of the last backup;
5. Simulate Tuesday data changes
use xbk;
create table t3(id int);
insert into t3 values(1),(2),(3);
commit;
6. Simulate Tuesday evening incremental backup inc2
innobackupex –user=root –password=123 –no-timestamp –incremental –incremental-basedir=/data/backup/inc1 /data/back/inc2
7. Simulate Wednesday data changes
use xbk;
create table t4(id, int);
insert into t4 values(1),(2),(3);
commit;
8. Create chaos at 10 am on Wednesday
pkill mysqld
rm -rf /data/3306/*
9. Confirm the integrity of the backup
Check the checkpoint, the from_lsn of the incremental backup = last_lsn of the base file – 9 (internal reason); then it is complete.
10. xbk full+ inc+ binlog backup recovery
a. Recovery concept
1. Merge and reorganize (prepare) all inc backups to full backup,
2. Restore the data, start the database,
3. Cut the binary log,
4. Restore the log;
b. Recovery process
1. Merge, prepare all inc backups to full backup
innobackupex –apply-log –redo-only /data/backup/full # Basic full backup reorganization
2. Merge, prepare inc2 to full
innobackupex –apply-log –redo-only –incremental-dir=/data/backup/inc1 /data/backup/full
3. Merge, prepare inc2 to full, check if the checkpoint is correct
innobackupex –apply-log –incremental-dir=/data/backup/inc2 /data/backup/full
4. Reorganize the whole again (prepare)
innobackupex –apply-log /data/backup/full
5. Restore 1: Modify the mysql data path – repair data to Tuesday night
chown -R mysql.mysql /data/backup/full #
Change the data path in the configuration file my.cnf datadir=/data/backup/full
Restart the database
6. Restore the log
Starting point: /data/backup/inc2/xtrabackup_binlog_info
End point: file end
mysqlbinlog –skip-gtids –start-position=1629 /data/binlog/mysql-bin.000020 /data/binlog/mysql-bin.000021> tmp/bin.sql
7. Restore the截取文件
mysql -uroot -p
set sql_log_bin=0;
source tmp/bin.sql;
set sql_log_bin=1;
7.4.4 Little surprise***
Thought-provoking question: Total data volume of 30T, 10 businesses, 10 databases, 500 tables, at 10 am on Wednesday, the core business table taobao.t1 of 20G was mistakenly dropped, causing the business of taobao database to fail to run normally;
Backup strategy: full on Sunday, incremental and binlog complete from Monday to Friday.
How to quickly recover without affecting other business? Method: migrate tablespace
Tip:
alter table taobao.t1 discard tablespace;
alter table tabobao.t1 import tablespace;
Chapter 9 Master-Slave Replication (Replication) ==================================================================================================================================================================
Introduction
Two or more database instances, through binary logs, to achieve a “synchronization” relationship of data;
Prerequisite for master-slave replication (or the setup process)
#Time synchronization
#At least two or more instances, with role division
#Master enables binlog
#Network is smooth
#Different serverid
#Enable the dedicated replication thread
#Enable the dedicated replication user
#”Catch up”, if the slave lags behind the master’s data, then the slave needs to make up for the missing data
#Confirm the replication starting point
Summary:
a. You need two or more instances, time synchronization, network is smooth, and serverid should be different, in order to distinguish different roles (master and slave);
b. The master needs to enable binlog and create a dedicated replication user;
c. The slave needs to “catch up” in advance, compensating for the missed data;
d. Slave: connection information to the master, and confirm the starting point of replication;
e. The slave needs to open a dedicated replication thread.
Implement the master-slave replication setup
3.1 Prepare the instance
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
netstat -tuinp
3.2 Check key information
Check the serverid
mysql -S /tmp/mysql3307.sock -e “select @@serverid”
mysql -S /tmp/mysql3308.sock -e “select @@serverid”
mysql -S /tmp/mysql3309.sock -e “select @@serverid”
3.3 Check Master’s Binlog
mysql -S /tmp/mysql3307.sock -e “select @@log_bin”;
3.4 Establish Replication User on Master
mysql -S /tmp/mysql3307.sock -e “grant replication slave on . to repl@’10.0.0.%’ identified by ‘123’”
mysql -S /tmp/mysql3307.sock -e “select user,host from mysql.user” — Confirm if the user exists
3.5 Backup and Restore Master to Slave
mysqldump -S /tmp/mysql3307.sock –A –master-data=2 –single-transaction> /tmp/all.sql
mysql -S /tmp/mysql3308.sock< /tmp/all.sql mysql -S /tmp/mysql3309.sock< /tmp/all.sql 3.6 Inform the Replication Information of the Slave CHANGE MASTER TO MASTER_HOST="10.0.0.51", MASTER_USER='repl', MASTER_PASSWORD="123", MASTER_PORT=3307, MASTER_LOG_FILE="mysql-bin.000002", MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10; grep "-- CHANGE MASTER TO" /tmp/all.sql -- Obtain the position information of the MASTER_LOG_FILE mysql -S /tmp/mysql3308.sock -- Inform 3308 CHANGE MASTER TO MASTER_HOST="10.0.0.51", MASTER_USER='repl', MASTER_PASSWORD="123", MASTER_PORT=3307, MASTER_LOG_FILE="mysql-bin.000002", MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10; mysql -S /tmp/mysql3309.sock -- Inform 3309 CHANGE MASTER TO MASTER_HOST="10.0.0.51", MASTER_USER='repl', MASTER_PASSWORD="123", MASTER_PORT=3307, MASTER_LOG_FILE="mysql-bin.000002", MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10; 3.7 Enable Dedicated Service Thread mysql -S /tmp/mysql3308.sock start slave; mysql -S /tmp/mysql3309.sock start slave; 3.8 Verify Master-Slave Status mysql -S /tmp/mysql3308.sock -e "show slave statusG"| grep Runing: mysql -S /tmp/mysql3309.sock -e "show slave statusG"| grep Runing: 3.9 If it cannot be set up, you can execute the following command to redo it mysql -S /etc/mysql3308.sock -e "stop slave;reset slave all;" mysql -S /etc/mysql3309.sock -e "stop slave;reset slave all;" Principle of Master-Slave Replication Check if synchronization is possible mysql -S /tmp/mysql3307.sock mysql -S /tmp/mysql3308.sock create database test charset utf8mb4; show databases; 4.1 Resources involved in Master-Slave Replication 4.1.1 File Master: binlog; Slave: relay-log: The slave stores the accepted binlog, by default in the slave data directory, manual definition method: show variables like "%relay%"; relay_log_basename= /data/3308/data/db01-relay-bin; master.info: Information about the connection to the master, as well as the position of the binlog received, stored by default in the slave data path; Modify master_info_repository=FILE/TABLE to change the storage method of master.info, file or table; relay-log.info: Records the position of the relay-log accessed by the slave, the default location is under the slave data path at relay-log.info, like master.info, it can be customized for location, as well as modify the file storage method. 4.1.2 Thread Resources Master thread: Binlog_dump_Thread: Function: Used to accept slave requests and deliver binlog to the slave; show processlist; -- You can see this thread Slave thread: IO thread: request and accept logs; SQL thread: replay relay-log; mysql -S /tmp/mysql3309.sock -e "show slave statusG"| grep Runing: -- You can see these two threads 4.2 Master-Slave Replication Principle 1.Slave: CHANGE MASTER TO: IP,PORT,USER,PASSWORD,Binlog point is written to the M.info file, start_slave(starts SQL and IO); 2.Slave: Connects to the master database, 3.Master: Allocates Dump_Thread, specifically communicates with the IO thread of the Slave, one for each slave, and it exists all the time; 4.Slave: The IO_Thread requests a new binlog; 5.Master: The Dump_thread intercepts the log and returns it to the slave, the slave IO_Thread accepts the request; 6.Slave: The IO_Thread accepts the binlog placed in the TCP/IP cache, at this time, the network layer returns ACK to the master; Master work is completed; 7.Slave: The IO_Thread writes the binlog into the relay-log and updates M.info; IO_Tread thread work is completed; 8.SQL: The SQL thread reads R.info to get the last executed position point; 9.Slave: The SQL thread executes the new relay-log backward and updates R.info again; 10.Slave: Relay-log Parameters: relay_log_purge=ON, regularly delete the applied relay-log; 11.Master: Real-time monitoring of binlog changes in the master database through the Dump thread. If there are new changes, send a signal to the slave. Master-Slave Monitoring 5.1 Master show process list; show slave hosts; 5.2 Slave show slave statusG Master-related Information: From M.info: Master_Host:10.0.51 Master_Port:3307 Connect_Retry:10 Master_Log_File:mysql-bin.000002 Read_Master_Log_Pos:619 Master-Slave Failure Analysis and Handling 6.1 Monitoring Methods show slave statusG Slave_IO_Running:Yes Slave_SQL_Running:Yes Last_IO_Errno: Last_IO_Error： Last_SQL_Errno:0 Last_SQL_Error: 6.2 IO Thread Failure 6.2.1 Normal State Slave_IO_Running:Yes 6.2.1 Abnormal State Slave_IO_Running:No/Connecting 6.2.2 Fault cause Connecting to the master: network, port, firewall; User, password, authorization issues: replication slave; Master connection limit: The default connection limit can be obtained through select @@max_connections; ±------------------+ | @@max_connections | ±------------------+ | 151 | ±------------------+ The gap between database versions: 5.7 uses native, 8.0 uses sha2. Fault simulation: Thread management in master-slave: mysql>start slave; – start all threads
mysql>stop slave; – close all threads
mysql>start slave sql_thread; – start the SQL thread separately
mysql>start slave io_thread; – start the IO thread separately
mysql>stop slave sql_thread; – close the SQL thread separately
mysql>reset slave all – remove the slave identity
Simulate network, port, firewall errors:
Last_IO_Errno:1045
Last_IO_Error:error connecting to master ‘repl@10.0.0.51:3307’-retry-time:10 retries:1
General fault handling approach:
1. Manually connect to the master through the replication user
mysql -urepl -p123456 -h10.0.0.51 -P3307
Request/accept log:
Incomplete binary log of the master: damaged, discontinuous…
Issues with the starting point request of the slave…
The serverid (server_uuid) between master and slave is the same…
relaylog issues…
Fault simulation:
6.3 SQL thread fault
6.3.1 Main work of the SQL thread
Playback the relay-log log, playback in the background, and execute the SQL in the relay-log.
6.3.2 Reason
6.3.2 Fault simulation
1. Create database test1 in the slave first;
2. Create database test1 in the master first;
3. Check the status of the slave SQL thread Slave_SQL_Running:No
4. Method one to deal with the fault, taking the master as the standard:
Reverse the operation of the slave, restart the thread:
myxql>drop database test1;
mysql>start slave;
5. Method two to deal with the fault, taking the slave as the main one
Skip this error:
Method 1. stop slave;
set global sql_slave_skip_counter= 1; — Skip errors, if this method is to be used, make sure that the master-slave data is consistent at this time;
Method 2. /etc/my.cnf — automatic jump
slave-skip-errors= 1032,1062,1007;
Common error codes: 1007-Object already exists; 1032-Unable to execute SQL; 1062-Primary key conflict or constraint conflict;
6.4 How does the slave become the master?
1. Repair to the latest status
2. Cancel the slave identity mysql>reset slave all;
3. Clear all binlog log information
6.5 Preventing Write to the Slave
1. Slave is read-only
mysql>select @@read_only # Normal user mode
mysql>select @@super_read_only # Normal administrator read-only
2. Read-Write Separation Middleware
User requests for operations will pass through middleware, write operations will be分流 to the master database, and read operations will be分流 to the slave database.
Analysis and Treatment of Master-Slave Latency Issues
7.1 What is Master-Slave Latency
When the master database occurs an actionable event, the slave takes a long time to catch up
7.2 How to Monitor Master-Slave Latency
mysql>shou slave status; — Rough evaluation
Seconds_Behind_Master:0 # The time the slave lags behind the master, can be determined whether there is or is not latency; Qualitative; does not mean there is no latency if it is equal to 0. A more accurate indicator for evaluating master-slave latency is how much log volume is delayed – log volume evaluation
That is: the comparison of the amount of logs executed by the master with the amount of logs executed by the slave; Log volume (master-slave position points) compared to the position point of relay execution
By comparing the position points in relay-log.info of the slave with the position points in show master status of the master, you can know the specific amount of delayed log;
7.3 Reasons for Master-Slave Replication Latency on the Master
a. External reasons: Network, hardware configuration, busy master database business (can split business – vertical or horizontal splitting; splitting large transactions), too many slave databases (usually three to four slave databases, but multi-level slave databases can be established – suitable for read more and write less);
b. Internal factors: i) Binary log updates (not timely, affecting the sync_binlog parameter, solution: sync_binlog=1 (double one));
ii) Before version 5.7, without GTID enabled, the Dump thread serially transmits binlog, but the master database can handle many transactions in parallel, leading to many transactions waiting to be transmitted (After GTID is enabled, transactions achieve global uniqueness and can be concurrently transmitted binlog, but they are still affected by large transactions and the issue of lock contention);
c. How to judge if the master database is not transmitting in time?
1. mysql>seconds_behind_master; — master database
2. mysql>show master status; — master database #cat master.info or mysql>show slave status; — slave database
a. External reasons: network, slave configuration is too low (mainly depends on memory and IO), parameters may differ;
b. Internal factors: i) IO thread: write relay-log-> depends on IO performance,
ii) SQL thread: Single-threaded playback of master database SQL; Serial playback in non-GTID mode (Solution: Ensure execution order, depends on GTID, must enable GTID, parallel playback SQL; 5.6+ GTID: database level, concurrent SQL threads based on database level; 5.7+ GTID: logical_clock logical clock, ensures transaction order at the same database level, supports concurrent playback based on transaction level, MTS); Even with built-in optimization mechanisms, attention must still be paid to the handling of large transactions, which requires reducing the size of large transactions, and also attention must be paid to locking issues.
Chapter 10 Advanced Progression of Master-Slave Replication======================================================================================================================================================================
Application of special slave databases
1.1 Delayed slave database
Function: Ordinary master-slave replication can only help us solve physical faults. If the master database appears drop database operation, the delayed slave database extends playback time (SQL) for a certain period of time, which can handle logical damage;
1.1.2 Configuration: Configure in the slave database
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY= 300;
mysql>start slave;
mysql>show slave statusG
SQL_DELAY: 300
SQL_Remaining_Delay:NULL
1.1.3 Fault simulation and recovery
a. Simulation data
create database ys charset utf8mb4;
use ys;
create table t1(id int);
begin;
isnert into t1 values(1),(2),(3);
commit;
drop database ys;
b. Recovery Thinking
1. First stop the business, hang the maintenance page;
2. Stop the slave SQL thread; mysql>stop slave sql_thread; Check the position information in relay-log.info;
mysql>stop slave;
3. Append the missing half of the subsequent log to the slave:
Log position: relay-log.info
Append range: relay.info ±–>DROP ±–>after
4. Directly migrate business to the slave;
c. Recovery Process
1. Stop SQL thread, shut down the slave
mysql>show slave status; –Slave
mysql>show master status; –Master
mysql>stop slave sql_thread; –Slave
mysql>stop slave; –Slave can wait a while, waiting for the transfer to continue;
2. Extract relay-log
Starting point: Relay_Log_File:db01-relay-log.000002
Relay_Log_Pos:482
Or: cat /data/3308/data/relay-log.info can also find the starting point;
End point: mysql>show relaylog events in ‘db01-relay-log-bin.000002’; –Find the position before drop, only observe the Pos column, End_log_pos corresponds to the position in the binlog of the master
mysqlbinlog –start-position 482 –stop-position 1402 /data/3308/data/db01-relay-bin.000002 >tmp/relay.sql;
3. Recovery of the Slave
mysql>set sql_log_bin=0;
mysql>source tmp/relay.sql;
mysql>set sql_log_bin=1;
1.2 Filtering Replication
1.2.1 Configuration Method
Master configuration: Modify in the configuration file, used less
mysql>show master status;
binlog_do_db – whitelist
binlog_ignore_db – blacklist
Slave configuration: Configure in the slave, commonly used (binlog logs will be transmitted, but the SQL thread only executes the filtered relaylog)
mysql>show slave status;
replication_do_db= –whitelist at database level
replication_ignore_db= –blacklist
replication_do_table = –table level
replication_ignore_table=
replication_wild_do_table= world.t* –fuzzy table level
replication_wild_ignore_table=
Set replication_do_db= test1
replication_do_db= test2 to write to the slave configuration file –vim /data/3309/my.cnf
Restart the database systemctl restart mysql3309
1.3 Semi-synchronous replication: has become historical
Classical replication: In the traditional asynchronous non-GTID replication mode, it will cause the data in the slave to be inconsistent;
To ensure the consistency of master-slave data in version 5.5, the semi-synchronous replication component (plugin) was added to allow the relaylog of the slave to be written to disk first, and then the application-level ACK to the master’s plugin is transmitted through the plugin. After receiving it, the transaction on the master can be committed successfully. The default timeout is 10s without returning ACK, then automatically switch to traditional asynchronous replication; Some good features were also added in 5.6 and 5.7, but they still cannot ensure the complete consistency of master-slave data; If the business is more concerned about the consistency of master-slave data, it is recommended to use MGR architecture or PXC and other consistency architectures;
1.4 GTID replication
Function: Mainly to ensure the advanced features of master-slave replication;
Introduction: By default, GTID is not enabled in 5.6, and even if it is not enabled, anonymous GTID records will be generated; it can enable parallel transmission of DUMP and provide concurrent playback of SQL threads; from version 5.7.17 onwards, it is basically GTID mode;
Set up GTID replication:
1. 3 master-slave virtual machines
IP: 51/52/53
Hostname: db01/dn02/db03
Firewall off, to enable remote xshell connection
Cleanup environment (for all 3 nodes)
pkill mysqld
rm -rf /data/3306/*
rm -rf /data/binlog/* to generate configuration files (for all 3 nodes, note the server_id)
#db01
cat>/etc/my.cnf<
EOF Initialize data (3 nodes do more)
mysqld –initialize-insecure –user=mysql –basedir=app/database/mysql –datadir=/data/3306 to start the database
/etc/init.d/mysqld start to build master and slave, create user (db01) on the master database
mysql>grant replication slave on . to repl@’10.0.0.%’ identified by ‘123’; Start master and slave on the slave
52/53:
mysql> change master to
master_host=’10.0.0.51′,
master_user=’repl’,
master_password=’123′,
MASTER_AUTO_POSITION=1;
start slave;
show slave status;
show master status;
If there is data in the master database, it is necessary to back up the data to the slave first; Note: For GTID master and slave replication, when it is turned on for the first time, read the last GTID of the relaylog and read the GTID_PURGE parameter to confirm the starting point of the parameter
Advantages: Ensure transaction global consistency; easier to capture logs, cross multiple files, easier to determine the starting and ending points; easier to judge the master and slave working status; transmission of logs, can be transmitted concurrently, SQL replay can be high concurrency; master and slave replication is more convenient master_auto_position=1
Master and slave architecture evolution
2.1 Native Support:
Single master and single slave, single master and multiple slaves, multi-level master and slave, dual master structure, delayed slave, filtered replication, MGR group replication (5.7.17+);
2.2 Non-native:
2.2.1 Security: High Availability
Yearly failure rate:
99% general grade
99.9% ordinary grade
99.99% quasi-high availability grade MHA architecture
99.999% financial grade ClusterInnoDB ClusterMGCOracle RACsysabse cluster architecture
99.9999% exceeding financial grade
2.2.2 Performance
Read more, write less: Read-write splitting Representative products: Altas, ProxySQL, Mycat…
Everything in abundance: Distributed solutions Representative products: Altas-sharing, Mycat(DBLE)…
Chapter 11 High Availability and Read-Write Splitting=======================================================================================================================================================================
Introduction to MHA high availability architecture
1.1 Requirements for master-slave architecture
More than 3 nodes, 1 master and 2 slaves, cannot be multiple instances; multi-node SSH mutual trust (To simplify the SSH process, certificate-based authentication is adopted, eliminating the need to enter account and password during SSH login)
1.2 Software structure
Master: Management software
masterha_manager Start MHA
masterha_check_ssh Check the SSH configuration of MHA
masterha_check_repl Check the MySQL replication status
masterha_master_monitor Check if the master is down
masterha_check_status Check the current MHA running status
masterha_master_swithch Control the failover (automatic or manual)
masterha_conf_host Add or delete the configured server information
Node: Managed software
save_binary_logs Save and replicate the master’s binary logs
apply_diff_relay_logs Identify the differential relay log events and apply the differences to others
purge_relay_logs Clear the relay logs (will not block the SQL thread)
MHA infrastructure planning and implementation
2.1 Planning
Master: 51 db01 node
Slave: 52 db02 node
53 db03 node manager
2.2 Prepare the environment (1 master and 2 slaves with GTID replication)
2.3 Configure the soft link of key programs (all nodes)
ln -s /app/database/mysql/bin/mysqlbinlog /user/bin/mysqlbinlog
ln -s /app/database/mysql/bin/mysql /user/bin/mysql
2.4 Configure mutual trust between nodes (key pair)
db01:
rm -rf /root/.ssh
ssh-keygen
sc /root/.ssh
mv id_rsa.pub authorized_keys
scp -r /root/.ssh 10.0.0.52:/root
scp -r /root/.ssh 10.0.0.53:/root
Node verification
db01:
ssh 10.0.0.51 data
ssh 10.0.0.52 data
ssh 10.0.0.53 data
db02:
ssh 10.0.0.51 data
ssh 10.0.0.52 data
ssh 10.0.0.53 data
db03:
ssh 10.0.0.51 data
ssh 10.0.0.52 data
ssh 10.0.0.53 data
2.5 Install software
Download MHA software:
MHA official website:
GitHub download address: https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
Note:
1. Version 8.0 cannot be used with this version, you need to change the password encryption mode (sha2—–>native)
2. You need to use MHA software version 0.58+;
Install Node software dependencies on all nodes
yum install perl-DBD-MySQL -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes rpm -ivh mha4mysql-node-0.56-0.e16.noarch.rpm
Create MHA required user in db01 master database
grant all privileges on . to mha@’10.0.0.%’ identified by ‘mha’;
2.6 Manage software installation (db03)
Create configuration file directory
mkdir -p /etc/mha Create log directory
mkdir -p /var/log/mha/app1 Edit MHA configuration file
cat>/etc/mha/app1.cnf <
2.9 Check MHA status
masterha_check_status –conf=/etc/mha/app1.cnf
High availability:
What it is best at is solving physical damage for us
Firstly: Start MHA;
Monitoring: Data node, mainly monitor the master
Through the masterha_master_monitor heartbeat detection script, monitor the master and check ssh connectivity at the same time; Default to probe four times, every ping_interval=2 seconds, if the master has not had a heartbeat, it is considered that the master has failed, and enter the failover fault transfer process; Master selection strategy:
a. Set the priority in advance (administrative subjective behavior):
If the candidate_master=1 parameter is added when the node is configured, it will be prioritized as the master; If the standby master’s log volume is far behind the master master (100M), it will not be selected as a new master either. At this time, you can add the parameter check_repl_delay=0 to not check the log lag behind the master master;
For example, add candidate_master=1 under [server1] in app1.cnf;
b. The log volume is closest to the master’s
c. If the log volume is the same, select the new master according to the order of the configuration file; Log compensation: catch up with the missing data
Situation 1: ssh is connected, immediately save the missing part of the log to the slave (under the var/tmp directory) and repair it using asve_binary_logs;
Situation 2: Unable to connect to ssh, try to minimize losses as much as possible. Perform relaylog log difference calculation and processing through (apply_diff_relay_logs); Switching of master-slave identity:
a. All slaves cancel the replication relationship with the original master database
mysql>stop slave;
mysql>reset slave all;
b. The new master database and the remaining slave databases reconstruct the master-slave relationship, and the faulty database is automatically removed;
Remove the information of the faulty database from the configuration file using the masterha_conf_host script; MHA is a one-time high availability, after failover, the Manager automatically exits, and MHA needs to be restarted; The shortcomings are:
a. Data compensation;
b. Automatic reminder;
c. Self-healing function: under development; MHA+K9s+Operator, 8.0 MGR+mysqlsh
d. Application transparency; vip drift
Implement transparent (vip) function
4.1 Description:
Can only be used in the same data center, cannot cross data centers or networks;
4.2 Configure parameters(db03)
vim /etc/mha/app1.cnf
master_ip_failover_script=/user/local/bin/master_ip_failover #Add under server_default in the configuration file
4.3 Modify the script content(db03)
cp master_ip_failover.txt /user/local/bin/master_ip_failover #master_ip_failover.txt can be downloaded online
vim /user/local/bin/master_ip_failover
my $vip=‘10.0.0.55/24’; #vip address
my $key=‘1’ #
my key $vip”;
my key down”;
Trick to convert Chinese characters to Unix dosunix /user/local/bin/master_ip_failover
Add execution permission chmod +x /user/local/bin/master_ip_failover
4.4 Manually bind the first vip address on the master database (db01)
ifconfug ens33:1 10/0.0.24
4.5 Restart MHA(db03)
masterha_stop –conf=/etc/mha/app1.cnf
nohup masterha_manager –conf=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null> var/log/mha/app1/mamager.log 2>&1 &
4.6 Check MHA status
masterha_check_status –conf=/etc/mha/app1.cnf
binlog server(db03)
5.1 Parameters
vim /etc/mha/app1.cnf
[binlog1]
no_master=1 #Do not consider this node when selecting the master, do not participate in the master selection
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog #Note that the position of master_binlog_dir in the node should not be the same, as it will overwrite
5.2 Create necessary paths
mkdir -p /data/mysql/binlgo #Create path
chown -R mysql.mysql /data/* #Authorization
5.3 Pull the master binlog log
cd /data/mysql/binlog #Must enter the directory you created
mysqlbinlgo -R –host=10.0.0.51 –user=mha –password=mha –raw –stop-never mysql-bin.000001 & #It is necessary to cd to the directory first, as long as the master does not fail, it will run continuously
#Note: The starting point for pulling logs needs to be based on the binary logs already obtained by the current slave
5.4 Restart MHA
masterha_stop –conf=/etc/mha/app1.cnf
nohub masterha_manager –conf=/etc/mhaapp1.cnf –remove_dead_master_conf –ignore_last_failover< /dev/null> /var/log/mha/app1/manager.log 2>&1 &
Email reminder
6.1 Parameters
report _script=/user/local/bin/send
6.2 Prepare the email script
send_report
Prepare the email script to /user/local/bin;
cd mail
cp -a /user/local/bin
chmod +x /user/local/bin/ Add the prepared script to the mha configuration file to make it call it;
6.3 Modify the MHA configuration file, call the email script
vim /etc/mha/app1.cnf
report_script=/user/local/bin/send
6.4 Restart MHA
masterha_stop –conf=/etc/mha/app1.cnf
nohub masterha_manager –conf=/etc/mhaapp1.cnf –remove_dead_master_conf –ignore_last_failover< /dev/null> /var/log/mha/app1/manager.log 2>&1 &
Test the function of MHA
7.1 Failure test
Test and view vip, check the switch log to see if the fault database is removed and the master-slave status
7.2 Master node failure
/etc/init.d/mysqld stop
7.3 View the /var/log/mha/app1/manager file
vim /var/log/mha/app1/manager.log
Fault repair ideas
8.1 Check the process status
(db03) ps -ef |grep manager
masterha_check_status –conf/etc/mha/app1.cnf
8.2 Check the node status of the configuration file
cat /etc/mha/app1.cnf
If a node is missing, it means the switch process has succeeded and the node has been removed; if all are still there, it means the switch process is stuck in the middle; then check the log /var/log/mha/app1/manager
8.3 Repair fault database(Repair)
8.4 Repair master-slave
After repairing the fault database, manually add it to the existing master as a slave;
CHANGE MASTER TO
master_host=’10.0.0.52′,
master_user=’repl’,
master_password=’213′,
MASTER_AUTO_POSITION=1;
start slave;
show slave status;
8.5 Repair the configuration file, restore to the original state
8.6 Check the mutual trust of SSH and the master-slave relationship of repl
masterha_check_ssh –conf=/etc/mha/app1.cnf
masterha_check_repl –conf=/etc/mha/app1.cnf
8.7 Repair binlog server(Note which is the master database)
cd /data/mysql/binlog
rm -rf *
mysqlbinlog -R –host=10.0.0.52 –user=mha –password=mha –raw –stop-never mysql-bin.000001 & #Note to use the binary file of the new master database show master statusG
8.8 Check the VIP status of the master node, see if it is on the master node
(db02) ip a
If not, generate manually
ifconfug ens33:1 10.0.0.55/24
8.9 Start MHA
masterha_check_status –conf=/etc/mha/app1.cnf
MHA high availability application
MHA infrastructure + binlogserver + VIP + Sendreport
9.1 MHA high availability architecture transformation
Historical architecture: pain points of MHA infrastructure application
a. Does not support VIP, the application end can only connect to the cluster through the master database IP; if the master database is down, the application end configuration needs to be manually modified.
b. If the master database is down overall, SSH connection cannot be established, it is very likely to lose some transactions because there is no binlogserver
c. No fault reminder function, administrators cannot respond in time; architecture transformation plan
a. Add binlogserver log compensation
b. VIP function application transparent
c. sendreport timely remind the administrator of their responsibilities
a. MHA switch can respond quickly, fix the architecture, because MHA is one-time
b. MHA optimization: try to shorten the MHA failover event
Monitoring: modifying the ping_interval parameter can reduce the monitoring time
Master election: force master election candidate_master=1
check_repl_delay=0
Log compensation: binlogserver
The log compensation phase is the most influential on the speed of Faliover, so fundamentally reducing the log compensation time, in the end, it is to reduce the master-slave delay, which is the main direction; it needs to consider GTID, lock, large transactions, SQL concurrent threads, and dual one
Atlas read-write separation middleware application
10.1 Download and Installation
Download: https://github.com/Qihoo360/Atlas/releases
Installation: rpm -ivh Atlas-2.2.1.e16.x86_64.rpm # Installed under db03
10.2 Configuration
cd /user/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
cat> test.cnf <
mysql>select @@server_id #Read operation test, execute multiple times, can see the round-robin query
mysql>begin; select @@server_id; commit; #Write operation test, can see write operations are executed on 51
10.5 Atlas management db03
mysql>mysql -uuser -ppwd -h 10.0.0.53 -P2345 #Log in to the administrator account
mysql>select * from help; #View all Atlas management commands
10.6 Management Commands
select * from help; #Help commands
select * from backends; #List backend node status and information
set offline 3 #Offline, node 3 (backend_ndx) temporarily set to offline
set online 3 #Online
add master $backend
add slave 10.0.0.53:3306 #
remove backend 3 #Remove node 3 from Atlas, temporary, will not affect the configuration file
select * from pwds #View user
add pwd $pwd
add enpwd $pwd
remove pwd $pwd
10.7 Enterprise user management
The first step is to add the user to the database master node
mysql>grant all on . to user1@‘10.0.0.%’ identified by ‘123’; The second step is to add the user in atlas:
add pwd user1:123; #Encrypt in plain text
mysql>add enpwd user1:O2jBXONX098= #Encrypt and save to the configuration file, permanent effect:
mysql>save config;
Chapter 13 MySQL Distributed Architecture========================================================================================================================================================================
Prepare the basic environment
1.1 Prepare the environment:
Two virtual machines db01 db02
Did not create 4 MySQL instances: 3307, 3308, 3309, 3310
1.2 Delete the historical environment in two virtual machines
pkill mysqld
rm rf /data/33{07…10}
mv /etc/my.cnf /etc/my.cnf.bak
1.3 Initialize data in two virtual machines separately
mkdir /data/33{07…10}/data -p
mysqld –initialize-insecure –user=mysql –datadir=/data/3307/data –basedir=/app/database/mysql
mysqld –initialize-insecure –user=mysql –datadir=/data/3308/data –basedir=/app/database/mysql
mysqld –initialize-insecure –user=mysql –datadir=/data/3309/data –basedir=/app/database/mysql
mysqld –initialize-insecure –user=mysql –datadir=/data/3310/data –basedir=/app/database/mysql
1.4 Prepare the Configuration File
db01=========================================================================================
[mysqld]
basedir=/app/database/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >data/3308/my.cnf<
10.0.0.51:3309 ———> 10.0.0.51:3307
10.0.0.52:3309 ———> 10.0.0.51:3307
#shard1
10.0.0.52:3308 <--------> 10.0.0.51:3308
10.0.0.52:3310 ———> 10.0.0.51:3308
10.0.0.51:3310 ———> 10.0.0.51:3308
1.7 Configuration begins
#shard1
#10.0.0.51:3307 <--------> 10.0.0.52:3307
#db02
mysql -S /data/3307/mysql.sock -e “grant replication slave on . to repl@’10.0.0.%’ identified by ‘123’;”
mysql -S /data/3307/mysql.sock -e “grant all on . to root@’10.0.0.%’ identified by ‘123’ with grant option”
#db01
mysql -S /data/3307/mysql.sock -e “change master to MASTER_HOST=’10.0.0.52′,MASTER_PORT=3307,MASTER_AUTO_POSITION=1,MASTER_USER=’repl’,MASTER_PASSWORD=’123′;”
mysql -S /data/3307/mysql.sock -e “start slave;”
mysql -S /data/3307/mysql.sock -e “show slave statusG”
#db02
mysql -S /data/3307/mysql.sock -e “change master to MASTER_HOST=’10.0.0.51′,MASTER_PORT=3307,MASTER_AUTO_POSITION=1,MASTER_USER=’repl’,MASTER_PASSWORD=’123′;”
mysql -S /data/3307/mysql.sock -e “start slave;”
mysql -S /data/3307/mysql.sock -e “show slave statusG”
1.8 Master-slave status detection
mysql -S /data/3307/mysql.sock -e “select slave statusG”| grep Running:
mysql -S /data/3308/mysql.sock -e “select slave statusG”| grep Running:
mysql -S /data/3309/mysql.sock -e “select slave statusG”| grep Running:
mysql -S /data/3310/mysql.sock -e “select slave statusG”| grep Running:
1.9 If an error occurs in the middle, execute the following command on each node and then restart from 1.7
mysql -S /data/3307/mysql.sock -e “stop slave;reset slave all;”
mysql -S /data/3308/mysql.sock -e “stop slave;reset slave all;”
mysql -S /data/3309/mysql.sock -e “stop slave;reset slave all;”
mysql -S /data/3310/mysql.sock -e “stop slave;reset slave all;”
Introduction and evolution of distributed architecture
Database vertical splitting—>Database horizontal splitting—>Database application splitting Mycat application
More Stories
