Nginx Load Balancer +mysql master-slave copy, read/write Separation +tomcat Project
Architecture: (only two virtual machines due to limited resources)
1192.168.137.3Nginx, Mysql (master), TOMCAT1, TOMCAT2
2192.168.137.5Mysql (from)
System version: CentOS 7
192.168.137.3
1, first install Nginx, convenient for later use
2. Install MySQL
File/usr/share/mysql/charsets/swe7.xml from install of mysql-server-5.6.13-1.linux_glibc2.5.x86_64 conflicts with File from Package mariadb-libs-1:5.5.52-1.el7.x86_64
Yum Remove Mariadb-libs
[Email protected] local]# RPM-IVH mysql-server-5.6.13-1.linux_glibc2.5.x86_64.rpm
In preparation ... ################################# [100%]
Upgrading/installing ...
1:mysql-server-5.6.13-1.linux_glibc################################# [100%]
Can ' t locate data/dumper.pm in @INC (@INC contains:/usr/local/lib64/perl5/usr/local/share/perl5/usr/lib64/perl5/ Vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5.) at/usr/bin/mysql_install_db Line 42.
BEGIN failed--compilation aborted at/usr/bin/mysql_install_db line 42.
[[email protected] local]# yum Install ' Perl (Data::D umper) '
[Email protected] local]# rpm-e mysql-server
[Email protected] local]# RPM-IVH mysql-server-5.6.13-1.linux_glibc2.5.x86_64.rpm
In preparation ... ################################# [100%]
Upgrading/installing ...
1:mysql-server-5.6.13-1.linux_glibc################################# [100%]
Be sure to remember to initialize the!!!!! I'm so drunk.
[Email protected] ~]# mysql_install_db
[Email protected] ~]# CP/USR/SHARE/MYSQL/MY-DEFAULT.CNF/ETC/MY.CNF
[[Email protected] ~]# service MySQL start
Starting MySQL. error! The server quit without updating PID file (/var/lib/mysql/localhost.localdomain.pid).
[[email protected] ~]# vi/etc/my.cnf and add log-error =/var/log/mysql.log in config file, log file is generated at initialization
[Email protected] ~]# chown-r mysql:mysql/var/lib/mysql/
[[Email protected] ~]# service MySQL start
Starting MySQL. success!
/usr/bin/mysqladmin-u root password ' 123123 '
3. Install Tomcat
Installing the JDK
Unzip into the directory
java_home=/usr/local/jdk1.7.0_09
Classpath= $JAVA _home/lib/
Path= $PATH: $JAVA _home/bin
Export PATH java_home CLASSPATH
[Email protected] jdk1.7.0_09]# Vi/etc/profile
[Email protected] jdk1.7.0_09]# Source/etc/profile
[Email protected] jdk1.7.0_09]# java-version
Java Version "1.7.0_09"
Java (TM) SE Runtime Environment (build 1.7.0_09-b05)
Java HotSpot (TM) 64-bit Server VM (build 23.5-b02, Mixed mode)
Installing Tomcat
Unzip into the directory
[Email protected] local]# MV apache-tomcat-6.0.44 Tomcat
[Email protected] local]# Vi/etc/profile
Export Tomcat_home=/usr/local/tomcat
Installing TOMCAT2
Unzip into the directory
[Email protected] local]# MV apache-tomcat-6.0.44 TOMCAT2
[Email protected] local]# Vi/etc/profile
Export TOMCAT_HOME2=/USR/LOCAL/TOMCAT2
Modify the appropriate port
Like 8081.
Close the FIREWALLD client test
192.168.137.5 installing MySQL (as with the above installation)
4, configure MySQL read and write separation, the first to set the master-slave
Main MySQL:
#GRANT REPLICATION slave,reload,super on * * to ' CP ' @ '% ' identified by ' 123123 ' with GRANT OPTION;
Note: The above line cannot be used!!!! When configuring read-write separation, the operation database permissions are insufficient, with the following statement!!!!!!
Grant all privileges on * * to ' CP ' @ '% ' identified by ' 123123 ' with GRANT OPTION;
From MySQL:
Editing a configuration file
Server-id = 222
Relay-log =/data/relaylogs/relay-bin
mysql> change MASTER to master_host= ' 192.168.137.3 ', master_port=3306,master_user= ' CP ', master_password= ' 123123 ', Master_log_file= ' mysql-bin.000004 ', master_log_pos=2186726;
ERROR (HY000): File '/data/relaylogs/relay-bin.index ' not found (Errcode:13-permission denied)
The reason is:/data/relaylogs/relay-bin.index This file is not found, need to touch this file!!!!
After the above command succeeds, start slave
Then use: Show slave status\g to see if it's correct
Issue 1: Change the existing library from MySQL error slave_sql_running:no
Workaround:
mysql> Reset Slave;
mysql> stop Slave;
Mysql> set GLOBAL sql_slave_skip_counter=1;
mysql> start slave;
Issue 2: A database that already exists on Master cannot be synced to slave
Workaround:
Reconfigure master-Slave!!!!!!!!!!!!!!!
Reconfigure master-Slave---then the above operation
1, enter the main library, the lock table, to prevent data write
Mysql> flush tables with read lock;
2. From the library, directly back up the remote database to the local directory
Mysqldump-h 192.168.137.3-ucp-p123123--databases Erpback >erpback.bak.sql
3, from the library, stop slave
mysql> stop Slave;
4. Create a new Erpback library and import the data
mysql> CREATE DATABASE Erpback;
mysql> use Erpback;
Mysql> Source/root/erpback.bak.sql
5. On the main library, unlock the table, and view the binary log information, and record
mysql> unlock tables;
Mysql> Show master status;
6. Reconfigure the connection information from the library
Change MASTER to master_host= ' 192.168.137.3 ', master_port=3306,master_user= ' CP ', master_password= ' 123123 ', master_ Log_file= ' mysql-bin.000006 ', master_log_pos=120;
7. Start slave; view master and slave status
mysql> start slave;
Mysql> Show Slave Status\g
8, on the main library, update the Erpback library data, and check from the library is working properly!!
Slave_io_running:yes
Slave_sql_running:yes
Issue 3: After restarting the system the next day, show slave status\g displays an error:
Slave_io_running:no
Slave_sql_running:no
Start start slave, display error:
ERROR (HY000): File '/data/relaylogs/relay-bin.index ' not found (Errcode:13-permission denied)
Resolution: Show Master Status; Reconfigure Master and slave
mysql> reset slave; reset slave, and reconfigure slave
mysql> change MASTER to master_host= ' 192.168.137.3 ', master_port=3306,master_user= ' CP ', master_password= ' 123123 ', Master_log_file= ' mysql-bin.000009 ', master_log_pos=467;
mysql> start slave;
Mysql> Show Slave status\g see if it's correct
Configure MySQL read-write separation, using amoeba
https://sourceforge.net/projects/amoeba/files/
Download the latest version, and unzip
Unzip Amoeba-mysql-3.0.4-beta-distribution.zip
MV Amoeba-mysql-3.0.4-beta Amoeba
CD Amoeba
chmod +x bin/
VI Conf/amoeba.xml
#设置客户端连接的参数: port, user name, password, and other information
<property name= "User" >root</property>
<property name= "Password" >root</property>
#设置主从的写与读 the original configuration file below is commented out, you need to open
<property name= "Writepool" >server1</property>
<property name= "Readpool" >server2</property>
VI Conf/dbservers.xml
#这里设置连接主从数据库的参数: port, database, user name, password
<!--MySQL Port--
<property name= "Port" >3306</property>
<!--MySQL Schema--
<property name= "schema" >erpBack</property>
<!--mysql User--
<property name= "User" >cp</property>
<property name= "Password" >123123</property>
Eg: it is important to note that both master and slave MySQL need to turn on the remote access function and set the same username and password!!
#设置主从的ip地址, note: This machine is also replaced by IP address.
<dbserver name= "Server1" parent= "Abstractserver" >
<factoryConfig>
<!--mysql IP--
<property name= "IpAddress" >192.168.137.3</property>
</factoryConfig>
</dbServer>
<dbserver name= "Server2" parent= "Abstractserver" >
<factoryConfig>
<!--mysql IP--
<property name= "IpAddress" >192.168.137.5</property>
</factoryConfig>
</dbServer>
Start Amoeba
Bin/launcher &
View Logs
Tail-f Logs/net.log
Client testing:
Mysql-h 192.168.137.3-p8066-uroot-proot
Configure Nginx Proxy
#servers
Upstream test.com {
Ip_hash;
Server 127.0.0.1:8080;
Server 127.0.0.1:8081;
}
#以上是每个请求按访问ip的hash结果分配, so that each visitor fixed access to a back-end server, can solve the session problem.
Proxy_pass http://test.com;
Proxy_redirect default;
Finally, test on the client!
This article from "Mysky" blog, reproduced please contact the author!
Nginx Load Balancer +mysql master-slave copy, read/write Separation +tomcat Project