首页 / Mysql

mysql主从数据库配置

张绪航 2020-06-09 15:32:37 2931 浏览 Mysql Mysql Linux
192.168.188.254(主)

192.168.188.253(从)

一、主数据库配置

    #修改主数据库mysql配置文件 vi /etc/my.cnf

[mysqld]
  log-bin=mysql-bin      #启用二进制日志
  server-id=1            #唯一的ServerID
  binlog-do-db=site      #要同步的数据库
  binlog-ignore-db=mysql #要忽略的数据库
    #mysql命令创建账号master
create user master;
GRANT REPLICATION SLAVE ON *.* TO 'master'@'192.168.188.253' IDENTIFIED BY 'password';
flush privileges;
    #重启mysql服务

        systemctl restart mysqld

    #mysql命令查看主数据库状态

        show master status;

二、从数据库配置

    #修改从据库mysql配置文件 vi /etc/my.cnf

log-bin=mysql-bin         #启用二进制日志
server-id=2               #唯一的ServerID
replicate-do-db=site      #要同步的数据库
binlog-ignore-db=mysql    #要忽略的数据库
relay-log=mysqld-relay-bin
     #mysql命令配置连接主服务器的信息
change master to master_host='192.168.188.254',master_user='master',master_password='password',master_log_file='mysql-bin.000001', master_log_pos=120;
flush privileges;   
start slave;
    #重启mysql服务

        systemctl restart mysqld

    #mysql命令查看从服务器状态

        show slave status\G;

    #如果Slave_IO_Running: No

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
reset slave;
start slave;
    #如果Slave_SQL_Running: No
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

     #如果报错Slave failed to initialize relay log info structure from the repository

        reset slave;
        start slave;