mysql主主互备模式配置！！！

21312 · 发表于 2016-3-14 08:39:51

项目背景：
实现mysql的主主实时复制，保证我们的数据安全！

实验环境：
vmware workstation 11
mysql主服务器：ip：192.168.0.53 主机名：DB1
mysql从服务器：ip：192.168.0.26 主机名：DB2
主服务器和从服务器安装的的软件（一样）
mysql-5.1.73-5.el6_6.x86_64
mysql-devel-5.1.73-5.el6_6.x86_64
mysql-libs-5.1.73-5.el6_6.x86_64
mysql-server-5.1.73-5.el6_6.x86_64
mysql主服务器：iptables关掉  setenforce 0
mysql从服务器：iptables关掉  setenforce 0
SecureCRT （ssh远程连接软件）

实验流程：
一、软件下载（主服务器和从服务器分别执行下面的命名安装）
yum install -y mysql mysql-server mysql-devel

二、主机名修改（主服务器和从服务器都修改）
vim /etc/sysconfig/network

三、关掉防火墙（主服务器和从服务器都关闭）
service iptables stop ;chkconfig iptables off

四、修改/etc/hosts文件（主服务器和从服务器都修改）
[iyunv@DB1 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.53  DB1
192.168.0.26  DB2

[iyunv@DB2 ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.0.53  DB1
192.168.0.26  DB2

五、修改mysql配置文件（/etc/my.cnf）
主服务器配置文件：
[iyunv@DB1 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=1
log-bin=mysql-bin
relay-log =mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

从服务器配置：
[iyunv@DB2 ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=2
log-bin=mysql-bin
relay-log =mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

六、手动同步数据库
打包mysql来实现，具体你可以百度一下。

七、创建复制用户并授权
登录以后执行：
主服务器：
grant replication slave on *.* to  'repl_user'@'192.168.0.26' identified by 'repl_passwd';

从服务器：

grant replication slave on *.* to 'repl_user'@'192.168.0.53' identified by 'repl_passwd';

八、执行 show master status；

主服务器执行

从服务器执行

记住它的信息

九、在DB2 mysql数据库把DB1设置为主服务器，然后执行：start slave

mysql> change master to \
-> master_host ='192.168.0.53',
-> master_user='repl_user',
-> master_password='repl_passwd',
-> master_log_file='mysql-bin.0000004'
-> msater_log_pos='1001'

十、在DB1 mysql数据库把DB2设置为主服务器，然后执行：start slave

mysql> change master to \

-> master_host ='192.168.0.26',

-> master_user='repl_user',

-> master_password='repl_passwd',

-> master_log_file='mysql-bin.0000004'

-> msater_log_pos='417'

十一、安装keepalived软件

十二、修改keepalived配置文件

DB1:

[iyunv@DB1 ~]# 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 LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 80
priority 100
advert_int 2
nopreempt

authentication {
      auth_type PASS
      auth_pass 123456
}
virtual_ipaddress {
      192.168.0.10/24
}
}

DB2：
[iyunv@DB2 ~]# 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 LVS_DEVEL
}

vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 80
priority 90
advert_int 2

authentication {
      auth_type PASS
      auth_pass 123456
}
virtual_ipaddress {
      192.168.0.10/24
}
}

十三、启动keepalived（DB1和DB2都启动）

十四、远程mysql登录
[iyunv@localhost ~]# mysql -u root -p -h 192.168.0.10
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.73-log Source distribution

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> show databes;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'databes' at line 1
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| fuchao1          |
| mysql             |
| test             |
+--------------------+
4 rows in set (0.00 sec)

mysql> show  variables like "hostname%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname    | DB1 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show  variables like "%server_id%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id    | 1    |
+---------------+-------+
1 row in set (0.00 sec)

可以看到我们用vip登录的，现在主是DB1

十五、数据复制测试

我在DB1上创建一个数据库，看看DB2上会不会复制过去