mysql主主安装视频教程_1-mysql主主安装脚本

#!/bin/bash

set-e

set-x

#定义全局变量

node1=10.134.224.159node2=10.134.224.161endnode1=159endnode2=161slaveuser="slave"slavepass="Slave@123"em=`route -n|grep "UG"|awk '{print $NF}'|uniq`

node=`ifconfig $em|grep netmask|awk '{print $2}'`

#刪掉依賴

#yum remove -y mariadb-libs*#[ $? -eq 0 ] && echo "del ok" || echo "del error"#安装包yum -y install mysql-community-client.x86_64 mysql-community-devel.x86_64 mysql-community-server.x86_64 mysql-community-common.x86_64 > /dev/null 2>&1#[ $? -eq 0 ] && echo "install ok" || echo "install fail"#调配置及初始化groupsmysql

[ $? -eq 0 ] && echo "group mysql exsit" ||groupadd mysqlidmysql

[ $? -eq 0 ] && echo "mysql already exsit" || useradd mysql -g mysql -s /sbin/nologin -M

[-d /data/mysql ] || mkdir -p /data/mysqlchown -R mysql:mysql /data/mysqlsed -i 's/^datadir=.*/datadir=\/data\/mysql/g' /etc/my.cnf

systemctl start mysqld.service

#查找原始密码

PASS=$(grep "temporary password" /var/log/mysqld.log |tail -n 1|awk '{print $NF}')

[ $? -eq 0 ] && echo "log pass get ok" || echo "log pass fail"#修改root密码

mysql-uroot -p$PASS --connect-expired-password -e "alter user 'root'@'localhost' identified by 'Foxconn@123';flush privileges;"[ $? -eq 0 ] && echo "change root pass ok" || echo "change root pass fail!!!"mysql-uroot -pFoxconn@123 --connect-expired-password -e "flush privileges;"#调主主配置

systemctl stop mysqld

systemctl start mysqld

systemctl enable mysqld

#配置my.cnfif [ $node == $node1 ]; then

mv /etc/my.cnf /etc/my.cnf.bakcat >/etc/my.cnf<

[client]

port= 3306default-character-set =utf8

socket= /var/lib/mysql/mysql.sock

user=root

password= "Foxconn@123"[mysqld]

#normal

port= 3306datadir= /data/mysql

socket= /var/lib/mysql/mysql.sock

tmpdir= /tmp

pid-file = /data/mysql/mysqld.pid

log_error= /var/log/mysqld_error.log

expire_logs_days= 7#relay

server_id=$endnode1

log-bin = $endnode1-binlog

relay_log= $endnode1-relay-bin

auto_increment_increment= 2auto_increment_offset= 1#binlog and cache

binlog-ignore-db =mysql

binlog_format=ROW

binlog_cache_size=128m

max_binlog_cache_size=512m

table_open_cache= 8000#skip-grant-tables

#skip-networking

explicit_defaults_for_timestamp= 1#connect

connect_timeout= 20wait_timeout= 86400max_connections= 2000max_user_connections= 1900max_connect_errors= 100000max_allowed_packet=1G

#utf8

character-set-server =utf8

collation-server =utf8_bin

#innodb

innodb_file_per_table= 1innodb_log_file_size=1024m

innodb_log_buffer_size=256m

innodb_file_format=Barracuda

skip-name-resolve

EOF

systemctl stop mysqld.service

systemctl start mysqld.service

#创建slave用户

mysql-e "grant replication slave,replication client on *.* to"slave"@"$node2"identified by 'Slave@123';"mysql-e "flush privileges;"[ $? -eq 0 ] && echo "slave create ok" && echo "slave create fail"#这里设置sleep 30是为了让node1,node2配置了slave用户sleep 30binfile=$(echo $(mysql -h $node2 -uslave -pSlave@123 --connect-expired-password -e "show master status;" 2>/dev/null)|grep binlog|awk '{print$(NF-2)}')

pos=$(echo $(mysql -uroot -pFoxconn@123 --connect-expired-password -e "show master status;" 2>/dev/null)|grep binlog|awk '{print$(NF-1)}')

#通过node2的slave账号去查binlogfile和pos

sql=$(echo "change master to master_host='$node2',master_user='slave',master_password='Slave@123',master_port=3306,master_log_file='$binfile',master_log_pos=$pos;")

mysql-uroot -pFoxconn@123 --connect-expired-password -e "$sql"#mysql-uroot -pFoxconn@123 --connect-expired-password -e "change master to master_host="$node2",master_user='slave',master_password='Slave@123',master_port=3306,master_log_file="$binfile",master_log_pos="$pos";"[ $? -eq 0 ] && echo "change master ok" || echo "change master fail"#启动slave

mysql-e "start slave;"

else

mv /etc/my.cnf /etc/my.cnf.bakcat >/etc/my.cnf<

[client]

port= 3306default-character-set =utf8

socket= /var/lib/mysql/mysql.sock

user=root

password= "Foxconn@123"[mysqld]

#normal

port= 3306datadir= /data/mysql

socket= /var/lib/mysql/mysql.sock

tmpdir= /tmp

pid-file = /data/mysql/mysqld.pid

log_error= /var/log/mysqld_error.log

expire_logs_days= 7#relay

server_id=$endnode2

log-bin = $endnode2-binlog

relay_log= $endnode2-relay-bin

auto_increment_increment= 2auto_increment_offset= 2#binlog and cache

binlog-ignore-db =mysql

binlog_format=ROW

binlog_cache_size=128m

max_binlog_cache_size=512m

table_open_cache= 8000#skip-grant-tables

#skip-networking

explicit_defaults_for_timestamp= 1#connect

connect_timeout= 20wait_timeout= 86400max_connections= 2000max_user_connections= 1900max_connect_errors= 100000max_allowed_packet=1G

#utf8

character-set-server =utf8

collation-server =utf8_bin

#innodb

innodb_file_per_table= 1innodb_log_file_size=1024m

innodb_log_buffer_size=256m

innodb_file_format=Barracuda

skip-name-resolve

EOF

systemctl stop mysqld.service

systemctl start mysqld.service

#创建slave用户

mysql-e "grant replication slave,replication client on *.* to 'slave'@"$node1"identified by 'Slave@123';"mysql-e "flush privileges;"[ $? -eq 0 ] && echo "slave create ok" && echo "slave create fail"#这里设置sleep 30是为了让node1,node2配置了slave用户sleep 20#通过node1的slave账号去查binlogfile和pos

binfile=$(echo $(mysql -h $node1 -uslave -pSlave@123 --connect-expired-password -e "show master status;" 2>/dev/null)|grep binlog|awk '{print$(NF-2)}')

pos=$(echo $(mysql -h $node1 -uslave -pSlave@123 --connect-expired-password -e "show master status;" 2>/dev/null)|grep binlog|awk '{print$(NF-1)}')

#change master to

sql=$(echo "change master to master_host='$node1',master_user='slave',master_password='Slave@123',master_port=3306,master_log_file='$binfile',master_log_pos=$pos;")

#mysql-e "change master to master_host=$node1,master_user='slave',master_password='Slave@123',master_port=3306,master_log_file="$binfile",master_log_pos="$pos";"mysql-e "$sql"[ $? -eq 0 ] && echo "change master ok" || echo "change master fail"#启动

mysql-e "start slave;"

fi