环境:centos7
1.创建目录
切换目录:cd /usr/local/
创建目录:mkdir mysql
2.下载mysql
切换目录:cd mysql/
下载mysql:wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
3.rpm安装mysql
rpm安装mysql:rpm -ivh mysql80-community-release-el7-1.noarch.rpm
4.安装mysql:yum -y install mysql-server
拓展:下载mysql命令:yum search mysql
安装命令:yum --installroot=/usr/local/mysql --releasever=/ -y install mysql-server
批量按时间删除文件:find /usr/local/mysql -name "*.*" -mtime -1 -exec rm {} \;
/usr/local/mysql :需要清理的指定目录
-name "*.*" :查找所有文件。若查找某一类,写法:-name "*.jpg*"
-mtime -1 :查找1天后的文件,不包含目录。-1代表1天之后,+1代表1天之前。
-exec :固定写法
{} \; :一对花括号+空格+\+分号
注意:若报错“遗漏“-exec”的参数”,请检查命令写法
批量按照文件大小查找:find . -size -2k -exec ls -l {} \;
5.配置文件
配置文件:vim /etc/my.cnf
日志文件:more /var/log/mysqld.log
服务启动脚本文件:less /usr/lib/systemd/system/mysqld.service
socket文件:vi /var/run/mysqld/mysqld.pid
6.启动服务
切换目录:cd /usr/lib/systemd/system
授权写功能:chmod +x mysqld.service
启动服务:systemctl start mysqld.service
7.设置开机自动启动:systemctl enable mysqld.service
8.查看随机密码:grep "password" /var/log/mysqld.log
9.登录mysql:mysql -u root -p
10.重置密码:alter user 'root'@'localhost' identified by 'Root@123';
11.添加远程授权权限,远程工具可连接:grant all privileges on *.* to 'root'@'%' identified by '密码' with grant option;
出现问题:
解决方法:
CREATE USER 'root'@'%' IDENTIFIED BY '密码';
GRANT ALL ON *.* TO 'root'@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
12.刷新权限:flush privileges;
13.(慎操作)移除远程访问:revoke all privileges on *.* from root@localhost;
revoke grant option on *.* from root@localhost;
14.创建表空间、创建分库:create database test;
15.进入数据库:use test;
16.创建表:CREATE TABLE `mytest` (`id` int(7) NOT NULL AUTO_INCREMENT, `name` char(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
17.显示表:show tables;
18.插入数据:INSERT INTO `mytest` VALUES (1,'sven'),(2,'jim'),(3,'zhu'),(4,'wang'),(5,'ftd'),(6,'test'),(7,'test01'),(8,'test02'),(9,'test03');
19.查询数据:select * from test.mytest;
20.更新mysql中数据:set @RECV = 1;
select @RECV;
update mytest set name = 'qqq' where id = 1;
select @RECV;
21.mysql开发包安装
切换目录:cd /usr/local/mysql/
安装开发包:yum install mysql-devel -y
22.mysql出现问题
(1)退出mysql:exit quit
(2)切换目录:cd /usr/lib/systemd/system
(3)停止mysql:systemctl stop mysqld.service service mysqld stop
(4)查找文件:find / -name mysqld_safe
(5)复制文件:cp /var/lib/docker/overlay2/501f2991295e700aaf32e02d9508f02b6d9358a39d5786fad01254af10290415/diff/usr/bin/mysqld_safe /var/lib/mysql
(6)切换目录:cd /var/lib/mysql
(7)屏蔽权限:./mysqld_safe --skip-grant-table
出现问题:找不到mysqld_safe
解决方法:命令mysqld --user=mysql --skip-grant-tables --skip-networking & ,将mysqld_safe改为mysqld
(8)新开一个xshell,执行命令:mysql -u root mysql
(9)删除空格:delete from user where USER='';
(10)刷新权限: FLUSH PRIVILEGES;
(11)退出数据库:\q
(12)切换目录:cd /usr/lib/systemd/system
(13)重启mysql:service mysqld start
(14)登录数据库:mysql -u root -p
(15)查看数据库:show databases;
(16)查看当前数据库:select database();
23. root权限丢失只能看到information_schema数据库
(1)编辑文件:vi /etc/my.cnf
(2)文档末尾添加:skip-grant-tables
(3)查看文档末尾:tail /etc/my.cnf
(5)切换目录:cd /usr/lib/systemd/system
(6)重启mysql:service mysqld start
(7)登录数据库:mysql -u root -p
(8)进入mysql库:use mysql;
(9)刷新权限:flush privileges;
(10)查看数据库表:show databases;