目录
安装
范例:二进制安装
流程
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 |
+--------------------+
标签:rows,服务,##,rocky31,sec,mysql,root
From: https://www.cnblogs.com/szlhwei/p/18282178