Keepalived building master and slave MySQL

MySQL Master master installation configuration:
Pre-Installation Confirmation: Version number, check code (md5sum), port number, installation path
Installation dependencies:
Yum install gcc gcc-c++-y
Yum Install Cmake-y
Yum Install Ncurses-devel libtool-y
Yum Install Bison-y

Create the required folders for MySQL:
Mkdir/usr/local/mysql/{etc,logs,data}
Mkdir/usr/local/mysql/logs/{bin-log,relay-log,slow-log,error-log}

Chown Mysql:mysql/usr/local/mysql

Create a MySQL program to run the user:
sudo useradd–s/sbin/nologin MySQL

Compile and install:
cmake-dcmake_install_prefix=/usr/local/mysql/-dmysql_datadir=/usr/local/mysql/data-dmysql_user=mysql
Make && make install
Note: If the compilation error, you can first find/-name CMakeCache.txt, perform the deletion, and then troubleshoot errors based on the error, perform recompilation

To assign folder permissions:
Chown–r Mysql:mysql/usr/local/mysql

Configuration file configuration:
CP SUPPORT-FILES/MY-INNODB-HEAVY-4G.CNF/ETC/MY.CNF

初始化mysql:        /usr/local/mysql/scripts/mysql_install_db --user=mysql  --defaults-file=/usr/local/mysql/etc/my.cnf --basedir=/usr/local/mysql/basedir --datadir=/usr/local/mysql/data

Start Mysql:usr/local/mysql/basedir/bin/mysqld_safe--user=mysql

停止：/usr/local/mysql/basedir/bin/mysqladmin  

(Stopping the process by kill is prohibited)
After rewriting the configuration file, start the error:
View error log troubleshooting error/usr/local/mysql/logs/error-log/
Check the error, Baidu experience needs reinitialization, after initialization to solve the problem

MySQL Create user Modify permissions:
Set password for Root=password (' 12345678 ')
Grant all privileges in . to ' SAS ' @ ' percent ' identified by ' 12345678 ';
Delete from Mysql.user where password= "";
Master root: Password: 12345678
From root: Password: 12345678

Create a database user that is available for master-slave replication:
Grant replication Slave on . to ' slave ' @ '% ' identified by ' 12345678 ';

View binary logs, master replication:
Show master status;
Stop slave;
Change Master to master_host= ' 192.168.102.26 ', master_user= ' slave ', master_password= ' 12345678 ', master_log_file= ' Binlog.000001 ', master_port=3306,master_log_pos=244;
Start slave;
show slave status;

System Services:
Ln-s/usr/local/mysql/bin/ /usr/bin
Ln-s/usr/local/mysql/lib/ /usr/lib
CP Support-files/mysql.server/etc/init.d/mysqld-p
chmod 755/etc/init.d/mysqld
Chown Mysql.mysql-r/usr/local/mysql
Chkconfig mysqld on

Install keepalived:
TAR–XF keepalived-1.2.7.tar.gz
./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-pv/etc/keepalived
cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/
Ln-s/usr/local/keepalived/sbin/keepalived/sbin/

To modify a configuration file:

Vim/etc/keepalived/keepalived.conf
! Configuration File for Keepalived

Global_defs {

 notification_email {  [email protected]  }   notification_email_from [email protected]  smtp_server mail.163.com  smtp_connect_timeout 30  router_id MySQL-ha  }  

Vrrp_instance Vi_1 {

 state BACKUP   #两台配置此处均是BACKUP   interface eth0  #网卡，可使用ifconfig查看 virtual_router_id 51  priority 100   #优先级，另一台改为90   advert_int 1  nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置   authentication {  auth_type PASS  auth_pass keepalivedpass  }   virtual_ipaddress {  192.168.1.200  #虚拟ip }   }  

Virtual_server 192.168.1.200 3306 {

 delay_loop 2   #每个2秒检查一次real_server状态   lb_algo wrr   #LVS算法   lb_kind DR    #LVS模式   persistence_timeout 60   #会话保持时间   protocol TCP  real_server 192.168.1.104 3306 {    #真实ip weight 3  notify_down /etc/keepalived/check_keepalived  #检测到服务down后执行的脚本   TCP_CHECK {  connect_timeout 10    #连接超时时间   nb_get_retry 3       #重连次数   delay_before_retry 3   #重连间隔时间   connect_port 3306   #健康检查端口   }  

}
}

Script executed after editing the service down:
Vim/etc/keepalived/check_keepalived
#!/bin/bash
Mysql=/usr/local/mysql/bin/mysql
mysql_host=192.168.100.102 #根据实际填写
Mysql_user=root
mysql_password=123456
Check_time=3
#mysql is working mysql_ok are 1, MySQL down MYSQL_OK is 0
Mysql_ok=1
function Check_mysql_helth () {
$MYSQL-H $MYSQL _host-u $MYSQL _user-p${mysql_password}-e "show status;" >/dev/null 2>&1
If [$? = 0];then
Mysql_ok=1
echo MySQL OK
Else
Mysql_ok=0
echo MySQL no ok
Fi
return $MYSQL _OK
}

While [$CHECK _time-ne 0]
Do
Let "check_time-= 1"
Check_mysql_helth
if [$MYSQL _OK = 1]; Then
Check_time=0
Exit 0
Fi
If [$MYSQL _ok-eq 0] && [$CHECK _time-eq 0]
Then
/etc/init.d/keepalived stop
Exit 1
Fi
Sleep 1
Done

Start keepalived:
Service keepalived Restart

Query VIP:
Ip Addr
Firewall Release VRRP protocol
Firewall Open
#iptables?-I.? Input?-i?eth0?-d?224.0.0.0/8?-p?vrrp?-j? ACCEPT
#iptables?-I.? Output?-o?eth0?–d 224.0.0.0/8?-p?vrrp?-j? ACCEPT
Iptables-a input-i eth0-p vrrp-s 192.168.1.107-j ACCEPT (IP address of each other)
Or
Iptables-a input-p vrrp-j ACCEPT
Verify:
1, in the main library to perform additions and deletions, in from the library to observe whether the state is synchronized
2, in the library to perform additions and deletions, in the main library to observe whether the state is synchronized
3, stop the main keepalived, check the IP from, whether the binding is successful

Summary of issues:
1, Permissions issues:
Symptom: Initialization is not reported successfully, no error log
Analysis: Could be an exception caused by an inability to write
Scenario: Check user permissions, folder permissions (including permissions for the first-level directory)
2. Synchronization failure:
Symptom: View from status, show OK
Analysis: May be due to a problem with initial synchronization, check the status of the two library
Scenario: Resync Master-slave database, if still unable to sync, may be due to tar package corruption, view MD5 value, re-download the installation
3, 5.5 and 5.6 differences:
Two versions set different character sets, version 5.6 adds the concept of UUID
4, keepalived bind VIP failure:
Scenario: Checking for configuration file issues
1) is there an extra part
2) Check firewall configuration, Release VRRP protocol
3) Put the Check health status script under the mail configuration

Keepalived building master and slave MySQL