mysql 主从复制

 

 

 

 

 

 

一,前言

　　配置前确定服务器mysql高度一样

　　并启动数据库;

 

二, 修改主服务器配置

    
       [mysqld]
       log-bin=mysql-bin    //[必须]启用二进制日志
       server-id=1       //[必须]服务器唯一ID，默认是1，一般取IP最后一段

 三,修改从库服务器

     
       [mysqld]
       log-bin=mysql-bin   //[不是必须]启用二进制日志
       server-id=2      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

四,  更改了配置需要重启

五, 主服务器授权用户

　　  

　　  

　　grant replication slave on *.* to 'myimport'@'%' identified by '123456';

六, 从库启用

　　

　　  change master to master_host='10.0.0.51',master_user='myimport',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=322;

七, 

mysql>  show slave status\G*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.0.0.51
                  Master_User: myimport
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 322
               Relay_Log_File: web01-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Connecting
            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
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 322
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /application/mysql-5.6.34/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

八, 0测试

 　主库:

　
　　

　　

从库:

 　　

　　完成!!