MySQL数据读写分离、MySQL性能调优、MySQL多实例

最新推荐文章于 2024-03-04 19:52:26 发布

原创最新推荐文章于 2024-03-04 19:52:26 发布 · 594 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql数据的读写分离 #SQL性能调优 #MYSQL多实例

MYSQL 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了MySQL数据读写分离的原理和实现，包括配置读写分离结构、授权用户以及测试读写功能。同时，讨论了MySQL多实例的优势，并提供了在一台主机上安装和管理多个数据库服务器的步骤。此外，还涵盖了MySQL性能调优的理论，如硬件优化、参数调整和SQL查询优化，以及如何监控和分析MySQL的日志。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

读写分离，性能调优

 mysql数据读写分离:
把客户端查询数据的请求分别给不同的数据库服务器处理

 读写分离的原理
• 多台Mysql服务器
– 分别提供读、写服务,均衡流量
– 通过主从复制保持数据一致性

• 由 MySQL 代理面向客户端
– 收到 SQL 写请求时,交给服务器 A 处理
– 收到 SQL 读请求时,交给服务器 B 处理
– 具体区分策略由服务设置

 构建读写分离
基本构建思路

已搭建好 MySQL 主从复制
– 基于上一个实验的结果
– 其中 Slave 为只读
添加一台 MySQL 代理服务器
– 部署 / 启用 maxscale
客户端通过代理主机访问 MySQL 数据库
– 访问代理服务器

 配置MYSQL数据读写分离结构
1 配置MYSQL 一主一从同步结构：要求把51配置为52的从库

2.准备代理服务器
下载maxscale-2.1.2-1.rhel.7.x86_64.rpm软件到主机53
给53主机其名 proxy53，方便管理
停止MYSQL数据库服务
3.配置代理服务器主机53
a) 装包（提供命令max[两次tab键]）

[root@mysql53 ~]# rpm -ivh  maxscale-2.1.2-1.rhel.7.x86_64.rpm

b) 修改配置文件

[root@mysql53 ~]# cp  /etc/maxscale.cnf /root/    //备份配置文件
[root@mysql53 ~]# vim /etc/maxscale.cnf   //进入修改配置文件
 #服务启动之后线程数量
 9 [maxscale]    //配置项名称
 10 threads=auto   //根据CPU的核数指定线程数量
#定义数据库服务器的名称和ip地址
#定义第1台 
 18 [server1]
 19 type=server
 20 address=192.168.4.51 
 21 port=3306
 22 protocol=MySQLBackend
#定义第2台
24 [server2]
 25 type=server
 26 address=192.168.4.52
 27 port=3306
 28 protocol=MySQLBackend
#定义代理服务器启动之后要监控的数据库服务器，以及连接的用户名和密码
 36 [MySQL Monitor]
 37 type=monitor
 38 module=mysqlmon
 39 servers=server1,server2   //服务器列表
 40 user=scalemon   //定义监控时使用的用户名（自定义）
 41 passwd=123asd...A   //定义监控时的用户名密码（自定义）
 42 monitor_interval=10000
#不定义只读服务器
 53 #[Read-Only Service]
 54 #type=service
 55 #router=readconnroute
 56 #servers=server1
 57 #user=myuser
 58 #passwd=mypwd
 59 #router_options=slave
#定义读写分离服务
 64 [Read-Write Service]
65 type=service
66 router=readwritesplit
67 servers=server1,server2    //数苦苦服务器列表
68 user=maxscale       //定义客户端连接数据库时使用的用户名
69 passwd=123asd...A     //定义客户端连接数据库时使用的用户名密码
70 max_slave_connections=100%
#定义管理服务
76 [MaxAdmin Service]
77 type=service
78 router=cli
#不定义只读服务器
86 #[Read-Only Listener]
87 #type=listener
88 #service=Read-Only Service
89 #protocol=MySQLClient
90 #port=4008
#定义读写分离默认使用的端口号
92 [Read-Write Listener]
93 type=listener
94 service=Read-Write Service
95 protocol=MySQLClient
96 port=4006
#管理服务使用的端口号
98 [MaxAdmin Listener]
99 type=listener
100 service=MaxAdmin Service
101 protocol=maxscaled
102 socket=default
103 port=4016

c) 根据配置文件的设置，在数据库服务器上添加对应的授权用户
对应的授权用户scalemon 和 maxscale
在主库52 做用户授权，51主机会自动把授权同步到本机

授权监控用户

mysql> grant  replication slave,replication  client  on *.*  to scalemon@"%" \
	> identified by  '123asd...A';

授权路由用户

mysql> grant select on mysql.*  to maxscale@"%"  identified  by '123asd...A';

授权对数据有访问权限的用户

mysql> create database db2;
mysql> create table db2.a(id int);
mysql> grant select,insert on db2.* to webuser@"%" identified by "123asd...A";

d) 启动服务并查看服务信息

	[root@mysql53 ~]# maxscale -f /etc/maxscale.cnf
	[root@mysql53 ~]# ss -natpul  | grep 4016
	[root@mysql53 ~]# ss -natpul  | grep 4006

e) 访问管理服务查看配置信息

[root@mysql53 ~]# maxadmin  -uadmin -pmariadb -P4016

f) 测试读写分离配置：在客户端连接代理服务器，访问数据，能够实现数据读写分离功能
在客户端50主机上连接53，在51从库上插入数据，分别在52、51和50上查看，会发现51和50上有插入的数据，在50上插入数据，会在51和52上同时有数据

[root@client50 ~]# mysql -uwebuser -h192.168.4.53  -P4006 -p123asd...A
52:	mysql> insert into db2.a  values(123);   //在52上插入数据，会同步到51
50:	mysql> select * from db2.a;				//在50上查看
51:	mysql> insert into db2.a values(456);  //在51上插入数据，不会同步到52
52:	mysql> select * from db2.a;  		 //在52上查看不到在51上插入的数据
50:	mysql> select * from db2.a;			//在50上可以查看到在51上插入的数据
50:	mysql> insert into db2.a   values(888); 	 //在50上插入数据
51:	mysql> select * from db2.a;  		//在51上能看到插入的数据
52:	mysql> select * from db2.a; 	 	//在52上能看到在51上插入的数据

将51上slave服务停止，在50上访问的所有操作均交给52执行，不影响访问和写入，当服务启动时，会自动同步在服务器宕机期间产生的数据；将数据库服务停止，不影响访问和写入
将52数据库服务停止，只是不同步数据到从库，在50上访问不影响访问和写入

 mysql多实例
优点：节约运维成本
提高硬件利用率
在主机50上做多实例实验
下载软件mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz到主机50里

多实例：在1台服务上运行多个数据库服务器
 1. 停止其他软件提供的MYSQL服务

[root@client50 ~]# systemctl stop mysqld

 2. 安装提供多实例服务的MYSQL软件包

[root@client50 ~]# tar -zxvf /root/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz 
[root@client50 ~]# mv  /root/mysql-5.7.20-linux-glibc2.12-x86_64/  /usr/local/mysql
[root@client50 ~]# ls /usr/local/mysql/

 3. 创建主配置文件

[root@client50 ~]# mv /etc/my.cnf{,.bak}
[root@client50 ~]# vim /etc/my.cnf
	[mysqld_multi]        //启用多实例
	mysqld=/usr/local/mysql/bin/mysqld_safe			//指定进程文件路径
	mysqladmin=/usr/local/mysql/bin/mysqladmin		//指定管理命令路径
	user=root				//指定进程用户

	[mysqld1]   //实例进程名称，1表示示例编号
	datadir=/datadir3307	//数据库目录，要手动创建
	socket=/datadir3307/mysql3307.sock		//指定socket文件的路径和名称
	port=3307		//端口号
	log-error=/datadir3307/mysqld3307.log		//错误日志文件
	pid-file=/datadir3307/mysqld3307.pid		//指定pid号文件位置

	[mysqld2]
	datadir=/datadir3308
	socket=/datadir3308/mysql3308.sock
	port=3308
	log-error=/datadir3308/mysqld3308.log
	pid-file=/datadir3308/mysqld3308.pid
	[root@client50 ~]# mkdir /datadir3307
	[root@client50 ~]# mkdir /datadir3308

 4. 启动多实例服务

[root@client50 ~]# /usr/local/mysql/bin/mysqld_multi  start 1
..........
2018-10-23T09:16:25.201603Z 1 [Note] A temporary password is generated for
root@localhost: sLcw?E!Hq8Bi-------->初始密码

 5. 访问多实例服务

[root@client50 ~]# ss -natpul | grep mysqld
[root@client50 ~]# /usr/local/mysql/bin/mysql -uroot -p'sLcw?E!Hq8Bi' -S /datadir3307/mysql3307.sock
修改登陆密码：
mysql> alter user user()  identified  by  '123456';
使用修改后的密码登陆
[root@client50 ~]# /usr/local/mysql/bin/mysql -uroot -p123456 -S  /datadir3307/mysql3307.sock

建库、建表

mysql> show databases;
mysql> create database db2;

查看数据库目录文件

[root@client50 ~]# ls /datadir3307/

 6. 停止多实例服务

[root@client50 ~]# /usr/local/mysql/bin/mysqld_multi --user=root --password=123456 stop 1
[root@client50 ~]# ss -natpul | grep mysqld

 mysql性能调优（理论）
 提高MYSQL系统的性能、响应速度
— 替换有问题的硬件（CPU/磁盘/内存等）
— 服务程序的运行参数调整
— 对SQL查询进行优化
【搭建监控服务器：监控服务的硬件使用率CPU 内存存储】
MYSQL体系结构

MYSQL 执行流程

cpu查看方法 uptime

数据库服务运行参数：
并发及连接控制

查看全局变量信息

mysql> show variables;

查看连接信息

mysql> show variables like "%connect%";

查看当前已使用的连接数（最大连接数）

mysql> show variables like "max_connections";
mysql> set global  max_connections = 300;（设置最大连接数）

查看当前连接状态（最大连接数）

mysql> show  global status like "%connect%";
mysql> show global status like "Max_used_connections";

查看当前连接信息

mysql> show processlist;

查看等待连接超时时间

mysql> show variables like "connect_timeout";

查看等待关闭连接的不活动超时秒数

mysql> show  variables  like "wait_timeout";

缓存参数控制
缓冲区、线程数量、开表数量

key_buffer_size=8M
当Key_reads / Key_read_request较低时可适当加大此缓存值

mysql>show global status like  “key_read%”;

mysql>show variables like “key_buffer_size”;

sort_buffer_size=256K
增大此值可提高ORDER和GROUP的速度

mysql>show variables like “sort_buffer_size”;

查看表记录读取缓存
此缓存值影响SQL查询的响应速度

mysql>show variables like  “read_%_size”;

查看可重用线程数

mysql>show variables like “thread_%_size”;

查看当前的线程重用状态

msyql>show global status like “threads_%”;

查看已打开、打开过多少个表

mysql>show global status like  “open%tables”;

查看可缓存多少个打开的表

mysql>show variabels like “table_open_cache”;

 SQL查询优化
 MySQL日志类型
常用日志种类及选项

 优化SQL查询
记录慢查询

查看慢查询日志
使用mysqldumpslow工具

[root@client50 ~]# mysqldumpslow /var/lib/mysql/mysql-slow.log

 关于缓存的查询
查看缓存的大小

msyql>show variables like  “query_cache%”;

query_cache_limit        			 //查询结果超过设定值不允许存入查询缓存        
query_cache_min_res_unit      		//查询缓存的最小存储单元 
query_cache_size        			 //查询缓存的大小     
query_cache_type       				 //查询缓存类型      
query_cache_wlock_invalidate   		//查询缓存写锁  《针对Myisam存储引擎有效》

查询缓存的最小存储单元值小可最大化利用存储空间，但会导致查询速度变慢；此值大可提高查询速度，但会导致浪费存储空间
查看当前的缓存统计

Qcache_hits						//查询缓存命中率

查询缓存命中率过低说明查询缓存空间过小，可适当调大查询缓存空间大小

Qcache_inserts						//查询请求总数
Qcache_hits      					//查询缓存的命中率
Qcache_inserts   					//查询请求总数