vlambda博客
学习文章列表

安装mysql

yum -y install mariadb mariadb-server#启动并设置开机自启systemctl enable mariadb
systemctl start mariadb

mysql主从备份

#开启二进制日志

vim /etc/my.cnf 
	log-bin=mysql-bin
	server-id=24
	binlog-ignore-db=mysql
	binlog-ignore-db=information_schema

#重启mysql

systemctl restart mariadb

#主服务器授权

grant replication slave on *.* to slave@'192.168.80.80' identified by '123456';

#查看log日志状态

show master status;

#从服务器保存授权

change master to master_user='slave',master_password='123456',master_host='192.168.80.70',master_log_file='mysql-bin.000001',master_log_pos=398;

##成功后会有文件保存了连接信息
#/var/lib/mysql/master.info

#开启从服务器

start slave;

#查看从服务器状态
#Slave_IO_Running和Slave_SQL_Running 为yes即为开启成功

show slave status\G;

#测试
主服务器更新数据会同步到从服务器，从服务器更新数据不会同步到主服务器，若要相互同步，则采用主主模式。

Mysql主主备份

#步骤与主从类似，就是把主从服务器互换再配置一次实现主主备份。但要修改配置防止冲突，其他的步骤参考步骤一。

#修改二进制日志

vim /etc/my.cnf 
	log-bin=mysql-bin
	server-id=24  	#将不必要的资源不写入bin-log日志
	binlog-ignore-db=mysql
	binlog-ignore-db=information_schema	#防止主键冲突 主键从1开始，每次加2
	auto-increment-increment=2
	auto-increment-offset=1 #从服务器设置为2

安装amoeba实现读写分离

#开启第三台服务器安装amoeba

#安装所需依赖

yum -y install gcc*

#将文件上传到服务器

#解压压缩包

unzip amoeba-n.zip

#配置java环境
#解压文件夹，配置环境变量

tar -zxvf jdk-7u40-linux-x64.gz

#创建软连接缩短命令

ln -s jdk1.7.0_40/ jdk

vim /etc/profile
	JAVA_HOME=/root/amoeba-n/jdk #jdk文件位置
	export JAVA_HOME
	
	PATH=$JAVA_HOME/bin:$PATH
	export PATH
	
	CLASSPATH=.:$JAVA_HOME/bin/tools.jar:$JAVA_HOME/lib/dt.jar:$CLASSPATH
	export CLASSPATH

#重新加载文件

source /etc/profile

#测试java是否成功

java -version

#安装amoeba
#解压并指定位置

unzip amoeba-mysql-1.3.1-BETA.zip -d /usr/local/amoeba

#修改权限

chmod -R +x /usr/local/amoeba/bin/

#修改配置文件
vim /usr/local/amoeba/conf/amoeba.xml

#修改server1，并新增sever2

#我的文件内容

<?xml version="1.0" encoding="gbk"?><!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd"><amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/">
	<server>
		<property name="port">8066</property>
		<property name="ipAddress">192.168.80.80</property>
		<property name="readThreadPoolSize">20</property>
		<property name="clientSideThreadPoolSize">30</property>
		<property name="serverSideThreadPoolSize">30</property>
		<property name="netBufferSize">128</property>
		<property name="tcpNoDelay">true</property>
		<property name="user">hhh</property>
		<property name="password">password</property>
		<property name="queryTimeout">60</property>
	</server>
	<connectionManagerList>
		<connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
			<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>
		</connectionManager>
	</connectionManagerList>
	
	<dbServerList>
		<dbServer name="server1">
			<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
				<property name="manager">defaultManager</property>
				<property name="port">3306</property>
				<property name="ipAddress">192.168.80.70</property>
				<property name="schema">test</property>
				<property name="user">hhh</property>
				<property name="password">123456</property>
			</factoryConfig>
			<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
				<property name="maxActive">200</property>
				<property name="maxIdle">200</property>
				<property name="minIdle">10</property>
				<property name="minEvictableIdleTimeMillis">600000</property>
				<property name="timeBetweenEvictionRunsMillis">600000</property>
				<property name="testOnBorrow">true</property>
				<property name="testWhileIdle">true</property>
			</poolConfig>
		</dbServer>
		
		<dbServer name="server2">
			<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
				<property name="manager">defaultManager</property>
				<property name="port">3306</property>
				<property name="ipAddress">192.168.80.30</property>
				<property name="schema">test</property>
				<property name="user">hhh</property>
				<property name="password">123456</property>
			</factoryConfig>
			<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
				<property name="maxActive">200</property>
				<property name="maxIdle">200</property>
				<property name="minIdle">10</property>
				<property name="minEvictableIdleTimeMillis">600000</property>
				<property name="timeBetweenEvictionRunsMillis">600000</property>
				<property name="testOnBorrow">true</property>
				<property name="testWhileIdle">true</property>
			</poolConfig>
		</dbServer>

		<dbServer name="write" virtual="true">
			<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
				<property name="loadbalance">1</property>
				<property name="poolNames">server1</property>
			</poolConfig>
		</dbServer>
		<dbServer name="read" virtual="true">
			<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
				<property name="loadbalance">1</property>
				<property name="poolNames">server1,server2</property>
			</poolConfig>
		</dbServer>
	</dbServerList>
	<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
		<property name="ruleConfig">${amoeba.home}/conf/rule.xml</property>
		<property name="functionConfig">${amoeba.home}/conf/functionMap.xml</property>
		<property name="ruleFunctionConfig">${amoeba.home}/conf/ruleFunctionMap.xml</property>
		<property name="LRUMapSize">1500</property>
		<property name="defaultPool">write</property>
		<property name="writePool">write</property>
		<property name="readPool">read</property>
		<property name="needParse">true</property>
	</queryRouter></amoeba:configuration>

#修改配置文件，将-Xss128k 修改为 -Xss256k
vim /usr/local/amoeba/bin/amoeba

#主从服务器分别授权给amoeba

grant all on *.* to hhh@'192.168.80.80' identified by '123456';

#后台运行amoeba

nohup bash -x /usr/local/amoeba/bin/amoeba &

#查看运行情况
ps aux | grep amoeba

测试

mysql -uhhh -p password -h 192.168.80.80 -P8066

#查看数据，由于从服务器更新数据不会同步到主服务器，在从服务器加入两条数据区分主从服务器，分别观察效果。

#插入数据测试

资料下载

amoeba和jdk源码包create.sql.gz文件下载