MARIADB cluster and Nginx load Balancer configuration--CENTOS7 version

This is configured as a single Nginx host. First prepare 4 host, three mariadb cluster, an nginx.

--------------------------------------------------------------------------------------------------------------- ----------

MARIADB cluster configuration

Environmental information

MariaDB server:mariadb-10.2.10

Centos:centos Linux release7.2.1511 (Core)

MariaDB galera Cluster Three cluster node host name and IP address information:
192.168.1.51 DB1
192.168.1.52 DB2
192.168.1.53 DB3

Environment preparation, after minimizing installation of CentOS7.2, install net-tools-2.0-0.17.20131004git.el7.x86_64.rpm and lrzsz-0.12.20-36.el7.x86_64.rpm, Easily manage and transfer files remotely.

1. Edit the configuration Hosts file
# vi/etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.1.51 DB1
192.168.1.52 DB2
192.168.1.53 DB3

2. # vi/etc/security/limits.conf
* Soft Nofile 65536
* Hard Nofile 65536

3. # vi/etc/sysctl.conf
fs.file-max=655350
Net.ipv4.ip_local_port_range = 1025 65000
Net.ipv4.tcp_tw_recycle = 1

Final execution:
# sysctl-p

4. Turn off SELinux and firewalls
# Vi/etc/selinux/config
Selinux=disabled

# Systemctl Stop Firewalld.service
# systemctl Disable Firewalld.service
# Setenforce 0

Deploying MARIADB

Starting with the MARIADB 10.1 release, Galera cluster is already included in the MARIADB package and does not require a separate deployment of Mariadb-galera-server and Galera packages.
-----------------------------------------------------------------------
galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm mariadb-10.2.10-centos7-x86_64-compat.rpm
mariadb-10.2.10-centos7-x86_64-client.rpm mariadb-10.2.10-centos7-x86_64-server.rpm
mariadb-10.2.10-centos7-x86_64-common.rpm

Get these five bags ready.
--------------------------------------------------------------
Step One: Configure the Yum source (192.168.1.51,192.168.1.52,192.168.1.53)
# Touch/etc/yum.repos.d/mariadb-idc.repo
Add the following content:
[MARIADB]
Name = MariaDB
BaseURL =HTTP://YUM.MARIADB.ORG/10.1/CENTOS7-AMD64
Gpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb
Gpgcheck=1

The above Yum source may be too slow, consider using the following address:
[MARIADB]
Name = MariaDB
Baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.2.10/yum/centos/7.2/x86_64
Gpgcheck=0
--------------------------------------------------------------------------------
You can also use the local source
-----------------------------------------
Step Two: Install Mariadb (192.168.1.51,192.168.1.52,192.168.1.53)
# yum Install mariadb-server-y

If you are unable to access the extranet, or if the extranet is too slow, consider deploying a local Yum source to install the dependent packages separately, MARIADB packages are downloaded directly to Yum installation, which demonstrates the deployment of MARIADB Galera Cluster in combination with Yum and local files.

# Mount/dev/cdrom/media

# Cat/etc/yum.repos.d/local.repo
[Local]
Name=local
Baseurl=file:///media
Gpgcheck=0
Enabled=1

# ls
galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm mariadb-10.2.10-centos7-x86_64-compat.rpm
mariadb-10.2.10-centos7-x86_64-client.rpm mariadb-10.2.10-centos7-x86_64-server.rpm
mariadb-10.2.10-centos7-x86_64-common.rpm

# yum Install mariadb-10.2.10-centos7-x86_64-* galera-25.3.20-1.rhel7.el7.centos.x86_64.rpm

Configuring MariaDB Galera Cluster

Below we begin to configure the MARIADB Galera Cluster, respectively, to modify the Galera file on each node of the MARIADB/etc/my.cnf.d/server.cnf cluster, the contents of each node are as follows:
1.192.168.1.51 node/etc/my.cnf.d/server.cnf file contents:

[Email protected] ~]# CAT/ETC/MY.CNF.D/SERVER.CNF | Grep-v "#" | Grep-v "^$"
[Server]
[Mysqld]
server_id=151
Datadir=/opt/galera
User=mysql
Skip-external-locking
Skip-name-resolve
Character-set-server=utf8

[Galera]
Wsrep_causal_reads=on #节点应用完事务才返回查询请求
wsrep_provider_options= "gcache.size=4g" #同步复制缓冲池
Wsrep_certify_nonpk=on #为没有显式申明主键的表生成一个用于certificationtest的主键, the default is on
#log-bin=/opt/galera/mysql-bin #如果不接从库, Comment out
#log_slave_updates =1 #如果不接从库, comment out
query_cache_size=0 #关闭查询缓存
Wsrep_on=on #开启全同步复制模式
Wsrep_provider =/usr/lib64/galera/libgalera_smm.so#galera Library
Wsrep_cluster_name=mariadb-galera-cluster
Wsrep_ cluster_address= "gcomm://192.168.1.51,192.168.1.52,192.168.1.53" #galera cluster URL
WSREP_NODE_NAME=DB1
wsrep_node_address=192.168.1.51
Binlog_format=row
Default_storage_engine=innodb
Innodb_autoinc_lock_ mode=2 #主键自增模式修改为交叉模式
Wsrep_slave_threads=8 #开启并行复制线程, based on the number of CPU cores
Innodb_flush_log_at_trx_commit=0 # Transaction commit brush disk every 1 seconds
innodb_buffer_pool_size=2g
Wsrep_sst_method=rsync
[embedded]
[mariadb]
[mariadb-10.1 ]

2.192.168.1.52 node of the/etc/my.cnf.d/server.cnf file content (each contact except server_id, Wsrep_node_name and Wsrep_node_address different, the others are the same, You can modify these three places after copying):

[Email protected] ~]# CAT/ETC/MY.CNF.D/SERVER.CNF | Grep-v "#" | Grep-v "^$"
[Server]
[Mysqld]
server_id=152
Datadir=/opt/galera
User=mysql
Skip-external-locking
Skip-name-resolve
Character-set-server=utf8

[Galera]
Wsrep_causal_reads=on
Wsrep_provider_options= "GCACHE.SIZE=4G"
Wsrep_certify_nonpk=on
Query_cache_size=0
Wsrep_on=on
Wsrep_provider=/usr/lib64/galera/libgalera_smm.so
Wsrep_cluster_name=mariadb-galera-cluster
Wsrep_cluster_address= "gcomm://192.168.1.51,192.168.1.52,192.168.1.53"
Wsrep_node_name=db2
wsrep_node_address=192.168.1.52
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
Wsrep_slave_threads=8
Innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2g
Wsrep_sst_method=rsync
[Embedded]
[MARIADB]
[mariadb-10.1]

3.192.168.1.53 node/etc/my.cnf.d/server.cnf file contents:
[Email protected] yum.repos.d]# CAT/ETC/MY.CNF.D/SERVER.CNF | Grep-v "#" | Grep-v "^$"
[Server]
[Mysqld]
server_id=152
Datadir=/opt/galera
User=mysql
Skip-external-locking
Skip-name-resolve
Character-set-server=utf8

[Galera]
Wsrep_causal_reads=on
Wsrep_provider_options= "GCACHE.SIZE=4G"
Wsrep_certify_nonpk=on
Query_cache_size=0
Wsrep_on=on
Wsrep_provider=/usr/lib64/galera/libgalera_smm.so
Wsrep_cluster_name=mariadb-galera-cluster
Wsrep_cluster_address= "gcomm://192.168.1.51,192.168.1.52,192.168.1.53"
Wsrep_node_name=db3
wsrep_node_address=192.168.1.53
Binlog_format=row
Default_storage_engine=innodb
innodb_autoinc_lock_mode=2
Wsrep_slave_threads=8
Innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2g
Wsrep_sst_method=rsync
[Embedded]
[MARIADB]
[mariadb-10.1]

MARIADB a node to initialize the installation (only 192.168.1.51, no other nodes required!) ）：
# mysql_install_db--DEFAULTS-FILE=/ETC/MY.CNF.D/SERVER.CNF--user=mysql

On the 192.168.1.51 node through the bootstrap boot (the first boot must use--wsrep-new-cluster, start again do not need, other nodes do not need!!) ）

# Mysqld_safe--defaults-file=/etc/my.cnf.d/server.cnf--user=mysql--wsrep-new-cluster &

Set root password and security settings on 192.168.1.51 nodes (not required by other nodes)

#/usr/bin/mysql_secure_installation
Or
# mysql_secure_installation
You can set the root password during the configuration process, please keep in mind that you need to use it later.

Start mariadb on the 192.168.1.52,192.168.1.53 node, note: If the/opt/galera directory specified in the previous configuration file does not exist, you need to manually create and specify the permissions and the owner:
# Mkdir/opt/galera
# chown Mysql:root/opt/galera
# chmod 700/opt/galera
Then start the database.
# Mysqld_safe--DEFAULTS-FILE=/ETC/MY.CNF.D/SERVER.CNF--user=mysql &

Validation actions
Log in to three nodes to view

192.168.1.51 nodes:
[Email protected] ~]# mysql-uroot–pxxxxxx
MariaDB [(None)]> SHOW STATUS like ' wsrep_cluster_size ';
+--------------------+-------+
| variable_name | Value |
+--------------------+-------+
| Wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in Set (0.00 sec)

MariaDB [(None)]> show global status like ' ws% ';
+------------------------------+-------------------------------------------------------------+
| variable_name | Value |
+------------------------------+-------------------------------------------------------------+

| wsrep_cluster_conf_id | 3 |
| Wsrep_cluster_size | 3 |
| Wsrep_cluster_state_uuid |3108c722-ff29-11e6-a31f-bb500598d033 |
| Wsrep_cluster_status | Primary |

| wsrep_flow_control_sent | 0 |
| Wsrep_gcomm_uuid |3107a278-ff29-11e6-96d3-374133af7e21 |
| wsrep_incoming_addresses | 192.168.1.52:3306,192.168.1.53:3306,192.168.1.51:3306|

| wsrep_provider_version | 25.3.19 (r3667) |
| Wsrep_ready | On |

| Wsrep_thread_count | 9 |
+------------------------------+-------------------------------------------------------------+
(0.00 sec)

Comments:

The Wsrep_cluster_status is primary, which indicates that the node is the primary node and reads normally.

Wsrep_ready is on to indicate that the cluster is functioning properly.

The wsrep_cluster_size is 3, which indicates that the cluster has three nodes.

Create a database test

192.168.1.51 nodes:
[Email protected] my.cnf.d]# mysql-uroot–pxxxxxx
MariaDB [(None)]> CREATE Database test_db;
Query OK, 1 row affected (0.01 sec)

192.168.1.52 node View:
[Email protected] my.cnf.d]# mysql-uroot-pxxxxxx
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| test_db |
+--------------------+

192.168.1.53 Node View:
[Email protected] my.cnf.d]# mysql-uroot-pxxxxxx
MariaDB [(None)]> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| test_db |
+--------------------+
4 rows in Set (0.00 sec)

Verifying tables stored by InnoDB
[Email protected] my.cnf.d]# mysql-uroot–pxxxxxx
MariaDB [test_db]> CREATE TABLE Stuinfo (ID int,name text) ENGINE InnoDB;
Query OK, 0 rows affected (0.04 sec)

MariaDB [test_db]> INSERT INTO stuinfo values (1, ' hive ');
Query OK, 1 row Affected (0.00 sec)

MariaDB [test_db]> INSERT INTO stuinfo values (2, ' hbase ');
Query OK, 1 row Affected (0.00 sec)

MariaDB [test_db]>

Other nodes view:

[Email protected] my.cnf.d]# mysql-uroot-pxxxxxx
MariaDB [(None)]> use test_db;
Reading table information for completion oftable and column names
Can turn off this feature to get Aquicker startup with-a

Database changed
MariaDB [test_db]> SELECT * from Stuinfo;
+------+-------+
| ID | name |
+------+-------+
| 1 | Hive |
| 2 | HBase |
+------+-------+
2 rows in Set (0.00 sec)

[Email protected] my.cnf.d]# mysql-uroot–pxxxxxx
MariaDB [(None)]> use test_db;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
MariaDB [test_db]> SELECT * from Stuinfo;
+------+-------+
| ID | name |
+------+-------+
| 1 | Hive |
| 2 | HBase |
+------+-------+
2 rows in Set (0.00 sec)

Analog node failure

Stop the 192.168.1.51 database:
[Email protected] system]# mysqladmin-uroot-p "Shutdown"

Then perform the 192.168.1.52 on the other nodes:
MariaDB [test_db]> show global status like ' wsrep% ';
+------------------------------+-----------------------------------------------+
| variable_name | Value |
+------------------------------+-----------------------------------------------+

| wsrep_flow_control_sent | 0 |
| Wsrep_gcomm_uuid | 0CE8537E-FF2A-11E6-B037-8A383B6A8DB5 |
| wsrep_incoming_addresses | 192.168.1.52:3306,192.168.1.53:3306 |
| wsrep_last_committed | 10 |

| Wsrep_replicated_bytes | 0 |
| Wsrep_thread_count | 9 |
+------------------------------+-----------------------------------------------+

At this point the cluster automatically rejects the 192.168.1.51 fault node and provides the service normally.

Finally we recover the failed node:
[Email protected] system]# mysqld_safe--defaults-file=/etc/my.cnf.d/server.cnf--user=mysql &

Review the cluster environment again:

MariaDB [test_db]> SHOW STATUS like ' wsrep_cluster_size ';
+--------------------+-------+
| variable_name | Value |
+--------------------+-------+
| Wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in Set (0.00 sec

This completes the cluster configuration.

-----------------------------------------------------------------------------------------

First configure the basic lamp environment.

-----------------------------------------

Simplified configuration of lamp environment

Yum Install-y httpd*

----------------------------------------

Yum Install-y php*

---------------------------------------

Yum Install-y mariadb*

---------------------------------------------
Initializing the database

Mysql_secure_installation

Configure password

--------------------------------------
Systemctl Restart httpd
Systemctl Restart MARIADB
-----------------------------------------
See if PHP is installed correctly
vi/var/www/html/index.php
<?php
Phpinfo ();
?>

-----------------------------------------------

Next is the Nginx configuration.

----------------------------------------

Download the Nginx package first.

Install the compiler first. Depend on
Yum install gcc gcc-c++ zlib-devel pcre-devel openssl-devel openssl-libs openssl-y

---------------------------------------------
./configure--prefix=/usr/local/nginx
Compile to the specified directory

Make
Compile

Make install

Installation

----------------------------
After the completion of the next configuration environment variables will not use absolute path to operate Nginx:

vi/etc/profile.d/http.sh
Export Path=/usr/local/nginx/sbin: $PATH

------------------------
SOURCE!$
Effect

Start Nginx
Nginx

----------------

Enter ip\\\\\\\\\\\\\\\\\\\\\\ on the browser and do not conflict with HTTP port

Appear
"Welcome to nginx! ”

Installation Successful

When you are going to change the configuration file

Upstream App1 {
Ip_hash;
Server 192.168.1.51:80;
Server 192.168.1.52:80;
Server 192.168.1.53:80;
}
server {
Listen 80;
server_name localhost;

#charset Koi8-r;

#access_log Logs/host.access.log Main;

Location/{
Proxy_set_header x-forwarded-for $remote _addr;
Proxy_pass Http://app1;
Proxy_set_header Host $host;
Proxy_set_header X-real-ip $remote _addr;
Proxy_set_header x-forwarded-for $proxy _add_x_forwarded_for;
}

#error_page 404/404.html;

# REDIRECT Server error pages to the static page/50x.html
#
Error_page 502 503 504/50x.html;
Location =/50x.html {
root HTML;
}
---------------------------------------------------------
Nginx-s Reload
Restart
Enter the IP to access the rest of the host's home page even if it succeeds.

MARIADB cluster and Nginx load Balancer configuration--CENTOS7 version