MySQL

安装

Docker

docker run --name <mysql> -v <local/path>:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=<password>

1
docker run -p 3306:3306 --name mysql \
2
-v ~/data/mysql:/var/lib/mysql \
3
-e MYSQL_ROOT_PASSWORD=password \
4
-d mysql

mysql.yml|Docker Compose

Synology

通过注册表下载完成后，从映像启动；
高级设置
- 存储空间: /docker/mysql -> /var/lib/mysql
- 端口设置: 64406 -> 3306
- 环境: MYSQL_ROOT_PASSWORD -> <密码>

macOS

安装：brew install mysql
启动：brew services start mysql
设置：mysql_secure_installation
连接：mysql -u root -p

1
sunyichaodeMacBook-Pro:~ sunyichao$ /usr/local/opt/mysql/bin/mysql_secure_installation # mysql 提供的配置向导
2
Press y|Y for Yes, any other key for No: k # 是否采用mysql密码安全检测插件
3
Remove anonymous users? [Y/n] Y # 是否删除匿名用户
4
Disallow root login remotely? [Y/n] Y # 是否禁止远程登录
5
Remove test database and access to it? [Y/n] Y # 删除测试数据库，并登录
6
Reload privilege tables now? [Y/n] Y# 重新载入权限表

CentOS

1
  # 下载源
2
  wget https://repo.mysql.com/mysql57-community-release-el7-9.noarch.rpm
3
  # 安装源
4
  yum localinstall mysql57-community-release-el7-9.noarch.rpm
5
  # 安装 MySQL
6
  yum install mysql-community-server -y
7
  # 启动 MySQL
8
  systemctl start mysqld
9
  # 设为开机启动
10
  systemctl enable mysqld
11

12
  # 查看密码
13
  grep password /var/log/mysqld.log
14
  # 登录
15
  mysql -u root -p
16
  # 修改密码
17
  ALTER user '用户名'@'%' IDENTIFIED BY '新密码';
18

19
  # 配置文件修改 /etc/my.cnf
20
  [mysqld]
21
  character_set_server = utf8 # 设置字符集
22
  port = 64406 # 修改默认端口

连接

命令行连接: mysql -h <ip> -P <port> -u <name> -p

操作

修改密码

1
-- 关联 mysql 数据库
2
USE mysql;
3
-- 查看用户名和host
4
SELECT user, host from user;
5
SELECT host, user, authentication_string, plugin from user;
6

7
-- 查看密码策略
8
SHOW VARIABLES LIKE 'validate_password.%'
9
-- 修改密码
10
ALTER user 'root'@'localhost' IDENTIFIED BY 'password';
11

12
-- 设置密码检测强度，默认 MEDIUM
13
set global validate_password.policy = 'LOW';
14
-- RDS MySQL 5.7 修改密码
15
ALTER user '用户名'@'%' IDENTIFIED BY '新密码';
16
-- RDS MySQL 5.6 修改密码（后台）
17
use mysql;
18
UPDATE user SET PASSWORD=PASSWORD("新密码") WHERE user='用户名';
19
flush privileges;

创建账号

1
-- '%' - 所有情况都能访问
2
-- ‘localhost’ - 本机才能访问
3
-- '127.0.0.1'
4
CREATE USER 'user1'@'%' IDENTIFIED BY '07fa533360d9';
5
-- 添加权限
6
-- all 可以替换为 select,delete,update,create,drop
7
grant all privileges on 想授权的数据库.* to 'user1'@'%';
8
-- 刷新
9
flush privileges;

授权

1
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1’ WITH GRANT OPTION;
2
grant all on hi.* to 'user4'@'%';
3

4
-- 取消指定库所有权限
5
revoke all privileges ON lscloud.* from 'lscloud'@'%';
6

7
-- 设定指定列授权
8
grant select (paytype) ON lscloud.ecsmonitor to 'lscloud'@'%';
9
-- 取消指定列授权
10
revoke select (paytype) ON lscloud.ecsmonitor from 'lscloud'@'%';
11

12
-----------------------
13

14
-- 获取数据指定日期内所有数据
15
select ecsinfo.departmentName, ecsinfo.projectName, ecsinfo.cpu, ecsinfo.memory, ecsinfo.privateIpAddress, ecsinfo.eipAddress, ecsmonitor.memoryUtilization, ecsmonitor.cpuUtilization, ecsmonitor.updateTime from ecsinfo join ecsmonitor on ecsinfo.instanceId = ecsmonitor.instanceId AND ecsmonitor.updateTime > "2020-11-30";

结束进程

1
SHOW PROCESSLIST; #查看当前进程
2
KILL id; # 结束指定进程

当锁表的时候可以尝试把对应进程都结束掉。

删除

删除表中指定日期外的数据

1
DELETE FROM logs WHERE created_at < NOW() - INTERVAL 7 DAY

删除指定项目所有数据

1
DELETE
2
FROM
3
  ecsmonitor
4
WHERE
5
  projectName = "报名系统";

搜索

模糊搜索和全文搜索

1
-- 模糊搜索
2
SELECT
3
  *
4
FROM
5
  ecsmonitor
6
WHERE
7
  instanceName LIKE '%项目%';
8

9
-- 全文搜索
10
-- 建立索引：index_ecsmonitor 索引名，ecsmonitor 表名，instanceName 字段名
11
CREATE FULLTEXT INDEX index_ecsmonitor ON ecsmonitor ( instanceName ) WITH PARSER ngram;
12
-- 搜索
13
SELECT
14
  *
15
FROM
16
  ecsmonitor
17
WHERE
18
  MATCH ( instanceName ) AGAINST ( '项目' IN NATURAL LANGUAGE MODE );

查看数据库大小

1
SELECT
2
  table_schema AS '数据库',
3
  sum( table_rows ) AS '记录数',
4
  sum(
5
  TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
6
  sum(
7
  TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
8
FROM
9
  information_schema.TABLES
10
GROUP BY
11
  table_schema
12
ORDER BY
13
  sum( data_length ) DESC,
14
  sum( index_length ) DESC;

定时执行任务

1
CREATE EVENT test -- 任务名
2
ON SCHEDULE EVERY 1 DAY -- 执行周期
3
DO -- 需执行的语句

在原值基础上修改值

1
UPDATE <table name> SET image_url= CONCAT('result/',image_url)

报错

ModuleNotFoundError: No module named ‘mysql’

pip 安装：

pip install mysql-connector-python

Authentication plugin ‘caching_sha2_password’ is not supported

pip 安装

mysql-connector-python, 而非 mysql-connector