服务器配置,MySQL双主互备

2019-05-03 22:24 来源:未知

一,某娱乐公司例行上线与创新流程示例

例行维护/更新流程

一、条件介绍

服务器意况

CentOs7.4 64位
jdk9
tomcat9.0.1
mysql5.7.19

1.一 更新今日

提早确认好要翻新的是怎么着,更新会有人公告你,一般是营业职员
譬如:我们前天做怎么着什么样更新

1.1、规划

序号

类别

版本

主机名

IP

端口

备注

1

OS

CentOS release 6.9 (Final) (minimal)

my1

172.16.210.180

8306

172.16.210.183

2

mysql

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下载地址:

预备工作

  • jdk安装包(http://www.oracle.com/technetwork/java/javase/downloads/jdk9-downloads-3848520.html)
  • Tomcat安装包(https://tomcat.apache.org/download-90.cgi)
  • ubuntu /opt目录下新建文件夹soft mkdir soft,将jdk及tomcat安装包copy到当前目录下

1.2 第2天更新

诚如固定点更新,会先收到更新邮件
营业人士会发公告给游戏者,说咱俩什么样什么游戏,几点打开保护
比如:10点更新:

(一)关闭游戏端口,禁止外部访问,对自个儿集团开放(更新完要测试)

透过防火墙脚本达成(参考集团已有并在线上健康使用的剧本)

(二)结束游戏程序

借使做了监督,停服前能够关闭监控,要是您明白是和煦在停服,也足以不关
以此主题素材具体要问开辟,我们集团就是终止游戏程序的时候会把数据写到数据库,所以等次第关闭后再备份。(假使不先结束游戏程序,那么会促成数据库的数额差异样)

(三)备份数据库

透过脚本实现数据备份并问下研究开发必要备份哪些库
备份完结后,检查备份的多寡大小,推断是还是不是与昨天备份的数据有未有太大的差异(差距太大标记有失常态,要求检讨)

(四)通告有关人口展开创新操作(一般是开荒,也也许是团结,根据厂商情形而定)

一旦是你和睦做创新,先看看是怎么更新(减价更新,版本更新,web更新等)
那就得依据是怎样更新,就实行什么样更新操作(开垦会给更新流程),即使有老人带,做完后总结下(那样和和气气会更清楚些)
更新内容,举个例子加多多少个文本,数据库增加字段等,纯熟后方可写成脚本

(5)运转游戏程序

当更新实现后,就开行游戏程序,以便举办上边包车型客车测试

(六)通告相关人士张开测试(研究开发,测试人士,运维职员等)

娱乐能否符合规律张开,能否健康登入,该更新的事物有没有更新好

(柒)展开娱乐端口,允许外部访问

壹旦测试没不平常,启摄人心魄士会说,能够开服了;就能够把嬉戏端口对外,正是游戏发烧友能够登录游戏
和有关人士确认好怎么时候能够告一段落游戏程序操作(停以前备份数据库依旧后来)

一.二、MySQL双主互备 keepalived高可用框架结构介绍

  MySQL主从复制架构能够在非常大程度有限帮助MySQL的高可用,在壹主多从的框架结构中还足以应用读写分离将读操作分配到从库中,缓慢消除主库压力。不过在那种架构中,主库出现故障时索要手动将1台从库提高为主库。在对写操作须要较高的条件中,主库故障在基本框架结构中会成为单点故障。因此须要主主互备架构,幸免主节点故障变成写操作失效。

  在双主互备的架构中,每台MySQL都充当主服务器,同时担当对方的从服务器。在随心所欲壹台服务器上的写操作都会被复制到另1台服务器上,从而保险了数据的可信赖性。

  在双主互备的功底上助长keepalived,在里边1台机器上绑定虚拟ip(VIP)。利用vip统1对外地劳工务,能够避免在多个节点同时写多少变成争辨。同时当keepalived主节点爆发故障时,keeplived会自动将VIP切换来备节点上,从而完结主服务器的高可用。

防火墙相关

CentOs开掘柒.X的系统默许使用的是firewall的防火墙,相关命令如下:

  • 启动:# systemctl start firewalld
  • 翻看景况:# systemctl status firewalld 或者 firewall-cmd --state
  • 停止:# systemctl disable firewalld
  • 禁用:# systemctl stop firewalld

1.3 总结

  1. 选取到更新邮件
  2. 翻开是哪些更新
  3. 差异的更新施行不一的翻新流程
  4. 和血脉相通人口承认好怎么时候能够举行翻新操作
  5. 到了创新时间后开始展览翻新流程
  6. 连上要求立异的服务器
  7. 实践防火墙脚本,关闭游戏端口,禁止外部访问,对和睦集团支付
  8. 停下游戏程序
  9. 备份数据库
  10. 照会有关人口展开革新操作
  11. 创新落成后,运行游戏程序
  12. 照会相关人士开始展览测试
  13. 测试没难题,实践防火墙脚本,开放娱乐端口,让游戏的使用者登入游戏

二、安装mysql5.7

JDK安装

  • 解压jdk文件并活动到/opt目录下
    cd /opt/soft tar -zxvf jdk-9_linux-x64_bin.tar.gz mv jdk-9 ../
  • 设置情状变量
    vi /etc/profile
    输入i ,最后扩张上边内容
export JAVA_HOME=/opt/jdk-9
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar 
export PATH=$PATH:$JAVA_HOME/bin

最后按esc键,然后输入:,最后输入:wq 保存退出,重启linux

  • 测试
    java -version

一.四 注意事项

更新前操作:

(一)收到更新邮件,能够先认同邮件中更新文件是或不是能够健康下载

机器相比较多,开采也很忙,有时会油可是生,开采并从未把包上传到固定的机器或许未有传好之类的。

(2)确认好更新维护时间

貌似是定点时间,然后运维人士要对游戏的使用者发布通知

(三)能够先连上须求操作的机器

(4)假设做了监督检查,停服前能够关闭监察和控制,即令你精晓是协调在停服,也足以不关

(5)检查娱乐开放的端口

拓展更新时:

(①)第三步关闭游戏端口并确认端口确实关闭了

(2)甘休游戏程序并确认确实关闭了(能够透过翻看进程的主意,固然是java写的顺序,直接ps -ef | grep java就可以)

(3)实行备份数据库的脚本并证实备份文件的大大小小是还是不是健康

(四)备份好了后,公告有关人口打开立异操作并认可更元正确,比对md伍,文件时间等(如若是友好更新,就领取筹算好更新的步子;纯熟后,能够写成脚本)

(伍)所有服更新完结后,运维游戏程序并承认运维成功

(⑥)布告相关职员开始展览测试,举例,是还是不是能登录游戏,登入游戏后要求改换的地点有没有改观(一般运行职员操作)

(7)测试未有失水准,实施防火墙脚本,开放娱乐端口,让游戏发烧友登录游戏

二.壹、主机M一上的操作

Tomcat安装

  • 解压tomcat文件并活动到/opt目录下
    cd /opt/soft tar -zxvf apache-tomcat-xxx.tar.gz mv apache-tomcat-9.0.1 ../
  • 修改tomcat配置(端口,线程数,虚拟主机等)
    cd ../ apache-tomcat-xxx
    vi /conf/server.xml(此处仅改换8080端口为80端口)
  • 启动tomcat
    /bin/startup.sh
  • 测试tomcat
    走访当前ip地址,展现tomcat主页即安装运营成功

一.五 游戏业务类专门的学问术语解释

  • [x] 合服:其实就是统一游戏用户数量(举例:一服和二服合并,其实就是联合游戏者的数目,将一服数量导入贰服数据库,然后一服就能够用来开三服了)
  • [x] 混服:正是同贰个服务器,运维四款不等同的玩乐
  • [x] 同服:在3个布置相比高的服务器上,同时布置三款一样的娱乐,比如火影忍者,能够安插壹服贰服等(就是将次第拷贝一份,修改端口等在起步====>类似多实例)
  • [x] 跨服:多个服的游戏的使用者在一块儿玩,比方一,二,叁,四服游戏的使用者都在联合玩。

二.1.壹、安装依赖包

yum  clean all

yum -y update

yum -y install gcc gcc-c   make autoconf automake ncurses-devel bison ncurses  cmake libaio libaio-devel  boost

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 libcurl4-openssl-dev

yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt

yum -y install gcc automake autoconf libtool openssl-devel

yum -y install perl-devel perl-ExtUtils-Embed

yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison

yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel

yum -y install ntpdate rsync svn  patch  iptables iptables-services

yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel

yum -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" 

安装现身的标题

  • 安装安城,运行健康,日志无不当,防火墙也关了但就是访问不了tomcat首页
    消除:Ali云找到服务器的实例,增添一组入方向的安全组规则(协议项目:自定义TCP 端口范围:80/80 授权类型:地址段访问 授权对象:0.0.0.0/0 ),然后浏览器壹刷新,立马就好了

二.壹.二、下载解压

rm -rf /etc/my.cnf  

mkdir -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

MySql安装

二.1.3、创设所急需的目录

mkdir  -p /data/mysql/mysql_8306/{data,logs,tmp}

Centos安装

  • 下载与安装
    实际下载的rpm路线,由https://dev.mysql.com/downloads/repo/yum/网页找出
    • wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
    • rpm -ivh mysql57-community-release-el7-11.noarch.rpm
    • yum install mysql-community-server
  • 翻开修改密码
    • 查阅密码
      grep 'temporary password' /var/log/mysqld.log
    • 修改密码强度
      vi /etc/my.cnf终极1行增加validate-password=OFF封存退出
      重启生效service mysqld restart
    • 修改密码
      登陆mysql -uroot -p
      修改SET PASSWORD = PASSWORD('密码');
  • 同意远程访问数据库
    同意持有远程机登入拜会
    sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf注释掉bind-address = 127.0.0.1
    登录本地数据库
    mysql -u用户名 -p密码
    开垦远程登入权限,允许用户在随心所欲地方报到
    GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '密码' WITH GRANT OPTION;
    重载授权表
    FLUSH PRIVILEGES;

二.一.四、更换权限

groupadd mysql

useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

ubuntu安装

  • 安装MySQL
    sudo apt-get update 更新一下apt-get
    sudo apt-get install mysql-server设置进度中注意输入数据库密码
  • 同意远程访问数据库
    同意全体远程机登入拜会
    sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf注释掉bind-address = 127.0.0.1
    登录本地数据库
    mysql -u用户名 -p密码
    开荒远程登入权限,允许用户在随便地点报到
    GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '密码' WITH GRANT OPTION;
    重载授权表
    FLUSH PRIVILEGES;
  • 闭馆防火墙
    安装防火墙管理sudo apt-get install ufw
    启用sudo ufw enable
    关闭sudo ufw disable
    翻开防火墙状态sudo ufw status
  • 起步数量库 service mysql start
  • 重启ubuntu reboot
  • 测试远程连接 mysql -h ip地址 -u用户名 -p

2.1.5、创建my.cnf

cat >/data/mysql/mysql_8306/my_8306.cnf <<EOF

#my.cnf

[client]

port            = 8306

socket          = /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 = 5

no-auto-rehash



[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql_8306/data

port = 8306

socket = /data/mysql/mysql_8306/tmp/mysql_8306.sock



#timeout

interactive_timeout = 300

wait_timeout = 300



#character set

character-set-server = utf8



open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000



skip-name-resolve = 1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = /data/mysql/mysql_8306/logs/slow.log

log-error = /data/mysql/mysql_8306/logs/error.log

log_error_verbosity = 3

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1



#tmp

tmpdir=/data/mysql/mysql_8306/tmp

event_scheduler = 1

performance_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.248

explicit_defaults_for_timestamp = 1





#binlog

binlog_format = row

server-id = 1818306

log-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 = 0

expire_logs_days = 90

#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%



#relay log

skip_slave_start = 1

max_relay_log_size = 1G

relay_log_purge = 1

relay_log_recovery = 1

log_slave_updates

#slave-skip-errors=1032,1053,1062



explicit_defaults_for_timestamp=1

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 256

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_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 = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 64M

innodb_log_file_size = 500M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_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-2                          

#auto-increment-offset = 2

#auto-increment-increment = 2



# semi sync replication 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 = 5000      

loose_rpl_semi_sync_master_trace_level = 32   

loose_rpl_semi_sync_master_wait_no_slave = on

loose_rpl_semi_sync_slave_enabled = on      

loose_rpl_semi_sync_slave_trace_level = 32

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

loose_rpl_semi_sync_master_wait_for_slave_count=1

loose_rpl_semi_sync_master_wait_point=AFTER_SYNC



slave_preserve_commit_order = 1

slave_transaction_retries = 128

log_timestamps = system

show_compatibility_56 = on

slave_parallel_workers = 16

slave_parallel_type = LOGICAL_CLOCK



loose_innodb_numa_interleave = 1

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 16

innodb_undo_log_truncate = 1

innodb_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.so

nice=-19

open-files-limit=65535

EOF

Redis安装

  • 明确gcc是或不是安装
    rpm -q gccrpm -qa | grep gcc
  • 安装gcc
    sudo yum install gcc-c
  • 解压文件
    将文件copy到/opt/soft下举行解压,并活动到/opt目录下
    tar -xvzf redis-4.0.2.tar.gz
    mv redis-4.0.2 ../
  • 编译redis
    cd redis-4.0.2/ make
    显示Hint: It's a good idea to run 'make test' ;)意味着编写翻译成功
  • 安装
    make PREFIX=/usr/local/redis install
    安装成功后在/usr/local/redis/bin目录下有可施行文件
  • copy文件
    cd /opt/redis-4.0.2/
    cp redis.conf /usr/local/redis/
  • 启动
    • 前者格局运行
      /usr/local/redis/bin/redis-server
      缺点:运行达成后不可能张开别的操作,ctrl c程序截止,不引入
    • 后端运转
      • 修改redis.conf文件,daemonize yes今后端方式运维
        vim /usr/local/redis/redis.conf
      • 运维时钦定布署文件
        cd /usr/local/redis/
        ./bin/redis-server ./redis.conf
  • 停止
    cd /usr/local/redis/
    ./bin/redis-cli shutdown
  • 连接
    cd /usr/local/redis/
    ./bin/redis-cli
  • 远程连接
    • 修改redis.conf
      /usr/local/redis/ vim redis.conf
      • redis暗中同意只允许地点访问,要使redis能够远程访问能够修改redis.conf
        bind 1二7.0.0.1,注释掉bind 12柒.0.0.一能够使具备的ip访问redis
      • 在redis三.贰之后,redis扩展了protected-mode,在这几个情势下,固然注释掉了bind 1二7.0.0.一,再拜访redisd时候依然报错,修改为protected-mode no
    • ./bin/redis-cli -h ip地址 -p 端口号

二.一.六、修改目录权限

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 &

贰.一.玖、登陆情势

/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.一.拾、创立授权修改密码

 

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';

flush privileges;

set sql_log_bin = 1;

reset  master ; reset slave all; 

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 statusG; 

二.一.1二、设置高速登入方式

[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'

二.一.一叁、快捷关闭数据库

[root@my1 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

二.一.1四、到场备忘录

[root@my1 ~]# cat  >>/root/.bashrc <<EOF

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

二.1.一五、备份脚本

[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、主机M二上的操作

二.2.1、安装信赖包

yum  clean all

yum -y update

yum -y install gcc gcc-c   make autoconf automake ncurses-devel bison ncurses  cmake libaio libaio-devel  boost

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 libcurl4-openssl-dev

yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt

yum -y install gcc automake autoconf libtool openssl-devel

yum -y install perl-devel perl-ExtUtils-Embed

yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison

yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel

yum -y install ntpdate rsync svn  patch  iptables iptables-services

yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel

yum -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"

贰.二.二、下载安装

rm -rf /etc/my.cnf  

mkdir -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.3、创设所急需的目录

mkdir  -p /data/mysql/mysql_8306/{data,logs,tmp} 

二.二.四、改造权限

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 <<EOF

#my.cnf

[client]

port            = 8306

socket          = /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 = 5

no-auto-rehash



[mysqld]

#misc

user = mysql

basedir = /usr/local/mysql

datadir = /data/mysql/mysql_8306/data

port = 8306

socket = /data/mysql/mysql_8306/tmp/mysql_8306.sock



#timeout

interactive_timeout = 300

wait_timeout = 300



#character set

character-set-server = utf8



open_files_limit = 65535

max_connections = 100

max_connect_errors = 100000



skip-name-resolve = 1

#logs

log-output=file

slow_query_log = 1

slow_query_log_file = /data/mysql/mysql_8306/logs/slow.log

log-error = /data/mysql/mysql_8306/logs/error.log

log_error_verbosity = 3

pid-file = mysql.pid

long_query_time = 1

#log-slow-admin-statements = 1

#log-queries-not-using-indexes = 1

log-slow-slave-statements = 1



#tmp

tmpdir=/data/mysql/mysql_8306/tmp

event_scheduler = 1

performance_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.248

explicit_defaults_for_timestamp = 1





#binlog

binlog_format = row

server-id = 1818306

log-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 = 0

expire_logs_days = 90

#replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%





#relay log

skip_slave_start = 1

max_relay_log_size = 1G

relay_log_purge = 1

relay_log_recovery = 1

log_slave_updates

#slave-skip-errors=1032,1053,1062



explicit_defaults_for_timestamp=1

#buffers & cache

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache = 2048

max_heap_table_size = 96M

sort_buffer_size = 2M

join_buffer_size = 2M

thread_cache_size = 256

query_cache_size = 0

query_cache_type = 0

query_cache_limit = 256K

query_cache_min_res_unit = 512

thread_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 = 1

innodb_data_file_path = ibdata1:1G:autoextend

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 64M

innodb_log_file_size = 500M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

innodb_rollback_on_timeout

innodb_status_file = 1

innodb_io_capacity = 2000

transaction_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-2                          

auto-increment-offset = 2

auto-increment-increment = 2



# semi sync replication 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 = 5000      

loose_rpl_semi_sync_master_trace_level = 32   

loose_rpl_semi_sync_master_wait_no_slave = on

loose_rpl_semi_sync_slave_enabled = on      

loose_rpl_semi_sync_slave_trace_level = 32

loose_rpl_semi_sync_master_enabled = 1

loose_rpl_semi_sync_slave_enabled = 1

loose_rpl_semi_sync_master_timeout = 5000

loose_rpl_semi_sync_master_wait_for_slave_count=1

loose_rpl_semi_sync_master_wait_point=AFTER_SYNC



slave_preserve_commit_order = 1

slave_transaction_retries = 128

log_timestamps = system

show_compatibility_56 = on

slave_parallel_workers = 16

slave_parallel_type = LOGICAL_CLOCK



loose_innodb_numa_interleave = 1

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 16

innodb_undo_log_truncate = 1

innodb_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.so

nice=-19

open-files-limit=65535

EOF 

2.2.陆、修改目录权限

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 &

二.②.九、登录格局

/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';

flush privileges;

set sql_log_bin = 1;

reset  master ; reset slave all; 

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 statusG;

2.2.1二、设置高速登录格局

[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.二.一三、急迅关闭数据库

[root@my2 local]#alias mysql.8306.stop='/usr/local/mysql/bin/mysqladmin --login-path=8306_localhost_login shutdown'

2.二.1四、参与备忘录

[root@my2 ~]# cat  >>/root/.bashrc <<EOF

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.①5、备份脚本

[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

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 6

Server version: 5.7.20-log MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



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



[root@localhost][(none)]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| sys                |

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

4 rows in set (0.00 sec)



[root@localhost][(none)]> create database db1;

Query OK, 1 row affected (0.01 sec)



[root@localhost][(none)]> show databases;

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

| Database           |

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

| information_schema |

| db1                |

| mysql              |

| performance_schema |

| sys                |

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

5 rows in set (0.00 sec)



[root@localhost][(none)]>

[root@localhost][(none)]> show slave statusG;

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: 172.16.210.181

                  Master_User: rpl_user

                  Master_Port: 8306

                Connect_Retry: 10

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 150

               Relay_Log_File: my1-relay-bin.000003

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000001

             Slave_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: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 150

              Relay_Log_Space: 721

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 0

                  Master_UUID: d14b54b4-de49-11e7-96ea-8ae132e2dda2

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_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:1

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)



ERROR:

No query specified



[root@localhost][(none)]> 

3.2、在my2中操作

[root@my2 local]# mysql.8306.login

Welcome to the MySQL monitor.  Commands end with ; or g.

Your MySQL connection id is 32

Server version: 5.7.20-log MySQL Community Server (GPL)



Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.



Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.



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



[root@localhost][(none)]> show databases;

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

| Database           |

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

| information_schema |

| db1                |

| mysql              |

| performance_schema |

| sys                |

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

5 rows in set (0.00 sec)



[root@localhost][(none)]> create database db2;

Query OK, 1 row affected (0.00 sec)



[root@localhost][(none)]> show slave statusG;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 172.16.210.180

                  Master_User: rpl_user

                  Master_Port: 8306

                Connect_Retry: 10

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 303

               Relay_Log_File: my2-relay-bin.000003

                Relay_Log_Pos: 508

        Relay_Master_Log_File: mysql-bin.000003

             Slave_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: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 303

              Relay_Log_Space: 910

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1808306

                  Master_UUID: ce20a632-de49-11e7-9587-c2c763ed137c

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1

            Executed_Gtid_Set: ce20a632-de49-11e7-9587-c2c763ed137c:1,

d14b54b4-de49-11e7-96ea-8ae132e2dda2:1

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)



ERROR:

No query specified

同步正常 

4、同步故障管理比如

# 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: 628306

                  Master_UUID: 11526eb0-fcbc-11e6-af7d-005056b937e2

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp: 170227 15:44:06

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1-2

            Executed_Gtid_Set: 11526eb0-fcbc-11e6-af7d-005056b937e2:1,

1760a7a5-fcbc-11e6-8f14-005056b90358:1

                Auto_Position: 1

         Replicate_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 statusG; 

5、配置keeplived落成高可用

参考资料:

http://www.linuxidc.com/Linux/2015-06/118767.htm

五.1、在my第11中学的操作

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/keepalived

make && 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



# server1

cat >/etc/keepalived/keepalived.conf <<EOF

! Configuration File forkeepalived

global_defs {

# notification_email {

# test@sina.com

#  }

# notification_email_from  admin@test.com

# smtp_server 127.0.0.1

# smtp_connect_timeout 30

router_id MYSQL_HA18      #标识,双主相同

 }

vrrp_instance VI_1 {

 state BACKUP           #两台都设置BACKUP

 interface eth0

 virtual_router_id 18       #主备相同

 priority 100           #优先级,backup设置90

 advert_int 1

 nopreempt             #不主动抢占资源,只在master这台优先级高的设置,backup不设置

 authentication {

 auth_type PASS

 auth_pass 1111

 }

 virtual_ipaddress {

 172.16.210.183

 }

}

virtual_server 172.16.210.183 8306 {

 delay_loop 2

 #lb_algo rr              #LVS算法,用不到,我们就关闭了

 #lb_kind DR              #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL

 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器

 protocol TCP

 real_server 172.16.210.180 8306 {   #检测本地mysql,backup也要写检测本地mysql

 weight 3

 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时,执行此脚本,杀死keepalived实现切换

 TCP_CHECK {

 connect_timeout 3    #连接超时

 nb_get_retry 3       #重试次数

 delay_before_retry 3 #重试间隔时间

  }

}

EOF



cat >/usr/local/keepalived/mysql.sh <<EOF

#!/bin/bash

pkill keepalived

EOF

chmod  x /usr/local/keepalived/mysql.sh

chmod  x /etc/init.d/keepalived

/etc/init.d/keepalived start 

5.二、在my第22中学的操作

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/keepalived

make && 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



# server2

cat >/etc/keepalived/keepalived.conf <<EOF

cat /etc/keepalived/keepalived.conf

! Configuration File forkeepalived

global_defs {

# notification_email {

# test@sina.com

#  }

# notification_email_from  admin@test.com

# smtp_server 127.0.0.1

# smtp_connect_timeout 30

router_id MYSQL_HA18      #标识,双主相同

 }

vrrp_instance VI_1 {

 state BACKUP           #两台都设置BACKUP

 interface eth0

 virtual_router_id 18       #主备相同

 priority 90           #优先级,backup设置90

 advert_int 1

 #nopreempt             #不主动抢占资源,只在master这台优先级高的设置,backup不设置

 authentication {

 auth_type PASS

 auth_pass 1111

 }

 virtual_ipaddress {

 172.16.210.183

 }

}

virtual_server 172.16.210.183 8306 {

 delay_loop 2

 #lb_algo rr              #LVS算法,用不到,我们就关闭了

 #lb_kind DR              #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL

 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器

 protocol TCP

 real_server 172.16.210.181 8306 {   #检测本地mysql,backup也要写检测本地mysql

 weight 3

 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时,执行此脚本,杀死keepalived实现切换

 TCP_CHECK {

 connect_timeout 3    #连接超时

 nb_get_retry 3       #重试次数

 delay_before_retry 3 #重试间隔时间

  }

}

EOF



cat >/usr/local/keepalived/mysql.sh <<EOF

#!/bin/bash

pkill keepalived

EOF

chmod  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

Dec 12 15:12:46 localhost Keepalived_healthcheckers[13090]: IPVS: Service not defined

Dec 12 15:12:46 localhost Keepalived_healthcheckers[13090]: Using LinkWatch kernel netlink reflector...

Dec 12 15:12:46 localhost Keepalived_healthcheckers[13090]: Activating healthchecker for service [172.16.210.180]:8306

Dec 12 15:12:46 localhost kernel: IPVS: Scheduler module ip_vs_ not found

Dec 12 15:12:50 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Transition to MASTER STATE

Dec 12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Entering MASTER STATE

Dec 12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) setting protocol VIPs.

Dec 12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183

Dec 12 15:12:51 localhost Keepalived_healthcheckers[13090]: Netlink reflector reports IP 172.16.210.183 added

Dec 12 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

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Opening file '/etc/keepalived/keepalived.conf'.

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Configuration is using : 10232 Bytes

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Using LinkWatch kernel netlink reflector...

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Entering BACKUP STATE

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Scheduler not found

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Service not defined

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Using LinkWatch kernel netlink reflector...

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Activating healthchecker for service [172.16.210.181]:8306

Dec 12 15:15:02 localhost kernel: IPVS: Scheduler module ip_vs_ not found

[root@my2 ~]# 

6.3、关闭my1的mysql服务

[root@my1 ~]# ps -ef|grep mysql

root     13119 10926  0 15:15 pts/0    00:00:00 grep mysql

root     19296     1  0 Dec11 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_8306/my_8306.cnf

mysql    20709 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|grep mysql

root     13138 10926  0 15:18 pts/0    00:00:00 grep mysql

[root@my1 ~]#



#  查看my1的日志

[root@my1 ~]# tail -20 /var/log/messages  

Dec 12 15:12:46 localhost Keepalived_healthcheckers[13090]: IPVS: Service not defined

Dec 12 15:12:46 localhost Keepalived_healthcheckers[13090]: Using LinkWatch kernel netlink reflector...

Dec 12 15:12:46 localhost Keepalived_healthcheckers[13090]: Activating healthchecker for service [172.16.210.180]:8306

Dec 12 15:12:46 localhost kernel: IPVS: Scheduler module ip_vs_ not found

Dec 12 15:12:50 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Transition to MASTER STATE

Dec 12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Entering MASTER STATE

Dec 12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) setting protocol VIPs.

Dec 12 15:12:51 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183

Dec 12 15:12:51 localhost Keepalived_healthcheckers[13090]: Netlink reflector reports IP 172.16.210.183 added

Dec 12 15:12:56 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183

Dec 12 15:18:34 localhost Keepalived_healthcheckers[13090]: TCP connection to [172.16.210.180]:8306 failed !!!

Dec 12 15:18:34 localhost Keepalived_healthcheckers[13090]: Removing service [172.16.210.180]:8306 from VS [172.16.210.183]:8306

Dec 12 15:18:34 localhost Keepalived_healthcheckers[13090]: IPVS: Service not defined

Dec 12 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]:8306

Dec 12 15:18:34 localhost Keepalived_healthcheckers[13090]: Lost quorum 1-0=1 > 0 for VS [172.16.210.183]:8306

Dec 12 15:18:34 localhost Keepalived[13089]: Stopping Keepalived v1.2.7 (12/12,2017)

Dec 12 15:18:34 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) sending 0 priority

Dec 12 15:18:34 localhost Keepalived_vrrp[13091]: VRRP_Instance(VI_1) removing protocol VIPs.

Dec 12 15:18:34 localhost Keepalived_healthcheckers[13090]: Netlink reflector reports IP 172.16.210.183 removed

Dec 12 15:18:34 localhost Keepalived_healthcheckers[13090]: IPVS: No such service

[root@my1 ~]# 

陆.4、查看my2的日志及其vip情状

[root@my2 ~]# ip add

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

    inet6 ::1/128 scope host

       valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

    link/ether 8a:e1:32:e2:dd:a2 brd ff:ff:ff:ff:ff:ff

    inet 172.16.210.181/24 brd 172.16.210.255 scope global eth0

    inet 172.16.210.183/32 scope global eth0

    inet6 fe80::88e1:32ff:fee2:dda2/64 scope link

       valid_lft forever preferred_lft forever

3: eth1: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN qlen 1000

    link/ether f6:da:a4:00:84:cc brd ff:ff:ff:ff:ff:ff

[root@my2 ~]#

[root@my2 ~]# tail  -30 /var/log/messages

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: No such interface, eth1

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Netlink reflector reports IP 172.16.210.181 added

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Netlink reflector reports IP fe80::88e1:32ff:fee2:dda2 added

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Registering Kernel netlink reflector

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Registering Kernel netlink command channel

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Registering gratuitous ARP shared channel

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Interface queue is empty

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: No such interface, eth1

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Netlink reflector reports IP 172.16.210.181 added

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Netlink reflector reports IP fe80::88e1:32ff:fee2:dda2 added

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Registering Kernel netlink reflector

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Registering Kernel netlink command channel

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Opening file '/etc/keepalived/keepalived.conf'.

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Configuration is using : 61661 Bytes

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Opening file '/etc/keepalived/keepalived.conf'.

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Configuration is using : 10232 Bytes

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: Using LinkWatch kernel netlink reflector...

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Entering BACKUP STATE

Dec 12 15:15:02 localhost Keepalived_vrrp[26721]: VRRP sockpool: [ifindex(2), proto(112), fd(10,11)]

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Scheduler not found

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: IPVS: Service not defined

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Using LinkWatch kernel netlink reflector...

Dec 12 15:15:02 localhost Keepalived_healthcheckers[26719]: Activating healthchecker for service [172.16.210.181]:8306

Dec 12 15:15:02 localhost kernel: IPVS: Scheduler module ip_vs_ not found

Dec 12 15:18:34 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Transition to MASTER STATE

Dec 12 15:18:35 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Entering MASTER STATE

Dec 12 15:18:35 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) setting protocol VIPs.

Dec 12 15:18:35 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183

Dec 12 15:18:35 localhost Keepalived_healthcheckers[26719]: Netlink reflector reports IP 172.16.210.183 added

Dec 12 15:18:40 localhost Keepalived_vrrp[26721]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 172.16.210.183

[root@my2 ~]#

表明已经切换完成。 

七、系统参数优化布局

七.一、系统安顿

#手工的执行如下的动作,使之立刻生效,以下是物理机中会有,根据实际可能需要修改:

echo never > /sys/kernel/mm/transparent_hugepage/enabled

echo never > /sys/kernel/mm/transparent_hugepage/defrag

echo deadline > /sys/block/sda/queue/scheduler

echo "16" > /sys/block/sda/queue/read_ahead_kb

echo "512" > /sys/block/sda/queue/nr_requests





#减少预读:/sys/block/sda/queue/read_ahead_kb,默认128,调整为16

#增大队列:/sys/block/sda/queue/nr_requests,默认128,调整为512



echo "16" > /sys/block/sda/queue/read_ahead_kb

echo "512" > /sys/block/sda/queue/nr_requests



#如果是使用普通SAS盘的话,使用elevator=deadline

#如果是使用SSD/FLASH卡的话,使用elevator=noop

echo noop > /sys/block/sda/queue/scheduler

echo deadline > /sys/block/sda/queue/scheduler



#对于关闭透明大页的问题,也执行如下的操作:编辑 /etc/rc.local,添加如下内容



cat >> /etc/rc.local <<EOF

#echo noop > /sys/block/sda/queue/scheduler

echo deadline > /sys/block/sda/queue/scheduler

echo never > /sys/kernel/mm/transparent_hugepage/enabled

echo never > /sys/kernel/mm/transparent_hugepage/defrag

echo "16" > /sys/block/sda/queue/read_ahead_kb

echo "512" > /sys/block/sda/queue/nr_requests



if test -f /sys/kernel/mm/transparent_hugepage/enabled; then

   echo never > /sys/kernel/mm/transparent_hugepage/enabled

fi

if test -f /sys/kernel/mm/transparent_hugepage/defrag; then

   echo never > /sys/kernel/mm/transparent_hugepage/defrag

fi

EOF



# 修改目录权限

chown -R mysql.mysql /data/mysql/ 

7.二、修改系统基本参数

cat >> /etc/sysctl.conf <<EOF

fs.file-max=655360

fs.aio-max-nr = 1048576

kernel.sem = 5050 646400 5050 128

kernel.shmmax = 137438953472

kernel.shmall = 4294967296

kernel.shmmni = 4096

net.ipv4.ip_local_port_range = 9000 65500

net.ipv4.tcp_mem = 94500000 915000000 927000000

net.core.wmem_default = 8388608

net.core.rmem_default = 8388608

net.core.rmem_max = 16777216

net.core.wmem_max = 16777216

net.ipv4.tcp_rmem = 4096 87380 16777216

net.ipv4.tcp_wmem = 4096 65536 16777216

net.core.netdev_max_backlog = 32768

net.ipv4.tcp_tw_recycle = 1

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_fin_timeout = 10

net.ipv4.tcp_keepalive_time = 300

net.ipv4.tcp_max_syn_backlog = 32768

net.ipv4.tcp_syncookies = 1

net.ipv4.tcp_timestamps = 0

net.ipv4.conf.default.accept_source_route = 0

vm.swappiness=1

EOF



sysctl -p



echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore       

echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce       

echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore       

echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce 

附录:新安装的虚拟机械修理改消息

一、修改主机名

yum -y install vim telnet unzip zip lrzsz

hostname IT_rio_dev_test

sed -i 's/HOSTNAME=localhost.localdomain/HOSTNAME=IT_rio_dev_test/g' /etc/sysconfig/network

sed -n '/HOSTNAME/p' /etc/sysconfig/network

2、关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

setenforce 0

三、关闭防火墙

/etc/init.d/iptables  stop

cat >>/etc/rc.local <<EOF

/etc/init.d/iptables  stop

EOF

4、修改网卡ip音信

cat >/etc/sysconfig/network-scripts/ifcfg-eth0 <<EOF

TYPE=Ethernet

BOOTPROTO=static

NAME=eth0

DEVICE=eth0

ONBOOT=yes

IPADDR=172.16.210.111

NETMASK=255.255.255.0

GATEWAY=172.16.210.250

EOF

5、重启网卡服务

/etc/init.d/network restart

6、修改dns

cat >/etc/resolv.conf <<EOF

nameserver 172.16.110.11

nameserver 8.8.8.8

EOF

7、系统开头化配置

7.1、关闭SELINUX

#修改配置文件,重启服务后永久生效。

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

#命令行设置立即生效

setenforce 0

七.二、防火墙设置

cat >>/etc/rc.local <<EOF

/etc/init.d/iptables  stop

EOF



cp /etc/sysconfig/iptables /root/iptables.bak

cat >/etc/sysconfig/iptables <<EOF

# Firewall configuration written by system-config-firewall

# Manual customization of this file is 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.叁、安装基础信赖包

# 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 libxslt

yum  clean all

yum -y update

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 libcurl4-openssl-dev

yum -y install libcurl-devel libcurl freetype-config freetype freetype-devel unixODBC libxslt

yum -y install gcc automake autoconf libtool openssl-devel

yum -y install perl-devel perl-ExtUtils-Embed

yum -y install cmake ncurses-devel.x86_64  openldap-devel.x86_64 lrzsz  openssh-clients gcc-g77  bison

yum -y install libmcrypt libmcrypt-devel mhash mhash-devel bzip2 bzip2-devel

yum -y install ntpdate rsync svn  patch  iptables iptables-services

yum -y install libevent libevent-devel  cyrus-sasl cyrus-sasl-devel

yum -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_64

yum -y install gd.x86_64 gd-devel.x86_64  gcc-c  .x86_64 readline.x86_64 readline-devel.x86_64

七.肆、时间同步

cat >/root/ntp.sh <<EOF

#!/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;32mt[成功]e[0m"

        echo -e "e[1;32m同步成功,退出......e[0m"

        break

    else

        echo -e "e[1;31mt[失败]e[0m"

        echo -e "e[1;31m继续同步下一个!!!!!e[0m"

        let NUM  

    fi

    sleep 2

done

EOF

chmod  x /root/ntp.sh

sh /root/ntp.sh



cat >>/etc/crontab <<EOF

01 * * * *  /root/ntp.sh

#0 0 */5 * * /bin/bash /root/cutlog.sh

EOF

本文永远更新链接地址:http://www.linuxidc.com/Linux/2017-12/149495.htm

图片 1

TAG标签: 韦德娱乐1946
版权声明:本文由韦德娱乐1946_韦德娱乐1946网页版|韦德国际1946官网发布于韦德国际1946官网,转载请注明出处:服务器配置,MySQL双主互备