binlog-do-db和replicate-do-db表示需要同步的数据库
binlog-ignore-db和replicate-ignore-db表示不需要同步的数据库
云端服务器为master配置如下:
[root@DB data]# egrep "server-id|log-bin|binlog-ignore-db|auto-increment-increment|auto-increment-offset|slave-skip-errors" /etc/my.cnf
log-bin=mysql-bin
binlog-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all
server-id = 1
#server-id = 2
#log-bin=mysql-bin
先查看下log bin日志和pos值位置:
[root@DB data]# mysql -uroot -p -e "show master status;"
Enter password:
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000016 | 107 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
[root@DB data]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL... SUCCESS!
mysql> select user,password,host from user;
+--------+-------------------------------------------+-----------+
| user | password | host |
+--------+-------------------------------------------+-----------+
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |
+--------+-------------------------------------------+-----------+
2 rows in set (0.05 sec) mysql> grant replication slave on *.* to 'rep'@'%' identified by 'replication';
Query OK, 0 rows affected (0.05 sec) mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) mysql> change master to
-> master_host='192.168.1.99',
-> master_user='rep',
-> master_password='replication',
-> master_log_file='mysql-bin.000016',
-> master_log_pos=107;
Query OK, 0 rows affected (0.41 sec) mysql> select user,password,host from user;
+--------+-------------------------------------------+-----------+
| user | password | host |
+--------+-------------------------------------------+-----------+
| root | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | localhost |
| rep | *D36660B5249B066D7AC5A1A14CECB71D36944CBC | % |
| zabbix | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | % |
+--------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
backup配置如下:
mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。
auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 ..
auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 ..
在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.
mysql> use mysql;
Database changed
mysql> select user,host,password from user;
+--------+-----------+-------------------------------------------+
| user | host | password |
+--------+-----------+-------------------------------------------+
| root | localhost | *5E9021B1C46435835981A94A7087707B3465144B |
| master | % | *5E9021B1C46435835981A94A7087707B3465144B |
| rep | % | *5E9021B1C46435835981A94A7087707B3465144B |
+--------+-----------+-------------------------------------------+
3 rows in set (0.00 sec) mysql> grant replication slave on *.* to 'rep'@'%' identified by "3306";
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000023 | 184 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> change master to
-> master_host='192.168.1.66',
-> master_user='rep',
-> master_password='3306',
-> master_log_file='mysql-bin.000023',
-> master_log_pos=184;
Query OK, 0 rows affected (0.29 sec) mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果有如下报错及解决方案：
mysql> slave stop
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> reset slave;
Query OK, 0 rows affected (0.18 sec) mysql> slave start;
Query OK, 0 rows affected (0.13 sec) mysql> show slave status;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@DB-S data]# egrep "server-id|log-bin|binlog-ignore-db|auto-increment-increment|auto-increment-offset|slave-skip-errors" /etc/my.cnf
log-bin=mysql-bin
server-id = 2
MASTER中新建张表;
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec) mysql> create table user (number INT(10),name VARCHAR(255));
Query OK, 0 rows affected (0.17 sec) mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.01 sec)
查看BACKUP是否同步：
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
在backup中插入一条数据：
mysql> insert into user values(01,'zhangsan');
Query OK, 1 row affected (0.11 sec) mysql> select * from user;
+--------+----------+
| number | name |
+--------+----------+
| 1 | zhangsan |
+--------+----------+
1 row in set (0.00 sec)
查看MASTER是否同步成功：
mysql> select * from user;
+--------+----------+
| number | name |
+--------+----------+
| 1 | zhangsan |
+--------+----------+
1 row in set (0.00 sec)
同步成功！
在BACKUP端：
[root@DB ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包
[root@DB ~]# which wget
/usr/bin/wget
[root@DB ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
[root@DB ~]# ls
keepalived-1.2.7.tar.gz
[root@DB ~]# cd keepalived-1.2.7
[root@DB keepalived-1.2.7]# pwd
/root/keepalived-1.2.7
[root@DB keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived
[root@DB keepalived-1.2.7]# make && make install
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@DB keepalived-1.2.7]# mkdir /etc/keepalived/
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@DB keepalived-1.2.7]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@DB keepalived]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs {
router_id MYSQL_HA#标识，双主相同
}
vrrp_script check_run{
script '/usr/local/mysql/mysql_check.sh'
interval 300
}
vrrp_instance VI_1 {
state BACKUP#两台都设置BACKUP
interface eth0
virtual_router_id 51#主备相同
priority 100#优先级，backup设置90(最关建点之一，同为backup时优先级别越高，就会主动去抢VIP，所以如果不想让这种情况出现加参数nopreempt)
advert_int 1#主备心跳检查间隔
nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置(当master恢复时，不会把VIP抢过来)
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.74 #vip
}
} virtual_server 192.168.1.74 3306 {
delay_loop 2
#lb_algo rr#LVS算法，用不到，我们就关闭了
#lb_kind NAT
#nat_mask 255.255.255.0
persistence_timeout 50#同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP real_server 192.168.1.66 3306 { #检测本地mysql，backup也要写检测本地mysql
weight 3
notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3#连接超时
nb_get_retry 3#重试次数
delay_before_retry 3#重试间隔时间
}
}
[root@DB keepalived]# cat /usr/local/keepalived/mysql.sh
#!/bin/bash /usr/bin/pkill keepalived
backup端：
[root@DB-S ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id MYSQL_HA
#router_id LVS_DEVEL
} vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.74
}
} virtual_server 192.168.1.74 3306 {
delay_loop 2
#lb_algo rr
#lb_kind NAT
#nat_mask 255.255.255.0
persistence_timeout 50
protocol TCP real_server 192.168.1.99 3306 {
weight 3
notify_down /usr/local/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
[root@DB ~]# chmod +x /usr/local/keepalived/mysql.sh
[root@DB ~]# /etc/init.d/keepalived start
# 授权两台DB服务器允许管理员登录，用于其它服务器登录测试！
mysql> grant all on *.* to 'admin'@'%' identified by '3306';
Query OK, 0 rows affected (0.00 sec) mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec) mysql> select user,host,password from user;
+--------+-----------+-------------------------------------------+
| user | host | password |
+--------+-----------+-------------------------------------------+
| U_R | % | *5E9021B1C46435835981A94A7087707B3465144B |
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| admin | % | *5E9021B1C46435835981A94A7087707B3465144B |
| rep | % | *D36660B5249B066D7AC5A1A14CECB71D36944CBC |
| zabbix | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+--------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
通过MYSQL客户端通过VIP连接，看是否成功！
[root@bass ~]# mysql -uadmin -h192.168.1.74 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 175
Server version: 5.5.40-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
成功连接！！！！
可通MASTER或BACKUP的/var/log/messges日志，看出主备切换过程
[root@DB ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:94:a0:9f brd ff:ff:ff:ff:ff:ff
inet 192.168.1.66/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.74/32 scope global eth0
inet6 fe80::5054:ff:fe94:a09f/64 scope link
valid_lft forever preferred_lft forever
[root@DB-S ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:e4:6a:a4 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.99/24 brd 192.168.1.255 scope global eth0
inet6 fe80::5054:ff:fee4:6aa4/64 scope link
valid_lft forever preferred_lft forever
[root@DB ~]# service mysqld stop
Shutting down MySQL.... SUCCESS!
[root@DB ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:94:a0:9f brd ff:ff:ff:ff:ff:ff
inet 192.168.1.66/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.74/32 scope global eth0
inet6 fe80::5054:ff:fe94:a09f/64 scope link
valid_lft forever preferred_lft forever
[root@DB-S ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 52:54:00:e4:6a:a4 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.99/24 brd 192.168.1.255 scope global eth0
inet 192.168.1.74/32 scope global eth0
inet6 fe80::5054:ff:fee4:6aa4/64 scope link
valid_lft forever preferred_lft forever
#vip 在BACKUP上出现
特别注意：mysql客户端不能在和双主服务器在一台上，否则VIP飘逸时，客户端连接会失败！