MySQL主主备份

﹏ヽ暗。殇╰゛Y 2023-10-09 09:46 38阅读 0赞

### 文章目录 ###

*  1、配置
 *  2、配置主库：106.11.111.11
 *  3、配置主库：106.11.111.12
 *  4、开始配置主主
 *  5、开始验证
 *  6、杂谈

--------------------

# 1、配置 #

一台 centos 数据库  
端口：3306  
地址：106.11.111.11

一台 docker 数据库  
端口：3307  
地址：106.11.111.12

因为资源有限，只好先这样搭建两台数据库进行测试使用。在正常情况下使用，最少是两台服务器进行操作。

### 操作的步骤都是一样的，不用在意我使用的是容器。在主从数据库我会先建两个相同的数据库 ###

centos的数据库已经安装好了

运行容器数据库,可以参考：  
[https://blog.csdn.net/qq\_38637558/article/details/101231395][https_blog.csdn.net_qq_38637558_article_details_101231395]

# 首先检查主从两台Mysql是否作为其他master的slave，并停止复制，以免受到影响。
    show slave status\G
    stop slave;

# 2、配置主库：106.11.111.11 #

修改my.cnf  
我的位置在：/usr/local/mysql

vim my.cnf
    
    [mysqld]
    server-id=1                       #指定master主机的id，不可为0，否则拒绝所有slave连接。
    log-bin=mysql_bin                  #指定bin-log文件前缀名称，开启binlog日志
    binlog_do_db=nestcloud        #指定binlog日志是记录的是哪个库 （nestcloud是我的数据库名）
    replicate-do-db=nestcloud       #指定复制哪一个库（nestcloud是我的数据库名）
    auto-increment-increment = 2   #每次增长2
    auto-increment-offset = 1  #设置自动增长的字段的偏移量，即初始值为1
    log_bin_trust_function_creators=1           #下面进行解释
    expire_logs_days = 10            #保留10天的bin_log日志，防止日志太多占用磁盘空间
    max_binlog_size = 100M              #限制每个bin_log日志大小最大为100M。
    #log-slave-updates=1        #slave执行master的sql后，将sql记录在binlog日志中（默认是不记录的）——实际生产我没开启这条
    max_connections=600             #指定最大连接数
    wait_timeout=5                       #等待超时
    
    
    log_bin_trust_function_creators： 
    当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。
    如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。
    如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70]

# 重启MySQL
    service mysqld restart

创建账号

# 进入MySQL
    mysql -h127.0.0.1 -uroot -p
    #创建用户 106.11.111.12  （从服务器的IP）
    CREATE USER 'sla'@'106.11.111.12' IDENTIFIED BY 'password123';
    #分配权限
    GRANT REPLICATION SLAVE ON *.* TO 'sla'@'106.11.111.12';
     #刷新权限
    flush privileges;

查看master状态，记录二进制文件名(mysql\_bin.000001)和位置(120)：

show master status;

![在这里插入图片描述][20190925003749778.png]

# 3、配置主库：106.11.111.12 #

其实我们配置这台主库，和配置上面的那台主库是一样的操作。基本上是没有什么不一样的，只不过my.cnf文件不同罢了  
需要注意：server-id 和 auto-increment-offset 这两个值

vim my.cnf
    
    [mysqld]
    server-id=2                       #该值不能不可为0，并且所操作数据库的该值也不能一样
    log-bin=mysql_bin                  #指定bin-log文件前缀名称，开启binlog日志
    binlog_do_db=nestcloud        #指定binlog日志是记录的是哪个库 （nestcloud是我的数据库名）
    replicate-do-db=nestcloud       #指定复制哪一个库（nestcloud是我的数据库名）
    auto-increment-increment = 2   #每次增长2
    auto-increment-offset = 2  #该值应设为操作的数据库的总数，本案例用到两台服务器，所以值设为2。
    log_bin_trust_function_creators=1           #下面进行解释
    expire_logs_days = 10            #保留10天的bin_log日志，防止日志太多占用磁盘空间
    max_binlog_size = 100M              #限制每个bin_log日志大小最大为100M。
    #log-slave-updates=1        #slave执行master的sql后，将sql记录在binlog日志中（默认是不记录的）——实际生产我没开启这条
    max_connections=600             #指定最大连接数
    wait_timeout=5                       #等待超时
    
    
    log_bin_trust_function_creators： 
    当二进制日志启用后，这个变量就会启用。它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。
    如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。
    如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 1]

# 重启MySQL
    service mysqld restart

创建账号

# 进入MySQL
    mysql -h127.0.0.1 -uroot -p
    #创建用户 106.11.111.12  （从服务器的IP）
    CREATE USER 'sla2'@'106.11.111.12' IDENTIFIED BY 'password123';
    #分配权限
    GRANT REPLICATION SLAVE ON *.* TO 'sla2'@'106.11.111.12';
     #刷新权限
    flush privileges;

查看master状态，记录二进制文件名(mysql\_bin.000001)和位置(786)：

show master status;

![在这里插入图片描述][20190925005013746.png]

# 4、开始配置主主 #

# 我现在在数据库：106.11.111.12 #

进入MySQL

执行同步SQL语句:

mysql> CHANGE MASTER TO
        -> MASTER_HOST='106.11.111.11',
        -> MASTER_USER='sla',
        -> MASTER_PASSWORD='password123',
        -> MASTER_PORT=3306,
        -> MASTER_LOG_FILE='mysql_bin.000001',
        -> MASTER_LOG_POS=120;
    
    MASTER_HOST：106.11.111.11 数据库地址
    MASTER_USER：106.11.111.11 用户名
    MASTER_PASSWORD：106.11.111.11 密码
    MASTER_PORT：106.11.111.11 端口，如果是3306可以不写
    MASTER_LOG_FILE：106.11.111.11 指定开始复制的binlog二进制文件。
    MASTER_LOG_POS：106.11.111.11 指定（在master二进制文件中）要开始复制的位置
    
    # 整合成一段
    CHANGE MASTER TO MASTER_HOST='106.11.111.11', MASTER_USER='sla', MASTER_PASSWORD='password123', MASTER_PORT=3306, MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=120;

![在这里插入图片描述][20190925010003975.png]

启动slave同步进程：
    start slave;
    
    查看slave状态：
    show slave status\G

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 2]  
当这个两个为yes就证明成功了一台  
\---------------------------------------------------------------------…----------------------------------------

# 现在配置另外一台，我现在在数据库：106.11.111.11 #

步骤和上面一样，只不过用户名，密码，端口等修改一下就可以了

进入MySQL  
执行同步SQL语句:

CHANGE MASTER TO MASTER_HOST='106.11.111.12', MASTER_USER='sla2', MASTER_PASSWORD='password123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=786;
    
    
    MASTER_HOST：106.11.111.12的 数据库地址
    MASTER_USER：106.11.111.12的 用户名
    MASTER_PASSWORD：106.11.111.12的 密码
    MASTER_PORT：106.11.111.12的 端口，如果是3306可以不写
    MASTER_LOG_FILE：106.11.111.12的 指定开始复制的binlog二进制文件。
    MASTER_LOG_POS：106.11.111.12的 指定（在master二进制文件中）要开始复制的位置

启动slave同步进程：
    start slave;
    
    查看slave状态：
    show slave status\G

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 3]  
当这个两个为yes就证明我们又成功了一台

\---------------------------------------------------------------------…----------------------------------------  
到此我们主主就已经成功了

# 5、开始验证 #

在数据库：106.11.111.11 里面对数据做CURD操作，数据库106.11.111.12会一起更新  
同样在数据库：106.11.111.12 里面对数据做CURD操作，数据库106.11.111.11也会一起更新  
这个地方我就不放截图了

# 6、杂谈 #

其实主主备份，和主从备份是一样的原理，基本上就是一样的，会了主从就自然会了主主。  
主主其实配置一面，另一面基本上就是重复操作了，无非修改一些关键性的数据  
  
  
  
然后就是注意用户名，密码，二进制文件名，要开始复制的位置，这些不要填写错误就没有什么问题  
  
  
  
有个问题需要特别注意，当我们开启了主主备份，需要注意数据库的函数调用，调用函数可能会出现问题，解决方法可参考：  
https://blog.csdn.net/lost\_in\_the\_woods/article/details/81589294  
https://blog.csdn.net/topasstem8/article/details/8216740  
  
  
  
  
  
好了，就这样吧，告辞！  
上一篇：[主从备份][Link 1]

下一篇：定时清除备份文件

提供一个群：807770565，欢迎各位进来尬聊  
![在这里插入图片描述][20190925011731616.png]

[https_blog.csdn.net_qq_38637558_article_details_101231395]: https://blog.csdn.net/qq_38637558/article/details/101231395
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190925003030163.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4,size_16,color_FFFFFF,t_70
[20190925003749778.png]: https://img-blog.csdnimg.cn/20190925003749778.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20190925004555987.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4,size_16,color_FFFFFF,t_70
[20190925005013746.png]: https://img-blog.csdnimg.cn/20190925005013746.png
[20190925010003975.png]: https://img-blog.csdnimg.cn/20190925010003975.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20190925010114282.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20190925010833310.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4NjM3NTU4,size_16,color_FFFFFF,t_70
[Link 1]: https://blog.csdn.net/qq_38637558/article/details/101229831
[20190925011731616.png]: https://img-blog.csdnimg.cn/20190925011731616.png