Mysql学习MySQL5.6 数据库主从同步安装与配置详解（Master/Slave）

MYSQL数据库
log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=ufind_db 

MYSQL数据库
service mysqld restart

MYSQL数据库
 >GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';
 >GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';
>FLUSH PRIVILEGES

MYSQL数据库
mysql> show master status;

MYSQL数据库
mysql> show master status;
+------------------+----------+--------------+----------------------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000004 |  28125 | ufind_db   | information_schema,cluster,mysql |          |
+------------------+----------+--------------+----------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> 

MYSQL数据库
log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=ufind_db
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

MYSQL数据库
master-host=192.168.1.1 #Master的主机IP
master-user=root
master-password=mysql password #Master的MySQL暗码

MYSQL数据库
[root@VM_128_194_centos bin]# service mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysqldb/VM_128_194_centos.pid).
[root@VM_128_194_centos bin]# 

MYSQL数据库
2016-05-06 13:12:04 13345 [Note] InnoDB: Waiting for purge to start
2016-05-06 13:12:04 13345 [Note] InnoDB: 5.6.27 started; log sequence number 2850211
2016-05-06 13:12:04 13345 [ERROR] /data/home/server/mysql-5.6.27/bin/mysqld: unknown variable 'master-host=192.168.1.1'
2016-05-06 13:12:04 13345 [ERROR] Aborting

MYSQL数据库
mysql> stop slave; #关闭Slave
mysql> change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000004', master_log_pos=28125;
mysql> start slave; #开启Slave

MYSQL数据库
mysql> show slave status \G;
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.167.1.1
         Master_User: root
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000004
     Read_Master_Log_Pos: 28125
        Relay_Log_File: VM_128_194_centos-relay-bin.000004
        Relay_Log_Pos: 26111
    Relay_Master_Log_File: mysql-bin.000004
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
       Replicate_Do_DB: ufind_db
     Replicate_Ignore_DB: mysql
      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: 28125
       Relay_Log_Space: 26296
       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: 0
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: 2
         Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd
       Master_Info_File: /data/mysqldb/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)
ERROR: 
No query specified
mysql> 

MYSQL数据库
>stop slave
>change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000005', master_log_pos=120;
>start slave

MYSQL数据库
mysql> show slave status\G;
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.1.1
         Master_User: root
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: mysql-bin.000005
     Read_Master_Log_Pos: 1422
        Relay_Log_File: VM_128_194_centos-relay-bin.000004
        Relay_Log_Pos: 283
    Relay_Master_Log_File: mysql-bin.000005
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
       Replicate_Do_DB: ufind_db,test
     Replicate_Ignore_DB: mysql
      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: 1422
       Relay_Log_Space: 468
       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: 0
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: 2
         Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd
       Master_Info_File: /data/mysqldb/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)
ERROR: 
No query specified
mysql>