mysql命令
wang 2021-07-21 mysql
# 安装
# rpm安装（推荐）
# 查询有没mysql或mariadb安装包
rpm -qa | grep mysql
rpm -qa | grep mariadb
# 删除mysql或mariadb的安装包，不然会有冲突
rpm -ev 包名
# 下载mysql5.7的rpm仓库
wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
# 安装mysql5.7的rpm仓库
yum -y install mysql57-community-release-el7-11.noarch.rpm
# 安装mysql
yum -y install mysql-server --nogpgcheck
# 启动mysql
systemctl start mysqld
# 刚安装的mysql，密码做这个文件里，执行命令拿到密码
cat /var/log/mysqld.log| grep password
# 第一次进入需要修改密码，修改命令
ALTER USER USER() IDENTIFIED BY '123456Admin@123';
# 设置root账号远程访问
grant all privileges on *.* to 'root'@'%' identified by '123456Admin@123' with grant option;
# 刷新配置
flush privileges;
# 如果远程连不上，需要关闭防火墙，或者开放mysql的端口
# 开启
systemctl start mysqld
# 关闭
systemctl stop mysqld
# 重启
systemctl restart mysqld
# 开启自启
systemctl enable mysqld
# 关闭自启
systemctl disable mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
# 源码安装
mysql5.7下载地址：下载 mysql-5.7.36-el7-x86_64.tar.gz 安装包
1.创建mysql用户和用户组
# 检查mysql 用户组是否存在
cat /etc/group | grep mysql
cat /etc/passwd |grep mysql
# 创建mysql 用户组和用户
groupadd mysql
useradd -r -g mysql mysql
1
2
3
4
5
6
7
2
3
4
5
6
7
2.解压并设置设置目录权限
# 解压
tar -zxvf mysql-5.7.36-el7-x86_64.tar.gz
# 改名
mv mysql-5.7.36-el7-x86_64.tar.gz mysql
# 设置用户和用户组权限
chown -R mysql:mysql /usr/local/mysql
chmod -R 755 /usr/local/mysql
# 创建mysql用到的目录
mkdir -p /data/mysql/{data,logs,tmp}
# 设置目录组权限
chown -R mysql.mysql /data/mysql/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
3.添加配置文件
# 添加配置文件，请在分类的《配置文件模板》里查找
vi /etc/my.cnf
1
2
2
4.设置开机自启
cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysql
vi /etc/init.d/mysql
basedir=/usr/local/mysql # 修改安装目录位置
datadir=/data/mysql/data # 修改数据目录位置
chkconfig --add mysql # 注册开机启动服务
chkconfig --list # 查询已有的开机启动服务
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
5.配置环境变量
# 配置环境变量
echo "PATH=$PATH:/usr/local/mysql/bin " >> /etc/profile
# 刷新配置
source /etc/profile
# mysql初始化
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
# 启动报错，临时密码保存在errlog中 ，获取mysql密码
more /data/mysql/logs/error.log |grep password
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
启动
# 启动mysql服务（第一种方式）
service mysql start
# 启动mysql服务（第二种方式）
/usr/local/mysql/support-files/mysql.server start
# 关闭mysql服务
/usr/local/mysql/support-files/mysql.server stop
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
连接
# 登录mysql
mysql -uroot -p
# 修改密码
alter user 'root'@'localhost' identified by 'root';
# 刷新生效
flush privileges;
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
# sql语句
# 导出
mysqldump -h localhost -u用户 -p密码 database table > dump.sql
1
# 修改远程登录
update user set host='%' where host='localhost.localdomain';
1
# 配置远程连接
GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";
1
# 刷新权限表
flush privileges;
1
# 自增id清0
truncate 表名
1
# 查询开起logbin日志
show variables like "%bin%";
1
# 根据id查询子集
SELECT dept_id from sys_dept WHERE FIND_IN_SET (7,pids)
1
# 根据id查询父集
SELECT t2.type_id as type_id, t2.type_name as type_name
FROM (
SELECT
@r AS _id,
(SELECT @r := pid FROM sjjy_wiki_type WHERE type_id = _id) AS pid,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{wikiTypeId}, @l := 0) vars,
sjjy_wiki_type h
WHERE @r <> 0) t1
JOIN sjjy_wiki_type t2 ON t1._id = t2.type_id
ORDER BY t1.lvl DESC
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 索引
# 加索引
-- 1.PRIMARY KEY（主键索引）
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )
-- 2.UNIQUE(唯一索引)
ALTER TABLE `table_name` ADD UNIQUE (`column` )
-- 3.INDEX(普通索引)
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
-- 4.FULLTEXT(全文索引)
ALTER TABLE `table_name` ADD FULLTEXT ( `column` )
-- 5.多列索引
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
# docker安装
# 安装
docker pull mysql:5.7
1
# 启动
docker run --privileged=true --restart=always --name mysql -p 3306:3306 \
-v /usr/local/docker/mysql/log:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-v /usr/local/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-e MYSQL_ROOT_PASSWORD=密码 \
-d mysql:5.7
1
2
3
4
5
6
2
3
4
5
6