@mysql 使用配置及多实例部署

0|1文章目录

1|0一、mysqld服务程序构成

mysqld是一个守护进程

1|11.连接层

1.验证用户的身份，用户名密码是否匹配
2.提供两种连接方式（TCP/IP连接、socket连接）
3.连接层提供了一个与sql层交互的线程

1|22.SQL层

1.接收连接层传过来的SQL语句
2.验证执行的SQL语法
3.验证SQL的语义（DDL,DML,DQL,DCL）
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器传来的执行计划选择最优的一条执行
6.执行器：将最优的一条执行
	6.1 与存储引擎层建立交互的线程
	6.2 将要执行的sql发给存储引擎层
7.如果有缓存，则走缓存
8.记录日志（binlog）

1|33.存储引擎层

1.接收SQL层传来的语句
2.与磁盘交互，获取数据，返回给sql层
3.建立与sql层交互的线程

2|0二、mysql的多实例

【多实例部署多结构多版本】

#NGINX多实例就是多个配置文件
#mysql多实例：
	1.多个数据目录
	2.多个端口
	3.多个socket文件
	4.多个日志文件

2|11.创建多个数据目录

[root@db01 ~]# mkdir /data/{3307,3308,3309} -p

2|22.准备多个配置文件

[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

2|33.初始化多套数据目录

[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

2|44.授权目录

[root@db01 scripts]# chown -R mysql.mysql /data

2|55.启动数据库

[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 &

2|66.检查启动

[root@db01 scripts]# netstat -lntup|grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      25550/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      25722/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      25894/mysqld

2|77.多实例设置密码

[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'

2|88.多实例验证

[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     |
+---------------+-------+

2|99.连接多实例小技巧

[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*

3|0三、数据库多实例主从

3|11.主库创建主从复制用户

mysql> grant replication slave on *.* to rep@'127.0.0.1' identified by '123';
Query OK, 0 rows affected (0.00 sec)

3|22.主库查看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|33.从库配置主库信息

#从库需要知道：我的主库是谁?主库的主从复制用户是谁?主从复制用户的密码?端口?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)

3|44.从库开启主从复制

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

3|55.检查主从复制状态

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

3|66.主从复制状态错误

1|01）如果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

1|02）如果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，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步

1|03）连接时有反向解析的问题

#连接时地址反向解析了
[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

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

4|11.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

4|22.初始化时使用的命令不一样了

#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

5|0五、mysql用户权限管理

5|11.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

5|22.在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)

5|33.用户管理

1|01）创建用户

mysql> create user oldboy@'10.0.0.%' identified by '123';

1|02）查看用户

mysql> select user,host from mysql.user;

1|03）删除用户

mysql> drop user oldboy@‘10.0.0.%’;

1|04）修改密码

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';

1|05）忘记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

5|44.权限管理

#授权命令
grant all privileges on *.* to oldboy@'10.0.0.%' identified by '123';
grant    all       on    *.*             to  oldboy@'10.0.0.%' identified  by '123';
授权SQL  所有权限	  在	 所有库.所有表 上面	 给  用户名@主机域        密码    为    123；

1|01）作用对象：所有库所有表

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';

1|02）在企业中权限的设定

#开发人员说：请给我开一个用户

#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|55.实践思考

1|01）准备数据库

#创建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);

1|02）授权

#给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’;

1|03）问题

一个客户端程序使用wordpress用户登陆到10.0.0.51的MySQL后，
	1、对t1表的管理能力？		all
	2、对t2表的管理能力？		select insert,delete,update   增删改查
	3、对tb1表的管理能力?		 select

1|04）权限总结

1、如果在不同级别都包含某个表的管理能力时，权限是相加关系。
2、但是我们不推荐在多级别定义重复权限。
3、最常用的权限设定方式是单库级别授权，即：wordpress.*
4、如果涉及到用户的敏感信息,需要做脱敏,单列级别授权 即:select(列名) 单库.单表

6|0六、mysql连接管理

6|11.连接工具

1|01）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

1|02）第三方的连接工具

1.sqlyog
2.navicat
#注意：数据库是没有外网的，想连接可以使用通道
3.应用程序连接MySQL
#注意：需要加载对应语言程序的API

6|22.连接方式

1|01) socket连接

mysql -uroot -poldboy123 -S /service/mysql/tmp/mysql.sock
mysql -uroot -poldboy123

1|02) TCP/IP

mysql -uroot -poldboy123 -h10.0.0.51 -P3306

7|0七、MySQL启动关闭流程

7|11.启动数据库

/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

7|22.停止数据库

/etc/init.d/mysqld stop
systemctl stop mysqld
mysqladmin -uroot -p123 shutdown

#不建议使用
kill -9 pid
killall mysqld
pkill mysqld
#出现问题：
1.如果在业务繁忙的情况下，数据库不会释放pid和sock文件
2.号称可以达到和Oracle一样的安全性，但是并不能100%达到
3.在业务繁忙的情况下，丢数据（补救措施，高可用）

8|0八、MySQL实例初始化配置

8|11.初始化配置文件的作用

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）

8|22.配置文件读取顺序

1|01）读取顺序

/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
defaults-extra-file （类似include）
~/.my.cnf

1|02）生效顺序

~/.my.cnf
defaults-extra-file （类似include）
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
/etc/mysql/my.cnf
/etc/my.cnf

1|03）生效顺序验证

#配置/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)

8|33.思考参数执行优先级

#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

8|44.配置文件的使用

1|01）mysql配置文件的作用

1.影响服务端的启动（mysqld）
2.影响客户端的连接

1|02）配置连接数据库可以不输入密码

[root@db01 scripts]# vim /etc/my.cnf
#最下面添加
[client]
user=root
password=123

#不需要重启和输入密码就可以直接连数据库

1|03）修改配置的影响

1.客户端程序也是受到配置文件的影响  [mysql] && [client]
2.修改完客户端的配置,不需要重启
3.修改完服务端的配置,必须重启,才能生效	[mysqld] && [server]

__EOF__

本文作者：ଲ小何才露煎煎饺
本文链接：https://www.cnblogs.com/zeny/p/15121491.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！

posted @ 2021-07-06 10:29 ଲ小何才露煎煎饺阅读(48) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

公告

@mysql 使用配置及多实例部署

发表于 2021-07-06 10:29阅读：48评论：0推荐：0

关注

跳至底部

昵称： ଲ小何才露煎煎饺
园龄： 4年1个月
粉丝： 14
关注： 1

博客一笑奈何

一笑奈何博客

@mysql 使用配置及多实例部署

0|1文章目录

1|0一、mysqld服务程序构成

1|11.连接层

1|22.SQL层

1|33.存储引擎层

2|0二、mysql的多实例

2|11.创建多个数据目录

2|22.准备多个配置文件

2|33.初始化多套数据目录

2|44.授权目录

2|55.启动数据库

2|66.检查启动

2|77.多实例设置密码

2|88.多实例验证

2|99.连接多实例小技巧

3|0三、数据库多实例主从

3|11.主库创建主从复制用户

3|22.主库查看binlog名和位置

3|33.从库配置主库信息

3|44.从库开启主从复制

3|55.检查主从复制状态

3|66.主从复制状态错误

1|01）如果IO线程是NO

1|02）如果sql线程是NO

1|03）连接时有反向解析的问题

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

4|11.cmake的时候加入了bostorg

4|22.初始化时 使用的命令不一样了

5|0五、mysql用户权限管理

5|11.linux和mysql用户对比

5|22.在MySQL中,用户是如何定义的

5|33.用户管理

1|01）创建用户

1|02）查看用户

1|03）删除用户

1|04）修改密码

1|05）忘记root用户密码

5|44.权限管理

1|01）作用对象：所有库所有表

1|02）在企业中权限的设定

5|55.实践思考

1|01）准备数据库

1|02）授权

1|03）问题

1|04）权限总结

6|0六、mysql连接管理

6|11.连接工具

1|01）mysql自带的连接命令 mysql

1|02）第三方的连接工具

6|22.连接方式

1|01) socket连接

1|02) TCP/IP

7|0七、MySQL启动关闭流程

7|11.启动数据库

7|22.停止数据库

8|0八、MySQL实例初始化配置

8|11.初始化配置文件的作用

8|22.配置文件读取顺序

1|01）读取顺序

1|02）生效顺序

1|03）生效顺序验证

8|33.思考参数执行优先级

8|44.配置文件的使用

1|01）mysql配置文件的作用

1|02）配置连接数据库可以不输入密码

1|03）修改配置的影响

公告

ଲ小何才露煎煎饺

@mysql 使用配置及多实例部署

搜索

常用链接

随笔档案

文章分类

文章档案

阅读排行榜

评论排行榜

推荐排行榜

4|22.初始化时使用的命令不一样了