MySQL一主一从、配置一主多从结构、数据读写分离

部署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: NoSlave_SQL_Running: No......

启动slave进程

mysql> start slave;

查看状态信息

mysql> show slave status \G

mysql> show slave status \G......Slave_IO_Running: YesSlave_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  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: NoSlave_SQL_Running: No......注意:日志名和偏移量 要写 在mysql53主机执行完全备份之前查看到的日志名和偏移量

启动slave

mysql> start slave;

查看状态信息

mysql> show slave status \G......Slave_IO_Running: YesSlave_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上查看验证

# 55mysql> select * from bbsdb.a;+------+| id |+------+|110 ||114 ||119 ||122 |+------+4 rows in set (0.00 sec)# 54mysql> select * from bbsdb.a;+------+| id |+------+|110 ||114 ||119 ||122 |+------+4 rows in set (0.00 sec)

部署mysql数据读写分离结构

56master数据库服务器

57slave数据库服务器

58读写分离服务器

相关理论

把客户端访问数据库服务的查询访问select、写访问insert 分别给不同的数据库服务器处理

功能：减轻单台数据库服务器的并发访问压力

如何实现数据的读写分离

人肉分离select——–>57

insert——–>56

搭建读写分离服务实现

能够实现数据读写分离服务的软件（中间件）有哪些

mysql-proxy mycat和mycat2

环境准备

配置mysql主从结构

56master数据库服务器

57slave数据库服务器

配置器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 ~]# mysqlmysql> 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 startStarting mycat2...[root@myscat58 ~]# ls /usr/local/mycat/logs/mycat.pidwrapper.log[root@myscat58 ~]# ss -ntulp | grep 8066tcp LISTEN 0128 *:8066 *:*users:(("java",pid=22649,fd=71))[root@myscat58 ~]#

连接mycat服务

[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321mysql> 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.jsonwhost56.datasource.jsonwhost57.datasource.json

在数据库服务器上添加hmy用户

[root@mysql56 ~]# mysqlmysql> create user hmy@"%" identified by '123456';mysql> grant all on *.* to hmy@"%";# 测试[root@myscat58 ~]# mysql -h192.168.88.56 -uhmy -p123456mysql>[root@myscat58 ~]# mysql -h192.168.88.57 -uhmy -p123456mysql>

创建集群 把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: rwclusterSWITCH_TYPE: SWITCHMAX_REQUEST_COUNT: 2000 TYPE: BALANCE_ALL WRITE_DS: whost56READ_DS: whost56,whost57WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1 READ_L: io.mycat.plug.loadBalance.BalanceRandom$1AVAILABLE: true*************************** 2. row *************************** NAME: prototypeSWITCH_TYPE: SWITCHMAX_REQUEST_COUNT: 200 TYPE: BALANCE_ALL WRITE_DS: prototypeDsREAD_DS: prototypeDsWRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1 READ_L: io.mycat.plug.loadBalance.BalanceRandom$1AVAILABLE: true2 rows in set (0.00 sec)# 创建的集群以文件的形式保存在目录下[root@myscat58 ~]# ls /usr/local/mycat/conf/clusters/prototype.cluster.jsonrwcluster.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

[root@myscat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321mysql> 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 restartStopping mycat2...Stopped mycat2.Starting mycat2...[root@myscat58 ~]# ss -ntulp | grep 8066tcp LISTEN 0128 *:8066 *:*users:(("java",pid=23325,fd=72))[root@myscat58 ~]# 

测试配置

# 连接mycat服务建表插入记录[root@client ~]# mysql -h192.168.88.58 -P8066 -umycat -p654321mysql> 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 ||| |+----------------+----------+--------------+------------------+-------------------+