MySQL互为主从配置

最新推荐文章于 2024-09-13 16:04:09 发布

cnbird2008

最新推荐文章于 2024-09-13 16:04:09 发布

阅读量1.2k

点赞数

文章标签： mysql ssl file table query 服务器

MySQL-VIP：192.168.1.200

`2`	`MySQL-master1：192.168.1.201`

`3`	`MySQL-master2：192.168.1.202`

4

`5`	`OS版本：CentOS 5.4`

`6`	`MySQL版本：5.0.89`

`7`	`Keepalived版本：1.1.20`

　一、MySQL master-master配置

　　1、修改MySQL配置文件

　　两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件[MySQLd]段中加上log-bin=MySQL-bin选项

　　两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可

　　2、将192.168.1.201设为192.168.1.202的主服务器

　　在192.168.1.201上新建授权用户

 
   01MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication'; 
 
   02Query OK, 0 rows affected (0.00 sec)     
 
   03  
 
   04MySQL> show master status; 
 
   05+------------------+----------+--------------+------------------+ 
 
   06| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
 
   07+------------------+----------+--------------+------------------+ 
 
   08| MySQL-bin.000003 |      374 |              |                  | 
 
   09+------------------+----------+--------------+------------------+ 
 
   101 row in set (0.00 sec) 
 
   11　　在192.168.1.202上将192.168.1.201设为自己的主服务器 
 
   12  
 
   13MySQL> change master to master_host='192.168.1.201',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374; 
 
   14Query OK, 0 rows affected (0.05 sec)     
 
   15  
 
   16MySQL> start slave; 
 
   17Query OK, 0 rows affected (0.00 sec)     
 
   18  
 
   19MySQL> show slave status/G 
 
   20*************************** 1. row *************************** 
 
   21             Slave_IO_State: Waiting for master to send event 
 
   22                Master_Host: 192.168.1.201 
 
   23                Master_User: replication 
 
   24                Master_Port: 3306 
 
   25              Connect_Retry: 60 
 
   26            Master_Log_File: MySQL-bin.000003 
 
   27        Read_Master_Log_Pos: 374 
 
   28             Relay_Log_File: MySQL-master2-relay-bin.000002 
 
   29              Relay_Log_Pos: 235 
 
   30      Relay_Master_Log_File: MySQL-bin.000003 
 
   31           Slave_IO_Running: Yes 
 
   32          Slave_SQL_Running: Yes 
 
   33            Replicate_Do_DB: 
 
   34        Replicate_Ignore_DB: 
 
   35         Replicate_Do_Table: 
 
   36     Replicate_Ignore_Table: 
 
   37    Replicate_Wild_Do_Table: 
 
   38Replicate_Wild_Ignore_Table: 
 
   39                 Last_Errno: 0 
 
   40                 Last_Error: 
 
   41               Skip_Counter: 0 
 
   42        Exec_Master_Log_Pos: 374 
 
   43            Relay_Log_Space: 235 
 
   44            Until_Condition: None 
 
   45             Until_Log_File: 
 
   46              Until_Log_Pos: 0 
 
   47         Master_SSL_Allowed: No 
 
   48         Master_SSL_CA_File: 
 
   49         Master_SSL_CA_Path: 
 
   50            Master_SSL_Cert: 
 
   51          Master_SSL_Cipher: 
 
   52             Master_SSL_Key: 
 
   53      Seconds_Behind_Master: 0 
 
   541 row in set (0.00 sec)

　　3、将192.168.1.202设为192.168.1.201的主服务器

　　在192.168.1.202上新建授权用户

 
   01MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication'; 
 
   02Query OK, 0 rows affected (0.00 sec)     
 
   03  
 
   04MySQL> show master status; 
 
   05+------------------+----------+--------------+------------------+ 
 
   06| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
 
   07+------------------+----------+--------------+------------------+ 
 
   08| MySQL-bin.000003 |      374 |              |                  | 
 
   09+------------------+----------+--------------+------------------+ 
 
   101 row in set (0.00 sec)

　　在192.168.1.201上，将192.168.1.202设为自己的主服务器

 
   01MySQL> change master to master_host='192.168.1.202',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374; 
 
   02Query OK, 0 rows affected (0.05 sec)     
 
   03  
 
   04MySQL> start slave; 
 
   05Query OK, 0 rows affected (0.00 sec)     
 
   06  
 
   07MySQL> show slave status/G 
 
   08*************************** 1. row *************************** 
 
   09             Slave_IO_State: Waiting for master to send event 
 
   10                Master_Host: 192.168.1.202 
 
   11                Master_User: replication 
 
   12                Master_Port: 3306 
 
   13              Connect_Retry: 60 
 
   14            Master_Log_File: MySQL-bin.000003 
 
   15        Read_Master_Log_Pos: 374 
 
   16             Relay_Log_File: MySQL-master1-relay-bin.000002 
 
   17              Relay_Log_Pos: 235 
 
   18      Relay_Master_Log_File: MySQL-bin.000003 
 
   19           Slave_IO_Running: Yes 
 
   20          Slave_SQL_Running: Yes 
 
   21            Replicate_Do_DB: 
 
   22        Replicate_Ignore_DB: 
 
   23         Replicate_Do_Table: 
 
   24     Replicate_Ignore_Table: 
 
   25    Replicate_Wild_Do_Table: 
 
   26Replicate_Wild_Ignore_Table: 
 
   27                 Last_Errno: 0 
 
   28                 Last_Error: 
 
   29               Skip_Counter: 0 
 
   30        Exec_Master_Log_Pos: 374 
 
   31            Relay_Log_Space: 235 
 
   32            Until_Condition: None 
 
   33             Until_Log_File: 
 
   34              Until_Log_Pos: 0 
 
   35         Master_SSL_Allowed: No 
 
   36         Master_SSL_CA_File: 
 
   37         Master_SSL_CA_Path: 
 
   38            Master_SSL_Cert: 
 
   39          Master_SSL_Cipher: 
 
   40             Master_SSL_Key: 
 
   41      Seconds_Behind_Master: 0 
 
   421 row in set (0.00 sec)