mysql keepalived主主_动视

mysql keepalived主主

2025-09-24 10:54:32 责编:小OO

点击下载本文 文档为doc格式

Mysql + keepalived 主主配置

环境

Mysql db1主 192.168.0.28

Mysql db2主 192.168.0.196

Mysql vip 192.168.0.100

在Mysql db1主和Mysql db2主相同配置

一软件

mysql-5.1.38.tar.gz 数据库

keepalived-1.1.20.tar.gz

二安装配置

1 安装数据库

sudo -s

LANG=C

yum -y install gcc gcc-c++ autoconf libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel openldap openldap-devel nss_ldap openldap-clients openldap-servers

libtermcap-devel

2 解压缩数据库

tar -zxvf mysql-5.1.38.tar.gz -C /usr/src/

cd /usr/src/mysql-5.1.38/

3 创建数据库用户

useradd -M -s /sbin/nologin mysql

4 编译安装mysql

./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --enable-assembler --with-extra-charsets=complex --enable-thread-safe-client --with-big-tables --with-readline --with-ssl --with-embedded-server --enable-local-infile --with-plugins=partition,innobase,myisammrg

5 安装数据库

Make && make install

[root@mail mysql-5.1.38]# cp support-files/my-medium.cnf /etc/my.cnf

/usr/local/mysql/bin/mysql_install_db --user=mysql

chown -R root:mysql /usr/local/mysql/

chown -R mysql /usr/local/mysql/var/

echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf

执行命令刷新动态链接库

Ldconfig

/usr/local/mysql/bin/mysqld_safe --user=mysql &

查看mysql

ps -e | grep mysql

6061 pts/0 00:00:00 mysqld_safe

61 pts/0 00:00:00 mysqld

cp support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

chkconfig --add mysqld

chkconfig mysqld on

export PATH=$PATH:/usr/local/mysql/bin/

echo "PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile

mysqladmin -uroot password 123456 生成root密码

6 编辑mysql配置文件

[root@mail mysql-5.1.38]# vi /etc/my.cnf

修改或添加如下内容

server-id = 1 \\\\mysql db2 的id写成2 ，mysql slave写成3只有不重复就可以

binlog_ignore_db = test 不同步的数据库

binlog_ignore_db = information_schema

binlog_ignore_db = mysql

binlog_ignore_db = extmail

replicate-ignore-db = mysql 不复制的数据库

replicate-ignore-db = test

replicate-ignore-db = information_schema

replicate-ignore-db = extmail

log-slave-updates

slave-skip-errors=all

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

注： mysql db2的配置只有server-id = 1不同其他一样

[root@mail mysql-5.1.38]# /etc/init.d/mysqld restart 重启数据库

7 配置mysql的账号

mysql –u root –p 进入mysql db1数据库

执行 grant replication slave on *.* to 'test'@'%' identified by '123456';

表示授权test用户对主数据库有replication slave权限

mysql –u root –p 进入mysql db2数据库

执行 grant replication slave on *.* to 'test'@'%' identified by '123456';

表示授权test用户对主数据库有replication slave权限

8 在mysql db1 查看mysql数据库的master的日志文件和坐标位置

[root@mail mysql-5.1.38]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 774

Server version: 5.1.38-log Source distribution

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql> show master status;

+------------------+----------+--------------+---------------------------------------+

+------------------+----------+--------------+---------------------------------------+

+------------------+----------+--------------+---------------------------------------+

1 row in set (0.00 sec)

9 进入mysql db2

mysql> change master to

-> master_host='192.168.0.28',

-> master_user='test',

-> master_password='123456',

-> master_log_file=’ mysql-bin.000017',

master_log_pos=6965;

启动从服务器slave线程

mysql>start slave;

查看数据库状态

mysql> show slave status\\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.196

Master_User: test

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 6965

Relay_Log_File: mail-relay-bin.000034

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,test,information_schema,extmail

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

Relay_Log_Space: 2850

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:

1 row in set (0.00 sec)

其中 Slave_IO_Running: Yes

Slave_SQL_Running: Yes必须为yes才可以同步

mysql> show master status; 查看mysql db2 的master信息并记录

+------------------+----------+--------------+---------------------------------------+

+------------------+----------+--------------+---------------------------------------+

+------------------+----------+--------------+---------------------------------------+

1 row in set (0.00 sec)

10 登录mysql db1 进入数据库

mysql> change master to

-> master_host='192.168.0.196,

-> master_user='test',

-> master_password='123456',

-> master_log_file=' mysql-bin.000027',

→master_log_pos=292921;

启动从服务器slave线程

mysql>start slave;

mysql> show slave status\\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.196

Master_User: test

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000017

Read_Master_Log_Pos: 6965

Relay_Log_File: mail-relay-bin.000034

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000017

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,test,information_schema,extmail

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

Relay_Log_Space: 2850

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:

1 row in set (0.00 sec)

三安装keepalived

Mysql db1和mysql db2 相同

Yum install kernel-*

tar zxvf keepalived-1.1.20.tar.gz

cd keepa;ived

./configure --with-kernel-dir=/usr/src/kernels/2.6.18-274.3.1.el5-i686/

mkdir /etc/keepalived

cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

cp /usr/local/etc/rc.d/init.d/keepalived /etc/init.d/

ln -s /usr/local/sbin/keepalived /usr/sbin/keepalived

编辑配置文件vi /etc/keepalived/keepalived.conf

global_defs {

notification_email {

1983ty@sina.com

}

notification_email_from Alexandre.Cassen@firewall.loc

smtp_server smtp.sina.com

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_sync_group GLVS {

group {

LVS_1

}

vrrp_instance LVS_1 {

state BACKUP

interface eth0

lvs_sync_daemon_inteface eth0

nopreempt 不抢占，只在优先级高的机子上即可，优先级低得不设置。

virtual_router_id 51

priority 90 优先级另一台为80

advert_int 1

"/etc/keepalived/keepalived.conf" 53L, 959C

192.168.0.100

}

virtual_server 192.168.0.100 3306 {

delay_loop 6

lb_algo wlc

lb_kind DR

nat_mask 255.255.255.255

persistence_timeout 50

protocol TCP

real_server 192.168.0.28 3306 {

weight 1

notify_down /etc/keepalived/mysql.sh

TCP_CHECK {

connect_timeout 3

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

[root@mail keepalived-1.1.20]# cat /etc/keepalived/mysql.sh

#!/bin/sh

pkill keepalived

[root@mail keepalived-1.1.20]#

Chmod +x /etc/keepalived/mysql.sh

分别在2台数据库启动服务/etc/init.d/mysqld restart

/etc/init.d/keepalived start

之后就可以尝试2个数据库同步了。关掉其中一个主库的服务，另一台自动接管vip提供服务下载本文

显示全文

全部频道