MYSQL 主从数据库配置

前提：相同版本的两台MYSQL数据库

1、更改Master库配置项

[mysqld]
log-bin=mysql-bin
server-id       = 1  #主从库的server-id不同即可
binlog-do-db    = coreseek_test  #需要同步的数据库名
binlog-ignore-db=mysql #忽略掉的数据库
expire_logs_days=10 #日志留存天数

2、更改Slave库配置项

[mysqld]
log-bin=mysql-bin
server-id  = 130  #与主从库不同即可

修改完成后重启主从数据库。

3、登录Master数据库，授权Slave

GRANT REPLICATION SLAVE ON *.* to 'backup'@'192.168.136.130' identified by '123';

mysql> show master status;
+------------------+----------+---------------+------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+------------------+----------+---------------+------------------+
| mysql-bin.000012 |      107 | coreseek_test | mysql            |
+------------------+----------+---------------+------------------+
1 row in set (0.00 sec)

4、登录Slave数据库，配置Master库中的日志查找

change master to master_host='192.168.136.129',master_user='backup',master_password='123',master_log_file='mysql-bin.000012',master_log_pos=107; 

注意：master_log_file 和 master_log_post的值分别对应Master库 ‘show master status’ 中的file和Position的值。

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.129
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 107
               Relay_Log_File: Local-YSZ-relay-bin.000007
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0

注意：Slave_IO_Running 和 Slave_SQL_Running 都为yes 才成功

剩下的任务就是见证奇迹的时刻啦。。。