MySQL配置Master-Slave

来源：互联网发布：盒子直播平台源码编辑：程序博客网时间：2024/04/28 22:34

master库打开log-bin参数，保证master和slave的server-id参数不同

在master上创建允许slave访问的rep用户

MASTER@root@(none) 12:34:34>grant replication slave on *.* to 'rep'@'192.168.1.12' identified by 'rep';Query OK, 0 rows affected (0.00 sec)

MASTER@root@test 12:48:58>show variables like '%server_id%';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 1     |+---------------+-------+1 row in set (0.00 sec)MASTER@root@test 12:49:04>show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)

slave库尝试登录

[root@mynode2 mysql]# /service/mysql/bin/mysql -h mynode1 -urep -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.5.34-log MASTER_DBCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.SLAVE@rep@(none) 12:47:05>show grants;+---------------------------------------------------------------------------------------------------------------------------+| Grants for rep@192.168.1.12                                                                                               |+---------------------------------------------------------------------------------------------------------------------------+| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.1.12' IDENTIFIED BY PASSWORD '*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C' |+---------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)SLAVE@rep@(none) 12:47:16>

slave库的log-bin和server-id参数

SLAVE@root@(none) 12:50:02>show variables like '%server_id%';+---------------+-------+| Variable_name | Value |+---------------+-------+| server_id     | 2     |+---------------+-------+1 row in set (0.00 sec)SLAVE@root@(none) 12:50:11>show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+1 row in set (0.00 sec)

查看master库status

MASTER@root@test 12:49:14>show master status\G*************************** 1. row ***************************            File: binlog-master.000003        Position: 1061    Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

在slave库上配置master-slave

SLAVE@root@test 12:54:25>change master to master_host='mynode1',master_user='rep',master_password='rep',master_log_file='binlog-master.000003',master_log_pos=1061;Query OK, 0 rows affected (0.00 sec)

启动并查看slave status

SLAVE@root@test 12:54:53>start slave;Query OK, 0 rows affected (0.01 sec)SLAVE@root@test 12:55:01>show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: mynode1                  Master_User: rep                  Master_Port: 30307                Connect_Retry: 60              Master_Log_File: binlog-master.000003          Read_Master_Log_Pos: 1061               Relay_Log_File: mynode2-relay-bin.000002                Relay_Log_Pos: 257        Relay_Master_Log_File: binlog-master.000003             Slave_IO_Running: Yes            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: 1061              Relay_Log_Space: 415              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:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)SLAVE@root@test 12:55:03>

确保Slave_IO_Running和Slave_SQL_Running状态都为Yes

0 0