Centos7 MySQL 5.7 via amoeba read/write separation

Centos7 MySQL read and write separate MySQL introduction

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications.
MySQL is a relational database management system, where relational databases store data in different tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.
The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software has adopted a dual licensing policy, divided into community and commercial version, due to its small size, fast, low total cost of ownership, especially the open source, the development of the general small and medium-sized web site to choose MySQL as the site database.

Pre-Installation Preparation

3 MySQL servers (this time using 3 5.7 versions of MySQL)

One CentOS server (used as installation amoeba)

Amoeba installation package Java environment Package

Experimental steps to turn off firewall self-booting, as well as related features and enhanced security features

 systemctl stop firewalld.service  setenforce 0

Time synchronization

yum install ntp -yvim /etc/ntp.conf在配置文件中添加   server 127.127.100.0    ##本地是时钟源fudge 127.127.100.0 stratum 8    ##设置时间层级为8service ntpd start    ##开启服务/usr/sbin/ntpdate 192.168.100.100   ##进行时间同步

Configuration on MySQL master server

vim /etc/my.cnf在[mysqld] 下添加server-id=1log-bin=master-bin      ##主服务器日志文件log-slave-updates=true      ##从服务器更新二进制日志service mysqld restartmysql -u root -p     ##进入mysql数据库GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.100.%‘ IDENTIFIED BY ‘123456‘;             ##  给予回应从服务器的权限FLUSH PRIVILEGES;    ##刷新数据show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000002 |      339 |              |                  |+-------------------+----------+--------------+------------------+1 row in set (0.00 sec)

MySQL from server configuration

vim /etc/my.cnf在 [mysqld]下添加server-id=11relay-log=relay-log-bin      ##从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index     ##定义relay-log的位置和名称##   第二台从服务器的 serverid不能相同service mysqld restart   ##重启服务mysql -u root -p    ##进入mysql数据库change master to master_host=‘192.168.100.100‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000002‘,master_log_pos=339;     ##配置主服务器start slave；   ##开启同步show slave status \G;     ##查看同步状态

Verify that the master-slave server is turned on successfully

在主服务器上create database test;在从服务器上show databases;能看到test 则说明成功

Install the Amoeba service and configure it

It is recommended that you use the Java 1.6 Environment Pack CP Jdk-6u14-linux-x64.bin/usr/local//usr/local/jdk-6u14-linux-x64.bin go down and enter Yes, and then press ENTER for a moment. Configure complete JAVA environment MV jdk1.6.0_14//usr/local/jdk1.6vim/etc/profile Add export java_home=/usr/local/jdk1.6export CLASSPATH= at the end $CLASSPATH: $JAVA _home/lib: $JAVA _home/jre/libexport path= $JAVA _home/lib: $JAVA _home/jre/bin/: $PATH: $HOME/ Binexport amoeba_home=/usr/local/amoebaexport path= $PATH: $AMOEBA _home/binsource/etc/profile # #使配置生效mkdir/usr/ Local/amoeba # #创建amoeba服务器位置tar ZXVF amoeba-mysql-binary-2.2.0.tar.gz-c/usr/local/amoeba/# #解压amoebachmod-R 755/ Usr/local/amoeba # #给予amoeba权限/usr/local/amoeba/bin/amoeba# #显示amoeba start| Stop instructions installed successfully on three MySQL add permissions open to amoeba access grant all on * * to [email protected] ' 192.168.100.% ' identified by ' 123.com '; Back to Amoeba server Cd/usr/local/amoebavim conf/amoeba.xml---30 rows--# #用户名 <property name= "user" >amoeba</property >----32 Lines---------# #密码 <property name= "password" >123456</property>---117-uncomment-<property name= " Defaultpool ";master</property> # #默认池 <property name= "Writepool" >master</property> # #写入池 <property na Me= "Readpool" >slaves</property> # #读取池vi conf/dbservers.xml--23--Common database name for MySQL server--<property name= " Schema ">mysql</property>--26-29--remove annotations-<property name=" user ">test</property> <property Name= "Password" >123.com</property>-----42-Primary Server address---<dbserver name= "master" parent= "Abstractserver"  > <property name= "ipAddress" >192.168.100.100</property>--52-from server host name-<dbserver name= "Slave1" Parent= "Abstractserver" >--55-from server address-<property name= "IpAddress" >192.168.100.101</property> The slave2 and its IP address will be copied and configured from 6 rows of the server-----63-from the server address pool---<dbserver name= "slaves" virtual= "true" > <poolconfig--End--< Property Name= "Poolnames" >slave1,slave2</property> </poolconfig>/usr/local/amoeba/bin/amoeba start & # #开启服务 NETSTAT-ANPT | grep Java sees 8066 indicating that the service is successfully opened to see the address of the master-slave server indicates successful connection database

Test

在一台已经安装mysql服务器的电脑上mysql -u amoeba -p123456 -h 192.168.100.100 -P8066## 通过amoeba 登录--在主服务器上--create database zzz；create table test (id int(10),name varchar(10),address varchar(20));--在两台从服务器上--stop slave；     ##关闭同步--在主服务器上--use zzz；insert into test values(‘1‘,‘abc‘,‘this_is_master‘);--在从服务器1上--use zzz；insert into test values(‘2‘,‘abc‘,‘this_is_slave1‘);--在从服务器2上--use zzz；insert into test values(‘3‘,‘abc‘,‘this_is_slave2‘);------在客户端上测试----第一次会向从服务器1读数据-第二次会各从2读取select * from test;一次显示id为2的数据  一次显示id为3的数据-------------在通过客户端连接数据库后写入的数据只有主会记录，然后同步给从-从服务器不会记录，从而实现了读写分离。----insert into test values(‘5‘,‘abc‘,‘this_is_client‘);--在主服务器上可以看到写入的数据--select * from test;可以看到id 为 1 和 5的数据--在从服务器上--start slave； 开启同步--在客户端--select * from test;一次会显示 id为 1 2 5 的数据一次会显示 id 为 1 3 5 的数据

The MySQL read-write separation installation is complete

Centos7 MySQL 5.7 via amoeba read/write separation