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

andyyuduo

　　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> grant  replication  slave on .  to  yaya@"192.168.4.55">　　2  启用binlog日志
　　10  vim  /etc/my.cnf
　　server_id=54
　　log-bin=master54
　　binlog-format="mixed"
　　：wq
　　3 重启数据库服务器
　　11  systemctl  stop mysqld
　　12  systemctl  start mysqld
　　13  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;
　　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> 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.*  >　　#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
　　9 [maxscale]
　　10 threads=1
　　18 [server1]
　　19 type=server
　　20 address=192.168.4.54
　　21 port=3306
　　22 protocol=MySQLBackend
　　23
　　25 [server2]
　　26 type=server
　　27 address=192.168.4.55
　　28 port=3306
　　29 protocol=MySQLBackend
　　38 [MySQL Monitor]
　　39 type=monitor
　　40 module=mysqlmon
　　41 servers=server1, server2
　　42 user=scalemon   # 监控数据库服务服务的运行状态和主从状态
　　43 passwd=123456
　　44 monitor_interval=10000
　　66 [Read-Write Service]
　　67 type=service
　　68 router=readwritesplit
　　69 servers=server1, server2
　　70 user=maxscale #检查接收客户端连接请求时，连接的用户名和密码在数据库服务器上是否存在
　　71 passwd=123456
　　72 max_slave_connections=100%
　　78 [MaxAdmin Service]
　　79 type=service
　　80 router=cli
　　94 [Read-Write Listener]
　　95 type=listener
　　96 service=Read-Write Service
　　97 protocol=MySQLClient
　　98 port=4006
　　100 [MaxAdmin Listener]
　　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. 启动服务

systemctl  stop mysqld
　　[root@003 ~]# maxscale -f  /etc/maxscale.cnf
　　[root@003 ~]# netstat -utnalp  | grep :4010
　　tcp6       0      0 :::4010                 :::                    LISTEN      9559/maxscale      
　　[root@003 ~]#
　　[root@003 ~]# netstat -utnalp  | grep :4006
　　tcp6       0      0 :::4006                 :::                    LISTEN      9559/maxscale
　　[root@003 ~]# netstat -utnalp  | grep maxscle
　　[root@003 ~]# 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
　　[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@'%'>　　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> 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_sizename  Index
　　select name  from user where  name="zhangsan";
　　select name,uid  from user order  by   uid  desc;   查询缓存
　　name="lucy"
　　pc3 update  user  set 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> show  global  status  like  "qcache%";
　　Qcache_hits               0
　　Qcache_inserts          0
　　Qcache_lowmem_prunes     0
　　并发连接数：
　　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 数据存储架构设置有数据传输瓶颈。