MySQL transcription 6:mysql read/write separation

Amoeba

• With MySQL as the underlying data store, and a proxy for the application to provide the MySQL protocol interface
• Centrally want to use the application's request, according to the user's pre-set rules, send the SQL request to a specific database execution
• This allows for load balancing, read-write separation, high availability, and more
• It is emphasized that the amoeba configuration is convenient, XML-based configuration file, with Sqljep grammar writing rules
• A router that is equivalent to a SQL request
• It is necessary to combine MySQL replication and other mechanisms to achieve the functions of copy synchronization.
• Pluggable mechanisms are used for the underlying database connection management and routing implementations

Mysql-proxy

• Official tools
• LUA-based scripting

Separation based on program read and write

• Write a library, read a library
• Use the scene?

Mysql-proxy Read/write separation

• Configuring MySQL read-write separation

Yum install mysql-server MySQL mysql-devel-  y

• MY.CNF of Master Library

[mysqld] DataDir=/var/lib/MySQL Socket=/var/lib/mysql/Mysql.sock User=MySQL # disabling symbolic-links are recommended to prevent assorted security risks symbolic-links=0Log-bin=mysql-Bin Server-ID=1Auto_increment_offset=1auto_increment_increment=2[Mysqld_safe] Log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/Mysqld.pid Replicate- Do-db =all

Master my.cnf

• The my.cnf of Slave Library

[mysqld] DataDir=/var/lib/mysql/Socket=/var/lib/mysql/Mysql.sock User=MySQL # disabling symbolic-links are recommended to prevent assorted security risks symbolic-links=0Log-bin=mysql-Bin Server-ID=2Auto_increment_offset=2auto_increment_increment=2[Mysqld_safe] Log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/Mysqld.pid Master-connect-retry= -Replicate- Do-db =all

slave my.cnf

• Master-Slave synchronization authorization
• Mysql-proxy Installation

wget wget https://downloads.mariadb.com/archives/mysql-proxy/ mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz  useradd-r mysql-proxytar zxvf mysql-proxy-0.8. 3-LINUX-GLIBC2. 3-x86-64bit. tar. gz-c/usr/localmv /usr/local/mysql-proxy-0.8. 3-LINUX-GLIBC2. 3-x86-64bit/usr/local/mysql-proxy

• Setting environment variables

Export path= $PATH:/usr/local/mysql-proxy/bin/echo"path= $PATH:/usr/local/mysql-proxy/bin "

The relevant parameters of mysql-proxy are as follows: --help-all: Get all help information;--proxy-address=Host:port: The address and Port of the agent service listener;--admin-address=Host:port: The address and port that the management module listens to;--proxy-backend-addresses=Host:port: The address and port of the backend MySQL server;--proxy-read-only-backend-addresses=Host:port: The address and port of the backend read-only MySQL server;--proxy-lua-script=file_name: The Lua script that completes the MySQL proxy function;--daemon: Starting mysql-in daemon modeproxy;--keepalive: In mysql-proxy crashes when attempting to restart;--log-file=/path/to/log_file_name: Log file name;--log-level=level: log levels;--log-use-syslog: Log logging based on syslog;--plugins=plugin: In mysql-the plug-in loaded at proxy startup;--user=user_name: Running mysql-the user of the proxy process;--defaults-file=/path/to/conf_file_name: The configuration file path used by default, and its configuration section using [mysql-Proxy] identification;--proxy-skip-Profiling: Disable profile;--pid-file=/path/to/pid_file_name: Process file name;

• Start Mysql-proxy

Mysql-proxy--daemon--log-level=debug--user=mysql-proxy--keepalive--log-file=/var/log/mysql-proxy.log--plugins="Proxy"--proxy-backend-addresses="192.168.1.14:3306"--proxy-read-only-backend-addresses="192.168.1.15:3306"--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"--plugins=admin--admin-username="Admin"--admin-password="Admin"--admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

• viewing ports and testing

Netstat-ntpl | grep mysql--h192. 168.1.  -uadmin-p-P4041Select * from Backends;

• Data write and test using port 4040

Problem:

How did the VIP arise?

MySQL transcription 6:mysql read/write separation