mysql主备

最新推荐文章于 2025-04-22 14:12:21 发布

redstarofsleep

最新推荐文章于 2025-04-22 14:12:21 发布

阅读量1.7k

点赞数 1

分类专栏：数据库文章标签： mysql 主备同步

本文链接：https://blog.youkuaiyun.com/redstarofsleep/article/details/53539226

版权

数据库专栏收录该内容

5 篇文章

订阅专栏

mysql的实时主备就是保持两个mysql的数据同步，主备及一主一备，对主数据库的操作也会实时反应到备数据库中。数据库的主备是实现读写分离、容灾备份、负载均衡等的基础。

mysql的主备是mysql本身就提供的功能，我们只需要对数据库进行配置就可以了。mysql的主备的原理也不是很复杂，就是主数据库（master）会把自己的每一次改动都记录到二进制日志 Binarylog 中。从数据库（Slave），会用master上的账号登陆到 master上，读取master的Binarylog, 写入到自己的中继日志 Relaylog，然后自己的sql线程会负责读取这个中继日志，并执行一遍。然后主数据库（master）上的更改就同步到从数据库（slave）上了。

下面记录下配置的过程（ubuntu14.04 mysql5.5）：

1. 在主库（master）上创建用户备份的账户，后面从库（slave）会用这个账户来读取binlog。

    grant replication slave on *.* to 'repl'@'%' identified by 'repl';

21/7/31更新：

8.0版本的mysql需要先创建用户，然后再分配权限

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>

语法说明如下：

1) <用户名>

指定创建用户账号，格式为 'user_name'@'host_name'。这里user_name是用户名，host_name为主机名，即用户连接 MySQL 时所在主机的名字。若在创建的过程中，只给出了账户的用户名，而没指定主机名，则主机名默认为“%”，表示一组主机。

grant replication slave on *.* to 'repl'@'%'

2. 开启主库（master）的binlog

编辑/etc/mysql/my.cnf，在[mysqld]节点下添加：

    server-id               = 1
    log_bin                 = /var/log/mysql/mysql-bin
    binlog_do_db            = sample
    auto-increment-increment= 2
    auto-increment-offset   = 1

这里几项的意思：

server-id：编号，只要保证唯一就可以了

log_bin：binlog的路径和文件名

binlog_do_db：对哪个数据库记录binlog，如果有多个数据库，这一条可以配置多遍

binlog-ignore-db ：需要忽略哪些数据库。我这边没有配置这一项，也可配置这一项忽略个别数据库，剩余的都记录，一般和上面的binlog_do_db取一个配就可以了

auto-increment-increment：主键的增涨值

auto-increment-offset：主键的起始值。

最后两项的作用是当有多个数据库做主库，都可以在上面插入数据时防止主键重复的。比如上面的配置，主键起始值为1，增长量为2，那么在这台数据库上，主键就都是奇数1，3，5，7，9.而另一台数据库就会配置主键起始值为2，增长量为2，这样它的主键就都是偶数：2，4，6，8。这时候它们之间同步数据的时候主键就不会冲突了。

到这边主库基本就配置完了，然后重启

    service mysql restart

3. 为了保持初始数据一致，需要将主库的数据导出来

导出之前可以先锁一下数据库

    FLUSH TABLES WITH READ LOCK;

然后导出

    mysqldump --master-data -u root -p sample > sample.sql

解锁

    unlock tables；

下面配置从库

4. 创建数据库，导入数据

    create database sample default charset utf8;
    mysql -u root -p sample < sample.sql

5. 编辑/etc/mysql/my.conf, 在[mysqld]节点下加入如下内容：

    server-id               = 2
    replicate-do-db         = sample
    relay_log               = /var/log/mysql/mysqld-relay-bin
    log-slave-updates       = ON

这里面几项的内容是和上面的差不多的

server-id：编号

replicat-do-db：需要同步的库

replicate-ignore-db：忽略的库

relay_log：relaylog的全路径

log-slave-update：中继日志执行之后，这些变化是否需要计入自己的binarylog。当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。就是双主互相备份，或者多主循环备份。
6. 开启同步

在主库上执行：

    mysql> show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000055 |  1528312 | sample       |                  |
    +------------------+----------+--------------+------------------+

在从库上执行：

    CHANGE MASTER TO 
       MASTER_HOST='192.***.***.***', 
       MASTER_USER='repl', 
       MASTER_PASSWORD=****', 
       MASTER_LOG_FILE='mysql-bin.000055', 
       MASTER_LOG_POS=1528312;

master_host：主库的ip

master_user：一开使在主库上建的那个用户

master_password：那个用户的密码

master_log_file：binlog文件，就是上面在主库上执行show master status显示的file

master_log_pos：文件位置，也是上面显示的数字。

7. 最后启动slave

start slave

8. 查看同步状态

在从库上执行“show slave status \G;"。一般来说，Slave_IO_Running: Yes，Slave_SQL_Running: Yes，这两个显示yes基本就是正常的。

好了，结束。

转载请注明出处 http://blog.youkuaiyun.com/redstarofsleep

更多内容请关注微信公众号：