一.mysql数据库RPM安装包安装
rpm –qa |grep -i mysql //查看是否安装了数据库
rpm -e 文件名–nodeps //卸载已安装的文件
tar –xzvf ./ 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.创建一个复制用户，具有replication slave 权限。
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
测试:
创建数据库:
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_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
AUTHOR bin ChangeLog configure configure.in CONTRIBUTORS COPYING doc genhash INSTALL install-sh keepalived keepalived.spec lib Makefile.in README TODO VERSION
[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. #mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
8. MYSQL_OK=1
9. function check_mysql_health (){
10. $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show status;" >/dev/null 2>&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 -ne 0 ]
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. exit 0
25. fi
26.
27. if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
28. then
29. /etc/init.d/keepalived stop
30. exit 1
31. fi
32. sleep 1
33. done
3.2判断防火墙是否打开
1. #! /bin/bash
2.
3. /etc/init.d/iptables stop
4 keepalived配置文件
主库配置文件
1. ! Configuration File for keepalived
2.
3. global_defs {
4. notification_email {
5. yuchao_cacti@163.com
6. }
7.
8. notification_email_from yuchao_cacti@163.com
9. smtp_server smtp.163.com
10. smtp_connect_timeout 30
11. router_id LVS_DEVEL
12. vrrp_skip_check_adv_addr
13. vrrp_strict
14. vrrp_garp_interval 0
15. vrrp_gna_interval 0
16. }
17. vrrp_script check_run {
18. script "/home/sh/check_mysql.sh"
19. interval 5
20. }
21.
22. vrrp_instance VI_1 {
23. state MASTER
24. interface eth0
25. virtual_router_id 51
26. priority 100
27. advert_int 1
28. authentication {
29. auth_type PASS
30. auth_pass 1111
31. }
32. virtual_ipaddress {
1. 192.168.98.155/24 dev eth0 label eth0: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. ! Configuration File for keepalived
3.
4. global_defs {
5. notification_email {
6. yuchao_cacti@163.com
7. }
8.
9. notification_email_from yuchao_cacti@163.com
10. smtp_server smtp.163.com
11. smtp_connect_timeout 30
12. router_id LVS_DEVEL
13. vrrp_skip_check_adv_addr
14. vrrp_strict
15. vrrp_garp_interval 0
16. vrrp_gna_interval 0
17. }
18. vrrp_script check_run {
19. script "/home/sh/check_mysql.sh"
20. interval 5
21. }
22.
23. vrrp_instance VI_1 {
24. state BACKUP
25. interface eth0
26. virtual_router_id 51
27. priority 99
28. advert_int 1
29. authentication {
30. auth_type PASS
31. auth_pass 1111
32. }
33. virtual_ipaddress {
34. 192.168.98.155/24 dev eth0 label eth0: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_server 192.168.98.155 3306 {
43. delay_loop 2
44. lb_algo wrr
45. lb_kind DR
46. persistence_timeout 60
47. protocol TCP
48. real_server 192.168.98.130 3306 {
49. weight 3
50. TCP_CHECK {
51. connect_timeout 10
52. nb_get_retry 3
53. delay_before_retry 3
54. connect_port 3306
55. }
56. }
57. }
配置完成
5重新启动keepalived 和mysql
service keepalived start
service mysql start
主设备和从设备做一样的操作
6查看是否正常绑定了虚拟的IP地址
master(主设备)ifconfig
eth0 Link encap:Ethernet HWaddr00:50:56:26:31:26
inet addr:192.168.98.132 Bcast:192.168.98.255 Mask:255.255.255.0
inet6 addr: fe80::250:56ff:fe26:3126/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU: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.0 Mask:255.255.255.0
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU: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
eth0 Link encap:Ethernet HWaddr00:0C:29:C3:8D:91
inet addr:192.168.98.130 Bcast:192.168.98.255 Mask:255.255.255.0
inet6 addr: fe80::20c:29ff:fec3:8d91/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU: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)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU: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)
仅在本地测试环境中使用,生产环境还需要进一步完善
