mysql+keepalived Master-slave switch script to go

keepalived mysql Failure auto-switch script

The MySQL schema is master-slave (master-slave), master failure is automatically switched to slave. Of course, can also be set to double master, but here is a disadvantage: when the main pressure is very large, from the delay is very large, such as backward 2000 seconds, at this time the main hung, from the takeover (VIP drift to from), the user just published the article, at this time because of synchronization delay large, has not copied over, so the user has , when the original master repaired, since the IO and the SQL thread is still open, and will continue to synchronize the data has not been synchronized just now, it is possible to change the user's newly published article, resulting in user data loss.

In view of this situation, I still use the Master-slave (master-slave) architecture.

KeepAlive installation is very simple, no longer verbose here. Mainly look at the configuration files and scripts:

2. # more/etc/keepalived/keepalived.conf

4. Global_defs {

6. router_id Keepalive_mysql

8. }

12. Vrrp_script Check_run {

14. Script "/root/sh/mysql_check.sh"

16. Interval 300

18. }

22. Vrrp_sync_group VG1 {

24. Group {

26. Vi_1

28. }

30. }

34. Vrrp_instance Vi_1 {

36. State BACKUP

38. Interface eth0

40. VIRTUAL_ROUTER_ID 51

42. Priority 100

44. Advert_int 1

46. Nopreempt

48. Authentication {

50. Auth_type PASS

52. Auth_pass 1111

54. }

56. Track_script {

58. Check_run

60. }

64. notify_master/root/sh/master.sh

66. notify_backup/root/sh/backup.sh

68. notify_stop/root/sh/stop.sh

72. virtual_ipaddress {

74. 192.168.8.150

76. }

78. }

Notify_master <STRING>|<QUOTED-STRING>    # The script executed after the state changes to master Notify_backup <STRING>|< quoted-string>    # Status changed to backup script executed after Notify_fault <STRING>|<QUOTED-STRING>    # Scripts executed after the state changes to fault notify_stop <STRING>|<QUOTED-STRING>    # VRRP after a stop after execution of the script notify <STRING>|< Quoted-string>        # (1) scripts executed after any state change

The following explains the usage of these 4 scripts:

mysql_check.sh (Health Check script, when found MySQL connection is not on, will put the keepalive process off, and switch. ）

2. # more Mysql_check.sh

4. #!/bin/bash

8. . /root/.bash_profile

12. count=1

16. While True

18. Do

22. Mysql-e "Show status;" >/dev/null 2>&1

24. I=$?

26. PS aux | grep mysqld | grep-v grep >/dev/null 2>&1

28. J=$?

30. If [$i = 0] && [$J = 0]

32. Then

34. Exit 0

36. Else

38. If [$i = 1] && [$J = 0]

40. Then

42. Exit 0

44. Else

46. If [$count-GT 5]

48. Then

50. Break

52. Fi

54. Let count++

56. Continue

58. Fi

60. Fi

64. Done

68. /etc/init.d/keepalived stop

master.sh (the script executed after the state changes to master) (first determine if synchronous replication is complete, if not, wait 1 minutes, and then skip and stop the synchronous copy process, whether or not it finishes.) (Second, change the permissions and passwords for the business account admin that the front-end program connects to, and record the logs and POS points that are currently switched.) ）

2. # more Master.sh

4. #!/bin/bash

8. . /root/.bash_profile

12. master_log_file=$ (mysql-e "show slave status\g" | grep-w master_log_file | awk-f ":" ' {print $} ')

14. relay_master_log_file=$ (mysql-e "show slave status\g" | grep-w relay_master_log_file | awk-f ":" ' {print $} ')

16. read_master_log_pos=$ (mysql-e "show slave status\g" | grep-w read_master_log_pos | awk-f ":" ' {print $} ')

18. exec_master_log_pos=$ (mysql-e "show slave status\g" | grep-w exec_master_log_pos | awk-f ":" ' {print $} ')

22. i=1

26. While True

28. Do

32. If [$master_log_file = $Relay _master_log_file] && [$Read _master_log_pos-eq $Exec _master_log_pos]

34. Then

36. echo "OK"

38. Break

40. Else

42. Sleep 1

46. If [$i-GT 60]

48. Then

50. Break

52. Fi

54. Continue

56. Let i++

58. Fi

60. Done

64. Mysql-e "Stop Slave;"

66. MYSQL-E "flush logs; GRANT all privileges on * * to ' admin ' @ '% ' identified by ' admin '; flush privileges; "

68. Mysql-e "Show master status;" >/tmp/master_status_$ (Date "+%y%m%d-%h%m"). txt

backup.sh (the script executed after the state changes to backup)

1. # more Backup.sh

2. #!/bin/bash

4. . /root/.bash_profile

6. MYSQL-E "GRANT all privileges on * * to ' admin ' @ ' percent ' identified by ' 1q2w3e4r '; flush privileges;"

stop.sh (script executed after keepalived stop) (first change the admin password) (second, set the parameters to ensure that no data is lost) (Finally, see if there is a write operation, regardless of whether or not the execution is complete, 1 minutes after the exit. ）

2. # more Stop.sh

4. #!/bin/bash

8. . /root/.bash_profile

12. MYSQL-E "GRANT all privileges on * * to ' admin ' @ ' percent ' identified by ' 1q2w3e4r '; flush privileges;"

14. MYSQL-E "Set global innodb_support_xa=1;"

16. MYSQL-E "Set global sync_binlog=1;"

18. MYSQL-E "Set global innodb_flush_log_at_trx_commit=1;"

22. m_file1=$ (mysql-e "show Master status\g" | awk-f ': '/file/{print $} ')

24. m_position1=$ (mysql-e "show Master status\g" | awk-f ': '/position/{print $} ')

26. Sleep 1

28. m_file2=$ (mysql-e "show Master status\g" | awk-f ': '/file/{print $} ')

30. m_position2=$ (mysql-e "show Master status\g" | awk-f ': '/position/{print $} ')

34. i=1

38. While True

40. Do

44. If [$m_file1 = $M _file2] && [$M _position1-eq $M _position2]

46. Then

48. echo "OK"

50. Break

52. Else

54. Sleep 1

58. If [$i-GT 60]

60. Then

62. Break

64. Fi

66. Continue

68. Let i++

70. Fi

72. Done

mysql+keepalived Master-slave switch script to go