mysql主从复制 配置测试（一主一从）

关于mysql主从复制

1.在主mysql创建一个可以远端访问的帐号。

grant all on *.* tobuck@'192.168.1.66' identified by "hello";

use mysql

updateuserset host ='%'where user ='buck';

 

2.配置主mysql;  vi /etc/my.cnf

server-id = 1 //1代表主数据库（源） 2代表辅数据库（目的）
binlog_do_db = testmirror //要做同步的数据库名字，可以是多个数据库，之间用分号分割。

binlog-ignore-db=mysql //不需要备份的数据库 

#启动二进制文件 
log-bin=mysql-bin 

 

3.配置从mysql;   vi /etc/my.cnf

server-id = 2 //2代表辅

4.从mysql账号权限设置

GRANT REPLICATION SLAVEON*.* TOroot@'%' IDENTIFIED BY'password';

 

5.在主mysql

show masterstatus;

记住file position

 

6.从mysql：

mysql>change master to master_host='192.168.246.134',
    ->master_user='buck',
    ->master_password='hello',
    ->master_port=3306,
    ->master_log_file='mysql-bin.000002',
    ->master_log_pos=17620976,
    ->master_connect_retry=10;

 

mysql> start slave;

 

mysql> show slave status \G;
*************************** 1. row***************************
               Slave_IO_State: Waitingfor master to sendevent
                  Master_Host: 192.168.246.134
                  Master_User:buck
                  Master_Port:3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos:17620976
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos:251
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

两个必须都为yes

7.在主mysql服务器插入数据  查看从mysql是否发生变化

 

即可.