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

项目背景：

实现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文件（主服务器和从服务器都修改）

[root@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

[root@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）

主服务器配置文件：

[root@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

从服务器配置：

[root@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；

主服务器执行

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |     1001 |              |                  |

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

1 row in set (0.00 sec)

mysql> 

从服务器执行

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      417 |              |                  |

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

1 row in set (0.00 sec)

mysql> 

记住它的信息

九、在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:

[root@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：

[root@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登录

[root@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上会不会复制过去

当然你进行删除的话 也会同步！！！！ 你可以自己试一下！

谢谢大家，希望对大家有帮助。