Mysql查询 主从复制及引擎管理

禁用邮件通知：

vi /etc/profile

在末尾添加

#禁止邮件提示 unset MAILCHECK

数据库部署及引擎管理

数据库简介

数据库技术构成
1.数据库系统 DBS

A.数据库管理系统（DataBase Management System， DBMS）: SQL(RDS): ORACLE、Oracle MySQL、MariaDB、Percona server、DB2 NoSQL: Redis、MongoDB、Memcache
B.DBA数据库管理员

2.SQL语言（Structured Query Language 即结构化查询语言）
A. DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程、函数， CREATE DROP
ALTER //开发人员
B. DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE,drop、更新数据
UPDATE //开发人员
C. DQL语句 数据库查询语言： 查询数据 SELECT
D. DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE
grant all on . root to root@localhost identified by '123'
3.数据访问技术

A.ODBC PHP <.php>

B.JDBC JAVA <.jsp>

主 从
主IP

数据库分类关系型：mysql、甲骨文、IBM、微软

非关系型数据库：mogoDB、redis、memcache (数据库缓存服务器) 1 name
2 key

数据库部署

Mysql的YUM安装

1/52

mysql的官方网站：www.mysql.com mysql.org
点击downloadshttps点击最下面的mysql community 1.下载mysql的yum仓库
[root@22e34e653991 /]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm 2.安装mysql的yum仓库 [root@22e34e653991 /]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm

3.修改安装版本（方法一）
[root@22e34e653991 /]# yum repolist all | grep mysql 查看所有关于mysql的库
[root@22e34e653991 /]# yum -y install yum-utils yum的工具包
[root@22e34e653991 /]# yum-config-manager --enable mysql57-community 将禁用的yum源库启用
[root@22e34e653991 /]# yum-config-manager --disable mysql80-community 将启用的yum源库禁用
4.安装数据库
[root@22e34e653991 /]# yum -y install mysql mysql-server

修改yum仓库（方法二）

5.查看数据库的初始密码

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

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

6.使用密码登陆

[root@22e34e653991 /]# mysql -u root -p'k12zPB1r;2Ta'

7.修改密码方法一：

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'QianFeng@123';
方法二：
[root@22e34e653991 /]# mysqladmin -u root -p'k12zPB1r;2Ta password 'QianFeng@123'
密码：大小写有特殊字符数字

mysql使用弱密码登陆

编辑mysql的配置文件

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

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data validate_password=off 添加后可设置弱密码强度

lower_case_table_names=1 不区分大小写

Mysql的编译安装

源码安装

与二进制(RPM)发行版本相比，如果我们选择了通过源代码进行安装，那么在安装过程中我们能够对MySQL所做的调整将会更多更灵活一些。因为通过源代码编译我们可以：
a)针对自己的硬件平台选用合适的编译器来优化编译后的二进制代码；

b)根据不同的软件平台环境调整相关的编译参数；

c)针对我们特定应用场景选择需要什么组件不需要什么组件；

d)根据我们的所需要存储的数据内容选择只安装我们需要的字符集；

2/52

e)同一台主机上面可以安装多个MySQL；

f)等等其他一些可以根据特定应用场景所作的各种调整。

在源码安装给我们带来更大灵活性的同时，同样也给我们带来了可能引入的隐患：
a)对编译参数的不够了解造成编译参数使用不当可能使编译出来的二进制代码不够稳定；

b)对自己的应用环境把握失误而使用的优化参数可能反而使系统性能更差；

c)还有一个并不能称之为隐患的小问题就是源码编译安装将使安装部署过程更为复杂，所花费的时间更长；

Mysql部署

1.准备编译环境

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

2.准备源码包(www.baidu.com;官网)

3.清空系统残留并创建新的账户 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.环境准备 groupadd mysql

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

5.解压

tar xvf mysql-boost-5.7.26.tar.gz

6.配置

[root@mysql-5.7.26 ~]# cmake . \ -DWITH_BOOST=boost_1_59_0/ \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DSYSCONFDIR=/etc \指定安装目录配置文件的位置,默认就是etc -DMYSQL_DATADIR=/usr/local/mysql/data \数据目录 错误日志文件 -DINSTALL_MANDIR=/usr/share/man \ 帮助文档的目录 -DMYSQL_TCP_PORT=3306 \ 默认端口号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
默认存储引擎
提示:boost也可以使用如下指令自动下载

-DDOWNLOAD_BOOST=1

7.编译
make

8.安装 make install

9.初始化
cd /usr/local/mysql 把这个删了就相当于卸载

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

初始化,只需要初始化一次

10.启动MySQL-使用mysqld_safe bin/mysqld_safe --user=mysql & （后台运行）
./bin/mysqladmin -u root -p'原密码' password 123

11.使用客户端测试

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

12.创建数据库配置文件

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

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

编译配置文件

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

扩展

添加环境变量：

echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
让环境变量生效
source /etc/profile

设置开机启动：

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基础

编译安装：

[root@47ed2bec974d mysql]# ls

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

1、bin目录

用于放置一些可执行文件，如mysql、mysqld、mysqlbinlog等。2、include目录用于放置一些头文件，如：mysql.h、mysql_ername.h等。3、lib目录

用于放置一系列库文件。
4、share目录
用于存放字符集、语言等信息。

yum安装：

/var/lib/mysql 存放数据文件 /usr/share/mysql 用于存放字符集、语言等信息。

数据库存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引

擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

InnoDB存储引擎:
InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键;InnoDB是默认的
MySQL引擎
InnoDB特点：

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比

如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM存储引擎：（了解）
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。
MyISAM特点：
插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY存储引擎(了解)
MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问
MEMORY特点：

所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的

安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

引擎功能对比：

5/52

click me click me click me click me
功 能 MYISAM Memory InnoDB

存储限制 256TB RAM 64TB

支持事物 No No Yes

支持全文索引 Yes No No

支持数索引 Yes Yes Yes

支持哈希索引 No Yes No

支持数据缓存 No N/A Yes

支持外键 No No Yes

如何选择引擎：

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择；如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率；如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎；MySQL中使用该引擎作为临时表，存放查询的中间结果；如果只有INSERT和SELECT操作，可以选择
Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive。

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能。

存储引擎查看：

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)

Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎

mysql> alter table service engine=innodb;

Mysql事务

6/52

MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

•在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

•事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
•事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个
环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

•一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

•隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发

执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

•持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
•BEGIN 开始一个事务
•ROLLBACK 事务回滚
•COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
•SET AUTOCOMMIT=0 禁止自动提交
•SET AUTOCOMMIT=1 开启自动提交

show variables like 'autocommit'; //查看是否修改成功

注意：在编写应用程序时，最好事务的控制权限交给开发人员

表管理及数据类型

数据类型

分类：

7/52

数值类型字符串类型

时间和日期类型

数值类型： TINYINT SMALLINT MEDIUMINT INT BIGINT
整数类型
作用：用于存储用户的年龄、游戏的Level、经验值等。
类型 大小 范围（有符号） 范围（无符号）
TINYINT 1 字节 (-128，127) (0，255)
SMALLINT 2 字节 (-32 768，32 767) (0，65 535)
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215)
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295)
BIGINT 8 字节 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 6
浮点数类型 FLOAT DOUBLE
作用：用于存储用户的身高、体重、薪水等
float(5.3) 5宽度 3精度
宽度不算小数点
mysql> create table t12(id float(6,2));
mysql> insert into t1 values ('2.22');

click me click me click me click me
FLOAT 4 字节 (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 0，(1.175 494
823 466 351 E+38)
DOUBLE 8 字节 (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0， 0，(2.225 073
(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

定点数类型 DEC 定点数在MySQL内部以字符串形式存储，比浮点数更精确，适合用来表示货币等精度高的数据。

位类型 BIT BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位

字符串类型：
CHAR系列 CHAR VARCHAR
TEXT系列TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型： SET ENUM
集合类型：
类型 大小 用途

CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据

枚举类型： 枚举列可以把一些不重复的字符串存储成一个预定义的集合 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

时间和日期类型： DATE TIME DATETIME TIMESTAMP YEAR
作用：用于存储用户的注册时间，文章的发布时间，文章的更新时间，员工的入职时间等

类型 大小 范围 格式
(字节)
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD
TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS
YEAR 1 1901/2155 YYYY
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD
TIMESTAMP 4 1970-01-01 00:00:00/2038 YYYYMMDD
结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间
2038年1月19日 凌晨 03:14:07

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.01 sec)
timestamp 类型的列还有个特性：默认情况下，在 insert, update 数据时，timestamp 列会自动以当前时
间（CURRENT_TIMESTAMP）填充/更新。"自动"的意思就是，你不去管它，MySQL 会替你去处理。
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)

扩展：

select now();查看当前时间

表操作

MySQL表操作 DDL
表是数据库存储数据的基本单位，由若干个字段组成，主要用来存储数据记录。表的操作包括:
创建表、查看表、修改表和删除表。

9/52

这些操作都是数据库管理中最基本，也是最重要的操作。本节内容包括:
创建表 create table

查看表结构 desc table, show create table 修改表 alter table
复制表 create table ...
删除表 drop table

一、创建表（表的基本操作）表:school.student1
字段 字段 字段
id name sex age
1 tom male 23 记录
2 jack male 21 记录
3 alice female 19 记录

语法：
create table 表名(自定义)(

字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]
)[存储引擎 字符集]; ==在同一张表中，字段名是不能相同==宽度和约束条件可选==字段名和类型是必须的

mysql> CREATE DATABASE school; //创建数据库school
mysql> use school;
mysql> create table student1(
-> id int,
-> name varchar(50),
-> sex enum('m','f'),
-> age int
-> );
Query OK, 0 rows affected (0.03 sec)
查看表（当前所在库）
mysql> show tables;
+------------------ +
| Tables_in_school |
+------------------ +
| student1 |
+------------------ +
1 row in set (0.00 sec)
向表中插入内容
语法：
insert into 表名(字段1,字段2... ) values(字段值列表...);
mysql> insert into student1(id,name,sex,age) values(1,'xingdia','m','26');
查看表结构
mysql> desc student1;
+------- +--------------- +------ +----- +--------- +------- +
| Field | Type | Null | Key | Default | Extra |
+------- +--------------- +------ +----- +--------- +------- +
| id | int(11) | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| sex | enum('m','f') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+------- +--------------- +------ +----- +--------- +------- +
4 rows in set (0.00 sec)

10/52

mysql> select id,name,sex,age from student1; //查询表中所有字段的值
Empty set (0.00 sec)
mysql> select * from student1; /查询表中所有字段的值
Empty set (0.00 sec)
mysql> select name,age from student1; //查询表中指定字段的值
Empty set (0.00 sec)
mysql> insert into student1 values (1,'xingdian','m',33),(2,'alice','m',20),(3,'jack','m',40); //
顺序插入
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);//只向指定的字段插入值
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0
表school.student2
字段名 数据类型
编号 id int
姓名 name varchar(50)
出生年份 born_year year
生日 birthday date
上课时间 class_time 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,'tom',now(),now(),now(),now());

mysql> insert into student2 values(2,'jack',1982,19821120,123000,20140415162545);

表school.student3

id id int
姓名 name varchar(50)
性别 sex enum('male','female')
爱好 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 (1,'tom','male','book,game'); mysql> insert into student3 values (2,'jack','male','film'); mysql> select * from student3;

二、查看表结构
DESCRIBE查看表结构

11/52

DESCRIBE 表名;

DESC 表名;

SHOW CREATE TABLE查看表详细结构

SHOW CREATE TABLE 表名;

三、表完整性约束作用：用于保证数据的完整性和一致性

约束条件 说明
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录，不可以为空 UNIQUE +

NOT NULL
FOREIGN KEY (FK) 标识该字段为该表的外键，实现表与表（父表主键/子表1外键/子表2外键）之
间的关联
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的，可以为空，一个表中可以有多个UNIQUE KEY
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值

说明：

1.是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值

2.字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20

3. 是否是key主键 primary key
   外键 forengn key

NOT NULL
表school.student4

mysql> create table school.student4( id int not null,

name varchar(50) not null,

sex enum('m','f') 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(1,'jack','m',20,'book'); Query OK, 1 row affected (0.00 sec)

mysql> select * from student4;

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

mysql> insert into student4 values(3,NULL,'m',40,'book'); ERROR 1048 (23000): Column 'name' cannot be null

设置唯一约束 UNIQUE
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
表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 | |

+-----------+-------------+------+-----+---------+-------+

设置主键约束 PRIMARY KEY
primary key 字段的值是不允许重复，且不允许不NULL（UNIQUE + NOT NULL）

单列做主键表school.student6 方法一

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,'alice','female',22);

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)

设置字段值增 AUTO_INCREMENT 表company.department3
CREATE TABLE department3 (

dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR(30),

comment VARCHAR(50) );

四、修改表ALTER TABLE
语法：

1. 修改表名

ALTER TABLE 表名 RENAME 新表名;

2. 增加字段
   ALTER TABLE 表名
   ADD 字段名 数据类型 [完整性约束条件…],
   ADD 字段名 数据类型 [完整性约束条件…];

ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;

3. 删除字段
   ALTER TABLE 表名 DROP 字段名;

4. 修改字段
   ALTER TABLE 表名
   MODIFY 字段名 数据类型 [完整性约束条件…];

13/52

ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

示例：

1. 修改存储引擎

mysql> alter table service

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

2. 添加字段

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; //添加name字段之后

mysql> alter table student10

add sex enum('male','female') default 'male' first; //添加到最前面

3. 删除字段

mysql> alter table student10

-> drop sex;

mysql> alter table service

-> drop mac;

4. 修改字段类型modify

mysql> alter table student10

-> modify age tinyint not null ; //注意保留原有的约束条件

mysql> alter table student10

-> modify id int not null primary key ; //修改字段类型、约束、主键

5. 增加约束（针对已有的主键增加auto_increment） mysql> alter table student10 modify id int not null primary key auto_increment; //错误，该字段已经是 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

6. 增加主键 mysql> alter table student1
   -> add primary key(id);

7.增加主键和自动增长 mysql> alter table student1

-> modify id int not null primary key auto_increment;

8.删除主键[primary key auto_increment]
a. 删除自增约束
mysql> alter table student10 modify id int not null;

b. 删除主键 mysql> alter table student10
-> drop primary key;

14/52

五、复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）复制表结构/记录+表结构，不会将Key复制 mysql> create table new_service select * from service;

只复制表结构

mysql> create table new1_service select * from service where 1=2; //条件为假，查不到任何记录

可以复制主键,只复制表结构 mysql> create table t4 like employees;

六、删除表

DROP TABLE 表名;

表操作（扩展）

修改数据表中字段的值:语法：

Update 表名 set 列名=值where 条件 update student set name='123' where id=1

删除某一行：语法：
delete from 表名 where id=1 delete from type where id=1

id name

1xingdian renren

库操作

系统数据库

information_schema： 虚拟库，主要存储了系统中的一些数据库对象的信息，例如用户表信息、列信息、权限信息、字符信息等
performance_schema： 主要存储数据库服务器的性能参数
mysql： 授权库，主要存储系统用户的权限信息
sys： 主要存储数据库服务器的性能参数

创建数据库：DDL 1. #mysqladmin -u root -p1 create db1
2.直接去创建数据库目录并且修改权限
3.mysql> create database xingdian;

数据库命名规则：区分大小写唯一性

不能使用关键字如 create select

不能单独使用数字

查看数据库 mysql> show databases;

mysql> show create database xingdian; mysql> select database(); 查看当前所在的库

15/52

切换数据库 mysql> use xingdian; mysql> show tables;

删除数据库

DROP DATABASE 数据库名;

数据库查询

数据库查询

单表查询

简单查询通过条件查询查询排序

限制查询记录数使用集合函数查询分组查询使用正则表达式查询

测试表：company.employee5
雇员编号 id int
雇员姓名 name varchar(30)
雇员性别 sex enum
雇用时期 hire_date date
职位 post varchar(50)
职位描述 job_description varchar(100)
薪水 salary double(15,2)
办公室 office int
部门编号 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(15,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',5000,501,100), ('tom','male','20180203','instructor','teach',5500,501,100), ('robin','male','20180202','instructor','teach',8000,501,100), ('alice','female','20180202','instructor','teach',7200,501,100), ('','male','20180202','hr','hrcc',600,502,101),

('harry','male','20180202','hr',NULL,6000,502,101),

('emma','female','20180206','sale','salecc',20000,503,102),

('christine','female','20180205','sale','salecc',2200,503,102),

('zhuzhu','male','20180205','sale',NULL,2200,503,102),

('gougou','male','20180205','sale','',2200,503,102);

16/52

mysql> select 字段名称,字段名称2 from 表名 条件
简单查询：

mysql> select * from employee5;

mysql> select name, salary, dep_id from employee5 where id <=5;

避免重复DISTINCT SELECT post FROM employee5;
SELECT distinct post FROM employee5;

注：不能部分使用DISTINCT，通常仅用于某一字段。

通过四则运算查询

SELECT name, salary, salary*14 FROM employee5;

SELECT name, salary, salary*14 AS Annual_salary FROM employee5;

SELECT name, salary, salary*14 Annual_salary FROM employee5;

定义显示格式 CONCAT() 函数用于连接字符串

SELECT concat(name, 's annual salary: ', salary*14) AS Annual_salary FROM employee5;

单条件查询

SELECT name,post FROM employee5 WHERE post='hr';

多条件查询

SELECT name,salary FROM employee5 WHERE post='hr' AND salary>10000; select * from employee5 where salary>5000 and salary<10000 or dep_id=102;

关键字BETWEEN AND between and

SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;

SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;

关键字IS NULL SELECT name,job_description FROM employee5 WHERE job_description IS NULL;

SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;

SELECT name,job_description FROM employee5 WHERE job_description='';

NULL说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、比较时使用关键字用"is null"和"is not null"。
5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。

关键字IN集合查询
SELECT name, salary FROM employee5

WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;

SELECT name, salary FROM employee5

WHERE salary IN (4000,5000,6000,9000) ;

SELECT name, salary FROM employee

WHERE salary NOT IN (4000,5000,6000,9000) ;

关键字LIKE模糊查询通配符'% ':所有字符
SELECT * FROM employee5 WHERE name LIKE 'al%';

通配符'_' 一个字符

SELECT * FROM employee5

WHERE name LIKE 'al___';

17/52

排序查询

mysql> 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; 控制显示前3行。
mysql> select china from t1 order by china desc limit 1,3; 从序号1开始显示三行的内容。

注：

ascending 美音 /ə'sɛndɪŋ/ 升序 descending 美音 /dɪ'sɛndɪŋ/ 降序

按多列排序:

入职时间相同的人薪水不同

SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;

限制查询的记录数

SELECT * FROM employee5 ORDER BY salary DESC

LIMIT 5; //默认初始位置为0

SELECT * FROM employee5 ORDER BY salary DESC

LIMIT 0,5;

SELECT * FROM employee5 ORDER BY salary DESC

LIMIT 3,5; //从第4条开始，共显示5条

使用集合函数查询
count 可以查看共有多少条记录
select count(*) from employee5;

select count( name) from employee5;
select max(salary) from employee5; //部门薪资最高

select min(salary) from employee5;

select avg(salary) from employee5;

sale这个部门的总工资：
select concat("Total Department Wages：",sum(salary)) from employee5 where post='sale';

打印薪水最高的这个人的详细信息： select * from employee5 where salary = (select max(salary) from employee5);

分组查询：
GROUP BY和GROUP_CONCAT()函数一起使用
部门ID相同，就把名字拼到一起：
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和集合函数一起使用部门最高薪资
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)

正则查询
SELECT * FROM employee5 WHERE name REGEXP '^ali';

SELECT * FROM employee5 WHERE name REGEXP 'yun$';

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

小结：对字符串匹配的方式
WHERE name = 'tom';

WHERE name LIKE 'to%'; _ %

18/52

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

多表查询（扩展）

表查询左右内链接

多表连接查询复合条件连接查询

一、准备两张表

一、准备两张测试表

表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 ('',19,200),

('tom',26,201),

('jack',30,201),

('alice',24,202),

('robin',40,200),

('xingdian',16,200),

('natasha',28,204);

mysql> select * from employee6;
表company.department6
mysql> create table department6( dept_id int,

dept_name varchar(100) );

mysql> desc department6;

mysql> insert into department6 values (200,'hr'),

(201,'it'),

(202,'sale'),

(203,'fd');

mysql> select * from department6;
注：
Financial department:财务部门 fd

二、多表的连接查询

交叉连接： 生成笛卡尔积，它不使用任何匹配条件交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合

内连接： 只连接匹配的行
外连接

左连接： 会显示左边表内所有的值，不论在右边表内匹不匹配

右连接： 会显示右边表内所有的值，不论在左边表内匹不匹配

全外连接： 包含左、右两个表的全部行

=================交叉连接=======================

select employee6.emp_name,employee6.age,employee6.dept_id,department6.dept_name from employee6,department6;

=================内连接=======================

19/52

只找出有部门的员工 (部门表中没有natasha所在的部门)

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;

外连接语法：
SELECT 字段列表
FROM 表1 LEFT|RIGHT JOIN 表2
ON 表1.字段 = 表2.字段;

先用谁谁就是左。
=================外连接(左连接 left join)=======================

mysql> select emp_id,emp_name,dept_name from employee6 left join department6 on employee6.dept_id = department6.dept_id;
找出所有员工及所属的部门，包括没有部门的员工

=================外连接(右连接right join)=======================
mysql> select emp_id,emp_name,dept_name from employee6 right join department6 on employee6.dept_id = department6.dept_id;
找出所有部门包含的员工，包括空部门

20/52

=================全外连接=======================

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)

三、复合条件连接查询
示例1：以内连接的方式查询employee6和department6表，并且employee6表中的age字段值必须大于25

找出公司所有部门中年龄大于25岁的员工

示例2：以内连接的方式查询employee6和department6表，并且以age字段的升序方式显示

四、子查询
子查询是将一个查询语句嵌套在另一个查询语句中。

22/52

内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN等关键字

还可以包含比较运算符：= 、 !=、> 、<等

1. 带IN关键字的子查询查询employee表，但dept_id必须在department表中出现过

2. 带比较运算符的子查询
   =、!=、>、>=、<、<=、<>
   查询年龄大于等于25岁员工所在部门（查询老龄化的部门）

23/52

数据库日志管理

日志分类

1 错误日志 ：启动，停止，关闭失败报错。rpm安装日志位置 /var/log/mysqld.log 排错
2 通用查询日志：所有的查询都记下来。
3 二进制日志：实现备份，增量备份。只记录改变数据，除了select都记。 备份 binlog
4 中继日志：读取主服务器的binlog，在本地回放。保持一致。 复制
5 slow log：慢查询日志，指导调优，定义某一个查询语句，定义超时时间，通过日志提供调优建议给开发

人员。 调优

6 DDL log： 定义语句的日志。

Error Log

log-error=/var/log/mysqld.log

Binary Log:前提需要开启

log-bin=/var/log/mysql-bin/slave2 server-id=2

[root@slave2 ~]# mkdir /var/log/mysql-bin

[root@slave2 ~]# chown mysql.mysql /var/log/mysql-bin/ [root@slave2 ~]# systemctl restart mysqld
查看binlog日志

mysqlbinlog slave2-bin.000001 -v --base64-output=decode-rows 时间点 ： 141126 14:04:49 位置点 : at 106

注：
1.重启mysqld 会截断

2.flush logs 会截断

3. reset master 删除所有binlog rm -rf /
4. 删除部分
   PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

截取binlog

24/52

all：

mysqlbinlog mysql.000002

datetime：

#mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56"

#mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54"

#mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54"

position：

#mysqlbinlog mysql.000002 --start-position=260

#mysqlbinlog mysql.000002 --stop-position=260

#mysqlbinlog mysql.000002 --start-position=260 --stop-position=930

Slow Query Log

slow_query_log=1 slow_query_log_file=/var/log/mysql-slow/slow.log long_query_time=3 设置慢查询超时时间 单位是：秒验证:

#mysql -u root -p2

select sleep(6); #执行一个超过6秒的查询操作

#cat /var/log/mysql/slow-log

权限控制和备份恢复

权限控制

mask

权限级别

权限级别：

Global level：系统级，所有库，所有表，的权限 Database level：某个数据库中的所有表，的权限 Table level：库中的某个表，的权限 Column level：表中的某个字段，的权限 procs level：某个存储过程，的权限 proxies level：代理服务器，的权限

查看权限记录表：因为超级管理员默认已经设置。所以直接查询权限即可。

Global level

select * from mysql.user\G;

用户字段：root
权限字段：Select_priv
安全字段：*B1DD4ADE47888D9AEC4D705C85230F1B52D2A817

Database level

select * from mysql.db\G;

25/52

测试库权限 mysql> create database ttt;

Query OK, 1 row affected (0.00 sec)

mysql> grant all on ttt.* to 'u1'@'localhost' identified by 'QianFeng@123'; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from mysql.db\G

验证：

#mysql -u u1 -pQianFeng@123 >show databases;

Table level

select * from mysql.tables_priv\G;

grant all on k0.t1 to 'u1'@'localhost' identified by 'QianFeng@123';

验证：

#mysql -u u1 -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');

前提是有库，有表，有权限。

++++mysql ++++web（farm）

不允许root账户 给web项目普通用户 farm -----> 库（单个/多个）

sql漏洞注入------>脱库

用户管理

注意： 修改（update，grant）完权限后，要刷新授权表，目录让授权的用户的权限立即生效。

MySQL用户管理
1.登录和退出MySQL
#mysql -h292.168.5.240 -P 3306 -u root -p123 mysql -e 'select user,host from user'
-h 指定主机名 【默认为localhost】
-P MySQL服务器端口 【默认3306】
-u 指定用户名 【默认root】
-p 指定登录密码 【默认为空密码】
此处mysql为指定登录的数据库 -e 接SQL语句 (在脚本中使用)

2. 创建用户

方法一：CREATE USER语句创建 mysql> create user xingdian;

26/52

mysql> create user xingdian@'%' identified by '123456'; 这样可以直接从远程登录

方法二： GRANT语句创建(授权) mysql> GRANT ALL ON . TO 'xingdian'@'localhost' IDENTIFIED BY '123456';
mysql> grant select,insert on k1.* to admin1@'%' identified by '123';

FLUSH PRIVILEGES;

查看单独的库权限 mysql> select * from mysql.db\G

注意： ALL 单独的权限 . 单独的库和单独的表
xingdian@localhost 用户有则授权无则创建 localhost % 10.19.40.% 10.19.40.11 3. 删除用户方法一：DROP USER语句删除

DROP USER 'user1'@'localhost';

方法二：DELETE语句删除

DELETE FROM mysql.user WHERE user='user2' AND host='localhost';
FLUSH PRIVILEGES; 刷新授权表

4. 修改用户密码root修改自己密码方法一：

   mysqladmin -uroot -p'123' password 'new_password' //123为旧密码

方法二：
mysql> update mysql.user set authentication_string=password('Qianfeng123!') where user='root' and host='localhost';

方法三：

给那个用户设置密码，你要在那个用户下执行

SET PASSWORD=password('new_password');
上面方法将会在后面的版本remove,使用下面方法

SET PASSWORD='new_password'; 直接设置密码

root修改其他用户密码方法一：

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

上面的方法会在将来remove，使用下面的方法： mysql> SET PASSWORD FOR user3@'localhost'='new_password';

方法二：

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

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

普通用户修改自己密码 mysql> SET password=password('new_password');

mysql> select * from mysql.user\G

mysql> alter user 'wing'@'localhost' identified by 'Qianfeng123!@';

查看现有的密码策略 mysql> SHOW VARIABLES LIKE 'validate_password%';
参数解释：
1).validate_password_dictionary_file 指定密码验证的文件路径; 2).validate_password_length 密码最小长度 3).validate_password_mixed_case_count 密码至少要包含的小写字母个数和大写字母个数; 4).validate_password_number_count 密码至少要包含的数字个数

5).validate_password_policy 密码强度检查等级，对应等级为：0/LOW、1/MEDIUM、2/STRONG,默认为1 0/LOW：只检查长度; 1/MEDIUM：检查长度、数字、大小写、特殊字符;

2/STRONG：检查长度、数字、大小写、特殊字符字典文件。

6).validate_password_special_char_count密码至少要包含的特殊字符数

27/52

找回密码

5.6/5.7版本：

mysqld --skip-grant-tables --user=mysql & //跳过数据库权限验证

mysql

mysql> UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='root' AND host='localhost';
mysql> FLUSH PRIVILEGES; 刷新授权表

注意：报错处理

问题1：ERROR You must reset your password using ALTER USER statement before executing this statement

解决方案：ALTER USER 'root'@'localhost' IDENTIFIED BY 'Xiaoming250';

报错解决方案

创建用户时报错：

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

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
报错原因：密码强度不够。
解决方法：(该账号为测试账号，所以采用降低密码策略强度) 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)
再次创建用户，成功

报错：

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

解决方案：

报错：

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'

解决方案：

备份恢复

概述

MySQL数据备份

所有备份数据都应放在非数据库本地，而且建议有多份副本。测试环境中做日常恢复演练，恢复较备份更为重要。

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

冗余： 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群。

备份过程中必须考虑因素：
1.数据的一致性

2.服务的可用性

逻辑备份：

备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。 mysqldump

物理备份：
直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版

本。 tar,cp xtrabackup lvm snapshot

完全备份

增量备份:每次备份上一次备份到现在产生的新数据

29/52

差异备份:只备份跟完整备份不一样的

tar数据备份(物理)

tar备份数据库注：备份期间，服务不可用

备份的过程：【完全物理备份】
1.停止数据库

2.tar备份数据

3.启动数据库
[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 ./*
注：备份文件应该复制其它服务器或存储上

30/52

还原的过程：
1.停止数据库

2.清理环境

3.导入备份数据

4.启动数据库
[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备份(物理)

percona-xtrabackup 物理备份 + binlog

它是开源免费的支持MySQL 数据库热备份的软件，它能对InnoDB和XtraDB存储引擎的数据库非阻塞地

备份。它不暂停服务创建Innodb热备份；为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载。

percona是一家老牌的mysql技术咨询公司。它不仅提供mysql的技术支持、培训、咨询，还发布了mysql

的分支版本--percona Server。并围绕percona Server还发布了一系列的mysql工具。

软件安装

部署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

注意：如果依赖包perl-DBD-MySQL安装不上，]需先把percona源拿掉用centos的源单独安装，然后再安装 percona-xtrabackup-24.x86_64

完整备份

完全备份流程创建备份目录：
[root@xingdian full]# mkdir -p /xtrabackup/full
备份：
[root@xingdian full]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full
完全备份恢复流程
1.停止数据库
2.清理环境

3.重演回滚－－> 恢复数据
4.修改权限

5.启动数据库关闭数据库：

#systemctl stop mysqld

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

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

#rm -rf /var/log/mysql-slow/slow.log (有则删除，无则不需要操作)
恢复之前的验证恢复：
#innobackupex --apply-log /xtrabackup/full/2019-08-20_15-57-31/

确认数据库目录：
恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里

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

恢复数据：
[root@xingdian mysql]# innobackupex --copy-back /xtrabackup/full/2019-08-20_15-57-31/
修改权限：
[root@xingdian mysql]# chown mysql.mysql /var/lib/mysql -R
启动数据库：
[root@xingdian mysql]# systemctl start mysqld
验证数据：
[root@xingdian mysql]# mysql -u root -pQianFeng@123 mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| k1 |

| mysql |

| performance_schema |

| sys |

+--------------------+

增量备份

增量备份流程
原理：每次备份上一次备份到现在产生的新数据

32/52

创建备份目录：

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

1、完整备份：周一

[root@xingdian ~]# innobackupex --user=root --password='QianFeng@123' /xtrabackup/full 2、增量备份：周二 -- 周六

需要创建数据

[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

需要创建数据

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

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

……

增量备份恢复流程
1.停止数据库

2.清理环境
3.依次重演回滚redo log－－> 恢复数据
4.修改权限

5.启动数据库

[root@xingdian ~]# systemctl stop mysqld [root@xingdian ~]# rm -rf /var/lib/mysql/* 依次重演回滚redo log 周一：full

[root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2019-08-20_15-57-31/
周二 --- 周四

[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/

……

恢复数据
[root@xingdian ~]# innobackupex --copy-back /xtrabackup/full/2019-08-20_15-57-31/ (datadir)

修改权限

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

[root@xingdian ~]# systemctl start mysqld

验证恢复：

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

mysql> show databases;

+-------------------- +
| Database |
+-------------------- +
| information_schema |

| k1 |

| k2 |

33/52

| k3 |

| mysql |

| performance_schema |

| sys |

+--------------------+

差异备份

差异备份流程：只备份跟完整备份不一样的

创建备份目录：

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

1、完整备份：周一

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

2、差异备份：周二 -- 周六创建备份目录：

[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/完全备份目录（周一）

insert into testdb.test2 values(3);

[root@xingdian ~]# innobackupex --user=root --password=888 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/完全备份目录（周一）

insert into testdb.test values(4);

[root@xingdian ~]# innobackupex --user=root --password=888 --incremental /xtrabackup/jian --incremental-basedir=/xtrabackup/完全备份目录（周一）

差异备份恢复流程
1.停止数据库

2.清理环境

3.重演回滚redo log（周一，某次差异）－－> 恢复数据
4.修改权限

5.启动数据库

1. 停止数据库

[root@xingdian ~]# systemctl stop mysqld

2. 清理环境
   [root@xingdian ~]# rm -rf /var/lib/mysql/*

3. 重演回滚redo log（周一，某次差异）－－> 恢复数据
   1).恢复全量的redo log
   [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）2).恢复差异的redo log [root@xingdian ~]# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录（周一）--incremental-dir=/xtrabacku/某个差异备份

4.复制数据文件(cp,rsync)，修改权限 [root@xingdian ~]# innobackupex --copy-back /xtrabackup/完全备份目录（周一）

5.启动mysqld

[root@xingdian ~]# systemctl start mysqld

34/52

6.验证恢复：

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

mysql> show databases;

+-------------------- +
| Database |
+-------------------- +
| information_schema |

| k1 |

| k2 |

| k3 |

| mysql |

| performance_schema |

| sys |

+--------------------+

mysqldump（逻辑）

mysqldump实现逻辑完全备份 + binlog

数据一致，服务可用备份表
备份： # mysqldump -u root -p1 db1 t1 > /db1.t1.sql

恢复： # mysql -u root -p1 db1 < /db1.t1.sql

备份一个库

mysqldump -u root -p1 db1 > /db1.sql

备份多个库
#mysqldump -u root -p1 -B db1 db2 db3 > /db123.sql
备份所有的库
#mysqldump -u root -p1 -A > /alldb.sql
恢复数据库
为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志因为binlog使用binlog日志恢复数据时也会产生binlog日志 mysql> set sql_log_bin=0

mysql> source db1.t1.sql

或者

#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

2. 将数据迁移到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快照备份流程：

1. 加全局读锁 mysql> flush tables with read lock;

2. 创建快照

[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

3. 释放锁 mysql> unlock tables;

1-3必须同一会话中完成
[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)'

4. 从快照中备份
   [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. 移除快照
[root@tianyun ~]# cd; umount /mnt/

37/52

[root@tianyun ~]# lvremove -f /dev/vg_tianyun/lv-mysql-snap LVM快照恢复流程：
1.停止数据库
2.清理环境
3.导入数据
4.修改权限

5.启动数据库
6.binlog恢复
[root@slave2 ~]# tar -xf /backup/2016-12-07-mysql-all.tar -C /var/lib/mysql/ [root@slave2 ~]# systemctl start mysqld

Tar解压的注意事项： tar -tf 2016-12-07-mysql-all.tar |less

脚本 + 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

主从复制和读写分离

主从复制原理

一、什么是主从复制?
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

二、主从复制的作用

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

三、主从复制的原理
1.数据库有个bin-log二进制文件，记录了所有sql语句。

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

38/52

3.让其在从数据的relay-log(中继日志)重做日志文件中再执行一次这些sql语句即可。

四：具体原理

1.binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：
2.从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求
主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。3.从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

可以知道，对于每一个主从复制的连接，都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程，每一个从库都有它自己的I/O线程和SQL线程。
主从复制如图：

master 对外提供 负责写数据 slave 读master日志写到relay-log

I/O进程：负责通信

SQL进程：负责写数据，根据log日志写数据。

主从原理：从库slave生成两个线程，i/o线程和sql线程，i/o将变更记录写到二进制日志文件中，再写到中继日志中，sql线程读取中继日志，解析操作，最终数据统一

主从复制部署

AB复制
环境：MASTER原来没有旧数据
1.主从都关闭防火墙selinux #/etc/init.d/iptables stop #systemctl stop firewalld #setenforce 0

2.主 谁是主 给谁提供账户安装软件mysql mysql-server
配置：

#vim /etc/my.cnf [mysqld]

log-bin = my1log

39/52

server-id = 1

创建账户： mysql> grant replication slave,reload,super on . to 'slave'@'%' identified by '123';

mysql> flush privileges;

重启服务：

#systemctl restart mysqld

查看bin_log日志文件的名称：

show binlog events;

注意:如果不成功删除以前的binlog日志 replication slave：
拥有此权限可以查看从服务器，从主服务器读取二进制日志。
super权限：

允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句reload权限：
必须拥有reload权限，才可以执行flush [tables | logs | privileges]

3.从

安装软件

配置：

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

log-bin = my2log //1.使用双主的时候必须写 2.使用gtid方式的ab复制

启动服务：

#systemctl restart mysqld
指定主服务器信息

help change master to 查看从mysql编辑文件
CHANGE MASTER TO MASTER_HOST='mysql-master-1.blackmed.cn/ip', MASTER_USER='slave', //主服务器用户 MASTER_PASSWORD='big', MASTER_PORT=3306, MASTER_LOG_FILE='master2-bin.001', //日志文件 MASTER_LOG_POS=4, //日志位置 MASTER_CONNECT_RETRY=10; //默认尝试次数

edit

show master status 在主服务器查看日志文件

启动：start slave

mysql> show slave status \G

如果没成功：删除从的binlog重启服务

======================================

log_slave_updates参数：
当从库log_slave_updates参数没有开启时，从库的binlog不会记录来源于主库的操作记录。只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志 log_slave_updates=1 重启服务既可以

======================================
出现的问题整理：
问题：1.报uuid重复的错误。
方案：在/var/lib/mysql/里的auto.cnf里的uuid号修改。

40/52

GTID主从复制

M-S GTID 基于事务ID复制

GTID
全局事务标识：global transaction identifiers

是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置，不再使用MASTER_LOG_FILE+MASTER_LOG_POS开启复制。而是使用MASTER_AUTO_POSTION=1的方式开始复制。

MySQL-5.6.5开始支持的，MySQL-5.6.10后开始完善

在传统的slave端，binlog是不用开启的，但是在GTID中slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。

GTID的组成：

GTID = source_id:transaction_id

source_id源id，用于鉴别原服务器，即mysql服务器唯一的的server_uuid，由于GTID会传递到slave，所以也可以理解为源ID。
transaction_id事务id，为当前服务器上已提交事务的一个序列号，通常从1开始自增长的序列，一个数值对应一个事务。

示例：

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

前面的一串为服务器的server_uuid，即3E11FA47-71CA-11E1-9E33-C80AA9429562，后面的23为transaction_id

GTID的工作原理：
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

========================================================================

master1 (master) ---------> master2 (slave)

192.168.122.10 192.168.122.20

建议把master2重置，因为上一个实验环境的原因

[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 -p'5ovl_*4WV0Ct' password '(TianYunYang123)'

MS流程 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@'%'

3. 初始化数据库 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
//这两个参数会将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议用
relay_log_recovery = on

2. 初始化数据库 导入数据

41/52

3. mysql > change master to master_host='master1', master_user='授权用户', master_password='授权密码', master_auto_position=1;
4. mysql > start slave; #启动slave角色
5. mysql > show slave status\G

双主双从部署

一.环境四台mysql服务器
192.168.122.196 master1 192.168.122.197 master2 192.168.122.198 slave1 192.168.122.199 slave2
二：配置

M--M master1：

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

gtid_mode=ON //开启gtid enforce_gtid_consistency=1
创建授权账户：

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 查找配置文件 >edit
change master to master_host='master1', master_user='授权用户', master_password='授权密码', master_auto_position=1;

start slave;

show slave status\G
注意：
master2上也要创建一样的授权用户，在master1上进行change master to 的操作 S--S
slave1：
首先备份master1的所有数据，mysqldump -u root -p123 -A > /all.sql 数据分别导入slave1和slave2，mysql -u root -p123 < /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_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_PASSWORD='big',

MASTER_AUTO_POSITION=1 FOR CHANNEL 'master2' >start slave;

show slave status\G

注意：slave1和slave2上做相同的change master to 操作。

读写分离

Mycat部署

galera(扩展)

mysql的组复制技术 group replication

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

43/52

准备环境：主机解析：
[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

关闭防火墙和selinux: setenforce 0 && systemctl stop firewalld
安装时间服务器：yum -y install ntp
时间同步：修改时区：

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

准备时间服务器：

#vim /etc/ntp.conf

server 127.127.1.0 # local clock fudge 127.127.1.0 stratum 10

#systemctl restart ntpd

客户端同步时间：

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

下载和安装Galera（每台都需要安装）

注意：Galera有独立的补丁包，也有在mysql基础上打好补丁的完整包我们要下载带wsrep扩展补丁的版本，比如：

44/52

MySQL 5.7.20 extended with wsrep patch version 25.13

所以：删除原版本mysql:

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

根据官方下载提示找到下载路径，发现下载路径下是已经做好yum源的路径，所以可以直接修改yum配置文件使用yum安装

配置yum源：
[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'

安装：

[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

每台启动服务修改密码：

#mysqladmin -u root -p'2rttwxb?3_oP' password 'Qianfeng123!' #mysql -u root -p'Qianfeng123!'

每台机 器创建用于数 据 同步的用户: mysql> grant all on . to 'syncuser'@'%' identified by 'QianFeng@123'; mysql> flush privileges;

配置Galera Replication:

galera1配置：主配置文件my.cnf追加如下内容，黑体部分是和其他节点不同的地方

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://' //第一台可以什么也不写 wsrep_node_name='mysql-galera-1' wsrep_node_address='192.168.245.133'
wsrep_sst_auth=syncuser:'QianFeng@123' //创建的授权用户和密码 wsrep_sst_method=rsync galera2配置：主配置文件my.cnf追加如下内容 server-id=2

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配置：主配置文件my.cnf追加如下内容 server-id=3

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配置：主配置文件my.cnf追加如下内容 server-id=4

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
重启服务：每台机器
[root@mysql-galera-1 ~]# systemctl restart mysqld

查看端口：galera端口4567 mysql端口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))
测试：
mysql> show status like 'wsrep%';
wsrep_incoming_addresses |
192.168.245.136:3306,192.168.245.135:3306,192.168.245.10:3306 |
| wsrep_cluster_size | 3 //表示一共有3个节点，我少配置了一个

阶段测试:
在任何一台机器上写数据，在其他机器上全部会同步

配置文件参数解释

46/52

1.binlog_format=row
① STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变
化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
② ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
③ MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

2.innodb_file_per_table=1
开启数据和索引存储到共享表空间

3.innodb_autoinc_lock_mode=2

由于这个模式下已经没有了auto_inc锁，所以这个模式下的性能是最好的；但是它也有一个问题，就是对于同一个语句来说它所得到的auto_incremant值可能不是连续的。

mycat部署

mycat 服务器端不需要安装mysql 客户端测试需要安装mysql
一：部署mycat
1.安装jdk环境
在/etc/profile下面写java的环境变量
JAVA_HOME=/usr/local/java
PATH=$JAVA_HOME/bin:$PATH
export JAVA_HOME PATH
source /etc/profile //使环境变量生效
安装mycat
2.在～/.bash_profile下面写mycat的环境变量
：/usr/local/mycat/bin
source ~/.bash_profile
然后启动mycat start
3.测试端口：
jps出现
1762 WrapperSimpleApp
2023 Jps
ss -ntpl | grep java
LISTEN 0 1 127.0.0.1:32000 : 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

===========================================================

二：mycat使用mysql中添加数据库和账户：
mysql> create database shop; mysql> create database bbs; mysql> create database blog;

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

47/52

设置3个账户和3个schema:
切换到mycat的工作目录： server.xml Mycat的配置文件，设置账号、参数等
schema.xml Mycat对应的物理数据库和数据库表的配置 rule.xml Mycat分片（分库分表）规则1、user标签 user 用户配置节点

-name 登录的用户名，也就是连接Mycat的用户名
-password 登录的密码，也就是连接Mycat的密码
-schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如需要这个用户需要管理两个数据库db1,db2，则配置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

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> show status like 'wsrep%'

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> show status like 'wsrep%'

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> show status like 'wsrep%'

48/52

balance=1 开启读写分离机制，所有读操作都发送到当前备用的 writeHost 上。 wirteType=0 所有写操作发送到第一个writeHost，第一个挂了切换到第二个 switchType=3 基于MySQL Galera cluster的切换机制，心跳语句为show status like 'wsrep%'

随便找台机器做客户端：

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

结论：
1.所有节点都正常writeHost负责写操作，备writeHost负责读操作

2.当第一个writeHost失效时，其中一个备的writeHost负责写操作，其他备的writeHost负责读操作
3.当只有一个writeHost时，同时负担读写

mycat优化部署

server.xml优化

一：server.xml 配置文件
1.privileges标签
对用户的 schema以及表进行精细化的DML(数据操纵语言)权限控制
--check 表示是否开启DML权限检查。默认是关闭。 --dml 顺序说明：insert,update,select,delete

tb02的权限是insert,update,select,delete。其他表默认是udpate,select。

2.system标签
这个标签内嵌套的所有 property 标签都与系统配置有关。
utf8
字符集
1
处理线程数量，默认是cpu数量。

4096
每次读取留的数量，默认4096。
409600

创建共享buffer需要占用的总空间大小。processorBufferChunkprocessors100。 0
默认为0。0表示DirectByteBufferPool，1表示ByteBufferArena。 100 二级共享buffer是processorBufferPool的百分比，这里设置的是百分比。 100
全局ID生成方式。(0:为本地文件方式，1:为数据库方式；2:为时间戳序列方式；3:为ZK生成ID；4:为ZK递增ID生成。
1
是否开启mysql压缩协议。1为开启，0为关闭，默认关闭。
4

指定 Mysql 协议中的报文头长度。默认 4。 16M
指定 Mysql 协议可以携带的数据最大长度。默认 16M。 1800000
指定连接的空闲超时时间。某连接在发起空闲检查下，发现距离上次使用超过了空闲时间，那么这个连接

49/52

会被回收，就是被直接的关闭掉。默认 30 分钟，单位毫秒。

3
前端连接的初始化事务隔离级别，只在初始化的时候使用，后续会根据客户端传递过来的属性对后端数据
库连接进行同步。默认为 REPEATED_READ，设置值为数字默认 3。 READ_UNCOMMITTED = 1;
READ_COMMITTED = 2;

REPEATED_READ = 3; SERIALIZABLE = 4;

300 SQL 执行超时的时间，Mycat 会检查连接上最后一次执行 SQL 的时间，若超过这个时间则会直接关闭这连
接。默认时间为 300 秒，单位秒。
1000

清理 NIOProcessor 上前后端空闲、超时和关闭连接的间隔时间。默认是 1 秒，单位毫秒。

300000
对后端连接进行空闲、超时检查的时间间隔，默认是 300 秒，单位毫秒。

10000
对后端所有读、写库发起心跳的间隔时间，默认是 10 秒，单位毫秒。

0.0.0.0 mycat 服务监听的 IP 地址，默认值为 0.0.0.0。 8066

定义 mycat 的使用端口，默认值为 8066。 9066
定义 mycat 的管理端口，默认值为 9066。 5.6
mycat 模拟的 mysql 版本号，默认值为 5.6 版本，如非特需，不要修改这个值，目前支持设置 5.5,5.6,5.7 版本，其他版本可能会有问题。

0
是否开启实时统计。1为开启；0为关闭 。

0
是否开启全局表一致性检测。1为开启；0为关闭 。

0

分布式事务开关。0为不过滤分布式事务；1为过滤分布式事务；2 为不过滤分布式事务,但是记录分布式事务日志。

65535

默认是65535。 64K 用于sql解析时最大文本长度以上举例的属性仅仅是一部分，可以配置的变量很多。
System标签下的属性，一般是上线后，需要根据实际运行的情况，分析后调优的时候进行修改。

3. Firewall标签
   防火墙的设置，也就是在网络层对请求的地址进行限制，主要是从安全角度来保证Mycat不被匿名IP进行访
   问

50/52

schema.xml优化

一：schema.xml
-schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应
-dataNode 分片信息，也就是分库相关配置 -dataHost 物理数据库，真正存储数据的数据库

1、schema 标签

schema标签用来定义mycat实例中的逻辑库，mycat可以有多个逻辑库，每个逻辑库都有自己的相关配置。可以使用schema标签来划分这些不同的逻辑库,如果不配置schema标签，所有表的配置会属于同一个默认的
逻辑库。逻辑库的概念和MySql的database的概念一样，我们在查询两个不同逻辑库中的表的时候，需要切换到该逻辑库下进行查询。

-name 逻辑数据库名，与server.xml中的schema对应 -checkSQLschema 数据库前缀相关设置，当该值为true时，例如我们执行语句select from
TESTDB.company 。mycat会把语句修改为 select from company 去掉TESTDB。
-sqlMaxLimit 当该值设置为某个数值时，每条执行的sql语句，如果没有加上limit语句，Mycat会自动加上对应的值。不写的话，默认返回所有的值。需要自己sql语句加limit。

2、dataNode标签

datanode标签定义了mycat中的数据节点，也就是数据分片。一个datanode标签就是一个独立的数据分片。localhost1数据库实例上的db1物理数据库，这就组成一个数据分片，最后我们用dn1来标示这个分片。
-name 定义数据节点的名字，这个名字需要唯一。我们在table标签上用这个名字来建立表与分片对应的关
系

-dataHost 用于定义该分片属于哪个数据库实例，属性与datahost标签上定义的name对应 -database 用于定义该分片属于数据库实例上 的具体库。

3、dataHost标签
这个标签直接定义了具体数据库实例，读写分离配置和心跳语句。

select user()

-name 唯一标示dataHost标签，供上层使用
-maxCon 指定每个读写实例连接池的最大连接。
-minCon 指定每个读写实例连接池的最小连接，初始化连接池的大小
-balance 负载均称类型
balance="0"：不开启读写分离机制，所有读操作都发送到当前可用的writeHost上balance="1"：全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式（M1-S1，M2-S2 并且M1 M2互为主备），正常情况下，M2,S1,S2都参与select语句的负载均衡。balance="2"：所有读操作都随机的在writeHost、readHost上分发balance="3"：所有读请求随机的分发到writeHst对应的readHost执行，writeHost不负担读写压力。 -writeType 负载均衡类型。 writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新

启动后已切换后的为准，切换记录在配置文件中:dnindex.properties . writeType="1"，所有写操作都随机的发送到配置的 writeHost。1.5以后版本废弃不推荐。
-switchType -1不自动切换
1 默认值 自动切换
2 基于MySql主从同步的状态决定是否切换心跳语句为 show slave status 3 基于mysql galary cluster 的切换机制（适合集群） 心跳语句为 show status like 'wsrep%'
-dbType 指定后端链接的数据库类型目前支持二进制的mysql协议，还有其他使用jdbc链接的数据库，例

51/52

如：mongodb，oracle，spark等

-dbDriver 指定连接后段数据库使用的driver，目前可选的值有native和JDBC。使用native的话，因为这个值执行的是二进制的mysql协议，所以可以使用mysql和maridb，其他类型的则需要使用JDBC驱动来支持。
如果使用JDBC的话需要符合JDBC4标准的驱动jar 放到mycat\lib目录下，并检查驱动jar包中包括如下目录结构文件 META-INF\services\java.sql.Driver。 在这个文件写上具体的driver类名，例如com.mysql.jdbc.Driver writeHost readHost指定后端数据库的相关配置给mycat，用于实例化后端连接池。
-tempReadHostAvailable
如果配置了这个属性 writeHost 下面的 readHost 仍旧可用，默认 0 可配置（0、1）。
1）heartbeat标签
这个标签内指明用于和后端数据库进行心跳检查的语句。

例如：MYSQL 可以使用 select user()，Oracle 可以使用 select 1 from dual 等。 2) writeHost /readHost 标签
这两个标签都指定后端数据库的相关配置，用于实例化后端连接池。唯一不同的是，writeHost 指定写实例、readHost 指定读实例。
在一个 dataHost 内可以定义多个 writeHost 和 readHost。但是，如果 writeHost 指定的后端数据库宕机，那么这个 writeHost 绑定的所有 readHost 都将不可用。
另一方面，由于这个 writeHost 宕机，系统会自动的检测到，并切换到备用的 writeHost 上去。这两个标签的属性相同，这里就一起介绍。

-host 用于标识不同实例，一般 writeHost 我们使用M1，readHost 我们用S1。 -url 后端实例连接地址。Native：地址：端口 JDBC：jdbc的url -password 后端存储实例需要的密码
-user 后端存储实例需要的用户名字
-weight 权重 配置在 readhost 中作为读节点的权重 -usingDecrypt 是否对密码加密，默认0。

52/52