Shulou(Shulou.com)06/03 Report--

Disable mail notifications:

Vi / etc/profile

Add at the end

# disable email prompt unset MAILCHECK

Database deployment and engine management

Brief introduction of database

Technical composition of database

1. Database system DBS

a. Database management system (DataBase Management System, DBMS): SQL (RDS): ORACLE, Oracle MySQL, MariaDB, Percona server, DB2 NoSQL: Redis, MongoDB, Memcache

B.DBA database administrator

2.SQL language (Structured Query Language is structured query language)

A. DDL statement database definition language: database, table, view, index, stored procedure, function, CREATE DROP

ALTER / / developer

B. DML statement database manipulation language: insert data INSERT, delete data DELETE,drop, update data

UPDATE / / developer

C. DQL statement database query language: query data SELECT

D. DCL statement database control language: for example, control user's access rights GRANT, REVOKE

Grant all on. Root to root@localhost identified by '123'

3. Data access technology

A.ODBC PHP

B.JDBC JAVA

Master and slave

Master IP

Database classification relationship: mysql, Oracle, IBM, Microsoft

Non-relational databases: mogoDB, redis, memcache (database cache server) 1 name

2 key

Database deployment

YUM installation of Mysql

1/52

Mysql's official website: www.mysql.com mysql.org

Click downloadshttps and click mysql community 1 at the bottom. Download mysql's yum repository

[root@22e34e653991 /] # wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 2. Install yum repository of mysql [root@22e34e653991 /] # rpm-ivh mysql80-community-release-el7-3.noarch.rpm

3. Modify the installation version (method 1)

[root@22e34e653991 /] # yum repolist all | grep mysql to view all libraries about mysql

[root@22e34e653991 /] # toolkit for yum- y install yum-utils yum

[root@22e34e653991 /] # yum-config-manager-- the yum source library that enable mysql57-community will disable is enabled

[root@22e34e653991 /] # yum-config-manager-- disable mysql80-community disables the enabled yum source library

4. Install the database

[root@22e34e653991 /] # yum-y install mysql mysql-server

Modify yum warehouse (method 2)

5. View the initial password of the database

[root@22e34e653991 /] # grep 'password' / var/log/mysqld.log

2019-07-13T15:14:31.176905Z 1 [Note] A temporary password is generated for root@localhost: k12zPB1rten 2Ta

6. Log in with a password

[root@22e34e653991 /] # mysql-u root-pamphk12zPB1ritt2Ta'

7. Method 1 of changing password:

Mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY' QianFeng@123'

Method 2:

[root@22e34e653991 /] # mysqladmin-u root-pamphk12zPB1rterter2Ta password 'QianFeng@123'

Password: numbers with special characters in upper and lower case

Mysql logs in with a weak password

Edit the configuration file for mysql

[root@mysql1 mysql] # vim / etc/my.cnf [mysqld]

Basedir=/usr/local/mysql

Weak password strength can be set after datadir=/usr/local/mysql/data validate_password=off is added.

Lower_case_table_names=1 is not case sensitive

Compilation and installation of Mysql

Source code installation

Compared to the RPM distribution, if we choose to install from the source code, we will be able to make more and more flexible adjustments to MySQL during the installation process. Because by compiling the source code, we can:

A) choose a suitable compiler for your hardware platform to optimize the compiled binaries

B) adjust the relevant compilation parameters according to different software platform environments

C) choose what components are needed and what components are not needed for our specific application scenario

D) according to the content of the data we need to store, choose to install only the character set we need

2/52

E) multiple MySQL can be installed on the same host

F) and other adjustments that can be made according to specific application scenarios.

While the source installation gives us more flexibility, it also brings us hidden dangers that may be introduced:

A) improper use of compilation parameters due to lack of understanding of compilation parameters may make the compiled binaries unstable

B) the optimization parameters used due to misunderstanding of one's own application environment may lead to worse system performance.

C) another small problem that cannot be called a hidden trouble is that the source code compilation and installation will make the installation and deployment process more complex and take longer

Mysql deployment

1. Prepare the compilation environment

Yum-y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make cmake

two。 Prepare the source code package (www.baidu.com; official website)

3. Empty the system residue and create a new account userdel-r mysql

Yum-y remove mariadb mariadb-libs mariadb-server mariadb-devel rm-rf / etc/my*

Rm-rf / var/lib/mysql rm-rf / var/log/mysql*

4. Environment preparation groupadd mysql

Useradd-r-g mysql-s / bin/nolgin mysql

5. Decompression

Tar xvf mysql-boost-5.7.26.tar.gz

6. Configuration

[root@mysql-5.7.26] # cmake. \-DWITH_BOOST=boost_1_59_0/\-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DSYSCONFDIR=/etc\ specifies the location of the installation directory configuration file. The default is etc-DMYSQL_DATADIR=/usr/local/mysql/data\ data directory error log file-DINSTALL_MANDIR=/usr/share/man\ directory of help documentation-DMYSQL_TCP_PORT=3306\ default port number 3306

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock\ is used for network communication.-DDEFAULT_CHARSET=utf8\ default character set is generated only when it is started.

-DEXTRA_CHARSETS=all\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_READLINE=1\ can flip the history command-DWITH_SSL=system\-DWITH_EMBEDDED_SERVER=1\ embedded server

-DENABLED_LOCAL_INFILE=1\ supports importing-DWITH_INNOBASE_STORAGE_ENGINE=1 from native.

Default storage engine

Tip: boost can also be downloaded automatically using the following instructions

-DDOWNLOAD_BOOST=1

7. Compile

Make

8. Install make install

9. Initialization

Cd / usr/local/mysql deleting this is equivalent to uninstalling

3/52

Mkdir mysql-files

Chown-R mysql.mysql / usr/local/mysql

. / bin/mysqld-initialize-user=mysql-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data

Initialize, only need to initialize once

10. Start MySQL- using mysqld_safe bin/mysqld_safe-- user=mysql & (run in the background)

. / bin/mysqladmin-u root-p 'original password' password 123

11. Use client-side testing

[root@mysql1 bin] #. / bin/mysql-u root-p 'password'

twelve。 Create a database profile

[root@mysql1 bin] # vim / etc/my.cnf [mysqld]

Basedir=/usr/local/mysql

Datadir=/usr/local/mysql/data

Compile configuration file

Cmake. \-DWITH_BOOST=boost/boost_1_59_0/\-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\-DSYSCONFDIR=/etc\-DMYSQL_DATADIR=/usr/local/mysql/data\-DINSTALL_MANDIR=/usr/share/man\-DMYSQL_TCP_PORT=3306\-DMYSQL_UNIX_ADDR=/tmp/mysql.sock\-DDEFAULT_CHARSET=utf8\-DEXTRA_CHARSETS=all\-DDEFAULT_COLLATION=utf8_general_ci\-DWITH_READLINE=1\-DWITH_SSL=system\ -DWITH_EMBEDDED_SERVER=1\-DENABLED_LOCAL_INFILE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1

Expansion

Add environment variables:

Echo "export PATH=$PATH:/usr/local/mysql/bin" > > / etc/profile

Let the environment variable take effect

Source / etc/profile

Set up boot boot:

Cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld centos 6:

Chkconfig-add mysqld chkconfig mysqld on

4/52

Service mysqld start

Mysql Foundation

Compile and install:

[root@47ed2bec974d mysql] # ls

COPYING README bin include mysql-test support-files COPYING-test README-test docs lib share

1. Bin directory

Used to place executable files, such as mysql, mysqld, mysqlbinlog, etc. 2. The include directory is used to place some header files, such as mysql.h, mysql_ername.h and so on. 3. Lib directory

Used to place a series of library files.

4. Share directory

Used to store character set, language and other information.

Yum installation:

/ var/lib/mysql stores data files / usr/share/mysql is used to store character sets, languages and other information.

Database storage engine

The database storage engine is the underlying software organization of the database. The database management system (DBMS) uses the data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing skills, locking levels and other functions, using different storage references.

Engine, you can also get specific functions. Many different database management systems now support a variety of different data engines. The core of MySQL is the storage engine.

InnoDB Storage engine:

InnoDB is the preferred engine for transactional databases, supporting transaction security tables (ACID), row locking and foreign keys; InnoDB is the default

MySQL engine

InnoDB features:

Support for transaction processing, foreign keys, crash repair and concurrency control. If you need to have a higher requirement for transaction integrity (than

Such as banks), requires concurrency control (such as ticket sales), then choosing InnoDB has a great advantage. If you need to update and delete databases frequently, you can also choose InnoDB because transaction commit (commit) and rollback (rollback) are supported.

MyISAM Storage engine: (understand)

MyISAM is based on and extends the ISAM storage engine. It is one of the most commonly used storage engines in Web, data warehousing and other application environments. MyISAM has high insertion and query speed, but does not support transactions.

MyISAM features:

Fast data insertion and low space and memory usage. If the table is mainly used to insert new records and read records, then selecting MyISAM can achieve high processing efficiency. It can also be used if the integrity and concurrency requirements of the application are relatively low.

MEMORY Storage engine (understanding)

The MEMORY storage engine stores the data in the table in memory, and provides fast access without querying and referencing other table data.

MEMORY features:

All the data is in memory, and the processing speed of the data is fast, but the security is not high. If you need to read and write very fast, for the data

The security requirement is low, so you can choose MEMOEY. It has requirements on the size of the table and cannot create too large a table. Therefore, such databases are only used in relatively small database tables.

Engine function comparison:

5/52

Click me click me click me click me

Power and energy MYISAM Memory InnoDB

Storage limit 256TB RAM 64TB

Support No No Yes for things

Support for full-text indexing Yes No No

Number index Yes Yes Yes is supported

Support hash indexing No Yes No

Support for data cache No NumberA Yes

Foreign key No No Yes is supported

How to choose an engine:

If you want to provide transaction security (ACID compatibility) with commit, rollback and crash recovery capabilities, and require concurrency control, InnoDB is a good choice; if the data table is mainly used for inserting and querying records, the MyISAM engine can provide higher processing efficiency; if you only temporarily store data, the amount of data is small and do not need high data security, you can choose the Memory engine that keeps the data in memory. In MySQL, the engine is used as a temporary table to store the intermediate results of the query; if there are only INSERT and SELECT operations, you can choose

Archive,Archive supports highly concurrent insert operations, but is not transaction-safe by itself. Archive is ideal for storing archived data, such as logging information using Archive.

Which engine to use requires flexible choice, multiple tables in a database can use different engines to meet a variety of performance and practical needs, using an appropriate storage engine will improve the performance of the whole database.

Storage engine View:

Mysql > show engines

+-+-

| | Engine | Support | Comment | Transactions | XA | Savepoints | |

+-+-

| | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | |

| | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| | BLACKHOLE | YES | / dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| | MyISAM | YES | MyISAM storage engine | NO | NO | NO | |

| | CSV | YES | CSV storage engine | NO | NO | NO | |

| | ARCHIVE | YES | Archive storage engine | NO | NO | NO | |

| | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | |

| | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | |

+-+-

9 rows in set (0.00 sec)

The value of the Support column indicates whether an engine can be used: YES means it can be used, NO means it cannot be used, and DEFAULT indicates that the engine is the current default storage engine.

Mysql > alter table service engine=innodb

Mysql transaction

6/52

MySQL transaction

MySQL transactions are mainly used to deal with data with large amount of operations and high complexity. For example, in the personnel management system, if you delete a person, you need to delete not only the basic information of the person, but also the information related to that person, such as mailboxes, articles, etc., so that these database operation statements constitute a transaction!

In MySQL, only databases or tables that use the Innodb database engine support transactions.

Transactions can be used to maintain the integrity of the database, ensuring that batches of SQL statements are either executed or not executed.

Transactions are used to manage insert,update,delete statements

Generally speaking, a transaction must satisfy four conditions (ACID): atomicity (Atomicity, or indivisibility), consistency (Consistency), isolation (Isolation, also known as independence), and persistence (Durability).

Atomicity: all operations in a transaction are either completed or not completed, and do not end in the middle

Link. An error occurs during the execution of a transaction and is Rollback back to its state before the transaction starts, as if the transaction had never been executed.

Consistency: the integrity of the database is not compromised before the transaction starts and after the transaction ends. This means that the data written must fully comply with all the preset rules, including the accuracy and concatenation of the data, and that the subsequent database can spontaneously complete the scheduled work.

Isolation: the ability of a database to allow multiple concurrent transactions to read, write and modify its data at the same time. Isolation can prevent the concurrency of multiple transactions.

During execution, the data is inconsistent due to cross execution. Transaction isolation is divided into different levels, including read uncommitted (Read uncommitted), read commit (read committed), repeatable read (repeatable read) and serialization (Serializable).

Persistence: after the transaction is completed, the modification of the data is permanent, even if the system failure will not be lost.

Under the default setting of the MySQL command line, transactions are committed automatically, that is, the COMMIT operation is performed immediately after the SQL statement is executed. So to explicitly open a transaction, you must use the command BEGIN or START TRANSACTION, or execute the command SET AUTOCOMMIT=0 to disable autocommit using the current session.

There are two main methods of MYSQL transaction processing:

1. Implement it with BEGIN, ROLLBACK and COMMIT.

BEGIN starts a transaction

ROLLBACK transaction rollback

COMMIT transaction confirmation

2. Directly use SET to change the automatic submission mode of MySQL:

SET AUTOCOMMIT=0 forbids automatic submission

SET AUTOCOMMIT=1 enables auto-submission

Show variables like 'autocommit'; / / check whether the modification is successful

Note: when writing an application, it is best to give control of the transaction to the developer

Table management and data types

Data type

Classification:

7/52

Numeric type string type

Time and date type

Numeric type: TINYINT SMALLINT MEDIUMINT INT BIGINT

Integer type

Function: used to store the user's age, Level of the game, experience value, etc.

Type size range (signed) range (unsigned)

TINYINT 1 byte (- 128127) (0255)

SMALLINT 2 bytes (- 32 768, 32 767) (0, 65,535)

MEDIUMINT 3 bytes (- 8 388 608) (0meme 16 777 215)

INT or INTEGER 4 bytes (- 2 147 483 648 min2 147 483 647) (0pr 4 294 967 295)

BIGINT 8 Bytes (- 9 recollection 223) 372 744 073 709 551 6

Floating point type FLOAT DOUBLE

Function: used to store the user's height, weight, salary, etc.

Float (5.3) 5 width 3 Precision

Width is not a decimal point

Mysql > create table T12 (id float (6jie 2))

Mysql > insert into T1 values ('2.22')

Click me click me click me click me

FLOAT 4 bytes (- 3.402 823 466 Ely38), 0, (1.175 494351 Emuri 38 3.4020, (1.175 49494)

823 466 351 Egg 38)

DOUBLE 8 bytes (- 1.797 693 134 862 315 7 Elaine 308 mai 2.225 073 858 507 201 4 Emuri 308), 0,0, (2.225 073

(2.225 073,858 507 201 4 Emuri 308 Magi 1.797 693 134 862 315 7 Elaine 308)

Fixed-point number type DEC fixed-point number is stored as a string inside MySQL, which is more accurate than floating-point number and is suitable for representing data with high precision such as currency.

Bit type BIT BIT (M) can be used to store multi-bit binary numbers, M range from 1 to 64, if not written default is 1 bit

String type:

CHAR series CHAR VARCHAR

TEXT series TINYTEXT TEXT MEDIUMTEXT LONGTEXT

BLOB series TINYBLOB BLOB MEDIUMBLOB LONGBLOB

BINARY series BINARY VARBINARY

Enumeration type: SET ENUM

Collection type:

Type, size, use

CHAR 0-255byte fixed length string

VARCHAR 0-65535 byte variable length string

TINYBLOB 0-255Bytes binary string with no more than 255characters

TINYTEXT 0-255byte short text string

BLOB 0-65,535 byte long text data in binary form

TEXT 0-65,535 bytes of long text data

Enumerated type: enumerated columns can store non-repeating strings into a predefined collection mysql > create table enum_table (e ENUM ('fish','apple','dog'))

Query OK, 0 rows affected (0.35 sec)

Mysql > insert into enum_table (e) values ('fish'); Query OK, 1 row affected (0.11 sec)

Mysql > select * from enum_table; +-+

8/52

| | e | +-+ | fish | +-+ |

1 row in set (0.00 sec)

Mysql > insert into enum_table (e) values ('nihao')

ERROR 1265 (01000): Data truncated for column 'e'at row 1

Time and date type: DATETIME DATETIME TIMESTAMP YEAR

Function: it is used to store the registration time of users, the release time of articles, the update time of articles, the entry time of employees, etc.

Type size range format

(bytes)

DATE 3 1000-01-01 Compact 9999-12-31 YYYY-MM-DD

TIME 3'- 838 59 race 59 HH:MM:SS

YEAR 1 1901/2155 YYYY

DATETIME 8 1000-01-01 00 YYYY-MM-DD-12-31 00 YYYY-MM-DD

TIMESTAMP 4 1970-01-01 00 YYYYMMDD

The end time is the 2147483647 second, Beijing time 2038-1-19 11:14:07 GMT

03:14:07 on January 19, 2038

Create table T8 (

Id1 timestamp NOT NULL default CURRENT_TIMESTAMP

Id2 datetime default NULL

);

Mysql > desc T8

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id1 | timestamp | NO | | CURRENT_TIMESTAMP |

| | D2 | datetime | YES | | NULL |

+-+ +

2 rows in set (0.01sec)

Columns of type timestamp also have a feature: by default, when insert, update data, the timestamp column automatically changes to the current time

CURRENT_TIMESTAMP fill / update. "automatic" means that if you leave it alone, MySQL will handle it for you.

Mysql > insert into T8 (id1) values ('20180109000000')

Mysql > select * from T8

+-+ +

| | id1 | D2 | |

+-+ +

| | 2018-01-09 00:00:00 | NULL |

+-+ +

1 row in set (0.00 sec)

Extend:

Select now (); view the current time

Table operation

MySQL table operation DDL

Table is the basic unit of database to store data, which is composed of several fields and is mainly used to store data records. The operations of the table include:

Create tables, view tables, modify tables, and delete tables.

9/52

These operations are the most basic and important operations in database management. This section includes:

Create table create table

View the table structure desc table, show create table modify table alter table

Copy the table create table...

Delete table drop table

First, create a table (basic operation of the table) Table: school.student1

Field field field

Id name sex age

1 tom male 23 record

2 jack male 21 record

3 alice female 19 record

Syntax:

Create table table name (custom) (

Field name 1 type [(width) constraint], field name 2 type [(width) constraint], field name 3 type [(width) constraint]

) [storage engine character set]; = in the same table, field names cannot be the same = width and constraints are optional = field names and types are required

Mysql > CREATE DATABASE school; / / create database school

Mysql > use school

Mysql > create table student1 (

-> id int

> name varchar (50)

-> sex enum ('masking dwelling f')

-> age int

->)

Query OK, 0 rows affected (0.03 sec)

View the table (current library)

Mysql > show tables

+-+

| | Tables_in_school |

+-+

| | student1 |

+-+

1 row in set (0.00 sec)

Insert content into the table

Syntax:

Insert into table name (field 1, field 2... ) Values (list of field values.)

Mysql > insert into student1 (id,name,sex,age) values

View table structure

Mysql > desc student1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | YES | | NULL |

| | name | varchar (50) | YES | | NULL |

| | sex | enum ('masking dagger') | YES | | NULL | |

| | age | int (11) | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

10/52

Mysql > select id,name,sex,age from student1; / / query the values of all fields in the table

Empty set (0.00 sec)

Mysql > select * from student1; / query the values of all fields in the table

Empty set (0.00 sec)

Mysql > select name,age from student1; / / query the value of the specified field in the table

Empty set (0.00 sec)

Mysql > insert into student1 values (1), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2) (2), (2) (2), (2) (2), (2) (2), (2) (2), (2) (2), (2), (2), (2), (2), (2), (2), (2), (2)

Sequential insertion

Query OK, 3 rows affected (0.14 sec)

Records: 3 Duplicates: 0 Warnings: 0

Mysql > insert into student1 (name,age) values ('zhuzhu',10), (' gougou',20); / / insert values into specified fields only

Query OK, 2 rows affected (0.12 sec)

Records: 2 Duplicates: 0 Warnings: 0

Table school.student2

Field name data type

Serial number id int

Name name varchar (50)

Year of birth born_year year

Birthday birthday date

Class time class_time time

Registration time reg_time datetime

Mysql > create table student2 (id int

Name varchar (50), born_year year, birthday date, class_time time, reg_time datetime)

Mysql > desc student2

Mysql > insert into student2 values (1), now (), now (), now ()

Mysql > insert into student2 values. (2) jackhammer, 1982, 1982, 1982, 1120, 123000, 20140415162545)

Table school.student3

Id id int

Name name varchar (50)

Gender sex enum ('male','female')

Like hobby set ('music','book','game','disc')

Mysql > create table student3 (id int

Name varchar (50)

Sex enum ('male','female')

Hobby set ('music','book','game','disc'))

Mysql > desc student3

Mysql > show create table student3\ G

Mysql > insert into student3 values; mysql > insert into student3 values; mysql > select * from student3

Second, view the table structure

DESCRIBE view table structure

11/52

DESCRIBE table name

DESC table name

SHOW CREATE TABLE View Table detail structure

SHOW CREATE TABLE table name

Table integrity constraints: used to ensure the integrity and consistency of data

Constraint description

PRIMARY KEY (contention) identifies the field as the primary key of the table, can uniquely identify the record, and cannot be empty UNIQUE +

NOT NULL

FOREIGN KEY (FK) identifies the field as the foreign key of the table and implements the table and table (parent table primary key / child table 1 foreign key / child table 2 foreign key)

The connection between

NOT NULL indicates that the field cannot be empty

UNIQUE KEY (UK) identifies that the value of this field is unique, can be empty, and can have multiple UNIQUE KEY in a table

AUTO_INCREMENT identifies that the value of this field grows automatically (integer type and primary key)

DEFAULT sets the default value for this field

Description:

1. Whether empty is allowed. Default NULL. NOT NULL can be set. Fields are not allowed to be empty and must be assigned a value.

two。 Whether a field has a default value. The default value is NULL. If the field is not assigned a value when inserting a record, this field uses the default value.

Sex enum ('male','female') not null default' male' age int unsigned NOT NULL default 20 must be positive (unsigned). Null is not allowed. Default is 20.

Whether it is key primary key primary key

Foreign key forengn key

NOT NULL

Table school.student4

Mysql > create table school.student4 (id int not null

Name varchar (50) not null

Sex enum ('mast mast') default'm' not null, age int unsigned default 18 not null

Hobby set ('music','disc','dance','book') default' book,dance')

Mysql > insert into student4 values; Query OK, 1 row affected (0.00 sec)

Mysql > select * from student4

Mysql > insert into student4 (id,name) values (2 row affected); Query OK, 1 row affected (0.00 sec)

Mysql > insert into student4 values (3 null name' cannot be null); ERROR 1048 (23000): Column 'book'

Set unique constraint UNIQUE

The establishment of MySQL index is very important for the efficient operation of MySQL, and the index can greatly improve the retrieval speed of MySQL.

Table company.department1

CREATE TABLE company.department1 (

Dept_id INT

Dept_name VARCHAR (30) UNIQUE

Comment VARCHAR (50)

);

Mysql > desc department1

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | dept_id | int (11) | YES | | NULL |

12/52

| | dept_name | varchar (30) | YES | UNI | NULL |

| | comment | varchar (50) | YES | | NULL |

+-+ +

Set the primary key constraint PRIMARY KEY

The value of the primary key field does not allow repetition and does not allow NULL (UNIQUE + NOT NULL)

Single column as primary key table school.student6 method 1

Mysql > create table student6 (

Id int primary key not null auto_increment, name varchar (50) not null

Sex enum ('male','female') not null default' male', age int not null default 18

);

Query OK, 0 rows affected (0.00 sec)

Mysql > insert into student6 values (1)

Mysql > insert into student6 (name,sex,age) values ('jack','male',19)

('tom','male',23)

Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0

Mysql > select * from student6

-+

| | id | name | sex | age |-+ |

| | 1 | alice | female | 22 | |

| | 2 | jack | male | 19 |

| | 3 | tom | male | 23 |-+ |

3 rows in set (0.00 sec)

Set field value to increase AUTO_INCREMENT table company.department3

CREATE TABLE department3 (

Dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR (30)

Comment VARCHAR (50))

4. Modify table ALTER TABLE

Syntax:

Modify table name

ALTER TABLE table name RENAME new table name

Add field

ALTER TABLE table name

ADD field name data type [integrity constraints …]

ADD field name data type [integrity constraints …]

ALTER TABLE table name

ADD field name data type [integrity constraints …] AFTER field name

Delete a field

ALTER TABLE table name DROP field name

Modify field

ALTER TABLE table name

MODIFY field name data type [integrity constraints …]

13/52

ALTER TABLE table name

CHANGE old field name new field name old data type [integrity constraints …]

ALTER TABLE table name

CHANGE old field name new field name new data type [integrity constraints …]

Example:

Modify the storage engine

Mysql > alter table service

-> engine=innodb; / / engine=myisam | memory |.

Add a field

Mysql > create table student10 (id int)

Mysql > alter table student10

-> add name varchar (20) not null

-> add age int not null default 22

Mysql > alter table student10

-> add stu_num int not null after name; / / after adding the name field

Mysql > alter table student10

Add sex enum ('male','female') default' male' first; / / added to the front

Delete a field

Mysql > alter table student10

-> drop sex

Mysql > alter table service

-> drop mac

Modify field type modify

Mysql > alter table student10

-> modify age tinyint not null; / / pay attention to keeping the original constraints

Mysql > alter table student10

-> modify id int not null primary key; / / modify field types, constraints, and primary keys

Add constraint (add auto_increment for existing primary key) mysql > alter table student10 modify id int not null primary key auto_increment; / / error, this field is already primary key

ERROR 1068 (42000): Multiple primary key defined

Mysql > alter table student10 modify id int not null auto_increment; Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Increase the primary key mysql > alter table student1

-> add primary key (id)

7. Add primary keys and auto-grow mysql > alter table student1

-> modify id int not null primary key auto_increment

8. Delete primary key [primary key auto_increment]

a. Delete self-increasing constraint

Mysql > alter table student10 modify id int not null

b. Delete the primary key mysql > alter table student10

-> drop primary key

14/52

Copy the table

Replicate table structures + records (key does not copy: primary keys, foreign keys and indexes) replicate table structures / records + table structures, do not copy Key mysql > create table new_service select * from service

Copy only the table structure

Mysql > create table new1_service select * from service where 1 / 2; / / the condition is false and no record can be found.

You can copy the primary key, only the table structure mysql > create table T4 like employees

Delete the table

DROP TABLE table name

Table operation (extension)

Modify the values of fields in the data table: syntax:

Update table name set column name = value where condition update student set name='123' where id=1

Delete a line: syntax:

Delete from table name where id=1 delete from type where id=1

Id name

1xingdian renren

Library operation

System database

Information_schema: virtual library, which mainly stores the information of some database objects in the system, such as user table information, column information, permission information, character information, etc.

Performance_schema: mainly stores performance parameters of the database server

Mysql: authorization library, which mainly stores the permission information of system users.

Sys: mainly stores performance parameters of the database server

Create a database: DDL 1. # mysqladmin-u root-p1 create db1

two。 Go directly to create a database directory and modify permissions

3.mysql > create database xingdian

Database naming rules: case-sensitive uniqueness

Cannot use keywords such as create select

You cannot use numbers alone.

Check the database mysql > show databases

Mysql > show create database xingdian; mysql > select database (); view the current library

15/52

Switch database mysql > use xingdian; mysql > show tables

Delete database

DROP DATABASE database name

Database query

Database query

Single table query

Simple queries are sorted by conditional queries

Limit the number of query records using set functions query grouping queries using regular expression queries

Test table: company.employee5

Employee number id int

Employee name name varchar (30)

Employee gender sex enum

Employment period hire_date date

Position post varchar (50)

Job description job_description varchar

Salary salary double (15pc.2)

Office office int

Department number dep_id int

Mysql > CREATE TABLE company.employee5 (

Id int primary key AUTO_INCREMENT not null, name varchar (30) not null

Sex enum ('male','female') default' male' not null, hire_date date not null

Post varchar (50) not null, job_description varchar (100), salary double (15jue 2) not null, office int

Dep_id int)

Mysql > insert into company.employee5 (name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000501100), (' tom','male','20180203','instructor','teach',5500501100), ('robin','male','20180202','instructor','teach',8000501100), (' alice','female','20180202','instructor','teach',7200501100) ('', 'male','20180202','hr','hrcc',600502101)

('harry','male','20180202','hr',NULL,6000502101)

('emma','female','20180206','sale','salecc',20000503102)

('christine','female','20180205','sale','salecc',2200503102)

('zhuzhu','male','20180205','sale',NULL,2200503102)

('gougou','male','20180205','sale','',2200503102)

16/52

Mysql > select field name, field name 2 from table name condition

Simple query:

Mysql > select * from employee5

Mysql > select name, salary, dep_id from employee5 where id 10000; select * from employee5 where salary > 5000 and salary select china from T1 order by china; mysql > select china from T1 order by china desc

Mysql > select china from T1 order by china desc limit 3; controls the display of the first three lines.

Mysql > select china from T1 order by china desc limit 1 order by china desc limit 3; display three lines starting with serial number 1.

Note:

Ascending American sound / descending order nd American sound / ascending descending American sound / d American American sound / descending order

Sort by multiple columns:

People with the same entry time have different salaries.

SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC

Limit the number of records in a query

SELECT * FROM employee5 ORDER BY salary DESC

LIMIT 5; / / default initial position is 0

SELECT * FROM employee5 ORDER BY salary DESC

LIMIT 0,5

SELECT * FROM employee5 ORDER BY salary DESC

LIMIT 3d5; / / starting from Article 4, a total of 5 articles are displayed.

Query using set function

Count can see how many records there are.

Select count (*) from employee5

Select count (name) from employee5

Select max (salary) from employee5; / / Department has the highest salary

Select min (salary) from employee5

Select avg (salary) from employee5

The total salary of the sale department:

Select concat ("Total Department Wages:", sum (salary)) from employee5 where post='sale'

Print details of the person with the highest salary: select * from employee5 where salary = (select max (salary) from employee5)

Group query:

GROUP BY is used with the GROUP_CONCAT () function

The department has the same ID, so spell the name together:

SELECT dep_id,GROUP_CONCAT (name) FROM employee5 GROUP BY dep_id

SELECT dep_id,GROUP_CONCAT (name) as emp_members FROM employee5 GROUP BY dep_id

GROUP BY uses department maximum salary with aggregate function

SELECT post,max (salary) FROM employee5 GROUP BY post

+-+ +

| | post | max (salary) |

+-+ +

| | hr | 6000.00 | |

| | instructor | 8000.00 | |

| | sale | 20000.00 | |

+-+ +

3 rows in set (0.07 sec)

Regular query

SELECT * FROM employee5 WHERE name REGEXP'^ ali'

SELECT * FROM employee5 WHERE name REGEXP 'yun$'

SELECT * FROM employee5 WHERE name REGEXP'm {2}'

Summary: ways to match strings

WHERE name = 'tom'

WHERE name LIKE 'to%'; _%

18/52

WHERE name REGEXP 'yun$'; ^ ${2}

Multi-table query (extension)

Links between left and right of table query

Multi-table join query compound conditional join query

First, prepare two forms

First, prepare two test tables

Table company.employee6 mysql > create table employee6 (

Emp_id int auto_increment primary key not null, emp_name varchar (50)

Age int, dept_id int)

Mysql > desc employee6

Mysql > insert into employee6 (emp_name,age,dept_id) values (', 19200)

('tom',26201)

('jack',30201)

('alice',24202)

('robin',40200)

('xingdian',16200)

('natasha',28204)

Mysql > select * from employee6

Table company.department6

Mysql > create table department6 (dept_id int

Dept_name varchar (100)

Mysql > desc department6

Mysql > insert into department6 values (200Japanese hr')

(2011pct.)

(202, recording sale')

(203 is called fd')

Mysql > select * from department6

Note:

Financial department: fd of Finance Department

Second, join query of multiple tables

Cross join: generates a Cartesian product that returns all rows in the left table without any matching conditions, and each row in the left table is combined with all rows in the right table

Inner join: connect only matching rows

External connection

Left join: all values in the left table are displayed, regardless of whether they match or not in the right table

Right join: all values in the right table will be displayed, regardless of whether they match or not in the left table.

Full outer join: contains all rows of the left and right tables

= Cross-connect =

Select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6

= Internal connection =

19/52

Only employees with departments are identified (departments that do not have natasha in the department table)

Select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6 where employee6.dept_id=department6.dept_id

Select employee6.emp_name,department6.dept_name from employee6 inner join department6 on employee6.dept_id=department6.dept_id

Outer join syntax:

SELECT field list

FROM Table 1 LEFT | RIGHT JOIN Table 2

ON Table 1. Field = Table 2. Field

Whoever uses it first is left.

= external connection (left connection left join) =

Mysql > select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id

Find out all employees and the departments to which they belong, including those who do not have departments.

= external connection (right connection right join) =

Mysql > select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id

Find out the employees included in all departments, including empty departments

20/52

= full external connection =

Mysql > select * from employee6 full join department6; +-+

| | emp_id | emp_name | dept_id | dept_id | dept_name | |

-+

| | 1 | | 200 | 200 | hr |

| | 1 | | 200 | 201 | it |

| | 1 | | 200 | 202 | sale |

| | 1 | | 200 | 203 | fd |

| | 2 | tom | 201 | 200 | hr |

| | 2 | tom | 201 | 201 | it |

| | 2 | tom | 201 | 202 | sale |

| | 2 | tom | 201,203 | fd |

| | 3 | jack | 201 | 200 | hr |

| | 3 | jack | 201 | 201 | it |

| | 3 | jack | 201 | 202 | sale |

| | 3 | jack | 201,203 | fd |

| | 4 | alice | 202 | 200 | hr |

| | 4 | alice | 202 | 201 | it |

| | 4 | alice | 202 | 202 | sale |

| | 4 | alice | 202 | 203 | fd |

| | 5 | robin | 200 | 200 | hr |

| | 5 | robin | 200 | 201 | it |

| | 5 | robin | 200 | 202 | sale |

| | 5 | robin | 200 | 203 | fd |

| | 6 | natasha | 204 | 200 | hr |

| | 6 | natasha | 204 | 201 | it |

| | 6 | natasha | 204.202 | sale | |

| | 6 | natasha | 204 | 203 | fd |-+ |

21/52

24 rows in set (0.00 sec)

Third, compound conditional join query

Example 1: query the employee6 and department6 tables within joins, and the age field value in the employee6 table must be greater than 25

Identify employees over the age of 25 in all departments of the company

Example 2: query the employee6 and department6 tables within joins and display them in ascending order of the age field

4. Sub-query

A subquery is to nest one query statement in another query statement.

22/52

The query results of the inner query statement can provide query conditions for the outer query statement.

Subqueries can contain keywords such as IN, NOT IN, etc.

You can also include the comparison operators: =,! =, >, > =, grant all on ttt.* to 'u1, local host 'identified by' QianFeng@123'; Query OK, 0 rows affected, 1 warning (0. 00 sec)

Mysql > select * from mysql.db\ G

Verify:

# mysql-UU1-pQianFeng@123 > show databases

Table level

Select * from mysql.tables_priv\ G

Grant all on k0.t1 to 'u1century localhost 'identified by' QianFeng@123'

Verify:

# mysql-UU1-pQianFeng@123 > use K0

Show tables

Column level

Select * from mysql.columns_priv\ G

Insert into mysql.columns_priv (host,db,user,table_name,column_name,column_priv) values ('10.18.44.%', 'data','ying','t1','id','select')

The premise is that there are libraries, tables, and permissions.

+ mysql + web (farm)

Root accounts are not allowed to give farm-> libraries to ordinary users of web projects (single / multiple)

Sql vulnerability injection-> out of library

User management

Note: after the permissions are modified (update,grant), the authorization table is refreshed, and the directory allows the permissions of the authorized users to take effect immediately.

MySQL user Management

1. Log in and out of MySQL

# mysql-h292.168.5.240-P 3306-u root-p123 mysql-e'select user,host from user'

-h specify hostname [default is localhost]

-P MySQL server port [default 3306]

-u specify user name [default root]

-p specify login password [default is empty password]

Here mysql is the specified login database-e connected to the SQL statement (used in the script)

Create a user

Method 1: CREATE USER statement to create mysql > create user xingdian

26/52

Mysql > create user xingdian@'%' identified by '123456login; this allows you to log in directly from the remote

Method 2: GRANT statement to create (authorize) mysql > GRANT ALL ON. TO 'xingdian'@'localhost' IDENTIFIED BY' 123456'

Mysql > grant select,insert on k1.* to admin1@'%' identified by '123'

FLUSH PRIVILEGES

View individual library permissions mysql > select * from mysql.db\ G

Note: ALL has separate permissions. Separate libraries and separate tables

If there is no authorization for xingdian@localhost users, create localhost% 10.19.40.% 10.19.40.11 3. Delete user method 1: delete DROP USER statement

DROP USER 'user1'@'localhost'

Method 2: delete the DELETE statement

DELETE FROM mysql.user WHERE user='user2' AND host='localhost'

FLUSH PRIVILEGES; refresh authorization table

Change the user's password root method to change your own password: mysqladmin-uroot-pendant 123' password' new_password' / / 123 is the old password

Method 2:

Mysql > update mysql.user set authentication_string=password ('Qianfeng123') Where user='root' and host='localhost'

Method 3:

Set a password for that user, and you have to execute it under that user

SET PASSWORD=password ('new_password')

The above method will be in a later version of remove, using the following method

SET PASSWORD='new_password'; sets the password directly

Method 1 for root to modify passwords of other users:

Mysql > SET PASSWORD FOR user3@'localhost'=password ('new_password')

The above method will be remove in the future, using the following method: mysql > SET PASSWORD FOR user3@'localhost'='new_password'

Method 2:

UPDATE mysql.user SET authentication_string=password ('new_password')

WHERE user='user3' AND host='localhost'

Ordinary users change their passwords mysql > SET password=password ('new_password')

Mysql > select * from mysql.user\ G

Mysql > alter user 'wing'@'localhost' identified by' Qianfeng123 cities

View the existing password policy mysql > SHOW VARIABLES LIKE 'validate_password%'

Parameter explanation:

1) .validate_password_dictionary_file specifies the file path for password authentication; 2) .validate_password_length password minimum length 3) .validate_password_mixed_case_count password must contain at least the number of lowercase letters and uppercase letters; 4) .validate_password_number_count password must contain at least the number of digits

5). Validate_password_policy password strength check level, corresponding to: 0/LOW, 1/MEDIUM, 2/STRONG. Default is 1 0/LOW: only check length; 1/MEDIUM: check length, number, case, and special characters

2/STRONG: check length, number, case, and special character dictionary files.

6). Validate_password_special_char_count password must contain at least the number of special characters

27/52

Retrieve the password.

Version 5.6Unip 5.7:

Mysqld-- skip-grant-tables-- user=mysql & / / skip database permission verification mysql

Mysql > UPDATE mysql.user SET authentication_string=password ('new_password') WHERE user='root' AND host='localhost'

Mysql > FLUSH PRIVILEGES; refresh authorization table

Note: error handling

Problem 1:ERROR You must reset your password using ALTER USER statement before executing this statement

Solution: ALTER USER 'root'@'localhost' IDENTIFIED BY' Xiaoming250'

Error reporting solution

Error creating user Times:

Mysql > create user 'miner'@'192.168.%' IDENTIFIED BY' miner123'

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

Reason for the error: the password is not strong enough.

Solution: (this account is a test account, so reduce the password policy) mysql > set global validate_password_policy=0

Query OK, 0 rows affected (0.00 sec)

Mysql > set global validate_password_length=4; Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW VARIABLES LIKE 'validate_password%'

+-+ +

| | Variable_name | Value |

+-+ +

| | validate_password_dictionary_file |

| | validate_password_length | 4 |

| | validate_password_mixed_case_count | 1 | |

| | validate_password_number_count | 1 | |

| | validate_password_policy | LOW |

| | validate_password_special_char_count | 1 | |

+-+ +

6 rows in set (0.00 sec)

Create the user again, successful

Error report:

Mysql > SET PASSWORD FOR xingdian@'localhost'=password ('QianFengfang 123'); ERROR 1133 (42000): Can't find any matching row in the user table

Solution:

Error report:

28/52

[root@b0505f448652] # mysqladmin-u root-p2 password'4'

Mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

Mysqladmin: unable to change password; error: 'Your password does not satisfy the current policy requirements'

Solution:

Backup and recovery

Overview

MySQL data backup

All backup data should be placed locally in a non-database, and multiple copies are recommended. Do daily recovery drills in the test environment, recovery is more important than backup.

Backup: can prevent data loss due to mechanical failure and human misoperation, such as saving database files elsewhere.

Redundancy: there are multiple redundant copies of data, but different backups can only prevent data loss due to mechanical failures, such as active / standby mode and database cluster.

Factors must be considered during the backup process:

1. Consistency of data

two。 Availability of servic

Logical backup:

The backup is the SQL statement (DDL DML DCL) executed by table building, database building, insertion and other operations, which is suitable for small and medium-sized databases, and the efficiency is relatively low. Mysqldump

Physical backup:

Directly copy database files, suitable for large database environment, not limited by storage engine, but can not be restored to different MySQL versions

Ben. Tar,cp xtrabackup lvm snapshot

Full backup

Incremental backup: new data generated from each backup from the last backup to the present

29/52

Differential backup: only backup is different from full backup

Tar data backup (physical)

Tar backup database Note: service is not available during backup

Backup process: [full physical backup]

1. Stop the database

2.tar backup data

3. Start the database

[root@slave2 ~] # systemctl stop mysqld [root@slave2 ~] # mkdir / backup [root@slave2 ~] # cd / var/lib/mysql

[root@slave2 ~] # tar-zcvf / backup/date +% F-mysql-all.tar. / *

Note: backup files should be copied to other servers or storage

30/52

The process of restore:

1. Stop the database

two。 Clean up the environment

3. Import backup data

4. Start the database

[root@slave2 ~] # systemctl stop mysqld [root@slave2 ~] # rm-rf / var/lib/mysql/* [root@slave2 ~] # cd / backup

[root@slave2] # tar-xvf / backup/2019-08-20-mysql-all.tar-C / usr/lib/mysql [root@slave2 ~] # systemctl start mysqld

Xtarbackup backup (physical)

Percona-xtrabackup physical backup + binlog

It is an open source and free software that supports hot backup of MySQL databases. It can non-blocking the databases of InnoDB and XtraDB storage engines.

Backup. It does not suspend service to create Innodb hot backups; do incremental backups for mysql; do online table migration between mysql servers; make it easier to create replication; back up mysql without increasing the load on the server.

Percona is an established mysql technology consulting company. It not only provides technical support, training and consultation for mysql, but also releases mysql

The branch version of-- percona Server. A series of mysql tools have also been released around percona Server.

Software installation

Deploy xtrabackup

31/52

# wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

# rpm-ivh percona-release-0.1-4.noarch.rpm

# yum-y install percona-xtrabackup-24.x86_64

Note: if the dependency package perl-DBD-MySQL cannot be installed,] you need to remove the percona source and use the centos source to install it separately before installing percona-xtrabackup-24.x86_64

Full backup

The full backup process creates a backup directory:

[root@xingdian full] # mkdir-p / xtrabackup/full

Backup:

[root@xingdian full] # innobackupex-- user=root-- password='QianFeng@123' / xtrabackup/full

Full backup recovery proc

1. Stop the database

two。 Clean up the environment

3. Replay rollback-- > restore data

4. Modify permissions

5. Start the database and shut down the database:

# systemctl stop mysqld

# rm-rf / var/lib/mysql/*

# rm-rf / var/log/mysqld.log

# rm-rf / var/log/mysql-slow/slow.log (delete if any, no action required)

Verify the restore before the restore:

# innobackupex-- apply-log / xtrabackup/full/2019-08-20 15-57-31 /

Confirm the database directory:

Before restoring, you need to make sure that the database directory is specified in the configuration file, otherwise xtrabackup does not know where to restore.

Cat / etc/my.cnf [mysqld] datadir=/var/lib/mysql

Restore data:

[root@xingdian mysql] # innobackupex-- copy-back / xtrabackup/full/2019-08-20 15-57-31 /

Modify permissions:

[root@xingdian mysql] # chown mysql.mysql / var/lib/mysql-R

Start the database:

[root@xingdian mysql] # systemctl start mysqld

Verify the data:

[root@xingdian mysql] # mysql-u root-pQianFeng@123 mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | K1 | |

| | mysql |

| | performance_schema |

| | sys |

+-+

Incremental backup

Incremental backup proc

Principle: each backup of the new data generated from the last backup to the present

32/52

Create a backup directory:

[root@xingdian] # mkdir-p / xtrabackup/full

1. Full backup: Monday

[root@xingdian ~] # innobackupex-- user=root-- password='QianFeng@123' / xtrabackup/full 2, incremental backup: Tuesday-Saturday

Need to create data

[root@xingdian] # innobackupex-- user=root-- password='QianFeng@123'-- incremental / xtrabackup/zeng/-- incremental-basedir=/xtrabackup/full/2019-08-20 15-57-31 /

[root@xingdian zeng] # ls 2019-08-21 00-00-08

Need to create data

[root@xingdian] # innobackupex-- user=root-- password=QianFeng@123-- incremental / xtrabackup/zeng/-- incremental-basedir=/xtrabackup/zeng/2019-08-2120 00-00-08 /

[root@xingdian zeng] # ls 2019-08-21 00-00-08 2019-08-22 00-04-11

……

Incremental backup recovery process

1. Stop the database

two。 Clean up the environment

3. Repeat rollback redo log-- > restore data in turn

4. Modify permissions

5. Start the database

[root@xingdian ~] # systemctl stop mysqld [root@xingdian ~] # rm-rf / var/lib/mysql/* rollback redo log Monday: full

[root@xingdian] # innobackupex-- apply-log-- redo-only / xtrabackup/full/2019-08-20 15-57-31 /

Tuesday-Thursday

[root@xingdian] # innobackupex-- apply-log-- redo-only / xtrabackup/full/2019-08-20 15-57-31 /-- incremental-dir=/xtrabackup/zeng/2019-08-21 00-00-08 /

[root@xingdian] # innobackupex-- apply-log-- redo-only / xtrabackup/full/2019-08-20, 15-57-31 /-- incremental-dir=/xtrabackup/zeng/2019-08-22, 00-04-11 /

……

Recover data

[root@xingdian] # innobackupex-- copy-back / xtrabackup/full/2019-08-20 15-57-31 / (datadir)

Modify permissions

[root@xingdian] # chown-R mysql.mysql / var/lib/mysql

[root@xingdian ~] # systemctl start mysqld

Verify the restore:

[root@xingdian] # mysql-u root-pQianFeng@123

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | K1 | |

| | K2 | |

33/52

| | K3 | |

| | mysql |

| | performance_schema |

| | sys |

+-+

Differential backup

Differential backup process: only backup is different from full backup

Create a backup directory:

[root@xingdian] # mkdir-p / xtrabackup/full

1. Full backup: Monday

[root@xingdian] # innobackupex-- user=root-- password=888 / xtrabackup/full

2. Differential backup: create a backup directory from Tuesday to Saturday:

[root@xingdian] # mkdir-p / xtrabackup/jian

Insert into testdb.test2 values (2)

[root@xingdian ~] # innobackupex-user=root-password=888-incremental / xtrabackup/jian-incremental-basedir=/xtrabackup/ full backup directory (Monday)

Insert into testdb.test2 values (3)

[root@xingdian ~] # innobackupex-user=root-password=888-incremental / xtrabackup/jian-incremental-basedir=/xtrabackup/ full backup directory (Monday)

Insert into testdb.test values (4)

[root@xingdian ~] # innobackupex-user=root-password=888-incremental / xtrabackup/jian-incremental-basedir=/xtrabackup/ full backup directory (Monday)

Differential backup recovery process

1. Stop the database

two。 Clean up the environment

3. Repeat rollback redo log (Monday, some difference)-- > restore data

4. Modify permissions

5. Start the database

Stop the database

[root@xingdian ~] # systemctl stop mysqld

Clean up the environment

[root@xingdian ~] # rm-rf / var/lib/mysql/*

Repeat rollback redo log (Monday, some difference)-- > restore data

1)。 Restore full redo log

[root@xingdian] # innobackupex-- apply-log-- redo-only / xtrabackup/ full backup directory (Monday) 2). Restore differential redo log [root@xingdian ~] # innobackupex-- apply-log-- redo-only / xtrabackup/ full backup directory (Monday)-- incremental-dir=/xtrabacku/ a differential backup

4. Copy data file (cp,rsync), modify permissions [root@xingdian ~] # innobackupex-- copy-back / xtrabackup/ full backup directory (Monday)

5. Start mysqld

[root@xingdian ~] # systemctl start mysqld

34/52

6. Verify the restore:

[root@xingdian] # mysql-u root-pQianFeng@123

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | K1 | |

| | K2 | |

| | K3 | |

| | mysql |

| | performance_schema |

| | sys |

+-+

Mysqldump (logic)

Mysqldump implements logical full backup + binlog

The data is consistent and the backup table is available for the service.

Backup: # mysqldump-u root-p1 db1 T1 > / db1.t1.sql

Restore: # mysql-u root-p1 db1

< /db1.t1.sql 备份一个库 mysqldump -u root -p1 db1 >

/ db1.sql

Back up multiple libraries

# mysqldump-u root-p1-B db1 db2 db3 > / db123.sql

Back up all libraries

# mysqldump-u root-p1-A > / alldb.sql

Restore the database

To ensure data consistency, you should stop the external service of the database before restoring the data, and stop binlog logs because binlog will also generate binlog logs mysql > set sql_log_bin=0 when binlog uses binlog logs to recover data.

Mysql > source db1.t1.sql

Or

# mysql-u root-p1-D db1

< db1.t1.sql 常用备份选项： -A, --all-databases 备份所有库 -B, --databases bbs test mysql 备份多个数据库 -F, --flush-logs 备份之前刷新binlog日志 35/52 bin_log日志备份 binlog日志方法备份恢复数据记录每一个操作 默认存储位置 ： rpm ： /var/lib/mysql 编译: 安装目录的var下 产生binlog日志 一.在启动服务的时候启用日志(临时的) mysqld_safe --log-bin --user=mysql --server-id=1 & 查看binlog日志 mysqlbinlog slave2-bin.000001 -v --base64-output=decode-rows 时间点 ： 141126 14:04:49 位置点 : at 106 方法2. show binlog events; 默认查看第一个 show binlog events in 'mylog.00001'; 二.配置文件(永久修改) #vim /etc/my.cnf [mysqld] log-bin=mylog server-id=1 //做AB复制的时候使用 #/etc/init.d/mysqld restart 根据binlog恢复数据根据时间点恢复数据 mysqlbinlog --start-datetime='2019-07-30 15:45:39' --stop-datetime='2019-07-30 15:59:10' wing-bin.000001 | mysql -u root -p1 根据位置点恢复数据@后 mysqlbinlog --start-position 106 --stop-position 527 wing-bin.000001 | mysql -u root -p1 注：可以同时读取多个日志文件 刷新bin-log日志 #mysqladmin flush-logs -u root -p'' LVM快照备份(物理-扩展) Lvm快照实现物理备份 + binlog 只保存Inode 号 数据一致，服务可用 注：MySQL数据lv和将要创建的snapshot 必须在同一VG，因此VG必须要有一定的剩于空间 优点： 几乎是热备 (创建快照前把表上锁，创建完后立即释放)支持所有存储引擎备份速度快 无需使用昂贵的商业软件(它是操作系统级别的)缺点： 可能需要跨部门协调(使用操作系统级别的命令，DBA一般没权限)无法预计服务停止时间数据如果分布在多个卷上比较麻烦(针对存储级别而言) 36/52 操作流程： 1、flush table with read locak; 2、create snapshot 3、show master status; show slave status; [可选] 4、unlock tables; 5、Copy files from the snapshot 6、Unmount the snapshot. 7、Remove snapshot 正常安装MySQL： 1.安装系统 2.准备LVM，例如 /dev/vg_tianyun/lv-mysql，mount /var/lib/mysql 3.安装MySQL，默认datadir=/var/lib/mysql MySQL运行一段时间，数据并没有存储LVM：将现在的数据迁移到LVM 1. 准备lvm及文件系统 [root@xingdian ~]# lvcreate -n lv-mysql -L 2G datavg [root@xingdian ~]# mkfs.xfs /dev/datavg/lv-mysql 将数据迁移到LVM [root@xingdian ~]# systemctl stop mysqld [root@xingdian ~]# mount /dev/datavg/lv-mysql /mnt/ //临时挂载点 [root@xingdian ~]# cp -a /var/lib/mysql/* /mnt //将MySQL原数据镜像到临时挂载点 [root@xingdian ~]# umount /mnt/ [root@xingdian ~]# vim /etc/fstab //加入fstab开机挂载 /dev/datavg/lv-mysql /var/lib/mysql xfs defaults 0 0 [root@xingdian ~]# mount -a [root@xingdian ~]# chown -R mysql.mysql /var/lib/mysql [root@xingdian ~]# systemctl start mysqld LVM快照备份流程： 加全局读锁 mysql>

Flush tables with read lock

Create a snapshot

[root@tianyun] # lvcreate-L 500m-s-n lv-mysql-snap / dev/datavg/lv-mysql

[root@tianyun ~] # mysql-p'(TianYunYang584131420)'- e 'show master status' > / backup/date +% F_position.txt

Release lock mysql > unlock tables

1-3 must be done in the same session

[root@tianyun ~] # echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate-L 500m-s-n lv-mysql-snap / dev/ datavg/lv-mysql; UNLOCK TABLES;" | mysql- p' (TianYunYang584131420)'

[root@tianyun ~] # echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate-L 500m-s-n lv-mysql-snap / dev/ datavg/lv-mysql;" | mysql- p' (TianYunYang584131420)'

Back up from a snapshot

[root@tianyun ~] # mount-o ro / dev/datavg/lv-mysql-snap / mnt/ xfs-o ro,nouuid [root@tianyun ~] # cd / mnt/

[root@tianyun mnt] # tar-cf / backup/date +% F-mysql-all.tar. / * 5 Remove a snapshot

[root@tianyun ~] # cd; umount / mnt/

37/52

[root@tianyun ~] # lvremove-f / dev/vg_tianyun/lv-mysql-snap LVM snapshot recovery process:

1. Stop the database

two。 Clean up the environment

3. Import data

4. Modify permissions

5. Start the database

6.binlog recovery

[root@slave2] # tar-xf / backup/2016-12-07-mysql-all.tar-C / var/lib/mysql/ [root@slave2 ~] # systemctl start mysqld

Notes on Tar decompression: tar-tf 2016-12-07-mysql-all.tar | less

Script + Cron #! / bin/bash

# LVM backmysql...

Back_dir=/backup/date +% F

[- d $back_dir] | | mkdir-p $back_dir

Echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate-L 500m-s-n lv-mysql-snap / dev/datavg/lv-mysql;\ UNLOCK TABLES;" | mysql- p'(TianYunYang584131420)'

Mount-o ro,nouuid / dev/datavg/lv-mysql-snap / mnt/

Rsync-a / mnt/ $back_dir

If [$?-eq 0]; then umount / mnt/

Lvremove-f / dev/datavg/lv-mysql-snap

Fi

Master-slave replication and read-write separation

Principle of master-slave replication

1. What is master-slave replication?

Master-slave replication is used to establish a database environment exactly the same as the master database, which is called the slave database; the master database is generally a quasi-real-time business database.

Second, the role of master-slave replication

1. Hot backup of data, as a backup database, after the failure of the master database server, you can switch to the slave database to continue to work to avoid data loss.

2. The extension of the architecture. Due to the increasing volume of business, the access frequency of Iamp O is too high to be satisfied by a single machine. At this time, the storage of multiple databases can be done to reduce the access frequency of disk Iamp O and improve the performance of single machine.

3. Read and write separation, so that the database can support greater concurrency. It is especially important in the report. As part of the report sql statement is very slow, resulting in table lock, affecting the front desk service. If the foreground uses master and the report uses slave, then the report sql will not cause the foreground lock and ensure the speed of the foreground.

Third, the principle of master-slave replication

1. The database has an bin-log binary file that records all sql statements.

two。 Our goal is to copy the sql statements from the bin-log file of the main database.

38/52

3. Let it execute these sql statements again in the relay-log (Relay Log) redo log file of the data.

Four: concrete principle

1.binlog output thread: whenever a slave library connects to the master library, the master library creates a thread and sends binlog content to the slave library. In the slave library, when replication begins, two threads are created from the library to process:

two。 From the library Iwhite O thread: when the START SLAVE statement is executed from the slave library, create an Iwhite O thread from the library, which connects to the main library and requests

The master library sends the update records in the binlog to the slave library. The updates sent by the binlog output thread of the main library are read from the library Iswap O thread and copied to the local file, including the relay log file. 3. SQL thread from the library: create a SQL thread from the library that reads and executes the update event written from the library Imax O thread to relay log.

As you can see, for each master-slave replication connection, there are three threads. The master library with multiple slave libraries creates an binlog output thread for each slave library connected to the master library, each with its own I / O thread and SQL thread.

Master-slave replication is shown in the figure:

Master is responsible for writing data to slave, reading master logs to relay-log.

Icano process: responsible for communication

SQL process: responsible for writing data, writing data according to log logs.

Master-slave principle: two threads, I / o thread and SQL thread, are generated from the library slave, and the change records are written to the binary log file and then to the relay log. The sql thread reads the relay log, parses the operation, and finally unifies the data.

Master-slave replication deployment

AB replication

Environment: MASTER originally did not have old data

1. Both master and slave turn off the firewall selinux # / etc/init.d/iptables stop # systemctl stop firewalld # setenforce 0

two。 Who is the owner who provides the account installation software mysql mysql-server to whom?

Configuration:

# vim / etc/my.cnf [mysqld]

Log-bin = my1log

39/52

Server-id = 1

Create an account: mysql > grant replication slave,reload,super on. To 'slave'@'%' identified by' 123'

Mysql > flush privileges

Restart the service:

# systemctl restart mysqld

View the name of the bin_log log file:

Show binlog events

Note: if you do not successfully delete the previous binlog log replication slave:

With this permission, you can view the slave server and read binary logs from the master server.

Super permissions:

Allow users to use SET statements that modify global variables and CHANGE MASTER statements reload permissions:

You must have reload permission to execute flush [tables | logs | privileges]

3. From

Install softwar

Configuration:

# vim / etc/my.cnf [mysqld] server-id = 2

Log-bin = my2log / / 1. When using two masters, you must write 2. Ab replication using gtid

Start the service:

# systemctl restart mysqld

Specify primary server information

Help change master to View Editing Files from mysql

CHANGE MASTER TO MASTER_HOST='mysql-master-1.blackmed.cn/ip', MASTER_USER='slave', / / master server user MASTER_PASSWORD='big', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', / / log file MASTER_LOG_POS=4, / / log location MASTER_CONNECT_RETRY=10; / / default number of attempts

Edit

Show master status views log files on the master server

Launch: start slave

Mysql > show slave status\ G

If unsuccessful: delete the restart service from the binlog

= =

Log_slave_updates parameters:

When the slave library log_slave_updates parameter is not enabled, the binlog of the slave library will not record the operation records from the master library. Only when log_slave_updates is enabled, the slave binlog will record the synchronized operation log of the master library. Log_slave_updates=1 restart service can

= =

Sort out the problems that arise:

Question: 1. Report a duplicate error in uuid.

Solution: modify the uuid number in auto.cnf in / var/lib/mysql/.

40/52

GTID master-slave replication

Mmurs GTID based on transaction ID replication

GTID

Global transaction identity: global transaction identifiers

Is used to replace the traditional replication method, the biggest difference between GTID replication and normal replication mode is that you do not need to specify a binary file name and location, and no longer use MASTER_LOG_FILE+MASTER_LOG_POS to enable replication. Instead, you start copying using MASTER_AUTO_POSTION=1.

It is supported by MySQL-5.6.5 and improved after MySQL-5.6.10.

In the traditional slave terminal, the binlog does not need to be turned on, but in the GTID, the binlog of the slave terminal must be turned on in order to record the GTID (coercion) that has been executed.

The composition of GTID:

GTID = source_id:transaction_id

The source_id source id, which is used to identify the original server, that is, the unique server_uuid of the mysql server, can also be understood as the source ID because the GTID is passed to the slave.

Transaction_id transaction id, which is a sequence number of committed transactions on the current server, usually growing from 1, with a value corresponding to a transaction.

Example:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

The first string is the server_uuid of the server, namely 3E11FA47-71CA-11E1-9E33-C80AA9429562, and the latter 23 is transaction_id

How GTID works:

1. When master updates data, GTID will be generated before the transaction and recorded in the binlog log.

2. The iUnip thread on the slave side writes the changed binlog to the local relay log. 3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has a record.

4. If there is a record, the transaction of the GTID has been executed, and slave will ignore it.

5. If there is no record, slave will execute the transaction of the GTID from relay log and record it to binlog.

=

Master1 (master)-> master2 (slave)

192.168.122.10 192.168.122.20

It is recommended to reset master2 because of the previous experimental environment.

[root@master2 ~] # systemctl stop mysqld [root@master2 ~] # rm-rf / var/lib/mysql/* [root@master2 ~] # systemctl start mysqld [root@master2 ~] # grep password / var/log/mysqld.log

[root@master2 ~] # mysqladmin-pendant 5ovlwings 4WV0Ct 'password' (TianYunYang123)'

MS process GTID: Master

1.vim / etc/my.cnf

Log-bin server-id=1 gtid_mode = ON enforce_gtid_consistency=1 restart 2. Grant replication slave,reload,super on. To slave@'%'

Initialize database mysqldump all databases scp rsync-> master2

Slave server-id=2 gtid_mode = ON

Enforce_gtid_consistency=1 master-info-repository=TABLE relay-log-info-repository=TABLE

/ / these two parameters store master.info and relay.info in the table. The default is Myisam engine, which is officially recommended.

Relay_log_recovery = on

Initialize database import data

41/52

Mysql > change master to master_host='master1', master_user=' authorized user', master_password=' authorized password', master_auto_position=1;mysql > start slave; # launch slave role mysql > show slave status\ G

Dual master and dual slave deployment

one。 Four mysql servers in the environment

192.168.122.196 master1 192.168.122.197 master2 192.168.122.198 slave1 192.168.122.199 slave2

Two: configuration

Mmuri M master1:

Vim / etc/my.cnf validate_password=off log-bin = my1log server-id = 1

Gtid_mode=ON / / enable gtid enforce_gtid_consistency=1

Create an authorized account:

Grant all on. To 'slave'@'%' identified by' 123; master2:

Vim / etc/my.cnf validate_password=off log-bin = my2log server-id = 2 gtid_mode=ON enforce_gtid_consistency=1

Help change master to Lookup profile > edit

Change master to master_host='master1', master_user=' authorized user', master_password=' authorized password', master_auto_position=1

Start slave

Show slave status\ G

Note:

The same authorized user should be created on master2, and the operation of change master to should be carried out on master1.

Slave1:

First, back up all the data of master1, and import mysqldump-u root-p123-A > / all.sql data into slave1 and slave2,mysql-u root-p123, respectively.

< /all.sql vim /etc/my.cof slave1和slave2做相同的操作 validate_password=off log-bin = my3log server-id = 3 gtid_mode=ON enforce_gtid_consistency=1 relay_log_info_repository = TABLE master_info_repository = TABLE relay_log_recovery = on 当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行 42/52 的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性 >

Help change master to

Edit

CHANGE MASTER TO MASTER_HOST='master1/ip', MASTER_USER='slave', / / Master server user MASTER_PASSWORD='big'

MASTER_AUTO_POSITION=1 FOR CHANNEL 'master1' > start slave

Show slave status; > edit

CHANGE MASTER TO MASTER_HOST='master2/ip', MASTER_USER='slave', / / Master server user MASTER_PASSWORD='big'

MASTER_AUTO_POSITION=1 FOR CHANNEL 'master2' > start slave

Show slave status\ G

Note: do the same change master to operation on slave1 and slave2.

Separation of reading and writing

Mycat deployment

Galera (extension)

Group replication Technology group replication of mysql

Galera Replication http://galeracluster.com/downloads/

43/52

Prepare the environment: host resolution:

[root@mysql-galera-1 /] # ntpdate galera1 cat / etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4:: 1 localhost localhost.localdomain localhost6 localhost6.localdomain6 192.168.245.133 galera1

192.168.245.136 galera2 192.168.245.10 galera3 192.168.245.135 galera4

Turn off the firewall and selinux: setenforce 0 & & systemctl stop firewalld

Installation time server: yum-y install ntp

Time synchronization: modify time zone:

Ln-sf / usr/share/zoneinfo/Asia/Shanghai / etc/localtime

Prepare the time server:

# vim / etc/ntp.conf

Server 127.127.1.0 # local clock fudge 127.127.1.0 stratum 10

# systemctl restart ntpd

Client synchronization time:

[root@mysql-galera-2 /] # ntpdate galera1 [root@mysql-galera-3 /] # ntpdate galera1

Download and install Galera (each needs to be installed)

Note: Galera has a separate patch pack and a complete package that has been patched on the basis of mysql. We need to download a version with wsrep extension patches, such as:

44/52

MySQL 5.7.20 extended with wsrep patch version 25.13

So: delete the original mysql:

[root@mysql-galera-1 /] # yum erase rpm-qa | grep mysql- y

Find the download path according to the official download prompt, and find that the path under the download path is the path where the yum source has been made, so you can directly modify the yum configuration file and use yum installation.

Configure the yum source:

[root@mysql-galera-1 yum.repos.d] # cat galera.repo [galera]

Name=galera baseurl= http://releases.galeracluster.com/mysql-wsrep-5.7/centos/7/x86_64/ enabled=1

Gpgcheck=0

[root@wing yum.repos.d] # yum list | grep 'galera'

Installation:

[root@mysql-galera-1 /] # yum install mysql-wsrep-5.7.x86_64 rsync-y

[root@mysql-galera-2 /] # yum install mysql-wsrep-5.7.x86_64 rsync-y

[root@mysql-galera-3 /] # yum install mysql-wsrep-5.7.x86_64 rsync-y

Change the password for each startup service:

# mysqladmin-u root-paired 2rttwxbaked 3Qianfeng123 password'# mysql-u root-paired Qianfeng123bread'

Each machine creates a user for data synchronization: mysql > grant all on. To 'syncuser'@'%' identified by' QianFeng@123'; mysql > flush privileges

Configure Galera Replication:

Galera1 configuration: the main configuration file my.cnf is appended as follows. The boldface part is different from other nodes.

Server-id=1 binlog_format=row innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON

Wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://' / / the first one can write wsrep_node_name='mysql-galera-1' wsrep_node_address='192.168.245.133' without anything.

Authorized user and password wsrep_sst_method=rsync galera2 configuration created by wsrep_sst_auth=syncuser:'QianFeng@123' / /: the following content server-id=2 is appended to the main configuration file my.cnf

Binlog_format=row

45/52

Innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON

Wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://mysql-galera-1,mysql-galera-3,mysql-galera-4' wsrep_node_name='mysql-galera-2'

Wsrep_node_address='192.168.245.136' wsrep_sst_auth=syncuser:'QianFeng@123' wsrep_sst_method=rsync galera3 configuration: the following content server-id=3 is appended to the main configuration file my.cnf

Binlog_format=row innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON

Wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://mysql-galera-1,mysql-galera-2,mysql-galera-4' wsrep_node_name='mysql-galera-3'

Wsrep_node_address='192.168.245.10' wsrep_sst_auth=syncuser:'QianFeng@123' wsrep_sst_method=rsync galera4 configuration: the following content server-id=4 is appended to the main configuration file my.cnf

Binlog_format=row innodb_file_per_table=1 innodb_autoinc_lock_mode=2 wsrep_on=ON

Wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_name='galera' wsrep_cluster_address='gcomm://mysql-galera-1,mysql-galera-2,mysql-galera-3' wsrep_node_name='mysql-galera-4'

Wsrep_node_address='192.168.245.135' wsrep_sst_auth=syncuser:'QianFeng@123' wsrep_sst_method=rsync

Restart the service: each machine

[root@mysql-galera-1 ~] # systemctl restart mysqld

View port: galera port 4567 mysql port 3306

[root@galera1 ~] # ss-auntpl | grep-E '3306 | 4567'

Tcp LISTEN 0 128: 4567: users: ("mysqld", pid=11068,fd=12)

Tcp LISTEN 0 80:: 3306: users: ("mysqld", pid=11068,fd=39)

Test:

Mysql > show status like 'wsrep%'

Wsrep_incoming_addresses |

192.168.245.136Viru 3306192.168.245.135Vera 3306192.168.245.10Vera 3306 |

| | wsrep_cluster_size | 3 / / indicates that there are three nodes in total, and I have one less configuration |

Phase testing:

If you write data on any machine, it will be synchronized on all other machines.

Profile parameter interpretation

46/52

1.binlog_format=row

① STATEMENT Mode (SBR)

Each sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record every sql statement and data change for each row.

Reduce the number of binlog logs, save IO and improve performance. The disadvantage is that in some cases, it can lead to data inconsistency in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf), etc.)

② ROW Mode (RBR)

Instead of recording the context of each sql statement, you only need to record which piece of data has been modified and how it has been modified. And there is no problem that the calls and triggers of stored procedures, or function, or trigger, in certain cases can not be copied correctly. The disadvantage is that a large number of logs will be generated, especially in the case of alter table.

③ MIXED Mode (MBR)

With the mixed use of the above two modes, the general replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the SQL statement executed.

2.innodb_file_per_table=1

Enable data and index storage to a shared tablespace

3.innodb_autoinc_lock_mode=2

Since there is no auto_inc lock in this mode, the performance in this mode is the best; but it also has a problem, which is that the auto_ increment value it gets may not be contiguous for the same statement.

Mycat deployment

Mycat server side does not need to install mysql client test requires installation of mysql

One: deploy mycat

1. Install the jdk environment

Write the environment variable of java under / etc/profile

JAVA_HOME=/usr/local/java

PATH=$JAVA_HOME/bin:$PATH

Export JAVA_HOME PATH

Source / etc/profile / / make the environment variable effective

Install mycat

two。 Write the environment variable of mycat under ~ / .bash_profile

: / usr/local/mycat/bin

Source / .bash_profile

Then start mycat start

3. Test Port:

Jps appears

1762 WrapperSimpleApp

2023 Jps

Ss-ntpl | grep java

LISTEN 0 1 127.0.0.1 pid=1762,fd=4 32 000: users: ("java", pid=1762,fd=4)

LISTEN 0 100:: 9066: users: ("java", pid=1762,fd=63)

LISTEN 0 50:: 42138: users: ("java", pid=1762,fd=51)

LISTEN 0 50:: 46815: users: ("java", pid=1762,fd=49)

LISTEN 0 50:: 1984: users: ("java", pid=1762,fd=50)

LISTEN 0 100: 8066: * users: ("java", pid=1762,fd=67)

Ps aux | grep mycat

=

Second, mycat uses mysql to add databases and accounts:

Mysql > create database shop; mysql > create database bbs; mysql > create database blog

Mysql > grant all on shop. To shop@'%' identified by 'Qianfeng123 birthday; mysql > grant all on shop. To bbs@'%' identified by 'Qianfeng123 payment; mysql > grant all on shop.* to blog@'%' identified by' Qianfeng123 payment; mysql > flush privileges

47/52

Set up 3 accounts and 3 schema:

Switch to the working directory of mycat: server.xml Mycat configuration file, set account, parameters, etc.

Schema.xml Mycat corresponding physical database and database table configuration rule.xml Mycat shard (database table) rule 1, user tag user user configuration node

-the user name of the name login, that is, the user name of the connection to Mycat

-password for password login, that is, the password for connecting to Mycat

-schemas database name, which is associated with the configuration in schema.xml. Multiple databases are separated by commas. For example, if you need this user to manage two databases db1,db2, configure db1,db2 [root@mycat conf] # cd / usr/local/mycat/conf.

[root@mycat conf] # cat server.xml

123456 shop

123456 bbs

123456 blog

[root@master conf] # cat schema.xml

Show status like 'wsrep%'

Show status like 'wsrep%'

Show status like 'wsrep%'

48/52

Balance=1 enables the read-write separation mechanism, and all read operations are sent to the current standby writeHost. All wirteType=0 writes are sent to the first writeHost, and the first fails to switch to the second switchType=3 MySQL Galera cluster-based handover mechanism. The heartbeat statement is show status like 'wsrep%'.

Find a random machine as a client:

[root@client] # mysql-u shop-pendant 123456'-h 192.168.245.3-P8066

Conclusion:

1. All nodes are normal writeHost is responsible for write operation, standby writeHost is responsible for read operation

two。 When the first writeHost fails, one of the standby writeHost is responsible for the write operation, and the other writeHost is responsible for the read operation.

3. When there is only one writeHost, read and write at the same time

Mycat optimized deployment

Server.xml optimization

One: server.xml configuration file

1.privileges tag

Fine DML (data manipulation language) access control over users' schema and tables

-- check indicates whether to enable DML permission checking. The default is off. -- dml sequence description: insert,update,select,delete

The permission of db1 is update,select. The authority of tb01 is to do nothing.

The permission of tb02 is insert,update,select,delete. Other tables default to udpate,select.

2.system tag

All property tags nested within this tag are related to the system configuration.

Utf8

Character set

one

Number of processing threads. The default is the number of cpu.

4096

The number left per read is 4096 by default.

409600

The total amount of space required to create a shared buffer. ProcessorBufferChunkprocessors100 . 0

The default is 0. 0 means DirectByteBufferPool,1 means ByteBufferArena. The second level shared buffer is the percentage of processorBufferPool, which is set here. one hundred

Global ID generation method. (0: local file mode, 1: database mode, 2: timestamp sequence mode, 3: ID;4 generation for ZK: incremental ID generation for ZK.

one

Whether to enable the mysql compression protocol. 1 is on, 0 is off, and off by default.

four

Specifies the header length in the Mysql protocol. The default is 4. 16M

Specifies the maximum length of data that the Mysql protocol can carry. The default is 16m. 1800000

Specifies the idle timeout for the connection. When a connection initiates an idle check, it is found that the idle time has exceeded since it was last used, then the connection

49/52

Will be recycled, that is, it will be shut down directly. The default is 30 minutes, in milliseconds.

three

The initialization transaction isolation level of the front-end connection, which is only used during initialization. Later, the back-end data will be compared according to the attributes passed by the client.

Library connections are synchronized. The default is REPEATED_READ, and the setting value is the number default of 3. READ_UNCOMMITTED = 1

READ_COMMITTED = 2

REPEATED_READ = 3; SERIALIZABLE = 4

For the timeout of 300 SQL execution, Mycat will check the time of the last SQL execution on the connection. If it exceeds this time, the connection will be closed directly.

Answer it. The default time is 300 seconds, in seconds.

one thousand

Clean up the interval between front-end and back-end idle, timeout, and connection closure on the NIOProcessor. The default is 1 second, in milliseconds.

300000

The interval between idle and timeout checks for backend connections. The default is 300 seconds, in milliseconds.

10000

The interval between initiating heartbeats for all backend read and write libraries is 10 seconds by default, in milliseconds.

The IP address that the 0.0.0.0 mycat service listens to. The default value is 0.0.0.0. 8066

Define the usage port of mycat. The default value is 8066. 9066

Define the management port of mycat. The default value is 9066. 5.6

The mysql version number of mycat simulation. The default value is version 5.6. Do not modify this value if not for special needs. Currently, it is supported to set version 5.5, version 5.6, and version 5.7. Other versions may have problems.

0

Whether to turn on real-time statistics. 1: on; 0: off.

0

Whether to turn on global table consistency checking. 1: on; 0: off.

0

Distributed transaction switch. 0 is not filtering distributed transactions; 1 is filtering distributed transactions; 2 is not filtering distributed transactions, but recording distributed transaction logs.

65535

The default is 65535. The attributes above the maximum text length of 64K for sql parsing are only some of them, and there are many variables that can be configured.

Generally speaking, the attributes under the System tag need to be modified after analysis and tuning according to the actual operation.

Firewall tag

The setting of the firewall, that is, to restrict the requested address at the network layer, is mainly to ensure that Mycat is not accessed by anonymous IP from a security point of view.

Ask

50/52

Schema.xml optimization

One: schema.xml

-schema database settings. This database is a logical database, and name corresponds to schema in server.xml.

-dataNode sharding information, that is, the configuration related to the sub-database-dataHost physical database, the real database for storing data.

1. Schema tag

The schema tag is used to define logical libraries in mycat instances. Mycat can have multiple logical libraries, each with its own related configuration. You can use schema tags to divide these different logical libraries. If you do not configure schema tags, the configuration of all tables will belong to the same default

Logic library. The concept of logical library is the same as that of MySql's database. When querying tables in two different logical libraries, we need to switch to this logical library for query.

-name logical database name, which corresponds to the schema in server.xml-checkSQLschema database prefix related settings, when the value is true, for example, we execute the statement select from

TESTDB.company . Mycat changes the statement to select from company and removes the TESTDB.

-sqlMaxLimit when the value is set to a certain value, for each executed sql statement, if no limit statement is added, the Mycat will automatically add the corresponding value. If you don't write, all values are returned by default. You need to add your own sql statement and limit.

2. DataNode tag

The datanode tag defines the data node in the mycat, that is, the data shard. A datanode tag is a separate data shard. The db1 physical database on the localhost1 database instance, which forms a data shard, and finally we mark the shard with dn1.

-name defines the name of the data node, which needs to be unique. We use this name on the table tag to establish the relationship between the table and the shard

Department

-dataHost is used to define which database instance the shard belongs to. The attribute corresponds to the name defined on the datahost tag.-database is used to define that the shard belongs to a specific library on the database instance.

3. DataHost tag

This tag directly defines the specific database instance, read-write separation configuration, and heartbeat statements.

Select user ()

-name uniquely marks the dataHost tag for use by the upper layer

-maxCon specifies the maximum connection for each read-write instance connection pool.

-minCon specifies the minimum connection for each read-write instance connection pool, and initializes the size of the connection pool

-balance load balance type

Balance= "0": if the read-write separation mechanism is not enabled, all read operations are sent to the currently available writeHost balance= "1": all readHost and stand by writeHost participate in the load balancing of select statements. To put it simply, when the dual-master and double-slave mode (M1MaiS1m M2FS2 and M1 M2 is the master / standby), normally, M2Magi S1mS2 participates in the load balancing of select statements. Balance= "2": all read operations randomly distribute balance= "3" on writeHost and readHost: all read requests are randomly distributed to the corresponding readHost of writeHst for execution, and writeHost does not bear read and write pressure. -writeType load balancer type. WriteType= "0", all write operations are sent to the first writeHost of the configuration, the first fails to cut to the surviving second writeHost, and re-

After startup, the switch shall prevail, and the switch is recorded in the configuration file: dnindex.properties. WriteType= "1", all write operations are randomly sent to the configured writeHost. Versions after 1.5 are abandoned and not recommended.

-switchType-1 does not switch automatically

1 automatic switching of default value

2 based on the state of MySql master-slave synchronization, whether to switch heartbeat statement to show slave status 3 mysql galary cluster-based switching mechanism (suitable for cluster) heartbeat statement is show status like 'wsrep%'

-dbType specifies the database type of the backend link that currently supports the binary mysql protocol, and there are other databases that use jdbc links, for example

51/52

Such as: mongodb,oracle,spark, etc.

-dbDriver specifies the driver to be used in the database behind the connection. Available values are native and JDBC. If you use native, because this value implements the binary mysql protocol, you can use mysql and maridb, while other types need to be supported by a JDBC driver.

If you use JDBC, you need to place the JDBC4-compliant driver jar in the mycat\ lib directory, and check that the driver jar package includes the following directory structure file META-INF\ services\ java.sql.Driver. Write the specific driver class name in this file, for example, com.mysql.jdbc.Driver writeHost readHost specifies the relevant configuration of the back-end database to mycat to instantiate the back-end connection pool.

-tempReadHostAvailable

If the readHost under this property writeHost is still available, 0 is configurable by default (0,1).

1) heartbeat tag

This tag indicates the statement used for heartbeat checking with the back-end database.

For example, MYSQL can use select user (), Oracle can use select 1 from dual, and so on. 2) writeHost / readHost tag

Both tags specify the configuration of the back-end database to instantiate the back-end connection pool. The only difference is that writeHost specifies the write instance and readHost specifies the read instance.

Multiple writeHost and readHost can be defined within a dataHost. However, if the back-end database specified by writeHost goes down, then all readHost bound to this writeHost will be unavailable.

On the other hand, due to the downtime of the writeHost, the system will automatically detect and switch to the backup writeHost. The attributes of these two tags are the same, which are described together here.

-host is used to identify different instances. For general writeHost, we use M1 read Host and we use S1. -url backend instance connection address. Native: address: the url-password backend of port JDBC:jdbc stores the password required by the instance

-the user name required for the user backend storage instance

-weight weight is configured in readhost as the weight of the read node-whether usingDecrypt encrypts the password. Default is 0.

52/52

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.