首页 > 数据库 >Mysql主从复制

Mysql主从复制

时间：2022-09-01 21:33:08浏览次数：69  
标签：主从复制 slave root Mysql cnf MASTER mysql data

遇到的问题：

 # 查看slave状态show slave status\G显示IO线程为连接：
 Slave_IO_Running: NO
 解决思路：
1、检查change master to里用户名、密码、日志位置等信息； 如果不行就用reset slave all 清除所有同步信息；
2、如果以前该主机做过其他主机的冷备，那么有可能是/var/lib/mysql目录(二进制安装找二进制的目录)下的auto.cnf 里的UUID和其他主机一样了，删除该文件即可

# 复制错误解决方法
SET GLOBAL sql_slave_skip_counter = N   N是个数，意思是跳过N个错误
或者：
[mysqld]
slave_skip_errors=1007|ALL   #此处的1007是show slave status里可以查看到（Last_Errno: 1007），或者写ALL 跳过全部错误
1、mysqldump完全备份和还原
[root@rocky8 ~]#vim /etc/my.cnf.d/mysql-server.cnf   
[mysqld]
log-bin      #开启log-bin二进制日志
[root@rocky8 ~]#mysqldump -uroot -A -F --single-transaction --master-data=2 | gzip > /data/all-`date +%F`.sql.gz        

#-A备份所有数据库 -F备份前滚动日志 --single-transaction是开启单事务，--master-data=2 
#--master-data  阻止事务回滚 1是开启，用于主从，2是不开启，用于单机
[root@rocky8 ~]#scp /data/all-2022-08-31.sql.gz [email protected]:/data    ## 拷贝备份文件至其他地方

gzip -d all-2022-08-31.sql.gz       #解压备份文件
systemctl start mysqld.service      
mysql                                 
set sql_log_bin=off;                #临时关闭二进制日志功能
source /data/all-2022-08-31.sql     #导入数据库文件
set sql_log_bin=on;                 #开启二进制日志功能
2、利用二进制日志，还原数据库最新状态
[root@rocky8 ~]# vim /etc/my.cnf.d/mysql-server.cnf   
[mysqld]
log_bin=/data/mysql/mysqllog   #开启log-bin二进制日志
[root@rocky8 ~]# mysqldump -uroot -A -F --single-transaction --default-character-set=utf8mb4 --master-data=2 --flush-privileges  > /data/mysql_all.sql           #完全备份
[root@rocky8 ~]# mysql
mysql> show master logs;        #查看现在二进制日志文件和大小
mysqllog.000001 |       156 
mysql> use hellodb
mysql> insert students(name,age)values('a',20);     #新增条目
mysql> insert teachers (name,age)value('b',20);
mysql> drop table teachers;                          #删除teachers表
mysql> insert students (name,age)values('c',20);
mysql> exit
[root@rocky8 data]# mysqlbinlog mysql/mysqllog.000001 > inc.sql      #把二进制文件导出至inc.sql文件    
[root@rocky8 data]#sed -i '/^DROP TABLE/d' inc.sql                   #删除文件内删除动作
[root@rocky8 ~]# mysql
mysql> set sql_log_bin=0;            #临时停止二进制日志
mysql> source /data/mysql_all.sql    # 导入完全备份
mysql> source /data/inc.sql          #导入二进制日志文件的动作
mysql> select * from teachers;       #查看是否还原
mysql> set sql_log_bin=1;            #开启二进制日志或者退出重进
3、主从复制

image-20220831220137748

# master：
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log_bin=/data/mysql/mysqllog
[root@master ~]#systemctl restart mysqld.service
[root@master ~]#mysql
mysql> show master logs;         #查看二进制文件和位置
+-----------------+-----------+-----------+
| Log_name        | File_size | Encrypted |
+-----------------+-----------+-----------+
| mysqllog.000001 |      2494 | No        |
| mysqllog.000002 |       156 | No        |
+-----------------+-----------+-----------+

mysql> create user repluser@'10.0.0.%' identified by '123456';   #创建复制用户
mysql> grant replication slave on *.* to repluser@'10.0.0.%';    #授权复制用户


# slave：
[root@slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
log_bin
[root@slave8 ~]#systemctl restart mysqld.service
[root@slave8 ~]#mysql    
mysql> CHANGE MASTER TO            #通过help change maste to 可以查询到，稍微改下就能用
    ->   MASTER_HOST='10.0.0.8',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mysqllog.000002',
    ->   MASTER_LOG_POS=156,
    ->   MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 10 warnings (0.03 sec)

mysql> show slave status;
mysql> start slave;      #开启slave 即可

mysql> show slave status;
		...
  			Slave_IO_Running: Yes       #显示yes即连通
            Slave_SQL_Running: Yes       #显示yes即运行
         ...
          Seconds_Behind_Master: 0
          ....
          
  mysql> show processlist
4、新增一台从服务器实现一主二从

image-20220831225444232

#master：
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /data/all1.sql#完全备份
[root@master ~]#scp /data/all1.sql [email protected]:  #复制完全备份到新从服务器节点

#新slave1：
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
log_bin
[root@slave1 ~]#systemctl start mysqld.service
[root@slave1 ~]#vim all1.sql
CHANGE MASTER TO                   #原来有 ，回车换行，插入下边内容
     MASTER_HOST='10.0.0.8',        #增加
     MASTER_USER='repluser',        #增加
     MASTER_PASSWORD='123456',      #增加
     MASTER_PORT=3306,              #增加
MASTER_LOG_FILE='mysqllog.000003', MASTER_LOG_POS=156;  #原来有

[root@slave1 ~]#mysql
mysql> source /root/all1.sql    #导入主的完全备份
mysql> start slave;
5、级联复制

image-20220831230016750

# master:
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=8
log_bin=/data/mysql/mysqllog            #二进制日志文件自定义到单独的分区
[root@master ~]#systemctl restart mysqld.service
[root@master ~]#mysql
# 授权和上边主从复制一样
mysql> show grants for repluser@'10.0.0.%';     #查询授权用户信息
+---------------------------------------------------------+
| Grants for [email protected].%                            |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `repluser`@`10.0.0.%` |
+---------------------------------------------------------+
1 row in set (0.00 sec)
[root@master ~]#mysqldump -uroot -A -F --single-transaction --master-data=1 > /data/all2.sql
[root@master ~]#scp /data/all2.sql [email protected]:/data
[root@master ~]#scp /data/all2.sql [email protected]:/data

# slave：
[root@slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
log-bin
log_slave_updates    #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加
read_only=on
[root@slave ~]#systemctl restart mysqld.service
[root@slave ~]#vim /data/all2.sql
CHANGE MASTER TO
     MASTER_HOST='10.0.0.8',
     MASTER_USER='repluser',
     MASTER_PASSWORD='123456',
     MASTER_PORT=3306,
MASTER_LOG_FILE='mysqllog.000005', MASTER_LOG_POS=156;

[root@slave ~]#mysql
mysql> set sql_log_bin=0;
mysql> source /data/all2.sql
mysql> show master logs;         #记录二进制日志，下一节点
mysql> set sql_log_bin=1;
mysql> start slave;


#slave-slave：
[root@slave-slave ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
log_bin
read_only
[root@slave-slave ~]#systemctl restart mysqld.service 

[root@slave-slave ~]#vim /data/all2.sql 
CHANGE MASTER TO 
     MASTER_HOST='10.0.0.18',    #中间节点的ip
     MASTER_USER='repluser',
     MASTER_PASSWORD='123456',
     MASTER_PORT=3306,
MASTER_LOG_FILE='mysqllog.000005', MASTER_LOG_POS=156;
[root@slave-slave ~]#mysql
mysql> source /data/all2.sql
mysql> start slave;
6、主主复制

image-20220901111826485

  • 优点：两个节点，都可以更新数据，并且互为主从

  • 缺点： 容易数据不一致

​ 两个节点同时创建数据库 ，会导致复制冲突

# maste1：
[root@master1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin=/data/mysql/mysql-bin          #记得建立目录并更改属主属组为mysql
auto_increment_offset=1                #开始点
auto_increment_increment=2             #增长幅度
[root@rocky8 ~]# systemctl restart mysqld.service 
[root@master1 ~]# mysql
mysql> show master logs;
 mysql-bin.000043 |       156 | No 
 mysql> create user repluser@'10.0.0.%' identified by '123456';
 mysql> grant replication slave on *.* to repluser@'10.0.0.%';
 
 # master2:
 [root@rocky8 ~]# vim /etc/my.cnf.d/mysql-server.cnf
 [mysqld]
server-id=18
log-bin=/data/mysql/mylog       #记得建立目录并更改属主属组为mysql
auto_increment_offset=2         #开始点
auto_increment_increment=2      #增长幅度

mysql> show master logs;
 mylog.000002 |       156 | No  
 
 	  CHANGE MASTER TO            
      MASTER_HOST='10.0.0.8',
      MASTER_USER='repluser',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='mysql-bin.000043',
      MASTER_LOG_POS=156,
      MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show master logs;
+--------------+-----------+-----------+
| Log_name     | File_size | Encrypted |
+--------------+-----------+-----------+
| mylog.000001 |       179 | No        |
| mylog.000002 |       705 | No        |
+--------------+-----------+-----------+



## master1 ：
 	  CHANGE MASTER TO            
      MASTER_HOST='10.0.0.18',
      MASTER_USER='repluser',
      MASTER_PASSWORD='123456',
      MASTER_PORT=3306,
      MASTER_LOG_FILE='mylog.000002',
      MASTER_LOG_POS=705,
      MASTER_CONNECT_RETRY=10;
mysql> start slave;
7、分库备份
[root@rocky8 ~]# for i in `mysql -e 'show  databases' |egrep -vi '^(database|information_schema|performance_schema|sys)$'`
> do
> mysqldump -B $i | gzip > /data/$i.sql.gz
> done
8、脚本
8-1、冷备+还原
#!/bin/bash
#
#********************************************************************
#Author:            wangdayu
#QQ:                965507991
#Date:              2022-09-01
#FileName:          mysql_autoback.sh
#URL:               https://blog.51cto.com/dayu
#Description:       The test script
#Copyright (C):     2022 All rights reserved
#********************************************************************
. /etc/init.d/functions
read -p "请输入mysql备份到那台主机的IP地址（并且确保不需要输入ssh密码）：" IP
    if [[ $IP =~ ^(([1-9]|[1-9][0-9]{1,2}|2[0-4][0-9]|25[1-5]|0).){3}([1-9]|[1-9][0-9]{1,2}|2[0-4][0-9]|25[1-5]|0)$ ]];then
        echo "开始备份"
    else
        echo "IP地址输入错误"
    fi
#IP="10.0.0.28"
mysqldir="/var/lib/mysql"
bindir="/data/mysql"
conffile="/etc/my.cnf.d/mysql-server.cnf"
Allback="mysql_`date +%F_%H-%M`"
Binback="mysql_bin_`date +%F_%H-%M`"
lockfile=/data/mylock
serverIP=`hostname -I | awk -F"." '{print $4}'`
sqlgzip=`ls |egrep -o 'mysql_[0-9].*.tar.gz'`
bingzip=`ls |egrep -o 'mysql_bin.*.tar.gz'`

lock(){
    if [ -e ${lockfile} ];then
       echo "另一个程序正在备份，退出"
       exit 10
    else
       touch ${lockfile}
    fi   
}

unlock(){
    if [ -e ${lockfile} ];then
        rm -rf ${lockfile} &> /dev/null
    else
        exit 11
    fi 
}

back(){
    lock
    systemctl stop mysqld &> /dev/null && action "mysql已停止" || { action "服务停止失败，退出备份" false ; exit 1; }  
    tar zcf /data/$Allback.tar.gz $mysqldir &> /dev/null && action "mysql已备份至/data下" || { action "备份失败，退出备份" false ;exit 2; }
    tar zcf /data/$Binback.tar.gz $bindir &> /dev/null && action "mysql二进制日志已备份至/data下" || { action "二进制日志备份失败，退出备份" false ;exit 3; }
    rsync /data/$Allback.tar.gz $IP: &> /dev/null && action "备份文件复制至${IP}家目录" || { action "复制${IP}失败" false ;exit 4; }
    rsync /data/$Binback.tar.gz  $IP: &> /dev/null && action "二进制日志备份文件复制至${IP}家目录" || { action "二进制日志复制${IP}失败" false ;exit 5; }
    rsync ${conffile}  $IP: &> /dev/null && action "配置文件复制至${IP}家目录" || { action "配置文件复制${IP}失败" false ;exit 5; }
    unlock
    echo -e "启动mysql服务\n"; systemctl start mysqld &> /dev/null && action "mysql已启动" || { action "服务启动失败，请检查" false ; exit 6; }
}

restore(){
    systemctl stop mysqld  &> /dev/null && action "mysql已停止" || { action "服务停止失败，退出还原" false ; exit 1; }
    lock
    cd
    rm -rf /var/lib/mysql/*  &> /dev/null
    [ -e ${bindir} ] || mkdir -p ${bindir}
    chown -R mysql.mysql ${bindir}
    tar xf ${sqlgzip}  &> /dev/null && action "${sqlgzip}解压完成" || { action "解压失败，退出还原" false ; exit 1; }
    mv var/lib/mysql/*  ${mysqldir}  &> /dev/null && action "mysql备份已复制至${mysqldir}下" || { action "复制失败，退出还原" false ;exit 2; }  
    rm -rf var
    tar xf ${bingzip} &> /dev/null 
    mv data/mysql/* ${bindir}  &> /dev/null && action "mysql二进制日志备份已复制至${bindir}下" || { action "日志复制失败，退出还原" false ;exit 2; }
    rm -rf ${mysqldir}/auto.cnf  &> /dev/null && action "auto.cnf已删除" ||  action "删除失败，有可能启动失败，请检查" false  
    mv -f  mysql-server.cnf /etc/my.cnf.d/mysql-server.cnf  
    sed -i "s/server-id=8/server-id=${serverIP}/" /etc/my.cnf.d/mysql-server.cnf &> /dev/null && action "server-id已更改" ||  action "更改失败，有可能启动失败，请检查" false
    unlock
    systemctl start mysqld  &&  action "mysql服务已启动，恭喜还原成功" || action "服务启动失败，请检查配置文件、二进制日志、auto.cnf文件等" false
}

PS3="请输入选择：" 
select menu in 冷备份 恢复 退出;do
    case $menu in 
        冷备份)
            back
            ;;
        恢复)
            restore
            ;;
        退出)
            exit
            ;;
        *)
            echo "输入错误"
            ;;
    esac
done
8-2、mysqldump完全备份
#!/bin/bash
#
#********************************************************************
#Author:            wangdayu
#QQ:                965507991
#Date:              2022-09-01
#FileName:          mysqldump.sh
#URL:               https://blog.51cto.com/dayu
#Description:       The test script
#Copyright (C):     2022 All rights reserved
#********************************************************************
lockfile="/data/mysqllock"
user="root"
password="123456"
backdir="/data/all_`date +%F_%H-%M`.sql"


lock(){
    if [ -e $lockfile ];then
        echo "有另一个程序在备份，退出"
        exit 1
    else
        touch $lockfile
    fi
}
unlock(){
    if [ -e $lockfile ];then
        rm -rf ${lockfile}
    else
        exit 2
    fi
}
dump(){
    lock
   mysqldump -u${user} -p${password} -A -F --single-transaction --master-data=2 | gzip > ${backdir}.gz  && echo "已备份至${backdir}"
    unlock
}  
dump
8-3、分库备份
#!/bin/bash
#
#********************************************************************
#Author:            wangdayu
#QQ:                965507991
#Date:              2022-09-01
#FileName:          mysqldump.sh
#URL:               https://blog.51cto.com/dayu
#Description:       The test script
#Copyright (C):     2022 All rights reserved
#********************************************************************
lockfile="/data/mysqllock"
user="root"
password="123456"
backdir="/data"


lock(){
    if [ -e $lockfile ];then
        echo "有另一个程序在备份，退出"
        exit 1
    else
        touch $lockfile
    fi
}
unlock(){
    if [ -e $lockfile ];then
        rm -rf ${lockfile}
    else
        exit 2
    fi
}
dump(){
    lock
    for i in `mysql -uroot -p"${password}" -e 'show databases' | egrep -iv '^(database|information_schema|performance_schema|sys)$'`;do
    mysqldump -uroot -p"${password}" -B $i | gzip > ${backdir}/${i}_`date +%F`.gz
    scp ${backdir}/${i}_`date +%F`.gz [email protected]:
    done
    unlock
}  
dump

标签：主从复制,slave,root,Mysql,cnf,MASTER,mysql,data
From： https://www.cnblogs.com/wdy001/p/16647901.html

相关文章

  • 3.MySQL常用知识
    1，基础查询//1.groupby+聚合函数+HAVING条件过滤SELECTSEX,AVG(AGE)FROM`user`GROUPBYSEXHAVINGSEX='女'2，DCL语句1，创建用户，当前主机访问CREATEuser'ser......
  • Mysql （需5.6以上版本）Slave 延迟复制配置
    Mysql（需5.6以上版本）Slave延迟复制配置通过设置Slave上的MASTERTOMASTER_DELAY参数实现：CHANGEMASTERTOMASTER_DELAY=N；N为多少秒，该语句设置从数据库延时N秒后，再与......
  • MySQL Explain执行计划key_len详解(特意针对date和datetime详细测试说明)
    MySQLExplain执行计划key_len详解(特意针对date和datetime详细测试说明)我们在使用Explain查看SQL执行计划时，其中有一列为key_kenkey_len表示使用的索引长度，那么key_len......
  • MySQL5.7 datetime设置默认为当前时间
    DROPTABLEIFEXISTS`strategy`;CREATETABLE`strategy`(`id`bigint(11)NOTNULLAUTO_INCREMENTCOMMENT'自增ID',`create_time`datetime(3)DEFAULTCURR......
  • 连接MySQL出现Host is not allowed to connect to this MySQL server 解决方法
    前言关于这个问题在网上看过一些教程方法，因为本人是更倾向于细致化的比较看得懂，所以在操作的时候就一起记录一下操作步骤了提示：以下是本篇文章正文内容，下面案例可供参考......
  • MySql Host is blocked because of many connection errors; unblock with 'mysqladmi
     原因是：同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞 解决方法：/usr/bin/mysqladminflush-hosts-h172.......
  • MySQL常用日期函数
    目录1、日期函数1.1、CURDATE()1.2、CURRENT_DATE()1.3、CURRENT_DATE1.4、将日期转为19900101格式2、时间函数2.1、CURTIME()2.2、CURRENT_TIME()2.3、CURRENT_TIME3、日......
  • mysql 储存过程 如何使用递归循环来实现sql数据恢复
    CREATEPROCEDURE`relation_update`(inePIDvarchar(100),indeptidint)BEGINDECLAREtuivarchar(100);declarectint;declareiint;DECLAREcur1CURSORFORselect......
  • MYSQL数据库使用
    1.创建数据库createdatabase库名；2.确认数据库信息（所有的库名都显现）showdatabases; -->可查看是否创建成功了。3.指定或切换数据库  use数据库名4.显示当前使......
  • Mysql基本操作
    mysql数据库管理软件底层还是文件操作不用IO流使用sql语言数据库database表table列column数据datacmd控制台里操作-uroot-pshowdatabases;展示所有数据库；created......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99