mysql主从复制

一：master配置

1，创建一个用户，让slave用来登录

 mysql>create user repl; //创建新用户repl

2，给新用户授权，必须具有REPLICATION SLAVE权限，其他权限没必要添加，设置登录密码为‘123456’，指定可以用来连接的ip（%代表任意ip）

 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123456';

3，修改master的mysql配置文件my.cnf
在[mysqld]下添加下面几行

1 server-id=1   //给数据库服务的唯一标识
2 log-bin=master-bin //启动二进制日志log-bin
3 log-bin-index=master-bin.index

4，查看master日志

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 500 | | |
+-------------------+----------+--------------+------------------+

master-bin.000002和my.cnf中的log-bin=master-bin有关，记清楚，下面不要写错

5，重启MySQL服务

# service mysqld restart

二：slave配置

1，找到MySQL安装文件夹修改my.cnf文件，添加下面几句：

port=3306
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin 

2，重启slave的mysql
3，连接master

mysql> change master to master_host='1*2.92.**.***',//master的主机地址 
master_port=3306,//master端口号
master_user='repl',//连接数据库的用户名
master_password='123456',//连接数据库的密码
master_log_file='master-bin.000004',//读取master的二进制文件，和上面SHOW MASTER STATUS;保持一致
master_log_pos=0;

4，启动slave：start slave;

三：配置都完成了，在master数据库中创建库数据表，进行测试
四：没用成功时
1，查看slave的状态，在slave服务器中

mysql> SHOW SLAVE STATUS\G

出现：

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 1*2.92.**.***
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000012
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000012
             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: 4
              Relay_Log_Space: 107
              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: 1045
                Last_IO_Error: error connecting to master 'repl@1*2.92.**.***:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

2，出现错误，显示连接失败
（1）在master中查看权限：show grants for 'repl'@'%';

+-----------------------------------------------------------------------------------------------------+
| Grants for repl@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '*E74858DB86EBA20BC33D0AECAE8A8108C56B17FA' |
+-----------------------------------------------------------------------------------------------------+

没有slave的权限，添加权限：GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

（2）连接测试成功
3，在master添加数据库和表进行测试，不成功，还查看slave:SHOW SLAVE STATUS\G

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 1*2.92.**.***
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave-relay-bin.000006
                Relay_Log_Pos: 151
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: No
            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: 4
              Relay_Log_Space: 351
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'master-bin.000001' at 4, the last event read from './master-bin.000001' at 120, the last byte read from './master-bin.000001' at 120.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

这个表明是主从mysql版本不一样，可参考：http://blog.csdn.net/hongchangfirst/article/details/52121596