lnmp应用分离+keepalived+mysql互为主备

2017-11-06 1402

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL Serverless，0.5-2RCU 50GB

简介：

nginx+php:192.168.198.135,192.168.198.131
keepalived-master:192.168.198.228
keepalived-slave:192.168.198.229
mysql+keepalived:192.168.198.134,192.168.198.141

discuz:http://ahdx.down.chinaz.com/201710/Discuz_X3.4_SC_UTF8_1001.zip

1、安装nginx
①源码
②yum安装(我使用了这个)
yum install -y nginx

lnmp应用分离+keepalived+mysql互为主备

2、安装mariadb
①源码
②yum安装(我使用了这个)
yum install -y epel-release
yum install -y mariadb-server mariadb
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y

(1)mariadb主从配置(my.cnf)
主：
[mysqld]
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
wait_timeout = 300
#skip_name_resolve = 1
#explicit_defaults_for_timestamp=true
#symbolic-links =0
max_allowed_packet = 100M
thread_stack = 192K
thread_cache_size = 8
max_connections = 1000
max_connect_errors = 1500
transaction_isolation = READ-COMMITTED
#explicit_defaults_for_timestamp = 1
join_buffer_size = 134217728
tmp_table_size = 67108864
tmpdir = /tmp
#innodb_log_buffer_size = 32M
#innodb_log_file_size = 48M
#innodb_page_size = 8192
#innodb_buffer_pool_size = 12G
#innodb_buffer_pool_instances = 8
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
#innodb_lru_scan_depth = 2000
#innodb_lock_wait_timeout = 5
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
#innodb_flush_method = O_DIRECT
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_limit = 32M
query_cache_size = 32M
expire_logs_days = 10
max_binlog_size = 100M
#for replication
log-bin=mysql-bin
log-bin=test-bin
binlog-do-db=test
binlog-do-db=discuz
binlog-do-db=db
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#binlog-ignore-db=oc
[client]
#socket=/app/mysql/lib/mysql.sock
socket=/var/lib/mysql/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

#symbolic-links=0

Settings user and group are ignored when systemd is used.

If you need to run mysqld under a different user or group,

customize your systemd unit file for mariadb according to the

instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

include all files from the config directory

#
!includedir /etc/my.cnf.d

从：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character_set_server=utf8
#default-storage-engine=innodb
init_connect='SET NAMES utf8'
log-bin=test-bin
port=3306
#import csv file
secure_file_priv=""
max_connections = 1000
max_connect_errors = 1000
join_buffer_size = 11382893
tmp_table_size = 6710886
tmpdir = /tmp

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Settings user and group are ignored when systemd is used.

If you need to run mysqld under a different user or group,

customize your systemd unit file for mariadb according to the

instructions in http://fedoraproject.org/wiki/Systemd

inno settings

#innodb_buffer_pool_size = 5G
#innodb_log_buffer_size = 32M
#innodb_log_file_size = 512M
#innodb_lru_scan_depth = 2000
#innodb_lock_wait_timeout = 5
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000
#innodb_flush_method = O_DIRECT
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
#innodb_flush_neighbors = 1
#innodb_log_file_size = 2G
#innodb_log_buffer_size = 10777216
#innodb_purge_threads = 4
#innodb_large_prefix = 1
#innodb_thread_concurrency = 64
#innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1
#innodb_sort_buffer_size = 67108864
#####Logs Settings#####
#innodb_log_buffer_size = 10777216
#innodb_purge_threads = 4
#innodb_large_prefix = 1
#innodb_thread_concurrency = 64
#innodb_print_all_deadlocks = 1
#innodb_strict_mode = 1
#innodb_sort_buffer_size = 67108864
#####Logs Settings#####
query_cache_limit = 32M
query_cache_size = 32M
expire_logs_days = 10
max_binlog_size = 100M
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=134
replicate-do-db=db
replicate-do-db=test
replicate-do-db=discuz
replicate_wild_do_table=test.t2
replicate_wild_do_table=test.t4
###replicate-do-db=erp
#replicate-do-db=payment
#replicate-do-db=crm
#replicate-do-db=marketing
#replicate-do-db=ocrp
#replicate-do-db=oc
slave-skip-errors=1032,1062,1050,1051,1677
log-slave-updates=true
relay-log=test-relay-bin
#relay-log-index=relay-bin-index

#skip-slave-start
#slave-parallel-type=LOGICAL_CLOCK
#slave-parallel-workers=16
#slave_pending_jobs_size_max=120M
#relay_log_info_repository=table
#master_info_repository=table

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

include all files from the config directory

#
!includedir /etc/my.cnf.d

(2)mariadb主从数据库的配置
主：
①创建用户
[root@zimbra ~]# mysql -u root -p123456 -e "create user repl;"
②授权远程访问(这一步不做好的话，通过vip访问会导致discuz会出现1045的报错)
[root@zimbra ~]# mysql -u root -p123456 -e "grant all privileges on . to 'root'@'192.168.198.134' identified by '123456' with grant option;"

MariaDB [mysql]> grant all privileges on . to 'root'@'192.168.198.141' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> grant all privileges on . to 'root'@'192.168.198.135' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> grant all privileges on . to 'root'@'192.168.198.131' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> grant all privileges on . to 'root'@'192.168.198.23' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#####授权从库同步的账号
[root@zimbra ~]# mysql -u root -p123456 -e "grant all privileges on . to 'repl'@'192.168.198.134' identified by '123456' with grant option;"

lnmp应用分离+keepalived+mysql互为主备

从：
[root@slave ~]# mysql -uroot -e "stop slave\G"
[root@slave ~]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.52-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> change master to master_host='192.168.198.141',master_user='repl',master_password='123456',master_log_file='test-bin.000002',master_log_pos=3503;
###根据主pos来做从的数据库配置
lnmp应用分离+keepalived+mysql互为主备

3、安装php
wget http://cn2.php.net/distributions/php-5.6.0.tar.xz 再解压
cd /usr/local/src/php-5.6.0
./configure --prefix=/usr/local/php --with-config-file-path=/usr/local/php/etc --enable-inline-optimization --disable-debug --disable-rpath --enable-shared --enable-opcache --enable-fpm --with-fpm-user=www --with-fpm-group=www --with-mysql=mysqlnd --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-gettext --enable-mbstring --with-iconv --with-mcrypt --with-mhash --with-openssl --enable-bcmath --enable-soap --with-libxml-dir --enable-pcntl --enable-shmop --enable-sysvmsg --enable-sysvsem --enable-sysvshm --enable-sockets --with-curl --with-zlib --enable-zip --with-bz2 --with-readline --with-jpeg-dir=/usr/local/jpeg --with-png-dir=/usr/local/png --with-zlib --with-freetype-dir=/usr/local/freetype --with-gd
lnmp应用分离+keepalived+mysql互为主备
配置 PHP
配置文件：

cp php.ini-development /usr/local/php/etc/php.ini
php-fpm 服务

cp /usr/local/php/etc/php-fpm.conf.default /usr/local/php/etc/php-fpm.conf
cp sapi/fpm/init.d.php-fpm /etc/init.d/php-fpm
chmod +x /etc/init.d/php-fpm

添加 PHP 命令到环境变量
编辑 ~/.bash_profile，添加：

###java env
export JAVA_HOME=/usr/local/src/jdk1.6.0_45
#export JRE_HOME=$JAVA_HOME/jre
export PATH=$JAVE_HOME/bin:$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/lib

###php env
export PHP_HOME=/usr/local/php56
#export JRE_HOME=$JAVA_HOME/jre
export PATH=$PHP_HOME/bin:$PHP_HOME/bin:$PATH
export CLASSPATH=.:$PHP_HOME/lib:$PHP_HOME/lib

~/.bash_profile

查看看 PHP 版本
php -v
PHP 5.6.0 (cli) (built: Sep 23 2014 03:44:18)
Copyright (c) 1997-2014 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2014 Zend Technologies

4、keepalived配置
mysql-keepalived
lnmp应用分离+keepalived+mysql互为主备
因为mysql用不到那个lvs的东西，就注释了。

nginx-keepalived(主和从的区别在于state和priority)
lnmp应用分离+keepalived+mysql互为主备

在mariadb的show master status看到不会因为主的nginx机器挂掉而导致数据库连接丢失，因为有vip的
lnmp应用分离+keepalived+mysql互为主备

5、安装phpMyAdmin
wget https://files.phpmyadmin.net/phpMyAdmin/4.7.7/phpMyAdmin-4.7.7-all-languages.zip
把压缩包放进去网址根目录，然后解压，把解压出来的文件夹重命名为phpmyadmin,然后再进去这个目录，把config.sample.inc.php复制为config.inc.php，有行$cfg['Servers'][$i]['host'] = 'localhost'; 把localhost改为你的数据库IP
lnmp应用分离+keepalived+mysql互为主备
更改完毕就重启mariadb和nginx，再通过浏览器访问http://ip/phpmyadmin即可

关于报错
如果打开php页面404的话，很可能是nginx的配置文件导致的。
lnmp应用分离+keepalived+mysql互为主备
红色圈住的那里要改成网站根目录才行

mysql 1236报错

解决:stop slave;reset slave;start slave;
mysql 1050报错

在my.cnf报错添加slave-skip-errors=1032,1062,1050,1677这段配置后重启mariadb即可