温馨提示×

mysql怎么实现热备份

九三
1224
2021-02-12 16:33:43
栏目: 云计算
亿速云mysql数据库,读写分离,安全稳定,弹性扩容,低至0.3元/天!! 点击查看>>

mysql怎么实现热备份

mysql热备份主要分为主从备份和主主备份两种,下面介绍mysql实现主从备份的方法

1.主服务器配置

[mysqld]

#主从备份

log-bin=mysql-bin

binlog_format=mixed

server-id=1

read-only=0

binlog-do-db=mydb

binlog-ignore-db=mysql

binlog-ignore-db=test

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

#双主备份

#auto-increment-increment=10

#auto-increment-offset=1

2.从服务器配置

[mysqld]

#主从备份

#log-bin=mysql-bin

#binlog_format=mixed

server-id=2

replicate-do-db=mydb

replicate-ignore-db=mysql

replicate-ignore-db=test

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

replicate-wild-ignore-table=mydb.000%

replicate-wild-ignore-table=mydb.tmp%

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

replicate-wild-ignore-table=performance_schema.%

relay_log=mysql-relay-bin

log-slave-updates=ON

3.在mysql中创建一个备份用户

grant replication slave on *.* to 'backup'@'192.168.0.42' identified by '123456';

4.在从服务器中开启主从备份

mysql> CHANGE MASTER TO

    MASTER_HOST='192.168.0.41',

    MASTER_USER='backup',

    MASTER_PASSWORD='123456',

    MASTER_LOG_FILE='mysql-bin.000001',

    MASTER_LOG_POS=106;

5.主从备份开启后,重启mysql服务即可

sudo service mysql restart

亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>

推荐阅读:mysql热备份怎么实现

0