主从同步、读写分离、mysql性能调优(软优化)

淩亂°似流年 2022-05-21 12:16 112阅读 0赞

配置mysql主从同步  
1 主从同步的作用：让slave身份的数据库服务器自动同步

master身份的数据库服务器上的数据。

一、主数据库服务器的配置192.168.4.12  
1 用户授权  
mysql> grant replication slave on \*.\* to

slaveuser@"192.168.4.11" identified by "123456";  
2 启用binlog日志  
vim /etc/my.cnf  
\[mysqld\]  
server\_id=12  
log\_bin=master12  
binlog\_format="mixed"  
:wq  
\#systemctl stop mysqld  
\#systemctl start mysqld  
\#ls /var/lib/mysql/master12.\*  
\#mysql -uroot -p123456  
mysql> show master status;

二 、从数据库服务器的配置192.168.4.11  
1 验证主库的授权用户  
\#ping 192.168.4.12  
\#mysql -h192.168.4.12 -uslaveuser -p123456  
mysql> show grants;

2 修改配置文件指定server\_id  
vim /etc/my.cnf  
\[mysqld\]  
server\_id=11  
:wq

\#systemctl stop mysqld  
\#systemctl start mysqld

3 设置自己是那台主机的从库  
\#mysql -uroot -p123456  
mysql> show slave status;  
mysql> change master to

master\_host="192.168.4.12",  
master\_user="slaveuser",  
master\_password="123456",  
master\_log\_file="master12.000001",  
master\_log\_pos=154;  
mysql>show slave status\\G;  
mysql> start slave;  
mysql>show slave status\\G;  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes

三测试主从同步配置  
客户端访问主库时 产生的数据在从库上都可以看的到。

从库暂时不同步主库的数据  
mysql> stop slave;

把从库恢复为独立的数据库服务器  
\#rm -rf master.info localhost-relay-bin.\* relay-

log.info  
\#systemctl stop mysqld ; systemctl start mysqld

四、主从同步工作过程  
cd /var/lib/mysql/  
master.info 连接主库信息  
localhost-relay-bin.000001 中继日志文件  
localhost-relay-bin.index 日志索引文件  
relay-log.info 中继日志信息

IO线程 ：负责把主库binlog里的sql命令保存到本机的中继日

志文件里。

报错原因： 连接不上主库服务器（ping selinux firewalld

grant binlog日志指定错误）

Last\_IO\_Error: 报错信息

解决错误  
mysql> stop slave;  
mysql> change master to 选项=值，选项=值；  
mysql> start slave;

SQL线程：执行本机中继日志文件里的sql命令把数据写进库里

。  
报错原因：本机没有执行日志文件里sql命令时，使用的库或表

。

Last\_SQL\_Error: 报错信息

解决办法：让从库有主库比自己多的数据，然后 stop slave ;start slave;  
\++++++++++++++++++++++++++++++++++  
五、主从同步结构  
一主一从 \*  
一主多从 \*  
主从从  
主主结构（互相主从）  
\++++++++++++++++++++++++++++++++++++  
主从同步配置常用参数  
vim /etc/my.cnf  
/etc/my.cnf  
\[mysqld\]  
....  
:wq  
\#systemctl restart mysqld  
\#mysql>show master status;

主数据服务器使用的参数  
binlog\_do\_db=db1,db2 只允许同步本机库1和库2  
binlog\_ignore\_db=db1,db2 只不允许同步本机库1和库2

从数据服务器使用的参数  
log\_slave\_updates 允许级联复制（主从从）  
replicate\_do\_db=db1,db2 只同步库1和库2  
replicate\_ignore\_db=db1,db2 只不同步库1和库2  
relay\_log=名 指定中继日志文件名

五、mysql读写分离 （第三方软件 + 主从同步）  
1 什么读写分离：把不同操作给不同的数据库服务器处理  
2 为什么要做读写分离：减轻服务器的并发访问压力同时提高硬

件利用率。

实现读写分离  
第三方软件 ：有很多 统称mysql中间件 不同的中间件软件有不

同的功能，大多数都是开源 (mysql-proxy mysql-mmm

mycat maxscale\*)

主从同步结构(一主一从 )  
要求：把数据库服务器11 配置为数据库服务器12的从服务器。

1、配置12  
1.1 用户授权  
1.2 启用binlog日志

2、配置11  
2.1 测试授权用户  
2.2 设置server\_id  
2.3 管理员帐号登录，设置自己为12的从数据库服务器  
2.4 查看IO线程和SQL线程的状态

3、在客户端测试主从同步配置  
3.1 在主数据库服务器上添加对数据有访问权限的用户  
mysql> grant all on bbsdb.\* to webadmin@"%"

identified by "123456";  
mysql> create database bbsdb;

使用主数据库服务器上授权用户连接主数据库服务器，建库，建

表，连接从数据库时，也能够看到新创建的库和表。  
\#mysql -h192.168.4.12 -uwebadmin -p123456

3 配置实现读写分离功能的服务器（13）  
3.1 能够和2台数据库服务器通信  
\#ping 192.168.4.11/12  
3.2 安装软件maxscale-2.1.2-1.rhel.7.x86\_64.rpm  
\#rpm -ivh maxscale-2.1.2-1.rhel.7.x86\_64.rpm(根据提示

安装依赖包)  
\#rpm -ql maxscale  
\#rpm -qc maxscale

3.3 修改配置文件  
\# grep -v '\#' /etc/maxscale.cnf.template >

/etc/maxscale.cnf

\[root@mysql-13 ~\]\# cat /etc/maxscale.cnf  
\[maxscale\]  
threads=1

\[server1\]  
type=server  
address=192.168.4.11  
port=3306  
protocol=MySQLBackend

\[server2\]  
type=server  
address=192.168.4.12  
port=3306  
protocol=MySQLBackend

\[MySQL Monitor\]  
type=monitor  
module=mysqlmon  
servers=server1,server2  
user=scalemon  
passwd=111111  
monitor\_interval=10000

\[Read-Write Service\]  
type=service  
router=readwritesplit  
servers=server1,server2  
user=maxscale  
passwd=111111  
max\_slave\_connections=100%

\[MaxAdmin Service\]  
type=service  
router=cli

\[Read-Write Listener\]  
type=listener  
service=Read-Write Service  
protocol=MySQLClient  
port=4006

\[MaxAdmin Listener\]  
type=listener  
service=MaxAdmin Service  
protocol=maxscaled  
socket=default  
port=4008  
\[root@mysql-13 ~\]\#

\[maxscale\] 定义服务运行时启动的线程数量  
\[server数字\] 定义数据库服务器ip地址  
\[MySQL Monitor\] 定义监控成员列表  
\[Read-Write Service\]定义读写分离主机列表  
\[MaxAdmin Service\] 定义管理服务  
\[Read-Write Listener\]定义读写分离服务使用的端口  
\[MaxAdmin Listener\]定义管理服务使用的端口

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

to scalemon@'%' identified by “111111”;

mysql> grant select on mysql.\* to maxscale@'%'

identified by "111111";

3.4 启动服务  
\# maxscale -h  
\# maxscale -f /etc/maxscale.cnf  
\[root@mysql-13 ~\]\# netstat -utnalp | grep maxscale  
tcp 0 0 192.168.4.13:39870 192.168.4.12:3306

ESTABLISHED 12412/maxscale  
tcp 0 0 192.168.4.13:32980 192.168.4.11:3306

ESTABLISHED 12412/maxscale  
tcp6 0 0 :::4008 :::\* LISTEN

12412/maxscale  
tcp6 0 0 :::4006 :::\* LISTEN

12412/maxscale  
\[root@mysql-13 ~\]\#

在本机连接管理端口查看监控主机的状态  
\[root@mysql-13 ~\]\# maxadmin -P 4008 -pmariadb  
MaxScale> list servers  
Servers.  
\-------------------+-----------------+-------+-------------

\+--------------------  
Server | Address | Port | Connections |

Status  
\-------------------+-----------------+-------+-------------

\+--------------------  
server1 | 192.168.4.11 | 3306 | 0 | Slave,

Running  
server2 | 192.168.4.12 | 3306 | 0 | Master,

Running  
\-------------------+-----------------+-------+-------------

\+--------------------  
MaxScale> list ser  
Unknown or missing option for the list command. Valid

sub-commands are:  
clients List all clients  
dcbs List all DCBs  
filters List all filters  
listeners List all listeners  
modules List all currently loaded modules  
monitors List all monitors  
services List all the services  
servers List all servers  
sessions List all sessions  
threads List polling threads  
commands List registered commands  
MaxScale> quit

4 在客户端测试读写分离  
mysql -h192.168.4.13 -P 4006 -uwebadmin -

p123456  
mysql> select \* from bbsdb.a;  
insert insert into bbsdb.a values(1001);

\+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
mysql性能调优(软优化)  
维护的数据库服务器响应客户端请求慢，可能是什么原因导致的？  
1.1网络带宽窄（使用网络测速软件）

1.2服务器硬件配置过低（CPU 内存 存储）  
使用命令或监控软件监视 使用情况 （top free -m ）

1.3提供数据库服务软件版本低  
如何知道是否是软件版本低，查看服务运行时的参数信息  
/etc/my.cnf  
\[mysqld\]  
变量名=值  
:wq

\#mysql -uroot -p123456  
查看参数值  
mysql> show variables like "%timeout%";  
mysql> show variables like "connect\_timeout";

修改参数值  
mysql> set 变量名=值；  
mysql> set global 变量名=值；

mysql> SET GLOBAL connect\_timeout=5;

常用变量的设置：  
1 并发连接数 max\_connections  
mysql> show processlist

mysql> show global status like "max\_used\_connections";

max\_used\_connections/max\_connections=0.85

2建立连接3次握手超时时间  
connect\_timeout

3 连接建立后，关闭某个不活动连接的超时时间  
wait\_timeout 28800  
  
4 线程数量：允许保存在缓存中被重用的线程数量  
thread\_cache\_size

5 为所有线程缓存的打开的表的数量  
table\_open\_cache

6 与查询缓存相关的参数

select \* from t1; read\_buffer\_size

select \* from t1 where name like "a%"; key\_buffer-size  
seletct \* from t1 group by sex; read\_rnd\_buffer\_siz  
seletct \* from t1 order by 成绩； sort\_buffer\_size

查询优化  
阐述mysql数据服务器处理查询请求的过程？

mysql> show variables like "query\_cache%";  
query\_cache\_type=0/1/2

0 不允许存  
1 无条件存储  
2 要设置把查询结果记录到查询缓存里才存储  
  
select in\_sql\_cache \* from t1;

query\_cache\_wlock\_invalidate off  
myisam存储引擎的表

pc1 select name from t1 where name="jim"  
name="jim"

pc2 select name from t1 where name="jim";  
pc3 update t1 set name="tom" where name="jim";

显示查询缓存统计信息  
mysql> show global status like "qcache%";  
Qcache\_hits 30  
Qcache\_inserts 100  
Qcache\_lowmem\_prunes 10  
Qcache\_not\_cached

1.4 程序员访问数据的sql命令复杂导致处理速度慢  
mysql数据库服务启用慢查询日志 ，记录客户端连接后服务后，

超过指定时间显示查询结果sql命令。

mysql数据库服务的日志类型？  
错误日志 binlog日志 查询日志 慢查询日志  
默认启用的

vim /etc/my.cnf  
\[mysqld\]  
选项  
:wq  
\#systemctl stop mysqld;  
\#systemctl start mysqld;

错误日志:记录服务在启动和运行过程中产生的错误信息  
log-error=/var/log/mysqld.log