linux12 -MYSQL数据库 --＞03数据库权限与主从、以及配置

文章目录

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

posted @ 2021-07-02 12:11 博客之记阅读(101) 评论(0) 编辑收藏举报

刷新页面返回顶部

linux12 -MYSQL数据库 --＞03数据库权限与主从、以及配置

文章目录

MySQL数据库权限与主从、以及配置

一、mysqld服务程序构成

1.连接层

2.SQL层

3.存储引擎层

二、mysql的多实例

1、创建多个数据目录 （源码包数据机器安装–> Source Code）

2、准备多个配置文件

3、授权目录

4、初始化多套数据目录

5、启动数据库

6、启动数据库报错解决

7、检查启动

8、多实例设置密码

9、多实例验证

10、连接多实例小技巧

三、数据库多实例主从

1.创建主从复制用户

2.查看binlog名和位置

3.从库配置主库的信息

4.从库开启主从复制

5.检查主从复制状态

6.主从复制状态错误

1）如果IO线程是NO

2）如果sql线程是NO

3）连接时有反向解析的问题

四、MySQL5.6与MySQL5.7安装的区别

1.cmake的时候加入了bostorg

2.初始化时 使用的命令不一样了

五、mysql连接管理

1.连接工具

1）mysql自带的连接命令 mysql

2）第三方的连接工具

2.连接方式

1) socket连接

2) TCP/IP

六、MySQL启动关闭流程

1.启动数据库

2.停止数据库

七、MySQL实例初始化配置

1.初始化配置文件的作用

2.配置文件读取顺序

1）读取顺序

2）生效顺序

3）生效顺序验证

3.思考参数执行优先级

4.配置文件的使用

1）mysql配置文件的作用

2）配置连接数据库可以不输入密码

3）修改配置的影响

八、mysql相关命令

1.mysql连接后命令行快捷命令

1）help命令

2.客户端mysqladmin命令

1、创建多个数据目录（源码包数据机器安装–> Source Code）

2.初始化时使用的命令不一样了