初冬十月的笔记

MySQL学习笔记

2021/10/17 MySQL

# 安装MySQL

# Windows安装MySQL

# 解压文件

将 mysql-5.7.28-winx64.zip 解压到指定目录，如：E:\安装\MySQL\mysql-8.0.21-winx64

# 配置环境变量

新增系统变量

变量名=MYSQL_HOME

变量值=E:\安装\MySQL\mysql-8.0.21-winx64

将 %MYSQL_HOME%\bin 添加到Path环境变量中

# 新建配置文件

在 E:\安装\MySQL\mysql-8.0.21-winx64 下新建my.ini文件，内容如下：

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
basedir="E:\安装\MySQL\mysql-5.7.28-winx64"
datadir="E:\安装\MySQL\mysql-5.7.28-winx64\data\"
port=3306
max_connections=1000
max_user_connections=500
wait_timeout=200
character-set-server=utf8
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 
#character-set-server=UTF-8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
#忘记密码时使用
#skip-grant-tables
#设置协议认证方式(重点啊)
default_authentication_plugin=mysql_native_password
[mysql]
default-character-set=utf8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# 安装启动服务

以管理员身份进入命令行工具，cd到bin目录，依次执行以下命令

# 此命令执行完毕，安装目录会多出一个data文件夹
mysqld --initialize --user=mysql --console

# 安装服务
mysqld -install

# 启动MySQL
net start mysql

# 进入MySQL
mysql -u root -p

# 修改root密码
ALTER user 'root'@'localhost' IDENTIFIED BY 'root';

# 停止服务
net stop mysql

# 卸载服务，然后删除MySQL目录
mysqld --remove mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# Linux安装MySQL

# Docker安装MySQL

# 拉取镜像
docker pull mysql

# 创建目录
mkdir -p /my/mysql/conf /my/mysql/data

# 运行容器
docker run --name my-mysql \
-e MYSQL_ROOT_PASSWORD=123 \
-v /my/mysql/conf:/etc/mysql/conf.d \
-v /my/mysql/data:/var/lib/mysql \
-p 3306:3306 \
-p 33060:33060 \
--restart=always \
-d mysql

# 进入容器
docker exec -it my-mysql bash
mysql -h 127.0.0.1 -u root -p
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 基本命令

# 连接服务器

# mysql -u 用户名 -h 服务地址 -p
mysql -u root -h 127.0.0.1 -p
1
2

回车输入密码即可。

# 断开服务器

mysql> quit;
1

# 查看数据库

show databases;
1

# 创建数据库

create database 数据库名;
1

# 打开数据库

use 数据库名;
1

# 导出数据库

在Windows下，进入命令行工具，切换MySQL安装目录的bin文件夹，输入

mysqldump -u 用户名 -p 数据库名 > 导出文件全路径

mysqldump -u root -p uuap > E:\L1_S03_我的系统\uuap_dump.sql
1
2
3

# 导入数据库

【待测试】进入MySQL模式，新建数据库，打开数据库，然后

mysql > source 导入的文件名;
mysql > source E:\L1_S03_我的系统\uuap_dump.sql;
1
2

# 查看数据表

show tables;
1

# 查看表结构

desc 表名称;
1

# 查看版本号

docker ps
docker exec -it mysql-master-3307 bash
# 登录成功以后,会显示该MySQL的详细信息,其中包含版本号
mysql -uroot -p123
1
2
3
4

# 备份与恢复

mysqldump命令的工作原理：它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。这些CREATE语句和INSERT语句都是还原时使用的。

# 使用mysqldump 备份一个数据库

mysqldump -u username -p dbname > backup.sql
1

# 使用mysqldump 备份一个数据库的若干表

mysqldump -u username -p dbname table1 table2 ... tableN > backup.sql
1

# 使用mysqldump 备份多个数据库

mysqldump -u username -p -databases dbname1,dbname2,dbname3... > backup.sql
1

# 使用mysql恢复备份文件

mysql -u username -p [dbname] < backup.sql
1

# 主从同步

基本原理：

slave会从master读取binlog来进行数据同步

具体步骤：

  • step1：master将数据改变记录到二进制日志（binary log）中。
  • step2： 当slave上执行 start slave 命令之后，slave会创建一个 IO 线程用来连接master，请求master中的binlog。
  • step3：当slave连接master时，master会创建一个 log dump 线程，用于发送 binlog 的内容。在读取 binlog 的内容的操作中，会对主节点上的 binlog 加锁，当读取完成并发送给从服务器后解锁。
  • step4：IO 线程接收主节点 binlog dump 进程发来的更新之后，保存到 中继日志（relay log） 中。
  • step5：slave的SQL线程，读取relay log日志，并解析成具体操作，从而实现主从操作一致，最终数据一致。
mkdir -p \
/my/mysql-cluster/mysql-master-3307/conf \
/my/mysql-cluster/mysql-master-3307/data \
/my/mysql-cluster/mysql-slave1-3308/conf \
/my/mysql-cluster/mysql-slave1-3308/data \
/my/mysql-cluster/mysql-slave2-3309/conf \
/my/mysql-cluster/mysql-slave2-3309/data \
1
2
3
4
5
6
7

# 准备主服务器

启动容器

docker run --name mysql-master-3307 \
-e MYSQL_ROOT_PASSWORD=123 \
-v /my/mysql-cluster/mysql-master-3307/conf:/etc/mysql/conf.d \
-v /my/mysql-cluster/mysql-master-3307/data:/var/lib/mysql \
-p 3307:3306 \
-p 33070:33060 \
-d mysql
1
2
3
4
5
6
7

修改配置文件

vim /my/mysql-cluster/mysql-master-3307/conf/my.cnf
1

添加以下内容

[mysqld]
# 服务器唯一id，默认值1
server-id=1
1
2
3

重启容器

docker restart mysql-master-3307
1

# 准备从服务器1

启动容器

docker run --name mysql-slave1-3308 \
-e MYSQL_ROOT_PASSWORD=123 \
-v /my/mysql-cluster/mysql-slave1-3308/conf:/etc/mysql/conf.d \
-v /my/mysql-cluster/mysql-slave1-3308/data:/var/lib/mysql \
-p 3308:3306 \
-p 33080:33060 \
-d mysql
1
2
3
4
5
6
7

修改配置文件

vim /my/mysql-cluster/mysql-slave1-3308/conf/my.cnf
1

添加以下内容

[mysqld]
# 服务器唯一id，默认值1
server-id=2
1
2
3

重启容器

docker restart mysql-slave1-3308
1

# 准备从服务器2

启动容器

docker run --name mysql-slave2-3309 \
-e MYSQL_ROOT_PASSWORD=123 \
-v /my/mysql-cluster/mysql-slave2-3309/conf:/etc/mysql/conf.d \
-v /my/mysql-cluster/mysql-slave2-3309/data:/var/lib/mysql \
-p 3309:3306 \
-p 33090:33060 \
-d mysql
1
2
3
4
5
6
7

修改配置文件

vim /my/mysql-cluster/mysql-slave2-3309/conf/my.cnf
1

添加以下内容

[mysqld]
# 服务器唯一id，默认值1
server-id=3
1
2
3

重启容器

docker restart mysql-slave2-3309
1

# 实现主从同步

在主服务器中创建slave用户

-- 创建slave用户
CREATE USER 'test_slave'@'%';
-- 设置密码
ALTER USER 'test_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'test_slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8

在主服务器中执行以下脚本，查询主服务器状态，并记下File和Position的值，然后不要操作主服务器

SHOW MASTER STATUS;
1

在从服务器中执行以下脚本

-- 配置主从关系
CHANGE REPLICATION SOURCE TO 
SOURCE_HOST='192.168.1.12',
SOURCE_PORT=3307,
SOURCE_LOG_FILE='binlog.000003',
SOURCE_LOG_POS=1057;

-- 启动从机的复制功能
START REPLICA USER='test_slave' PASSWORD='123';
-- 停止从机的复制功能
STOP REPLICA;

-- 查看从机的状态
SHOW SLAVE STATUS
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 准备测试脚本

在主服务器中创建库、表

CREATE DATABASE `db_user`;

USE `db_user`;

DROP TABLE `sys_user`;

CREATE TABLE `sys_user` (
  `id` bigint DEFAULT NULL,
  `nickname` varchar(50) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8MB4;

INSERT INTO `db_user`.`sys_user` (`id`,`nickname`,`username`,`password`) VALUES ('1562417697810157568', 'admin', 'admin', '12345');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# my.cnf

[mysqld]
# 服务器唯一id，默认值1
server-id=1
# 设置日志格式，默认值ROW
binlog_format=STATEMENT
# 二进制日志名，默认binlog
#log-bin=binlog
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=db_xxx
# 设置不需要复制的数据库
#binlog-ignore-db=db_yyy
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin
1
2
3
4
5
6
7
8
9
10
11
12
13

binlog_format可选值：

  • binlog_format=STATEMENT：日志记录的是主机数据库的写指令，性能高，但是now()之类的函数以及获取系统参数的操作会出现主从数据不同步的问题。
  • binlog_format=ROW（默认）：日志记录的是主机数据库的写后的数据，批量操作时性能较差，解决now()或者 user()或者 @@hostname 等操作在主从机器上不一致的问题。
  • binlog_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

binlog-do-db和binlog-ignore-db的优先级如下：

PostgreSQL学习笔记