mysql主从搭建

mysql主从搭建

服务器介绍：
linux系统：centos6.5
mysql版本:mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
192.168.41.150 master
192.168.41.151 slave1

步骤：

1、 在master节点，修改mysql的配置文件

[root@master bin]# vi /etc/my.cnf
binlog-do-db=db1
binlog-do-db=db2
binlog-do-db=db3
binlog-ignore-db = mysql
log-bin=mysql-bin  #启用二进制日志
server-id=150  #服务器唯一ID，一般取IP最后一段

2、重启master的mysql：

[root@master bin]# service mysqld restart

3、在master节点：进入mysql终端，

[root@master bin]# mysql -uroot -p

输入密码，进去终端
新建slave用户：

mysql>CREATE USER 'slave'@'192.168.41.150' IDENTIFIED BY 'slave'; 
mysql>GRANT all privileges on *.* TO 'slave'@'%'identified by'slave'WITH GRANT OPTION; 
mysql>FLUSH PRIVILEGES;

查看现有的用户：

mysql>select user,host from mysql.user;

4、登录主服务器的mysql，查询master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000013 |      211 | db1,db2,db3  | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

注意：Master 重启后会修改mysql-bin（序号加1）

5、修改slave1节点的mysql配置文件

[root@slave bin]# vi /etc/my.cnf
replicate-do-db=db1
replicate-do-db=db2
replicate-do-db=db3
replicate-ignore-db=mysql
server-id=151
log-bin = mysql-bin

6、重启slave1的mysql

[root@master bin]# service mysqld restart

7、在slave1节点，进入mysql终端

[root@master bin]# mysql -uroot -p
mysql> slave stop;
mysql> change master to master_host='192.168.41.150',master_user='slave',master_password='slave',master_log_file='mysql-bin.000013',master_log_pos=211;
注意：Master重启后slave 要修改MASTER_LOG_FILE
mysql>start slave; 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.150
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000013
          Read_Master_Log_Pos: 211
               Relay_Log_File: mysqld-relay-bin.000034
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000013
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: db1,db2,db3
          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: 211
              Relay_Log_Space: 552
              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:
1 row in set (0.00 sec)

mysql>

注意：Slave_IO_Running 和Slave_SQL_Running的状态必须是yes，才算搭建主从成功。
问题说明：
ERROR 1201 (HY000):Could not initialize master info structure
解决方案是：运行命令 stop slave;
成功执行后继续运行 reset slave;