程序员老A

MySQL主从实时备份构建简述
centos 7.5 MySQL 5.6 主服务器:192.168.2.2 从服务器:192.168.2.3 首先...
扫描右侧二维码阅读全文
21
2018/11

MySQL主从实时备份构建简述

centos 7.5
MySQL 5.6
主服务器:192.168.2.2
从服务器:192.168.2.3
首先在2台服务器上安装2个版本一样的MySQL数据库,安装包产考:常用web软件yum源,源码包集合

主服务器配置

在MySQL配置文件/etc/my.cnf在[mysqld]下插入 必要选项——主从服务器都要添加

log-bin=mysql-bin           #开启二进制日志,主从都要
server-id = 2              #服务器在网络中的ID,主从不重复 
mysql> grant all on *.* to 'master'@'192.168.2.3' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

次要选项
replicate_wild_do_table=test.%   #指定同步某个库的数据
relay_log=mysqld-relay-bin       #记录中继日志
log-slave-updates=YES          #从服务器同步后记录日志 在主服务器上创建一个同步专用的用户,并且只允许在从服务器上面登录

查看主服务器的二进制日志,稍后要用

mysql> show master status;
+---------------------+----------+--------------+--------------------+-------------------+
|  File               | Position | Binlog_Do_DB | Binlog_Ignore_DB   | Executed_Gtid_Set |
+---------------------+----------+--------------+--------------------+-------------------+
| mysql-240-bin.000004|      684 |              |                    |                   |
+---------------------+----------+--------------+--------------------+-------------------+
1 row in set (0.00 sec)

从服务器开始配置

mysql>change master to master_host='192.168.2.2',master_user='master',master_password='123456',master_log_file='mysql-240-bin.000004',master_log_pos=684;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave ;     #启动slave
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
……………………………省略输出………………………
Slave_IO_Running: Yes    
Slave_SQL_Running: Yes
…………………………省略输出…………………………

看到IO,和SQL都没问题,主从就意味着可以了,这是主服务器做的任何增删改操作都会被同步到从服务器上去!

这就是主从的配置,其实明白了主从,主主,一主多从就差不多都明白了,
主主就是将主从的配置做两遍,两台服务器互相备份,
主从是配置一台主服务器,授权给多个从服务器

Last modification:December 24th, 2018 at 12:50 pm

One comment

  1. Jack

    You've made some good points there. I checked on the internet for additional information about the issue and found most people
    will go along with your views on this web site. There is certainly a lot to
    find out about this issue. I like all the points you've
    made. I’ve been surfing on-line greater than 3 hours today, yet I never found any attention-grabbing article like yours.
    It is beautiful value enough for me. In my opinion, if all site owners and bloggers made just right content material as you
    did, the web shall be much more useful than ever before.
    http://www.Cspan.net

Leave a Comment