MySQL8逐渐替代MySQL5.7,但是MySQL8对于新手小白来说是不太友好的,安装的时候容易踩坑,笔者使用二进制安装。安装完成后下面是一些基本的使用。
教程基于Centos7.9
# 其他版本也可以
cat /etc/redhat-release
MySQL的安装启动实践
关闭防火墙、关闭selinux
#查看防火墙命令:
systemctl status firewalld
#关闭防火墙命令:
systemctl stop firewalld
getenforce
确保没有mysql其他版本
一、检查
Yum检查
yum list installed | grep mysql
安装则直接删除
yum remove mysql mysql-server mysql-libs compat-mysql
yum remove mysql-community-release
rpm检查
rpm -qa | grep -i mysql
有则直接删除
rpm -e --nodeps mysql-community-libs-5.7.22-1.el7.x86_64
rpm -e –nodeps mysql57-community-release-el7-11.noarch
二、口令查找Mysql的安装目录和残存文件
whereis mysql
find / -name mysql
找到后,全部rm删除。
MySQL的安装
-
MySQL的下载官网:
https://dev.mysql.com/downloads
-
MySQL有各种版本,community社区版是开源免费的
安装地址: /usr/local/mysql
这个是习惯性位置,你可以自己更改
例如nginx位置/usr/local/nginx;redis位置/usr/local/redis
或者是/opt/mysql,/opt/nginx
MySQL安装说明
- rpm或者yum安装,生产环境比较少用
- 编译或者二进制安装,生产环境使用
我们选择二进制安装,解压到指定目录即可完成安装。
先下载二进制文件:
wget -c https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.17-linux-glibc2.12-i686.tar.xz
可以使用上传工具:
yum install lrzsz -y
# 环境依赖
yum install -y gcc gcc-c++ openssl openssl-devel libaio libaio-devel ncurses ncurses-devel
# 解压
tar -Jxvf mysql-8.0.17-linux-glibc2.12-i686.tar.xz
mv mysql-8.0.17-linux-glibc2.12-i686 /usr/local/mysql
环境变量设置
vim /etc/profile
加入export PATH=$PATH:/usr/local/mysql/bin
然后source /etc/profile
或者直接 echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile && source /etc/profile
测试安装
mysql -V
出现这个情况可能是由于64位的系统中安装了32位的MySQL程序,导致缺少一些依赖库。
安装glibc.i686和zlib.i686等组件,以支持32位的程序运行。
中的步骤,或者使用以下命令:
yum install glibc.i686
yum install zlib.i686 --setopt=protected_multilib=false
yum install xulrunner.i686
出现这个情况,两种选择:
- 换一个更低的mysql版本
- 升级你的系统
最后
MySQL8的配置及数据初始化
一般nginx,Redis这些,安装完,配置一下启动就好。但是Mysql还有数据初始化才可以启动。也就是MySQL需要:
- 配置
- 数据初始化
配置
vim /etc/my.cnf
[mysqld]
# 指定监听地址
bind-address=127.0.0.1
# 监听端口
port=3306
# 指定数据目录
datadir=/data/mysql
# 启动时的用户
user=mysql
skip-name-resolve
# 开启慢查询
slow_query_log=1
long_query_time=0.2
slow_query_log_file=/data/mysql/mysql-slow.log
innodb-file-per-table=1
innodb_flush_log_at_trx_commit = 2
max_allowed_packet = 512M
connect_timeout = 60
net_read_timeout = 120
[mysqld_safe]
# 指定错误日志位置
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
数据初始化
-
新增用户
useradd mysql -s /sbin/nologin
-
数据目录
mkdir -pv /data/mysql
chown -R mysql:mysql /data/mysql /usr/local/mysql/
-
初始化
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
我的报错了,如下(这是两个报错)
解决办法,缺少装啥
yum install -y libaio.so.1
yum install libnuma.so.1
成功了
-
需要记住临时密码:A temporary password is generated for root@localhost: jR=cxEs3LLRl
-
验证是否初始化成功:/data/mysql
![image-20230708210947979](https://img-blog.csdnimg.cn/img_convert/2833bc45d940a5e83bac4f45da8504ef.png)
MySQL8服务器启动
-
脚本启动:/etc/init.d/mysqld start
-
脚本关闭:/etc/init.d/mysqld stop
-
手工启动:nohup mysqld_safe --defaultsfile=/etc/my.cnf &
-
手工关闭:kill
出现问题,无法启动
解决:
cp /usr/local/mysql/.support-files/mysql.server /etc/init.d/mysqld
但是可以使用Centos系统的特色——Systemctl管理
vim /usr/lib/systemd/system/mysqld.service
添加以下内容
[Unit]
Description=mysqld
After=network.target
[Service]
Type=forking
ExecStart=/etc/init.d/mysqld start
[Install]
WantedBy=multi-user.target
Systemctl管理Mysql
-
开机启动:
systemctl enable mysqld
-
启动:
systemctl start mysqld
-
关闭:
systemctl stop mysqld
-
重启:
systemctl restart mysqld
是否运行:
- 端口
- 进程
- 日志
less /data/mysql/mysqld.log
MySQL客户端和数据库操作
流程:
- 建库
- 建表
- 数据操作:增删改查
MySQL客户端:
- Linux客户端,mysql命令
- windows客户端,界面操作
- 网站客户端phpadmin,需要LNMP
- 程序操作Mysql数据库,需要编程
Linux客户端使用
- 登录:
mysql -uroot -p密码 -A
-u指定用户,-p指定输入密码;-A取消命令提示;-h指定登录到那台主机;-S指定使用哪个Socket
注意:密码上面有
示例:mysql -uroot -pjR=cxEs3LLRl -A
- 退出exit
- 修改密码:
alter user 'root'@'localhost' identified by 'zhugenfu';
密码是zhugenfu
mysql操作说明
- 库 --> 表 --> 数据
- 多个库、多个表、多行数据
- 插入、读取、更新、删除数据
数据库操作
遵循sql语言
- 显示数据库
show databases;
- 创建数据库
CREATE DATABASE 数据库名;
- 删除数据库
drop database <数据库名>;
- 选择数据库
use <数据库名>;
MySQL数据类型
常用数值类型介绍
-
TINYINT整型1字节,SMALLINT整型2字节, MEDIUMINT整型3字节,INT整型4字节,BIGINT整型8字 节
-
FLOAT浮点数,整型无小数点
-
DOUBLE双浮点数
字符串类型
- CHAR定长字符串,VARCHAR变长字符串
- TEXT长文本数据,MEDIUMTEXT中长文本数据, LONGTEXT极大长文本数据
日期和时间类型
- 日期类型DATE,YYYY-MM-DD
- 时间类型TIME,HH:MM:SS
- 年类型YEAR,YYYY
- 日期时间类型DATETIME,YYYY-MM-DD HH:MM:SS
数据类型很重要,篇幅也比较多,很枯燥。
可以选择看菜鸟教程菜鸟教程-MySQL数据类型
MySQL数据表增删改查
数据表创建
先指定数据库:use <数据库名>;
创建MySQL数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段
以下为创建MySQL数据表的SQL通用语法:
CREATE TABLE table_name (column_name column_type);
例
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` ),
unique key (`runoob_title`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL, 在操作数据库时如果输入该字段的数据为NULL ,就会报错。
- AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1,用来标识用的。
- PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键,列间以逗号分隔。一个数据表只有一个主键
- unique key 是唯一键,数值不可以重复,一个数据表可以有多个唯一键
- ENGINE 设置存储引擎,CHARSET 设置编码。
数据表查看
所有表show tables;
查看表结构:desc <表名>;
或者show create <表名>;
数据表查询
读取所有内容select * from runoob_tbl;
读取指定内容select runoob_title from runoob_tbl;
以下为在MySQL数据库中查询数据通用的 SELECT 语法:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
- 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以使用 LIMIT 属性来设定返回的记录数。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
字段更新和删除
更新语法
update <表名> set 更改字段=更改值 where 条件;
一般是先select,再update
条件一定要加,不然就会全表更新,对于企业很致命
字段删除语法
delete from <表名> where 条件;
truncate table <表名>;
一般是先select,再delete
条件一定要加,不然就会全表删除了
整个表删除(保留自增id):delete from <表名>;
整个表删除(重置自增id):truncate table <表名>;
删除
删除MySQL数据表的通用语法:
DROP TABLE table_name ;
数据表插入数据
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
例如
INSERT INTO runoob_tbl
(runoob_title, runoob_author, submission_date)
VALUES
("学习 PHP", "菜鸟教程", NOW());
列的修改
根据上面的插入数据进行修改
使用 CHANGE
只修改列名
ALTER TABLE runoob_tbl
CHANGE runoob_title title VARCHAR(255);
AI 生成的代码。仔细查看和使用。 有关常见问题解答的详细信息.
这个命令将 runoob_title
列的名字改为 title
,类型保持不变。
使用 CHANGE
修改列名和类型
ALTER TABLE runoob_tbl
CHANGE runoob_author author TEXT;
这个命令将 runoob_author
列的名字改为 author
,并且将类型改为 TEXT
。
使用 MODIFY
修改类型
ALTER TABLE runoob_tbl
MODIFY submission_date DATETIME;
这个命令将 submission_date
列的类型从 TIMESTAMP
改为 DATETIME
。
增加一个列
ALTER TABLE runoob_tbl
ADD page_views INT DEFAULT 0;
这个命令在表中新增了一个名为 page_views
的列,类型为 INT
,默认值为 0
。
增加两个列
ALTER TABLE runoob_tbl
ADD (
category_id INT DEFAULT 0,
status TINYINT DEFAULT 1
);
这个命令在表中新增了两个列:category_id
(类型为 INT
,默认值为 0
)和 status
(类型为 TINYINT
,默认值为 1
)。
删除列
ALTER TABLE runoob_tbl
DROP COLUMN page_views;
执行这个命令后,page_views
列将会从 runoob_tbl
表中被永久删除。请在执行此类操作前确保该列不再需要,因为一旦删除,所有相关数据都将丢失。
删除两个列
ALTER TABLE runoob_tbl
DROP COLUMN category_id,
DROP COLUMN status;
Mysql8简单聚合查询、事务、索引实战
查询排序和分组查询
先创建一个数据表
mysql> create table user(
-> id bigint unsigned NOT NULL AUTO_INCREMENT,
-> username varchar(100) NOT NULL,
-> learntype varchar(100) NOT NULL,
-> grade int NOT NULL,
-> PRIMARY KEY (`id`)
-> )ENGINE=InnoDB;
插入数据
insert into user (username, learntype, grade)
values('shijiange', 'c', 80);
insert into user (username, learntype, grade)
values('shijiange', 'python', 90);
insert into user (username, learntype, grade)
values('daokun', 'c', 87);
insert into user (username, learntype, grade)
values('daokun', 'python', 85);
insert into user (username, learntype, grade)
values('afu', 'c', 60);
insert into user (username, learntype, grade)
values('afu', 'python', 62);
查询排序
- id升序:
select * from user order by id asc;
- id降序:
select * from user order by id desc;
- 通过学习类型:
select * from user order by learntype;
- 多列排序:
select * from user order by learntype asc,grade desc;
注意:user是表名,你的表名可能是其他名字,我的是user。
按照id升序查询
![image-20230709100641613](https://img-blog.csdnimg.cn/img_convert/8838cd55792ad0e910ffbab01737e599.png)
按照id降序查询
![image-20230709100746022](https://img-blog.csdnimg.cn/img_convert/b6af38647b5a6239229f0c19482d543f.png)
多列排序(learntype+grade)
![image-20230709101250664](https://img-blog.csdnimg.cn/img_convert/f3052a7625d5dd95a036cb2a6d2038c6.png)
多列排序(username升序+grade降序)
![image-20230709101445373](https://img-blog.csdnimg.cn/img_convert/ec1427cfa8aa65ba5ef39084973a4d0a.png)
分组查询
- 分组查询可以针对某个列进行分组
- 分组以后可以使用统计函数:count,sum,avg,max,min
先了解统计函数
查成绩最大值seletc max(grade) from user
![image-20230709101849752](https://img-blog.csdnimg.cn/img_convert/f07679bbbafeef675aa16723410c43d9.png)
分组统计
# 按照名字作为组别,显示username,统计总分(默认是按照id)
select username,sum(grade) from user group by username;
![image-20230709102242733](https://img-blog.csdnimg.cn/img_convert/32b0f6cce5c3b24f53f28ae38871da98.png)
# 按照名字作为组别,显示username(升序),统计总分
select username,sum(grade) from user group by username order by username;
![image-20230709102505210](https://img-blog.csdnimg.cn/img_convert/7738a2d19c7f19b3a6833cc119f6569d.png)
# 按照名字作为组别,显示username,统计总分(降序)
select username,sum(grade) from user group by username order by username;
![image-20230709102619416](https://img-blog.csdnimg.cn/img_convert/d200431959188a757c946dd0b3fe64a8.png)
多表组合查询
创建user表
create table user(
id bigint unsigned auto_increment,
username varchar(100) not null,
password varchar(100) not null,
primary key ( id )
)engine=InnoDB;
插入数据
insert into user (username, password) values
('daokun', 'mypwd');
insert into user (username, password) values
('shijiange', 'shijianpwd');
insert into user (username, password) values
('afu', 'afupwd');
insert into user (username, password) values
('other', 'opwd');
创建logincount表
create table logincount(
username varchar(100) not null,
count bigint unsigned not null
)engine=InnoDB;
插入数据
insert into logincount (username, count) values
('daokun', 10);
insert into logincount (username, count) values
('shijiange', 20);
insert into logincount (username, count) values
('other', 50);
insert into logincount (username, count) values
('nonono', 90);
Mysql多表联合查询
-
inner join两张表共有
select user.username,user.password,logincount.count from user inner join logincount on user.username=logincount.username;
-
left join以左表为主
select user.username,user.password,logincount.count from user left join logincount on user.username=logincount.username;
- right join以右表为主
select user.username,user.password,logincount.count from user right join logincount on user.username=logincount.username;
正则查询
这个比较少用,因为比较耗性能。
普通正则表达式
- .表示任意一个字符, *表示前面一个字符出现0次 或者多次
- [abc]表示中括号内任意一个字符,[^abc]表示非 中括号内的字符
- [0-9]表示数字,[a-z]表示小写字母,[A-Z]表示 大写字母
- ^xx表示以xx开头,xx$表示以xx结尾
- [a-zA-Z]表示所有字母,[a-zA-Z0-9]表示所有字 母+数字
- [^0-9]表示非数字
- \d表示数字,\D表示非数字
- \s表示空白字符,\S表示非空白字符
扩展正则表达
在普通正则符号的基础上进行扩展
- ?表示前面字符出现0或者1次,+前面字符出现1或者多次
- {a}表示前面字符匹配a次,{a,b}表示前面字符匹配a到b次
- {,b}表示前面字符匹配0次到b次,{a,}前面字符匹 配a或a+次
- string1|string2表示匹配string1或者string2
正则查询实战
正则查询是非常耗性能
-
只要匹配部分就能输出
select * from user where username regexp 'dao';
-
普通正则
select * from user where username regexp 'ge$';
-
扩展正则查询
select * from user where username regexp 'ge$|afu';
MySQL的事务操作实战
准备
SQL建User表
create table user(
id bigint unsigned not null auto_increment,
username varchar(100) not null,
password varchar(100) not null,
money int unsigned not null,
primary key ( id )
)engine=InnoDB;
数据准备
insert into user (username, password, money)
values ('shijiange', 'mypwd', 10000);
insert into user (username, password, money)
values ('lisi', 'mypwd', 10000);
事务介绍
- 事务可以由一个或者多个操作组成
- 事务就是把一个或多个操作捆绑在一起
- 事务还有一个功能就是预防部分误操作
MySQL默认开启事务自动提交
事务自动提交:数据的更新立即生效,无法回滚
可以查看
show variables like "%commit%";
![image-20230709175456611](https://img-blog.csdnimg.cn/img_convert/5dc15c1c6db161c3aad9ae1a1541ed37.png)
![image-20230709175557271](https://img-blog.csdnimg.cn/img_convert/48b35005577131282eb61c7344096132.png)
MySQL事务自控实战
准备两个ssh连接,一个ssh1,一个ssh2。默认是ssh1在操作。
-
当前会话事务自动提交关闭
set autocommit=OFF;
现在,我们在ssh1输入
commit
提交一下ssh2在查看一次
也就是说,提交才会更改
-
删除表,回滚复原
delete from user; select * from user; rollback; select * from user;
也就是可以预防update,delete执行操作失误。注意
drop table user;
无法回滚 -
多个操作组成一个事务
原子操作:shijiange向lisi转账5000块钱,要么都一起成功,要么一起失败,不能出现一个成功,一个失败。set autocommit=OFF; update user set money=5000 where username='shijiange'; update user set money = 15000 where username ='lisi'; # rollback 或者 commit;
再强调一下:不是所有都能回滚
- 回滚:
delete from user;
- 不可回滚:
drop table user;
MySQL更改表结构实战
如果我们创建了一个表有username,password字段,但是后面发现还需要monney字段,这个时候就需要更改表结构了。
改表结构需求
- 添加字段,例如用户表新增存款字段
- 字段长度不足,例如用户名20位不够存储
SQL建表
create table user(
id bigint unsigned not null auto_increment,
username varchar(20) not null,
password varchar(100) not null,
primary key ( id )
)engine=InnoDB;
新增和删除字段
-
增加money字段
alter table user add money bigint unsigned;
-
删除money字段
alter table user drop money;
-
指定位置新增字段:
alter table user add money bigint unsigned default 0 after username;
-
加入到第一个字段
alter table user add money int unsigned default 0 first
修改字段类型
-
修改money字段类型
alter table user modify money bigint unsigned default 0;
-
扩大 username字段
alter table user modify username varchar(100) not null default '';
MySQL索引实战
索引说明
- MySQL默认字段是没有索引的
- 索引大多情况下可以加快数据的查询
不适用于:
- 数据类型变化不多的,例如性别
- 数据更新频率更新频率不高的不建议,因为更新数据需要维护索引
实践思路:先创建一个数据表user。然后利用这个user表重复创建100多万数据。在这100万条数据添加一个特别的数据。试试普通查询和索引查询的区别。
创建user表
create table user(
id bigint unsigned not null auto_increment,
username varchar(100) not null,
password varchar(100) not null,
primary key ( id )
)engine=InnoDB;
插入两条数据
insert into user (username, password) values
('shijiange', 'mypwd');
insert into user (username, password) values
('justdoit', 'mypwd');
百万数据准备(这条命令多用几次)
insert into user (username,password) select username, password from user;
查看数据多少(我的是200万)
select count(*) from user;
![image-20230709222903925](https://img-blog.csdnimg.cn/img_convert/15da251c4a7e22fd55103766a0798bc3.png)
插入特别的数据
insert into user (username, password) values ('daokun', 'dfupwd');
普通查找这条特别的数据(花了0.57s)
select * from user where username="daokun";
![image-20230709223208694](https://img-blog.csdnimg.cn/img_convert/51f8cb320f7781c76b6d1aa8183208db.png)
索引查找特别数据
-
建立索引(花了14s)
alter table user add index i_usernam (username);
-
查找数据(0s)
select * from user where username="daokun";
查看表中的索引show indexes from user;
主键和唯一键默认会创建索引
注意:正则是无法命中索引的!!
MySQL的权限控制
设置用户和监听地址
Mysql监听说明
如果本地使用,监听在127.0.0.1,无风险(安全是安全,但是也没啥用啊)
如果局域网访问,监听在局域网,风险较小
如果是公网访问,风险比较大。权限如果不会设置 容易被入侵
修改监听
cd /etc/my.cnf
修改成0.0.0.0
![image-20230710003825557](https://img-blog.csdnimg.cn/img_convert/40c41cecdab7a2e8ae7dd5993999b408.png)
/etc/init.d/mysqld restart
查看netstat -tulnp |grep mysql
![image-20230710003504797](https://img-blog.csdnimg.cn/img_convert/e1e40c7994e0b3260b42b8fe94e71c20.png)
Mysql8默认只允许localhost登录
mysql.user记录着权限信息
select user,host from mysql.user;
查看用户权限
show grants for 'root'@'localhost';
Mysql8使用新的认证方式—caching_sha2_password
show variables like '%authen%';
因为客户端可能不支持这种验证方式,所以我们设置用户的时候,还是使用旧的认证方式。
Mysql增加用户
密码建议用强密码
# 创建用户和监听IP
create user 'daokun'@'127.0.0.1' identified with mysql_native_password by 'daokunpwd';
# 设置权限,这一步不能忘记了,不然新用户很多事情干不了
grant all privileges on *.* to 'daokun'@'127.0.0.1' with grant option;
flush privileges;
新用户登录
mysql -udaokun -pdaokunpwd -h 127.0.0.1 -A
因为指定了监听IP地址,所以需要-h
127.0.0.1代表允许访问的client ip。可以给多个 IP,例如192.168.%、10.%
all privileges代表所有权限。这里可以给具体权 限,例如select、update等
*.*
代表所有库所有表。这里可以给具体库、具体表,例如daokun.*daokun.user
with grant option代表给grant权限
with mysql_native_password使用旧的认证grant all privileges on *.* to 'daokun'@'%' with grant option; # 换成%是所有主机
检查有没有添加
![image-20230709234925343](https://img-blog.csdnimg.cn/img_convert/beb0255e88eb2a7d5380dd1b0cf9b12d.png)
修改用户密码
ALTER USER 'daokun'@'127.0.0.1' IDENTIFIED BY 'daokun123';
# 或者
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
当前用户改密码
SET PASSWORD = PASSWORD("newpassword");
重置用户密码
SET PASSWORD FOR 'daokun'@'127.0.0.1' = 'new_password';
权限回收
revoke all privileges on *.* from 'daokun'@'127.0.0.1';
flush privileges;
具体可以查看
SHOW GRANTS FOR ‘daokun’@’127.0.0.1’;
用户删除
drop user 'daokun'@'127.0.0.1';
客户端权限控制
Mysql图形化客户端
- Navicat for MySQL,需要注册破解,比较麻烦
- MySQLWorkbench,可直接使用。下载链接: https://dev.mysql.com/downloads/workbench/
建议下载和自己MySQL版本号对应的
win客户端IP:192.168.2.2
centos服务器192.168.2.4
按照上面的内容创建一个用户
create user 'mydaokun'@'192.168.2.2' identified with mysql_native_password by 'daokunpwd';
# 设置权限,这一步不能忘记了,不然新用户很多事情干不了
grant all privileges on *.* to 'mydaokun'@'192.168.2.2' with grant option;
flush privileges;
win客户端连接
![image-20230710000745930](https://img-blog.csdnimg.cn/img_convert/7b08533fe9eda7e8589e7f4d84889611.png)
MySQL日常运维
MySQL dump备份数据库
dump备份数据库会锁表,备份前要注意是否影响业务。
数据量大也不建议使用dump,可选择直接复制文件。
准备:我们有test1,test2两个数据库;test1里面有user表
备份
备份多个库
- 备份所有库:
mysqldump -uroot -pdaokun --all-databases >/tmp/all.sql
- 备份多个库:
mysqldump -uroot -pdaokun --databases test1 test2 >/tmp/all.sql
备份某个具体库或某个具体表
- 备份test1库:
mysqldump -uroot -pdaokun test1 >/tmp/test1.sql
- 备份test1下的user表:
mysqldump -uroot -pdaokun user test1>/tmp/user_test1.sq
特殊备份
- 忽略某个表:
mysqldump -uroot -pdaokun user --ignore-table=test1.user >/tmp/ignore.sql
- 备份表结构:
mysqldump -uroot -pdaokun user -d >/tmp/usertruct.sql
恢复
数据恢复
恢复多个库
- 登录到Mysql
- 设置编码:
set names utf8;
- 导入数据:
source /tmp/all.sql;
第二步默认是utf8,如不放心可以查看编码:
show variables like '%char%';
/tmp/all.sql是路径
恢复单个库
恢复test1里面user表
- 自己创建库:
create database test1;
- 进入user表:
use user;
- 导入数据:
source /tmp/user_test1.sql
MySQL重新初始化
什么情况下需要重新初始化数据库
- 学习中创建了很多无用的库,权限分配比较乱
- 安装完Mysql,用了一段时间,想废弃之前的数据
- 重新初始化会让Mysql恢复到原始的状态
注意:Mysql重新初始化 1. 重新初始化数据会丢失,注意备份 2. 线上环境请勿乱操作
重新初始化步骤
- 停止Mysql数据库。
/etc/init.d/mysqld stop
- 并删除数据目录
\rm -rf /data/mysql/*
- 重新初始化命令:
mysqld --initialize -- user=mysql --basedir=/usr/local/mysql -- datadir=/data/mysql
- 记住临时密码:xxx
- 启动数据库:
/etc/init.d/mysqld restart
- 进入Mysql更改密码:
alter user 'root'@'localhost' identified with mysql_native_password by 'daokun'
运维管理命令
能看到客户端IP、用户、执行的命令
- 在MySQL里面输入
show processlist;
- 在MySQL里面输入
show full processlist;
在Linux输入:mysql -uroot -pshijiangepwd -A -e "show processlist;"
等效于上面的1
Shell命令统计Mysql信息
mysql -uroot -daokun -A -e "show processlist;"|awk '{print $3}'
查看Mysql配置
- 所有设置查看:show variables;
- 字符集配置查看:show variables like ‘%char%’;
- 事务设置:show variables like ‘%commit%’;
- 密码过期时间设置:show variables like ‘%password%’;
- 最大连接数设置:show variables like ‘%connect%’;
- 缓存大小设置:show variables like ‘%buffer%’;
- 超时时间设置:show variables like ‘%timeout%’;
更改Mysql的配置
-
当前会话设置,临时生效:set autocommit=OFF;
-
全局设置,永久生效,只要Mysql不重启:set global max_connections=1024;
-
永久的需要写入配置文件预防Mysql重启后失效
Mysql的状态查看
可用来监控
- 全部状态:show global status;
- 启动时间:show global status like ‘%uptime%’
- . 流量信息:show global status like ‘%bytes%’;
- 连接信息:show global status like ‘%connect%’;
- 增删改查、事务提交信息:show global status like ‘%com_select%’;
MySQL忘记密码解决方案
前提条件:MySQL服务器能够登录进去(ssh)
本文有两种方法:
- 命令修改(MysSQL8不一定行)
-
跳过授权表(通用)
- 关闭数据库
mysqladmin shutdown -p
- 跳过授权表启动
具体文件看各自怎么路径安装的,会一波三折,耐心看报错
/usr/local/mysql/bin/mysqld --default-file=/etc/mt.cnf --skip-grant-tables --skip-networking=on --user=mysql &
3. 直接进去(不输入密码)
mysql -uroot -p
- 修改密码
- 配置文件修改(推荐)
- 备份Mysql配置
cp /etc/my.cnf /ect/my.cnf.bak
- 更改Mysql配置,新增skip-grant-tables和bindaddress=127.0.0.1,重启Mysql
- 无密码登录到数据库:
mysql -uroot -A
- 重置密码:
update mysql.user set authentication_string='' where user='root' and host='localhost';
- 还原配置,重启Mysql
cp /ect/my.cnf.bak /ect/my.cnf
- 使用空密码登录,修改密码
alter user 'root'@'localhost' identified with mysql_native_password by 'daokun';
- 备份Mysql配置
MySQL8安装脚本
实测可用
#!/bin/bash
# 定义MySQL 8.0的yum源URL
MYSQL_REPO_URL="https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm"
# 检查并删除所有旧的MySQL GPG密钥
echo "正在检查并删除所有旧的MySQL GPG密钥..."
sudo rpm -e --allmatches gpg-pubkey-a8d3785c
# 使用find命令查找MySQL GPG密钥的位置
echo "正在查找MySQL GPG密钥的位置..."
MYSQL_GPG_KEY_PATH=$(sudo find /etc/pki/rpm-gpg/ -name 'RPM-GPG-KEY-mysql*' | head -n 1)
if [ -z "$MYSQL_GPG_KEY_PATH" ]; then
echo "无法找到MySQL GPG密钥。"
exit 1
fi
echo "找到MySQL GPG密钥: $MYSQL_GPG_KEY_PATH"
# 导入新的MySQL GPG密钥
echo "正在导入新的MySQL GPG密钥..."
sudo rpm --import $MYSQL_GPG_KEY_PATH
# 检查MySQL 8.0的yum源是否已经存在
if ! yum repolist enabled | grep "mysql.*-community.*"; then
echo "MySQL 8.0 yum源不存在,正在添加..."
# 添加MySQL官方的yum源
sudo yum install -y $MYSQL_REPO_URL
if [ $? -ne 0 ]; then
echo "添加MySQL yum源失败,请检查网络连接或者源URL。"
exit 1
fi
else
echo "MySQL 8.0 yum源已存在。"
fi
# 安装MySQL 8.0
echo "开始安装MySQL 8.0..."
sudo yum install -y mysql-community-server --nogpgcheck
# 检查安装是否成功
if [ $? -eq 0 ]; then
echo "MySQL 8.0 安装成功。"
# 启动MySQL服务
sudo systemctl start mysqld
# 获取并显示MySQL的初始化密码
echo "正在获取MySQL的初始化密码..."
INITIAL_ROOT_PASSWORD=$(grep 'password' /var/log/mysqld.log | head -n1)
echo $INITIAL_ROOT_PASSWORD
# 开机自启MySQL服务
sudo systemctl enable mysqld
echo "MySQL服务已启动并设置为开机自启。"
else
echo "MySQL 8.0 安装失败,请检查yum源或者联系管理员。"
fi
改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'daokun';
FLUSH PRIVILEGES;"