Amoeba build a high-availability MySQL cluster (MySQL master-slave replication, read/write separation, load balancing)

What is Amoeba?

Amoeba (amoeba) project, the Open source framework launched a amoeba for MySQL software in 2008. This software is dedicated to MySQL's distributed database front-end agent layer, which is mainly used as the SQL routing function when the application layer accesses MySQL, focusing on the development of the Distributed Database Proxy layer (DB proxy), which is located between the client, DBServer (s), Transparent to the client. With load balancing, high availability, SQL filtering, read and write separation, routable related to the target database, multiple database merge results can be requested concurrently .

With amoeba you can perform high-availability, load-balancing, and data slicing functions for multiple data sources, currently amoeba is used on many enterprise production lines.

Related concepts of distributed database agents

Amoeba in the field of distributed databases will be committed to resolving data segmentation, to meet the client "centralized" processing of distributed data. Here the centralized is a relative concept, the client does not need to know the physical storage of some kind of data. Avoiding this logic on the business side greatly simplifies the complexity of how the client operates distributed data.

Benefits of a distributed database system:

• reduce costs . Distributed databases can be geographically distributed. The structure of the system conforms to the requirements of this distribution. Allow users in their own local recruitment, query, maintenance and other operations, the implementation of local control, reduce communication costs, to avoid the centralized need for higher requirements of hardware equipment. Moreover, the distributed database has a small amount of data on a single machine, and its response speed is significantly improved.
• improve overall system availability . Avoid the consequences of total paralysis due to the failure of a single database.
• easy to expand processing power and system size . The structure of distributed database system can easily extend the system, add a new node in the distributed database, and not affect the normal operation of the existing system. This approach is more flexible and economical than expanding centralized systems. In the centralized system to expand the system and system upgrades, due to hardware incompatibility and software change difficulties and other shortcomings, the cost of upgrading is often expensive and not feasible.

What can amoeba do?

• Transactions are not currently supported
• Stored procedures are not supported for the time being (recently supported)
• Not suitable for scenarios from amoeba or for queries of large data volumes (such as when a request returns more than 10w or more data)
• Temporarily does not support the Sub-Library sub-table, amoeba currently only do sub-database instances, each shard node needs to keep the structure of the library table consistent

Demand case:

There are three database nodes named Master, Slave1, and Slave2 as follows:

1. Master:master (write only)
2. Slaves:slave1, Slave2 (2 equal databases. Read-only/load-balanced)
   The case implementation of master, slaves between the database master-slave replication, read and write separation, load-balanced high-availability MySQL architecture.

Experimental architecture diagram:

System environment:

Host	Operating System	IP Address	Package
Amoeba Server	CentOS 7.0 x86_64	192.168.100.4	Jdk-6u14-linux-x64.bin, amoeba-mysql-binary-2.2.0.tar.gz
master server	CentOS 7.0 x86_64	192.168.100.5	Mysql-5.5.24.tar.gz
Slave1 Server	CentOS 7.0 x86_64	192.168.100.6	Mysql-5.5.24.tar.gz
Slave2 Server	CentOS 7.0 x86_64	192.168.100.7	Mysql-5.5.24.tar.gz
Client	CentOS 7.0 x86_64	192.168.100.3	Mysql

Package: Package Password: IIKF

Start deployment:

Under normal circumstances, our cluster for the intranet environment, can not be connected with the external network synchronization time, then we make the slaves server and master server time synchronization.

First, master server synchronization time 1. Modify the ntp.conf configuration file

vim /etc/ntp.conf

#本地是时钟源
Server 127.127.100.0
#设置时间层级为8 (limit within 15)
Fudge 127.127.100.0 Stratum 8

2. Start the NTPD service

service ntpd start

3. Turn off the firewall and SELinux

service iptables stopsetenforce 0

SLAVE1, Slave2 server sync time 1. Start the NTPD service

service ntpd start

2. Turn off the firewall and SELinux

service iptables stopsetenforce 0

3. Time synchronization

/usr/sbin/ntpdate 192.168.100.4

Second, Master, Slave1, Slave2 install MySQL database respectively 1. Install GCC, gcc-c++, make, CMake ncurses-devel, bison, libaio-devel packages

yum install gcc gcc-c++ make cmake ncurses-devel bisonlibaio-devel -y

2.mysql package extracted to the/OPT directory

tar zxvf mysql-5.5.24.tar.gz -C /opt/

3. Create a MySQL user

useradd  -s /sbin/nologin mysql

4. Create a MySQL directory

mkdir /usr/local/mysql

5. Go to the MySQL directory to install the package

cd /opt/mysql-5.5.24

#配置mysql

Cmake-dcmake_install_prefix=/usr/local/mysql
-dmysql_unix_addr=/home/mysql/mysql.sock
-ddefault_charset=utf8
-ddefault_collation=utf8_general_ci
-dwith_extra_charsets=all
-dwith_myisam_storage_engine=1
-dwith_innobase_storage_engine=1
-dwith_memory_storage_engine=1
-dwith_readline=1
-denabled_local_infile=1
-dmysql_datadir=/home/mysql
-dmysql_user=mysql
-dmysql_tcp_port=3306

6. Compile and install (long time)

make & make install

7. Modify the owner and group information of MySQL directory

chown -R mysql.mysql /usr/local/mysql         

8. Add the environment variables for MySQL

vi /etc/profile

Export path= $PATH:/usr/local/mysql/bin/

9. Update Environment variables

source /etc/profile

10. Copy MySQL default configuration file and startup script

cp /opt/mysql-5.5.24/support-files/my-medium.cnf /etc/my.cnfcp /opt/mysql-5.5.24/support-files/mysql.server /etc/init.d/mysqld          

11. Modify mysqld startup script execution permissions

chmod 755 /etc/init.d/mysqld

12. Add to startup item and INIT3/5 start MySQL service automatically

chkconfig --add /etc/init.d/mysqld                      chkconfig mysqld --level 35 on                           

13. Initializing MySQL Database

/usr/local/mysql/scripts/mysql_install_db \
--user=mysql \
--ldata=/var/lib/mysql \
--basedir=/usr/local/mysql \
--datadir=/home/mysql

14. Establishing a soft connection

ln -s /var/lib/mysql/mysql.sock /home/mysql/mysql.sock

15. Configure the MySQL installation path and the data directory path in the MYSQLD

vi /etc/init.d/mysqld

Basedir=/usr/local/mysql
Datadir=/home/mysql

16. Start the MySQL service

service mysqld start

17. Modify the password of the MySQL administrator (root) user

mysqladmin -u root password ‘pwd123‘

18. Log in to MySQL test

mysql -uroot -p pwd123

19. Turn off the firewall and SELinux

service iptables stopsetenforce 0

Special reminder: The above three MySQL server installation steps like

Third, MySQL master server master server Configuration 1. Modify MY.CNF

vim /etc/my.cnf

Server-id = 1 #修改id号
Log-bin=master-bin #新增, master server log file
Log-slave-updates=true #新增, updating binary logs from the server

2. Restart the MySQL service

service mysqld restart

3. Log in to MySQL

mysql -u root -p

4. Create a user for slaves from server synchronization

GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.100.%‘ IDENTIFIED BY ‘123456‘;

5. The above changes take effect directly

FLUSH PRIVILEGES;

6. View Master status information

show master status;

+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 339 | | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

SLAVE1 from server configuration 1. Modify MY.CNF

vim /etc/my.cnf

Server-id = one #修改id号
Relay-log=relay-log-bin #新增, log files synchronized from the primary server to local
Relay-log-index=slave-relay-bin.index #新增, defining the location and name of the Relay-log

2. Restart the MySQL service

service mysqld restart

3. Log in to MySQL

mysql -u root -p

4. Add the Synchronize data command to the primary server

change master to master_host=‘192.168.100.5‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=339;

5. Turn on slave from the server

start slave;

6. View slave status

show slave status\G;

Slave2 from server configuration 1. Modify MY.CNF

vim /etc/my.cnf

Server-id = #修改id号
Relay-log=relay-log-bin #新增, log files synchronized from the primary server to local
Relay-log-index=slave-relay-bin.index #新增, defining the location and name of the Relay-log

2. Restart the MySQL service

service mysqld restart

3. Log in to MySQL

mysql -u root -p

4. Add the Synchronize data command to the primary server

change master to master_host=‘192.168.100.5‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=339;

5. Turn on slave from the server

start slave;

6. View slave status

show slave status\G;

7. Verify that the master-slave synchronization creates the database on the Master master server:

create database db_test;

View the slave from the server database separately:

show databases;

Iv. Amoeba server 1. Shutting down the firewall and SELinux

service iptables stopsetenforce 0

2. Copy the JDK package and install

cp jdk-6u14-linux-x64.bin /usr/local/./jdk-6u14-linux-x64.bin          #  yes     按enter

3. Modify the JDK directory name

mv jdk1.6.0_14/ /usr/local/jdk1.6

4. Add JDK, JRE, amoeba environment variables

vi /etc/profile

Export java_home=/usr/local/jdk1.6
Export classpath= $CLASSPATH: $JAVA _home/lib: $JAVA _home/jre/lib
Export path= $JAVA _home/lib: $JAVA _home/jre/bin/: $PATH: $HOME/bin
Export Amoeba_home=/usr/local/amoeba
Export path= $PATH: $AMOEBA _home/bin

5. Refresh Environment Variables

source /etc/profile

6. Create a Amoeba directory

mkdir /usr/local/amoeba

7. Unzip the amoeba package to the/usr/local/amoeba directory

tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

8. Modify Amoebab Directory Permissions

chmod -R 755 /usr/local/amoeba/

9. Verify that the amoeba is installed successfully

/usr/local/amoeba/bin/amoeba
#显示amoeba Start|stop instructions for successful installation

10. Add permissions on three MySQL to provide amoeba access 10.1 login MySQL

mysql -u root -p abc123

10.2 Authorized access for Amoeba

grant all on *.* to [email protected]‘192.168.100.%‘ identified by ‘123123‘;

11. Go back to the amoeba server and modify its configuration file

cd /usr/local/amoeba/conf

12. Edit the Amoeba.xml configuration file

vim amoeba.xml

---30 Rows--
<property name= "User" >amoeba</property> #访问amoeba的用户名
----32 Rows---------
<property name= "Password" >123456</property> #密码
---117-Remove comments-
<property name= "Defaultpool" >master</property> #默认为主服务器
<property name= "Writepool" >master</property> #写入为主服务器
<property name= "Readpool" >slaves</property> #读取为slaves池, existing slave1, Slave2 server

13. Modify the Dbservers.xml configuration file

vi dbServers.xml

<dbserver name= "Master" parent= "Abstractserver" > #name为master
<factoryConfig>
<!--mysql IP--
<property name= "ipAddress" >192.168.100.5</property> #主服务器IP地址
</factoryConfig>
</dbServer>
49
<dbserver name= "slave1" parent= "Abstractserver" > #name为slave1
Wuyi <factoryConfig>
<!--mysql IP--
<property name= "ipAddress" >192.168.100.6</property> #从服务器1 IP Address
</factoryConfig>
</dbServer>
56
<dbserver name= "Slave2" parent= "Abstractserver" > #name为slave2
<factoryConfig>
<!--mysql IP--
<property name= "ipAddress" >192.168.100.7</property> #从服务器2 IP Address
</factoryConfig>
</dbServer>
63
<dbserver name= "Slaves" virtual= "true" > #name为slaves
<poolconfig class= "Com.meidusa.amoeba.server.MultipleServerPool" >
<!--Load balancing Strategy:1=roundrobin, 2=weightbased, 3=ha-->
<property name= "LoadBalance" >1</property> #默认为轮询方式
68
<!--separated by Commas,such As:server1,server2,server1--
<property name= "Poolnames" >slave1,slave2</property> #轮询顺序为: slave1, Slave2
</poolConfig>
</dbServer>

14. Start the Amoeba service

/usr/local/amoeba/bin/amoeba start&

15. View Java Services

netstat -anpt | grep java

V. Client 1. Install MySQL Software

yum install -y mysql

2. Log in to the amoeba server

mysql -u amoeba -p123456 -h 192.168.100.4 -P8066

3. Turn off the firewall and SELinux

service iptables stopsetenforce 0

VI. Client Test Access One, verify master-slave replication 1. Create a table on the master server

mysql -u root -pcreate database db_test;use db_test;create table zang (id int(10),name varchar(10),address varchar(20));

On the 2.Master server:

On the 3.SLAVE1 server:

On the 4.SLAVE2 server:

Second, verify read-write separation 1. Turn off synchronization on two Slave1, Slave2 from the server, respectively

stop slave;

2. Insert a record on the client and will not sync to the slave server

insert into zang values(‘1‘,‘zhang‘,‘this_is_master‘);

3.slave1 inserting records from the server

use db_test;insert into zang values(‘2‘,‘zhang‘,‘this_is_slave1‘);

4.slave2 inserting records from the server

use db_test;insert into zang values(‘3‘,‘zhang‘,‘this_is_slave2‘);

5. Test----On the client the first time the data is read from the server Slave1-the second time it reads from the server Slave2

#多次执行该sql语句查看
Use Db_test;
SELECT * from Zang;

III. Verifying load balancing 1. Repeatedly executing query statements on the client, polling access to slave1, Slave2 server

select * from zang;

Amoeba build a high-availability MySQL cluster (MySQL master-slave replication, read/write separation, load balancing)