MySQL 基础管理
#用户管理
- 白名单设定
用户名@'白名单'
白名单支持的方式？
wordpress@'10.0.0.%'
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'
- 创建用户
增： mysql> create user zyc@'43.82.209.%' identified by '123'; 查： mysql> desc mysql.user; mysql> select user ,host ,authentication_string from mysql.user 改: mysql> alter user zyc@'43.82.209.%' identified by '456'; 删： mysql> drop user zyc@'43.82.209.%';
- 授权
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,
PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER,
CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE,
REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE,
ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
1 mysql> grant all on wordpress.* to wordpress@'43.82.209.%' identified by '123';
#grant:授权命令
#all:权限
#on: 作用命令
#wordpress.*:权限的作用范围
##
*.* --->全库.全表 管理员用户
wordpress.* --->wordpress库 应用开发用户
wordpress.t1
##
#to: 作用命令2 mysql> grant select ,update,insert,delete on app.* to app@'43.80.209.%' identified by '123';
- 查看授权
1 mysql> show grants for zyc@'43.82.209.%';
- 回收授权
1 mysql> revoke delete on app.* from zyc@'43.82.209.%';
- 本地管理员密码忘记
[root@CentOS-Docker mysql]# systemctl stop mysqld [root@CentOS-Docker mysql]# mysqld_safe --skip-grant-tables --skip-networking & [1] 22160 [root@CentOS-Docker mysql]# 2019-09-14T12:18:20.479292Z mysqld_safe Logging to '/data/mysql/CentOS-Docker.err'. 2019-09-14T12:18:20.517459Z mysqld_safe Starting mysqld daemon with databases from /data/mysql mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.20 MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 3306 [(none)]>flush privileges; Query OK, 0 rows affected (0.00 sec) 3306 [(none)]>alter user root@'localhost' identified by '123'; Query OK, 0 rows affected (0.00 sec)
#连接管理
-u 用户 -p 密码 -h IP -P 端口 -S socket文件 -e 免交互执行命令 < 导入SQL脚本 [root@db01 ~]# mysql -uroot -p -h 10.0.0.51 -P3306 Enter password: mysql> select @@socket; +-----------------+ | @@socket | +-----------------+ | /tmp/mysql.sock | [root@db01 ~]# mysql -uroot -p -S /tmp/mysql.sock Enter password: [root@db01 ~]# mysql -uroot -p -e "select user,host from mysql.user;" Enter password: +---------------+-----------+ | user | host | +---------------+-----------+ | abc | 10.0.0.% | | app | 10.0.0.% | | root | 10.0.0.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ [root@db01 ~]# [root@db01 ~]# mysql -uroot -p <world.sql Enter password: [root@db01 ~]#
#多种启动方式
#初始化配置
- 作用
控制MySQL的启动
影响客户端的连接
- 初始化配置的方法
预编译
配置文件(所有启动方式)
命令行（仅限于mysqld_safe mysqld）
- 初始配置文件
[root@db01 ~]# mysqld --help --verbose |grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 注: 默认情况下，MySQL启动时，会依次读取以上配置文件，如果有重复选项，会以最后一个文件设置的为准。 但是，如果启动时加入了--defaults-file=xxxx时，以上的所有文件都不会读取.
- 配置文件的格式
[标签] 配置项=xxxx 标签类型：服务端、客户端 服务器端标签： [mysqld] [mysqld_safe] [server] 客户端标签： [mysql] [mysqldump] [client] 配置文件的示例展示： [root@db01 ~]# cat /etc/my.cnf [mysqld] user=mysql basedir=/app/mysql datadir=/data/mysql socket=/tmp/mysql.sock server_id=6 port=3306 log_error=/data/mysql/mysql.log [mysql] socket=/tmp/mysql.sock prompt=Master [\\d]>
#多实例的应用
- 准备多个目录
1 mkdir -p /data/330{7,8,9}/data
- 准备配置文件
1 cat > /data/3307/my.cnf <<EOF 2 [mysqld] 3 basedir=/app/mysql 4 datadir=/data/3307/data 5 socket=/data/3307/mysql.sock 6 log_error=/data/3307/mysql.log 7 port=3307 8 server_id=7 9 log_bin=/data/3307/mysql-bin 10 EOF 11 12 cat > /data/3308/my.cnf <<EOF 13 [mysqld] 14 basedir=/app/mysql 15 datadir=/data/3308/data 16 socket=/data/3308/mysql.sock 17 log_error=/data/3308/mysql.log 18 port=3308 19 server_id=8 20 log_bin=/data/3308/mysql-bin 21 EOF 22 23 cat > /data/3309/my.cnf <<EOF 24 [mysqld] 25 basedir=/app/mysql 26 datadir=/data/3309/data 27 socket=/data/3309/mysql.sock 28 log_error=/data/3309/mysql.log 29 port=3309 30 server_id=9 31 log_bin=/data/3309/mysql-bin 32 EOF
- 初始化
1 mv /etc/my.cnf /etc/my.cnf.bak 2 mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql 3 mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql 4 mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
- systemd管理多个实例
1 cd /etc/systemd/system 2 cp mysqld.service mysqld3307.service 3 cp mysqld.service mysqld3308.service 4 cp mysqld.service mysqld3309.service 5 6 vim mysqld3307.service 7 ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf 8 vim mysqld3308.service 9 ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf 10 vim mysqld3309.service 11 ExecStart=/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
- 授权
1 chown -R mysql.mysql /data/*
- 启动
1 systemctl start mysqld3307.service 2 systemctl start mysqld3308.service 3 systemctl start mysqld3309.service
- 验证
1 netstat -lnp|grep 330 2 mysql -S /data/3307/mysql.sock -e "select @@server_id" 3 mysql -S /data/3308/mysql.sock -e "select @@server_id" 4 mysql -S /data/3309/mysql.sock -e "select @@server_id"
