Linux下的MySQL主主复制

た入场券 2022-08-19 14:23 194阅读 0赞

mysql主从架构中其实就一个主在工作，而从就相当于一个备份机器，**从通过日志监测的方式来备份主库上的数据**而保证主库的数据安全。在这种架构中如果从上的数据做了改变，主数据是不会用任何变化的。因为mysql主从架构主要是mysql从监控mysql主的日志变化来实现同步，相反的在这个架构中主并没有监控从的日志变化。所以，mysql从数据反生变化，主也就没有什么变化了。  
通过上述描述，可以看到如果**想实现主主复制，无非就是在mysql主从架构上让mysql主实现监测从的日志变化，从而实现两台机器相互同步。**(主从的架构前面有博文 [http://www.linuxidc.com/Linux/2013-10/91682.htm][http_www.linuxidc.com_Linux_2013-10_91682.htm] )  
实验环境：两台服务器：  
主机名：HA1,HA2(呵呵,这个主机名是英文缩写High availability，高可用的意思)  
ip:

192.168.1.231

192.168.1.232

主机系统：[CentOS][]6.4  
mysql版本5.5.22  
**首先，看下HA1（192.168.1.231）的mysql配置文件  
vim /etc/my.cnf **  
\# Example MySQL config file for very large systems.   
\#   
\# This is for a large system with memory of 1G-2G where the system runs mainly   
\# MySQL.   
\#   
\# MySQL programs look for option files in a set of   
\# locations which depend on the deployment platform.   
\# You can copy this option file to one of those   
\# locations. For information about these locations, see:   
\# http://dev.mysql.com/doc/mysql/en/option-files.html   
\#   
\# In this file, you can use all long options that a program supports.   
\# If you want to know which options a program supports, run the program   
\# with the "--help" option.   
\# The following options will be passed to all MySQL clients   
**\[client\]   
\#password      = your\_password   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock **  
\# Here follows entries for some specific programs   
\# The MySQL server   
\[mysqld\]   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
skip-external-locking   
key\_buffer\_size = 384M  
max\_allowed\_packet = 1M  
table\_open\_cache = 512  
sort\_buffer\_size = 2M  
read\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 8M  
myisam\_sort\_buffer\_size = 64M  
thread\_cache\_size = 8  
query\_cache\_size = 32M  
\# Try number of CPU's\*2 for thread\_concurrency   
**thread\_concurrency = 8**  
\# Don't listen on a TCP/IP port at all. This can be a security enhancement,   
\# if all processes that need to connect to mysqld run on the same host.   
\# All interaction with mysqld must be made via Unix sockets or named pipes.   
\# Note that using this option without enabling named pipes on Windows   
\# (via the "enable-named-pipe" option) will render mysqld useless!   
\#   
\#skip-networking   
\# Replication Master Server (default)   
\# binary logging is required for replication   
**log-bin=mysql-bin  
log-slave-updates **  
\# required unique id between 1 and 2^32 - 1   
\# defaults to 1 if master-host is not set   
\# but will not function as a master if omitted   
**server-id      = 1**

在这个配置文件中，需要特别注意的三处地方：  
log-bin=mysql-bin：这个选项基本默认都是开着的，如果没有打开，可以手动打开。  
log-slave-updates：这个选项特别的重要**它是为了让slave也能充当master**，同时也为了更好的服务于 m-m + s 的环境，保证slave挂在任何一台master上都会接收到另一个master的写入信息。当然不局限于这个架构，级联复制的架构同样也需要log-slave-updates的支持。  
server-id = 1：这个ID为服务器ID如果配置一样会出现冲突，而不能复制  
**接着再看下HA2（192.168.1.232）的mysql配置文件  
vim /etc/my.cnf **  
\# Example MySQL config file for very large systems.   
\#   
\# This is for a large system with memory of 1G-2G where the system runs mainly   
\# MySQL.   
\#   
\# MySQL programs look for option files in a set of   
\# locations which depend on the deployment platform.   
\# You can copy this option file to one of those   
\# locations. For information about these locations, see:   
\# http://dev.mysql.com/doc/mysql/en/option-files.html   
\#   
\# In this file, you can use all long options that a program supports.   
\# If you want to know which options a program supports, run the program   
\# with the "--help" option.   
\# The following options will be passed to all MySQL clients   
\[client\]   
\#password      = your\_password   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
\# Here follows entries for some specific programs   
\# The MySQL server   
**\[mysqld\]   
port            = 3306  
socket          = /usr/local/mysql/tmp/mysql.sock   
skip-external-locking **  
key\_buffer\_size = 384M  
max\_allowed\_packet = 1M  
table\_open\_cache = 512  
sort\_buffer\_size = 2M  
read\_buffer\_size = 2M  
read\_rnd\_buffer\_size = 8M  
myisam\_sort\_buffer\_size = 64M  
thread\_cache\_size = 8  
query\_cache\_size = 32M  
\# Try number of CPU's\*2 for thread\_concurrency   
thread\_concurrency = 8  
\# Don't listen on a TCP/IP port at all. This can be a security enhancement,   
\# if all processes that need to connect to mysqld run on the same host.   
\# All interaction with mysqld must be made via Unix sockets or named pipes.   
\# Note that using this option without enabling named pipes on Windows   
\# (via the "enable-named-pipe" option) will render mysqld useless!   
\#   
\#skip-networking   
\# Replication Master Server (default)   
\# binary logging is required for replication   
**log-bin=mysql-bin  
log-slave-updates **  
\# required unique id between 1 and 2^32 - 1   
\# defaults to 1 if master-host is not set   
\# but will not function as a master if omitted   
**server-id      = 10**  
\# Replication Slave (comment out master section to use this)

在HA2的mysql配置文件中，除了server-id不一样，其他几乎一模一样。配置文件写好后，我们把两台服务器上的mysql服务器启动起来。

**首先，登录HA2（192.168.1.232）的mysql中，查看master状态**  
mysql> **show master status; **  
\+------------------+----------+--------------+------------------+  
| File            | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |   
\+------------------+----------+--------------+------------------+  
| mysql-bin.000016 |      615 |              |                  |   
\+------------------+----------+--------------+------------------+  
1 row in set (0.00 sec)   
mysql>

然后，登录HA1（192.168.1.231）的msyql中，把HA2配置成自己的主，

在做这个之前先在两台机器的mysql中建立一个可以复制用的帐号：  
mysql>grant all on \*.\* to duyunlong@'192.168.1.%' identified by '123456';   
Query OK, 0 rows affected (0.01 sec)   
mysql>change master to master\_host='192.168.1.232',master\_user='duyunlong',master\_password='123456',master\_log\_file='mysql-bin.000016',master\_log\_pos=615;

同上，查看HA1(192.168.1.231)master，然后登录HA2(192.168.1.232),把HA1(192.168.1.231)，配置成自己的主，然后分别在两台机器的mysql中，启动slave  
启动后HA1状态  
mysql> **show slave status \\G; **  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
              Slave\_IO\_State: Waiting for master to send event   
                  Master\_Host: 192.168.1.232  
                  Master\_User: duyunlong   
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000016  
          Read\_Master\_Log\_Pos: 615  
              Relay\_Log\_File: HA1-relay-bin.000002  
                Relay\_Log\_Pos: 346  
        Relay\_Master\_Log\_File: mysql-bin.000016  
            **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: 615  
              Relay\_Log\_Space: 500  
              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: 0  
Master\_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: 10  
1 row in set (0.00 sec)   
ERROR:   
No query specified

可以看到 Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
然后在看HA2的状态：  
mysql> show slave status \\G;   
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
              Slave\_IO\_State: Waiting for master to send event   
                  Master\_Host: 192.168.1.231  
                  Master\_User: duyunlong   
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000018  
          Read\_Master\_Log\_Pos: 552  
              Relay\_Log\_File: HA2-relay-bin.000002  
                Relay\_Log\_Pos: 441  
        Relay\_Master\_Log\_File: mysql-bin.000018  
           ** 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: 552  
              Relay\_Log\_Space: 595  
              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: 0  
Master\_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: 1  
1 row in set (0.00 sec)   
ERROR:   
No query specified

可以看到Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
接下来，我们要测试，是不是已经可以主主复制了呢，首先登录HA1(192.168.1.231)的mysql中，建立一数据库，当然在测试前我们先看下，两台服务器中的mysql中有哪些数据  
首先看下HA1(192.168.1.231)  
\[root@HA1 ~\]\# mysql mysql -uduyunlong -p123456 -h192.168.1.231 -e 'show databases;'   
\+--------------------+  
| Database          |   
\+--------------------+  
| information\_schema |   
| mysql              |   
| performance\_schema |   
| test              |   
| wanghaipeng        |   
\+--------------------+  
\[root@HA1 ~\]\#

在HA1（192.168.1.231）可以看到数据库“b”已经复制过来了。  
那么到此，主主复制架构已经陈功！

[http_www.linuxidc.com_Linux_2013-10_91682.htm]: http://www.linuxidc.com/Linux/2013-10/91682.htm
[CentOS]: http://www.linuxidc.com/topicnews.aspx?tid=14