Nginx Load Balancer +mysql master-slave copy, read/write Separation +tomcat Project

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