文章目录
- 1.创建多个数据目录
- 2.准备多个配置文件
- 3.初始化多套数据目录
- 4.授权目录
- 5.启动数据库
- 6.检查启动
- 7.多实例设置密码
- 8.多实例验证
- 9.连接多实例小技巧
- 4.权限管理
- 5.实践思考
- 1.连接工具
- 2.连接方式
一、mysqld服务程序构成
mysqld是一个守护进程
1.连接层
1.验证用户的身份，用户名密码是否匹配
2.提供两种连接方式（TCP/IP连接、socket连接）
3.连接层提供了一个与sql层交互的线程
2.SQL层
1.接收连接层传过来的SQL语句
2.验证执行的SQL语法
3.验证SQL的语义（DDL,DML,DQL,DCL）
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器传来的执行计划选择最优的一条执行
6.执行器：将最优的一条执行
6.1 与存储引擎层建立交互的线程
6.2 将要执行的sql发给存储引擎层
7.如果有缓存，则走缓存
8.记录日志（binlog）
3.存储引擎层
1.接收SQL层传来的语句
2.与磁盘交互，获取数据，返回给sql层
3.建立与sql层交互的线程
二、mysql的多实例
【多实例部署多结构多版本】
#NGINX多实例就是多个配置文件
#mysql多实例：
1.创建多个数据目录
[root@db01 ~]# mkdir /data/{3307,3308,3309} -p2.准备多个配置文件
[root@db01 data]# vim /data/3307/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3307/data
port=3307
socket=/data/3307/mysql.sock
log-error=/data/3307/data/mysql.err
log-bin=/data/3307/data/mysql-bin
server_id=7
-------------------------------------------
[root@db01 data]# vim /data/3308/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/data/mysql.err
log-bin=/data/3308/data/mysql-bin
server_id=8
--------------------------------------------
[root@db01 data]# vim /data/3309/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/data/3309/data
port=3309
socket=/data/3309/mysql.sock
log-error=/data/3309/data/mysql.err
log-bin=/data/3309/data/mysql-bin
server_id=9
3.初始化多套数据目录
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3307/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3308/data
[root@db01 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3309/data
#使用tree可以查看
[root@db01 scripts]# tree -L 3 /data
4.授权目录
[root@db01 scripts]# chown -R mysql.mysql /data
5.启动数据库
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf &
6.检查启动
[root@db01 scripts]# netstat -lntup|grep 330
7.多实例设置密码
[root@db01 scripts]# mysqladmin -uroot -S /data/3307/mysql.sock password '3307'
[root@db01 scripts]# mysqladmin -uroot -S /data/3308/mysql.sock password '3308'
[root@db01 scripts]# mysqladmin -uroot -S /data/3309/mysql.sock password '3309'
8.多实例验证
[root@db01 scripts]# mysql -uroot -p3307 -S /data/3307/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db01 scripts]# mysql -uroot -p3308 -S /data/3308/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db01 scripts]# mysql -uroot -p3309 -S /data/3309/mysql.sock -e "show variables like 'server_id';"
Warning: Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
9.连接多实例小技巧
[root@db01 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p3309 -S /data/3309/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3308
mysql -uroot -p3308 -S /data/3308/mysql.sock
[root@db01 scripts]# vim /usr/bin/mysql3307
mysql -uroot -p3307 -S /data/3307/mysql.sock
[root@db01 scripts]# chmod +x /usr/bin/mysql*
三、数据库多实例主从
1.主库创建主从复制用户
mysql> grant replication slave on *.* to rep@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00 sec)
2.主库查看binlog名和位置
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 467 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.从库配置主库信息
#从库需要知道：我的主库是谁?主库的主从复制用户是谁?主从复制用户的密码?端口?binlog名?binlog位置
mysql> change master to
-> master_host='127.0.0.1',
-> master_user='rep',
-> master_password='123',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=467,
-> master_port=3307;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
4.从库开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
5.检查主从复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for
6.主从复制状态错误
1）如果IO线程是NO
[root@db01 data]# ping 127.0.0.1
2.检测端口:
[root@db01 data]# telnet 127.0.0.1 3307
2）如果sql线程是NO
1.确定数据库主从之前数据同步
2.确定主库有的库和表从库也有
#解决方法：
方法一：数据要求不严格的情况，忽略错误后，继续同步
解决：
stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql>
3）连接时有反向解析的问题
#连接时地址反向解析了
[root@db01 ~]# mysql -uroot -p123 -h10.0.0.51
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'db01' (using password: YES)
#配置忽略反向解析
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
四、MySQL5.6与MySQL5.7安装的区别
1.cmake的时候加入了bostorg
建议使用二进制的包安装，已经过了cmake阶段，已经把bostorg装好了，无需cmake
[root@db02 mysql-5.7.20]# yum install -y gcc gcc-c++ automake autoconf
[root@db02 mysql-5.7.20]# yum install make cmake bison-devel ncurses-devel libaio-devel
[root@db02 mysql-5.7.20]#
wget httpss://dl.bintray.com/boostorg/release/1.65.1/source/boost_1_59_0.tar.gz
#登录boost.org下载也可以
[root@db02 mysql-5.7.20]# tar xf boost_1_59_0.tar.gz -C /usr/local/
[root@db02 mysql-5.7.20]#
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20 \
-DMYSQL_DATADIR=/application/mysql-5.7.20/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock \
#安装bostorg
-DDOWNLOAD_BOOST=1 \
#安装的目录
-DWITH_BOOST=/usr/local/boost_1_59_0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0
2.初始化时 使用的命令不一样了
#5.6初始化命令
/service/mysql/scripts/mysql_install_db --user= --basedir= --datadir=
#5.7初始化命令
/usr/local/mysql/bin/mysqld --initialize --user= --basedir= --datadir=
#初始--initialize会生成一个临时的随机密码，他会告诉你位置，你自己去找使用密码登录，而且修改密码也需要一个很复杂的密码，大小写数字特殊符号
#想不让他生成密码的话，还可以用另外一个参数--initialize-insecure
五、mysql用户权限管理
1.linux和mysql用户对比
linux系统 | mysql数据库 | |
用户作用 | 1.登陆系统 2.管理系统文件 | 1.登陆MySQL数据库 2.管理数据库对象 |
创建用户 | 1.useradd 2.adduser | 1.create user test@’%’; 2.grant |
用户密码 | 1.useradd cm -p 123 2.passwd cm | 1.create user test@’%’ identified by ‘123’; 2.mysqladmin |
删除用户 | userdel | 1.drop user test@’%’; 2.delete |
修改用户 | usermod | update |
2.在MySQL中,用户是如何定义的
mysql> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| | db01 |
| root | db01 |
| | localhost |
| root | localhost |
+------+-----------+
6 rows in set (0.00 sec)
#上面是6个用户，在数据库中一个完整的用户是：'用户名'@'主机域'
'用户名'@'主机域'
主机域写法：
localhost
127.0.0.1
10.0.0.%
10.0.%.%
10.%.%.%
%
10.0.0.5%（10.0.0.50-10.0.0.59）
db01
10.0.0.0/255.255.255.0
10.0.0.0/24 #可以创建但是不生效
测试：
grant all on *.* to oldboy@'10.0.0.0/255.255.255.0' identified by '123';
grant all on *.* to qiudao@'10.0.0.0/24' identified by '123';
#创建的用户是数字，必须用引号一起来表示字符串
mysql> create user 123@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123@'localhost'' at line 1
mysql> create user '123'@'localhost';
Query OK, 0 rows affected (0.02 sec)
3.用户管理
1）创建用户
mysql> create user oldboy@'10.0.0.%' identified by '123';
2）查看用户
mysql> select user,host from mysql.user;
3）删除用户
mysql> drop user oldboy@‘10.0.0.%’;
4）修改密码
1.mysqladmin -uroot -p123 password '1'
2.update mysql.user set password=password('123') where user='root' and host='localhost';
3.set password=password('1'); 修改当前用户的密码
4.grant all on *.* to 'root'@'localhost' identified by '123';
5）忘记root用户密码
1.停止MySQL
[root@db01 data]# /etc/init.d/mysqld stop
2.跳过授权表,安全启动
[root@db01 data]# mysqld_safe --skip-grant-tables --skip-networking &
3.连接数据库
[root@db01 data]# mysql
4.update修改密码
mysql> update mysql.user set password=password('123456789') where user='root' and host='localhost';
5.刷新授权表
mysql> flush privileges;
6.重启数据库
[root@db01 data]# /etc/init.d/mysqld restart
7.使用新密码链接数据库
[root@db01 data]# mysql -uroot -p567
4.权限管理
#授权命令
grant all privileges on *.* to oldboy@'10.0.0.%' identified by '123';
grant all on *.* to oldboy@'10.0.0.%' identified by '123';
1）作用对象：所有库所有表
grant all privileges on *.*
grant select,update,insert on mysql.* 单库
grant select,update,insert on mysql.user 单表
#企业里称单列授权为 脱敏：脱离敏感信息
grant select(user,host) on mysql.user to ptuser@'%' identified by '123'; 单列级别
#举例：（相亲网站）
grant select on mysql.user to vip@'%' identified by '123';
grant select(user) on mysql.user to diaosi@'%' identified by '123';
2）在企业中权限的设定
#开发人员说：请给我开一个用户
#1.首先进行沟通
1.你需要对哪些库、表进行操作
2.你从哪里连接过来
3.用户名有没有要求
4.密码要求
5.你要使用多长时间
6.发邮件
#2.一般给开发创建用户权限
grant select,update,delete,insert on *.* to oldboy@’10.0.0.%’ identified by ‘123’;
#建议delete权限也不给
#3.注意：开发想要root用户，打死也不能给，这个申请到谁那里批准，也不给，让老大给
5.实践思考
1）准备数据库
#创建wordpress数据库
create database wordpress;
#使用wordpress库
use wordpress;
#创建t1、t2表
create table t1 (id int);
create table t2 (id int);
#创建blog库
create database blog;
#使用blog库
use blog;
#创建t1表
create table tb1 (id int);
2）授权
#给wordpress@'10.0.0.5%'用户查询所有库,所有表的权限,密码是 123
1、grant select on *.* to wordpress@’10.0.0.5%’ identified by ‘123’;
#给wordpress@'10.0.0.5%'用户插入,删除,修改 针对wordpress库下的所有表 密码是123
2、grant insert,delete,update on wordpress.* to wordpress@’10.0.0.5%’ identified by ‘123’;
#给wordpress@'10.0.0.5%'用户所有权限针对wordpress库下的t1表 密码是123
3、grant all on wordpress.t1 to wordpress@’10.0.0.5%’ identified by ‘123’;
3）问题
一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，
1、对t1表的管理能力？ all
2、对t2表的管理能力？ select insert,delete,update 增删改查
3、对tb1表的管理能力? select
4）权限总结
1、如果在不同级别都包含某个表的管理能力时，权限是相加关系。
2、但是我们不推荐在多级别定义重复权限。
3、最常用的权限设定方式是单库级别授权，即：wordpress.*
4、如果涉及到用户的敏感信息,需要做脱敏,单列级别授权 即:select(列名)
六、mysql连接管理
1.连接工具
1）mysql自带的连接命令 mysql
mysql
#常见的特定于客户机的连接选项：
-u: 指定用户 mysql -uroot
-p: 指定密码 mysql -uroot -p567
-h: 指定主机域 mysql -uroot -p567 -h127.0.0.1
-P: 指定端口 mysql -uroot -p567 -h127.0.0.1 -P3307
-S: 指定socket文件 mysql -uroot -p567 -S /tmp/mysql.sock
-e: 指定SQL语句(库外执行SQL语句) mysql -uroot -p567 -e "show databases;"
#--protocol: 指定连接方式 mysql --protocol=TCP --protocol=socket
2）第三方的连接工具
1.sqlyog
2.navicat
#注意：数据库是没有外网的，想连接可以使用通道
3.应用程序连接MySQL
#注意：需要加载对应语言程序的API
2.连接方式
1) socket连接
mysql -uroot -poldboy123 -S /service/mysql/tmp/mysql.sock
mysql -uroot -poldboy123
2) TCP/IP
mysql -uroot -poldboy123 -h10.0.0.51 -P3306
七、MySQL启动关闭流程
1.启动数据库
/etc/init.d/mysqld start ------> mysql.server ------> mysqld_safe ------> mysqld
systemctl start mysql ------> mysqld_safe ------> mysqld
mysqld_safe --defaults-file=/etc/my.cnf ------> mysqld_safe ------>
2.停止数据库
/etc/init.d/mysqld stop
systemctl stop mysqld
mysqladmin -uroot -p123 shutdown
#不建议使用
kill -9 pid
killall mysqld
pkill mysqld
#出现问题：
八、MySQL实例初始化配置
1.初始化配置文件的作用
1.预编译：cmake去指定，硬编码到程序当中去
2.在命令行设定启动初始化配置
--skip-grant-tables
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my,cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err
3.初始化配置文件（/etc/my.cnf）
2.配置文件读取顺序
1）读取顺序
/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
defaults-extra-file （类似include）
~/.my.cnf
2）生效顺序
~/.my.cnf
defaults-extra-file （类似include）
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
/etc/mysql/my.cnf
/etc/my.cnf
3）生效顺序验证
#配置/etc/my.cnf
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
#配置/etc/mysql/my.cnf
[root@db01 ~]# mkdir /etc/mysql
[root@db01 ~]# vim /etc/mysql/my.cnf
[mysqld]
server_id=2
#配置$basedir/my.cnf
[root@db01 ~]# vim /service/mysql/my.cnf
[mysqld]
server_id=3
#配置~/my.cnf
[root@db01 ~]# vim ~/.my.cnf
[mysqld]
server_id=4
#重启数据库（注意这里不能使用systemctl restart mysql重启）
[root@db01 ~]# systemctl stop mysql
[root@db01 ~]# /etc/init.d/mysqld start
#查看server_id
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 4 |
+---------------+-------+
1 row in set (0.00 sec)
3.思考参数执行优先级
#cmake：
socket=/service/mysql/tmp/mysql.sock
#命令行：
--socket=/tmp/mysql.sock
#配置文件：
vim /etc/my.cnf
[mysqld]
socket=/opt/mysql.sock
#default参数：
--defaults-file=/tmp/a.txt
vim /tmp/a.txt
[mysqld]
socket=/tmp/test.sock
#启动测试
mysql_safe --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock
优先级测试结论：
1、命令行
2、defaults-file
3、配置文件
4、预编译
命令行 >> defaults-file >> ~/.my.cnf >> defaults-extra-file >> $basedir/my.cnf >> /etc/mysql/my.cnf >> /etc/my.cnf >>
4.配置文件的使用
1）mysql配置文件的作用
1.影响服务端的启动（mysqld）
2.影响客户端的连接
2）配置连接数据库可以不输入密码
[root@db01 scripts]# vim /etc/my.cnf
#最下面添加
[client]
user=root
password=123
#不需要重启和输入密码就可以直接连数据库
3）修改配置的影响
1.客户端程序也是受到配置文件的影响 [mysql] && [client]
2.修改完客户端的配置,不需要重启
3.修改完服务端的配置,必须重启,才能生效 [mysqld] && [server]