一、Msql在服务器中的部署方法
官网：http://www.mysql.com
在企业中90%的服务器操作系统均为Linux
在企业中对于Mysql的安装通常用源码编译的方式来进行
1.1 在Linux下部署MySQL
1.1.1 部署环境
| 主机 | IP | 角色 |
|---|---|---|
| MySQL-node1 | 172.25.254.13 | master |
| MySQL-node2 | 172.25.254.23 | slave1 |
| MySQL-node3 | 172.25.254.33 | slave2 |
注意：在部署MySQL前，先检查主机中的环境是否含有其他数据库，保持环境的纯净性，以防在部署时出现其他错误。另外以下所有案例的实现操作系统都为Rhel 7.9
# 检查是否含有其他数据库，一般为Maridb
[root@mysql-node ~]# rpm -qa | grep mariadb
[root@mysql-node ~]# yum remove mariadb # 移除mariadb
# 关闭防火墙
[root@mysql-node1 mysql]# systemctl stop firewalld
[root@mysql-node1 mysql]# systemctl mask firewalld
[root@mysql-node1 mysql]# setenforce 0
1.1.2 下载并解压MySQL源码包
（1）进入到MySQL官网
（2）选择MySQL Community Downloads
（3）选择Archives
（4）自行选择版本下载
（5）选择链接在Linux中进行下载并解压源码包
[root@mysql-node1 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.44.tar.gz # 下载源码包
[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz # 解压源码包
[root@mysql-node1 ~]# cd /root/mysql-5.7.44 # 进入到解压目录
1.1.3 安装依赖性
[root@mysql-node3 mysql-5.7.44]# yum install cmake gcc-c++ openssl-devel \
> ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rp^Cen.x86_64 -y
1.1.4 源码编译安装MySQL
[root@mysql-node1 mysql-5.7.44]# cmake \
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ # 指定安装路径
> -DMYSQL_DATADIR=/data/mysql \ # 指定数据目录
> -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ # 指定套接字文件
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \ # 指定启用INNODB存储引擎，默认使用myisam
> -DWITH_EXTRA_CHARSETS=all \ # 扩展字符集
> -DDEFAULT_CHARSET=utf8mb4 \ # 指定默认字符集
> -DDEFAULT_COLLATION=utf8mb4_unicode_ci \ # 指定默认校验字符集
> -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ # 指定C++库依赖[root@mysql-node1 mysql-5.7.44]# make -j2 # -j2使用两个核心来跑两个进程
[root@mysql-node1 mysql-5.7.44]# make install # 编译完后进行安装
1.1.5 部署MySQL
1.# 创建mysql的用户及用户组
[root@mysql-node1 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 ~]# mkdir /data/mysql -p
[root@mysql-node1 ~]# chown mysql.mysql -R /data/mysql2.# 生成启动脚本
[root@mysql-node1 ~]# cd /usr/local/mysql/support-files/
[root@mysql-node1 support-files]# cp mysql.server /etc/init.d/mysqld3.# 修改环境变量
[root@mysql-node1 ~]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@mysql-node1 ~]# source ~/.bash_profile 4.# 修改配置文件
[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql # 指定数据目录
socket=/data/mysql/mysql.sock # 指定套接字
symbolic-links=0 # 数据只能存放到数据目录中，禁止链接到数据目录5.# 数据库初始化建立mysql基本数据
[root@mysql-node1 ~]# mysql --user mysql --initialize # 记得保存生成的初始化密码，在接下来的安全初始化需要用到
[root@mysql-node1 ~]# /etc/init.d/mysqld start # 开启MySQL服务
[root@mysql-node1 ~]# chkconfig mysqld on # 设置自启动
[root@mysql-node1 ~]# chkconfig --list # 查看自启动的服务6.# 数据库安全初始化
[root@mysql-node1 ~]# mysql_secure_installation
测试：登录数据库
[root@mysql-node1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.44 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases; # 查看当前含有的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)mysql>
二、MySQL的主从复制
2.1 MySQL的主从复制原理
三个线程
实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，一个主库线程，两个从库线程。
-
二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
-
从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
-
从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。
复制三步骤
步骤1：Master将写操作记录到二进制日志（binlog）。
步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；
步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从接入点开始复制。
具体操作
1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息
2.master端在设定好binlog启动后会开启binlog dump的线程
3.master端的binlog dump把二进制的更新发送到slave端的
4.slave端开启两个线程，一个是I/O线程，一个是sql线程，i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘sql线程读取本地relog中继日志进行回放
5.什么时候我们需要多个slave？
当读取的而操作远远高与写操作时。我们采用一主多从架构数据库外层接入负载均衡层并搭配高可用机制
2.2 架构缺陷
主从架构采用的是异步机制
master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测master端直接保存二进制日志到磁盘
当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slavemaster出现问题slave端接管master，这个过程中数据就丢失了
这样的问题出现就无法达到数据的强一致性，零数据丢失
2.3 MySQL主从复制的实现
2.3.1 配置master
[root@mysql-n0de1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=13 # 配置主id为13
log-bin=mysql-bin # 开启二进制日志[root@mysql-node1 ~]# /etc/init.d/mysqld restart # 重启mysql服务# 进入数据库配置用户权限
[root@mysql-nide1 ~]# mysql -uroot -p
Enter password:
. . . . . .mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123123'; # 生成专门用来做复制的用户，此用户是用于slave端做认证用
Query OK, 0 rows affected (0.01 sec.mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; # 对这个用户进行授权
Query OK, 0 rows affected (0.00 sec)mysql> SHOW MASTER STATUS; # 查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)# 查看二进制日志
[root@mysql-nide1 ~]# cd /data/mysql/
[root@mysql-nide1 mysql]# mysqlbinlog mysql-bin.000001 -vv
2.3.2 配置salve
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=23 # 配置从id为23
super_read_only=on # 设置slave1只允许读取数据[root@mysql-node2 ~]# /etc/init.d/mysqld restart# 进入mysql进行设置
[root@mysql-node2 ~]# mysql -uroot -p
mysql> change master to master_host='172.25.254.13',master_user='repl',master_password='123123',master_log_file='log_pos=595; # 设置从库连接主库
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave; # 开启slave
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G # 查看从库是否连接成功
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.13Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 595Relay_Log_File: mysql-node2-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes # IO线程连接成功，实现数据传输Slave_SQL_Running: Yes # SQL进程连接成功,实现数据回放. . . . . .
注意：slave主机不能够写入数据，只能读取
2.3.3 主从复制测试
# 在主master中写入数据
[root@mysql-node1 ~]# mysql -uroot -p
Enter password:
. . .
mysql> create database haha; # 建立haha数据库
Query OK, 1 row affected (0.01 sec)mysql> use haha # 切换到haha数据库
Database changed
mysql> create table userlist(username varchar(20) not null,password varchar(50) not null); # 建立userlist表
Query OK, 0 rows affected (0.02 sec)mysql> insert into userlist value ('haha','123'); # 写入表格数据
Query OK, 1 row affected (0.04 sec)mysql> select * from userlist; # 查看表格数据
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
+----------+----------+
1 row in set (0.00 sec)# 在slave中查看数据是否同步过来
mysql> select * from haha.userlist; # 数据从主master同步过来
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
+----------+----------+
1 row in set (0.00 sec)
2.4 当有数据时添加slave2（一主两从）
2.4.1 配置salve2
1.# # 将主master的mysql远程传送到slave2中
[root@mysql-n0de1 ~]# rsync -al /usr/local/mysql root@172.25.254.33:/usr/local 2.# 创建mysql的用户及用户组
[root@mysql-node3 ~]# useradd -s /sbin/nologin -M mysql
[root@mysql-node3 ~]# mkdir /data/mysql -p
[root@mysql-node3 ~]# chown mysql.mysql -R /data/mysql3.# 生成启动脚本
[root@mysql-node3 ~]# cd /usr/local/mysql/support-files/
[root@mysql-node3 support-files]# cp mysql.server /etc/init.d/mysqld4.# 修改环境变量
[root@mysql-node3 ~]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@mysql-node3 ~]# source ~/.bash_profile 5.# 修改配置文件
[root@mysql-node3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql # 指定数据目录
socket=/data/mysql/mysql.sock # 指定套接字
symbolic-links=0 # 数据只能存放到数据目录中，禁止链接到数据目录
server-id=336.# 数据库初始化建立mysql基本数据
[root@mysql-node3 ~]# mysql --user mysql --initialize # 记得保存生成的初始化密码，在接下来的安全初始化需要用到
[root@mysql-node3 ~]# /etc/init.d/mysqld start # 开启MySQL服务
[root@mysql-node3 ~]# chkconfig mysqld on # 设置自启动
[root@mysql-node3 ~]# chkconfig --list # 查看自启动的服务7.# 数据库安全初始化
[root@mysql-node3 ~]# mysql_secure_installation
2.4.2 从master中备份数据
# 从master节点备份数据
[root@mysql-nide1 ~]# mysqldump -uroot -p haha > haha.sql
注意：生产环境中备份时需要锁表，保证备份前后的数据一致
mysql> FLUSH TABLES WITH READ LOCK;备份后再解锁
mysql> UNLOCK TABLES;mysqldump命令备份的数据文件，在还原时先DROP TABLE，需要合并数据时需要删除此语句
-- -- Table structure for table `userlist` -- DROP TABLE IF EXISTS `userlist`; #需要合并数据时需要删除此语句 /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */;
# 将master的数据库备份发送到slave2中
[root@mysql-node1 ~]# scp /root/haha.sql root@172.25.254.33:/mnt
[root@mysql-node3 ~]# cd /mnt
[root@mysql-node3 mnt]# ls
haha.sql# 利用master节点中备份出来的lee.sql在slave2中拉平数据
[root@mysql-node3 mnt]# mysql -uroot -p -e "create database haha;" # 在slave2中创建haha数据库
[root@mysql-node3 mnt]# mysql -uroot -p haha < haha.sql # 将备份的数据库放进haha数据库拉平数据
[root@mysql-node3 mnt]# mysql -uroot -p
mysql> select * from userlist; # 查看数据是否拉平
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
+----------+----------+
1 row in set (0.01 sec)
2.4.3 配置slave2的slave功能
# 在master中查询日志pos
[root@mysql-node1 ~]# mysql -uroot -p -e "show master status;"
Enter password:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1244 | | | |
+------------------+----------+--------------+------------------+-------------------+# 在slave2中建立数据的从复制同步
mysql> use haha # 登录到haha数据库
mysql> change master to master_host='172.25.254.13',master_user='repl',master_password='123123',master_log_file='mysql-bin.000001',master_log_pos=1244; -- 设置从连接主库
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave; # 开启slave服务
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G # 查看从库是否连接成功
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.13Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1244Relay_Log_File: mysql-node3-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes # I/O连接成功Slave_SQL_Running: Yes # SQL连接成功. . . . . .
2.4.4 测试
1.# 在master中写入数据
[root@mysql-node1 ~]# mysql -uroot -p -e "INSERT INTO haha.userlist VALUES('user2','222');"2.# 在slave2中进行查看
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)3.# 在slave1中进行查看
mysql> select * from haha.userlist;
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)
2.5 延迟恢复
- 延迟复制时用来控制sql线程的，和i/o线程无关
- 这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的
- 是日志已经保存在slave端了，那个sql要等多久进行回放
- 当master端误操作，可以在slave端进行数据备份
# 设置slave2来当作延迟数据库
mysql> stop slave sql_thread; # 停止slave的SQL
Query OK, 0 rows affected (0.00 sec)mysql> change master to master_delay=60; # 修改主master的延迟
Query OK, 0 rows affected (0.00 sec)mysql> start slave sql_thread; # 重新开启slave的SQL
Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G # 查看延迟设定
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.25.254.13Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 1510Relay_Log_File: mysql-node3-relay-bin.000002Relay_Log_Pos: 586Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes. . .SQL_Delay: 60 # 延迟60s. . .
测试：
1.# 在master中模拟删除数据
mysql> use haha
mysql> delete from userlist where username='haha'; # 删除haha行的数据
Query OK, 1 row affected (0.00 sec)mysql> select * from userlist; # 查看master的表数据
+----------+----------+
| username | password |
+----------+----------+
| user2 | 222 |
+----------+----------+
1 row in set (0.00 sec)2.# 在slave2中60s前查看数据
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
| user2 | 222 |
+----------+----------+
2 rows in set (0.00 sec)
[root@mysql-node3 ~]# mysqldump -uroot -p haha > haha.sql # 有需求情况下对此时数据库进行备份3.# 在slave2中60s后查看数据
mysql> select * from userlist;
+----------+----------+
| username | password |
+----------+----------+
| user2 | 222 |
+----------+----------+
1 row in set (0.00 sec)4.# 数据恢复
[root@mysql-node3 ~]# scp /root/haha.sql root@172.25.254.13:/mnt[root@mysql-nide1 mnt]# mysql -uroot -p haha < haha.sql
[root@mysql-nide1 mnt]# mysql -uroot -p -e "select * from haha.userlist;" # 数据恢复
Enter password:
+----------+----------+
| username | password |
+----------+----------+
| haha | 123 |
| user2 | 222 |
+----------+----------+
2.6 慢查询日志
- 慢查询，顾名思义，执行很慢的查询
- 当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个SQL语句就是需要优化的
- 慢查询被记录在慢查询日志里
- 慢查询日志默认是不开启的
- 如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。
慢查询日志在master端进行设置
# 查询慢查日志是否开启
mysql> show variables like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF | # 慢查询日志默认关闭
| slow_query_log_file | /data/mysql/mysql-nide1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.01 sec)# 开启慢查询日志
[root@mysql-nide1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=13
log-bin=mysql-bin
slow_query_log=ON; # 在客户端设置开机自启
[root@mysql-nide1 ~]# /etc/init.d/mysqld restart[root@mysql-nide1 ~]# mysql -uroot -pmysql> set global slow_query_log=ON; # 在mysql中打开慢日志功能
Query OK, 0 rows affected (0.00 sec)mysql> set long_query_time=4; # 设置慢日志时间，默认10s
Query OK, 0 rows affected (0.00 sec)mysql> show variables like "long%"; # 查看设置的慢查询时间
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.01 sec)mysql> show variables like "slow%";
+---------------------+----------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON | # 已经开启慢日志功能
| slow_query_log_file | /data/mysql/mysql-nide1-slow.log |
+---------------------+----------------------------------+
3 rows in set (0.00 sec)
慢日志测试：
mysql> select sleep (10); # 10s后执行命令
+------------+
| sleep (10) |
+------------+
| 0 |
+------------+
1 row in set (10.00 sec)[root@mysql-node1 ~]# cat /data/mysql/mysql-node1-slow.log # 查看慢查询日志
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306 Unix socket: /data/mysql/mysql.sock
Time Id Command Argument
# Time: 2024-08-25T03:36:32.234224Z
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 10.001551 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use haha;
SET timestamp=1724556992;
select sleep (10);
2.7 MySQL的并行复制-多线程回放
1.查看slave中的线程信息
默认情况下slave中使用的是sql单线程回放
在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重
2.开启MySQL的多线程回放可以解决上述问题
# 在slave1中进行设置
[root@mysql-node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=23
super_read_only=on
gtid_mode=ON # 开启gtid
enforce-gtid-consistency=ON # 设置保持gtid一致，不一致的舍弃slave-parallel-type=LOGICAL_CLOCK # 设置基于组提交
slave-parallel-workers=16 # 开启的线程数量
master_info_repository=TABLE # master信息在表中记录，默认记在/data/mysql//master.info
relay_log_info_repository=TABLE # 回放日志信息在表中记录，默认记录在/data/mysql/relay-log.info
relay_log_recovery=ON # 日志回放恢复功能开启[root@mysql-node2 ~]# /etc/init.d/mysqld restart
3.重新查看设置后的线程信息
此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求
MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。
![[线程]线程不安全问题 --- 内存可见性 及wait和notify](https://i-blog.csdnimg.cn/direct/ae9ffdc0b22a441597e3ade15b95c97a.png)
