6\MySQL 主从同步 、 MySQL 读写分离 、 MySQL 性能调优
day06一、mysql主从同步
二、数据读写分离
三、MySQL优化
++++++++++++++++++++++++++++++++
一、mysql主从同步
1.1主从同步介绍？从库服务器自动同步主库上数据（被客户端访问的数据库服务器做主库服务器）
1.2 结构
54 55
systemctl start mysqld systemctl start mysqld
主master数据库服务器 从slave数据库服务器
1.3 配置主从同步结构？
1.3.1主库角色主机的配置
1用户授权
mysql> grantreplicationslave on .toyaya@"192.168.4.55"> 2启用binlog日志
10vim/etc/my.cnf
server_id=54
log-bin=master54
binlog-format="mixed"
：wq
3 重启数据库服务器
11systemctlstop mysqld
12systemctlstart mysqld
13ls /var/lib/mysql/master54.*
4 查看正在使用binlog日志文件。
mysql -uroot -p123456
mysql> showmasterstatus;
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
server_id=55
:wq
#systemctlrestartmysqld
4 在本机数据管理员登录指定主库信息。
mysql> change masterto
-> master_host="192.168.4.54",
-> master_user="yaya",
-> master_password="123456",
-> master_log_file="master54.000001",
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.35 sec)
mysql> start slave;
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_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> stopslave;
mysql> change mstartto 选项=值；
mysql> startslave;
SQL线程报错： 执行中继日志里的sql命令时，命令操作的库或表在本机不存在。
查看报错信息：
Last_SQL_Error: 报错信息
mysql> stopslave;
在从本机有命令操作的库或表
mysql> startslave;
++++++++++++++++++++++++++++
让从数据库临时不同步主库的数据？
mysql> stopslave;
把从库 还原成独立的数据库服务器？
#rm-rf master.infoslave55-relay-bin.*> #systemctlrestartmysqld
mysql> show slavestatus;
主从同步结构模式？
一主一从
一主多从
主从从
互为主从（主主结构）
reset master#清空主的日志
reset slave #只清空日志
reset slave all #需要重新配置change master
++++++++++++++++++++++++++++++++++++
主从同步常用配置参数？（/etc/my.cnf）
主库角色主机的配置参数
binlog_do_db=数据库名，数据库名#只允许同步的库binlog_ignore_db=数据库名，数据库名#只不允许同步的库
mysql> showmaster stauts;
从库角色主机的配置参数
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
9
10 threads=1
18
19 type=server
20 address=192.168.4.54
21 port=3306
22 protocol=MySQLBackend
23
25
26 type=server
27 address=192.168.4.55
28 port=3306
29 protocol=MySQLBackend
38
39 type=monitor
40 module=mysqlmon
41 servers=server1, server2
42 user=scalemon # 监控数据库服务服务的运行状态和主从状态
43 passwd=123456
44 monitor_interval=10000
66
67 type=service
68 router=readwritesplit
69 servers=server1, server2
70 user=maxscale #检查接收客户端连接请求时，连接的用户名和密码在数据库服务器上是否存在
71 passwd=123456
72 max_slave_connections=100%
78
79 type=service
80 router=cli
94
95 type=listener
96 service=Read-Write Service
97 protocol=MySQLClient
98 port=4006
100
101 type=listener
102 service=MaxAdmin Service
103 protocol=maxscaled
104 socket=default
105 port=4010
根据配置文件的设置在数据库服务器上添加对应的授权用户。
mysql> grant replication slave, replication client on . to scalemon@'%' > "123456";
mysql> grant select on mysql.* to maxscale@'%'> c. 启动服务
systemctlstop mysqld
# maxscale -f/etc/maxscale.cnf
# netstat -utnalp| grep :4010
tcp6 0 0 :::4010 ::: LISTEN 9559/maxscale
#
# netstat -utnalp| grep :4006
tcp6 0 0 :::4006 ::: LISTEN 9559/maxscale
# netstat -utnalp| grep maxscle
# netstat -utnalp| grep maxscale
tcp 0 0 192.168.4.53:52855 192.168.4.55:3306 ESTABLISHED 9559/maxscale
tcp 0 0 192.168.4.53:52228 192.168.4.54:3306 ESTABLISHED 9559/maxscale
tcp6 0 0 :::4010 ::: LISTEN 9559/maxscale
tcp6 0 0 :::4006 ::: LISTEN 9559/maxscale
#
53访问本机的管理服务 查看监控主机的状态
# maxadmin -P4010 -uadmin-pmariadb
MaxScale>list servers
停止maxscale 服务
#ps-C maxscale
#kill-9pid号
d.在客户端测试配置
#ping -c 2 192.168.4.53
d.1在数据库服务器添加访问数据的用户
mysql> grant allon .tostudent@'%'> d.2访问代理主机
#mysql-P4006 -h192.168.4.53 -ustudent -p123456
+++++++++++++++++++++++++++++++++++++
三、MySQL优化(优化思路、优化什么 、怎么优化)
访问数据时，出结果特别慢。分析可能是由哪些原因导致的。
1 硬件配置低：查看应用设备的使用率CPU内存 存储(硬盘)
top 0.0 wa
sar
uptime I/O
free-m
2 网络带宽：使用网络测速软件 网速
3 提供数据库服务软件版本低：
查看数据库服务运行时的运行参数配置（常用配置参数）
超时时间
connect_timeout
建立连接时，三次握手的超时时间
wait_timeout 建立连接后，等待断开连接的超时时间
mysql> showvariableslike"connect_timeout";
mysql> showvariableslike"wait_timeout";
允许重复使用的线程的数量
mysql> showvariableslike "thread_cache_size";
为所有线程缓存打开表的数量。
table_open_cache=2000
mysql> showvariableslike "table_open_cache";
pc1 T1
pc2 t3 mysqld
pc3 T1
key_buffer-size
sort_buffer_size
read_buffer_sizeread_rnd_buffer_sizenameIndex
select namefrom user wherename="zhangsan";
select name,uidfrom user orderby uiddesc; 查询缓存
name="lucy"
pc3 updateuserset name="lili" where name="lucy";
query_cache_type 0|1|2
0 关闭 不存储
1 开启 无条件存储
2 开启 指定存储才存储
query_cache_limit 1048576 超过1M此数不存
query_cache_min_res_unit 4096 最小存储单元4k
查询查询缓存统计信息？
MySQL> showglobalstatuslike"qcache%";
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
并发连接数：
mysql> show variables like "max_connections";
曾经有过的最大连接数
mysql> showglobalstatus like "Max_used_connections";
公式：
曾经有过的最大连接数/并发连接数=0.85* 100%=85%
3000/ X= 0.85
查看参数的值
MySQL> showvariableslike"%关键字%";
mysql> show variables like "%password%";
mysql> show variables like "old_passwords";
临时定义 mysql>set变量名=值；
永久定义
vim /etc/my.cnf
变量名=值
....
:wq
#systemctlrestartmysqld
mysql体系结构？
连接池
sql接口
分析器
优化器
查询缓存
存储引擎
文件系统
管理工具
MySQL服务处理查询请求的过程？
4 程序编写的查询数据库的sql查询命令不合理 ，导致数据库处理慢
在数据库服务器上启用"慢查询日志"：记录超过指定时间显示查询结果的sql命令.（忍受值3秒）
5 数据存储架构设置有数据传输瓶颈。
页:
[1]