mysql

mysql

数据库原理

什么是数据库

数据库是存放数据的仓库。它的存储空间很大，可以存放百万条、千万条、上亿条数据。但是数据库并不是随意地将数据进行存放，是有一定的规则的，否则查询的效率会很低。当今世界是一个充满着数据的互联网世界，充斥着大量的数据。即这个互联网世界就是数据世界。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。

数据库管理系统

数据库管理系统是数据库系统的核心组成部分，主要完成对数据库的操作与管理功能，实现数据库对象的创建、数据库存储数据的查询、添加、修改与删除操作和数据库的用户管理、权限管理等。

数据库管理系统功能

数据定义

数据定义是数据库管理系统(DBMS)的核心功能之一，它涉及使用数据定义语言(DDL)来定义和创建数据库的各种对象和结构。

数据操作

数据库管理系统为用户提供了对数据库中的数据进行检索、插入、修改和删除等基本操作的功能。

数据库的维护

数据库的维护是确保数据库系统稳定、高效运行的关键过程。这包括数据载入、转换、转储以及数据库的重组合重构。

数据库的运行管理

数据库的运行管理是确保数据库系统稳定、高效和安全运行的关键过程。这包括事务管理、安全性完整性控制、并发控制和死锁检测、数据库恢复等功能。

数据组织、存储与管理

数据库管理系统（DBMS）的核心功能之一是数据组织、存储和管理。这包括对各种数据的分类组织，如数据字典、用户数据和存取路径。

通信

数据库管理系统（DBMS）的通信功能允许用户通过远程终端访问和使用数据库中的数据。

数据完整性控制

数据完整性控制是数据库管理系统确保数据准确性和一致性的关键功能。完整性是衡量数据准确性和一致性的标准。

数据库的传输

数据库管理系统（DBMS）的核心功能之一是处理数据的传输，确保用户程序与DBMS之间的高效通信。这种传输功能通常与操作系统紧密协调，以确保数据在各种环境中的流畅移动。

数据检索

数据检索是数据库管理系统（DBMS）的核心功能之一。它允许用户通过数据检索语言（DQL）查询数据库中的特定信息，而无需深入了解数据库的内部结构。

数据安全

数据安全是数据库管理系统(DBMS)的核心职责之一，旨在确保数据的安全性、机密性、完整性和可用性。

模式翻译

模式翻译是数据库管理系统中的一个关键功能，它涉及将用户通过数据定义语言（DDL）编写的数据库模式转换为系统的内部表示。

数据存取的物理构建

数据存取的物理构建是数据库管理系统为数据模式提供的物理存取和构建的有效方法与手段。

装入功能

装入功能是数据库管理系统中的一个关键功能，它涉及将实际的数据逐一导入并存储在物理设备上。数据更新

数据更新是数据库管理系统的一项核心功能。它允许用户修改数据库中的现有数据，确保数据与现实世界保持同步。数据更新通常涉及对数据的插入、删除和修改操作。

应用程序的编译

应用程序的编译是将包含访问数据库语句的应用程序转化为在数据库管理系统（DBMS）支持下可运行的目标程序的过程。

数据的组织与存取

数据库管理系统在数据组织与存取方面发挥着核心作用。它确保数据在外围储存设备上得到合理的物理组织，以便高效地存取。

数据字典管理

数据字典管理是数据库管理系统中的一个关键功能，专门用于管理数据库中的元数据。元数据是关于数据的数据，它描述了数据库的结构和属性。

交互式查询

交互式查询是数据库管理系统（DBMS）的核心功能之一。它允许用户通过易用的查询语言，如SQL，输入查询命令。

数据恢复

数据恢复是数据库管理系统的重要功能之一，主要用于将出现故障的数据库恢复到正常状态。

数据的服务

数据库管理系统为数据提供了多种服务功能。这些功能包括数据拷贝、转存、重组、性能检测和分析。

数据库管理系统分类

检索全网354篇文章，总结了以下20个答案

MySQL

MySQL是一款由瑞典MySQL AB公司开发的关系型数据库管理系统，现为Oracle旗下产品。它以开源和免费为特点，吸引了大量用户。

Oracle

Oracle是由Oracle公司提供的收费的大型关系型数据库管理系统，特别适用于处理大量的商业数据。该产品不仅功能强大，还具有高度的可配置性、可扩展性和可调整性。

DB2

DB2是IBM公司推出的大型收费关系型数据库管理系统。该系统能在所有主流平台上运行，包括Windows，特别适用于处理海量数据。

Sybase

Sybase是一种在UNIX和Windows平台上运行的大型关系型数据库系统。

PostgreSQL

PostgreSQL是一种开源的、对象-关系型数据库管理系统，具有高度的灵活性和强大的数据处理能力。

mysql的安装

yum安装

#添加yum源，一般有yum源，但官方源在国外，所以我们一般添加一个国内的源，如清华源：
[root@centos7 ~]#tee /etc/yum.repos.d/mysql.repo <<EOF
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
EOF
[root@localhost ~]#yum install mariadb-server -y
[root@localhost ~]#systemctl start mariadb.service
[root@localhost ~]#mysql
[root@localhost ~]#mysql_secure_installation 	#初始化设置  先输入密码 一路回车  

[root@localhost ~]# mysql -u root -p 	#登录mysql -u 指定登录用户  -p 用密码登录
Enter password:							#输入上面自己设置的密码 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

编译安装

安装相关依赖包

yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel   ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel

下载并解压缩源码包

[root@localhost ~]#cd /data
[root@localhost data]#tar xf  mysql-boost-5.7.20.tar.gz

[root@localhost data]#cd mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ 		#指定mysql的安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \	#指定mysql进程监听套接字文件（数据库连接文件）的存储路径
-DSYSCONFDIR=/etc \ 							#指定配置文件的存储路径
-DSYSTEMD_PID_DIR=/usr/local/mysql \			#指定进程文件的存储路径
-DDEFAULT_CHARSET=utf8  \						#指定默认使用的字符集编码，如utf8
-DDEFAULT_COLLATION=utf8_general_ci \			#指定默认使用的字符集校对规则
-DWITH_INNOBASE_STORAGE_ENGINE=1 \				#安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \				#安装ARCHIVE存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \				#安装BLACKHOLE存储引擎
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \			#安装FEDERATED存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data \			#指定数据库文件的存储路径
-DWITH_BOOST=boost \							#指定boost的路径，
-DWITH_SYSTEMD=1								#生成便于systemctl管理的文件
[root@localhost mysql-5.7.20]# make
[root@localhost mysql-5.7.20]# make install

创建MySQL用户和修改配置文件

[root@localhost mysql-5.7.20]# useradd -M -s /sbin/nologin mysql
[root@localhost mysql-5.7.20]#chown -R mysql:mysql /usr/local/mysql/
#对数据库目录进行权限调整
#对调整MySQL配置文件
[root@localhost mysql-5.7.20]#vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

[root@localhost mysql-5.7.20]#chown mysql:mysql /etc/my.cnf 	#改变属主 属组

#设置环境变量
[root@localhost ~]#echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost ~]#echo 'export PATH' >> /etc/profile
[root@localhost ~]#source /etc/profile		#刷新环境配置文件
#初始化数据库
[root@localhost ~]#cd /usr/local/mysql/
[root@localhost ~]#bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
[root@localhost ~]#cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
#数据库开启自启、开启、状态 
[root@localhost ~]#systemctl enable mysqld
[root@localhost ~]#systemctl start mysqld
[root@localhost ~]#systemctl status mysqld
[root@localhost ~]#netstat -anpt | grep 3306
#设置Mysql密码
[root@localhost ~]#mysqladmin -u root -p password
#开始初始密码为空  回车即可   然后输入新密码
#登录数据库
[root@localhost ~]#mysql -u root -p 
[root@localhost ~]# mysql -u root -p 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

二进制安装

准备用户

[root@localhost ~]# groupadd -r -g 306 mysql
[root@localhost ~]# useradd -r -g 306 -u 306 -d /data/mysql mysql

准备数据目录，建议使用逻辑卷

#可选做，后面的脚本mysql_install_db可自动生成此目录
[root@localhost ~]# mkdir /data/mysql
[root@localhost ~]# chown mysql:mysql /data/mysq

准备二进制程序

[root@localhost data]# tar xf mysql-VERSION-linux-x86_64.tar.gz -C /usr/local
[root@localhost data]# cd /usr/local
[root@localhost data]# ln -sv mysql-VERSION mysql
[root@localhost data]# chown -R root:root /usr/local/mysql/

准备配置文件

[root@localhost ~]# cd /usr/local/mysql
[root@localhost ~]# cp -b support-files/my-default.cnf   /etc/my.cnf
[root@localhost ~]# vim /etc/my.cnf
#mysql语句块中添加以下三个选项
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on  #在mariadb5.5以上版的是默认值，可不加
skip_name_resolve = on      #禁止主机名解析，建议使用 可选项

创建数据库文

[root@localhost ~]# cd /usr/local/mysql/
./scripts/mysql_install_db --datadir=/data/mysql --user=mysql

[root@localhost ~]# ll /data/mysql/

准备服务脚本，并启动服务

[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# service mysqld start

#如果有对应的service 文件可以执行下面
[root@localhost ~]# cp /usr/local/mysql/support-files/systemd/mariadb.service 
/usr/lib/systemd/system/
[root@localhost ~]# systemctl daemon-reload
[root@localhost ~]# systemctl enable --now mariadb

PATH路径

[root@localhost ~]# echo 		'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh

安全初始化

[root@localhost ~]# /usr/local/mysql/bin/mysql_secure_installation

优化操作

生成提示符

#查看mysql版本
[root@localhost ~]# mysql -V

#修改提示符

[root@localhost ~]# vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_

自动补全

yum安装可以 编译安装有问题

[mysql]
prompt=(mysql) [\\d]>\\_
auto-rehash
#自动补全 只能补全敲过的命令

客户端程序

• mysql: 交互式或非交互式的CLI工具
• mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成
• insert等写操作语句保存文本文件中
• mysqladmin：基于mysql协议管理mysqld
• mysqlimport：数据导入工具
• MyISAM存储引擎的管理工具
• myisamchk：检查MyISAM库
• myisampack：打包MyISAM表，只读

安装mycli 插件 客户端工具

客户端工具和mysql

[root@localhost opt]#yum -y install zlib-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel db4-devel libpcap-devel xz-devel libffi-devel
[root@localhost opt]#tar zxvf Python-3.7.7_.tgz
[root@localhost Python-3.7.7]#cd Python-3.7.7/
[root@localhost Python-3.7.7]#./configure --prefix=/usr/local/Python-3.7.7/
[root@localhost Python-3.7.7]#make  
[root@localhost Python-3.7.7]#make install
[root@localhost Python-3.7.7]#ln -s  /usr/local/Python-3.7.7/bin/python3.7  /usr/bin/python37
[root@localhost Python-3.7.7]#ln -s /usr/local/Python-3.7.7/bin/pip3.7 /usr/bin/pip37
[root@localhost Python-3.7.7]#pip37 install --upgrade pip -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com
#升级程序，要不然有可能安装失败
[root@localhost Python-3.7.7]#pip37 install mycli -i http://mirrors.aliyun.com/pypi/simple --trusted-host mirrors.aliyun.com
[root@localhost Python-3.7.7]#ln -s /usr/local/Python-3.7.7/bin/mycli /usr/bin/mycli
[root@localhost Python-3.7.7]#mycli -u root -p 123123

[root@localhost ~]#vim /etc/my.cnf                       
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
auto-rehash

图形化界面：

[root@localhost ~]#vim /etc/my.cnf
[mysqld]
skip_grant_tables

MySQL的基本操作

查看帮助信息

MariaDB [(none)]> help create；				#help 后面跟上具体命令可以查看帮助
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   CREATE DATABASE
   CREATE EVENT
   CREATE FUNCTION
   CREATE FUNCTION UDF
   CREATE INDEX
   CREATE PROCEDURE
   CREATE SERVER
   CREATE TABLE
   CREATE TABLESPACE
   CREATE TRIGGER
   CREATE USER
   CREATE VIEW
   SHOW
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SPATIAL

查看支持的字符集

MariaDB [(none)]> show charset;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
.........
| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)

查看默认使用的字符集

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

数据库管理指令

创建数据库

1.建立新的数据库
MariaDB [(none)]> create database wk1;			#create database 数据库名称；
Query OK, 1 row affected (0.01 sec)
2.查看数据库的基础消息
MariaDB [(none)]> show create database wk1;		#show create database 数据库名称；
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| wk1      | CREATE DATABASE `wk1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
3.新建数据库并指定字符集
MariaDB [(none)]> create database wk2 character set 'utf8';
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show create database wk2;		#查看数据库的基础信息
+----------+--------------------------------------------------------------+
| Database | Create Database                                              |
+----------+--------------------------------------------------------------+
| wk2      | CREATE DATABASE `wk2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
4.先判断数据库是否存在，若存在就不创建数据库，若不存在就创建数据库。
MariaDB [(none)]> create database if not exists db1;
Query OK, 1 row affected, 1 warning (0.00 sec)		#数据库存在
MariaDB [(none)]> create database if not exists db0;
Query OK, 1 row affected (0.00 sec)					#数据库不存在

删除数据库

MariaDB [(none)]> drop database  e;		#drop database 数据库名称;
Query OK, 0 rows affected (0.00 sec)

查看数据库列表

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |		#系统自带数据库  类似管理数据库
| bake               |
| mysql              |		#数据库账户和密码存储位置
| performance_schema |
| q                  |
| r                  |
| w                  |
+--------------------+
7 rows in set (0.00 sec)

切换数据库

MariaDB [(none)]> use 数据库名称;
Database changed
列子：
(mysql) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

(mysql) [(none)]> use sys;
Database changed
(mysql) [sys]> use mysql;
Database changed
(mysql) [mysql]> 

创建数据表和查看数据表

新建数据表和查看数据表

MariaDB [q]> create table minxin (id int, name varchar(10), gender char(2),phone varchar(11));
Query OK, 0 rows affected (0.00 sec)
###创建新的表####
create table 表名 （字段1 数据类型，字段2 数据类型[,...] [,PRIMARY KEY (主键名)]）；
#主键一般选择代表唯一性的字段不允许为空值（null）,且一个表只能有一个主键###

MariaDB [q]> show tables;		#查看当前数据库下所以的数据表
+-------------+
| Tables_in_q |
+-------------+
| minxin      |
+-------------+
1 row in set (0.00 sec)


unsigned：取消负数
primary  key：主键
auto_increment： 自增长
enum('M','F')：多选 
default 'M'：默认值为 M


MariaDB [q]>insert student (name,age) values('ben',19);
#加入数据，但加入的数据要是该表里的列。
MariaDB [q]>insert student values();
#加入空字段
MariaDB [q]>select * from student;
#查找 会发现多一条空记录

添加字段

例子：

mysql [db1]> alter table student add   phone char(11) not null ;
             命令字  命令字  表名   关键字  子段名称字段属性 
  
mysql [db1]> desc student;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| id     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)          | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned  | YES  |     | NULL    |                |
| gender | enum('M','F')        | YES  |     | M       |                |
| phone  | char(11)             | NO   |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

修改字段 名称

例子：

mysql [db1]> alter table student change phone mobile char(11);
mysql [db1]> desc student;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| id     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)          | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned  | YES  |     | NULL    |                |
| gender | enum('M','F')        | YES  |     | M       |                |
| mobile | char(11)             | YES  |     | NULL    |                |
+--------+----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

删除字段

例子：

mysql [db1]> alter table student drop mobile;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [db1]> desc student;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| id     | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(10)          | YES  |     | NULL    |                |
| age    | tinyint(3) unsigned  | YES  |     | NULL    |                |
| gender | enum('M','F')        | YES  |     | M       |                |
+--------+----------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

查看当前使用用户

(mysql) [mysql]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)

查看所以用户
(mysql) [(none)]> SELECT User, Host FROM mysql.user;
+---------------+---------------+
| User          | Host          |
+---------------+---------------+
| zhou          | 192.168.10.%  |
| ZHJ           | 192.168.10.10 |
| cxk           | 192.168.10.10 |
| lisi          | 192.168.10.10 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
7 rows in set (0.00 sec)

新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY '密码'];
-----------------------------------------------------------------------------------------
'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
       若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
       若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）
-----------------------------------------------------------------------------------------
例子：
(mysql) [(none)]> create user lisi@'192.168.10.10' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)

(mysql) [(none)]> create user cxk@'192.168.10.10' identified by password '*6691484EA6B50DDDE1926A220DA01FA9E575C18A';
Query OK, 0 rows affected, 1 warning (0.00 sec)

修改用户名称

格式：
rename user '旧名字' to '新名字;

例子：
(mysql) [(none)]> SELECT User, Host FROM mysql.user;
+---------------+---------------+
| User          | Host          |
+---------------+---------------+
| zhou          | 192.168.10.%  |
| zhang         | 192.168.10.10 |
| cxk           | 192.168.10.10 |
| lisi          | 192.168.10.10 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
7 rows in set (0.00 sec)

(mysql) [(none)]> rename user 'zhang'@'192.168.10.10' to 'ZHJ'@'192.168.10.10';
Query OK, 0 rows affected (0.00 sec)

(mysql) [(none)]> SELECT User, Host FROM mysql.user;
+---------------+---------------+
| User          | Host          |
+---------------+---------------+
| zhou          | 192.168.10.%  |
| ZHJ           | 192.168.10.10 |
| cxk           | 192.168.10.10 |
| lisi          | 192.168.10.10 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
7 rows in set (0.00 sec)

删除用户

格式：drop user '用户名@地址';
例子：
(mysql) [(none)]> SELECT User, Host FROM mysql.user;
+---------------+---------------+
| User          | Host          |
+---------------+---------------+
| zhou          | 192.168.10.%  |
| ZHJ           | 192.168.10.10 |
| cxk           | 192.168.10.10 |
| lisi          | 192.168.10.10 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
7 rows in set (0.00 sec)

(mysql) [(none)]> drop user 'lisi'@'192.168.10.10';
Query OK, 0 rows affected (0.00 sec)

(mysql) [(none)]> SELECT User, Host FROM mysql.user;
+---------------+---------------+
| User          | Host          |
+---------------+---------------+
| zhou          | 192.168.10.%  |
| ZHJ           | 192.168.10.10 |
| cxk           | 192.168.10.10 |
| mysql.session | localhost     |
| mysql.sys     | localhost     |
| root          | localhost     |
+---------------+---------------+
6 rows in set (0.00 sec)

修改用户密码

格式：
SET PASSWORD = PASSWORD('abc123');					#只能改自己当前

set password for '用户' = password('密码');		 	 #修改其他用户和密码

例子：
(mysql) [(none)]> set password = 'qwe123';
Query OK, 0 rows affected (0.00 sec)


(mysql) [(none)]> set password for 'ZHJ'@'192.168.10.10' = 'qwe123';
Query OK, 0 rows affected (0.00 sec)

破解密码

修改配置文件

vim /etc/my.cnf
[mysqld]
skip-grant-tables 
#数据库的单用户模式   此模式下权限受到限制，很多功能无法使用， 除了破解密码不要加此项
skip-networking  #MySQL8.0不需要

#然后清空密码
update mysql.user set authentication_string='' where user='root' and host='localhost';
#注意刷新后生效
flush privileges;

日志

MySQL 支持丰富的日志类型，如下：

• 事务日志：transaction log

  事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging事务日志文件： ib_logfile0， ib_logfile1

• 错误日志 error log

• 通用日志 general log

• 慢查询日志 slow query log

• 二进制日志 binary log

• 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

语法：

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])
routime_body 
proc_parameter : [IN|OUT|INOUT] parameter_name type

例子：

delimiter //
CREATE PROCEDURE selectById(IN id SMALLINT UNSIGNED)
BEGIN
 SELECT * FROM students WHERE stuid = id;
END//
delimiter ;
call selectById(2);

事务日志

事务日志：transaction log

• redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log
• undo log：保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

Innodb事务日志相关配置：

show variables like '%innodb_log%';
innodb_log_file_size   50331648 #每个日志文件大小  字节
innodb_log_files_in_group 2     #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径

ll -h /var/lib/mysql

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2


select @@innodb_flush_log_at_trx_commit;
#查看默认值

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性


0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务


2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

级别	0	1	2
安全性	较高	最高	最高
性能	最高	最差	较高

高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快

2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

双1设置：

说明：

• 设置为1，同时sync_binlog = 1表示最高级别的容错 （二进制日志）

• innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB 10.2.6 后废弃）

演示：

[root@localhost ~]# mysql  -uroot -p'自己的密码' 随便数据库 < testlog.sql
#生成测试环境
(root@localhost) [hellodb]> call sp_testlog;
#生成  一百万条数据


修改参数：
set  global innodb_flush_log_at_trx_commit=1;
select @@innodb_flush_log_at_trx_commit;
call sp_testlog;

错误日志

错误日志

• mysqld启动和关闭过程中输出的事件信息
• mysqld运行中产生的错误信息
• event scheduler运行一个event时产生的日志信息
• 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;

yum  安装
cat /var/log/mysqld.log

记录哪些警告信息至错误日志文件*

#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3...         #MySQL5.7之前
log_error_verbosity=0|1|2|3...   #MySQL8.0

例子：

SHOW GLOBAL VARIABLES LIKE 'log_warnings';

通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句

通用日志可以保存在：file（默认值）或 table（mysql.general_log表）

通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

例子：

范例: 启用通用日志并记录至文件中

select @@general_log;     #默认没开启
set global general_log=1; #开启

SHOW GLOBAL VARIABLES LIKE 'log_output';
#默认通用日志存放在文件中
select @@general_log_file;
#通用日志存放的文件路径

慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询相关变量

slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
long_query_time=N #慢查询的阀值，单位秒,默认为10s
slow_query_log_file=HOSTNAME-slow.log  #慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk 
#上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON  #不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语
句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 #多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain #记录内容
log_slow_queries = OFF    #同slow_query_log，MariaDB 10.0/MySQL 5.6.1 版后已删除


set  global slow_query_log=1;
#开启
set long_query_time=1;


select sleep(10)

二进制日志 备份

• 记录导致数据改变或潜在导致数据改变的SQL语句

• 记录已提交的日志

• 不依赖于存储引擎类型

功能：通过"重放"日志文件中的事件来生成数据副本

注意：建议二进制日志和数据文件分开存放

二进制日志记录三种格式

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少

基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式

混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

show variables like 'binlog_format';

二进制日志文件格式

有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：

sql_log_bin=ON|OFF：
#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=mysql-bin      默认是关闭
#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：
#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：
#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m 
#此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m 
#限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：
#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：
#二进制日志可以自动删除的天数。 默认为0，即不自动删除

在线查看 二进制

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
例子：
show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

离线查看二进制日志

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志

命令格式：

mysqlbinlog [OPTIONS] log_file…
 --start-position=# 指定开始位置
 --stop-position=#
 --start-datetime=  #时间格式：YYYY-MM-DD hh:mm:ss
 --stop-datetime= 
 --base64-output[=name]
        -v -vvv
        
# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1     
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

例子：

mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog  --start-datetime="2018-01-30 20:30:10"   --stop-datetime="2018-01-
30 20:35:22" mariadb-bin.000003 -vvv

二进制日志事件的格式：

# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1     
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

备份

备份类型

• 完全备份，部分备份

• 完全备份：整个数据集

• 部分备份：只备份数据子集，如部分库或表

• 完全备份、增量备份、差异备份

• 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

  增量备份

差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

注意：二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份

• 冷备：读、写操作均不可进行，数据库停止服务

• 温备：读操作可执行；但写操作不可执行

• 热备：读、写操作均可执行

MyISAM：温备，不支持热备 不支持 事务

InnoDB：都支持

• cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份

• LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份

• mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部

  分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份

• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份

• MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现

• mysqlbackup：热备份， MySQL Enterprise Edition 组件

• mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、

  FLUSH TABLES和cp或scp来快速备份数据库

完全备份

物理冷备份

通过打包备份 数据库文件夹的方式备份（一般用于数据库迁移）

备份方式： 冷备份，一定要先停数据库

备份工具： cp tar 等

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。

例子：

cd  /var/lib/
#在需要备份的主机上进入到数据库文件夹

tar zcvf /opt/mysql_back.tar.gz  mysql
#打包数据库文件夹

cd  /opt
scp mysql_back.tar.gz 192.168.10.10:/opt/
#远程拷贝


第二台主机
tar xf mysql_back.tar.gz
#解压

cd /var/lib/
mv mysql mysql.bak  -r
#先备份原来的 数据库

cp -a /opt/mysql     /var/lib/

mysqldump 备份与恢复

mysqldump [OPTIONS] database [tables]   
#支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
#支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]       
#备份所有数据库，包含数据库本身定义也会备份

mysqldump 常见通用选项：

-A, --all-databases 
#备份所有数据库，含create database
-B, --databases db_name…  
#指定备份的数据库，包括create database语句
-E, --events：
#备份相关的所有event scheduler
-R, --routines：
#备份所有存储过程和自定义函数
--triggers：
#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
-d, --no-data    
#只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info 
#只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db 
#不备份create database，可被-A或-B覆盖
--flush-privileges 
#备份mysql或相关时需要使用
-f, --force       
#忽略SQL错误，继续执行
--hex-blob        
#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick     
#不缓存查询，直接输出，加快备份速度

脚本

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=magedu
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > 
${DIR}/${DB}_${TIME}.sql.gz

完全备份一个或多个完整的库（包括其中所有的表）

语法：

mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql	
#导出的就是数据库脚本文件

例子：

mysqldump -uroot -p'Admin@123' hellodb  > /data/hellodb.sql
#单库   --databases   不加无法保存库名，加了会保存库名

mysqldump -uroot -p'Admin@123' --databases hellodb db1  > /data/hellodb.sql
#多库

mysqldump -uroot -p'Admin@123' --all-databases > /data/all_data.sql
#全库

mysqldump -uroot -p'Admin@123' hellodb students > /data/students.sql
#单表

mysqldump -uroot -p'Admin@123' hellodb students teachers > /data/students.sql
#多表

mysqldump -uroot -p'Admin@123' -d  hellodb  students > /data/students.sql
#只保留表结构  -d

mysql -uroot -pAdmin@123 < /data/hellodb.sql

恢复例子：

mysql -uroot -pAdmin@123 -e 'show databases;'
#-e  在linux中执行数据库

mysql -uroot -pAdmin@123 -e 'drop database hellodb;'
# 删库

mysql -uroot -pAdmin@123 < /data/hellodb.sql

mysql -uroot -pAdmin@123 -e 'create database hellodb;'
mysql -uroot -pAdmin@123 hellodb < /data/hellodb.sql·
mysql -uroot -pAdmin@123 -e 'show tables from hellodb;'

增量备份恢复

备份

1开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin = mysql-bin
binlog_format = MIXED				#可选，指定二进制日志(binlog)的记录格式为 MIXED
server-id = 1

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT


systemctl restart mysqld
ls -l /var/lib/mysql-bin.*

2可每周对数据库或表进行完全备份
mysqldump -u root -pAdmin@123 hellodb students > /opt/my_h_s_$(date +%F).sql
mysqldump -u root -p --databases hellodb > /opt/my_$(date +%F).sql

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs

4．插入新数据，以模拟数据的增加或变更
use kgc;
insert into students values(3,'user3','male','game');
insert into students values(4,'user4','female','reading');

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中

6．查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码并按行读取
#-v：显示详细内容

恢复

1．一般恢复
（1）模拟丢失更改的数据的恢复步骤
use kgc;
delete from info1 where id=3;
delete from info1 where id=4; 

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -pAdmin@123

（2）模拟丢失所有数据的恢复步骤
use kgc;
drop table info1;



2．断点恢复
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例：
# at 302
#201122 16:41:16
插入了“user3”的用户数据

# at 623
#201122 16:41:24
插入了“user4”的用户数据


（1）基于位置恢复
#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-position='682' /opt/mysql-bin.000002 | mysql -uroot -p

（2）基于时间点恢复
#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p


如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

主从复制

实战

假设主节点的数据库已经运行了一段时间，产生了一定量的数据，主从复制只能复制开启后数据，那之前的数据如何处理？

主节点

[root@localhost ~]#systemctl stop firewalld
[root@localhost ~]#setenforce 0

[root@localhost ~]#mysql  -uroot -pabc123  <  hellodb_innodb.sql 



#开启二进制日志
[root@localhost ~]#vim  /etc/my.cnf
erver-id=102
log-bin=/data/mysql/binlog/mysql-bin
[root@localhost ~]# mkdir  -p  /data/mysql/binlog
[root@localhost ~]# chown mysql.mysql /data/  -R
[root@localhost ~]# systemctl  restart  mysqld


[root@localhost ~]#mysqldump -uroot -pabc123 -A -F --master-data=1  --single-transaction |gzip  > /data/all.sql.gz


[root@localhost ~]#mysql  -uroot -pabc123

create   user  test@'192.168.91.%' identified by "Admin@123";   #新建主从复制用户
 
grant replication slave on *.*  to test@'192.168.91.%';         # 授权主从复制用户  


[root@localhost ~]#scp  /data/all.sql   192.168.91.102:/opt

从节点

[root@localhost ~]#systemctl stop firewalld
[root@localhost ~]#setenforce 0


#开启二进制日志
[root@localhost ~]#vim  /etc/my.cnf
server-id=102
log-bin=/data/mysql/binlog/mysql-bin
[root@localhost ~]# mkdir  -p  /data/mysql/binlog
[root@localhost ~]# chown mysql.mysql /data/  -R
[root@localhost ~]# systemctl  restart  mysqld



#修改备份脚本
[root@localhost ~]# vim  /opt/all.sql
#  找到    CHANGE MASTER TO  的行修改如下
CHANGE MASTER TO
  MASTER_HOST='192.168.91.100',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
 
#由于之前再备份的时候加入了    --master-data=1 选项    就添加了主从复制的选项000002文件的154位置往后开始主从复制
#000002文件154位置之前的  配置由  备份文件自行实现 



[root@localhost ~]#mysql  -uroot -pabc123
#进入数据库

mysql> set sql_log_bin=0;            #关闭二进制日志
Query OK, 0 rows affected (0.00 sec)


mysql> source /opt/all.sql           #导入数据库

mysql> start  slave;

mysql> show slave status\G;        #查看从节点的状态

MHA 实验

机器	作用
7-4 192.168.91.103	MHA管理节点
7-1 192.168.91.100	主
7-2 192.168.91.101	从
7-3 192.168.91.102	从

准备文件

7-4 需要客户端和服务端，其余只需要客户端

关闭防火墙selinux

systemctl disable --now firewalld
setenforce 0

主节点（7-4）安装 管理和客户端工具

[root@localhost opt]#yum install epel-release.noarch -y
#有依赖性用yum安装  需要先安装  epel源

[root@localhost data]#ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  mha4mysql-node-0.58-0.el7.centos.noarch.rpm

[root@localhost opt]#yum -y install mha4mysql-*.rpm

其余所有节点（7-1，7-2，7-3）安装客户端

[root@localhost opt]#yum install epel-release.noarch -y
[root@localhost data]# yum install  mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

所有节点 基于key验证

[root@localhost data]#ssh-keygen 
[root@localhost data]#ssh-copy-id  127.0.0.1
#自己和自己连生成 秘钥

[root@localhost data]#cd

[root@localhost data]#rsync -a .ssh   192.168.91.100:/root/
[root@localhost data]#rsync -a .ssh   192.168.91.101:/root/
[root@localhost data]#rsync -a .ssh   192.168.91.102:/root/
#注意.ssh 后不能加/    -a  保留属性

主节点（7-4）建立mha文件夹和配置文件

[root@localhost ~]#mkdir /etc/mastermha
[root@localhost ~]#vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=Admin@123
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=test
repl_password=Admin@123
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
check_repl_delay=0
master_binlog_dir=/data/mysql/


[server1]
hostname=192.168.91.100
candidate_master=1

[server2]
hostname=192.168.91.101
candidate_master=1

[server3]
hostname=192.168.91.102

准备切换脚本

[root@localhost ~]#vim  master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.91.188/24';
my $gateway = '192.168.91.2';
my $interface = 'ens33';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
# A simple system call that enable the VIP on the new master
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}




[root@localhost ~]#cp master_ip_failover   /usr/local/bin/
# 移动文件到对应的地方 之前的配置文件中规定了地方
[root@localhost ~]#chmod +x  /usr/local/bin/master_ip_failover 
#加上执行权限

实现主从复制

主服务器操作（7-1）

[root@localhost ~]#vim  /etc/my.cnf
#修改文件
[mysqld]
server_id=100
log-bin=/data/mysql/mysql-bin
skip_name_resolve=1
general_log 
#通用日志






[root@localhost data]#mysql -uroot -pabc123
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


mysql> grant replication slave on *.* to test@'192.168.91.%' identified by 'Admin@123';
#建立复制用户
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all on *.* to mhauser@'192.168.91.%' identified by 'Admin@123';
#建立  mha管理账户
Query OK, 0 rows affected, 1 warning (0.00 sec)

从服务器设置（7-2,7-3）

服务器7-2配置

[root@localhost ~]#vim  /etc/my.cnf
#修改文件
server_id=101
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1 
general_log 



[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld


[root@localhost data]#mysql -uroot -pabc123
CHANGE MASTER TO
  MASTER_HOST='192.168.91.100',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
  注意最后分号
  
mysql> start slave;
mysql> show slave status\G;

服务器7-3配置

[root@localhost ~]#vim  /etc/my.cnf
#修改文件
server_id=102
log-bin=/data/mysql/mysql-bin
read_only
relay_log_purge=0
skip_name_resolve=1 
general_log 



[root@localhost ~]#mkdir /data/mysql/   -p
#建立文件夹
[root@localhost ~]#chown mysql.mysql /data/ -R
#注意修改权限
[root@localhost ~]#systemctl restart mysqld


[root@localhost data]#mysql -uroot -pabc123
CHANGE MASTER TO
  MASTER_HOST='192.168.91.100',
  MASTER_USER='test',
  MASTER_PASSWORD='Admin@123',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;
  注意最后分号
  
mysql> start slave;
mysql> show slave status\G;

设置虚拟地址

在 mysql 主节点上配置 虚拟地址 也就是7-1

[root@localhost ~]#ifconfig ens33:1 192.168.91.188/24

在运行前需要先检测环境是否符合

在管理节点 7-3 上执行

检测 ssh 免密登录是否成功

[root@localhost ~]#masterha_check_ssh --conf=/etc/mastermha/app1.cnf
Thu Jul  4 23:55:53 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jul  4 23:55:53 2024 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Thu Jul  4 23:55:53 2024 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Thu Jul  4 23:55:53 2024 - [info] Starting SSH connection tests..
Thu Jul  4 23:55:55 2024 - [debug] 
Thu Jul  4 23:55:53 2024 - [debug]  Connecting via SSH from root@192.168.91.100(192.168.91.100:22) to root@192.168.91.101(192.168.91.101:22)..
Thu Jul  4 23:55:54 2024 - [debug]   ok.
Thu Jul  4 23:55:54 2024 - [debug]  Connecting via SSH from root@192.168.91.100(192.168.91.100:22) to root@192.168.91.102(192.168.91.102:22)..
Thu Jul  4 23:55:55 2024 - [debug]   ok.
Thu Jul  4 23:55:56 2024 - [debug] 
Thu Jul  4 23:55:54 2024 - [debug]  Connecting via SSH from root@192.168.91.102(192.168.91.102:22) to root@192.168.91.100(192.168.91.100:22)..
Thu Jul  4 23:55:55 2024 - [debug]   ok.
Thu Jul  4 23:55:55 2024 - [debug]  Connecting via SSH from root@192.168.91.102(192.168.91.102:22) to root@192.168.91.101(192.168.91.101:22)..
Thu Jul  4 23:55:56 2024 - [debug]   ok.
Thu Jul  4 23:55:56 2024 - [debug] 
Thu Jul  4 23:55:54 2024 - [debug]  Connecting via SSH from root@192.168.91.101(192.168.91.101:22) to root@192.168.91.100(192.168.91.100:22)..
Thu Jul  4 23:55:55 2024 - [debug]   ok.
Thu Jul  4 23:55:55 2024 - [debug]  Connecting via SSH from root@192.168.91.101(192.168.91.101:22) to root@192.168.91.102(192.168.91.102:22)..
Thu Jul  4 23:55:55 2024 - [debug]   ok.
Thu Jul  4 23:55:56 2024 - [info] All SSH connection tests passed successfully.

报错

[root@mha-manager ~]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
#如果设置了默认字符集起不来    在  /etc/my.cnf  文件中
unknown variable 'default-character-set=utf8'

检测主从复制 是否可以

[root@localhost /]#masterha_check_repl --conf=/etc/mastermha/app1.cnf
#  --conf=/etc/mastermha/app1.cnf  指明配置文件
.........................................................
.........................................................
.........................................................
Checking the Status of the script.. OK 
Fri Jul  5 00:03:44 2024 - [info]  OK.
Fri Jul  5 00:03:44 2024 - [warning] shutdown_script is not defined.
Fri Jul  5 00:03:44 2024 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

查看状态未开启

[root@localhost /]#masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

开启mha

#开启MHA,默认是前台运行,生产环境一般为后台执行
nohup masterha_manager --conf=/etc/mastermha/app1.cnf &> /dev/null 
#非后台
masterha_manager --conf=/etc/mastermha/app1.cnf 

#查看状态
masterha_check_status --conf=/etc/mastermha/app1.cnf  

测试

mha 如何发现主节点宕机

通过发送 SELECT 1 As Value 指令 把1 设置成 value 给主， 主无法执行就认为他死了

[root@localhost mysql]#tail -f   /var/lib/mysql/localhost.log 
2024-07-04T16:11:14.137683Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:15.137991Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:16.137965Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:17.138401Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:18.138703Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:19.138877Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:20.139094Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:21.139400Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:22.140600Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:23.140507Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:24.141510Z	    9 Query	SELECT 1 As Value
2024-07-04T16:11:25.141256Z	    9 Query	SELECT 1 As Value

查看 mha 服务的日志

[root@localhost ~]#tail  -f  /data/mastermha/app1/manager.log 

IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.91.188/24;/sbin/arping -I ens33 -c 3 -s 192.168.91.188/24 192.168.91.2 >/dev/null 2>&1===

Checking the Status of the script.. OK 
Fri Jul  5 00:08:27 2024 - [info]  OK.
Fri Jul  5 00:08:27 2024 - [warning] shutdown_script is not defined.
Fri Jul  5 00:08:27 2024 - [info] Set master ping interval 1 seconds.
Fri Jul  5 00:08:27 2024 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Fri Jul  5 00:08:27 2024 - [info] Starting ping health check on 192.168.91.100(192.168.91.100:3306)..
## Fri Jul  5 00:08:27 2024 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

模拟 mysql 主节点故障

切换的过程 会将 从服务器的 readonly 指令改成可写

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

切换成我们指定的 101

在从节点 102 上查看slave 信息 可以看到指向 新的主

mysql> show slave status\G;

查看日志

[root@localhost ~]#tail  -f  /data/mastermha/app1/manager.log 

----- Failover Report -----

app1: MySQL Master failover 192.168.91.100(192.168.91.100:3306) to 192.168.91.101(192.168.91.101:3306) succeeded

Master 192.168.91.100(192.168.91.100:3306) is down!

Check MHA Manager logs at localhost.localdomain:/data/mastermha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.91.100(192.168.91.100:3306)
The latest slave 192.168.91.101(192.168.91.101:3306) has all relay logs for recovery.
Selected 192.168.91.101(192.168.91.101:3306) as a new master.
192.168.91.101(192.168.91.101:3306): OK: Applying all logs succeeded.
192.168.91.101(192.168.91.101:3306): OK: Activated master IP address.
192.168.91.102(192.168.91.102:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.91.102(192.168.91.102:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.91.101(192.168.91.101:3306)
192.168.91.101(192.168.91.101:3306): Resetting slave info succeeded.
#   Master failover to 192.168.91.101(192.168.91.101:3306) completed successfully.

并且虚拟ip也在101 服务器上出现

[root@node2 ~]#ifconfig