MySQL 主从复制

MySQL 主从复制

大致步骤

注意:改完配置需要重启mysql

  • 主从服务器都需要配置 server_id (/etc/mysql/my.cnf [mysqld] )

    1
    2
    3
    4
    5
    > vi /etc/mysql/my.cnf

    [mysqld]
    log-bin=mysql-bin
    server_id=1 #局域网唯一
  • 主服务器创建用于复制的mysql用户,比如 slave1

    1
    2
    3
    4
    5
    6
    7
    mysql -u<username> -p<password>
    mysql>

    # mysql8:
    mysql> create user 'slave1'@'%' identified by '123456';

    # create user '<username>'@'<hostname>' identified by '<password>'
  • 主服务器将 REPLICATION 权限授予复制数据的用户 slave1

    1
    2
    3
    4
    5
    6
    mysql -u<username> -p<password>
    mysql>

    mysql> grant replication slave on *.* to 'slave1'@'%'

    # grant replication slave on <database>.<table> to '<username>'@'<hostname>'
  • 从服务器设置 master

    1
    2
    3
    4
    5
    6
    7
    8
    9
    mysql> change master to 
    master_host='<master hostname | ip>',
    master_port=<master port | 3306>,
    master_user='<slave user name in master>',
    master_password='<slave user password in master>',
    master_log_file='<binlog filename>',
    master_log_pos=<binlog position>,
    master_connect_retry=30;

  • 从服务器启动 slave

    1
    mysql> start slave;