mysql主从复制新手入门

mysql> create user 'slave'@'192.168.233.61' identified by 'slave';  --创建用户,该用户在slave机器上连接过来
Query OK, 0 rows affected (0.01 sec)
sql> grant replication slave on *.* to 'slave'@'192.168.233.61';    --grant replication slave on *.* 的权限给用户slave
Query OK, 0 rows affected (0.00 sec)

[mysqld]  
server-id=61

[root@jbxue backup]#mysqldump --user root -p --all-databases --master-data >backup.out
[root@jbxue backup]# scp backup.out 192.168.233.61:/root   --scp 到slave库
[root@d2 ~]# mysql -u root -p <backup.out    --导入
 

mysql> change master to
    -> master_host='192.168.233.110',
    -> master_user='slave',
    -> master_password='slave',
    -> master_log_file='myfirst.000002',   --来源于backup.out
    -> master_log_pos=120;                 --来源于backup.out
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;   --启动slave
Query OK, 0 rows affected (0.04 sec)

mysql> show slave status G;  --查看slave状态，可以看到slave_io_running和slave_sql_running都是处于yes状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.233.110
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: myfirst.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: d2-relay-bin.000002
                Relay_Log_Pos: 281
        Relay_Master_Log_File: myfirst.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

mysql> insert into frank.t1 values(5);  --主库上insert一条记录到frank.t1表中
Query OK, 1 row affected (0.01 sec)
mysql>  select * from frank.t1;     --备库中立即就可以查询到这条insert记录
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+