概述

Mariadb支持主从备份，可以把主数据库的所有的数据同时写到备份的数据库中，实现Mariadb数据库的热备份。

部署数据库

安装并启动mariadb：

yum -y install mariadb mariadb-server.x86_64

systemctl start mariadb.service
systemctl enable mariadb.service
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

修改密码：

use mysql ;
update user set password=password("123456") where user="root";
flush privileges;

授权远程访问：

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;

配置主从架构

配置master节点

• 修改my.cnf

systemctl stop mariadb.service
vi /etc/my.cnf

[mysqld]
server-id=1
log_bin=master-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
innodb_flush_log_at_trx_commit=1
binlog_format=mixed

• 重启mariadb

systemctl start mariadb.service

• 查看记录log_bin文件名和Position(前两行)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: master-bin.000001
Position: 245
Binlog_Do_DB: 
Binlog_Ignore_DB: mysql,information_schema,performance_schema,test

• 授予slave从机复制权限

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

slave配置

• 修改my.cnf

systemctl stop mariadb.service
vi /etc/my.cnf

[mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
relay_log_recovery=1

• 重启mariadb

systemctl start mariadb.service

• 配置slave参数

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]> change master to 
    ->        master_host='192.168.201.33',
    ->        master_user='root',
    ->        master_password='123456',
    ->        master_log_file='master-bin.000001',
    ->        master_log_pos=245;
Query OK, 0 rows affected (0.15 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

• 查看是否配置成功

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.201.33
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 530
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes   //slave配置成功
            Slave_SQL_Running: Yes //slave配置成功
              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: 245
              Relay_Log_Space: 824
              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: 1
1 row in set (0.00 sec)

创建数据库及表

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS TESTDB DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema 
| TESTDB             
| mysql              
| performance_schema 
| test               
+--------------------+
5 rows in set (0.00 sec)

use TESTDB;

 DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `userName` varchar(255) NOT NULL DEFAULT '' COMMENT '用户名 ',
 `pwd` varchar(255) NOT NULL DEFAULT '' COMMENT '密码',
 PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='用户信息表';
INSERT INTO `user` VALUES (1,'马云','123456'),(2,'刘德华','123456'),(3,'雷军','xiaomi.com');