服务-mysql

目录

安装

范例：二进制安装
流程
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 wang@10.0.0.%: 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;'

主从复制

image-20240703135638731
范例：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 |
+--------------------+
posted @   szlhwei  阅读(20)  评论(0)  编辑  收藏  举报
（评论功能已被禁用）
相关博文：
· Linux基础
· 服务-nginx
· mysql实战案例
· 数据库的简述
· MySQL总结
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
点击右上角即可分享
微信分享提示
AI IDE Trae