部署mysql主从同步
配置mysql主从
分为主数据库角色（master）、从数据库服务器角色（slave）
网站服务器连接后存储数据的服务器作为主服务器
自动同步主服务器上的数据
192.168.88.53 做master
启用binlog日志文件 指定server_id 重启服务
[root@mysql53 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
......
log-bin=mysql53 # 自定义binlog日志文件
server-id=53 # 主从配置 server id 不能一样
[root@mysql53 ~]# systemctl restart mysqld
[root@mysql53 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000001 | 156 | | | |
+----------------+----------+--------------+------------------+-------------------+
[root@mysql53 ~]# ls /var/lib/mysql/mysql53.*
/var/lib/mysql/mysql53.000001 /var/lib/mysql/mysql53.index用户授权
mysql> create user repluser@"%" identified by "123qqq...A";
mysql> grant replication slave on *.* to repluser@"%";
# 验证登录
[root@mysql54 ~]# mysql -h192.168.88.53 -urepluser -p123qqq...A
......
mysql>
查看正在使用的binlog日志文件
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql53.000001 | 667 | | | |
+----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)192.168.88.54 做slave
指定server_id 并重启数据库服务
[root@mysql54 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
......
server-id=54
[root@mysql54 ~]# systemctl restart mysqld
# 用一个已安装mysql的虚拟机克隆出来的主从机器，在从机器上执行此操作
[root@mysql54 ~]# rm -rf /var/lib/mysql/auto.cnf
[root@mysql54 ~]# systemctl restart mysqld
指定主服务器信息
mysql> change master to master_host="192.168.88.53",master_user="repluser",master_password="123qqq...A",master_log_file="mysql53.000001",master_log_pos=667;
mysql> show slave status \G
......
Slave_IO_Running: No
Slave_SQL_Running: No
......启动slave进程
mysql> start slave;
查看状态信息
mysql> show slave status \G
mysql> show slave status \G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......主从验证
在master上添加数据
# -e 命令行下执行数据库命令
[root@mysql53 ~]# mysql -e 'create database bbsdb'
[root@mysql53 ~]# mysql -e 'create table bbsdb.a(id int)'
[root@mysql53 ~]# mysql -e 'insert into bbsdb.a values(110)'
在slave上查看验证
[root@mysql54 ~]# mysql -e 'show databases'
+--------------------+
| Database |
+--------------------+
| bbsdb |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@mysql54 ~]# mysql -e 'desc bbsdb.a'
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
[root@mysql54 ~]# mysql -e 'select * from bbsdb.a'
+------+
| id |
+------+
| 110 |
+------+
主从同步的工作原理
Master：启用binlog日志，记录所有的数据库更新和修改操作
Slave：
Slave_IO：复制master主机 binlog日志文件里的SQL命令到本机的relay-log(中继日志文件)文件里
Slave_SQL：执行本机relay-log文件里的SQL语句，实现与Master数据一致
排错方法
Last_IO_Error: IO线程报错信息
报错的原因：通常是 change master to 的配置项不正确
解决办法：重新指定主服务器信息
stop slave;
change master to 服务器ip 用户 密码 日志文件名 偏移量;
start slave;
Last_SQL_Error: SQL线程报错信息
通常是执行relay-log文件里的sql命令时失败导致的
配置 一主多从 结构
给已有的master服务器53添加第二台slave角色的服务器55
指定server_id 并重启mysql服务
[root@mysql55 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
......
server-id=55
[root@mysql55 ~]# systemctl restart mysqld
要手动同步主服务器比自己多的
在master服务器执行完全备份 把备份文件拷贝给slave主机
mysql> show master status; # 查看binlog日志文件 偏移量
mysql> show databases; # 查看比slave多的库
[root@mysql53 ~]# mysqldump -B bbsdb > /root/bbsdb.sql # 完全备份
[root@mysql53 ~]# scp /root/bbsdb.sql root@192.168.88.55:/root在slave服务器执行 备份文件 恢复数据
[root@mysql55 ~]# mysql < /root/bbsdb.sql
[root@mysql55 ~]# mysql
mysql> show databases;
指定主服务器信息
change master to 服务器的ip 用户 密码 日志文件名 偏移量
mysql> change master to master_host="192.168.88.53",master_user="repluser",master_password="123qqq...A",master_log_file="mysql53.000002",master_log_pos=156;
mysql> show slave status \G
......
Slave_IO_Running: No
Slave_SQL_Running: No
......
注意:日志名和偏移量 要写 在mysql53主机执行完全备份之前查看到的日志名和偏移量启动slave
mysql> start slave;
查看状态信息
mysql> show slave status \G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......主从验证
在master上添加数据
mysql> insert into bbsdb.a values(114),(119),(122);
mysql> select * from bbsdb.a;
+------+
| id |
+------+
| 110 |
| 114 |
| 119 |
| 122 |
+------+
4 rows in set (0.00 sec)
在slave上查看验证
# 55
mysql> select * from bbsdb.a;
+------+
| id |
+------+
| 110 |
| 114 |
| 119 |
| 122 |
+------+
4 rows in set (0.00 sec)
# 54
mysql> select * from bbsdb.a;
+------+
| id |
+------+
| 110 |
| 114 |
| 119 |
| 122 |
+------+
4 rows in set (0.00 sec)
部署mysql数据读写分离结构
56 master数据库服务器
57 slave数据库服务器
58 读写分离服务器
相关理论
把客户端访问数据库服务的查询访问select、写访问insert 分别给不同的数据库服务器处理
功能：减轻单台数据库服务器的并发访问压力
如何实现数据的读写分离
人肉分离 select-------->57
insert-------->56
搭建读写分离服务实现
能够实现数据读写分离服务的软件（中间件）有哪些
mysql-proxy mycat和mycat2
环境准备
配置mysql主从结构
56 master数据库服务器
57 slave数据库服务器
配置器mycat服务器
58
准备软件
安装软件
# 安装jdk
[root@myscat58 ~]# yum -y install java-1.8.0-openjdk.x86_64
[root@myscat58 ~]# java -version
# 安装解压命令
[root@myscat58 ~]# which unzip || yum -y install unzip
# 安装mycat
[root@myscat58 ~]# unzip mycat2-install-template-1.21.zip
[root@myscat58 ~]# mv mycat /usr/local/
# 安装依赖
[root@myscat58 ~]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
# 修改权限
[root@myscat58 ~]# chmod -R 777 /usr/local/mycat/
修改配置
设置客户端连接mycat服务时使用的用户名和密码
[root@myscat58 ~]# vim /usr/local/mycat/conf/users/root.user.json
{^M
"dialect":"mysql",^M
"ip":null,^M
"password":"654321",^M
"transactionType":"proxy",^M
"username":"mycat"^M
}
设置mycat服务启动时连接的数据库服务器
[root@myscat58 ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.datasource.json
{^M
"dbType":"mysql",^M
"idleTimeout":60000,^M
"initSqls":[],^M
"initSqlsGetConnection":true,^M
"instanceType":"READ_WRITE",^M
"maxCon":1000,^M
"maxConnectTimeout":3000,^M
"maxRetryCount":5,^M
"minCon":1,^M
"name":"prototypeDs",^M
"password":"123456",^M
"type":"JDBC",^M
"url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",^M
"user":"hmy",^M
"weight":0^M
}
根据配置在本机运行mysql服务并创建hmy用户
[root@myscat58 ~]# yum -y install mysql-server mysql
[root@myscat58 ~]# systemctl start mysqld
[root@myscat58 ~]# mysql
mysql> create user hmy@"localhost" identified by '123456';
mysql> grant all on *.* to hmy@"localhost";
[root@myscat58 ~]# mysql -hlocalhost -uhmy -p123456
启动mycat服务
[root@myscat58 ~]# ls /usr/local/mycat/logs/
[root@myscat58 ~]# /usr/local/mycat/bin/mycat start
Starting mycat2...
[root@myscat58 ~]# ls /usr/local/mycat/logs/
mycat.pid wrapper.log
[root@myscat58 ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=22649,fd=71))
[root@myscat58 ~]#连接mycat服务
[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql> show databases;
+--------------------+
| `Database` |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.16 sec)
连接mycat服务配置读写分离
MySQL主从结构 + MyCat2
主从结构 + mycat 软件实现 数据的读写分离
添加数据源（定义数据库服务器的ip地址）
# 连接mycat服务
[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
# 添加mysql56数据库服务器
mysql> /*+ mycat:createdatasource{
-> "name":"whost56", "url":"jdbc:mysql://192.168.88.56:3306","user":"hmy","password":"123456"}*/;
Query OK, 0 rows affected (0.30 sec)
# 添加mysql57数据库服务器
mysql> /*+ mycat:createdatasource{
-> "name":"whost57", "url":"jdbc:mysql://192.168.88.57:3306","user":"hmy","password":"123456"}*/;
Query OK, 0 rows affected (0.07 sec)
# 查看数据源
/*+mycat:showDataSources{}*/ \G
# 添加的数据源以文件的形式保存在安装目录下
[root@myscat58 ~]# ls /usr/local/mycat/conf/datasources/
prototypeDs.datasource.json whost56.datasource.json whost57.datasource.json在数据库服务器上添加hmy用户
[root@mysql56 ~]# mysql
mysql> create user hmy@"%" identified by '123456';
mysql> grant all on *.* to hmy@"%";
# 测试
[root@myscat58 ~]# mysql -h192.168.88.56 -uhmy -p123456
mysql>
[root@myscat58 ~]# mysql -h192.168.88.57 -uhmy -p123456
mysql>创建集群 把56和57组成工作组
[root@myscat58 ~]#mysql -h127.0.0.1 -P8066 -umycat -p654321
# 创建集群
mysql> /*!mycat:createcluster{ "name":"rwcluster", "masters":["whost56"], "replicas":["whost57"] }*/;
# 查看集群信息
mysql> /*+ mycat:showClusters{}*/ \G*************************** 1. row ***************************
NAME: rwcluster
SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 2000
TYPE: BALANCE_ALL
WRITE_DS: whost56
READ_DS: whost56,whost57
WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
AVAILABLE: true
*************************** 2. row ***************************
NAME: prototype
SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 200
TYPE: BALANCE_ALL
WRITE_DS: prototypeDs
READ_DS: prototypeDs
WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
AVAILABLE: true
2 rows in set (0.00 sec)
# 创建的集群以文件的形式保存在目录下
[root@myscat58 ~]# ls /usr/local/mycat/conf/clusters/
prototype.cluster.json rwcluster.cluster.json
指定数据库服务器的角色
master角色机器负责写
slave角色机器负责访问
# 修改master角色主机仅负责写访问
[root@myscat58 ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"WRITE", # 仅负责写访问
"logAbandoned":true,
"maxCon":1000,
"maxConnectTimeout":30000,
"maxRetryCount":5,
"minCon":1,
"name":"whost56",
"password":"123456",
"queryTimeout":0,
"removeAbandoned":false,
"removeAbandonedTimeoutSecond":180,
"type":"JDBC",
"url":"jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
"user":"hmy",
"weight":0
}
# 修改slave角色主机仅负责读访问
[root@myscat58 ~]# vim /usr/local/mycat/conf/datasources/whost57.datasource.json
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ", # 仅负责读访问
"logAbandoned":true,
"maxCon":1000,
"maxConnectTimeout":30000,
"maxRetryCount":5,
"minCon":1,
"name":"whost57",
"password":"123456",
"queryTimeout":0,
"removeAbandoned":false,
"removeAbandonedTimeoutSecond":180,
"type":"JDBC",
"url":"jdbc:mysql://192.168.88.57:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
"user":"hmy",
"weight":0
}定义集群出来读访问的策略
集群处理查询访问的方式
[root@myscat58 ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetryCount":3,
"minSwitchTimeInterval":300,
"showLog":false,
"slaveThreshold":0.0
},
"masters":[
"whost56"
],
"maxCon":2000,
"name":"rwcluster",
"readBalanceType":"BALANCE_ALL_READ",
"replicas":[
"whost57"
],
"switchType":"SWITCH"
}
创建网站存储数据使用的库 并定义 库存储数据使用rwcluster文章来源：https://www.toymoban.com/news/detail-804161.html
[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
mysql> create database testdb;
# 指定testdb库存储数据使用的集群
[root@myscat58 ~]# vim /usr/local/mycat/conf/schemas/testdb.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"testdb",
"targetName":"rwcluster", # 添加此行,之前创建的集群名rwcluster
"shardingTables":{},
"views":{}
}
[root@myscat58 ~]# /usr/local/mycat/bin/mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@myscat58 ~]# ss -ntulp | grep 8066
tcp LISTEN 0 128 *:8066 *:* users:(("java",pid=23325,fd=72))
[root@myscat58 ~]#
测试配置文章来源地址https://www.toymoban.com/news/detail-804161.html
# 连接mycat服务建表插入记录
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321
mysql> create table user(name char(10),passwd char(6));
mysql> insert into user values("A","123");
mysql> select * from user;
+------+--------+
| name | passwd |
+------+--------+
| A | 123 |
+------+--------+
1 row in set (0.01 sec)
[root@mysql56 ~]# mysql -e 'select * from testdb.user'
+------+--------+
| name | passwd |
+------+--------+
| A | 123 |
+------+--------+
[root@mysql57 ~]# mysql -e 'select * from testdb.user'
+------+--------+
| name | passwd |
+------+--------+
| A | 123 |
+------+--------+
# 测试读写分离
# 在从服务器本机插入记录，数据仅在从服务器有，主服务器没有
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e 'insert into testdb.user values("B","678")'
[root@mysql57 ~]# mysql -e 'insert into testdb.user values("mysql57","57")'
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e 'select * from testdb.user'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------+--------+
| name | passwd |
+---------+--------+
| A | 123 |
| B | 678 |
| mysql57 | 57 |
+---------+--------+
# 主服务器数据不变，日志偏移量不不变
[root@mysql56 ~]# mysql -e 'select * from testdb.user'
+------+--------+
| name | passwd |
+------+--------+
| A | 123 |
| B | 678 |
+------+--------+
[root@mysql56 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql56.000001 | 2538 | | | |
+----------------+----------+--------------+------------------+-------------------+
# 客户端连接mycat服务读/写数据
[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321 -e 'insert into testdb.user values("bbb","222")'
# 在主服务器查看数据和日志偏移量
[root@mysql56 ~]# mysql -e 'show master status'
+----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| mysql56.000001 | 2828 | | | |
+----------------+----------+--------------+------------------+-------------------+
到了这里，关于MySQL一主一从、配置一主多从结构、数据读写分离的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
