MySQL主从备份

本文介绍了MySQL主从同步机制,依赖于二进制日志记录数据库变动。主服务器记录所有操作,从服务器通过接收并执行日志保持数据一致。详细步骤包括:主服务器设置(修改配置文件、创建slave账号)、从服务器设置(配置主服务器信息、启动slave服务)及相关参数介绍。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

主从同步机制

Mysql服务器之间的主从同步是基于二进制日志机制,主服务器使用二进制日志来记录数据库的变动情况,从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。

在使用二进制日志时,主服务器的所有操作都会被记录下来,然后从服务器会接收到该日志的一个副本。从服务器可以指定执行该日志中的哪一类事件(譬如只插入数据或者只更新数据),默认会执行日志中的所有语句。

每一个从服务器会记录关于二进制日志的信息:文件名和已经处理过的语句,这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分,并且从服务器可以随时连接或者中断和服务器的连接。

主服务器和每一个从服务器都必须配置一个唯一的ID号(在my.cnf文件的[mysqld]模块下有一个server-id配置项),另外,每一个从服务器还需要通过CHANGE MASTER TO语句来配置它要连接的主服务器的ip地址,日志文件名称和该日志里面的位置(这些信息存储在主服务器的数据库里)

1. 主服务器设置

1. 修改mysqld.cnf

在/etc/mysql/mysql.conf.d/mysqld.cnf的[mysqld]下新增字段

log-bin=mysql-bin
server-id=1
binlog-do-db=test

binlog-do-db是需要备份的数据库
然后重启mysql服务

2. 新增slave账号
create database test;
create user 'slave'@'%' identified by 'slave123';
grant replication slave on test.* to 'slave'@'%';
flush privileges;

然后查看master的status

show master status;

在这里插入图片描述

3. 从服务器设置

1. 修改mysqld.cnf

在[mysqld]下新增字段

server-id=2

重启mysql服务

2. 增加主服务器设置

使用root账号登录mysql,输入如下命令

create database test;
change master to master_host='121.248.50.86',master_user='slave',master_password='slave123',master_log_file='mysql-bin.000003',master_log_pos=749;

master_host是主服务器ip
master_user 是刚刚在主服务器上创建的slave账号
master_log_file,master_log_pos是主服务器中show master status;获取到的信息

3. 启动salve服务

在mysql中输入命令

start slave;
show slave status \G;

在这里插入图片描述
说明主从备份成功了,可以在master数据库上操作,slave数据库也会有相应的改变

4. 相关参数介绍
  1. –master-retry-count=count
PropertyValue
Command-Line Format–master-retry-count=#
Deprecated5.6.1
TypeInteger
Default Value86400
Minimum Value0
Maximum Value (64-bit platforms)18446744073709551615
Maximum Value (32-bit platforms)4294967295

The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by the MASTER_CONNECT_RETRY option of the CHANGE MASTER TO statement (default 60). Reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400. A value of 0 means “infinite”; the slave attempts to connect forever.

This option is deprecated as of MySQL 5.6.1 and will be removed in a future MySQL release. Applications should be updated to use the MASTER_RETRY_COUNT option of the CHANGE MASTER TO statement instead.

  1. –replicate-do-db=db_name
    The effects of this option depend on whether statement-based or row-based replication is in use.
    Statement-based replication(use为主,如果use B,而salve是复制A,则该条语句不起作用). Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo=‘bar’ while a different database (or no database) is selected.
    An example of what does not work as you might expect when using statement-based replication: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

    USE prices;
    UPDATE sales.january SET amount=amount+1000;
    

    Row-based replication(以SQL语句为主,use没什么作用). Tells the slave SQL thread to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. Suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master

    USE prices;
    UPDATE sales.february SET amount=amount+100;
    

    The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued.
    If you need cross-database updates to work, use --replicate-wild-do-table=db_name.%

  2. –replicate-ignore-db=db_name
    这个与 --replicate-do-db,忽略db

  3. –slave-net-timeout=seconds

    opertyValue
    mmand-Line Format–slave-net-timeout=#
    stem Variableslave_net_timeout
    opeGlobal
    namicYes
    peInteger
    fault Value3600
    nimum Value1

    The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. The default is 3600 seconds (one hour).

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值