mysql 读写分离缺点6_6\MySQL 主从同步 , MySQL 读写分离 , MySQL 性能调优

6\MySQL 主从同步 , MySQL 读写分离 , MySQL 性能调优

day06

一,mysql 主从同步

二,数据读写分离

三,MySQL 优化

++++++++++++++++++++++++++++++++

一,mysql 主从同步

1.1 主从同步介绍?从库服务器自动同步主库上数据(被客户端访问的数据库服务器做主库服务器)

1.2 结构5455

systemctl start mysqld systemctl start mysqld

主 master 数据库服务器 从 slave 数据库服务器

1.3 配置主从同步结构?

1.3.1 主库角色主机的配置

1 用户授权

mysql> grant replication slave on . to yaya@"192.168.4.55" identified by "123456";

2 启用 binlog 日志10vim/etc/my.cnf

server_id=54

log-bin=master54

binlog-format="mixed"

:wq

3 重启数据库服务器systemctl stop mysqld

systemctl start mysqld

ls/var/lib/mysql/master54.*

4 查看正在使用 binlog 日志文件.mysql-uroot-p123456

mysql>show master status;

1.3.2 从库角色主机的配置?

1 显示自己是否是从数据库服务器

mysql> show slave status;

2 测试主库授权的用户是否有效#mysql -h192.168.4.54 -uyaya -p123456

mysql>show grants;

mysql>quit

3 修改配置文件指定 server_id#vim /etc/my.cnf

[mysqld]

server_id=55

:wq

#systemctl restart mysqld

4 在本机数据管理员登录指定主库信息.mysql>change master to->master_host="192.168.4.54",

->master_user="yaya",

->master_password="123456",

->master_log_file="master54.000001",

->master_log_pos=154;

QueryOK,

0rows affected,

2warnings(0.35sec)mysql>start slave;

mysql>show slave status\G;

Slave_IO_Running:YesSlave_SQL_Running:Yes

验证主从同步配置:结果

客户端连接主库服务器 产生的新数据,在从角色的主机上都能够查看到.

+++++++++++++++++++++++++++++++

主从工作原理?

IO 线程: 把 master 库的 binlog 日志内容 记录到本机的 relay-binlog 日志里.

SQL 线程:执行本机 relay-binlog 日志里的 sql 命令 把数据写进本机的库.

从角色主机的数据库目录下会多出如下文件:

master.info 存储主数据库的信息

slave55-relay-bin.XXXXXX

中继日志文件 记录本机产生新数据的 sql 命令

slave55-relay-bin.index 中继日志文件索引文件

relay-log.info 记录当前数据库服务器使用的中继日志文件

主从同步配置排错?

IO 线程报错: 从库指定主库的信息错误;

安全限制(firewall selinux)

查看报错信息:

Last_IO_Error: 报错信息

mysql> stop slave;

mysql> change mstart to 选项 = 值;

mysql> start slave;

SQL 线程报错: 执行中继日志里的 sql 命令时,命令操作的库或表在本机不存在.

查看报错信息:

Last_SQL_Error: 报错信息

mysql> stop slave;

在从本机有命令操作的库或表mysql>start slave;

++++++++++++++++++++++++++++

让从数据库临时不同步主库的数据?

mysql> stop slave;

把从库 还原成独立的数据库服务器?#rm -rf master.info slave55-relay-bin.* relay-log.info

#systemctl restart mysqld

mysql>show slave status;

主从同步结构模式?

一主一从

一主多从

主从从

互为主从(主主结构)reset master#清空主的日志

reset slave#只清空日志

reset slave all#需要重新配置 change master

++++++++++++++++++++++++++++++++++++

主从同步常用配置参数?(/etc/my.cnf)

主库角色主机的配置参数

[mysqld]

binlog_do_db = 数据库名,数据库名 #只允许同步的库 binlog_ignore_db = 数据库名,数据库名 #只不允许同步的库

mysql> show master stauts;

从库角色主机的配置参数

[mysqld]

log_slave_updates 级联复制

replicate_do_db = 数据库名,数据库名 #只同步的库

replicate_ignore_db = 数据库名,数据库名 #只不同步的库 relay_log = 文件名 #设置中继日志文件名称

+++++++++++++++++++++++++++++

二,数据读写分离

2.1 读写分离介绍?把客户的查询记录的操作和写数据的操作在不同的数据库服务器上执行.

2.2 为什么要做读写分离? 减轻单台数据库服务器的并发访问压力和提供服务器硬件资源的利用率

2.3 配置数据读写分离 (Maxscale + 主从同步结构)

2.3.1 拓扑结构

client

|

53 maxscale 4010 管理端口 4006 读写分离端口

insert select

写 查

54 55

主 从

2.3.2 配置 MySQL 一主一从同步结构

2.3.3 配置 maxscale (53)

a. 装包

b. 修改配置文件vim/etc/maxscale.cnf

[maxscale]

threads=1

[server1]

type=server

address=192.168.4.54

port=3306

protocol=MySQLBackend

[server2]

type=server

address=192.168.4.55

port=3306

protocol=MySQLBackend

[MySQLMonitor]

type=monitor

module=mysqlmon

servers=server1,server2

user=scalemon# 监控数据库服务服务的运行状态和主从状态

passwd=123456

monitor_interval=10000

[Read-WriteService]

type=service

router=readwritesplit

servers=server1,server2

user=maxscale#检查接收客户端连接请求时,连接的用户名和密码在数据库服务器上是否存在

passwd=123456

max_slave_connections=100%

[MaxAdminService]

type=service

router=cli

[Read-WriteListener]

type=listener

service=Read-WriteService

protocol=MySQLClient

port=4006

[MaxAdminListener]

type=listener

service=MaxAdminService

protocol=maxscaled

socket=default

port=4010

根据配置文件的设置在数据库服务器上添加对应的授权用户.mysql>grant replication slave,

replication client on.to scalemon@'%'identifiedby"123456";

mysql>grantselecton mysql.*to maxscale@'%'identifiedby"123456";

c. 启动服务systemctl stop mysqld

[root@003~]#maxscale-f/etc/maxscale.cnf

[root@003~]#netstat-utnalp|grep:4010

tcp600:::4010:::LISTEN9559/maxscale

[root@003~]#

[root@003~]#netstat-utnalp|grep:4006

tcp600:::4006:::LISTEN9559/maxscale

[root@003~]#netstat-utnalp|grep maxscle

[root@003~]#netstat-utnalp|grep maxscale

tcp00192.168.4.53:52855192.168.4.55:3306ESTABLISHED9559/maxscale

tcp00192.168.4.53:52228192.168.4.54:3306ESTABLISHED9559/maxscale

tcp600:::4010:::LISTEN9559/maxscale

tcp600:::4006:::LISTEN9559/maxscale

[root@003~]#

53 访问本机的管理服务 查看监控主机的状态[root@003~]#maxadmin-P4010-uadmin-pmariadb

MaxScale>list servers

停止 maxscale 服务#ps -C maxscale

#kill -9 pid 号

d. 在客户端测试配置

#ping -c 2 192.168.4.53

d.1 在数据库服务器添加访问数据的用户

mysql> grant all on . to student@'%' identified by "123456";

d.2 访问代理主机#mysql -P4006 -h192.168.4.53 -ustudent -p123456

+++++++++++++++++++++++++++++++++++++

三,MySQL 优化 (优化思路, 优化什么 ,怎么优化)

访问数据时,出结果特别慢.分析可能是由哪些原因导致的.

1 硬件配置低:查看应用设备的使用率 CPU 内存 存储 (硬盘)top0.0wa

sar

uptime I/O

free-m

2 网络带宽: 使用网络测速软件 网速

3 提供数据库服务软件版本低:

查看数据库服务运行时的运行参数配置(常用配置参数)

超时时间

connect_timeout

建立连接时,三次握手的超时时间

wait_timeout 建立连接后,等待断开连接的超时时间mysql>show variables like"connect_timeout";

mysql>show variables like"wait_timeout";

允许重复使用的线程的数量

mysql> show variables like "thread_cache_size";

为所有线程缓存打开表的数量.table_open_cache=2000

mysql>show variables like"table_open_cache";

pc1 T1

pc2 t3 mysqld

pc3 T1

key_buffer-size

sort_buffer_size

read_buffer_sizeread_rnd_buffer_sizenameIndex

selectnamefromuserwherename="zhangsan";

selectname,uidfromuser orderbyuid desc;

select*fromuser;

selectshellfromusergroupbyshell;

查询缓存相关参数设置?

MySQL> show variables like "query_cache%";

查询缓存写锁有效 存储引擎(MYISAM)query_cache_wlock_invalidate|OFF

t1

pc1selectnamefromuserwherename="lucy";

name="lucy" ------> 查询缓存

pc2 select name from user where name="lucy";--> 查询缓存name="lucy"

pc3 update usersetname="lili"wherename="lucy";

query_cache_type0|1|2

0 关闭 不存储

1 开启 无条件存储

2 开启 指定存储才存储

query_cache_limit 1048576 超过 1M 此数不存

query_cache_min_res_unit 4096 最小存储单元 4k

查询查询缓存统计信息?MySQL>showglobalstatus like"qcache%";

Qcache_hits0

Qcache_inserts0

Qcache_lowmem_prunes0

并发连接数:

mysql> show variables like "max_connections";

曾经有过的最大连接数

mysql> show global status like "Max_used_connections";

公式:

曾经有过的最大连接数 / 并发连接数 = 0.85 * 100% = 85%

3000/ X = 0.85

查看参数的值MySQL>show variables like"% 关键字 %";

mysql>show variables like"%password%";

mysql>show variables like"old_passwords";

临时定义 mysql> set [global] 变量名 = 值;

永久定义vim/etc/my.cnf

[mysqld]

变量名 = 值....

:wq

#systemctl restart mysqld

mysql 体系结构?

连接池

sql 接口

分析器

优化器

查询缓存

存储引擎

文件系统

管理工具

MySQL 服务处理查询请求的过程?

4 程序编写的查询数据库的 sql 查询命令不合理 ,导致数据库处理慢

在数据库服务器上启用 "慢查询日志":记录超过指定时间显示查询结果的 sql 命令.(忍受值 3 秒)

5 数据存储架构设置有数据传输瓶颈.

6\MySQL 主从同步 , MySQL 读写分离 , MySQL 性能调优

来源: http://www.bubuko.com/infodetail-2475015.html