mysql用户管理
1. mysql 的登录方式有2种
# 网络连接串
mysql -uroot -p123456 -hlocalhost;
# 通过套接字
mysql -uroot -p123456 -S /data/mysql/tmp/mysql.sock
2. mysql启动过程
① 启动后台守护进程，并生成工作线程
② 预分配内存结构mysql处理数据使用
3 实例是什么
mysql的后台进程+线程+预分配的内存结构
4 存储引擎
存储数据
检索数据
通过索引查找数据
5 mysql的连接管理
①本地连接
mysql -uroot -p123456 -h localhost
mysql -uroot -p123456 -S sock_dir
②远程连接
mysql -utest -p123456 -h 10.0.0.87
6 mysql 用户及权限基本管理
权限： 功能：针对不同用户设置对不同对象管理能力 select update delete insert create 。。。
权限范围：
*.* ：全局范围oldboy.* :单库级别oldboy.t1 : 单表级别
grant all on wordpress.* to wordpress@'10.0.0.%' identified by '123456'; 权限 权限范围 用户 密码
7 mysql的启动和关闭
① mysql的启动流程
mysql.server ---启动----> mysqld_safe ---启动-----> mysqld
↑ ↑
|启动 | 启动
| |
service mysql start ./bin/mysqld_safe &
② mysql数据库的关闭
mysqladmin -uroot -p123456 shutdown
/etc/init.d/mysqld stop
kill -9
8 mysql 启动参数设置
① 预编译时候设置参数 参数会硬编码到程序中
② 命令行方式设定启动参数
③ 初始化的配置文件
优先级： ② > ③ > ①
9 /etc/my.cnf 影响了什么
① 影响了数据启动
[mysqld]
[mysqld_safe]
[server]
②影响了到数据库的链接
[mysql]
[mysqladmin]
[mysqldump]
[client]
10 数据库配置文件
[mysqld]
basedir=/application/
mysql
datadir=/application/mysql/
data
socket=/application/mysql/tmp/
mysql.sock
port=
3306
server_id=
10
log-error=/var/log/
mysql.log
log-bin=/application/mysql/data/mysql-
bin
binlog_format=
row
skip_name_resolve
[mysql]
socket=/application/mysql/tmp/mysql.sock
skip_name_resolve 禁止dns查询，解决远程连接慢问题
11 找回mysql root 密码
② 先停mysql服务
/etc/init.d/mysql stop
② 使用 mysqld_safe附带的 --skip-grant-tables （忽略授权登陆验证）启动MySQL服务
mysqld_safe --skip-grant-tables --user=mysql >/dev/
null 2>&
1 &
③ 登陆mysql
mysql
④ 修改密码
use mysql;
update mysql.user set password=password(
'123456') where user=
'root' and host=
'localhost';
⑤重启mysql
二.配置多实例
1 创建多个目录
[root@db2 ~]# mkdir -p /data/330{7,8,9}
2 准备多套配置文件
[root@db2 ~]# cat /data/3307/my.cnf
[mysqld]
basedir=/data/mysql
datadir=/data/3307
server-id=3307
port=3307
log-bin=/data/3307/mysql-bin socket=/data/3307/mysql.sock log-error=/data/3307/mysql.log [root@db2 ~]# cat /data/3308/my.cnf [mysqld] basedir=/data/mysql datadir=/data/3308 server-id=3308 port=3308 log-bin=/data/3308/mysql-bin socket=/data/3308/mysql.sock log-error=/data/3308/mysql.log [root@db2 ~]# cat /data/3309/my.cnf [mysqld] basedir=/data/mysql datadir=/data/3309 server-id=3309 port=3309 log-bin=/data/3309/mysql-bin socket=/data/3309/mysql.sock log-error=/data/3309/mysql.log
3 初始化多套数据
[root@db2 ~]# /data/mysql/scripts/mysql_install_db --basedir=/data/mysql/ --datadir=/data/3307 --user=mysql
[root@db2 ~]# /data/mysql/scripts/mysql_install_db --basedir=/data/mysql/ --datadir=/data/3308 --user=mysql
[root@db2 ~]# /data/mysql/scripts/mysql_install_db --basedir=/data/mysql/ --datadir=/data/3309 --user=mysql
4 启动mysql 实例
[root@db2 ~]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db2 ~]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db2 ~]# mysqld_safe --defaults-file=/data/3309/my.cnf &
5 检查进程是否存在
[root@db2 ~]# netstat -lnp | grep 330
tcp 0 0 :::3307 :::* LISTEN 24307/mysqld
tcp 0 0 :::3308 :::* LISTEN 24477/mysqld
tcp 0 0 :::3309 :::* LISTEN 24829/mysqld
unix 2 [ ACC ] STREAM LISTENING 108504 24307/mysqld /data/3307/mysql.sock
unix 2 [ ACC ] STREAM LISTENING 108634 24477/mysqld /data/3308/mysql.sock unix 2 [ ACC ] STREAM LISTENING 109335 24829/mysqld /data/3309/mysql.sock
6 登陆mysql实例
[root@db2 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3307 |
+---------------+-------+
[root@db2 ~]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3308 |
+---------------+-------+
[root@db2 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3309 |
+---------------+-------+
7 停mysql 实例
[root@db2 ~]# mysqladmin -S /data/3307/mysql.sock shutdown
[root@db2 ~]# mysqladmin -S /data/3308/mysql.sock shutdown
[root@db2 ~]# mysqladmin -S /data/3309/mysql.sock shutdown
8 修改多实例密码
mysqladmin -uroot -S /data/3307/mysql.sock -p"123123" password "123456"
9 mysql多实例的启动脚本
#!/bin/
bash
##############################
# mysql多实例的启动脚本
# augustyang
# version 1.0
##############################
. /etc/init.d/
functions
port=
3307
mysql_user=
"root"
Cmdpath=
"/data/mysql/bin"
mysql_sock=
"/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/
3307/
3307.pid
start(){
if [ ! -e
"$mysql_sock" ];
then
/bin/bash ${Cmdpath}/mysqld_safe --defaults-
file=/data/${port}/my.cnf --pid-
file=${mysqld_pid_file_path}
2>&
1 > /dev/
null &
sleep 3
if [ -e
"$mysql_sock" ];
then
action "Starting Mysql success" /bin/
true
else
action "mysqld-3307 process already exists" /bin/
true
fi
else
action "Mysql is running" /bin/
true
exit 1
fi
}
stop(){
if [ ! -e
"$mysql_sock" ];
then
action "MySQL--3307 server PID file could not be found! " /bin/
false
return 2
else
mysqld_pid=$(
cat "$mysqld_pid_file_path")
if (
kill -
0 $mysqld_pid
2>/dev/
null);
then
kill $mysqld_pid
sleep 2
fi
if [ ! -e
"$mysql_sock" ];
then
action "MySQL--3307 is stopped " /bin/
true
return 2
fi
fi
}
restart(){
printf "Restarting Mysql...\n"
stop
sleep 2
start
}
case "$1" in
start)
start
;;
stop)
stop
;;
restart)
restart
;;
*
)
printf "Usage: /data/${port}/mysql {start|stop|restart}\n"
esac
mysql 多实例的启动脚本
mysql 多实例的启动脚本
10 找回多实例 mysql密码
① 停 多实例mysql
② 启动数据库时加 --skip-grant-tables 参数注意 该参数要放在结尾
mysqld_safe --defaults-
file=/data/
3307/my.cnf --skip-grant-tables >/dev/
null 2>&
1 &
③ 登陆
mysql -S /data/
3307/mysql.sock
④修改密码
update mysql.user set password=password(
'123456') where user=
'root' and host=
'localhost';
flush privileges;
⑤重启 mysql
三 mysql 用户 管理
1. 收回无效的用户
mysql
> drop user "root"@"::
1";
Query OK, 0 rows affected (
0.00 sec)
mysql> drop user ""@"localhost";
Query OK, 0 rows affected (
0.00 sec)
mysql> drop user ""@"web02";
Query OK, 0 rows affected (
0.00 sec)
mysql> drop user "root"@"web02";
Query OK, 0 rows affected (
0.00 sec)
mysql> select user,host
from mysql.
user;
+------+-----------+
| user | host
|
+------+-----------+
| root
| 127.0.
0.1 |
| root
| localhost
|
+------+-----------+
2 rows
in set (
0.00 sec)
2. 权限管理
grant all on ysl.
* to test@
'10.0.0.%' identified
by '123456';
-- 权限 权限范围 用户 范文 密码
grant SELECT,
INSERT,
UPDATE,
DELETE,
CREATE,
DROP on testdb.
* to zabbix@
'10.0.0.%';
创建用户并授权
-- 创建用户并授权
grant all on *.
* to root@
'10.0.0.%' identified
by '123456';
3. 查询用户的权限
mysql> show grants
for test@
'%';
+--------------------------------------------------------------------------------------------------------------+
| Grants
for test@
% |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.
* TO 'test'@
'%' IDENTIFIED
BY PASSWORD
'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------+
1 row
in set (
0.00 sec)
4. 收回权限
mysql
> revoke create,
drop on *.
* from ysl@
'%';
Query OK, 0 rows affected (
0.00 sec)
mysql
> revoke all on *.
* from ysl@
'%';
Query OK, 0 rows affected (
0.00 sec)
转载于:https://www.cnblogs.com/augustyang/p/10907565.html
