mysql主主备份_mysql 主主备份

1.1、主主备份原理。

主主备份实际上是互为主从，主要是为了去缓解写入压力。

1.2、环境准备

两台机器ip分别为

100.100.100.105 (主1)

100.100.100.106(主2)

安装 mysql

[root@centos ~]# yum install mysql-server mysql-client -y

[root@centos ~]# service mysqld start

[root@centos ~]# /usr/bin/mysqladmin -u root password '123456'

1.3、修改配置文件

100.100.100.105

[root@centos ~]# vim /etc/my.cnf

log-bin=mysql-bin

server-id=105

#replicate-do-db=test #指定备份的数据库

binlog-ignore-db=mysql #忽略备份的数据库

binlog-ignore-db=infogmation_schema

auto-increment-offset=1 #主键id从1开始

auto-increment-increment=2 #主键id每次增长2

#相当于 1，3，5

100.100.100.106

[root@centos ~]# vim /etc/my.cnf

log-bin=mysql-bin

server-id=106

#replicate-do-db=test #指定备份的数据库

binlog-ignore-db=mysql #忽略备份的数据库

binlog-ignore-db=infogmation_schema

auto-increment-offset=2 #主键id从2开始

auto-increment-increment=2 #主键id每次增长2

#相当于 2，4，6

1.4、105机器为主，106为从

# 100.100.100.105

mysql> grant replication slave on *.* to slave@'100.100.100.106' identified by '123456';

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000005 | 906 | | mysql,infogmation_schema |

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

#100.100.100.106

change master to

master_user='slave',

master_password='123456',

master_host='100.100.100.105',

master_log_file='mysql-bin.000005',

master_log_pos=906;

1.5、106机器为主，105为从

#100.100.100.106

mysql> grant replication slave on *.* to slave@'100.100.100.105' identified by '123456';

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 | 266 | | mysql,infogmation_schema |

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

#100.100.100.105

change master to

master_user='slave',

master_password='123456',

master_host='100.100.100.106',

master_log_file='mysql-bin.000002',

master_log_pos=266;

1.6、都开启从mysql

mysql> start slave;

1.7、成功测试

mysql> show slave status\G; (两个都得有)

Slave_IO_Running: Yes

Slave_SQL_Running: Yes