本文共 6686 字，大约阅读时间需要 22 分钟。
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" identified by "123456";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> show master status;
1.3.2从库角色主机的配置？
1 显示自己是否是从数据库服务器 mysql> show slave status;2 测试主库授权的用户是否有效
#mysql -h192.168.4.54 -uyaya -p123456 mysql> show grants; mysql> quit3 修改配置文件指定server_id
#vim /etc/my.cnf [mysqld] server_id=55 :wq #systemctl restart mysqld4 在本机数据管理员登录指定主库信息。
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.* relay-log.info #systemctl restart mysqld主从同步结构模式？
一主一从 一主多从 主从从 互为主从（主主结构）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=118 [server1]
19 type=server 20 address=192.168.4.54 21 port=3306 22 protocol=MySQLBackend 2325 [server2]
26 type=server 27 address=192.168.4.55 28 port=3306 29 protocol=MySQLBackend38 [MySQL Monitor]
39 type=monitor 40 module=mysqlmon 41 servers=server1, server2 42 user=scalemon # 监控数据库服务服务的运行状态和主从状态 43 passwd=123456 44 monitor_interval=1000066 [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=cli94 [Read-Write Listener]
95 type=listener 96 service=Read-Write Service 97 protocol=MySQLClient 98 port=4006100 [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@'%' identified by"123456";
mysql> grant select on mysql.* to maxscale@'%' identified by "123456";
c. 启动服务
[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.53d.1 在数据库服务器添加访问数据的用户
mysql> grant all on . to student@'%' identified by "123456";d.2 访问代理主机
#mysql -P4006 -h192.168.4.53 -ustudent -p123456+++++++++++++++++++++++++++++++++++++
三、MySQL优化(优化思路、 优化什么 、怎么优化) 访问数据时，出结果特别慢。分析可能是由哪些原因导致的。1 硬件配置低：查看应用设备的使用率CPU 内存 存储(硬盘)
top 0.0 wa sar uptime I/Ofree -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 T1pc2 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; <----mysqld select * from user; <------ mysqld select shell from user group by shell;<------ mysqld查询缓存相关参数设置？
MySQL> show variables like "query_cache%";查询缓存写锁有效 存储引擎（MYISAM）
query_cache_wlock_invalidate | OFFt1
pc1 select name from user where name="lucy";
name="lucy" ------> 查询缓存pc2 select name from user where name="lucy";--> 查询缓存
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 mysqldmysql体系结构？
连接池 sql接口 分析器 优化器 查询缓存 存储引擎 文件系统 管理工具MySQL服务处理查询请求的过程？
4 程序编写的查询数据库的sql查询命令不合理 ，导致数据库处理慢
在数据库服务器上启用"慢查询日志"：记录超过指定时间显示查询结果的sql命令.（忍受值3秒）5 数据存储架构设置有数据传输瓶颈。
本文转自sweak_h 51CTO博客，原文链接：http://blog.51cto.com/13478354/2065212，如需转载请自行联系原作者