軟件環境：mysql軟件包：mysql-5.6.17.tar.gzcmake軟件包：cmake-2.8.12.2.tar.gz @ ?wget http://www.cmake.org/files/v2.8/cmake-2.8.12.2.tar.gz
系統版本：
[root@db src]# uname -a
Linux db.jrzj.com 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

[root@backup yum.repos.d]# uname -a
Linux backup.jrzj.com 2.6.32-358.el6.x86_64 #1 SMP Fri Feb 22 00:31:26 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux開始安裝：==============1:安裝cmake：[root@db cmake-2.8.12.2]# yum install gcc
[root@db cmake-2.8.12.2]# yum install gcc-C++
[root@db cmake-2.8.12.2]# yum install make
[root@db cmake-2.8.12.2]# ./configure
[root@db cmake-2.8.12.2]# make && make install2:安裝mysql為mysql創建用戶[root@db mysql-5.6.17]# useradd mysql創建mysql數據文件存放目錄[root@db mysql-5.6.17]# mkdir /data/開始安裝mysql[root@db mysql-5.6.17]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/ -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=GBK -DDEFAULT_COLLATION=gbk_chinese_ci[root@db mysql-5.6.17]# make && make installerror for cmake ?CMake Error at cmake/readline.cmake:85 (MESSAGE): ?Curses library not found. ?Please install appropriate package, ? ? ?remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.Call Stack (most recent call first): ?cmake/readline.cmake:128 (FIND_CURSES) ?cmake/readline.cmake:202 (MYSQL_USE_BUNDLED_EDITLINE) ?CMakeLists.txt:411 (MYSQL_CHECK_EDITLINE)[root@db mysql-5.6.17]# yum install ncurses-devel[root@db mysql-5.6.17]# rm CMakeCache.txt rm：是否刪除普通文件 "CMakeCache.txt"？y初始化mysql[root@db /]# ?chown -R mysql.mysql /usr/local/mysql
[root@db /]# ?chown -R mysql.mysql /data/
[root@db /]# ?cd /usr/local/mysql/scripts
[root@db scripts]# ?./mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/ --collation-server=gbk_chinese_ci[root@db scripts]# cd ../support-files/[root@db support-files]# cp mysql.server /etc/rc.d/init.d/mysqld[root@db support-files]# chkconfig --add mysqld[root@db support-files]# chkconfig mysqld on[root@db support-files]# echo 'PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile[root@db support-files]# source /etc/profile[root@db support-files]# mysql_secure_installation[root@db support-files]# service ?mysqld startStarting MySQL. SUCCESS! [root@db support-files]# mysqladmin -u root password 1234qwer[root@db support-files]# mysql -u root -pEnter password: Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status--------------mysql ?Ver 14.14 Distrib 5.6.17, for Linux (x86_64) using ?EditLine wrapper
Connection id:7Current database:Current user:root@localhostSSL:Not in useCurrent pager:stdoutUsing outfile:''Using delimiter:;Server version:5.6.17 Source distributionProtocol version:10Connection:Localhost via UNIX socketServer characterset:gbkDb ? ? characterset:gbkClient characterset:gbkConn. ?characterset:gbkUNIX socket:/tmp/mysqld.sockUptime:4 min 33 sec
Threads: 1 ?Questions: 19 ?Slow queries: 0 ?Opens: 67 ?Flush tables: 1 ?Open tables: 60 ?Queries per second avg: 0.069--------------
============單節點mysql安裝完成============重復以上操作安裝mysql_backup=============設置root用戶可遠程登錄grant all privileges ?on *.* to root@'%' identified by "root";
mysql -h 192.168.0.176-u root -p============設置主從：主DB：[root@db etc]# mysql -u root -p
Enter password:
mysql> ?create user jrzj IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to 'jrzj'@'192.168.0.176' identified by '1234qwer';
Query OK, 0 rows affected (0.00 sec)
[root@db etc]# vim /etc/my.cnf
server_id=177
log-bin=jrzj-bin
[root@db etc]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!查看日志偏移量：mysql> show master status \G*************************** 1. row *************************** ? ? ? ? ? ? File: jrzj-bin.000002 ? ? ? ? Position: 120 ? ? Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)mysql> flush tables with read lock;導出數據庫：[root@db tmp]# mysqldump ?-p3306 -uroot -p ?-S /tmp/mysqld.sock ?--all-databases > /tmp/mysql.sql從DB配置[root@db etc]# vim /etc/my.cnf
server_id=176[root@backup tmp]# mysql -uroot -p < /tmp/mysql.sql
mysql> change master to
-> master_host='192.168.0.177',
-> master_user='jrzj',
-> master_password='1234qwer',
-> master_port=3306,
-> master_log_file='jrzj-bin.000002',
-> master_log_pos=120;
mysql> ?show slave status\G;
***************************1. row ***************************
? ? ? ? ? ? ? Slave_IO_State: Waiting for master to send event
? ? ? ? ? ? ? ? ?Master_Host:192.168.0.177
? ? ? ? ? ? ? ? ?Master_User: jrzj
? ? ? ? ? ? ? ? ?Master_Port:3306
? ? ? ? ? ? ? ?Connect_Retry:60
? ? ? ? ? ? ?Master_Log_File: jrzj-bin.000002
? ? ? ? ?Read_Master_Log_Pos:120
? ? ? ? ? ? ? Relay_Log_File: backup-relay-bin.000002
? ? ? ? ? ? ? ?Relay_Log_Pos:282
? ? ? ?Relay_Master_Log_File: jrzj-bin.000002
? ? ? ? ? ? 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:120
? ? ? ? ? ? ?Relay_Log_Space:456
? ? ? ? ? ? ?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:177
? ? ? ? ? ? ? ? ?Master_UUID: e0fe5f94-c633-11e3-8f4e-0050568ea576
? ? ? ? ? ? Master_Info_File:/data/master.info
? ? ? ? ? ? ? ? ? ?SQL_Delay:0
? ? ? ? ?SQL_Remaining_Delay: NULL
? ? ?Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
? ? ? ? ? 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:
? ? ? ? ? ? ? ?Auto_Position:0=============主從配置完成==========將mysql配置為互為主從，開啟slave的bin log 并授權訪問=========高可用配置=========0.176/0.177[root@db tmp]# yum install ipvsadm keepalived
[root@db tmp]# chkconfig keepalived on[root@db tmp]# cat /etc/keepalived/keepalived.conf ! Configuration File for keepalivedglobal_defs { ? notification_email { ? ? yangjob2013@126.com ? } ? notification_email_from yangjob2013@126.com ? smtp_server smtp.126.com ? smtp_connect_timeout 30 ? router_id HA}vrrp_instance VI_1 { ? ?state MASTER ? ?interface eth0 ? ?virtual_router_id 51 ? ?priority 100 ? ?advert_int 1 ? ?preempt ? ?authentication { ? ? ? ?auth_type PASS ? ? ? ?auth_pass 1111 ? ?} ? ?virtual_ipaddress { ? ? ? ?192.168.0.16 ? ?}}
[root@db tmp]# cat /tmp/check_mysql.shMYSQL="/usr/local/mysql/bin/mysql"MYSQL_HOST=127.0.0.1MYSQL_USER=rootMYSQL_PASSWORD=1234qwerLOG_FILE="/tmp/log/check_mysql.log"MYSQL_OK=1 # mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0check_mysql_helth(){ ?$MYSQL -h$MYSQL_HOST -u$MYSQL_USER -p${MYSQL_PASSWORD} -e "show status" &>/dev/null ?if [ $? = 0 ] ; then ? ?MYSQL_OK=1 ?else ? ?MYSQL_OK=0 ?fi ?return $MYSQL_OK}check_keepalived(){ ? ps -ef | grep keepalived | grep -v grep > /dev/null ? if [ $? = 0 ];then ? ? ? keepalived_OK=1 ? else ? ? ? ? ? ? ? keepalived_OK=0 ? fi ? return $keepalived_OK}while :do ?CHECK_TIME=3 ?while [ $CHECK_TIME -ne 0 ] ?do ? ?let "CHECK_TIME -= 1" ? ?check_mysql_helth ? ?if [ $MYSQL_OK == 1 ];then ? ? ? ?check_keepalived if [ $keepalived_OK == 1 ];thenbreakelseservice keepalived start >>$LOG_FILEbreakfi ? ?elif [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ];then ? ? ?service keepalived stop >> $LOG_FILE ? ? ?echo `date --date=today +"%Y-%M-%d %H:%m:%S"` - [INFO] - mysql invaild. keepalived stop. >> $LOG_FILE ? ?fi ?donedone[root@db tmp]#service mysqld start[root@db tmp]#service keepalibed start[root@db tmp]# nohup ./check_mysql.sh &

轉載于:https://blog.51cto.com/crazyants/1406451

總結

以上是生活随笔為你收集整理的mysql主从复制及失败切换的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。