MySQL主从集群构建及故障切换

电玩女神 2022-05-30 11:35 173阅读 0赞

MYSQL数据 库主 从配置

环境准备：192.168.227.138为master主服务器，192.168.227.139o slave从服务器

在主和从服务器都安装mysql相关软件，命令如下：

yum install -y mysql-server mysql mysql-devel

安装完毕后，在Master修改vi /etc/my.cnf内容如下：

\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
log-bin=mysql-bin  
server-id=1  
auto\_increment\_offset=1  
auto\_increment\_increment=2  
  
  
\[mysqld\_safe\]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

replicate-do-db=all

启动mysql即可，/etc/init.d/mysqld restart

然后修改slave mysql数据库my.cnf 配置文件内容如下：

\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
\# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
log-bin=mysql-bin  
server-id=2  
auto\_increment\_offset=2  
auto\_increment\_increment=2  
  
  
\[mysqld\_safe\]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
master-host=192.168.227.138  
master-user=root  
master-pass=123456  
master-port=3306  
master-connect-retry=60

replicate-do-db=all

在slave 数据库服务器上设置权限，执行如下命令：

grant replication slave on \*.\* to 'root'@'%' identified by  '123456';

在Master数据库执行如下命令：

mysql>show master status;

然后在slava 服务器指定master ip 和同步的pos点：

mysql> change master to

-> master\_host='192.168.227.138',master\_user='root',master\_password='123456',master\_log\_file='mysql-bin.000001',master\_log\_pos=253;

在slave启动slave start 并执行show slave\\G 查看mysql主从状态

mysql> show slave status\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
               Slave\_IO\_State: Waiting for master to send event  
                  Master\_Host: 192.168.227.138  
                  Master\_User: root  
                  Master\_Port: 3306  
                Connect\_Retry: 60  
              Master\_Log\_File: mysq-bin.000002  
          Read\_Master\_Log\_Pos: 106  
               Relay\_Log\_File: mysqld-relay-bin.000002  
                Relay\_Log\_Pos: 250  
        Relay\_Master\_Log\_File: mysq-bin.000002  
             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: 106  
              Relay\_Log\_Space: 406  
              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:

1 row in set (0.00 sec)

在master主服务器上创建一个数据库，slave从服务器上会同步。

当主服务断开连接，从服务切换，修改主服务器 vi config\_global.php 文件

\[root@localhost html\]\# cd config/

\[root@localhost config\]\# ls  
config\_global\_default.php  config\_ucenter\_default.php  index.htm  
config\_global.php          config\_ucenter.php

\[root@localhost config\]\# vi config\_global.php

![2018031122002325][]

修改master主服务器/var/www/html/config/config\_ucenter.php

![20180311220812724][]

在slave从服务器上进行授权：

mysql> grant all on discuz.\* to discuz@'192.168.227.138' identified by '123456';

[2018031122002325]: /images/20220530/66a9585e3c2d49e7a29f38b43e305343.png
[20180311220812724]: /images/20220530/320e77b07f8048f8b7f0712fc2b4a552.png