mariadb(mysql)数据库主从配置

约定不等于承诺〃 2022-12-07 04:28 652阅读 0赞

### 文章目录 ###

*  数据库主从配置
 *   *  1.数据库挂载原理
     *   *  1.1检查主库的状态
         *  1.2 开启二进制日志文件
         *   *  1.2.1 修改主库的核心配置文件
             *  1.2.2 添加
             *  1.2.3 my.cnf文件
             *  1.2.4 重启数据库服务
             *  1.2.5 查看是否有mysql-bin.index文件
         *  1.3 实现主从挂载
         *   *  1.3.1 挂载分析：
             *  1.3.2 实现挂载
             *   *  1.3.2.1 从库配置：
                 *  1.3.2.2启动主从服务
                 *  1.3.2.3检查主从服务的状态
                 *  1.3.2.4 重新配置主从

*  **主从配置原理**：
    
     *  主数据库数据发生改变时生成二进制日志文件，  
        通过I/O线程读取二进制的日志文件吗，将文件写入中继（Relay log）  
        由sql线程将中继；里的数据以sql语句的形式执行在从库  
        使主库与从库数据更新保持一致  
        ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTMyOTgxNA_size_16_color_FFFFFF_t_70_pic_center]

# 数据库主从配置 #

## 1.数据库挂载原理 ##

### 1.1检查主库的状态 ###

MariaDB [(none)]> show master status;
    Empty set (0.06 sec)
    
    MariaDB [(none)]>

*  默认条件下数据库是不能充当主数据库的，需要开启二进制日志文件

### 1.2 开启二进制日志文件 ###

*  #### 1.2.1 修改主库的核心配置文件 ####
    
        vim /etc/my.cnf
 *  #### 1.2.2 添加 ####
    
        server-id=1    #### sql服务id 必须唯一
        log-bin=mysql-bin    ####二进制日志文件名
 *  #### 1.2.3 my.cnf文件 ####
    
        [mysqld]
        datadir=/var/lib/mysql
        socket=/var/lib/mysql/mysql.sock
        # Disabling symbolic-links is recommended to prevent assorted security risks
        symbolic-links=0
        # Settings user and group are ignored when systemd is used.
        # If you need to run mysqld under a different user or group,
        # customize your systemd unit file for mariadb according to the
        # instructions in http://fedoraproject.org/wiki/Systemd
        server-id=1    #### sql服务id 必须唯一
        log-bin=mysql-bin    ####二进制日志文件名
        [mysqld_safe]
        log-error=/var/log/mariadb/mariadb.log
        pid-file=/var/run/mariadb/mariadb.pid
        
        #
        # include all files from the config directory
        #
        !includedir /etc/my.cnf.d
 *  #### 1.2.4 重启数据库服务 ####
    
        [root@localhost etc]# systemctl restart mariadb
        [root@localhost etc]#
 *  #### 1.2.5 查看是否有mysql-bin.index文件 ####
    
        [root@localhost etc]# cd /var/lib/mysql
        [root@localhost mysql]# ls
        aria_log.00000001  ibdata1      ib_logfile1  localhost.err  mysql-bin.000001  mysql.sock
        aria_log_control   ib_logfile0  jtdb         mysql          mysql-bin.index   performance_schema
        [root@localhost mysql]#

### 1.3 实现主从挂载 ###

#### 1.3.1 挂载分析： ####

*  说明:从库挂载主库/主库IP地址/主库端口号/登录用户名/登录的密码/二进制日志文件/读取位置.

#### 1.3.2 实现挂载 ####

*  ##### 1.3.2.1 从库配置： #####
    
        MariaDB [(none)]> CHANGE MASTER TO
            -> MASTER_HOST="192.168.126.129",
            -> MASTER_PORT=3306,
            -> MASTER_USER="root",
            -> MASTER_PASSWORD="123456",
            -> MASTER_LOG_FILE="mysql-bin.000001",
            -> MASTER_LOG_POS=245;
        Query OK, 0 rows affected (0.03 sec)
 *  ##### 1.3.2.2启动主从服务 #####
    
        MariaDB [(none)]> start slave;
        Query OK, 0 rows affected (0.00 sec)
        
        MariaDB [(none)]>
 *  ##### 1.3.2.3检查主从服务的状态 #####
    
        MariaDB [(none)]> show slave status;
        +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
        | Slave_IO_State                   | Master_Host     | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File           | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
        +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
        | Waiting for master to send event | 192.168.126.129 | root        |        3306 |            60 | mysql-bin.000001 |                 245 | mariadb-relay-bin.000002 |           529 | mysql-bin.000001      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |                 245 |             825 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 |
        +----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
        1 row in set (0.00 sec)
        
        MariaDB [(none)]>
 *  ##### 1.3.2.4 重新配置主从 #####
    
        stop slave;
    
    然后重复以上步骤

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80OTMyOTgxNA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/6de63a9742464d7e948927ec0f2793dd.png