安装
Docker
docker run --name <mysql> -v <local/path>:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=<password>
docker run -p 3306:3306 --name mysql \
-v ~/data/mysql:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=password \
-d mysqlDocker 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
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
# 下载源
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";结束进程
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