MySQL的主从搭建

技术 · 2019-04-17

架构图:
2023-04-27T08:55:02.png

异步复制

  1. 首先确保主从上都安装了相同版本的mysql。(附:mysql的安装)
  2. 在主库上,新增一个用户从库查询的账号,并赋权:

    create user 'repl'@'%' identified by '123456';
    GRANT ALL PRIVILEGES ON . TO 'repl'@'%' WITH GRANT OPTION;
    GRANT REPLICATION SLAVE ON . To 'repl'@'%';
    flush privileges;

  3. 修改主库my.cnf配置,开启bin-log,并设置server-id的值,重启

    log-bin=/acdata/mysql/log/mysql-bin.log
    server-id=1

  4. 修改从库my.cnf配置,设置server-id值,不可与主库重复

    server-id=2

  5. dump主库数据到从库恢复(此步骤操作时,建议停掉主库执行)

    ./mysql/bin/mysqldump -uroot -p --all-databases > /acdata/20211020.sql
    登录从库
    mysql> source /acdata/20211020.sql

  6. 主库flush,并获取偏移量

    mysql> flush logs;
    Query OK, 0 rows affected (0.73 sec)

mysql> show master status;
FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Set
mysql-bin.000031607

1 row in set (0.00 sec)

  1. 根据file和位置,开启从库的同步

    CHANGE MASTER TO MASTER_HOST='172.17.32.51',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456' ,MASTER_LOG_FILE='mysql-bin.000031',MASTER_LOG_POS=607;

  2. 开启同步,并查询同步状态

    mysql> start slave;
    Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

  1. 主库可以执行show processlist;查询同步进程

    mysql> show processlist;

| 2143030 | repl | 172.17.32.65:34576 | NULL | Binlog Dump | 1863 | Master has sent all binlog to slave; waiting for more updates | NULL

mysql
Theme Jasmine by Kent Liao