数据库实战-主从备份&读写分离

  • 本文为个人学习笔记，仅供参考

准备工作

本次实验环境

虚拟机主机名 系统 IP 关系
mycat CentOS7 192.168.10.6/24 读写分离(DB1写，DB2读)
DB1 CentOS7 192.168.10.18/24 主从备份(主)
DB2 CentOS7 192.168.10.30/24 主从备份(从)

注：做好主机名映射以及网络互通

关闭防火墙以及将SELinux设置为permissive模式

1
2
3
systemctl stop firewalld
systemctl disable firewalld
setenforce 0

主从备份

db1、db2安装mariadb

1
2
3
[root@db1 ~] yum install -y mariadb mariadb-server
[root@db1 ~] systemctl enable mariadb
[root@db1 ~] systemctl restart mariadb
1
2
3
[root@db2 ~] yum install -y mariadb mariadb-server
[root@db2 ~] systemctl enable mariadb
[root@db2 ~] systemctl restart mariadb

配置数据库

  • db1和db2都需要做以下配置
初始化数据库
1
mysql_secure_installation

注：本次实验中密码设置为123456，“Disallow root login remotely”设置为no，其他皆为yes

进入/etc/my.cnf在[mariadb]下增加配置
1
2
3
4
5
vim /etc/my.cnf
增加内容为：
log_bin = mysql-bin
binlog_ignore_db = mysql
server_id = #db1为18，db2为30
重启数据库
1
systemctl restart mariadb
配置数据库

db1

1
mysql -uroot -p123456	#进入数据库
1
grant all privileges on *.* to root@'%' identified by "123456";	#赋权

注：
grant：赋权命令
all privileges：当前用户的所有权限
on：介词
.：当前用户对所有数据库和表的相应操作权限
to：介词
‘root’@’%’：权限赋给root用户，所有ip都能连接
identified by “123456”：连接时输入密码，密码为123456

1
grant replication slave on *.* to 'user'@'db2' identified by '123456';	#配置从服务器

注：
grant replication：配置从服务器

** db2 **

1
mysql -uroot -p123456	#进入数据库
1
change master to master_host='db1',master_user='user',master_password='123456';	#与主服务器关联

注：
change master：关联主服务器

检查是否成功

进入db2数据库，启动slave，查看slave状态

1
2
start slave;
show slave status\G;	#slave_IO_Running与Slave_SQL_Running显示yes则配置成功
测试主从备份

主服务器：

1
2
3
4
5
create database test;	#创建数据库test
use test;	#使选择数据库
create table company(id int no null primary key,name varchar(50),addr varchar(255))	#创建表company
insert into company values(1,"facebook","usa");	#写入数据
select * from company;	#查看company表

从服务器：

1
2
3
show databases;	#查看数据库，应有test数据库
use test;	#选择数据库test
select * from company;	#查看表是否与主服务器一致

读写分离

  • 以下操作皆在mycat虚拟机上

安装java

1
yum -y install java-1.8.0-opebjdk java-1.8.0-opebjdk-devel

安装mycat

本次实验将已经下载的mycat-server-1.6-RELEASE包解压即可，若无安装包或需使用其他版本可到 http://www.mycat.org.cn/ 下载

1
2
tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local
#解压mycat到/usr/local下

修改权限及环境变量

1
2
3
4
chmod -R 777 /usr/local/mycat/	#设置权限
echo export MYCAT_HOME=/usr/local/mycat/ >> /etc/profile	#修改环境变量
source /etc/profile	#使变量生效
echo $MYCAT_HOME	#查看是否成功

修改schema.xml及server.xml文件

schema.xml

路径：/usr/local/mycat/conf/schema.xml

1
2
3
4
5
6
7
8
9
10
11
12
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="USERDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="localhost1" database="test" /> 
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1"  slaveThreshold="100"> 
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.10.18:3306" user="root" password="123456">
<readHost host="hostS1" url="192.168.10.30:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>

注：根据个人配置更改其中参数，此schema.xml文件为本次实验参数

server.xml

路径：/usr/local/mycat/conf/server.xml

删除此部分

1
2
3
4
5
<user name="user">
		<property name="password">user</property>
		<property name="schemas">TESTED</property>
		<property name="readOnly">true</property>
</user>

修改此部分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<user name="root">
		<property name="password">123456</property>
		<property name="schemas">USERDB</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>
启动mycat，查看端口
1
2
/bin/bash /usr/local/mycat/bin/mycat start	#启动mycat
netstat -ntpl	#查看端口，若是没有此命令则安装net-tools

注：端口显示应有9066和8066，若是没有可能是配置文件有问题，可查看日志文件(/usr/local/mycat/logs/wrapper.log)排错

安装MariaDB-client

配置yum源

本实验使用已设置的yum源，如需获取或使用其他版本可到 https://mariadb.org/ 下载

1
2
3
4
5
[mariadb]
name=mariadb
baseurl=file:///opt/gpmall-repo
gpgcheck=0
enabled=1
1
yum repolist	#查看可用仓库
安装数据库客户端
1
yum install -y MariaDB-client

查看数据库

查看数据库
1
mysql -h127.0.0.1 -P8066 -uroot -p123456
1
2
3
4
5
6
show database;	#查看数据库，应有USERDB
use USERDB;	#选择数据库
show tables;	#查看表，应有之前创建的company
select * from company;	#查看表
insert into company values(2,"ball","usa");	#插入数据
select * from company;	#再次查看表

数据库读写分离

1
mysql -h127.0.0.1 -P9066 -uroot -p123456 -e 'show @@datasource;'	#查看读写分离状态

注：应显示两台虚拟机，一台读一台写

杂
数据库

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！