| |
|
|
|
|
|
| |
Home
\
Database \
To deploy MySQL database with separate read and write OneProxy |
|
|
- Linux operating system must know the security command (Linux)
- Fedora 20, Fedora 19, CentOS 6 and RHEL6 users how to install Wine 1.7.15 (Linux)
- Linux Network Programming - raw socket instance: MAC header message analysis (Programming)
- Use ISO document production OpenStack used CoreOS mirror (Linux)
- C # get the current screenshot (Programming)
- Ten to improve the efficiency of the Linux bash tricks (Linux)
- Radius server setup under CentOS (Server)
- Installation and use GAMIT / GLOBK Software (Linux)
- Linux Nginx installation and configuration instructions (Server)
- Installation and configuration to compile MySQL 5.6.10 under CentOS 5.9 (Database)
- Terminal multiplexing tool tmux use (Linux)
- Figlet use (Linux)
- Ubuntu 15.10 under Python + Apache + CGI fully configured (Server)
- CentOS6.6 ordinary users to use sudo command to borrow root user privileges (Linux)
- Java memory analysis tool uses detailed MAT (Programming)
- Spring WebSocket Comments (Programming)
- Ubuntu 14.04, 13.10 install OpenCV 2.4.9 (Linux)
- OpenStack package problems and solutions under CentOS6 (Linux)
- Oracle to use full-text indexing (Database)
- Linux firewall anti-hacker disguise malicious attacks (Linux)
|
|
| |
|
|
|
|
| |
To deploy MySQL database with separate read and write OneProxy |
|
|
|
| |
Add Date :
2018-11-21 |
|
|
|
| |
|
|
|
|
| |
|
|
|
|
| |
|
|
|
|
| |
Experimental System: CentOS 6.6_x86_64
Experimental conditions: Close all firewall and selinux
Experiment Description: In this study, a total of four hosts, IP allocation as topology
Experimental Software: mariadb-10.0.20 oneproxy-rhel5-linux64-v5.6-ga
Note: This experiment is to expand the MySQL-Proxy before the experiment, so most of the environment and the setting is the same, one, two steps are not repeated here, specifically refer to: the use of MySQL-Proxy MySQL database to read and write separation
First, the preparatory work
Second, configure the master-slave replication
Third, the installation oneproxy
1. This experiment was 19.79 onproxy server, so the software is installed on the host:
tar xf oneproxy-rhel5-linux64-v5.6-ga.tar.gz -C / usr / local /
cd / usr / local / oneproxy
vim demo.sh
-------------------------------------------------- -------------->
# / Bin / bash
#
export ONEPROXY_HOME = / usr / local / oneproxy
# Valgrind --leak-check = full --show-reachable = yes \
$ {ONEPROXY_HOME} / oneproxy --keepalive \ // automatically check and restart services OneProxy
--proxy-address = 192.168.19.79: 3306 \ // Proxy Server first listener address
--admin-address = 192.168.19.79: 4041 \ // management port address
--proxy-master-addresses = 192.168.19.66: 3306 @ server1 \ // Master node address (writable node), which is behind the @ "Server Group" name, if not specified, it defaults to "default"
--proxy-slave-addresses = 192.168.19.74: 3306 @ server1 \ // Slave node address (read node)
--proxy-slave-addresses = 192.168.19.76: 3306 @ server1 \
After --proxy-user-list = test / 14B1772BA9874974E6ECEA0745EE774930E2EFA3 \ // Proxy user list (username / password), has completed the configuration back to step 2
--proxy-part-tables = $ {ONEPROXY_HOME} /part.txt \
--proxy-found-rows \
--event-threads = 4 \ // OneProxy number of worker threads
--proxy-group-policy = server1: 2 \ // define the routing access policy MySQL Cluster, where the configuration is read from the Slave, if the Slave side is not available, then from the end to read Master
--proxy-group-security = server1: 0 \ // define security access policies MySQL Cluster
--proxy-memory-db = root / @ :: test \
--proxy-memory-engine \
--proxy-sql-autosave = $ {ONEPROXY_HOME} /sql_firewall.sql \
--log-file = $ {ONEPROXY_HOME} /oneproxy.log \
--pid-file = $ {ONEPROXY_HOME} /oneproxy.pid
2. Check the password characters and configure:
chmod + x demo.sh
./demo.sh
mysql -uadmin -pOneProxy -h192.168.19.79 --port = 4041 // into the management interface
-------------------------------------------------- ------------------>
passwd 'RedHat'; // Check password characters, and written in demo.sh
+ -------- + ---------------------------------------- - +
| TEXT | PASSWORD |
+ -------- + ---------------------------------------- - +
| Redhat | 14B1772BA9874974E6ECEA0745EE774930E2EFA3 |
+ -------- + ---------------------------------------- - +
3. Create a test user on the master:
/ Usr / local / mysql / bin / mysql
----------------------------------------------->
. GRANT ALL ON * * TO 'test'@'192.168.19.%' IDENTIFIED BY 'redhat'; // here to demo.sh and the Proxy user list information consistent
FLUSH PRIVILEGES;
4. Back oneproxy server startup process:
killall -9 oneproxy // to clean out the previous process
./demo.sh
tail -f oneproxy.log // View Log
mysql -uadmin -pOneProxy -h192.168.19.79 --port = 4041
-------------------------------------------------- --------------->
LIST BACKEND;
+ -------------------- + ----------- + ------ + -------- + -------- + ---------- + --------- + -------- + ------ + ---- - + ------- + --------- + ------- + --------- + ------- + --- ------ + ------ + ------ + --------- +
| INDX | ADDRESS | TYPE | STATUS | MARKUP | REQUESTS | DEGREE | GROUP | IS_M | IS_S | MFile | MOffset | DFile | DOffset | RFile | ROffset | IO | SQL | Seconds |
+ -------------------- + ----------- + ------ + -------- + -------- + ---------- + --------- + -------- + ------ + ---- - + ------- + --------- + ------- + --------- + ------- + --- ------ + ------ + ------ + --------- +
| 1 | 127.0.0.1:3306 | RW / Master | UP | 0 | 0 | 0 | | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | 192.168.19.66:3306 | RW / Master | UP | 1 | 0 | 0 | server1 | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3 | 192.168.19.74:3306 | RO / Slave | UP | 1 | 0 | 0 | server1 | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4 | 192.168.19.76:3306 | RO / Slave | UP | 1 | 0 | 0 | server1 | No | No | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+ -------------------- + ----------- + ------ + -------- + -------- + ---------- + --------- + -------- + ------ + ---- - + ------- + --------- + ------- + --------- + ------- + --- ------ + ------ + ------ + --------- +
5. respectively on three mariadb server Ethereal:
master:
tcpdump -i eth0 -nn -XX ip dst 192.168.19.66 and tcp dst port 3306
slave1:
tcpdump -i eth0 -nn -XX ip dst 192.168.19.74 and tcp dst port 3306
slave2:
tcpdump -i eth0 -nn -XX ip dst 192.168.19.76 and tcp dst port 3306
6. Create a database on oneproxy and reads:
mysql -utest -predhat -h192.168.19.79 // connect to oneproxy
Database command is no longer here to write, we can we can see from the picture, all write operations are performed on the master, reads are load balanced to the slave. |
|
|
|
| |
|
|
|
|
| |
|
|
|
|
| |
|
|
|
|
| |
More: |
|
|
|
| |
|
|
|
|
| |
|
|
|
|
|
|