MySQL

2022-03-31

安装

[[Docker]]

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

Terminal window
docker run -p 3306:3306 --name mysql \
-v ~/data/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
-d mysql

[[Docker Compose]]

[[Synology]]

  1. 通过注册表下载完成后，从映像启动；
  2. 高级设置
    • 存储空间: /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
Terminal window
sunyichaodeMacBook-Pro:~ sunyichao$ /usr/local/opt/mysql/bin/mysql_secure_installation # mysql 提供的配置向导
Press y|Y for Yes, any other key for No: k # 是否采用mysql密码安全检测插件
Remove anonymous users? [Y/n] Y # 是否删除匿名用户
Disallow root login remotely? [Y/n] Y # 是否禁止远程登录
Remove test database and access to it? [Y/n] Y # 删除测试数据库，并登录
Reload privilege tables now? [Y/n] Y# 重新载入权限表

[[CentOS]]

Terminal window
  # 下载源
  wget https://repo.mysql.com/mysql57-community-release-el7-9.noarch.rpm
  # 安装源
  yum localinstall mysql57-community-release-el7-9.noarch.rpm
  # 安装 MySQL
  yum install mysql-community-server -y
  # 启动 MySQL
  systemctl start mysqld
  # 设为开机启动
  systemctl enable mysqld


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


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

连接

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

操作

修改密码

-- 关联 mysql 数据库
USE mysql;
-- 查看用户名和host
SELECT user, host from user;
SELECT host, user, authentication_string, plugin from user;


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


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

创建账号

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

授权

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


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


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


-----------------------


-- 获取数据指定日期内所有数据
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";

结束进程

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

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

删除

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

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

删除指定项目所有数据

DELETE
FROM
  ecsmonitor
WHERE
  projectName = "报名系统";

搜索

模糊搜索和全文搜索

-- 模糊搜索
SELECT
  *
FROM
  ecsmonitor
WHERE
  instanceName LIKE '%项目%';


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

查看数据库大小

SELECT
  table_schema AS '数据库',
  sum( table_rows ) AS '记录数',
  sum(
  TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
  sum(
  TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
  information_schema.TABLES
GROUP BY
  table_schema
ORDER BY
  sum( data_length ) DESC,
  sum( index_length ) DESC;

定时执行任务

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

在原值基础上修改值

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

参考

# 数据库
# brew
# Docker
# SQL