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.cnfbinlog-do-db=db1binlog-do-db=db2binlog-do-db=db3binlog-ignore-db = mysqllog-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.cnfreplicate-do-db=db1replicate-do-db=db2replicate-do-db=db3replicate-ignore-db=mysqlserver-id=151log-bin = mysql-bin

6、重启slave1的mysql

[root@master bin]# service mysqld restart

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

[root@master bin]# mysql -uroot -pmysql> 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_FILEmysql>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: 0Master_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;