配置MySQL远程复制

太过爱你忘了你带给我的痛 2022-08-06 11:16 140阅读 0赞

MySQL远程复制的用途很广，常见的用于数据库的负载均衡，主库写，从库读，由于读锁是允许并发的，可有效提高并发处理能力。关于MySQL的高性能(High Performance)和高可用性(High availability)配置，推荐看看O’Reilly的《High Performance MySQL》一书。此外，远程复制对于远程数据交换也是有用的，如果你需要在不同的区域交换数据，不妨考虑的MySQL远程复制。

**1、远程复制基本原理**

从MySQL3.23.15以后，MySQL支持单向的远程复制。也就是说，1台MySQL服务器充当Master(主库)，1台或多台MySQL服务器充当Slaves(从库)，数据从Master向Slaves进行远程复制。注意，这种复制是异步的，有别于MySQL的同步复制实现（这种实现称做MySQL集群，MySQL Cluster）。

当主库有更新的时候，主库会把更新操作的SQL写入二进制日志(Bin log)，并维护一个二进制日志文件的索引，以便于日志文件轮回（Rotate）。在从库启动远程复制的时候，从库会开启两个I/O线程，其中一个线程连接主库，要求主库把二进制日志的变化部分传给从库，并把传回的日志写入本地磁盘。另一个线程则负责读取本地写入的二进制日志，并在本地执行，以反映出这种变化。较老的版本在复制的时候只启用一个I/O线程，实现这两部分的功能。

有几个关键性的地方需要注意：

\- 主库必须启用Bin log，主库和从库必须有唯一的Server Id  
\- 从库必须清楚了解从主库的哪一个Bin log文件的哪一个偏移位置起开始复制  
\- 从库可以从主库只复制指定的数据库，或者数据库的某些数据表  
\- 主库和从库的数据库名称可以不一样，不过还是推荐使用一样的名称  
\- 主库和从库的MySQL版本需保持一致

**2、如何配置MySQL复制**

我们在同一台机器做实验，1个主库，两个从库。基本情况如下：  
\- 主库  
端口号：3308  
数据目录：/var/lib/dbmaster  
配置文件：/var/lib/dbmaster/my.cnf

\- 从库1  
端口号：3309  
数据目录：/var/lib/dbslave1  
配置文件：/var/lib/dbslave1/my.cnf

\- 从库2  
端口号：3310  
数据目录：/var/lib/dbslave2  
配置文件：/var/lib/dbslave2/my.cnf

待同步的数据库名位research，我们的目标是实现research从主库复制到两个从库。

**(1) 主库的配置**

首先，建立数据目录位置。

# mkdir /var/lib/dbmaster

然后，编辑主库的配置文件。

# vi /var/lib/dbmaster/my.cnf

写入以下内容：

[mysqld]
    datadir=/var/lib/dbmaster
    socket=/var/lib/dbmaster/mysql.sock
    port=3308
    old_passwords=1
    
    log-bin
    server-id=1270000000013308
    binlog-do-db=research
    
    [mysqld_safe]
    err-log=/var/lib/dbmaster/mysqld.log
    pid-file=/var/lib/dbmaster/mysqld.pid
    
    [mysql.server]
    user=mysql
    basedir=/var/lib

运行下面的命令，初始化数据目录。

# mysql_install_db --defaults-file=/var/lib/dbmaster/my.cnf
    # chown -R mysql.mysql /var/lib/dbmaster

注意，我们为MySQL服务单独建立了用户mysql和用户组mysql。初始化数据目录后，必须把所有者改为mysql。

现在，我们启动主库服务，在主库创建数据库research，并创建一个数据表test，然后插入一条记录。

# mysqld_safe --defaults-file=/var/lib/dbmaster/my.cnf &
    # mysql -uroot -S /var/lib/dbmaster/mysql.sock
    mysql> create database research;
    mysql> use research;
    mysql> create table test (name varchar(20));
    mysql> insert into test set name="explorer";
    mysql> exit;

现在，我们配置主库，让从库从当前点开始复制。

首先需要配置一个用于复制的MySQL帐号，用于从库的连接。  
我们锁定整个主库，避免主库数据更新。  
然后我们查看主库的状态，记录下Bin log的文件名称(下例为localhost-bin.000003)和位置偏移(下例为79)。

# mysql -uroot -S /var/lib/dbmaster/mysql.sock
    mysql> GRANT REPLICATION SLAVE ON *.*
        -> TO 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass';
    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;
    +----------------------+----------+--------------+------------------+
    | File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +----------------------+----------+--------------+------------------+
    | localhost-bin.000003 |       79 | research     |                  |
    +----------------------+----------+--------------+------------------+
    1 row in set (0.00 sec)

然后我们另开一个终端开始下一步操作，注意保持原终端不要退出mysql。

**(2) 从库配置**

首先，建立数据目录位置。

# mkdir /var/lib/dbslave1

然后，编辑主库的配置文件。

# vi /var/lib/dbslave1/my.cnf

写入以下内容：

[mysqld]
    datadir=/var/lib/dbslave1
    socket=/var/lib/dbslave1/mysql.sock
    port=3309
    old_passwords=1
    
    log-bin
    server-id=1270000000013309
    log-slave-updates
    
    [mysqld_safe]
    err-log=/var/lib/dbslave1/mysqld.log
    pid-file=/var/lib/dbslave1/mysqld.pid
    
    [mysql.server]
    user=mysql
    basedir=/var/lib

运行下面的命令，初始化数据目录。

# mysql_install_db --defaults-file=/var/lib/dbslave1/my.cnf
    # chown -R mysql.mysql /var/lib/dbslave1
    # cp -ar /var/lib/dbmaster/research /var/lib/dbslave1

上面最后一条命令把主库的research数据库拷贝到了从库，在主库加了读锁的情况下这种拷贝是安全的。

现在，我们启动从库服务，启用主库到从库的复制。

# mysqld_safe --defaults-file=/var/lib/dbmaster/my.cnf &
    # mysql -uroot -S /var/lib/dbslave1/mysql.sock
    mysql> CHANGE MASTER TO
        -> MASTER_HOST='127.0.0.1',
        -> MASTER_PORT=3308,
        -> MASTER_USER='repl',
        -> MASTER_PASSWORD='slavepass',
        -> MASTER_LOG_FILE='localhost-bin.000003',
        -> MASTER_LOG_POS=79;
    mysql> START SLAVE;
    mysql> EXIT;

另一个从库配置与此类似。

**(3) 同步测试**

转入我们在(1)中打开的终端，此时我们还在mysql的提示符下。运行下面的SQL解锁并插入一条新记录。

mysql> UNLOCK TABLES;
    mysql> use research;
    mysql> insert into test set name="mars";
    mysql> exit;

如果正常的话应该可以在两个从库看到mars这条记录了。

**3、远程复制的进一步分析**

启用MySQL远程复制后，主库为每一个从库打开了一个连接，这可通过show processlist这条SQL看出来。

mysql> show processlist /G
    *************************** 1. row ***************************
         Id: 1
       User: repl
       Host: localhost.localdomain:40245
         db: NULL
    Command: Binlog Dump
       Time: 3404
      State: Has sent all binlog to slave; waiting for binlog to be updated
       Info: NULL
    *************************** 2. row ***************************
         Id: 2
       User: repl
       Host: localhost.localdomain:40246
         db: NULL
    Command: Binlog Dump
       Time: 3404
      State: Has sent all binlog to slave; waiting for binlog to be updated
       Info: NULL
    *************************** 3. row ***************************
         Id: 4
       User: root
       Host: localhost.localdomain:38327
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    3 rows in set (0.00 sec)

线程Id为1和2的是为从库开的连接，比较重要的信息是State，描述了当前同步状态。

从库会打开两个线程，同样我们也可以通过这个SQL语句看出来。

mysql> show processlist /G
    *************************** 1. row ***************************
         Id: 1
       User: system user
       Host:
         db: NULL
    Command: Connect
       Time: 3575
      State: Waiting for master to send event
       Info: NULL
    *************************** 2. row ***************************
         Id: 2
       User: system user
       Host:
         db: NULL
    Command: Connect
       Time: 3575
      State: Has read all relay log; waiting for the slave I/O thread to update it
       Info: NULL
    *************************** 3. row ***************************
         Id: 3
       User: root
       Host: localhost.localdomain:47107
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    3 rows in set (0.00 sec)

其中线程Id为1的是复制线程，连接远程的主库，复制Bin Log到本地磁盘。线程Id为2的是执行线程，负责执行本地磁盘同步过来的Bin Log。同样State信息是关键信息。

关于State的详细说明请参考http://dev.mysql.com/doc/refman/4.1/en/master-thread-states.html以及http://dev.mysql.com/doc/refman/4.1/en/slave-io-thread-states.html。

从库中关于复制有这么一些文件是需要注意的：

# ls /var/lib/dbslave1
    ibdata1               localhost-bin.000007  localhost-bin.000016        mysqld.log
    ib_logfile0           localhost-bin.000008  localhost-bin.000017        mysqld.pid
    ib_logfile1           localhost-bin.000009  localhost-bin.000018        mysql.sock
    localhost-bin.000001  localhost-bin.000010  localhost-bin.index         relay-log.info
    localhost-bin.000002  localhost-bin.000011  localhost-relay-bin.000012  research
    localhost-bin.000003  localhost-bin.000012  localhost-relay-bin.index   test
    localhost-bin.000004  localhost-bin.000013  master.info
    localhost-bin.000005  localhost-bin.000014  my.cnf
    localhost-bin.000006  localhost-bin.000015  mysql

\*-reloay-bin.\* 从主库同步过来的Bin log文件  
master.info 主库帐号信息和同步信息  
relay-log.info 跟踪执行同步过来的Bin log的执行情况  
mysqld.log 数据库日志，排错的时候相当有用

**4、其他工具**

netstat命令查看当前监听的网络端口，我们可以凭此判断MySQL端口是否开启。

# nestat -nlt
    Active Internet connections (only servers)
    Proto Recv-Q Send-Q Local Address               Foreign Address             State
    tcp        0      0 0.0.0.0:39137               0.0.0.0:*                   LISTEN
    tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN
    tcp        0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN
    tcp        0      0 0.0.0.0:3309                0.0.0.0:*                   LISTEN
    tcp        0      0 0.0.0.0:3310                0.0.0.0:*                   LISTEN
    tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN
    tcp        0      0 0.0.0.0:6000                0.0.0.0:*                   LISTEN
    tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN
    tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN
    tcp        0      0 :::6000                     :::*                        LISTEN

mysqlbinlog工具可查看二进制的Binlog文件，这对于判断同步复制点很有用处。

# mysqlbinlog localhost-relay-bin.000012
    ...

mysqldump命令可用于数据复制前主从库之间同步数据（在上节中我们是通过直接拷贝文件实现的）。

**参考资料：**

[http://dev.mysql.com/doc/refman/4.1/en/replication.html][http_dev.mysql.com_doc_refman_4.1_en_replication.html]

[http_dev.mysql.com_doc_refman_4.1_en_replication.html]: http://dev.mysql.com/doc/refman/4.1/en/replication.html