一、环境介绍
1.1、规划
序号
类别
版本
主机名
IP
端口
备注
1
OS
CentOS release 6.9 (Final) (minimal)
my1
172.16.210.180
8306
172.16.210.183
2
mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
my2
172.16.210.181
8306
3
keeplived
keepalived-1.2.7.tar.gz
mysql下载地址:
keeplived下载地址:
http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
1.2、MySQL双主互备+keepalived高可用架构介绍
MySQL主从复制架构可以在很大程度保证MySQL的高可用,在一主多从的架构中还可以利用读写分离将读操作分配到从库中,减轻主库压力。但是在这种架构中,主库出现故障时需要手动将一台从库提升为主库。在对写操作要求较高的环境中,主库故障在主从架构中会成为单点故障。因此需要主主互备架构,避免主节点故障造成写操作失效。
在双主互备的架构中,每台MySQL都充当主服务器,同时充当对方的从服务器。在任意一台服务器上的写操作都会被复制到另一台服务器上,从而保证了数据的可靠性。
在双主互备的基础上加上keepalived,在其中一台机器上绑定虚拟ip(VIP)。利用vip统一对外服务,可以避免在两个节点同时写数据造成冲突。同时当keepalived主节点发生故障时,keeplived会自动将VIP切换到备节点上,从而实现主服务器的高可用。
二、安装mysql5.7
2.1、主机M1上的操作
2.1.1、安装依赖包
yumclean allyum -y updateyum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses cmake libaio libaio-devel boostyum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip
yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-devyum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxsltyum -y install gcc automake autoconf libtool openssl-develyum -y install perl-devel perl-ExtUtils-Embedyum -y install cmake ncurses-devel.x86_64 openldap-devel.x86_64 lrzsz openssh-clients gcc-g77 bisonyum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-develyum -y install ntpdate rsync svn patch iptables iptables-servicesyum -y install libevent libevent-devel cyrus-sasl cyrus-sasl-develyum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make
yum -y groupinstall "Server Platform Development" "Development tools"
yum -y groupinstall "Development tools"
2.1.2、下载解压
rm -rf /etc/my.cnfmkdir -p /opt/mysql
cd/opt/mysql/
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
tar -zxf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
cd/usr/local/
ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64 mysql
2.1.3、创建所需要的目录
mkdir -p /data/mysql/mysql_8306/{data,logs,tmp}
2.1.4、更改权限
groupadd mysql
useradd-g mysql mysql -d /home/mysql -s /sbin/nologin
2.1.5、创建my.cnf
cat >/data/mysql/mysql_8306/my_8306.cnf <
#my.cnf
[client]
port= 8306socket= /data/mysql/mysql_8306/tmp/mysql_8306.sock
[mysql]
#prompt="\u@\h:\p \R:\m:\s [\d]>"#tee=/data/mysql/mysql_8306/data/query.log
#prompt="\u@\h:\p \R:\m:\s [\d]>"prompt= "[\u@\h][\d]>\_"connect_timeout= 5no-auto-rehash
[mysqld]
#misc
user=mysql
basedir= /usr/local/mysql
datadir= /data/mysql/mysql_8306/data
port= 8306socket= /data/mysql/mysql_8306/tmp/mysql_8306.sock
#timeout
interactive_timeout= 300wait_timeout= 300#character set
character-set-server =utf8
open_files_limit= 65535max_connections= 100max_connect_errors= 100000skip-name-resolve = 1#logs
log-output=fileslow_query_log= 1slow_query_log_file= /data/mysql/mysql_8306/logs/slow.log
log-error = /data/mysql/mysql_8306/logs/error.log
log_error_verbosity= 3pid-file =mysql.pid
long_query_time= 1#log-slow-admin-statements = 1#log-queries-not-using-indexes = 1log-slow-slave-statements = 1#tmp
tmpdir=/data/mysql/mysql_8306/tmp
event_scheduler= 1performance_schema=on
max_allowed_packet=32M
character_set_server=utf8mb4
#character_set_server=utf8
default-time-zone =system
default-storage-engine =InnoDB
#bind_address= 172.16.151.248explicit_defaults_for_timestamp= 1#binlog
binlog_format=row
server-id = 1818306log-bin = /data/mysql/mysql_8306/logs/mysql-bin
log-bin-index = /data/mysql/mysql_8306/logs/mysql-bin.index
binlog_cache_size=4M
max_binlog_size=1G
max_binlog_cache_size=2G
sync_binlog= 0expire_logs_days= 90#replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%#relay log
skip_slave_start= 1max_relay_log_size=1G
relay_log_purge= 1relay_log_recovery= 1log_slave_updates
#slave-skip-errors=1032,1053,1062explicit_defaults_for_timestamp=1#buffers&cache
table_open_cache= 2048table_definition_cache= 2048table_open_cache= 2048max_heap_table_size=96M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size= 256query_cache_size= 0query_cache_type= 0query_cache_limit=256K
query_cache_min_res_unit= 512thread_stack=192K
tmp_table_size=96M
key_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M
#myisam
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=10G
myisam_repair_threads= 1#innodb
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances= 1innodb_data_file_path=ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit= 2innodb_log_buffer_size=64M
innodb_log_file_size=500M
innodb_log_files_in_group= 3innodb_max_dirty_pages_pct= 50innodb_file_per_table= 1innodb_rollback_on_timeout
innodb_status_file= 1innodb_io_capacity= 2000transaction_isolation= READ-COMMITTED
innodb_flush_method=O_DIRECT
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay-log-info-repository =TABLE
binlog_checksum=NONE
log_slave_updates=ON
# Two-Master configure
#server-1auto-increment-offset = 1auto-increment-increment = 2#server-2#auto-increment-offset = 2#auto-increment-increment = 2# semisyncreplication settings #
plugin_dir= /usr/local/mysql/lib/plugin #官方版本的路径
#plugin_dir= /usr/local/mysql/lib/mysql/plugin
plugin_load= "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#官方版本的路径
#plugin_load= "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled=on
loose_rpl_semi_sync_master_timeout= 5000loose_rpl_semi_sync_master_trace_level= 32loose_rpl_semi_sync_master_wait_no_slave=on
loose_rpl_semi_sync_slave_enabled=on
loose_rpl_semi_sync_slave_trace_level= 32loose_rpl_semi_sync_master_enabled= 1loose_rpl_semi_sync_slave_enabled= 1loose_rpl_semi_sync_master_timeout= 5000loose_rpl_semi_sync_master_wait_for_slave_count=1loose_rpl_semi_sync_master_wait_point=AFTER_SYNC
slave_preserve_commit_order= 1slave_transaction_retries= 128log_timestamps=system
show_compatibility_56=on
slave_parallel_workers= 16slave_parallel_type=LOGICAL_CLOCK
loose_innodb_numa_interleave= 1innodb_buffer_pool_dump_pct= 40innodb_page_cleaners= 16innodb_undo_log_truncate= 1innodb_max_undo_log_size=2G
innodb_purge_rseg_truncate_frequency= 128#transaction_write_set_extraction=MURMUR32
# group replication
##log-bin =mysql
##server-id = 618306##gtid_mode=ON
##enforce_gtid_consistency=ON
##master_info_repository=TABLE
##relay-log-info-repository =TABLE
##binlog_checksum=NONE
##log_slave_updates=ON
##binlog_format=row
##transaction_write_set_extraction=XXHASH64
##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'##loose-group_replication_start_on_boot =off
##loose-group_replication_local_address= "10.125.141.62:28306"# 不能超过5位数字
##loose-group_replication_group_seeds= "10.125.141.62:28306,10.125.141.62:23307,10.125.141.62:23308"# 不能超过5位数字
##loose-group_replication_bootstrap_group=off
# loose-group_replication_single_primary_mode=FALSE ###本次搭建的是mutil_mode
# loose-group_replication_enforce_update_everywhere_checks=TRUE
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.sonice=-19open-files-limit=65535EOF
2.1.6、修改目录权限
chown -R mysql.mysql /data/mysql/
2.1.7、初始化
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --initialize-insecure &
2.1.8、启动
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &
# 推荐的启动方式
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &
2.1.9、登陆方式
/usr/local/mysql/bin/mysql -uroot -p -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock
或者
/usr/local/mysql/bin/mysql -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock
2.1.10、创建授权修改密码
set sql_log_bin = 0;create user 'rpl_user'@'%';grant replication slave on *.* to 'rpl_user'@'%' identified by 'rpl_user2017';update mysql.user set authentication_string=password('root2017') where user='root';
flushprivileges;set sql_log_bin = 1;
reset master ; reset slaveall;
2.1.11、change master
CHANGE MASTER TO MASTER_HOST='172.16.210.181',MASTER_USER='rpl_user',
MASTER_PASSWORD='rpl_user2017',MASTER_PORT=8306,
MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;
start slave;
show slave status\G;
2.1.12、设置快捷登陆方式
[root@my1 local]# /usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=8306_localhost_login \> --user=root --port=8306 --password --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock
Enter password:
[root@my1 local]#
# 查看/usr/local/mysql/bin/mysql_config_editor print --all
[root@my1 local]# alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'
2.1.13、快捷关闭数据库
[root@my1 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'
2.1.14、加入备忘录
[root@my1 ~]# cat >>/root/.bashrc <
alias mysql.8306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &'alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'EOF
[root@my1~]# source /root/.bash_profile
2.1.15、备份脚本
[root@my1 ~]# cat /root/all_database.sh#!/bin/bash/usr/local/mysql/bin/mysqldump --login-path=8306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384 --master-data=2 --single-transaction --all-databases --quick | gzip >/root/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz
[root@my1~]#
2.2、主机M2上的操作
2.2.1、安装依赖包
yumclean allyum -y updateyum -y install gcc gcc-c++ make autoconf automake ncurses-devel bison ncurses cmake libaio libaio-devel boostyum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip
yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-devyum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxsltyum -y install gcc automake autoconf libtool openssl-develyum -y install perl-devel perl-ExtUtils-Embedyum -y install cmake ncurses-devel.x86_64 openldap-devel.x86_64 lrzsz openssh-clients gcc-g77 bisonyum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-develyum -y install ntpdate rsync svn patch iptables iptables-servicesyum -y install libevent libevent-devel cyrus-sasl cyrus-sasl-develyum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make
yum -y groupinstall "Server Platform Development" "Development tools"
yum -y groupinstall "Development tools"
2.2.2、下载安装
rm -rf /etc/my.cnfmkdir -p /opt/mysql
cd/opt/mysql/
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
tar -zxf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
cd/usr/local/
ln -s /opt/mysql/mysql-5.7.20-linux-glibc2.12-x86_64 mysql
2.2.3、创建所需要的目录
mkdir -p /data/mysql/mysql_8306/{data,logs,tmp}
2.2.4、更改权限
groupadd mysql
useradd-g mysql mysql -d /home/mysql -s /sbin/nologin
2.2.5、创建my.cnf
cat >/data/mysql/mysql_8306/my_8306.cnf <
#my.cnf
[client]
port= 8306socket= /data/mysql/mysql_8306/tmp/mysql_8306.sock
[mysql]
#prompt="\u@\h:\p \R:\m:\s [\d]>"#tee=/data/mysql/mysql_8306/data/query.log
#prompt="\u@\h:\p \R:\m:\s [\d]>"prompt= "[\u@\h][\d]>\_"connect_timeout= 5no-auto-rehash
[mysqld]
#misc
user=mysql
basedir= /usr/local/mysql
datadir= /data/mysql/mysql_8306/data
port= 8306socket= /data/mysql/mysql_8306/tmp/mysql_8306.sock
#timeout
interactive_timeout= 300wait_timeout= 300#character set
character-set-server =utf8
open_files_limit= 65535max_connections= 100max_connect_errors= 100000skip-name-resolve = 1#logs
log-output=fileslow_query_log= 1slow_query_log_file= /data/mysql/mysql_8306/logs/slow.log
log-error = /data/mysql/mysql_8306/logs/error.log
log_error_verbosity= 3pid-file =mysql.pid
long_query_time= 1#log-slow-admin-statements = 1#log-queries-not-using-indexes = 1log-slow-slave-statements = 1#tmp
tmpdir=/data/mysql/mysql_8306/tmp
event_scheduler= 1performance_schema=on
max_allowed_packet=32M
character_set_server=utf8mb4
#character_set_server=utf8
default-time-zone =system
default-storage-engine =InnoDB
#bind_address= 172.16.151.248explicit_defaults_for_timestamp= 1#binlog
binlog_format=row
server-id = 1818306log-bin = /data/mysql/mysql_8306/logs/mysql-bin
log-bin-index = /data/mysql/mysql_8306/logs/mysql-bin.index
binlog_cache_size=4M
max_binlog_size=1G
max_binlog_cache_size=2G
sync_binlog= 0expire_logs_days= 90#replicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%#relay log
skip_slave_start= 1max_relay_log_size=1G
relay_log_purge= 1relay_log_recovery= 1log_slave_updates
#slave-skip-errors=1032,1053,1062explicit_defaults_for_timestamp=1#buffers&cache
table_open_cache= 2048table_definition_cache= 2048table_open_cache= 2048max_heap_table_size=96M
sort_buffer_size=2M
join_buffer_size=2M
thread_cache_size= 256query_cache_size= 0query_cache_type= 0query_cache_limit=256K
query_cache_min_res_unit= 512thread_stack=192K
tmp_table_size=96M
key_buffer_size=8M
read_buffer_size=2M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=32M
#myisam
myisam_sort_buffer_size=128M
myisam_max_sort_file_size=10G
myisam_repair_threads= 1#innodb
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances= 1innodb_data_file_path=ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit= 2innodb_log_buffer_size=64M
innodb_log_file_size=500M
innodb_log_files_in_group= 3innodb_max_dirty_pages_pct= 50innodb_file_per_table= 1innodb_rollback_on_timeout
innodb_status_file= 1innodb_io_capacity= 2000transaction_isolation= READ-COMMITTED
innodb_flush_method=O_DIRECT
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay-log-info-repository =TABLE
binlog_checksum=NONE
log_slave_updates=ON
# Two-Master configure
#server-1#auto-increment-offset = 1#auto-increment-increment = 2#server-2auto-increment-offset = 2auto-increment-increment = 2# semisyncreplication settings #
plugin_dir= /usr/local/mysql/lib/plugin #官方版本的路径
#plugin_dir= /usr/local/mysql/lib/mysql/plugin
plugin_load= "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#官方版本的路径
#plugin_load= "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"loose_rpl_semi_sync_master_enabled=on
loose_rpl_semi_sync_master_timeout= 5000loose_rpl_semi_sync_master_trace_level= 32loose_rpl_semi_sync_master_wait_no_slave=on
loose_rpl_semi_sync_slave_enabled=on
loose_rpl_semi_sync_slave_trace_level= 32loose_rpl_semi_sync_master_enabled= 1loose_rpl_semi_sync_slave_enabled= 1loose_rpl_semi_sync_master_timeout= 5000loose_rpl_semi_sync_master_wait_for_slave_count=1loose_rpl_semi_sync_master_wait_point=AFTER_SYNC
slave_preserve_commit_order= 1slave_transaction_retries= 128log_timestamps=system
show_compatibility_56=on
slave_parallel_workers= 16slave_parallel_type=LOGICAL_CLOCK
loose_innodb_numa_interleave= 1innodb_buffer_pool_dump_pct= 40innodb_page_cleaners= 16innodb_undo_log_truncate= 1innodb_max_undo_log_size=2G
innodb_purge_rseg_truncate_frequency= 128#transaction_write_set_extraction=MURMUR32
# group replication
##log-bin =mysql
##server-id = 618306##gtid_mode=ON
##enforce_gtid_consistency=ON
##master_info_repository=TABLE
##relay-log-info-repository =TABLE
##binlog_checksum=NONE
##log_slave_updates=ON
##binlog_format=row
##transaction_write_set_extraction=XXHASH64
##loose-group_replication_group_name = '3db33b36-0e51-409f-a61d-c99756e90154'##loose-group_replication_start_on_boot =off
##loose-group_replication_local_address= "10.125.141.62:28306"# 不能超过5位数字
##loose-group_replication_group_seeds= "10.125.141.62:28306,10.125.141.62:23307,10.125.141.62:23308"# 不能超过5位数字
##loose-group_replication_bootstrap_group=off
# loose-group_replication_single_primary_mode=FALSE ###本次搭建的是mutil_mode
# loose-group_replication_enforce_update_everywhere_checks=TRUE
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.sonice=-19open-files-limit=65535EOF
2.2.6、修改目录权限
chown -R mysql.mysql /data/mysql/
2.2.7、初始化
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --initialize-insecure &
2.2.8、启动
# /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf 推荐的启动方式/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &
2.2.9、登陆方式
/usr/local/mysql/bin/mysql -uroot -p -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock
或者/usr/local/mysql/bin/mysql -P8306 -S /data/mysql/mysql_8306/tmp/mysql_8306.sock
2.2.10、创建授权修改密码
set sql_log_bin = 0;create user 'rpl_user'@'%';grant replication slave on *.* to 'rpl_user'@'%' identified by 'rpl_user2017';update mysql.user set authentication_string=password('root2017') where user='root';
flushprivileges;set sql_log_bin = 1;
reset master ; reset slaveall;
2.2.11、change master
CHANGE MASTER TO MASTER_HOST='172.16.210.180',MASTER_USER='rpl_user',
MASTER_PASSWORD='rpl_user2017',MASTER_PORT=8306,
MASTER_CONNECT_RETRY=10,MASTER_AUTO_POSITION =1;
start slave;
show slave status\G;
2.2.12、设置快捷登陆方式
[root@my2 local]# /usr/local/mysql/bin/mysql_config_editor set --host=localhost --login-path=8306_localhost_login \> --user=root --port=8306 --password --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock
Enter password:
[root@my2 local]#
# 查看/usr/local/mysql/bin/mysql_config_editor print --all
[root@my2 local]# alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'
2.2.13、快捷关闭数据库
[root@my2 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'
2.2.14、加入备忘录
[root@my2 ~]# cat >>/root/.bashrc <
alias mysql.8306.start='/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf &'alias mysql.8306.login='/usr/local/mysql/bin/mysql --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --login-path=8306_localhost_login'alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'EOF
[root@my2~]# source /root/.bash_profile
2.2.15、备份脚本
[root@my2 ~]# cat /root/all_database.sh#!/bin/bash/usr/local/mysql/bin/mysqldump --login-path=8306_localhost_login -R -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384 --master-data=2 --single-transaction --all-databases --quick | gzip >/root/all_database_bak_`date +%Y-%m-%d_%H_%M_%S`.sql.gz
[root@my2~]#
三、验证同步情况
3.1、在my1中操作
[root@my1 ~]# mysql.8306.login
Welcometo the MySQL monitor. Commands end with ; or\g.
Your MySQL connection idis 6Server version:5.7.20-logMySQL Community Server (GPL)
Copyright (c)2000, 2017, Oracle and/or its affiliates. Allrights reserved.
Oracleis a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarksoftheir respective
owners.
Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.[root@localhost][(none)]>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00sec)[root@localhost][(none)]> create databasedb1;
Query OK,1 row affected (0.01sec)[root@localhost][(none)]>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00sec)[root@localhost][(none)]>
[root@localhost][(none)]>show slave status\G;*************************** 1. row ***************************Slave_IO_State:
Master_Host:172.16.210.181Master_User: rpl_user
Master_Port:8306Connect_Retry:10Master_Log_File: mysql-bin.000001Read_Master_Log_Pos:150Relay_Log_File: my1-relay-bin.000003Relay_Log_Pos:4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0Last_Error:
Skip_Counter:0Exec_Master_Log_Pos:150Relay_Log_Space:721Until_Condition: None
Until_Log_File:
Until_Log_Pos:0Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert: No
Last_IO_Errno:0Last_IO_Error:
Last_SQL_Errno:0Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:0Master_UUID: d14b54b4-de49-11e7-96ea-8ae132e2dda2
Master_Info_File: mysql.slave_master_info
SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1Auto_Position:1Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:1 row in set (0.00sec)
ERROR:
No query specified[root@localhost][(none)]>
3.2、在my2中操作
[root@my2 local]# mysql.8306.login
Welcometo the MySQL monitor. Commands end with ; or\g.
Your MySQL connection idis 32Server version:5.7.20-logMySQL Community Server (GPL)
Copyright (c)2000, 2017, Oracle and/or its affiliates. Allrights reserved.
Oracleis a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarksoftheir respective
owners.
Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.[root@localhost][(none)]>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00sec)[root@localhost][(none)]> create databasedb2;
Query OK,1 row affected (0.00sec)[root@localhost][(none)]>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event
Master_Host:172.16.210.180Master_User: rpl_user
Master_Port:8306Connect_Retry:10Master_Log_File: mysql-bin.000003Read_Master_Log_Pos:303Relay_Log_File: my2-relay-bin.000003Relay_Log_Pos:508Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:0Last_Error:
Skip_Counter:0Exec_Master_Log_Pos:303Relay_Log_Space:910Until_Condition: None
Until_Log_File:
Until_Log_Pos:0Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:0Last_IO_Error:
Last_SQL_Errno:0Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:1808306Master_UUID: ce20a632-de49-11e7-9587-c2c763ed137c
Master_Info_File: mysql.slave_master_info
SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State: Slave hasread all relay log; waiting formore updates
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1Executed_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1,
d14b54b4-de49-11e7-96ea-8ae132e2dda2:1Auto_Position:1Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:1 row in set (0.00sec)
ERROR:
No query specified
同步正常
四、同步故障处理举例
# gtid故障处理
模拟在从库删除库,然后再在主库删除该库,报如下错误
Last_SQL_Error: Error'Can't drop database 'db1'; database doesn't exist' on query. Default database: 'db1'. Query: 'drop database db1'Replicate_Ignore_Server_Ids:
Master_Server_Id:628306Master_UUID: 11526eb0-fcbc-11e6-af7d-005056b937e2
Master_Info_File: mysql.slave_master_info
SQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:
Master_Retry_Count:86400Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:170227 15:44:06Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1-2Executed_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1,
1760a7a5-fcbc-11e6-8f14-005056b90358:1Auto_Position:1Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
处理方法:
stop slave;set gtid_next='11526eb0-fcbc-11e6-af7d-005056b937e2:2';begin;commit;set gtid_next='automatic';
start slave;
show slave status\G;
五、配置keeplived实现高可用
参考资料:
5.1、在my1中的操作
yum install -y pcre-devel openssl-devel popt-devel libnl-* libn*#安装依赖包
# 将keepalived配置成系统服务wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
tar zxvf keepalived-1.2.7.tar.gz
cd keepalived-1.2.7./configure --prefix=/usr/local/keepalivedmake && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived/
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
chmod +x /etc/init.d/keepalived
# server1cat >/etc/keepalived/keepalived.conf <
global_defs {
# notification_email {
# test@sina.com
# }
# notification_email_from admin@test.com
# smtp_server127.0.0.1# smtp_connect_timeout30router_id MYSQL_HA18 #标识,双主相同
}
vrrp_instance VI_1 {
state BACKUP #两台都设置BACKUP
interface eth0
virtual_router_id18#主备相同
priority100#优先级,backup设置90
advert_int1nopreempt #不主动抢占资源,只在master这台优先级高的设置,backup不设置
authentication {
auth_type PASS
auth_pass1111}
virtual_ipaddress {172.16.210.183}
}
virtual_server172.16.210.183 8306{
delay_loop2#lb_algo rr #LVS算法,用不到,我们就关闭了
#lb_kind DR #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
persistence_timeout50#同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server172.16.210.180 8306{ #检测本地mysql,backup也要写检测本地mysql
weight3notify_down/usr/local/keepalived/mysql.sh#当mysq服down时,执行此脚本,杀死keepalived实现切换
TCP_CHECK {
connect_timeout3#连接超时
nb_get_retry3#重试次数
delay_before_retry3#重试间隔时间
}
}
EOFcat >/usr/local/keepalived/mysql.sh <
#!/bin/bash
pkill keepalived
EOFchmod +x /usr/local/keepalived/mysql.sh
chmod +x /etc/init.d/keepalived/etc/init.d/keepalived start
5.2、在my2中的操作
yum install -y pcre-devel openssl-devel popt-devel libnl-* libn*#安装依赖包
# 将keepalived配置成系统服务wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
tar zxvf keepalived-1.2.7.tar.gz
cd keepalived-1.2.7./configure --prefix=/usr/local/keepalivedmake && make install
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived/
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
chmod +x /etc/init.d/keepalived
# server2cat >/etc/keepalived/keepalived.conf <
global_defs {
# notification_email {
# test@sina.com
# }
# notification_email_from admin@test.com
# smtp_server127.0.0.1# smtp_connect_timeout30router_id MYSQL_HA18 #标识,双主相同
}
vrrp_instance VI_1 {
state BACKUP #两台都设置BACKUP
interface eth0
virtual_router_id18#主备相同
priority90#优先级,backup设置90
advert_int1#nopreempt #不主动抢占资源,只在master这台优先级高的设置,backup不设置
authentication {
auth_type PASS
auth_pass1111}
virtual_ipaddress {172.16.210.183}
}
virtual_server172.16.210.183 8306{
delay_loop2#lb_algo rr #LVS算法,用不到,我们就关闭了
#lb_kind DR #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
persistence_timeout50#同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server172.16.210.181 8306{ #检测本地mysql,backup也要写检测本地mysql
weight3notify_down/usr/local/keepalived/mysql.sh#当mysq服down时,执行此脚本,杀死keepalived实现切换
TCP_CHECK {
connect_timeout3#连接超时
nb_get_retry3#重试次数
delay_before_retry3#重试间隔时间
}
}
EOFcat >/usr/local/keepalived/mysql.sh <
#!/bin/bash
pkill keepalived
EOFchmod +x /usr/local/keepalived/mysql.sh
chmod +x /etc/init.d/keepalived/etc/init.d/keepalived start
六、测试高可用性
6.1、启动my1的keeplive服务
[root@my1 ~]# /etc/init.d/keepalived start
Starting keepalived: [ OK ]
[root@my1~]#
[root@my1~]# tail /var/log/messages
Dec12 15:12:46 localhost Keepalived_healthcheckers[13090]: IPVS: Service not defined
Dec12 15:12:46 localhost Keepalived_healthcheckers[13090]: Using LinkWatch kernel netlink reflector...
Dec12 15:12:46 localhost Keepalived_healthcheckers[13090]: Activating healthchecker for service [172.16.210.180]:8306Dec12 15:12:46localhost kernel: IPVS: Scheduler module ip_vs_ not found
Dec12 15:12:50 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Transition to MASTER STATE
Dec12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183Dec12 15:12:51 localhost Keepalived_healthcheckers[13090]: Netlink reflector reports IP 172.16.210.183added
Dec12 15:12:56 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183[root@my1~]#
6.2、启动my2的keeplived服务
[root@my2 ~]# /etc/init.d/keepalived start
Starting keepalived: [ OK ]
[root@my2~]#
[root@my2~]# tail /var/log/messages
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Opening file '/etc/keepalived/keepalived.conf'.
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Configuration is using : 10232Bytes
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Using LinkWatch kernel netlink reflector...
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Entering BACKUP STATE
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Scheduler not found
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Service not defined
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Using LinkWatch kernel netlink reflector...
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Activating healthchecker for service [172.16.210.181]:8306Dec12 15:15:02localhost kernel: IPVS: Scheduler module ip_vs_ not found
[root@my2~]#
6.3、关闭my1的mysql服务
[root@my1 ~]# ps -ef|grepmysql
root13119 10926 0 15:15 pts/0 00:00:00 grepmysql
root19296 1 0 Dec11 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf
mysql20709 19296 0 Dec11 ? 00:00:55 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_8306/my_8306.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_8306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_8306/logs/error.log --open-files-limit=65535 --pid-file=mysql.pid --socket=/data/mysql/mysql_8306/tmp/mysql_8306.sock --port=8306[root@my1~]# mysql.8306.stop
[root@my1~]# ps -ef|grepmysql
root13138 10926 0 15:18 pts/0 00:00:00 grepmysql
[root@my1~]#
# 查看my1的日志
[root@my1~]# tail -20 /var/log/messages
Dec12 15:12:46 localhost Keepalived_healthcheckers[13090]: IPVS: Service not defined
Dec12 15:12:46 localhost Keepalived_healthcheckers[13090]: Using LinkWatch kernel netlink reflector...
Dec12 15:12:46 localhost Keepalived_healthcheckers[13090]: Activating healthchecker for service [172.16.210.180]:8306Dec12 15:12:46localhost kernel: IPVS: Scheduler module ip_vs_ not found
Dec12 15:12:50 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Transition to MASTER STATE
Dec12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183Dec12 15:12:51 localhost Keepalived_healthcheckers[13090]: Netlink reflector reports IP 172.16.210.183added
Dec12 15:12:56 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: TCP connection to [172.16.210.180]:8306 failed !!!Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: Removing service [172.16.210.180]:8306 from VS [172.16.210.183]:8306Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: IPVS: Service not defined
Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: Executing [/usr/local/keepalived/mysql.sh] for service [172.16.210.180]:8306 in VS [172.16.210.183]:8306Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: Lost quorum 1-0=1 > 0 for VS [172.16.210.183]:8306Dec12 15:18:34 localhost Keepalived[13089]: Stopping Keepalived v1.2.7 (12/12,2017)
Dec12 15:18:34 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) sending 0priority
Dec12 15:18:34 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) removing protocol VIPs.
Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: Netlink reflector reports IP 172.16.210.183removed
Dec12 15:18:34 localhost Keepalived_healthcheckers[13090]: IPVS: No such service
[root@my1~]#
6.4、查看my2的日志及其vip情况
[root@my2 ~]# ip add1: lo: mtu 65536qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8scope host lo
inet6 ::1/128scope host
valid_lft forever preferred_lft forever2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000link/ether 8a:e1:32:e2:dd:a2 brd ff:ff:ff:ff:ff:ff
inet172.16.210.181/24 brd 172.16.210.255scope global eth0
inet172.16.210.183/32scope global eth0
inet6 fe80::88e1:32ff:fee2:dda2/64scope link
valid_lft forever preferred_lft forever3: eth1: mtu 1500 qdisc noop state DOWN qlen 1000link/ether f6:da:a4:00:84:ccbrd ff:ff:ff:ff:ff:ff
[root@my2~]#
[root@my2~]# tail -30 /var/log/messages
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: No such interface, eth1
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Netlink reflector reports IP 172.16.210.181added
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Netlink reflector reports IP fe80::88e1:32ff:fee2:dda2 added
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Registering Kernel netlink reflector
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Registering Kernel netlink command channel
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Registering gratuitous ARP shared channel
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Interface queue is empty
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: No such interface, eth1
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Netlink reflector reports IP 172.16.210.181added
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Netlink reflector reports IP fe80::88e1:32ff:fee2:dda2 added
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Registering Kernel netlink reflector
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Registering Kernel netlink command channel
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Opening file '/etc/keepalived/keepalived.conf'.
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Configuration is using : 61661Bytes
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Opening file '/etc/keepalived/keepalived.conf'.
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Configuration is using : 10232Bytes
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: Using LinkWatch kernel netlink reflector...
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Entering BACKUP STATE
Dec12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Scheduler not found
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Service not defined
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Using LinkWatch kernel netlink reflector...
Dec12 15:15:02 localhost Keepalived_healthcheckers[26719]: Activating healthchecker for service [172.16.210.181]:8306Dec12 15:15:02localhost kernel: IPVS: Scheduler module ip_vs_ not found
Dec12 15:18:34 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Transition to MASTER STATE
Dec12 15:18:35 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Entering MASTER STATE
Dec12 15:18:35 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) setting protocol VIPs.
Dec12 15:18:35 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183Dec12 15:18:35 localhost Keepalived_healthcheckers[26719]: Netlink reflector reports IP 172.16.210.183added
Dec12 15:18:40 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183[root@my2~]#
表明已经切换完成。
七、系统参数优化配置
7.1、系统配置
#手工的执行如下的动作,使之立刻生效,以下是物理机中会有,根据实际可能需要修改:echo never > /sys/kernel/mm/transparent_hugepage/enabledecho never > /sys/kernel/mm/transparent_hugepage/defragecho deadline > /sys/block/sda/queue/schedulerecho "16" > /sys/block/sda/queue/read_ahead_kbecho "512" > /sys/block/sda/queue/nr_requests
#减少预读:/sys/block/sda/queue/read_ahead_kb,默认128,调整为16
#增大队列:/sys/block/sda/queue/nr_requests,默认128,调整为512echo "16" > /sys/block/sda/queue/read_ahead_kbecho "512" > /sys/block/sda/queue/nr_requests
#如果是使用普通SAS盘的话,使用elevator=deadline
#如果是使用SSD/FLASH卡的话,使用elevator=noopecho noop > /sys/block/sda/queue/schedulerecho deadline > /sys/block/sda/queue/scheduler
#对于关闭透明大页的问题,也执行如下的操作:编辑/etc/rc.local,添加如下内容cat >> /etc/rc.local <
#echo noop > /sys/block/sda/queue/schedulerecho deadline > /sys/block/sda/queue/schedulerecho never > /sys/kernel/mm/transparent_hugepage/enabledecho never > /sys/kernel/mm/transparent_hugepage/defragecho "16" > /sys/block/sda/queue/read_ahead_kbecho "512" > /sys/block/sda/queue/nr_requestsif test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabledfi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defragfiEOF
# 修改目录权限chown -R mysql.mysql /data/mysql/
7.2、修改系统内核参数
cat >> /etc/sysctl.conf <
fs.file-max=655360fs.aio-max-nr = 1048576kernel.sem= 5050 646400 5050 128kernel.shmmax= 137438953472kernel.shmall= 4294967296kernel.shmmni= 4096net.ipv4.ip_local_port_range= 9000 65500net.ipv4.tcp_mem= 94500000 915000000 927000000net.core.wmem_default= 8388608net.core.rmem_default= 8388608net.core.rmem_max= 16777216net.core.wmem_max= 16777216net.ipv4.tcp_rmem= 4096 87380 16777216net.ipv4.tcp_wmem= 4096 65536 16777216net.core.netdev_max_backlog= 32768net.ipv4.tcp_tw_recycle= 1net.ipv4.tcp_tw_reuse= 1net.ipv4.tcp_fin_timeout= 10net.ipv4.tcp_keepalive_time= 300net.ipv4.tcp_max_syn_backlog= 32768net.ipv4.tcp_syncookies= 1net.ipv4.tcp_timestamps= 0net.ipv4.conf.default.accept_source_route= 0vm.swappiness=1EOF
sysctl-pecho "1" >/proc/sys/net/ipv4/conf/lo/arp_ignoreecho "2" >/proc/sys/net/ipv4/conf/lo/arp_announceecho "1" >/proc/sys/net/ipv4/conf/all/arp_ignoreecho "2" >/proc/sys/net/ipv4/conf/all/arp_announce
附录:新安装的虚拟机修改信息
1、修改主机名
yum -y install vim telnet unzip ziplrzszhostnameIT_rio_dev_testsed -i 's/HOSTNAME=localhost.localdomain/HOSTNAME=IT_rio_dev_test/g' /etc/sysconfig/networksed -n '/HOSTNAME/p' /etc/sysconfig/network
2、关闭selinux
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
setenforce0
3、关闭防火墙
/etc/init.d/iptables stopcat >>/etc/rc.local <
EOF
4、修改网卡ip信息
cat >/etc/sysconfig/network-scripts/ifcfg-eth0 <
TYPE=Ethernet
BOOTPROTO=static
NAME=eth0
DEVICE=eth0
ONBOOT=yes
IPADDR=172.16.210.111NETMASK=255.255.255.0GATEWAY=172.16.210.250EOF
5、重启网卡服务
/etc/init.d/network restart
6、修改dns
cat >/etc/resolv.conf <
nameserver172.16.110.11nameserver8.8.8.8EOF
7、系统初始化配置
7.1、关闭SELINUX
#修改配置文件,重启服务后永久生效。sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
#命令行设置立即生效
setenforce0
7.2、防火墙设置
cat >>/etc/rc.local <
EOFcp /etc/sysconfig/iptables /root/iptables.bakcat >/etc/sysconfig/iptables <
# Firewall configuration written by system-config-firewall
# Manual customization of thisfileis not recommended.*filter
:INPUT DROP [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT-A INPUT -p icmp -j ACCEPT-A INPUT -i lo -j ACCEPT-A INPUT -p tcp -m tcp --dport 80 -j ACCEPT-A INPUT -s 175.41.54.216/29 -p tcp -m tcp --dport 10050 -j ACCEPT-A INPUT -s 175.41.54.216/29 -p udp -m udp --dport 161 -j ACCEPT-A INPUT -s 58.71.118.56/29 -p udp -m udp --dport 161 -j ACCEPT-A INPUT -s 116.93.118.104/29 -p udp -m udp --dport 161 -j ACCEPT-A INPUT -s 58.71.118.56/29 -p tcp -m tcp --dport 22 -j ACCEPT-A INPUT -s 58.71.118.56/29 -p tcp -m tcp --dport 10050 -j ACCEPT-A INPUT -s 116.93.118.104/29 -p tcp -m tcp --dport 22 -j ACCEPT-A INPUT -s 116.93.118.104/29 -p tcp -m tcp --dport 873 -j ACCEPT-A INPUT -s 116.93.118.104/29 -p tcp -m tcp --dport 10050 -j ACCEPT-A INPUT -j REJECT --reject-with icmp-host-prohibited-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT
EOF/etc/init.d/iptables restart
7.3、安装基础依赖包
# yum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip#yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libxml2 libcurl4-openssl-dev
#yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxsltyumclean allyum -y updateyum -y install gcc-c++ gd libxml2-devel libjpeg-devel libpng-devel net-snmp-devel wget telnet vim zip unzip
yum -y install curl-devel libxslt-devel pcre-devel libjpeg libpng libcurl4-openssl-devyum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxsltyum -y install gcc automake autoconf libtool openssl-develyum -y install perl-devel perl-ExtUtils-Embedyum -y install cmake ncurses-devel.x86_64 openldap-devel.x86_64 lrzsz openssh-clients gcc-g77 bisonyum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-develyum -y install ntpdate rsync svn patch iptables iptables-servicesyum -y install libevent libevent-devel cyrus-sasl cyrus-sasl-develyum -y install gd-devel libmemcached-devel memcached git libssl-devel libyaml-devel auto make
yum -y groupinstall "Server Platform Development" "Development tools"
yum -y groupinstall "Development tools"
yum -y install gcc.x86_64 libxml2.x86_64 libxml2-devel.x86_64 openssl.x86_64 openssl-devel.x86_64 libcurl.x86_64 libcurl-devel.x86_64yum -y install gd.x86_64 gd-devel.x86_64 gcc-c++.x86_64 readline.x86_64 readline-devel.x86_64
7.4、时间同步
cat >/root/ntp.sh <
#!/bin/bash
# ntp.sh#NTP服务器数组列表
ntpServer=(
[0]=tw.pool.ntp.org
[1]=time.stdtime.gov.tw
[2]=asia.pool.ntp.org
[3]=10.30.0.5)
#校验#
serverNum=`echo \${#ntpServer[*]}`
NUM=0
for ((i=0; i<=\$serverNum; i++)); do
echo -n "正在和NTP服务器:\${ntpServer[\$NUM]}校验中..."
/usr/sbin/ntpdate \${ntpServer[\$NUM]} >> /dev/null 2>&1
if [ \$? -eq 0 ]; then
echo -e "\e[1;32m\t[成功]\e[0m"
echo -e "\e[1;32m同步成功,退出......\e[0m"breakelse
echo -e "\e[1;31m\t[失败]\e[0m"
echo -e "\e[1;31m继续同步下一个!!!!!\e[0m"let NUM++
fi
sleep 2
doneEOFchmod +x /root/ntp.sh
sh /root/ntp.sh
cat >>/etc/crontab <