Mysql高级实验 mysql的实验原理

转载

huatechinfo 2023-10-11 06:17:06

文章标签 Mysql高级实验 mysql 数据库 database sql 文章分类 MySQL 数据库

MySQL实验；

主从复制

作用，

负载均衡读操作
备份
高可用和故障切换
数据分布
MySQL升级

架构，

原理

实验一：一主一从

实验准备：

主节点：
1，server ID　２，二进制开启　３，用户授权　４，二进制位置；
从节点：
１，server　ID 2，开启复制，哪个位置账号，指定用户3，开启线程

数据库安装：

主节点：

yum -y install mysql-server
vim /etc/my.cnf
[msyqld]
server-id=6
log-bin=/data/mysql/logbin/mysql-bin
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/
systemctl enable --now mysqld

(二进制查看)
ll /data/mysql/logbin/
mysql
show master logs;
show master status;

mysql-bin.000002 |      156 

(创建账号并授权)
create user repluser@'10.0.0.%' identified by '123456';
grant replication slave on *.* repluser@'10.0.0.%';

从节点

MASTER_CONNECT_RETRY=2;(默认十秒执行)

（指定用户，二进制位置）

show slave status;
 CHANGE MASTER TO
  MASTER_HOST='10.0.0.9',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=156;
 
 show slave status\G
 
 #Slave_IO_Running: No
          #  Slave_SQL_Running: No

 start slave;(开启线程)
 
 show processlist;(查看线程连接)
 
 ss -ntl;(查看端口)
 
 ll /var/lib/mysql/

方法二：

主节点上：

yum -y install mysql-server
vim /etc/my.cnf
[msyqld]
server-id=6
log-bin=/data/mysql/logbin/mysql-bin
mkdir -p /data/mysql/logbin/
chown -R mysql.mysql /data/mysql/
systemctl enable --now mysqld

(二进制查看)
ll /data/mysql/logbin/
mysql
show master logs;
show master status;


在这备份：
mysqldump -uroot -A -F --single-transaction --source-data=1 > /data/full_bak.sql  （MySQL没有密码）
less /data/full_bak.sql (查看这个二进制文件)
scp /data/full_bak.sql 10.0.0.9(copy☞从节点)

从节点

vi /etc/my.cnf
[mysqld]
server-id=9
read-only
mysql
show slave status\G
set sql_log_bin=0;
source full_bak.sql;(与法一，可以文件中录入，省人工操作）；
show slave status\G;
start slave;
set sql_log_bin=1;

MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=156;

这个时候已经同步完成，可以使用数据测试！

优点：读写分离，负载均衡，数据备份；

实验二：级联复制；

所遇到的错误：

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：

解决办法：mysql> stop slave;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

在实验一的基础上配置：

实验原理准备：

需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

主节点：

yum -y install mysql-server
vim /etc/my.cnf
[mysqld]
server-id=6
log-bin
systemctl start mysqld
mysql
create user repluser@'10.0.0.%' identified by '123456';
grant replication slave on *.* to repluser@'10.0.0.%';
mysqldump -uroot -A -F --single-transaction --master-data=1 > /data/all.sql;
scp /data/all.sql 10.0.0.9:
scp /data/all.sql 10.0.0.8:

从节点：

从节点：需要开启二进制事件，中继日志，更好的转发的次从节点；

中间级联slave实现：

vim /etc/my.cnf
[mysqld]
server-id=9
log-bin
read-only
log_slave_updates
systemctl restart mysqld

#还原数据库
vim /data/all.sql
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000002',
  MASTER_LOG_POS=156;
  mysql-bin.000005
  
  mysql
  set sql_log_bin=0;
  source /data/all.sql;
  show master logs; #记录二进制位置，给第三个节点使用
  set sql_log_bin=1;
  start slave;
  
  
  #第三个节点slave实现

vim  /etc/my.cnf
server-id=8;
read-only

systemctl restart mysqld
vim /data/all.sql
CHANGE MASTER TO
  MASTER_HOST='10.0.0.9',#中间节点IP
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',（二进制位置）
  MASTER_LOG_POS=156;
  
  mysql < /data/all.sql
  mysql -e 'start slave';

实验三：半同步

此实验是基于实验一主从复制基础操作。具体操作已自己的机器为准！

主从服务器的版本要一致；如不一致，主底，从高–保证，从高向下兼容二进制。

rocky8-bin.000001 |       156 

CHANGE MASTER TO
  MASTER_HOST='10.0.0.6',#主节点的IP
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='rocky8-bin.000001',#结合自己的二进制位置
  MASTER_LOG_POS=156;

同步：客户更新数据，proxy代理服务器选着，主节点写入，主节点二进制保存，传入从结点同步，从节点完成回复给主节点，接着做同步完成。发送信息给代理。

半同步：从节点一个，如果都不成功，有超时时间，还是还回信息。

半同步对于主从复制架构来说多了几个操作：

装插件以及开启插件；

#查看插件
[root@centos8 ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so

#master服务器配置
[root@master ~]#vim /etc/my.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON   #修改此行,需要先安装semisync_master.so插件后,再重
启,否则无法启动
rpl_semi_sync_master_timeout=3000  #设置3s内无法同步，也将返回成功信息给客户端（一般默认10秒）

#主服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;
mysql>SHOW PLUGINS; #查看插件
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 3000;  #超时长1s,默认值为10s
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';


#slave服务器配置
[root@slave1 ~]#vim /etc/my.cnf
[mysqld]
server-id=18
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动


#从服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql>SET GLOBAL rpl_semi_sync_slave_enabled=1; #临时修改变量
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';

#注意:如果已经实现主从复制,需要stop slave;start slave;
mysql> stop slave;
mysql> start slave;
mysql> SHOW GLOBAL STATUS LIKE '%semi%';

实验四：Mycat代理服务器

实验模型

Mycat作用：数据库是对底层存储文件的抽象，而Mycat是对数据库的抽象。

原理：最重要的是一个“拦截”，拦截用户发送过来的SQL语句，然后分析语句，在将SQL发往后端的真实数据库，返回的结果做适当处理，最终返回给用户。

Mycat主要配置说明

server.xml 存放mycat软件本身相关的配置文件，连接mycat的用户，密码，数据库名称等。

schema.xml最主要的配置项，文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器

实验准备：

服务器三台：

mycat 6，MySQL-maser 8， mysql-slave 9

在8，9上配置主从复制；

#master主服务器
vim /etc/my.cnf
[mysqld]
server-id=8
bin-log
systemctl start mysqld

mysql
create user repluser@'10.0.0.%' identified by '123456';
grant replcation slave on *.* repluser@'10.0.0.%';
show master status;


#slave从服务器
mysql
CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000002',
  MASTER_LOG_POS=156;
 
 start slave;
 
 show slave status\G
 
 
 #Mycat代理服务器
 yum -y install java
 java -version
 
 #wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-
server-1.6.7.6-release-20210303094759-linux.tar.gz

 mkdir /apps
 tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
 
 #配置环境变量
 echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
 . /etc/profile.d/mycat.sh
ss -ntl
 mycat start
 #以看到打开多个端口,其中8066端口用于连接MyCAT
 ss -ntl
 #查看日志，确定成功
 tail /apps/mycat/logs/wrapper.log
 
 
 #客户端连接
 mysql -uroot -p123456 -h 10.0.0.6 -P8066
 show databases;
 +----------+
| DATABASE |
+----------+
| TESTDB  |
+----------+

#mycat服务器上
vim /apps/mycat/conf/server.xml
<property name="serverPort">3306</property>#可以改为3306

#修改schema.xml实现读写分离策略
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="***false***" sqlMaxLimit="100"
dataNode="***dn1***"></schema>
<dataNode name="dn1" dataHost="localhost1" database="***mycat***" />  #其中mycat
表示后端服务器实际的数据库名称
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="***1***"
 writeType="0" dbType="mysql" dbDriver="native" switchType="1"
 slaveThreshold="100">
<heartbeat>select user()</heartbeat>
***<writeHost host="host1" url="10.0.0.18:3306" user="root"
password="123456">***
***<readHost host="host2" url="10.0.0.28:3306" user="root" password="123456"
/>***
</writeHost>
</dataHost>
</mycat:schema>
上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为
从库
注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，
也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，
否则会导致登录mycat后，对库和表操作失败！
范例：schema.xml

#以上***部分表示原配置文件中需要修改的内容
#重新启动mycat
mycat restart

6、在后端主服务器创建用户并对mycat授权

mysql

create database mycat;
create user root@’10.0.0.%‘ identified by '123456';
grant all on root.* to 'root'@'10.0.0.%';


7、在Mycat服务器上连接并测试

mysql -uroot -p123456 -h10.0.0.6 
show databases;
+----------+
| DATABASE |
+----------+
| TESTDB  |  //只能看一个虚拟数据库
+----------+
mysql> use TESTDB;
mysql> create table t1(id int);
MySQL> select @@server_id;
MySQL> select @@hostname;

8、通过通用日志确认读写分离；
show variables like 'general_log';  #查看日志是否开启
set global general_log=on;   #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置
 
 
 #主从服务器上；
 vim /etc/my.cnf
 [mysqld]
 general_log=on
 systemctl restart mysqld
 
 tail -f /var/lib/mysql/主机名.log
 
 
 #MyCAT对后端服务器的健康性检查方法select user()
 
 主从查看通用日志：
 [root@master ~]#tail -f /var/lib/mysql/master.log
 
 [root@slave ~]#tail -f /var/lib/mysql/slave.log

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。