MYSQL实战

1.下载安装LIUNX版本：

MySQL :: Download MySQL Community Server (Archived Versions)

这里选的是8.0.26

tar -vxf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar

rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
#如果上面这句报错 执行以下命令 yum remove mysql-libs

rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm

yum install openssl-devel

rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm

#需要两个前置包安装
yum install libaio 
yum -y install net-tools 

rpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm
#上面一步没有问题就已经安装好了

#启动
systemctl start mysqld

#查看密码
grep 'temporary password' /var/log/mysqld.log

#使用客户端登录
mysql -u root -p

#开发环境下 方便密码记忆可以使用以下命令设置复杂度
set global validate_password.policy = 0;
set global validate_password.length = 4;

#重置密码
alter user  user() identified by '1234';

#设置密码并设置可以远程登录
create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

#设置权限
grant all on *.* to 'root'@'%';

以上一台搭建好MYSQL环境的liunx服务器已经准备好了。

2.搭建主从复制

概述

MySQL 复制的优点主要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务。
• 实现读写分离，降低主库的访问压力。
• 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

实战

1.准备环境

分别准备两台虚拟机搭建以上环境

主库：192.168.56.21

主库：192.168.56.22

2配置主库

修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1

server-id=1

#是否只读,1 代表只读, 0 代表读写

read-only=0

#忽略的数据, 指不需要同步的数据库
#binlog-ignore-db=mysql
#指定同步的数据库
#binlog-do-db=db01

重启MySQL服务器

systemctl restart mysqld

登录mysql，创建远程连接的账号，并授予主从复制权限

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';

#为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

通过指令，查看二进制日志坐标

 show master status;

mysql>  show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000019 |      663 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3配置从库

修改配置文件 /etc/my.cnf

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，和主库不一样即可

server-id=2

#是否只读,1 代表只读, 0 代表读写

read-only=1

重新启动MySQL服务

systemctl restart mysqld

登录mysql，设置主库配置

注意

这里的binlog.000004，663一定要与master二进制日志坐标保持一致。

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.56.21', 
SOURCE_USER='itcast', SOURCE_PASSWORD='Root@123456', 
SOURCE_LOG_FILE='binlog.000009', SOURCE_LOG_POS=595;

上述是8.0.23中的语法。如果mysql是 8.0.23 之前的版本，执行如下SQL：

CHANGE MASTER TO MASTER_HOST='192.168.91.166', 
MASTER_USER='itcast', MASTER_PASSWORD='Root@123456',
 MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=663;

参数名	含义	8.0.23之前
SOURCE_HOST	主库IP地址	MASTER_HOST
SOURCE_USER	连接主库的用户名	MASTER_USER
SOURCE_PASSWORD	连接主库的密码	MASTER_PASSWORD
SOURCE_LOG_FILE	binlog日志文件名	MASTER_LOG_FILE
SOURCE_LOG_POS	binlog日志文件位置	MASTER_LOG_POS

开启同步操作

start replica ; #8.0.22之后
start slave ; #8.0.22之前

查看主从同步状态

show replica status\G ; #8.0.22之后
show slave status ; #8.0.22之前

4.测试

在主库 192.168.56,21 上创建数据库、表，并插入数据

create database db01;
use db01;
create table tb_user(
	id int(11) primary key not null auto_increment,
	name varchar(50) not null,
	sex varchar(1)
)engine=innodb default charset=utf8mb4;
insert into tb_user(id,name,sex) values(null,'Tom', '1'),(null,'Trigger','0'),(null,'Dawn','1');

在从库 192.168.56,22 中查询数据，验证主从是否同步。

验证成功！

锁

整体分为3种锁

全局锁，表锁、行锁

全局锁

对整个数据库加锁，粒度非常大，很少用，常用于数据备份

sql语句：

flush tables with read lock; # 加全局锁
UNLOCK tables; # 解锁

上图是备份的整体流程，在备份期间，所有操作数据库写入的操作会阻塞等待。直到解锁

数据加全局锁，是一个很重的操作，存在以下问题：

• 在主库上备份，所有更新操作会阻塞，业务会停止。
• 在从库上备份，备份期间从库不会执行主库同步过来的数据（通过二进制日志 binlog），导致主从延迟。

表级锁

表锁

lock tables 表名 ..  read/write  ；读锁或写锁

unlock tables；

 

元数据锁

意向锁

解决表锁和行锁的冲突问题

添加表锁时会逐一检查每一行的行锁是否冲突，意向锁其实就是在加行锁时，加一个意向锁，此时添加表锁时直接和意向锁判断是否冲突。

 

 

 

错误日志 默认开启

binlog 二进制日志数据恢复 默认开启

开启查询日志默认不开启

 慢查询日志 默认不开启