mysql-主从同步搭建

mysql-主从同步搭建

环境

系统	IP	软件	备注
CentOS 7.5 64-主	192.168.11.140	mysql5.7
CentOS 7.5 64-备	192.168.11.141	mysql5.7

主库操作步骤

1. 参考此链接：部署MySQL5.7.20

2. /etc/my.cnf配置文件修改

   ]# vim /etc/my.cnf
   [mysqld]
   basedir=/usr/local/mysql57
   datadir=/usr/local/mysql57/data
   socket=/tmp/mysql.sock
   pid-file=/usr/local/mysql57/data/mysqld.pid
   log-bin=master-bin
   server_id=1
   
   [mysql]
   socket=/tmp/mysql.sock
   port=3306
   
   [mysqld_safe]
   log-error=/usr/local/mysql57/data/mysql.log
   

3. 创建同步用户

   mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.11.141' IDENTIFIED BY 'newpasswd';
   mysql> FLUSH PRIVILEGES;
   

4. 重启数据库

   ]# service mysqld restart
   

5. 查看主库的file和position

   mysql> show master status\G
   *************************** 1. row ***************************
                File: master-bin.000003
            Position: 154
        Binlog_Do_DB: 
    Binlog_Ignore_DB: 
   Executed_Gtid_Set: 
   1 row in set (0.00 sec)
   

从库操作步骤

1. 参考此链接：部署MySQL5.7.20

2. /etc/my.cnf配置文件修改

   ]# vim /etc/my.cnf
   [mysqld]
   basedir=/usr/local/mysql57
   datadir=/usr/local/mysql57/data
   socket=/tmp/mysql.sock
   pid-file=/usr/local/mysql57/data/mysqld.pid
   log-bin=relay-bin
   server_id=3
   
   [mysql]
   socket=/tmp/mysql.sock
   port=3306
   
   [mysqld_safe]
   log-error=/usr/local/mysql57/data/mysql.log
   

3. 使用有复制权限的账号连接主服务器

   mysql> change master to master_host='192.168.11.140',master_user='repluser',master_password='newpasswd',master_log_file='master-bin.000003',master_log_pos=154,master_port=3306;
   

4. 重启查看从库状态

   ]# service mysqld restart
   ]# mysql -uroot -p
   mysql> show slave status\G
           *************************** 1. row ***************************
                          Slave_IO_State: Waiting for master to send event
                             Master_Host: 192.168.11.140
                             Master_User: repluser
                             Master_Port: 3306
                           Connect_Retry: 60
                         Master_Log_File: master-bin.000003
                     Read_Master_Log_Pos: 154
                          Relay_Log_File: relay-bin.000003
                           Relay_Log_Pos: 153
                   Relay_Master_Log_File: relay-bin.000003
                        Slave_IO_Running: Yes
                       Slave_SQL_Running: Yes
                         Replicate_Do_DB:
                     Replicate_Ignore_DB:
   ...省略
   

   在状态中Slave_IO_Running和Slave_SQL_Running都为Yes，则成功

同步验证

1. 在主库创建新库和新表
2. 在从库查看是否有相同库和表
3. 有则表示成功，无则表示失败