Mariadb cluster and nginx Load Balancing configuration -- centos7, nginx -- centos7

Mariadb cluster and nginx Load Balancing configuration -- centos7, nginx -- centos7

The configuration here is a single nginx host .. Prepare four hosts, three mariadb clusters, and one nginx.

Bytes -------------------------------------------------------------------------------------------------------------------------

Mariadb cluster configuration

Environment Information

MariaDB Server: MariaDB-10.2.10

CentOS: CentOS Linux release7.2.1511 (Core)

MariaDB Galera Cluster three Cluster nodes host name and IP address information:
192.168.1.51 db1
192.168.1.52 db2
192.168.1.53 db3

 

 

Environment preparation, minimal installation after CentOS7.2, net-tools-2.0-0.17.20131004git.el7.x86_64.rpm and lrzsz-0.12.20-36.el7.x86_64.rpm for remote management and file transfer.

1. Edit and configure the 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

Last run:
# Sysctl-p

4. Disable SELINUX and Firewall
# Vi/etc/selinux/config
SELINUX = disabled

# Systemctl stop firewalld. service
# Systemctl disable firewalld. service
# Setenforce 0

Deploy MariaDB

From MariaDB 10.1, Galera Cluster has been included in the MariaDB package. You do not need to deploy the MariaDB-Galera-server and galera packages separately.
-----------------------------------------------------------------------
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

Prepare these five packages
--------------------------------------------------------------
Step 1: 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
Base url = 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. You can consider using the following address:
[Mariadb]
Name = MariaDB
Base url = 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 2: Install MariaDB (192.168.1.51, 192.168.1.52, 192.168.1.53)
# Yum install MariaDB-server-y

If you cannot access the Internet or the Internet is too slow, you can deploy a local Yum source separately to install the dependency package. After downloading the MariaDB package, install the yum directly, this example shows how to deploy MariaDB Galera Cluster by combining YUM with 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

Configure MariaDB Galera Cluster

Next, we will configure MariaDB Galera Cluster and modify the/etc/my. cnf. d/server. cnf file on each node of the MariaDB Galera Cluster respectively. The content of each node is as follows:
1. The content of the/etc/my. cnf. d/server. cnf file of the 192.168.1.51 node:

[Root @ db1 ~] # 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 # The Node returns the query request only after the transaction is applied.
Wsrep_provider_options = "gcache. size = 4G" # synchronous replication Buffer Pool
Wsrep_certify_nonPK = ON # generate a primary key for the certificationtest table without explicitly declaring the primary key. The default value is ON
# Log-bin =/opt/galera/mysql-bin # comment out if the slave database is not connected
# Log_slave_updates = 1 # comment out if the slave database is not connected
Query_cache_size = 0 # disable query Cache
Wsrep_on = ON # enable full synchronization replication Mode
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 # change the primary key auto-increment mode to cross Mode
Wsrep_slave_threads = 8 # enable the parallel replication thread and set it based on the number of CPU Cores
Innodb_flush_log_at_trx_commit = 0 # fl every 1 second
Innodb_buffer_pool_size = 2G
Wsrep_sst_method = rsync
[Embedded]
[Mariadb]
Mariadb-10.1

2. /etc/my. cnf. d/server. the content of the cnf file (except server_id, wsrep_node_name, and wsrep_node_address are different at each point. You can copy and modify these three points ):

[Root @ db2 ~] # 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. The content of the/etc/my. cnf. d/server. cnf file of the 192.168.1.53 node:
[Root @ db3 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 initialize and install a node (only 192.168.1.51, not required for other nodes !) :
# Mysql_install_db -- defaults-file =/etc/my. cnf. d/server. cnf -- user = mysql

Start on the 192.168.1.51 node through bootstrap (you must use -- wsrep-new-cluster to start it for the first time. You do not need to start it again, nor do you need to start it for other nodes !!)

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

 

Set the root password and security settings on the 192.168.1.51 node (not required for other nodes)

#/Usr/bin/mysql_secure_installation
Or
# Mysql_secure_installation
You can set the root password during the configuration process. Remember to use it later.

Start MariaDB on nodes 192.168.1.52 and 192.168.1.53. Note: If the/opt/galera directory specified in the preceding configuration file does not exist, you need to manually create and specify the permission and 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 &

Verification operation
Log on to the three nodes to view

192.168.1.51 node:
[Root @ db1 ~] # 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 | 3366c722-ff29-11e6-a31f-bb500598d033 |
| Wsrep_cluster_status | Primary |

| Wsrep_flow_control_sent | 0 |
| Wsrep_gcomm_uuid | 3366a278-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 |
+ ------------------------------ + ------------------------------------------------------------- +
58 rows in set (0.00 sec)

Note:

Wsrep_cluster_status is Primary, indicating that the node is the master node and can be read and written normally.

If wsrep_ready is ON, the cluster runs normally.

Wsrep_cluster_size is 3, indicating that the cluster has three nodes.

Create Database Test

192.168.1.51 node:
[Root @ db1 my. cnf. d] # mysql-uroot-pxxxxxx
MariaDB [(none)]> create database test_db;
Query OK, 1 row affected (0.01 sec)

 

 

 

Check the 192.168.1.52 node:
[Root @ db2 my. cnf. d] # mysql-uroot-pxxxxxx
MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test_db |
+ -------------------- +

 

 

 

View nodes 192.168.1.53:
[Root @ db3 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)

 

Verify the table stored in InnoDB
[Root @ db1 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]>

 

 

View other nodes:

 

[Root @ db2 my. cnf. d] # mysql-uroot-pxxxxxx
MariaDB [(none)]> use test_db;
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with-

Database changed
MariaDB [test_db]> select * from stuinfo;
+ ------ + ------- +
| Id | name |
+ ------ + ------- +
| 1 | hive |
| 2 | hbase |
+ ------ + ------- +
2 rows in set (0.00 sec)

[Root @ db3 my. cnf. d] # mysql-uroot-pxxxxxx
MariaDB [(none)]> use test_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
MariaDB [test_db]> select * from stuinfo;
+ ------ + ------- +
| Id | name |
+ ------ + ------- +
| 1 | hive |
| 2 | hbase |
+ ------ + ------- +
2 rows in set (0.00 sec)

 

 

Simulate node failure

Stop the database 192.168.1.51:
[Root @ db1 system] # mysqladmin-uroot-p "shutdown"

Run the following command on another node 192.168.1.52:
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 |
+ ------------------------------ + ----------------------------------------------- +

 

In this case, the cluster automatically removes the faulty node 192.168.1.51 and provides services normally.

 

Finally, we restore the failed node:
[Root @ db1 system] # mysqld_safe -- defaults-file =/etc/my. cnf. d/server. cnf -- user = mysql &

 

View 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

Now the cluster configuration is complete.

Bytes -----------------------------------------------------------------------------------------

Configure the basic lamp environment first.

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

Simplest lamp environment Configuration

Yum install-y httpd *

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

Yum install-y php *

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

Yum install-y mariadb *

---------------------------------------------
Initialize Database

Mysql_secure_installation

Configure Password

--------------------------------------
Systemctl restart httpd
Systemctl restart mariadb
-----------------------------------------
Check whether php is correctly installed
Vi/var/www/html/index. php
<? Php
Phpinfo ();
?>

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

The next step is nginx configuration.

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

Download the nginx package

First install the compiler. Dependency
Yum install gcc-c ++ zlib-devel pcre-devel openssl-libs openssl-y

---------------------------------------------
./Configure -- prefix =/usr/local/nginx
Compile to a specified directory

Make
Compile

Make install

Install

----------------------------
After the environment variable is configured, you do not need to use the absolute path to operate Nginx:

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

------------------------
Source! $
Effective

Start Nginx
Nginx

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

Enter the ip \ in the browser. do not conflict with the http port.

Appears
"Welcome to nginx !"

Installed successfully

 

Modify 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 500 502 503 x.html;
Location =/50x.html {
Root html;
}
---------------------------------------------------------
Nginx-s reload
Restart
If you enter an ip address to access the home page of other hosts, the operation is successful.