企业web高可用集群实战之lvs+keepalived+mysql

时间：2022-07-06 01:27:46

标签：keepalived 集群 mysql HA lvs

原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://qiuzhijun.blog.51cto.com/2238445/932673

前端用两台server做lvs+keepalived负载调度器，中间用apache+php作为web应用服务器，后端用两台做mysql高可用，用nfs、mfs或作为web文件共享服务器

 系统环境：LVS调度主服务器：192.168.8.11LVS调度从服务器：192.168.8.12LVS VIP：192.168.8.10apache服务器：192.168.8.20 192.168.8.21MySQL主服务器：192.168.8.31MySQL从服务器：192.168.8.32MySQL VIP：192.168.8.30  由于工作时间比较紧，同时还要培训，架构图后期补上!! 用到软件准备：所有服务器软件包都放在/opt 目录下，下面是下载地址：
   
  
  
    

  1. wget http://www.keepalived.org/software/keepalived-1.1.20.tar.gz 
    2. 
    
   
   
  2. wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24-linux2.6-x86_64.tar.gz #64位 
    3. 
    
   
   
  3. wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24-linux2.6-i686.tar.gz #32位 
    4.
一、LVS+Keepalived1.安装ipvsadmyum -y install ipvsadm kernel-develln -sv /usr/src/kernels/2.6.18-308.8.2.el5-x86_64/ /usr/src/linux 2.安装keepalived
   
  
  
    

  1. cd /opt/ 
    2. 
    
   
   
  2. tar zxf keepalived-1.1.20.tar.gz 
    3. 
    
   
   
  3. cd keepalived-1.1.20 
    4. 
    
   
   
  4. ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-308.11.1.el5-x86_64/ 
    5. 
    
   
   
  5.  
    6. 
    
   
   
  6. 出现如下信息内核加载成功： 
    7. 
    
   
   
  7. Keepalived configuration 
    8. 
    
   
   
  8. ------------------------ 
    9. 
    
   
   
  9. Keepalived version : 1.1.20 
    10. 
    
   
   
  10. Compiler : gcc 
    11. 
    
   
   
  11. Compiler flags : -g -O2 
    12. 
    
   
   
  12. Extra Lib : -lpopt -lssl -lcrypto 
    13. 
    
   
   
  13. Use IPVS Framework : Yes 
    14. 
    
   
   
  14. IPVS sync daemon support : Yes 
    15. 
    
   
   
  15. Use VRRP Framework : Yes 
    16. 
    
   
   
  16. Use Debug flags : No 
    17. 
    
   
   
  17. make;make install 
    18. 
    
   
   
  18. cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d 
    19. 
    
   
   
  19. cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ 
    20. 
    
   
   
  20. mkdir /etc/keepalived 
    21. 
    
   
   
  21. chkconfig keepalived on 
    22.

3.创建配置文件
   
  
  
    

  1. cat /etc/keepalived/keepalived.conf 
    2. 
    
   
   
  2.  
    3. 
    
   
   
  3. ! Configuration File for keepalived 
    4. 
    
   
   
  4.  
    5. 
    
   
   
  5. global_defs { 
    6. 
    
   
   
  6.  notification_email { 
    7. 
    
   
   
  7.  250621008@qq.com 
    8. 
    
   
   
  8.  } 
    9. 
    
   
   
  9.  notification_email_from 250621008@qq.com 
    10. 
    
   
   
  10.  smtp_server smtp.163.com 
    11. 
    
   
   
  11.  #smtp_connect_timeout 30 
    12. 
    
   
   
  12.  router_id LVS_DEVEL 
    13. 
    
   
   
  13. } 
    14. 
    
   
   
  14.  
    15. 
    
   
   
  15. vrrp_instance VI_1 { 
    16. 
    
   
   
  16.  state MASTER #备份服务器上将MASTER改为BACKUP 
    17. 
    
   
   
  17.  interface eth0 
    18. 
    
   
   
  18.  lvs_sync_daemon_inteface eth0 
    19. 
    
   
   
  19.  virtual_router_id 51 
    20. 
    
   
   
  20.  priority 100 #备份服务上将100改为低于100的数值 
    21. 
    
   
   
  21.  advert_int 5 
    22. 
    
   
   
  22.  authentication { 
    23. 
    
   
   
  23.  auth_type PASS 
    24. 
    
   
   
  24.  auth_pass 1111 
    25. 
    
   
   
  25.  } 
    26. 
    
   
   
  26.  virtual_ipaddress { 
    27. 
    
   
   
  27.  192.168.8.10 
    28. 
    
   
   
  28.  } 
    29. 
    
   
   
  29. } 
    30. 
    
   
   
  30.  
    31. 
    
   
   
  31. virtual_server 192.168.8.10 80 { 
    32. 
    
   
   
  32.  delay_loop 6 #(每隔10秒查询realserver状态) 
    33. 
    
   
   
  33.  lb_algo wlc #(lvs 算法) 
    34. 
    
   
   
  34.  lb_kind DR #(Direct Route) 
    35. 
    
   
   
  35.  persistence_timeout 60 #(同一IP的连接60秒内被分配到同一台realserver) 
    36. 
    
   
   
  36.  protocol TCP #(用TCP协议检查realserver状态) 
    37. 
    
   
   
  37.  
    38. 
    
   
   
  38.  real_server 192.168.8.11 80 { 
    39. 
    
   
   
  39.  weight 100 #(权重) 
    40. 
    
   
   
  40.  TCP_CHECK { 
    41. 
    
   
   
  41.  connect_timeout 10 #(10秒无响应超时) 
    42. 
    
   
   
  42.  nb_get_retry 3 
    43. 
    
   
   
  43.  delay_before_retry 3 
    44. 
    
   
   
  44.  connect_port 80 
    45. 
    
   
   
  45.  } 
    46. 
    
   
   
  46.  } 
    47. 
    
   
   
  47.  real_server 192.168.8.12 80 { 
    48. 
    
   
   
  48.  weight 100 
    49. 
    
   
   
  49.  TCP_CHECK { 
    50. 
    
   
   
  50.  connect_timeout 10 
    51. 
    
   
   
  51.  nb_get_retry 3 
    52. 
    
   
   
  52.  delay_before_retry 3 
    53. 
    
   
   
  53.  connect_port 80 
    54. 
    
   
   
  54.  } 
    55. 
    
   
   
  55.  } 
    56. 
    
   
   
  56. } 
    57.
PS :从配置文件参考以上主的配置文件，不同的地方是红色标注两处！ 二、WEB集群1、这里可以选择lamp、lnmp、lanmp等web架构，至于搭建略！2、在各web节点上创建realserver脚本
   
  
  
    

  1. cat /root/sh/lvs_real.sh 
    2. 
    
   
   
  2. #!/bin/bash 
    3. 
    
   
   
  3. #Description: Config realserver script 
    4. 
    
   
   
  4. #Written by : opsren----http://linuxops.blog.51cto.com 
    5. 
    
   
   
  5. SNS_VIP=192.168.8.10 
    6. 
    
   
   
  6. /etc/rc.d/init.d/functions 
    7. 
    
   
   
  7.  
    8. 
    
   
   
  8. case "$1" in 
    9. 
    
   
   
  9. start) 
    10. 
    
   
   
  10.  /sbin/ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP 
    11. 
    
   
   
  11.  /sbin/route add -host $SNS_VIP dev lo:0 
    12. 
    
   
   
  12.  echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore 
    13. 
    
   
   
  13.  echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce 
    14. 
    
   
   
  14.  echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore 
    15. 
    
   
   
  15.  echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce 
    16. 
    
   
   
  16.  sysctl -p >/dev/null 2>&1 
    17. 
    
   
   
  17.  echo "RealServer Start OK" 
    18. 
    
   
   
  18.  
    19. 
    
   
   
  19.  ;; 
    20. 
    
   
   
  20. stop) 
    21. 
    
   
   
  21.  /sbin/ifconfig lo:0 down 
    22. 
    
   
   
  22.  /sbin/route del $SNS_VIP >/dev/null 2>&1 
    23. 
    
   
   
  23.  echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore 
    24. 
    
   
   
  24.  echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce 
    25. 
    
   
   
  25.  echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore 
    26. 
    
   
   
  26.  echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce 
    27. 
    
   
   
  27.  echo "RealServer Stoped" 
    28. 
    
   
   
  28.  ;; 
    29. 
    
   
   
  29. *) 
    30. 
    
   
   
  30.  echo "Usage: $0 {start|stop}" 
    31. 
    
   
   
  31.  exit 1 
    32. 
    
   
   
  32. esac 
    33. 
    
   
   
  33.  
    34. 
    
   
   
  34. exit 0 
    35. 
    
   
   
  35.  
    36. 
    
   
   
  36. 给予可执行权限： 
    37. 
    
   
   
  37. chmod +755 /roo/sh/lvs_real.sh 
    38. 
    
   
   
  38.  
    39. 
    
   
   
  39. 启动脚本： 
    40. 
    
   
   
  40. /root/sh/lvs_real.sh start 
    41.

三、MySQL高可用（双主）部署1.MySQL安装与配置这里采用mysql5.5.24二进制tar包tar zxf mysql-5.5.24-linux2.6-x86_64.tar.gz -C /usr/local/mysql5.5设置配置文件：
   
  
  
    

  1. cat >> /etc/my.cnf << EOF 
    2. 
    
   
   
  2. [client] 
    3. 
    
   
   
  3. port = 3306 
    4. 
    
   
   
  4. socket = /tmp/mysql.sock 
    5. 
    
   
   
  5. #character-set-server = utf8 
    6. 
    
   
   
  6.  
    7. 
    
   
   
  7. [mysqld] 
    8. 
    
   
   
  8. connect_timeout = 20 
    9. 
    
   
   
  9. basedir = /usr/local/mysql 
    10. 
    
   
   
  10. datadir = /data/mysql-5.5/data 
    11. 
    
   
   
  11. user = mysql 
    12. 
    
   
   
  12.  
    13. 
    
   
   
  13. port = 3306 
    14. 
    
   
   
  14. socket = /tmp/mysql.sock 
    15. 
    
   
   
  15. pid-file = /data/mysql-5.5/data/mysql.pid 
    16. 
    
   
   
  16.  
    17. 
    
   
   
  17. ##init_connect = 'SET AUTOCOMMIT=1' 
    18. 
    
   
   
  18. character-set-server = utf8 
    19. 
    
   
   
  19.  
    20. 
    
   
   
  20. open_files_limit = 10240 
    21. 
    
   
   
  21. table_open_cache = 6144 
    22. 
    
   
   
  22. table_definition_cache = 4096 
    23. 
    
   
   
  23. table_cache = 512 
    24. 
    
   
   
  24. max_connections = 1100 
    25. 
    
   
   
  25. max_connect_errors = 1000 
    26. 
    
   
   
  26. back_log = 500 
    27. 
    
   
   
  27. event_scheduler = ON 
    28. 
    
   
   
  28.  
    29. 
    
   
   
  29. external-locking = FALSE 
    30. 
    
   
   
  30. skip-name-resolve 
    31. 
    
   
   
  31. #default_table_type = MyISAM 
    32. 
    
   
   
  32.  
    33. 
    
   
   
  33. server-id = 10 
    34. 
    
   
   
  34.  
    35. 
    
   
   
  35. #master-host = 192.168.8.31 
    36. 
    
   
   
  36. #master-user = repl 
    37. 
    
   
   
  37. #master-password = repl 
    38. 
    
   
   
  38. #replicate-ignore-db=mysql 
    39. 
    
   
   
  39. slave-net-timeout=35 
    40. 
    
   
   
  40. #master-connect-retry=30 
    41. 
    
   
   
  41. #relay-log = relay-bin 
    42. 
    
   
   
  42. #max-relay-log-size = 128M 
    43. 
    
   
   
  43. #skip-slave-start 
    44. 
    
   
   
  44. #replicate-same-server-id=0 
    45. 
    
   
   
  45. #log_slave_update 
    46. 
    
   
   
  46. #replicate-do-db=WATCDB01 
    47. 
    
   
   
  47. #replicate-do-db=WATCDB02 
    48. 
    
   
   
  48. #replicate-do-db=WATCDB03 
    49. 
    
   
   
  49. #replicate-do-db=WATCDB04 
    50. 
    
   
   
  50.  
    51. 
    
   
   
  51.  
    52. 
    
   
   
  52. wait_timeout=100 
    53. 
    
   
   
  53. interactive_timeout = 100 
    54. 
    
   
   
  54. ##Add for debug,record all the sql 
    55. 
    
   
   
  55. ##log = /usr/local/mysql/fetiondata/data/g_log.log 
    56. 
    
   
   
  56. ##innodb_status_file = 1 
    57. 
    
   
   
  57. # 
    58. 
    
   
   
  58. #tmpdir = /tmp 
    59. 
    
   
   
  59. tmpdir = /data/mysql-5.5/tmp 
    60. 
    
   
   
  60.  
    61. 
    
   
   
  61.  
    62. 
    
   
   
  62. tmp_table_size = 32M 
    63. 
    
   
   
  63. max_heap_table_size = 64M 
    64. 
    
   
   
  64.  
    65. 
    
   
   
  65. thread_cache_size = 64 
    66. 
    
   
   
  66. thread_concurrency = 32 
    67. 
    
   
   
  67. thread_stack = 192K 
    68. 
    
   
   
  68.  
    69. 
    
   
   
  69. max_allowed_packet = 16M 
    70. 
    
   
   
  70.  
    71. 
    
   
   
  71. sort_buffer_size = 512k 
    72. 
    
   
   
  72. join_buffer_size = 512k 
    73. 
    
   
   
  73. query_cache_size = 0 
    74. 
    
   
   
  74. query_cache_limit = 2M 
    75. 
    
   
   
  75. key_buffer_size = 64M 
    76. 
    
   
   
  76. read_buffer_size = 512k 
    77. 
    
   
   
  77. read_rnd_buffer_size = 512k 
    78. 
    
   
   
  78. bulk_insert_buffer_size = 32M 
    79. 
    
   
   
  79.  
    80. 
    
   
   
  80. binlog_format = mixed 
    81. 
    
   
   
  81. #binlog_cache_size = 1M 
    82. 
    
   
   
  82. #log-bin = /data/mysql-5.5/logs/mysql-bin 
    83. 
    
   
   
  83. max_binlog_size = 128M 
    84. 
    
   
   
  84.  
    85. 
    
   
   
  85. #log_long_format 
    86. 
    
   
   
  86. slow-query-log 
    87. 
    
   
   
  87. slow-query-log-file = mysql.slow 
    88. 
    
   
   
  88.  
    89. 
    
   
   
  89. #log_queries_not_using_indexes = 0 
    90. 
    
   
   
  90. long_query_time = 1 
    91. 
    
   
   
  91.  
    92. 
    
   
   
  92. myisam_sort_buffer_size = 32M 
    93. 
    
   
   
  93. myisam_max_sort_file_size = 10G 
    94. 
    
   
   
  94. #myisam_max_extra_sort_file_size = 10G 
    95. 
    
   
   
  95. myisam_repair_threads = 1 
    96. 
    
   
   
  96. myisam_recover 
    97. 
    
   
   
  97.  
    98. 
    
   
   
  98. log_error = /data/mysql-5.5/logs/error.log 
    99. 
    
   
   
  99.  
    100. 
    
   
   
  100.  
    101. 
    
   
   
  101. # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE 
    102. 
    
   
   
  102. #default#transaction_isolation = REPEATABLE-READ 
    103. 
    
   
   
  103. transaction_isolation = READ-COMMITTED 
    104. 
    
   
   
  104.  
    105. 
    
   
   
  105. #innodb_checksums = 0 
    106. 
    
   
   
  106. #innodb_doublewrite = 0 
    107. 
    
   
   
  107. innodb_flush_method = O_DIRECT 
    108. 
    
   
   
  108.  
    109. 
    
   
   
  109. #96G  
    110. 
    
   
   
  110. innodb_buffer_pool_size = 48G 
    111. 
    
   
   
  111. #innodb_buffer_pool_size = 60G 
    112. 
    
   
   
  112. #innodb_buffer_pool_instances = 10 
    113. 
    
   
   
  113. innodb_buffer_pool_instances = 4 
    114. 
    
   
   
  114.  
    115. 
    
   
   
  115. #innodb_buffer_pool_size = 33G 
    116. 
    
   
   
  116. innodb_additional_mem_pool_size = 16M 
    117. 
    
   
   
  117.  
    118. 
    
   
   
  118. innodb_data_home_dir = 
    119. 
    
   
   
  119. #innodb_data_file_path = ibdata1:20G;ibdata2:20G;ibdata3:20G;ibdata4:20G;ibdata5:20G;ibdata6:20G;ibdata7:20G;ibdata8:100M:autoextend 
    120. 
    
   
   
  120. innodb_data_file_path = ibdata1:2048M:autoextend 
    121. 
    
   
   
  121.  
    122. 
    
   
   
  122. innodb_log_group_home_dir = /data/mysql-5.5/logs 
    123. 
    
   
   
  123. innodb_log_buffer_size = 16M 
    124. 
    
   
   
  124. innodb_log_file_size = 1024M 
    125. 
    
   
   
  125. innodb_log_files_in_group = 3 
    126. 
    
   
   
  126.  
    127. 
    
   
   
  127. innodb_thread_concurrency = 288 
    128. 
    
   
   
  128. innodb_thread_sleep_delay = 500 
    129. 
    
   
   
  129. #innodb_file_io_threads = 8 
    130. 
    
   
   
  130. innodb_concurrency_tickets = 1000 
    131. 
    
   
   
  131.  
    132. 
    
   
   
  132. innodb_sync_spin_loops=40 
    133. 
    
   
   
  133.  
    134. 
    
   
   
  134. ##innodb_force_recovery is only used for recovery mode; 
    135. 
    
   
   
  135. ##innodb_force_recovery = 3  
    136. 
    
   
   
  136.  
    137. 
    
   
   
  137.  
    138. 
    
   
   
  138. innodb_flush_log_at_trx_commit = 0 
    139. 
    
   
   
  139. innodb_max_dirty_pages_pct = 90 
    140. 
    
   
   
  140. #innodb_lock_wait_timeout = 120 
    141. 
    
   
   
  141. innodb_lock_wait_timeout = 10 
    142. 
    
   
   
  142. innodb_support_xa = 0 
    143. 
    
   
   
  143.  
    144. 
    
   
   
  144. #Added to allow create function 
    145. 
    
   
   
  145. log_bin_trust_function_creators = 1 
    146. 
    
   
   
  146.  
    147. 
    
   
   
  147.  
    148. 
    
   
   
  148. innodb_file_per_table = 1 
    149. 
    
   
   
  149.  
    150. 
    
   
   
  150. #turn  
    151. 
    
   
   
  151. innodb_stats_on_metadata=0 
    152. 
    
   
   
  152. innodb_old_blocks_pct=20 
    153. 
    
   
   
  153. innodb_change_buffering=all 
    154. 
    
   
   
  154. #innodb_max_purge_lag  
    155. 
    
   
   
  155. innodb_purge_threads=1 
    156. 
    
   
   
  156. innodb_purge_batch_size = 32 
    157. 
    
   
   
  157.  
    158. 
    
   
   
  158.  
    159. 
    
   
   
  159.  
    160. 
    
   
   
  160. innodb_read_io_threads = 8 
    161. 
    
   
   
  161. innodb_write_io_threads = 8 
    162. 
    
   
   
  162. innodb_io_capacity = 2000 
    163. 
    
   
   
  163.  
    164. 
    
   
   
  164. innodb_file_format = Barracuda 
    165. 
    
   
   
  165.  
    166. 
    
   
   
  166. #manage 
    167. 
    
   
   
  167. performance_schema 
    168. 
    
   
   
  168. performance_schema_events_waits_history_size=100 
    169. 
    
   
   
  169. performance_schema_events_waits_history_long_size=30000 
    170. 
    
   
   
  170.  
    171. 
    
   
   
  171. #innodb_strict_mode=1 
    172. 
    
   
   
  172.  
    173. 
    
   
   
  173. [mysqldump] 
    174. 
    
   
   
  174. quick 
    175. 
    
   
   
  175. max_allowed_packet = 128M 
    176. 
    
   
   
  176.  
    177. 
    
   
   
  177. [mysql] 
    178. 
    
   
   
  178. no-auto-rehash 
    179. 
    
   
   
  179.  
    180. 
    
   
   
  180. [mysqlhotcopy] 
    181. 
    
   
   
  181. interactive-timeout 
    182. 
    
   
   
  182.  
    183. 
    
   
   
  183. [mysqld_safe] 
    184. 
    
   
   
  184. open-files-limit = 28192 
    185. 
    
   
   
  185. EOF 
    186.

注意两点：1).在两台MySQL服务器上开启bin-log日志，默认是开启的！2).另外一台服务器节点的server-id设置为20，本来设置为10 2.将31服务器设置32的主服务器在31服务器上建立授权用户！
   
  
  
    

  1. mysql> grant replication slave on *.* to 'rep'@'%' identified by "rep"; 
    2. 
    
   
   
  2. Query OK, 0 rows affected (0.09 sec) 
    3. 
    
   
   
  3.  
    4. 
    
   
   
  4. mysql> flush privileges; 
    5. 
    
   
   
  5. Query OK, 0 rows affected (0.09 sec) 
    6. 
    
   
   
  6.  
    7. 
    
   
   
  7. mysql> show master status; 
    8. 
    
   
   
  8. +---------------+----------+--------------+------------------+ 
    9. 
    
   
   
  9. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    10. 
    
   
   
  10. +---------------+----------+--------------+------------------+ 
    11. 
    
   
   
  11. | binlog.000004 | 138637 | | | 
    12. 
    
   
   
  12. +---------------+----------+--------------+------------------+ 
    13. 
    
   
   
  13. 1 row in set (0.03 sec) 
    14. 
    
   
   
  14.  
    15. 
    
   
   
  15. 在32服务器上把31服务器设为自己的主服务器，mysql客户端连接到32服务器： 
    16. 
    
   
   
  16. mysql> change master to master_host='192.168.8.31',master_user='rep',master_password='rep',master_log_file='binlog.000004',master_log_pos=138637; 
    17. 
    
   
   
  17. Query OK, 0 rows affected (0.01 sec) 
    18. 
    
   
   
  18.  
    19. 
    
   
   
  19. mysql> start slave; 
    20. 
    
   
   
  20. Query OK, 0 rows affected (0.00 sec) 
    21. 
    
   
   
  21.  
    22. 
    
   
   
  22. mysql> show slave status\G 
    23. 
    
   
   
  23. *************************** 1. row *************************** 
    24. 
    
   
   
  24.  Slave_IO_State: Waiting for master to send event 
    25. 
    
   
   
  25.  Master_Host: 192.168.8.31 
    26. 
    
   
   
  26.  Master_User: rep 
    27. 
    
   
   
  27.  Master_Port: 3306 
    28. 
    
   
   
  28.  Connect_Retry: 60 
    29. 
    
   
   
  29.  Master_Log_File: binlog.000004 
    30. 
    
   
   
  30.  Read_Master_Log_Pos: 138637 
    31. 
    
   
   
  31.  Relay_Log_File: relaylog.000002 
    32. 
    
   
   
  32.  Relay_Log_Pos: 250 
    33. 
    
   
   
  33.  Relay_Master_Log_File: binlog.000004 
    34. 
    
   
   
  34.  Slave_IO_Running: Yes 
    35. 
    
   
   
  35.  Slave_SQL_Running: Yes 
    36. 
    
   
   
  36.  Replicate_Do_DB: 
    37. 
    
   
   
  37.  Replicate_Ignore_DB: mysql,test,information_schema 
    38. 
    
   
   
  38.  Replicate_Do_Table: 
    39. 
    
   
   
  39.  Replicate_Ignore_Table: 
    40. 
    
   
   
  40.  Replicate_Wild_Do_Table: 
    41. 
    
   
   
  41.  Replicate_Wild_Ignore_Table: 
    42. 
    
   
   
  42.  Last_Errno: 0 
    43. 
    
   
   
  43.  Last_Error: 
    44. 
    
   
   
  44.  Skip_Counter: 0 
    45. 
    
   
   
  45.  Exec_Master_Log_Pos: 138637 
    46. 
    
   
   
  46.  Relay_Log_Space: 399 
    47. 
    
   
   
  47.  Until_Condition: None 
    48. 
    
   
   
  48.  Until_Log_File: 
    49. 
    
   
   
  49.  Until_Log_Pos: 0 
    50. 
    
   
   
  50.  Master_SSL_Allowed: No 
    51. 
    
   
   
  51.  Master_SSL_CA_File: 
    52. 
    
   
   
  52.  Master_SSL_CA_Path: 
    53. 
    
   
   
  53.  Master_SSL_Cert: 
    54. 
    
   
   
  54.  Master_SSL_Cipher: 
    55. 
    
   
   
  55.  Master_SSL_Key: 
    56. 
    
   
   
  56.  Seconds_Behind_Master: 0 
    57. 
    
   
   
  57. Master_SSL_Verify_Server_Cert: No 
    58. 
    
   
   
  58.  Last_IO_Errno: 0 
    59. 
    
   
   
  59.  Last_IO_Error: 
    60. 
    
   
   
  60.  Last_SQL_Errno: 0 
    61. 
    
   
   
  61.  Last_SQL_Error: 
    62. 
    
   
   
  62.  Replicate_Ignore_Server_Ids: 
    63. 
    
   
   
  63.  Master_Server_Id: 1 
    64. 
    
   
   
  64. 1 row in set (0.00 sec) 
    65.

PS:Slave_IO_Running: YesSlave_SQL_Running: Yes两处都为YES，表示OK！ 3.将32服务器设置31的主服务器在32服务器上建立授权用户： 
   
  
  
    

  1. mysql> grant replication slave on *.* to 'rep'@'%' identified by "rep"; 
    2. 
    
   
   
  2. Query OK, 0 rows affected (0.03 sec) 
    3. 
    
   
   
  3.  
    4. 
    
   
   
  4. mysql> flush privileges; 
    5. 
    
   
   
  5. Query OK, 0 rows affected (0.00 sec) 
    6. 
    
   
   
  6.  
    7. 
    
   
   
  7. mysql> show master status; 
    8. 
    
   
   
  8. +---------------+----------+--------------+------------------+ 
    9. 
    
   
   
  9. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    10. 
    
   
   
  10. +---------------+----------+--------------+------------------+ 
    11. 
    
   
   
  11. | binlog.000004 | 320 | | | 
    12. 
    
   
   
  12. +---------------+----------+--------------+------------------+ 
    13. 
    
   
   
  13. 1 row in set (0.00 sec) 
    14. 
    
   
   
  14.  
    15. 
    
   
   
  15. 在31服务器上把32服务器设为自己的主服务器，mysql客户端连接到31服务器： 
    16. 
    
   
   
  16. mysql> change master to master_host='192.168.8.32',master_user='rep',master_password='rep',master_log_file='binlog.000004',master_log_pos=320; 
    17. 
    
   
   
  17. ERROR 2006 (HY000): MySQL server has gone away 
    18. 
    
   
   
  18. No connection. Trying to reconnect... 
    19. 
    
   
   
  19. Connection id: 74 
    20. 
    
   
   
  20. Current database: *** NONE *** 
    21. 
    
   
   
  21.  
    22. 
    
   
   
  22. Query OK, 0 rows affected (0.07 sec) 
    23. 
    
   
   
  23.  
    24. 
    
   
   
  24. mysql> start slave; 
    25. 
    
   
   
  25. Query OK, 0 rows affected (0.03 sec) 
    26. 
    
   
   
  26.  
    27. 
    
   
   
  27. mysql> show slave status\G 
    28. 
    
   
   
  28. *************************** 1. row *************************** 
    29. 
    
   
   
  29.  Slave_IO_State: Waiting for master to send event 
    30. 
    
   
   
  30.  Master_Host: 192.168.8.32 
    31. 
    
   
   
  31.  Master_User: rep 
    32. 
    
   
   
  32.  Master_Port: 3306 
    33. 
    
   
   
  33.  Connect_Retry: 60 
    34. 
    
   
   
  34.  Master_Log_File: binlog.000004 
    35. 
    
   
   
  35.  Read_Master_Log_Pos: 320 
    36. 
    
   
   
  36.  Relay_Log_File: relaylog.000002 
    37. 
    
   
   
  37.  Relay_Log_Pos: 250 
    38. 
    
   
   
  38.  Relay_Master_Log_File: binlog.000004 
    39. 
    
   
   
  39.  Slave_IO_Running: Yes 
    40. 
    
   
   
  40.  Slave_SQL_Running: Yes 
    41. 
    
   
   
  41.  Replicate_Do_DB: 
    42. 
    
   
   
  42.  Replicate_Ignore_DB: mysql,test,information_schema 
    43. 
    
   
   
  43.  Replicate_Do_Table: 
    44. 
    
   
   
  44.  Replicate_Ignore_Table: 
    45. 
    
   
   
  45.  Replicate_Wild_Do_Table: 
    46. 
    
   
   
  46.  Replicate_Wild_Ignore_Table: 
    47. 
    
   
   
  47.  Last_Errno: 0 
    48. 
    
   
   
  48.  Last_Error: 
    49. 
    
   
   
  49.  Skip_Counter: 0 
    50. 
    
   
   
  50.  Exec_Master_Log_Pos: 320 
    51. 
    
   
   
  51.  Relay_Log_Space: 399 
    52. 
    
   
   
  52.  Until_Condition: None 
    53. 
    
   
   
  53.  Until_Log_File: 
    54. 
    
   
   
  54.  Until_Log_Pos: 0 
    55. 
    
   
   
  55.  Master_SSL_Allowed: No 
    56. 
    
   
   
  56.  Master_SSL_CA_File: 
    57. 
    
   
   
  57.  Master_SSL_CA_Path: 
    58. 
    
   
   
  58.  Master_SSL_Cert: 
    59. 
    
   
   
  59.  Master_SSL_Cipher: 
    60. 
    
   
   
  60.  Master_SSL_Key: 
    61. 
    
   
   
  61.  Seconds_Behind_Master: 0 
    62. 
    
   
   
  62. Master_SSL_Verify_Server_Cert: No 
    63. 
    
   
   
  63.  Last_IO_Errno: 0 
    64. 
    
   
   
  64.  Last_IO_Error: 
    65. 
    
   
   
  65.  Last_SQL_Errno: 0 
    66. 
    
   
   
  66.  Last_SQL_Error: 
    67. 
    
   
   
  67.  Replicate_Ignore_Server_Ids: 
    68. 
    
   
   
  68.  Master_Server_Id: 2 
    69. 
    
   
   
  69. 1 row in set (0.00 sec) 
    70.

4.主主同步测试先在31服务器上建个库：
   
  
  
    

  1. mysql> create database t_test; 
    2. 
    
   
   
  2. Query OK, 1 row affected (0.03 sec) 
    3. 
    
   
   
  3.  
    4. 
    
   
   
  4. mysql> show databases; 
    5. 
    
   
   
  5. +--------------------+ 
    6. 
    
   
   
  6. | Database | 
    7. 
    
   
   
  7. +--------------------+ 
    8. 
    
   
   
  8. | information_schema | 
    9. 
    
   
   
  9. | mysql | 
    10. 
    
   
   
  10. | performance_schema | 
    11. 
    
   
   
  11. | t_test | 
    12. 
    
   
   
  12. | test | 
    13. 
    
   
   
  13. +--------------------+ 
    14. 
    
   
   
  14. 5 rows in set (0.00 sec) 
    15. 
    
   
   
  15.  
    16. 
    
   
   
  16. 再到32服务器查询是否同步过来这个库： 
    17. 
    
   
   
  17. mysql> show databases; 
    18. 
    
   
   
  18. +--------------------+ 
    19. 
    
   
   
  19. | Database | 
    20. 
    
   
   
  20. +--------------------+ 
    21. 
    
   
   
  21. | information_schema | 
    22. 
    
   
   
  22. | bbs | 
    23. 
    
   
   
  23. | mysql | 
    24. 
    
   
   
  24. | performance_schema | 
    25. 
    
   
   
  25. | t_test | 
    26. 
    
   
   
  26. | test | 
    27. 
    
   
   
  27. +--------------------+ 
    28. 
    
   
   
  28. 6 rows in set (0.00 sec) 
    29. 
    
   
   
  29.  
    30. 
    
   
   
  30. 可以看到同步过来了！ 
    31. 
    
   
   
  31. 反过来一样！这步略！ 
    32. 
    
   
   
  32.  
    33. 
    
   
   
  33. 5.keepalived安装配置 
    34. 
    
   
   
  34. yum -y install kernel-devel ipvsadm 
    35. 
    
   
   
  35. ln -sv /usr/src/kernels/2.6.18-308.8.2.el5-x86_64/ /usr/src/linux 
    36. 
    
   
   
  36. cd /opt 
    37. 
    
   
   
  37. tar zxf keepalived-1.1.20.tar.gz 
    38. 
    
   
   
  38. cd keepalived-1.1.20 
    39. 
    
   
   
  39. ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-308.11.1.el5-x86_64/ 
    40. 
    
   
   
  40. make;make install 
    41. 
    
   
   
  41. cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d 
    42. 
    
   
   
  42. cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ 
    43. 
    
   
   
  43. mkdir /etc/keepalived 
    44. 
    
   
   
  44. chkconfig keepalived on 
    45. 
    
   
   
  45.  
    46. 
    
   
   
  46. 创建配置文件： 
    47. 
    
   
   
  47. 主服务器： 
    48. 
    
   
   
  48. cat /etc/keepalived/keepalived.conf 
    49. 
    
   
   
  49.  
    50. 
    
   
   
  50. ! Configuration File for keepalived 
    51. 
    
   
   
  51.  
    52. 
    
   
   
  52. global_defs { 
    53. 
    
   
   
  53.  notification_email { 
    54. 
    
   
   
  54.  250621008@qq.com 
    55. 
    
   
   
  55.  } 
    56. 
    
   
   
  56.  notification_email_from 250621008@qq.com 
    57. 
    
   
   
  57.  smtp_server 127.0.0.1 
    58. 
    
   
   
  58.  smtp_connect_timeout 30 
    59. 
    
   
   
  59.  router_id mysql-ha 
    60. 
    
   
   
  60. } 
    61. 
    
   
   
  61.  
    62. 
    
   
   
  62. vrrp_instance VI_1 { 
    63. 
    
   
   
  63.  state BACKUP 
    64. 
    
   
   
  64.  interface eth0 
    65. 
    
   
   
  65.  virtual_router_id 51 
    66. 
    
   
   
  66.  priority 100 
    67. 
    
   
   
  67.  advert_int 1 
    68. 
    
   
   
  68.  nopreempt #不抢占,只在priority高的节点上设置 
    69. 
    
   
   
  69.  authentication { 
    70. 
    
   
   
  70.  auth_type PASS 
    71. 
    
   
   
  71.  auth_pass 1111 
    72. 
    
   
   
  72.  } 
    73. 
    
   
   
  73.  virtual_ipaddress { 
    74. 
    
   
   
  74.  192.168.8.30 
    75. 
    
   
   
  75.  } 
    76. 
    
   
   
  76. } 
    77. 
    
   
   
  77.  
    78. 
    
   
   
  78. virtual_server 192.168.8.30 3306 { 
    79. 
    
   
   
  79.  delay_loop 2 
    80. 
    
   
   
  80.  lb_algo wrr 
    81. 
    
   
   
  81.  lb_kind DR 
    82. 
    
   
   
  82.  persistence_timeout 60 
    83. 
    
   
   
  83.  protocol TCP 
    84. 
    
   
   
  84.  
    85. 
    
   
   
  85.  real_server 192.168.8.31 3306 
    86. 
    
   
   
  86.  weight 3 
    87. 
    
   
   
  87.  notify_down /root/sh/mysql.sh 
    88. 
    
   
   
  88.  TCP_CHECK { 
    89. 
    
   
   
  89.  connect_timeout 10 
    90. 
    
   
   
  90.  nb_get_retry 3 
    91. 
    
   
   
  91.  delay_before_retry 3 
    92. 
    
   
   
  92.  connect_port 3306 
    93. 
    
   
   
  93.  } 
    94. 
    
   
   
  94.  } 
    95. 
    
   
   
  95. } 
    96. 
    
   
   
  96.  
    97. 
    
   
   
  97. ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 
    98. 
    
   
   
  98.  
    99. 
    
   
   
  99. 从服务器： 
    100. 
    
   
   
  100. cat /etc/keepalived/keepalived.conf 
    101. 
    
   
   
  101.  
    102. 
    
   
   
  102. ! Configuration File for keepalived 
    103. 
    
   
   
  103.  
    104. 
    
   
   
  104. global_defs { 
    105. 
    
   
   
  105.  notification_email { 
    106. 
    
   
   
  106.  250621008@qq.com 
    107. 
    
   
   
  107.  } 
    108. 
    
   
   
  108.  notification_email_from 250621008@qq.com 
    109. 
    
   
   
  109.  smtp_server 127.0.0.1 
    110. 
    
   
   
  110.  smtp_connect_timeout 30 
    111. 
    
   
   
  111.  router_id mysql-ha 
    112. 
    
   
   
  112. } 
    113. 
    
   
   
  113.  
    114. 
    
   
   
  114. vrrp_instance VI_1 { 
    115. 
    
   
   
  115.  state BACKUP 
    116. 
    
   
   
  116.  interface eth0 
    117. 
    
   
   
  117.  virtual_router_id 51 
    118. 
    
   
   
  118.  priority 90 
    119. 
    
   
   
  119.  advert_int 1 
    120. 
    
   
   
  120.  authentication { 
    121. 
    
   
   
  121.  auth_type PASS 
    122. 
    
   
   
  122.  auth_pass 1111 
    123. 
    
   
   
  123.  } 
    124. 
    
   
   
  124.  virtual_ipaddress { 
    125. 
    
   
   
  125.  192.168.8.30 
    126. 
    
   
   
  126.  } 
    127. 
    
   
   
  127. } 
    128. 
    
   
   
  128.  
    129. 
    
   
   
  129. virtual_server 192.168.8.30 3306 { 
    130. 
    
   
   
  130.  delay_loop 2 
    131. 
    
   
   
  131.  lb_algo wrr 
    132. 
    
   
   
  132.  lb_kind DR 
    133. 
    
   
   
  133.  persistence_timeout 60 
    134. 
    
   
   
  134.  protocol TCP 
    135. 
    
   
   
  135.  
    136. 
    
   
   
  136.  real_server 192.168.8.32 3306 
    137. 
    
   
   
  137.  weight 3 
    138. 
    
   
   
  138.  notify_down /root/sh/mysql.sh 
    139. 
    
   
   
  139.  TCP_CHECK { 
    140. 
    
   
   
  140.  connect_timeout 10 
    141. 
    
   
   
  141.  nb_get_retry 3 
    142. 
    
   
   
  142.  delay_before_retry 3 
    143. 
    
   
   
  143.  connect_port 3306 
    144. 
    
   
   
  144.  } 
    145. 
    
   
   
  145.  } 
    146. 
    
   
   
  146. } 
    147.

主备配置文件注意几点：1).router_id 两边必须相同2).state 两边都为BACKUP3).virtual_router_id 两边必须相同4).priority 主节点的值必须大于从节点的值5).nopreempt 不抢占，只在priority高的节点（即主节点）上设置6).real_server 只需要本机的IP，不需要其它节点的！7).notify_down 作用是监测到当mysql停止工作时自动关闭本机的keepalived的脚本，实现故障转移！ 在主从上都创建当mysql停止工作时自动关闭本机的keepalived的脚本：
   
  
  
    

  1. cat /root/sh/mysql.sh 
    2. 
    
   
   
  2. #!/bin/bash 
    3. 
    
   
   
  3.  
    4. 
    
   
   
  4. MYSQL=/usr/local/mysql/bin/mysql 
    5. 
    
   
   
  5. MYSQL_HOST=localhost 
    6. 
    
   
   
  6. MYSQL_USER=root 
    7. 
    
   
   
  7. MYSQL_PASSWORD="admin" 
    8. 
    
   
   
  8.  
    9. 
    
   
   
  9. CHECK_TIME=3 
    10. 
    
   
   
  10.   
    11. 
    
   
   
  11. #mysql is working MYSQL_OK is 0 , mysql down MYSQL_OK is 1 
    12. 
    
   
   
  12. MYSQL_OK=1 
    13. 
    
   
   
  13.  
    14. 
    
   
   
  14. function check_mysql_helth (){ 
    15. 
    
   
   
  15. $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p $MYSQL_PASSWORD -e "show status;" > /dev/null 2>&1 
    16. 
    
   
   
  16. if [ $? = 0 ] ;then 
    17. 
    
   
   
  17. MYSQL_OK=0 
    18. 
    
   
   
  18. else 
    19. 
    
   
   
  19. MYSQL_OK=1 
    20. 
    
   
   
  20. fi 
    21. 
    
   
   
  21. return $MYSQL_OK 
    22. 
    
   
   
  22. } 
    23. 
    
   
   
  23.  
    24. 
    
   
   
  24. while [ $CHECK_TIME -ne 0 ] 
    25. 
    
   
   
  25. do 
    26. 
    
   
   
  26. let "CHECK_TIME -= 1" 
    27. 
    
   
   
  27.  
    28. 
    
   
   
  28. check_mysql_helth 
    29. 
    
   
   
  29.  
    30. 
    
   
   
  30. if [ $MYSQL_OK = 0 ] ; then 
    31. 
    
   
   
  31. CHECK_TIME=0 
    32. 
    
   
   
  32. exit 0 
    33. 
    
   
   
  33. fi 
    34. 
    
   
   
  34.  
    35. 
    
   
   
  35. if [ $MYSQL_OK -eq 1 ] && [ $CHECK_TIME -eq 1 ] 
    36. 
    
   
   
  36. then 
    37. 
    
   
   
  37.  /etc/init.d/keepalived stop 
    38. 
    
   
   
  38. exit 1 
    39. 
    
   
   
  39. fi 
    40. 
    
   
   
  40.  
    41. 
    
   
   
  41. sleep 1 
    42. 
    
   
   
  42.  
    43. 
    
   
   
  43. done 
    44.

6.故障转移测试分别启动主备节点的keepalived服务，然后在主节点查看VIP有没有启动：
   
  
  
    

  1. 31服务器： 
    2. 
    
   
   
  2. [root@mysql1 ~]# ip a |grep eth0 
    3. 
    
   
   
  3. 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 
    4. 
    
   
   
  4.  inet 192.168.8.31/24 brd 192.168.8.255 scope global eth0 
    5. 
    
   
   
  5.  inet 192.168.8.30/32 scope global eth0 
    6. 
    
   
   
  6.  
    7. 
    
   
   
  7. 32服务器： 
    8. 
    
   
   
  8. [root@mysql2 keepalived-1.1.19]# ip a |grep eth0 
    9. 
    
   
   
  9. 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 
    10. 
    
   
   
  10.  inet 192.168.8.32/24 brd 192.168.8.255 scope global eth0 
    11. 
    
   
   
  11.  
    12. 
    
   
   
  12. 现在把31服务器的mysql服务停掉： 
    13. 
    
   
   
  13. [root@mysql1 keepalived]# ip a 
    14. 
    
   
   
  14. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue 
    15. 
    
   
   
  15.  link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 
    16. 
    
   
   
  16.  inet 127.0.0.1/8 scope host lo 
    17. 
    
   
   
  17. 2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000 
    18. 
    
   
   
  18.  link/ether 00:0c:29:d4:fd:b3 brd ff:ff:ff:ff:ff:ff 
    19. 
    
   
   
  19.  inet 192.168.8.31/24 brd 192.168.8.255 scope global eth0 
    20. 
    
   
   
  20.  inet 192.168.8.30/32 scope global eth0 
    21. 
    
   
   
  21. [root@mysql1 keepalived]# service mysqld stop 
    22. 
    
   
   
  22. Shutting down MySQL. [ OK ] 
    23. 
    
   
   
  23. [root@mysql1 keepalived]# service keepalived status 
    24. 
    
   
   
  24. keepalived is stopped 
    25. 
    
   
   
  25. [root@mysql1 keepalived]# 
    26.

可以看到mysql服务停掉后，keepalived服务也马上停掉了!再到32节点上查看下VIP有没有接管：[root@mysql2 ~]# ip a1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueuelink/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lo2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast qlen 1000link/ether 00:0c:29:e3:dd:5a brd ff:ff:ff:ff:ff:ffinet 192.168.8.32/24 brd 192.168.8.255 scope global eth0inet 192.168.8.30/32 scope global eth0可以看到接管过来了！

 

标签：

相关文章