目录
安装
范例：二进制安装
流程
1、文件准备：
相关包：libaio numactl-libs ncurses-compat-libs
mysql包：https://dev.mysql.com/downloads/，找lts长期支持的。
2、服务器环境准备
创建组，创建用户，指定id
解压缩mysql指定目录，授权，创建软连接
创建data目录
修改环境变量，创建service
3、初始化
数据库生成
###################################################
##1、文件准备
[root@rocky31 ~]$yum -y install libaio numactl-libs ncurses-compat-libs
##-O指定名称，避免某些下载名称过长或有乱码
[root@rocky31 ~]$wget -O mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz 'https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz'
###################################################
##2、服务器环境准备
##创建系统组、用户，1000以内为系统组、用户，确认801未用
[root@rocky31 ~]$grep 801 /etc/passwd
[root@rocky31 ~]$grep 801 /etc/group
##-g指定id，-r指定创建系统组、系统用户
[root@rocky31 ~]$groupadd -g 801 -r mysql
##创建系统用户，-r系统用户，-u指定id，-g指定组，-s指定默认shell（nologin不登录），-d指定主目录，-c描述，最后加用户名称
[root@rocky31 ~]$useradd -r -u 801 -g mysql -s /sbin/nologin -c "mysql" mysql
##MySQL官方推荐的标准安装路径/usr/local/mysql，创建软连接，并授权
[root@rocky31 ~]$tar xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz -C /usr/local
[root@rocky31 ~]$ln -s /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64/ /usr/local/mysql
[root@rocky31 ~]$chown -R mysql.mysql /usr/local/mysql/
##增加环境变量
[root@rocky31 ~]$echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@rocky31 ~]$. /etc/profile.d/mysql.sh
##增加数据目录
[root@rocky31 ~]$cat > /etc/my.cnf <<EOF
> [mysqld]
> datadir=/data/mysql
> EOF
[root@rocky31 ~]$mkdir /data/mysql -p
[root@rocky31 ~]$chown -R mysql.mysql /data/mysql/
##增加service
[root@rocky31 ~]$cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@rocky31 ~]$chkconfig --add mysqld
[root@rocky31 ~]$systemctl cat mysqld.service | grep start
Description=LSB: start and stop MySQL
Restart=no
ExecStart=/etc/rc.d/init.d/mysqld start
####################################################
##初始化，--initialize-insecure不设置root密码，指定mysql用户，--datadir指定mysql目录，执行完会看到初始化的文件
[root@rocky31 ~]$mysqld --initialize-insecure --user=mysql --datadir=/data/mysql
##启动mysqld服务，可以使用journalctl -f -u实时刷新日志
[root@rocky31 ~]$systemctl start mysqld.service
[root@rocky31 ~]$journalctl -f -u mysqld
-- Logs begin at Mon 2024-07-01 19:47:09 CST. --
7月 02 10:37:31 rocky31 systemd[1]: Starting LSB: start and stop MySQL...
7月 02 10:37:31 rocky31 mysqld[3155]: Starting MySQL.
7月 02 10:37:31 rocky31 mysqld[3163]: Logging to '/data/mysql/rocky31.err'.
7月 02 10:37:34 rocky31 mysqld[3155]: ..
7月 02 10:37:34 rocky31 mysqld[3322]: [ 确定 ]
7月 02 10:37:34 rocky31 systemd[1]: Started LSB: start and stop MySQL.
##登录成功，输入exit或\q退出
[root@rocky31 ~]$mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
##修改密码后，需要-p输入密码才能登录，否则报错，无-u默认当前用户登录
[root@rocky31 ~]$mysqladmin -uroot password '123456'
[root@rocky31 ~]$mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@rocky31 ~]$mysql -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql基础
范例：mysql客户端登录，执行脚本
##-h, --host= 服务器主机 ,默认为localhost
##-p, --passowrd= 用户密码 ,建议使用-p,默认为空密码
##-P, --port= 服务器端口
##-D, --database= 指定默认数据库
##-e "SQL" 执行SQL命令
##-V, --version 显示版本
[root@rocky31 ~]$mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@rocky31 ~]$cat >show_db.sql<<EOF
> show databases;
> EOF
[root@rocky31 ~]$mysql <show_db.sql
Database
information_schema
mysql
performance_schema
sys
范例：管理用户、权限，增改锁
##为方便登录，先把密码置为空
mysql> alter user root@'localhost' identified by '';
Query OK, 0 rows affected (0.00 sec)
##查看用户，'USERNAME'@'HOST'
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
##查看当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
##创建wang用户，能从10.0.0.*网段登录，密码123456
mysql> create user 'wang'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
##授权，给root用户授权create、option，否则可能无法给wang用户全部权限
mysql> GRANT CREATE USER, GRANT OPTION ON *.* TO 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)
##刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##修改用户密码
mysql> alter user 'wang'@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
##重命名用户
mysql> rename user wang@'10.0.0.%' to wei;
Query OK, 0 rows affected (0.01 sec)
##授权账号，给select,insert,update指定hellodb.students库表
mysql> grant select,insert,update on hellodb.students to 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)
##给hellodb库所有权限
mysql> grant all on hellodb.* to 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##创建admin账号，给admin所有权限，with grant option且能给其它账户授权
mysql> create user 'admin'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to admin@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
##锁账号
mysql> ALTER USER 'wang'@'10.0.0.%' ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)
##解锁账号
mysql> alter user wei@'%' account unlock;
Query OK, 0 rows affected (0.00 sec)
##revoke取消权限，不如用锁定，避免权限内容不知道
mysql> revoke all on *.* from 'wang'@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
##show grants查看权限
mysql> SHOW GRANTS FOR 'wang'@'10.0.0.%'\G
*************************** 1. row ***************************
Grants for [email protected].%: GRANT USAGE ON *.* TO `wang`@`10.0.0.%`
1 row in set (0.00 sec)
范例：查看默认字符集和排序规则
[root@rocky31 ~]$mysql -e "show variables like 'collation%';"
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
[root@rocky31 ~]$mysql -e "show variables like 'character%' ;"
+--------------------------+---------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/local/mysql-8.4.0-linux-glibc2.28-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------------+
##修改字符集和排序方式为utf8
[root@rocky31 ~]$sed -i.bak '/mysqld/acharacter-set-server=utf8mb4\ncollation-server=utf8mb4_general_ci' /etc/my.cnf
常用SQL
范例：数据库DDL： CREATE ， DROP ，ALTER
##创建数据库
mysql> create database db1 character set 'utf8' collate 'utf8_bin';
Query OK, 1 row affected, 2 warnings (0.01 sec)
mysql> show create database db1\G
*************************** 1. row ***************************
Database: db1
Create Database: CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_bin */ /*!80016 DEFAULT ENCRYPTION='N' */
1 row in set (0.00 sec)
##再次创建报错
mysql> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
mysql> show warnings\G
*************************** 1. row ***************************
Level: Error
Code: 1007
Message: Can't create database 'db1'; database exists
1 row in set (0.00 sec)
##使用条件if则不会报错，也不会创建
mysql> create database if not exists db1;
Query OK, 1 row affected, 1 warning (0.01 sec)
##修改数据库
mysql> alter database db1 character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)
##删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.01 sec)
##查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
范例：数据表DDL： CREATE ， DROP ，ALTER
##需要先创建数据库，使用数据库后才能创建表；
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> use school;
Database changed
##表字段有各种类型，AUTO_INCREMENT PRIMARY KEY自增主键，char字符，int整数，decimal小数，enum枚举；
mysql> CREATE TABLE student (
-> id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL,
-> age tinyint UNSIGNED,
-> height DECIMAL(5,2),
-> gender ENUM('M','F') default 'M'
-> )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
##表字段timestamp时间类型，CURRENT_TIMESTAMP当前时间
mysql> create table testdata (id int auto_increment primary key,date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,name char(3));
Query OK, 0 rows affected (0.01 sec)
##使用查询结果创建新表
mysql> create table userdata select user,host from mysql.user;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
##创建相同表结构
mysql> create table student2 like student;
Query OK, 0 rows affected (0.01 sec)
##创建外键foreign key
mysql> create table school ( id int primary key auto_increment,name varchar(10));
Query OK, 0 rows affected (0.02 sec)
mysql> create table teacher(id int primary key auto_increment,name varchar(10), school_id int,foreign key(school_id) references school(id));
Query OK, 0 rows affected (0.01 sec)
##删除表
mysql> drop table if exists student2;
Query OK, 0 rows affected (0.01 sec)
##修改表名
mysql> ALTER TABLE student RENAME s1;
Query OK, 0 rows affected (0.01 sec)
##修改表结构，增加字段
mysql> ALTER TABLE s1 ADD gender2 ENUM('m','f');
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
范例：DML：INSERT, DELETE, UPDATE
mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| StuID | int unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint unsigned | NO | | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint unsigned | YES | | NULL | |
| TeacherID | int unsigned | YES | | NULL | |
+-----------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
##全插入
mysql> insert students values(null,'wang',18,'M',2,2);
Query OK, 1 row affected (0.01 sec)
##部分插入
mysql> insert students(name,age)values('zhang',20);
Query OK, 1 row affected (0.00 sec)
##更新数据
##注意一定要有限制条件，否则将修改所有行数据，可在配置中增加如下，即MySQL将禁止不带WHERE子句的UPDATE和DELETE语句
##[mysqld]
##sql_safe_updates = 1
mysql> update students set name='wei' where StuID='27';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
##删除数据
##尽量不删除，新增一个deleted标记删除
mysql> alter table students add is_del bool default false;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
##清空数据
mysql> truncate table students;
Query OK, 0 rows affected (0.04 sec)
范例：DQL单表查询语句
##指定列，查询不知道数据量必须加limit限制结果
mysql> select stuid 学员ID,name as 姓名 ,gender 性别 from students limit 3;
+----------+-------------+--------+
| 学员ID | 姓名 | 性别 |
+----------+-------------+--------+
| 1 | Shi Zhongyu | M |
| 2 | Shi Potian | M |
| 3 | Xie Yanke | M |
+----------+-------------+--------+
3 rows in set (0.00 sec)
##ifnull函数判断null时的显示
mysql> select stuid,name,ifnull(classID, '无班级 ') from students where classid is null;
+-------+-------------+---------------------------------+
| stuid | name | ifnull(classID, '无班级 ') |
+-------+-------------+---------------------------------+
| 24 | Xu Xian | 无班级 |
| 25 | Sun Dasheng | 无班级 |
+-------+-------------+---------------------------------+
2 rows in set (0.00 sec)
##distinct去重
mysql> select distinct gender from students ;
+--------+
| gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.00 sec)
##查询平均年龄
mysql> select sum(age)/count(*) from students where gender ='F';
+-------------------+
| sum(age)/count(*) |
+-------------------+
| 19.0000 |
+-------------------+
1 row in set (0.00 sec)
##group by分组查询
mysql> select count(*),gender from students group by gender ;
+----------+--------+
| count(*) | gender |
+----------+--------+
| 15 | M |
| 10 | F |
+----------+--------+
2 rows in set (0.00 sec)
##order by 【desc】 排序
mysql> select * from students order by age desc limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
范例：DQL多表子查询
##子查询作为单个条件
mysql> SELECT Name,Age FROM students WHERE Age<(SELECT avg(Age) FROM teachers) limit 3;
+-------------+-----+
| Name | Age |
+-------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
+-------------+-----+
3 rows in set (0.00 sec)
##子查询作为多个条件，使用in
mysql> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
+-------------+-----+
| Name | Age |
+-------------+-----+
| Sun Dasheng | 77 |
+-------------+-----+
1 row in set (0.00 sec)
##用于from子句的子查询
mysql> SELECT s.ClassID,s.aage FROM (SELECT ClassID,avg(Age) AS aage FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s WHERE s.aage>30;
+---------+---------+
| ClassID | aage |
+---------+---------+
| 2 | 36.0000 |
| 5 | 46.0000 |
+---------+---------+
2 rows in set (0.00 sec)
范例：DQL多表查询union/join
##联合查询union，合并默认去重，不需要去重需要union all
mysql> SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers limit 3;
+-------------+-----+
| Name | Age |
+-------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
+-------------+-----+
3 rows in set (0.00 sec)
##inner join，内连接，取交集
mysql> select * from students inner join teachers on
-> students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
##left outer join，左外连接，左表为主，右表无则显示null
mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students as s left outer join
-> teachers as t on s.teacherid=t.tid limit 8;
+-------+-------------+-----+-----------+------+---------------+------+
| stuid | name | age | teacherid | tid | name | age |
+-------+-------------+-----+-----------+------+---------------+------+
| 1 | Shi Zhongyu | 22 | 3 | 3 | Miejue Shitai | 77 |
| 2 | Shi Potian | 22 | 7 | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | 16 | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | 4 | 4 | Lin Chaoying | 93 |
| 5 | Yu Yutong | 26 | 1 | 1 | Song Jiang | 45 |
| 6 | Shi Qing | 46 | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | NULL | NULL | NULL | NULL |
+-------+-------------+-----+-----------+------+---------------+------+
8 rows in set (0.00 sec)
范例：sql脚本注意事项
##更新数据注意一定要有限制条件，否则将修改所有行数据，可在配置中增加如下，即MySQL将禁止不带WHERE子句的UPDATE和DELETE语句
[mysqld]
sql_safe_updates = 1
##尽量不删除，新增一个deleted标记删除
mysql> alter table students add is_del bool default false;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
##查询不知道数据量必须加limit限制结果
mysql> select stuid 学员ID,name as 姓名 ,gender 性别 from students limit 3;
##防止SQL注入，输入敏感信息不能有特殊符号、且要限制长度
如用户输入在用户名输入“admin'; --”或“admin'; # ”，即使密码随便输入，万一数据库里有admin账户，则能登录
##优先使用join替代子查询
执行子查询时，需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表 中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。
##索引
对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
mysql常用功能
范例：备份/还原前全局锁
##关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> create database db2;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
##全局锁，插入/更新数据会等待，查询不受影响
mysql> insert students(name,age) values('wei',30);
##解锁后，插入成功
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
范例：表write、read锁
##write锁，当前会话能查询、修改；其它会话一直等待；
mysql> lock table teachers write;
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)
##read锁，当前会话只能查询；其它会话也能查询；
mysql> lock table teachers read;
Query OK, 0 rows affected (0.00 sec)
mysql> update teachers set age=30 where tid=1;
ERROR 1099 (HY000): Table 'teachers' was locked with a READ lock and can't be updated
mysql> unlock tables ;
Query OK, 0 rows affected (0.00 sec)
范例：使用事务ACID特性完成一系列操作
##为了保证一系列操作为一个整体操作，使用begin开始，rollback回滚，commit提交；
##rollback不能撤销truncate、drop等DDL操作，只能撤销DML；
##事务一次性提交，可减少磁盘I/O，提高速度；
##只有在当前会话commit后，其它会话查询结果才看到最新值；不怕没修改完断网；
##Mysql默认隔离级别：可重复读
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update teachers set age=20 where tid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
范例：kill事务锁
##会话1，开启begin，不结束
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update teachers set age=50 where tid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
##会话2，一直等待
mysql> update teachers set age=70 where tid=1;
##会话3，查看锁
mysql> SELECT trx_mysql_thread_id,trx_state FROM information_schema.INNODB_TRX;
+---------------------+-----------+
| trx_mysql_thread_id | trx_state |
+---------------------+-----------+
| 30 | RUNNING |
+---------------------+-----------+
1 row in set (0.00 sec)
##查看线程详细信息，确认是否可以kill
mysql> show processlist;
+----+-----------------+-----------+---------+---------+-------+------------------------+---------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+---------+---------+-------+------------------------+---------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 11700 | Waiting on empty queue | NULL |
| 30 | root | localhost | hellodb | Sleep | 299 | | NULL |
| 39 | root | localhost | hellodb | Query | 11 | updating | update teachers set age=70 where tid=1 |
| 44 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+---------+---------+-------+------------------------+---------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
##kill掉后会话2执行成功，会话1再操作时会报断开
mysql> kill 30;
##查看事务超时时长
mysql> show global variables like 'innodb_lock_wait_timeout' ;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
1 row in set (0.01 sec)
日志管理
事务日志transaction log
范例：事务日志及性能说明（redo undo）![image-20240703101657336]()
事务日志作用：
redo log：可以用来恢复未写入data file的已成功事务更新的数据
undo log：可以用来在事务失败时进行rollback
事务日志性能：
0 提交--日志缓冲区--每秒写入到磁盘
1 默认值：提交--日志缓冲区，同时写入到磁盘
2 提交--os缓冲区--每秒写入到磁盘
使用1场景：安全，但效率较低，磁盘I/O较多；
使用2场景：高并发业务，os崩溃比mysql崩溃概率低，效率也高；
修改和查看事务日志配置：
如需修改，在/etc/my.cnf中的[mysqld]增加
innodb_flush_log_at_trx_commit=1
##查看默认值
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
范例：查看错误日志（.err）
查看数据目录
[root@rocky31 ~]$grep datadir /etc/my.cnf
datadir=/data/mysql
查看日志路径
mysql> SHOW GLOBAL VARIABLES LIKE 'log_error';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| log_error | ./rocky31.err |
+---------------+---------------+
1 row in set (0.01 sec)
tail -f实时刷新
[root@rocky31 ~]$tail -f /data/mysql/rocky31.err
或者用linux自带journalctl日志-f实时刷新，-u指定程序
[root@rocky31 ~]$journalctl -fu mysqld
范例：开启通用日志（记录操作语句）
记录对数据库的通用操作，包括:错误的SQL语句，默认不启用
开启后重启服务
[root@rocky31 ~]$grep general_log /etc/my.cnf
general_log=ON
确认是否打开
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
查看日志路径
mysql> select @@general_log_file;
+-------------------------+
| @@general_log_file |
+-------------------------+
| /data/mysql/rocky31.log |
+-------------------------+
1 row in set (0.00 sec)
验证
[root@rocky31 ~]$tail -f /data/mysql/rocky31.log
...
2024-07-02T10:26:54.190608Z 9 Query select user()
2024-07-02T10:27:14.498637Z 9 Query show databases
...
范例：二进制日志配置（binlog备份）
功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
二进制日志记录三种格式：
statement 记录语句，日志量较少
row 记录数据，日志量较大，可用于备份和恢复
mixed 让系统自行判定该基于哪种方式进行
二进制文件构成：
binlog.000001 日志文件，如binlog.000004一直累加
binlog.index 索引文件，记录当前已有的二进制日志文件列表
常用配置说明：
sql_log_bin=ON ##开启二进制日志
log_bin=/PATH/BIN_LOG_FILE ##指定文件位置
binlog_format=ROW ##按行记录数据
sync_binlog=1 ##启动二进制日志即时同步磁盘功能
都可以使用show variables like查看当前值
mysql> show variables like 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
##生成新的二进制文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
备份和恢复
范例：冷备份和恢复
##说明：31为主服务器；41为准备一套一样的系统和mysql版本
##冷备份和恢复需要停服务
##31服务器
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec)
##41服务器
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
##31使用rsync -a完全同步，程序、数据、配置
[root@rocky31 ~]$systemctl stop mysqld.service
[root@rocky31 ~]$rsync -a /usr/local/mysql 10.0.0.41:/usr/local/
[root@rocky31 ~]$rsync -a /data/mysql 10.0.0.41:/data/
[root@rocky31 ~]$rsync -a /etc/my.cnf 10.0.0.41:/etc/
##41开启mysqld服务，登录验证
[root@rocky-41 ~]$systemctl start mysqld.service
[root@rocky-41 ~]$mysql
...
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| admin | % |
| wei | % |
| wang | 10.0.0.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
7 rows in set (0.01 sec)
范例：mysqldump实现完全备份和恢复
##31备份，41恢复
##开启二进制，最好和数据库分区
[root@rocky31 ~]$cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
binlog_format=row
log_bin=/mysqlbin/
[root@rocky31 ~]$ll /mysqlbin/
总用量 16
-rw-r----- 1 mysql mysql 181 7月 2 19:31 mysql.000001
-rw-r----- 1 mysql mysql 712 7月 2 20:55 mysql.000002
-rw-r----- 1 mysql mysql 158 7月 2 20:55 mysql.000003
-rw-r----- 1 mysql mysql 69 7月 2 20:55 mysql.index
#############################################################
##mysqldump常用备份选项
##-u登录用户；-p密码；
##-A所有数据库；-B: 后跟要备份的数据库列表
##-F刷新服务器日志
##--master-data=2表示以注释形式显示change master to语句；可能名称需要修改为--source-data；备份还原为2，需要设置主从复制为1；
##--single-transaction开启事务，保证备份时的数据一致性
##--default-character-set=utf8设置默认字符集
##--flush-privileges在备份之前刷新权限
##--events事件调度器；--routines存储过程和函数；--triggers触发器；
#############################################################
##全库备份，刷新日志
[root@rocky31 ~]$mysqldump -uroot -p -A -F --master-data=1 --single-transaction --flush-privileges --default-character-set=utf8 > fullback.sql
##备份后二进制日志文件新建了
[root@rocky31 ~]$ll /mysqlbin/
总用量 20
-rw-r----- 1 mysql mysql 181 7月 2 19:31 mysql.000001
-rw-r----- 1 mysql mysql 712 7月 2 20:55 mysql.000002
-rw-r----- 1 mysql mysql 201 7月 2 21:02 mysql.000003
-rw-r----- 1 mysql mysql 158 7月 2 21:02 mysql.000004
-rw-r----- 1 mysql mysql 92 7月 2 21:02 mysql.index
##拷贝到41
[root@rocky31 ~]$scp fullback.sql 10.0.0.41:
##41恢复时，最好先关闭二进制日志，恢复完再开启日志
##备份脚本都是创建库和插入数据，所以可以直接执行，有判断，很方便
[root@rocky-41 ~]$mysql -uroot -p'123' -e'set sql_log_bin=off;'
[root@rocky-41 ~]$mysql -p <fullback.sql
[root@rocky-41 ~]$mysql -uroot -p'123' -e'set sql_log_bin=on;'
范例：利用binlog二进制日志增量备份和还原
##按照之前完全备份的基础，31服务器mysql有变动，41增量还原
##31刷新日志，确定上次全量备份后的新日志文件为mysql.000005
[root@rocky31 ~]$mysql -e'flush logs;'
[root@rocky31 ~]$ll /mysqlbin/
总用量 24
-rw-r----- 1 mysql mysql 181 7月 2 19:31 mysql.000001
-rw-r----- 1 mysql mysql 712 7月 2 20:55 mysql.000002
-rw-r----- 1 mysql mysql 201 7月 2 21:02 mysql.000003
-rw-r----- 1 mysql mysql 383 7月 2 21:34 mysql.000004
-rw-r----- 1 mysql mysql 158 7月 2 21:34 mysql.000005
-rw-r----- 1 mysql mysql 115 7月 2 21:34 mysql.index
##使用mysqlbinlog将日志文件生成sql脚本传送到41
[root@rocky31 ~]$mysqlbinlog /mysqlbin/mysql.000005 >new.sql
[root@rocky31 ~]$scp new.sql 10.0.0.41:
##41先关闭二进制记录，然后执行，完成增量还原
[root@rocky-41 ~]$mysql -e'set sql_log_bin=off;'
[root@rocky-41 ~]$mysql <new.sql
[root@rocky-41 ~]$mysql -e'set sql_log_bin=on;'
主从复制
范例：mariadb实现主从复制
##部分mysql版本使用master关键字会报错，因此使用mariadb
##实现：32为主有数据，31为空数据库，实现31从节点只读，数据和32相同且同步
#########################################################
##32服务器
##设置服务id，开启二进制日志
[root@anolis-32 ~]$cat >>/etc/my.cnf<<EOF
> [mysqld]
> log_bin=/data/mysqlbin/mysql-bin
> server-id=32
> EOF
##创建目录并授权，启动数据库
[root@anolis-32 ~]$mkdir /data/mysqlbin/ -p && chown mysql.mysql /data/mysqlbin
[root@anolis-32 ~]$systemctl start mariadb.service
##导入部分数据做验证
[root@anolis-32 ~]$mysql <hellodb_innodb.sql
[root@anolis-32 ~]$mysql -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
##创建repluser用户并授权，用于从节点连接同步数据
[root@anolis-32 ~]$mysql -e 'create user repluser@"10.0.0.%" identified by "123";'
[root@anolis-32 ~]$mysql -e 'grant replication slave on *.* to repluser@"10.0.0.%";'
##全备份，--master-data=1生成change master to语句，发送给31
[root@anolis-32 ~]$mysqldump -A -F --master-data=1 --single-transaction > all.sql
[root@anolis-32 ~]$scp all.sql 10.0.0.31:
#######################################################
##31服务器
##设置服务id，且为只读模式
[root@anolis31 ~]$cat >>/etc/my.cnf<<EOF
> [mysqld]
> server-id=31
> read-only
> EOF
##将32发送的sql脚本添加主机号，用户和密码，如果端口有修改也可以增加
[root@anolis31 ~]$sed -i.bak "s/^CHANGE MASTER TO/\
> CHANGE MASTER TO master_host='10.0.0.32',\
> master_user='repluser',\
> master_password='123',/" all.sql
##执行脚本后，完成数据拷贝，有主节点，能启动从节点
[root@anolis31 ~]$mysql < all.sql
##启动从节点，查看从节点状态，且数据库也同步，后续数据也会同步
[root@anolis31 ~]$mysql -e 'start slave;'
[root@anolis31 ~]$mysql -e 'show slave status\G;' | grep "Master_Host\|Slave_IO_Running\|Slave_SQL_Running"
Master_Host: 10.0.0.32
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
[root@anolis31 ~]$mysql -e "show databases;"
+--------------------+
| Database |
+--------------------+
| db2 |
| hellodb |
| information_schema |
| mysql |
| performance_schema |
+--------------------+