MySQL5.5复制配置步骤

古城微笑少年丶 2022-08-07 02:56 163阅读 0赞

### [史上最详细MySQL5.5复制配置步骤，与以前版本的有所不同][MySQL5.5]  ###

*  **博客分类：**
 *  [MySQL管理][MySQL]

## 史上最详细MySQL5.5复制配置步骤，与以前版本的有所不同 ##

操作系统：CentOS 5.8

MySQL版本：5.5.x

原来状况：数据库在一台机器上，已经有一些生产数据

目标：主主复制。两台数据库服务器设置成主主复制方式，即对任何一台的更改都会自动同步到另外一台。

第一台：ip地址为 xxx.xxx.xxx.156  主机名 s156

第二台：ip地址为 xxx.xxx.xxx.170  主机名 s170

注：本文中的ip地址均采用 xxx. 形式，运用时根据实际情况修改。

本文链接：[http://codingstandards.iteye.com/blog/1535994][MySQL5.5] 转载请注明出处

### 操作步骤一：停止应用运行 ###

在修改mysql配置之前，应该停止与数据库有关的进程（程序），比如：

1. tomcat或其他Web应用容器

使用 service tomcat stop 停止

或者进入 tomcat/bin 执行 ./shutdown.sh）

2. crontab 里面设置的某些与数据库有关的定时任务。

比如：那些检测tomcat是否启动的定时任务。

可以先用 crontab -e 打开，把相应项注释掉（在前面加\#号）。

3. 其他与数据库有关的进程。

### 操作步骤二：检查主机名 ###

**什么情况下需要修改主机名？**

如果发现shell命令行提示符是

\[root@**localhost** ~\]\#

或者用hostname命令得到主机名是localhost

\[root@localhost ~\]\# hostname  
localhost

**为什么需要确认主机名是否正确？**

1. 因为有些时候，安装系统的人比较懒，没有在安装时设置主机名；

2. mysql运行时生成日志文件需要用到主机名，尤其是配置成复制（replication）后；

3. 如果在配置了复制之后再进行修改主机名，会造成mysql执行错误，这是我曾经碰到过的情况。

如果不需要修改主机名，跳到下一个步骤。

注意：需要对两台服务器检查和修改。

修改主机名的步骤：

1. 停止与主机名有关系的程序或服务，比如mysql（它生成的日志文件名中包含主机名）

service mysql stop

2. 使用hostname命令修改主机名（假定新主机名为newname）

hostname newname

比如：hostname s156

3. 在/etc/hosts文件中配置主机名的解析（假定ip地址记为ip，主机名newname，域名为yourdomain.com）

127.0.0.1               localhost.localdomain localhost  
::1             localhost6.localdomain6 localhost6

ip   newname.yourdomain.com newname

比如：xxx.156 s156.hndy16.com s156

4. 在/etc/sysconfig/network中修改HOSTNAME=newname

比如：HOSTNAME=s156

5. 最好重启机器，如果不允许的话，重新登录也行

重启使用 reboot 命令

重新登录使用 exit 命令

6. 看一下命令行提示符和hostname是否正确

\[root@s156 ~\]\# hostname  
s156

7. 重新启动第一步中停止的程序或服务，比如mysql

service mysql start

### 操作步骤三： 创建复制账号 ###

需要在两台数据库上创建，假定用户名和密码都是rep。

先登录mysql

mysql -pxxx

创建用户账号，只赋予slave权限  
grant replication slave on \*.\* to 'rep'@'xxx.170' identified by 'rep';

清空所有表和块写入语句  
flush tables with read lock;

### 操作步骤四：手工复制数据库 ###

如果两台服务器都是新装的，可以跳过这一步。

如果原来单机上有数据，那么需要将数据复制到另外一台上面，让它们在开始复制之前的状态相同。

有两种方式：

1. 复制文件法：将数据库的数据文件直接复制到另外一台；

2. 使用 mysqldump 工具导出数据，然后导入到另外一台。

注意：不是所有的数据需要复制，比如 mysql 数据库、information\_schema 数据库 和 performance\_schema 数据库。

如果 test数据库中没有什么重要数据，也不必复制。

我经常采用第2种方式：

mysqldump -pxxx --default-character-set=gbk dbname >dbname.sql

tar zcf dbname.tar.gz dbname

通过sftp传到另外一台上面

sftp other\_hostname

put dbname.tar.gz

quit

或者从另外一台机器上sftp

sftp source\_hostname

get dbname.tar.gz

quit

将数据解压

tar zxf dbname.tar.gz

导入到数据库中

mysql -pxxx --default-character-set=gbk <dbname.sql

### 操作步骤五：停止mysql服务 ###

操作命令是

service mysql stop

此步的目的是避免数据库中的数据在我们修改配置文件时被修改。

当然如果所有与数据库有关的应用程序和定时任务都已经停止，那么应该不会出现这种情况。

但是，不怕一万，就怕万一。

### 操作步骤六：修改mysql配置文件 ###

配置文件 /etc/my.cnf

这是其中一台的配置，另外一台稍作修改就可以了。

s156上 /etc/my.cnf 写道

\[client\]  
default-character-set=gbk  
  
\[mysqld\]  
\#default-character-set=gbk  
character-set-server=gbk  
\# server  
server-id=156  
log-bin=mysql-bin  
binlog-ignore-db=mysql  
\# replication  
\#master-host=xxx.170  
\#master-port=3306  
\#master-user=rep  
\#master-password=rep  
\#master-connect-retry=60  
\# auto\_incr  
auto\_increment\_offset=1  
auto\_increment\_increment=2

另外一台机器上的配置

s170上 /etc/my.cnf 写道

\[client\]  
default-character-set=gbk  
  
\[mysqld\]  
\#default-character-set=gbk  
character-set-server=gbk  
\# server  
server-id=170  
log-bin=mysql-bin  
binlog-ignore-db=mysql  
\# replication  
\#master-host=xxx.156  
\#master-port=3306  
\#master-user=rep  
\#master-password=rep  
\#master-connect-retry=60  
\# auto\_incr  
auto\_increment\_offset=2  
auto\_increment\_increment=2

**要点1：默认字符集都应该一样，比如gbk：**

\[client\] 的配置名称为 default-character-set

\[mysqld\] 的配置名称为 character-set-server  （注：与以前版本的mysql不同，5.5版不认识 default-character-set）

**要点2：server-id 要互不相同才行，可以是任意的正整数**

为了更容易清楚是那一台服务器，可以设置为 ip地址的最后那个数字。

**要点3：log-bin 和 binlog-ignore-db 配置**

以前版本的mysql，binlog-ignore-db 项也可以配置成 set-variable=binlog-ignore-db=mysql，但5.5版不行了。

**要点4：不能配置 master-host 等项**

上面的几个配置文件中 master-host, master-port, master-user, master-password 等项都注释掉了，因为5.5版不支持这种配置方式了，

要改用 change master to 语句（在后面的步骤中会使用）

**要点5：自动递增的设置**

在使用mysql做数据库时，经常会使用 auto\_increment 整数类型的字段作为主键，这种主键由mysql服务器生成，每次自动递增1。

如果是在主主复制方式下，就会产生冲突，即有可能两台服务器产生同一主键值。

所以 mysql 提供了 auto\_increment\_offset 和 auto\_increment\_increment 配置项，

其中 auto\_increment\_increment 是每次递增的数值，应该大于等于复制集群中服务器的数量，比如在主主复制中可以设置为2；

auto\_increment\_offset 是基数，可以从1开始到auto\_increment\_increment，每台服务器应该不同，比如一台设置为1，另外一台设置2。

注意：如果是应用程序模拟生成自动递增，可能需要修改应用程序。

### 操作步骤七：启动mysql服务 ###

service mysql start

如果不能启动，那么就要查看 /var/lib/mysql/\*.err 文件的内容去分析启动失败的原因，修改一下配置文件再重新启动。

### 操作步骤八：更改 master 设置 ###

由于5.5版不再支持配置文件中直接设置 master-host 等数据，只能使用 change-master-to 命令，如下所示：

CHANGE MASTER TO  
MASTER\_HOST='master\_host\_name',  
MASTER\_USER='replication\_user\_name',  
MASTER\_PASSWORD='replication\_password',  
MASTER\_LOG\_FILE='recorded\_log\_file\_name',  
MASTER\_LOG\_POS=recorded\_log\_position;

注意：两台服务器上都要执行

实际例子：

在s156上

change master to master\_host='xxx.170', master\_port=3306, master\_user='rep', master\_password='rep';

在s170上

change master to master\_host='xxx.156', master\_port=3306, master\_user='rep', master\_password='rep';

之后启动从服务器线程

start slave 或者  slave start

执行示例：

mysql> change master to master\_host='xxx.156', master\_port=3306, master\_user='rep', master\_password='rep';  
Query OK, 0 rows affected (0.01 sec)  
  
mysql> start slave;  
Query OK, 0 rows affected (0.00 sec)

### 操作步骤九：确认并验证配置 ###

使用 show slave status\\G 查看从服务器状态。

使用 SHOW VARIABLES LIKE 'auto\_inc%'; 查看自动递增设置。

在一台上创建测试表、插入和更新数据，到另外一台上是否被复制。

mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: xxx.170  
                  Master\_User: rep  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysql-bin.000001  
          Read\_Master\_Log\_Pos: 107  
               Relay\_Log\_File: s156-relay-bin.000002  
                Relay\_Log\_Pos: 253  
        Relay\_Master\_Log\_File: mysql-bin.000001  
             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: 107  
              Relay\_Log\_Space: 408  
              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: 170  
1 row in set (0.00 sec)  
  
mysql> SHOW VARIABLES LIKE 'auto\_inc%';  
\+--------------------------+-------+  
| Variable\_name            | Value |  
\+--------------------------+-------+  
| auto\_increment\_increment | 2     |  
| auto\_increment\_offset    | 1     |  
\+--------------------------+-------+  
2 rows in set (0.00 sec)

mysql> use test  
Database changed  
mysql> create table test1 (id integer not null primary key);      
Query OK, 0 rows affected (0.00 sec)

在另外一台看看是否复制过来了

mysql> show tables;  
\+----------------+  
| Tables\_in\_test |  
\+----------------+  
| test1          |  
\+----------------+  
1 rows in set (0.00 sec)

更多的确认测试可自行进行，比如：insert/update/delete等操作。

### 操作步骤十：恢复应用运行 ###

与操作步骤一对应，要把应用程序和定时任务恢复过来。

比如：

1. tomcat等应用服务器。

2. crontab中的与数据库有关的定时任务。

3. 其他与数据库有关的程序。

![MySQL5.5复制配置步骤 - zhou8622 - zhou8622的博客][MySQL5.5_ - zhou8622 - zhou8622]**配置结束，大功告成！**

[MySQL5.5]: http://codingstandards.iteye.com/blog/1535994
[MySQL]: http://codingstandards.iteye.com/category/181603
[MySQL5.5_ - zhou8622 - zhou8622]: /images/20220805/616a4211decb4a7ba538e7451b2a06b2.png