當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql主从代替技术_mysql主从复制及失败切换

發布時間：2025/3/15 数据库 25 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql主从代替技术_mysql主从复制及失败切换小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

軟件環境：

mysql軟件包：mysql-5.6.17.tar.gz

cmake軟件包：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 -aLinux 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 -aLinux 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 install

2:安裝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 install

error 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 start

Starting MySQL. SUCCESS!

[root@db support-files]# mysqladmin -u root password 1234qwer

[root@db support-files]# mysql -u root -p

Enter 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:7

Current database:

Current user:root@localhost

SSL:Not in use

Current pager:stdout

Using outfile:''

Using delimiter:;

Server version:5.6.17 Source distribution

Protocol version:10

Connection:Localhost via UNIX socket

Server characterset:gbk

Db ? ? characterset:gbk

Client characterset:gbk

Conn. ?characterset:gbk

UNIX socket:/tmp/mysqld.sock

Uptime: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 -pEnter 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.cnfserver_id=177log-bin=jrzj-bin?[root@db etc]# service mysqld restartShutting 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.cnfserver_id=176

[root@backup tmp]# mysql -uroot -p < /tmp/mysql.sqlmysql>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:Waitingformaster to send eventMaster_Host:192.168.0.177Master_User:jrzjMaster_Port:3306Connect_Retry:60Master_Log_File:jrzj-bin.000002Read_Master_Log_Pos:120Relay_Log_File:backup-relay-bin.000002Relay_Log_Pos:282Relay_Master_Log_File:jrzj-bin.000002Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_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:120Relay_Log_Space:456Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:177Master_UUID:e0fe5f94-c633-11e3-8f4e-0050568ea576Master_Info_File:/data/master.infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slave hasreadall relay log; waitingforthe slave I/O thread to update itMaster_Retry_Count:86400Master_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 keepalived

global_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.sh

MYSQL="/usr/local/mysql/bin/mysql"

MYSQL_HOST=127.0.0.1

MYSQL_USER=root

MYSQL_PASSWORD=1234qwer

LOG_FILE="/tmp/log/check_mysql.log"

MYSQL_OK=1 # mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0

check_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

return $MYSQL_OK

}

check_keepalived()

{

ps -ef | grep keepalived | grep -v grep > /dev/null

if [ $? = 0 ];then

keepalived_OK=1

else

keepalived_OK=0

return $keepalived_OK

}

while :

CHECK_TIME=3

while [ $CHECK_TIME -ne 0 ]

let "CHECK_TIME -= 1"

check_mysql_helth

if [ $MYSQL_OK == 1 ];then

check_keepalived

if [ $keepalived_OK == 1 ];then

break

else

service keepalived start >>$LOG_FILE

break

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

done

[root@db tmp]#service mysqld start

[root@db tmp]#service keepalibed start

[root@db tmp]# nohup ./check_mysql.sh &

總結

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

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

上一篇： java while语句_Java wh
下一篇： python脚本转lua_初次使用Pyt