MySQL高可用集群环境搭建主从复制与数据备份linux上配置环境搭建

末蓝、 2023-07-10 08:19 16阅读 0赞

# 主机master的配置 #

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:414.8pt;"> vi /etc/my.cnf &nbsp;新增以下内容 ###服务器id server_id=1 ###开启日志文件 log-bin=mysql-bin &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

重启mysql服务 service mysqld restart

验证是否已经配置成功

show variables like '%server\_id%';

能够查询对应配置文件中的server\_id 说明已经配置成功

show master status;

能够看到同步的文件，和行数 说明已经配置成功。

[root@Hadoop ~]# mysql   -u  root   -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.1.73-log Source distribution
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show variables like '%server_id%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 1     |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> exit;
    Bye
    [root@Hadoop ~]# 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
    #添加数据配置  beg
    ###服务器id
    server_id=1
    ###开启日志文件
    log-bin=mysql-bin
    
    
    
    [mysql]
    protocol=tcp
    #server_id=177
    ###服务器id
    #log-bin=mysql-bin
    ###开启日志文件
    
    #添加数据配置  end
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

# **从服务器节点** #

<table style="margin-left:-.25pt;"> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;width:396.8pt;"> vi /etc/my.cnf ###从服务器server_id server_id=2&nbsp; ###日志文件同步方式 log-bin=mysql-bin ###同步数据库 binlog_do_db=test &nbsp;&nbsp; &nbsp; &nbsp; ###从服务器server_id server_id=3&nbsp; ###日志文件同步方式 log-bin=mysql-bin ###同步数据库 binlog_do_db=test &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

重启mysql服务 service mysqld restart

验证是否已经配置成功

show variables like '%server\_id%';

能够查询对应配置文件中的server\_id 说明已经配置成功

从服务器同步主服务器配置

change master to master\_host='192.168.184.129',master\_user='root',master\_password='123456',

master\_log\_file='mysql-bin.000003',master\_log\_pos=216;

开始同步

start slave;

检查从服务器复制功能状态

SHOW SLAVE STATUS;

[root@CentOSA ~]# vi /etc/my.cnf
    You have new mail in /var/spool/mail/root
    [root@CentOSA ~]# vi /etc/my.cnf
    [root@CentOSA ~]# vi /etc/my.cnf
    [root@CentOSA ~]# service  mysql  restart
    mysql: unrecognized service
    [root@CentOSA ~]# service  mysqld  restart
    Stopping mysqld:                                           [  OK  ]
    Starting mysqld:                                           [  OK  ]
    [root@CentOSA ~]# mysql  -u   root  -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.1.73-log Source distribution
    
    Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show variables like '%server_id%';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | server_id     | 2     |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> change master to master_host='192.168.184.129',master_user='root',master_password='root',
        ->          master_log_file='mysql-bin.000002',master_log_pos=216;
    Query OK, 0 rows affected (0.03 sec)

MySql中有一种日志叫做bin日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（insert,update,delete,ALTER TABLE,grant等等）。

主从复制的原理其实就是把主服务器上的ＢＩＮ日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

vi /etc/my.cnf

sql-mode=NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION