数据库keepalived部署文档
一.mysql数据库RPM安装包安装
rpm �Cqa |grep -i mysql //查看是否安装了数据库
rpm -e 文件名�Cnodeps //卸载已安装的文件
tar �Cxzvf ./ MariaDB-10-linux.tar.gz //解压MySQL rpm安装包
rpm -ivh *.rpm --force --nodeps //安装MySQL数据库
rpm -qa|grep -i mariadb//验证安装是否成功
安装成功显示如下:
[root@Test2 MariaDB-10-linux]# rpm -qa |grep -i mariadb
MariaDB-Galera-server-10.0.14-1.el6.x86_64
MariaDB-cassandra-engine-10.0.15-1.el6.x86_64
MariaDB-common-10.0.15-1.el6.x86_64
MariaDB-test-10.0.15-1.el6.x86_64
MariaDB-oqgraph-engine-10.0.15-1.el6.x86_64
MariaDB-shared-10.0.15-1.el6.x86_64
MariaDB-client-10.0.15-1.el6.x86_64
MariaDB-server-10.0.15-1.el6.x86_64
MariaDB-Galera-test-10.0.14-1.el6.x86_64
启动数据库:
[root@Test2 MariaDB-10-linux]# servicemysql start
Starting MySQL.. SUCCESS!
添加root用户密码
[root@Test2 MariaDB-10-linux]# mysqladmin-uroot password 123
登录进入数据库
[root@Test2 MariaDB-10-linux]# mysql -uroot-p123
数据库主从模式
二.主数据库
1.创建一个复制用户，具有replicationslave权限。
MariaDB [(none)]> grant replicationslave on *.* to 'copy'@'192.168.98.130' identified by 'copy';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.编辑my.cnf文件
[mysqld]
key_buffer_size=2048M
server-id=132
log-bin=/var/lib/mysql/mysql-bin
log-slave-updates=1
3重启MySQL数据库
[root@Test0 ~]# service mysql restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
4设置读写锁
MariaDB [(none)]> flush tables with readlock;
Query OK, 0 rows affected (0.00 sec)
5得到binlog日志和偏移量
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 312 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
*******************************************************************************
6.备份数据库
[root@Test0 ~]# mysqldump -uroot -p123 test>test.sql
解锁
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
************************************************************************************************************************************************************
2从数据库
1编辑配置文件
vi /etc/my.cnf.d/server.conf
添加
server-id=109
2.重启数据库
[root@Test2 MariaDB-10-linux]# servicemysql restart
Shutting down MySQL... SUCCESS!
Starting MySQL.. SUCCESS!
3.停止复制
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00sec)
MariaDB [(none)]> change master tomaster_host='192.168.98.132', master_user='copy', master_password='copy',master_log_file='mysql-bin.0000013', master_log_pos=365;
出现错误:
MariaDB [(none)]> show slave status\G;
*************************** 1. row***************************
Slave_IO_State:
Master_Host: 192.168.98.132
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.0000013
Read_Master_Log_Pos: 365
Relay_Log_File:Test2-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.0000013
Slave_IO_Running: No
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: 365
Relay_Log_Space: 248
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error1236 from master when reading data from binary log: 'Could not find first logfile name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 132
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
解决方法:
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TOMASTER_LOG_FILE='mysql-bin.000013',MASTER_LOG_POS=365;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
4检查主从是否同步正常
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting formaster to send event
Master_Host: 192.168.98.132
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000013
Read_Master_Log_Pos: 365
Relay_Log_File:Test2-relay-bin.000002
Relay_Log_Pos: 535
Relay_Master_Log_File: mysql-bin.000013
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: 365
Relay_Log_Space: 832
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: 132
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
5主数据库查看:
MariaDB [(none)]> show processlist\G;
*************************** 1. row***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
Progress: 0.000
*************************** 2. row***************************
Id: 9
User: copy
Host: 192.168.98.130:56411
db: NULL
Command: Binlog Dump
Time: 54
State: Master has sent all binlog to slave; waiting for binlog to beupdated
Info: NULL
Progress: 0.000
2 rows in set (0.00 sec)
ERROR: No query specified
测试:
创建数据库:
CREATEDATABASEIFNOTEXISTSyourdbnameDEFAULTCHARSETutf8COLLATEutf8_general_ci;
三.数据库keepalive
1下载keepalived软件
http://www.keepalived.org/software/keepalived-1.3.7.tar.gz
[root@Test0 usr]# cd keepalived-1.1.5
[root@Test0 keepalived-1.1.5]# ls
AUTHORbin ChangeLog configureconfigure.in CONTRIBUTORS COPYINGdoc genhash INSTALLinstall-sh keepalived keepalived.spec libMakefile.in README TODOVERSION
[root@Test0 keepalived-1.1.5]# mkdir -p/usr/local/keepalived
2配置keppalived
[root@Test0 keepalived-1.1.5]# ./configure--prefix=/usr/local/keepalived
问题:1
configure: error:
!!!OpenSSL is not properly installed on your system. !!!
!!!Can not include OpenSSL headers files. !!!
解决方法：
yum -y install openssl-devel
问题:2
configure: error: Popt libraries isrequired
解决方法:
[root@Test2 keepalived-1.1.5]# yum installpopt-devel
make
make install
root@Test0 keepalived-1.1.5]# make install
make -C keepalived install
make[1]: Entering directory`/usr/keepalived-1.1.5/keepalived'
install -d /usr/local/keepalived/sbin
install -m 700 ../bin/keepalived/usr/local/keepalived/sbin/
install -d /usr/local/keepalived/etc/init.d
install -m 755 etc/init.d/keepalived.init/usr/local/keepalived/etc/init.d/keepalived
install -d /usr/local/keepalived/etc/keepalived/samples
install -m 644etc/keepalived/keepalived.conf /usr/local/keepalived/etc/keepalived/
install -m 644 ../doc/samples/*/usr/local/keepalived/etc/keepalived/samples/
make[1]: Leaving directory`/usr/keepalived-1.1.5/keepalived'
make -C genhash install
make[1]: Entering directory`/usr/keepalived-1.1.5/genhash'
install -d /usr/local/keepalived/bin
install -m 755 ../bin/genhash/usr/local/keepalived/bin/
make[1]: Leaving directory`/usr/keepalived-1.1.5/genhash'
[root@Test0 keepalived-1.1.5]#
keepalive 安装成功
拷贝相关启动文件到系统目录
cp/usr/keepalived-1.3.7/keepalived/etc/init.d/keepalived /etc/init.d/
cp /usr/local/sbin/keepalived /usr/sbin/
cp/usr/keepalived-1.3.7/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp -r/usr/keepalived-1.3.7/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
3检查脚本
3.1判断mysql进程
编写检查MySQL进程是否存活shell脚本
E:\文档\linux服务器脚本\check_mysql.bash
1. #!/bin/bash
2. MYSQL=/usr/bin/mysql
3. MYSQL_HOST=localhost
4. MYSQL_USER=root
5. MYSQL_PASSWORD=123
6. CHECK_TIME=3
7. #mysqlisworkingMYSQL_OKis1,mysqldownMYSQL_OKis0
8. MYSQL_OK=1
9. functioncheck_mysql_health(){
10. $MYSQL-h$MYSQL_HOST-u$MYSQL_USER-p$MYSQL_PASSWORD-e"showstatus;">/dev/null2>&1
11. if[$?=0];then
12. MYSQL_OK=1
13. else
14. MYSQL_OK=0
15. fi
16. return$MYSQL_OK
17. }
18. while[$CHECK_TIME-ne0]
19. do
20. let"CHECK_TIME-=1"##（小提示这里我们采用的是let进行整数的运算当然您可以用expr，感觉let省去了$比较方便）
21. check_mysql_health
22. if[$MYSQL_OK=1];then
23. CHECK_TIME=0
24. exit0
25. fi
26.
27. if[$MYSQL_OK-eq0]&&[$CHECK_TIME-eq0]
28. then
29. /etc/init.d/keepalivedstop
30. exit1
31. fi
32. sleep1
33. done
3.2判断防火墙是否打开
1. #!/bin/bash
2.
3. /etc/init.d/iptablesstop
4 keepalived配置文件
主库配置文件
1. !ConfigurationFileforkeepalived
2.
3. global_defs{
4. notification_email{
5. yuchao_cacti@163.com
6. }
7.
8. notification_email_fromyuchao_cacti@163.com
9. smtp_serversmtp.163.com
10. smtp_connect_timeout30
11. router_idLVS_DEVEL
12. vrrp_skip_check_adv_addr
13. vrrp_strict
14. vrrp_garp_interval0
15. vrrp_gna_interval0
16. }
17. vrrp_scriptcheck_run{
18. script"/home/sh/check_mysql.sh"
19. interval5
20. }
21.
22. vrrp_instanceVI_1{
23. stateMASTER
24. interfaceeth0
25. virtual_router_id51
26. priority100
27. advert_int1
28. authentication{
29. auth_typePASS
30. auth_pass1111
31. }
32. virtual_ipaddress{
1. 192.168.98.155/24 deveth0labeleth0:0
33. }
34. track_script{
35. check_run
36. }
37. notify_master /home/sh/close_iptables.sh
38. notify_backup /home/sh/close_iptables.sh
39. }
备用配置文件
2. !ConfigurationFileforkeepalived
3.
4. global_defs{
5. notification_email{
6. yuchao_cacti@163.com
7. }
8.
9. notification_email_fromyuchao_cacti@163.com
10. smtp_serversmtp.163.com
11. smtp_connect_timeout30
12. router_idLVS_DEVEL
13. vrrp_skip_check_adv_addr
14. vrrp_strict
15. vrrp_garp_interval0
16. vrrp_gna_interval0
17. }
18. vrrp_scriptcheck_run{
19. script"/home/sh/check_mysql.sh"
20. interval5
21. }
22.
23. vrrp_instanceVI_1{
24. stateBACKUP
25. interfaceeth0
26. virtual_router_id51
27. priority99
28. advert_int1
29. authentication{
30. auth_typePASS
31. auth_pass1111
32. }
33. virtual_ipaddress{
34. 192.168.98.155/24deveth0labeleth0:0
35. }
36. track_script{
37. check_run
38. }
39. notify_master/home/sh/close_iptables.sh
40. notify_backup /home/sh/close_iptables.sh
41. }
42. virtual_server192.168.98.1553306{
43. delay_loop2
44. lb_algowrr
45. lb_kindDR
46. persistence_timeout60
47. protocolTCP
48. real_server192.168.98.1303306{
49. weight3
50. TCP_CHECK{
51. connect_timeout10
52. nb_get_retry3
53. delay_before_retry3
54. connect_port3306
55. }
56. }
57. }
配置完成
5重新启动keepalived 和mysql
service keepalived start
service mysql start
主设备和从设备做一样的操作
6查看是否正常绑定了虚拟的IP地址
master(主设备)ifconfig
eth0Link encap:Ethernet HWaddr00:50:56:26:31:26
inet addr:192.168.98.132Bcast:192.168.98.255Mask:255.255.255.0
inet6 addr: fe80::250:56ff:fe26:3126/64 Scope:Link
UP BROADCAST RUNNING MULTICASTMTU:1500 Metric:1
RX packets:15300 errors:0 dropped:0 overruns:0 frame:0
TX packets:15135 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:2745739 (2.6 MiB) TXbytes:2846628 (2.7 MiB)
eth0:0 Link encap:Ethernet HWaddr 00:50:56:26:31:26
inet addr:192.168.98.155 Bcast:0.0.0.0Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500Metric:1
loLink encap:Local Loopback
inet addr:127.0.0.1Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNINGMTU:16436 Metric:1
RX packets:4322 errors:0 dropped:0 overruns:0 frame:0
TX packets:4322 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:250079 (244.2 KiB) TXbytes:250079 (244.2 KiB)
backup(从设备)ifconfig
eth0Link encap:Ethernet HWaddr00:0C:29:C3:8D:91
inet addr:192.168.98.130Bcast:192.168.98.255Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fec3:8d91/64 Scope:Link
UP BROADCAST RUNNING MULTICASTMTU:1500 Metric:1
RX packets:15867 errors:0 dropped:0 overruns:0 frame:0
TX packets:7302 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:1913375 (1.8 MiB) TXbytes:1218646 (1.1 MiB)
loLink encap:Local Loopback
inet addr:127.0.0.1Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNINGMTU:16436 Metric:1
RX packets:4212 errors:0 dropped:0 overruns:0 frame:0
TX packets:4212 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:0
RX bytes:235872 (230.3 KiB) TXbytes:235872 (230.3 KiB)
问题3:
发现不能ping 通虚拟IP地址
解决方法:
关闭防火墙
问题4:不能绑定虚拟IP地址
解决方法:
1下载最新版本的keepalived软件
6测试MySQL自动切换
同时主设备和从设备的MySQL keepalived服务
主设备关闭/开启
从另外一同网段主机访问VIP---mysql
[root@Test01 ~]# mysql -umaster -p1111-h192.168.98.155
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 132|
+---------------+-------+
1 row in set (0.00 sec)
关闭主设备时,自动切换到备用数据库
mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 130|
+---------------+-------+
1 row in set (0.00 sec)
仅在本地测试环境中使用,生产环境还需要进一步完善
本文出自 “mysql数据库小节” 博客，请务必保留此出处http://13172370.blog.51cto.com/13162370/1973837
更多相关文章
- MySQL数据库多表查询
- Navicat工具备份还原mysql数据库详细图解
- mysql数据库连接查询
- 如何使用java Web服务从mysql数据库获取数据？
- c语言把mysql数据库语句和变量封装为一个语句
- 使用C＃在MySQL中备份数据库
- MySQL学习笔记_时间，多表更新，数据库元数据
- MySQL数据库root账户的设置和管理
- 【数据库管理工具】Navicat安装及使用教程