Shulou(Shulou.com)06/01 Report--

Mysql+amoeba read-write separation

A brief introduction:

Amoeba is a proxy which takes MySQL as the underlying data storage and provides MySQL protocol interface to the application. It responds to the application request centrally and sends the SQL request to a specific database for execution according to the rules set by the user in advance. Based on this, we can achieve load balancing, read-write separation, high availability and other requirements. Compared with MySQL's official MySQL Proxy, the author emphasizes the convenience of amoeba configuration (XML-based configuration files, writing rules in SQLJEP syntax, simpler than MySQL Proxy based on lua scripts).

Amoeba is the equivalent of a router for SQL requests and is intended to provide mechanisms for load balancing, read-write separation, and high availability, rather than fully implementing them. Users need to use MySQL's Replication and other mechanisms to achieve replica synchronization and other functions. Amoeba also uses a pluggable mechanism for underlying database connection management and routing implementation, and third parties can develop more advanced policy classes to replace the author's implementation. This program is generally in line with the idea of the KISS principle.

advantage

Amoeba mainly addresses the following issues:

a)。 Integration of complex data sources after data segmentation

b)。 Provide data segmentation rules and reduce the impact of data segmentation rules on the database

c)。 Reduce database and client connection

d)。 Read-write separate routing

Deficiency

A) currently, transactions are not supported

B), stored procedures are not supported for the time being (will be supported in the near future)

C) scenarios that are not suitable for importing data from Amoeba or query for large data queries (such as situations where more than 10w or more data is returned in one request)

D) currently, database and table segmentation is not supported. Amoeba only divides database instances, and each node that is split needs to keep the database table structure consistent:

Second preparation

1 three centos7 systems (note that I installed the minimal system and need to install some dependent packages through yum, such as: yum-y groupinstall development tools. It is recommended to install DVD version)

Amoeba:192.168.161.141

Msysql master:192.168.161.142

Mysql slave:192.168.161.143

2 install jdk-7u80-linux-x64.tar.gz and amoeba-mysql-3.0.5-RC-distribution.zip on Amoeba

3 install MySQL-client-5.6.6_m9-1.rhel5.x86_64.rpm on Mysql,

MySQL-server-5.6.6_m9-1.rhel5.x86_64.rpm

Three, turn off the firewall and selinux

Systemctl stop firewalld

Systemctl disable firewalld

Setenforce 0

Install mysql on 161.142 and 161.143, and configure master-slave synchronization.

Mysql is installed on 1 161.142 and 161.143

Rpm-ivh MySQL-server-5.6.6_m9-1.rhel5.x86_64.rpm

Rpm-ivh MySQL-client-5.6.6_m9-1.rhel5.x86_64.rpm

2 modify the configuration file

Mysql master (161.142):

[root@localhost home] # cat / etc/my.cnf

[mysqld]

Log-bin=mysql-bin

Server-id=1

New configuration in binlog-ignore-db=information_schema #, ignoring synchronization of information_schema

Mysql slave (161.143):

[root@localhost home] # cat / etc/my.cnf

[mysqld]

Log-bin=mysql-bin

Server-id=2

3 start two mysql and configure the master and slave

(1) start mysql:

Systemctl start mysql

(2) modify the default password of mysql:

Mysqladmin-uroot password '123'

(3) enter 161.142 mysql master:

[root@localhost home] # mysql-uroot-p123

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000004 | 120 | | information_schema |

+-+

1 row in set (0.00 sec)

(4) enter 161.143 mysql slave:

Execute:

Configure the slave server:

Change master to master_host='192.168.161.142',master_user='root',master_password='123'

Master_log_file='mysql-bin.000004',master_log_pos=120

Start the replication function from the server:

Start slave

View the status of replication from the server:

Mysql > show slave status\ G'

1. Row

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.161.142

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 120

Relay_Log_File: localhost-relay-bin.000009

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 120

Relay_Log_Space: 623

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: f2e02c4b-f6c3-11e7-a14e-66ab28c66abe

Master_Info_File: / var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

1 row in set (0.00 sec)

If Slave_IO_Running: Yes,Slave_SQL_Running: Yes is both, there is no problem.

(5) Test: slightly

5. Configure amoeba above 161.141

The Java environment is built on 1 161.141.

(1) decompress the Java package

Tar-xzvf jdk-7u80-linux-x64.tar.gz

(2) configure environment variables

Modify the configuration file:

Vi / etc/profile

JAVA_HOME=/home/jdk1.7.0_80

CLASSPATH=.:$JAVA_HOME/lib.tools.jar

PATH=$JAVA_HOME/bin:$PATH

Export JAVA_HOME CLASSPATH PATH

Make the configuration file effective immediately:

Source / etc/profile

Verify Java:

[root@localhost home] # java-version

Java version "1.7.080"

Java (TM) SE Runtime Environment (build 1.7.0_80-b15)

Java HotSpot (TM) 64-Bit Server VM (build 24.80-b11, mixed mode)

2 configure amoeba profile

(1) download

Wget-c

Https://ncu.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip

(2) decompression

Unzip amoeba-mysql-3.0.5-RC-distribution.zip-d / usr/local/amoeba

(if there is no unzip command, please install it yourself: yum install-y unzip zip)

(3) authorize mysql remote account to amoeba (root is not recommended)

Grant all on. To amoeba@ "" identified by "amoeba"

Flush privileges

(4) modify the configuration file

Amoeba only needs to modify the dbServers.xml and amoeba.xml configuration files to do read-write separation.

[root@localhost amoeba-mysql-3.0.5-RC] # cd / usr/local/amoeba/amoeba-mysql-3.0.5-RC/conf/

[root@localhost conf] # ls

Access_list.conf amoeba.dtd amoeba.xml dbserver.dtd dbServers.xml function.dtd functionMap.xml log4j.dtd log4j.xml rule.dtd ruleFunctionMap.xml rule.xml

Modify dbServers.xml

[root@localhost conf] # cat dbServers.xml

${defaultManager} 64 128 3306 test amoeba# sets the account and password for amoeba to connect to the backend database server, so you need to create the user on all backend databases And authorize the amoeba server to connect to amoeba 500500 1 600000 600000 true 192.168.161.142 192.168.161.143 1 slave

[root@localhost conf] #

Modify amoeba.xml

[root@localhost conf] # more amoeba.xml

8066 12864 amoeba# needs to use the account set here when the client connects to amoeba (the account password here has nothing to do with the password of amoeba connecting to the backend database server) amoeba ${amoeba.home} / conf/access_list.conf 128500 utf8 60 com.meidusa.toolkit. Net.AuthingableConnectionManager ${amoeba.home} / conf/dbServers.xml ${amoeba.home} / conf/rule.xml ${amoeba.home} / conf/ruleFunctionMap.xml ${amoeba.home} / conf/functionMap.xml 1500 master master slave # the original comments on these two lines are to remove true

(5) start amoeba

[root@server3 amoeba] # bin/launcher

The stack size specified is too small, Specify at least 228k

Error: Could not create the Java Virtual Machine.

Error: A fatal exception has occurred. Program will exit.

From the point of view of the error text, it should be due to the fact that the stack size is too small, resulting in the failure of JVM startup. Modify the JVM_OPTIONS parameter of the jvm.properties file.

Jvm.properties (/ usr/local/amoeba/amoeba-mysql-3.0.5-RC)

Set

JVM_OPTIONS= "- server-Xms256m-Xmx1024m-Xss196k-XX:PermSize=16m-XX:MaxPermSize=96m"

Change to

JVM_OPTIONS= "- server-Xms1024m-Xmx1024m-Xss256k"

Resolve the startup xss parameters required by jdk7 above.

[root@server3 amoeba] # netstat-unlpt | grep java # # View the listening port

Tcp 0 0: 8066: * LISTEN 1506/java

Turn amoeba on / off

It is best to start in the foreground first, and then start in the background after checking for no errors.

Close

# / usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/shutdown

Start

# / usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/launcher

Start in the background and save the log to / var/log/amoeba.log

# / usr/local/amoeba/amoeba-mysql-3.0.5-RC/bin/launcher > / var/log/amoeba.log 2 > & 1 &

(6) Note:

The account passwords in dbServers.xml and amoeba.xml configuration files are all accounts in mysql database. If you enter them incorrectly, there will be errors:

ERROR 1000 (42S02): Access denied for user 'amoeba'@'192.168.161.142:57952' (using password: YES)

Six tests:

1 connect amoeba on 161.142

(1) shut down master database 161.142 and test whether it is read-only

Systemctl stop mysql

[root@localhost etc] # mysql-uamoeba-pamoeba-h292.168.161.141-P8066

Mysql > use test

Database changed

Mysql > select * from b

+-+ +

| | sf | ff |

+-+ +

| | 1 | 1 |

| | 3 | 3 |

| | 5 | 5 |

+-+ +

3 rows in set (0.01sec)

Mysql > insert into b values (5, 5)

ERROR 1044 (42000): Amoeba could not connect to MySQL server [192.168.161.142 Connection refused]

Mysql >

(2) shut down slave database 161.143 and test whether it can only be written but not read

Mysql > use test

Database changed

Mysql > select * from b

ERROR 1044 (42000): Amoeba could not connect to MySQL server [192.168.161.143 Connection refused 3306]

Mysql > insert into b values (5, 5)

Query OK, 1 row affected (0.03 sec)

Seven draw lessons from the website

Http://blog.csdn.net/oufua/article/details/77373851

Http://blog.csdn.net/sds15732622190/article/details/69262632

Http://blog.csdn.net/Mryiyi/article/details/73521861

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.