搜索热词
1.1、主主备份原理。
主主备份实际上是互为主从,主要是为了去缓解写入压力。
1.2、环境准备
两台机器ip分别为
100.100.100.105 (主1)
100.100.100.106(主2)
[root@centos ~]# yum install MysqL-server MysqL-client -y
[root@centos ~]# service MysqLd start
[root@centos ~]# /usr/bin/MysqLadmin -u root password '123456'
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_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
总结
以上是编程之家为你收集整理的mysql 主主备份全部内容,希望文章能够帮你解决mysql 主主备份所遇到的程序开发问题。
如果觉得编程之家网站内容还不错,欢迎将编程之家网站推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。