mysql5.6主从配置

操作系统 ：CentOS 6.7 
数据库版本：MySQL 5.6.19
主机A：192.168.0.104 （Master）
主机B：192.168.0.102 （Slave）

前提：

源码编译安装的mysql数据库，保证数据库能跑通

主库操作：

MASTER主服务器文件配置：

vi /etc/my.cnf
# log_bin
log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=test_master

然后wq进行修改保存

service mysqld restart #重启服务

#1.登陆
mysql  -uroot -p
#2.创建用户并分配权限（只有从服务器复制权限）
GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'192.168.0.%' IDENTIFIED BY '123456'
#创建了mysync用户 密码123456 196.168.0.% 表示只能在ip为192.168.0.0-255 进行远程登陆
#3.获取主库上binlog的位置
show master status
+------------------+----------+--------------+----------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000001 |      120 | test_master  | information_schema,cluster,mysql |                   |
+------------------+----------+--------------+----------------------------------+-------------------+
#记住显示的 File | Position 的值
#我此次的 file名是:mysql-bin.000001,Position值为:120

导出主服务器Mysql库中的数据

 1.    导出主服务器上test数据库的数据

mysqldump -uroot -p test_master>/test_master.txt

#输入密码

 2.     将导出的数据库文件传递到从库所在的服务器,并导入从库的数据库

scp /test_master.txt root@192.168.0.102:/
#将主库的数据文件传到从库服务器上

从库设置

设置从库对应的主库配置

vi /etc/my.cnf
log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=test_master
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

重启mysql 

service mysqld restart

mysql -uroot -p 

#停止slave
mysql> stop slave;

mysql> change master to master_host='192.168.0.104',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=256;

#开启slave
mysql>start slave;

#查看配置的信息：
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.104
                  Master_User: mysync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 256
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test_master
          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: 256
              Relay_Log_Space: 460
              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: 322142ff-4904-11e7-b451-000c29fb678a
             Master_Info_File: /usr/local/mysql/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)

ERROR: 
No query specified

mysql> 

 如果Slave_IO_Running 和 Slave_SQL_Running都为 Yes，而且没有出现error的字样，并验证Master_%等相关配置是否正确

然后就可以进行测试了。