文章目录
MySQL数据库权限与主从、以及配置
一、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多实例：
多个数据目录
多个端口
多个socket文件
多个日志文件
1、创建多个数据目录 （源码包数据机器安装–> Source Code）
[root@db02 ~]# mkdir /data/{3307,3308,3309} -p
2、准备多个配置文件
[root@db02 data]# vim /data/3307/my.cnf
[mysqld]
basedir=/service/mysql
# basedir=/usr/local/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@db02 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@db02 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@db02 3307]# chown -R mysql.mysql /data/
4、初始化多套数据目录
[root@db02 scripts]# ./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3307/data
[root@db02 scripts]# ./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3308/data
[root@db02 scripts]# ./mysql_install_db --defaults-file=/data/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/data/3309/data
# 显示OK 数据库就初始化OK
#使用tree可以查看
[root@db02 scripts]# tree -L 3 /data/
/data/
├── 3307
│ ├── data
│ └── my.cnf
├── 3308
│ ├── data
│ └── my.cnf
└── 3309
├── data
└── my.cnf
6 directories, 3 files
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、启动数据库报错解决
出现此报错是因为swap分区空间不足导致！ # 真实环境中不需要添加，无需担心swap内存不足
1.运行命令出现如下提示
[root@db01 scripts]# mysqld_safe --defaults-file=/data/3309/my.cnf & 失败！
“mysqld_safe mysqld from pid file /data/3309/data/web03. exepid” # 若出现此提示
# 错误原因：是由于swap分区可用内存不够了，只需增加一下swap空间内存就可以了！
2.查看错误日志
[root@db01 ~]# mysqld_safe --defaults-file=/data/3309/my.cnf & 失败！
2021-02-25 04:39:03 4462 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
# 无法为缓冲池分配内存
2021-02-25 04:39:03 4462 [ERROR] Plugin 'InnoDB' init function returned error.
# 插件'InnoDB' init函数返回错误。
2021-02-25 04:39:03 4462 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
# InnoDB插件作为存储引擎注册失败。
2021-02-25 04:39:03 4462 [ERROR] Unknown/unsupported storage engine: InnoDB
# 未知/不支持的存储引擎:InnoDB
2021-02-25 04:39:03 4462 [ERROR] Aborting
3.解决方法如下：
sudo dd if=/dev/zero of=/swapfiles bs=1M count=1024 # 吐一个如此大的临时交换分区文件
sudo mkswap /swapfiles # 制作临时swap交换分区
sudo swapon /swapfiles # 开启临时swap交换分区
mysqld_safe --defaults-file=/data/3309/my.cnf & # 再次启动测试，成功！
PS：详情参考 https://www.copylian.com/technology/96.html 解决方法参考此文档！
7、检查启动
[root@db01 scripts]# netstat -lntup|grep 33*
tcp6 0 0 :::3306 :::* LISTEN 25477/mysqld
tcp6 0 0 :::3307 :::* LISTEN 25550/mysqld
tcp6 0 0 :::3308 :::* LISTEN 25722/mysqld
tcp6 0 0 :::3309 :::* LISTEN 25894/mysqld
8、多实例设置密码
[root@db02 scripts]# mysqladmin -uroot -S /data/3307/mysql.sock password '3307'
[root@db02 scripts]# mysqladmin -uroot -S /data/3308/mysql.sock password '3308'
[root@db02 scripts]# mysqladmin -uroot -S /data/3309/mysql.sock password '3309'
# 方式一
[root@db02 ~]# mysqladmin -uroot password '123' # 不指定端口的情况下默认是3306
# 方式二
mysql> update mysql.user set PASSWORD=password('123') where user='root';
#在数据库内直接编辑user表来指定用户设置密码
# 方式三
mysql> set password for root@localhost = password('123');
#在数据库内直接用set password命令指定用户设置密码
# 方式四
mysql>grant all on *.* to 'root'@'localhost' identified by '123'；
#在数据库内用授权密令直接给用户加上密码
mysql>flush privileges; #授权后要刷新授权
9、多实例验证
[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 |
+---------------+-------+
10、连接多实例小技巧
[root@db02 scripts]# vim /usr/bin/mysql3309
mysql -uroot -p3309 -S /data/3309/mysql.sock
[root@db02 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@db02 scripts]# chmod +x /usr/bin/mysql*
# 连接数据库
[root@db02 scripts]# mysql -h 127.0.0.1 -P 3307 -uroot -p # 大P指定端口 #小p指定密码
[root@db02 scripts]# mysql -uroot -p3309 -S /data/3309/mysql.sock
三、数据库多实例主从
| 主库 | 从库 |
|---|---|
| db01 | db02 |
主库db01操作：
1.创建主从复制用户
[root@db01 ~]# mysql -uroot -p123
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)
从库db02操作：
3.从库配置主库的信息
[root@db02 ~]# mysql -uroot -p123
#从库需要知道：我的主库是谁?主库的主从复制用户是谁?主从复制用户的密码?端口?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=3306;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
4.从库开启主从复制
[root@db02 ~]#
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
5.检查主从复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 467
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.主从复制状态错误
1）如果IO线程是NO
1.检测网络:
[root@db01 data]# ping 127.0.0.1
2.检测端口:
[root@db01 data]# telnet 127.0.0.1 3307
3.检测账号,密码:
mysql -urep -p123 -h127.0.0.1 -P 3307
2）如果sql线程是NO
1.确定数据库主从之前数据同步
2.确定主库有的库和表从库也有
#解决方法：
方法一：数据要求不严格的情况，忽略错误后，继续同步
解决：
stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。
方式二：重新做主从，完全同步
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]
skip-name-resolve
四、MySQL5.6与MySQL5.7安装的区别
1.cmake的时候加入了bostorg
建议大家使用二进制的包安装，已经过了cmake阶段，已经把bostorg装好了
[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
密码在 /var/log/mysqld.log #可以过滤password或者temp
五、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 ------> mysqld
2.停止数据库
/etc/init.d/mysqld stop
systemctl stop mysqld
mysqladmin -uroot -p123 shutdown
mysqladmin --socket/data/3307/mysql.sock -uroot -p123 shutdown #多实例指定端口号停止数据库
#不建议使用
kill -9 pid
killall mysqld
pkill mysqld
#出现问题：
1.如果在业务繁忙的情况下，数据库不会释放pid和sock文件
2.号称可以达到和Oracle一样的安全性，但是并不能100%达到
3.在业务繁忙的情况下，丢数据（补救措施，高可用）
七、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 >> cmake
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]
八、mysql相关命令
1.mysql连接后命令行快捷命令
\c：中断当前输入语句
\r：重新连接到服务器
\d：设置语句sql结束符 mysql> \d ]
\e：编辑命令 输出输入的内容 echo
\p：打印当前命令并执行
\G：垂直显示结果
\q：退出mysql 等于 quit exit
\g：表示结束 等于 ; 的作用
\h：显示此帮助 等于 help #help还可以查看命令语法 help create database;
\t：不写入outfile
\T：将所有内容附加到给定的输出文件中（只支持本次会话） \T /tmp/a.log
#\n：禁用寻呼机，打印到标准输出。
#\P：设置寻呼机[到寻呼机]。通过寻呼机打印查询结果。
\R：更改mysql提示符 \R mysql>>>
\.：执行一个sql文件 等于 source
\s：从服务器获取状态信息 等于 status
\u：切换数据库 等于 use #查看当前所在数据库select database();
\C：切换到另一个字符集 一般不使用
\W：在每个语句后显示警告
\w：不在每个语句后显示警告
1）help命令
mysql> help
mysql> help contents help 用法
mysql> help select
mysql> help create
mysql> help create user
mysql> help status
mysql> help show
2.客户端mysqladmin命令
1.修改密码,设置密码:password
[root@db01 ~]# mysqladmin -uroot -p旧密码 password '新密码'
2.关闭MySQL服务:shutdown
[root@db01 ~]# mysqladmin -uroot -p密码 -S socket文件 shutdown
3.库外建库:create
[root@db01 ~]# mysqladmin -uroot -p密码 create mm
[root@db01 ~]# mysql -uroot -p123 -e 'create database mm'
4.库外删除数据库:drop
[root@db01 ~]# mysqladmin -uroot -p123 drop mm
Do you really want to drop the 'mm' database [y/N] y
Database "mm" dropped
5.查看配置文件所有的默认参数:variables
[root@db01 ~]# mysqladmin -uroot -p123 variables
[root@db01 ~]# mysqladmin -uroot -p123 variables | grep server_id
6.检测MySQL进程是否存活:ping
[root@db01 ~]# mysqladmin -uroot -p123 ping
7.查看数据库 慢查询,负载信息:status
[root@db01 ~]# mysqladmin -uroot -p123 status
Uptime MySQL服务器已经运行的秒数
Threads 活跃线程（客户）的数量
Questions 从mysqld启动起来自客户问题的数量 已经发送给服务器的查询的个数
Slow queries 已经超过long_query_time秒的查询数量
Opens mysqld已经打开了多少表
Flush tables flush ..., refresh和reload命令数量
Open tables 现在被打开的表数量
Queries per second avg: 0.046 负载
8.重载授权表,刷新缓存主机:reload，相当于flush privileges
[root@db01 ~]# mysqladmin -uroot -p123 reload
9.刷新binlog日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
542
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
