Keepalived+Mysql怎样配置主主复制

小编给大家分享一下Keepalived+Mysql如何配置主主复制，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
 
安装环境：
 
Hostname IP virtual IP
D2-TYMYSQL51 192.168.12.51 192.168.12.50
D2-TYMYSQL52 192.168.12.52
软件包：
keepalived-1.3.5.tar.gz
mysql-5.7.16-linux-glibc2.5-x86_64.tar.gz
 
节点51部署
（1）环境确认
[root@D2-TYMYSQL51 ~]# cat /etc/centos-release
CentOS Linux release 7.2.1511 (Core)
[root@D2-TYMYSQL51 ~]# hostnamectl status
   Static hostname: D2-TYMYSQL51
         Icon name: computer-vm
           Chassis: vm
        Machine ID: 4ff908d829c546f8a037b61638b50e43
           Boot ID: 43cbb3326cd646f9aaa40b63c77384b5
    Virtualization: vmware
  Operating System: CentOS Linux 7 (Core)
       CPE OS Name: cpe:/o:centos:centos:7
            Kernel: Linux 3.10.0-327.el7.x86_64
      Architecture: x86-64
[root@D2-TYMYSQL51 ~]# cat /etc/hosts
192.168.  D2-
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.12.51  D2-TYMYSQL51
[root@D2-TYMYSQL51 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
[root@D2-TYMYSQL51 ~]# getenforce
Disabled
 
 
（2）安装数据库
参考http://blog.itpub.net/28536251/viewspace-2138854/安装Mysql，注意目录的位置。
[root@D2-TYMYSQL51 ~]# ps -ef | grep mysql
root     31799     1  0 11:16 pts/0    00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --datadir=/opt/mysql/data --pid-file=/opt/mysql/data/D2-TYMYSQL51.pid
mysql    32214 31799 37 11:16 pts/0    00:00:10 /opt/mysql/bin/mysqld --basedir=/opt/mysql --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/opt/mysql/data/D2-TYMYSQL51.pid
root     32754 30568  0 11:17 pts/0    00:00:00 grep --color=auto mysql
 
[root@D2-TYMYSQL51 ~]# rm -fr /opt/mysql/data/auto.cnf
[root@D2-TYMYSQL51 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
 
 
（3）主从配置
[root@D2-TYMYSQL51 ~]# vim /etc/my.cnf
[root@D2-TYMYSQL51 ~]# cat /etc/my.cnf
[mysqld]
server-id = 1251
character-set-server=utf8
 
slow-query-log
long_query_time=3
log-output=TABLE
expire_logs_days = 7
 
basedir=/opt/mysql
datadir=/opt/mysql/data
 
event_scheduler=1
 
innodb_file_per_table=1
innodb_log_file_size=536870912
innodb_buffer_pool_size=1073741824
max_binlog_size=1073741824
 
log_bin_trust_function_creators=1
innodb_stats_on_metadata=0
 
max_connect_errors = 1000000
max_connections = 5000
 
skip_name_resolve = 1
symbolic-links=0
 
 
[mysql]
default-character-set=utf8
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
[root@D2-TYMYSQL51 ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
 
[root@D2-TYMYSQL51 ~]# /opt/mysql/bin/mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.22-log Source distribution
 
Copyright (c) 2000, 2014, 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 master status;
+---------------+----------+--------------+--------------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+---------------+----------+--------------+--------------------------+-------------------+
| binlog.000003 |      120 |              | mysql,information_schema |                   |
+---------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
 
mysql> grant replication slave on *.* to 'replication'@'192.168.12.%' identified by 'replication';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant replication slave on *.* to 'root'@'192.168.12.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
 
mysql> grant all privileges on *.* to 'root'@'192.168.12.%' identified by '123456' with grant option;
Query OK, 0 rows affected (0.00 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
 
mysql> change master to master_host='192.168.12.52',master_user='replication',master_password='replication',master_log_file='binlog.000003',master_log_pos=637;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.12.52
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 120
               Relay_Log_File: relaylog.000005
                Relay_Log_Pos: 280
        Relay_Master_Log_File: binlog.000004
             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: 120
              Relay_Log_Space: 606
              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: 1252
                  Master_UUID: e3b13736-7258-11e5-b726-005056a70ab8
             Master_Info_File: /opt/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
 
 
（4）主从测试
主库：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
 
mysql> use test;
Database changed
mysql> create table usr(number int(10),name varchar(255));
Query OK, 0 rows affected (0.01 sec)
 
mysql> insert into usr values(01,'aa');
Query OK, 1 row affected (0.00 sec)
 
从库：
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usr            |
+----------------+
1 row in set (0.00 sec)
 
mysql> select * from usr;
+--------+------+
| number | name |
+--------+------+
|      1 | aa   |
+--------+------+
1 row in set (0.00 sec)
 
确认同步正常后删除测试数据库：
mysql> drop database test;
Query OK, 1 row affected (0.00 sec)