Mariadb集群--主从备份

前提条件：安装mariadb
1、开启二进制文件
主服务器

[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
log-bin=mysql-bin  #开启二进制文件
server-id=23   #服务器id号

[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

从服务器

log-bin=mysql-bin  #开启二进制文件
server-id=24   #服务器id号

2、在主服务器上授权，从服务器保存授权信息

MariaDB [(none)]>grant replication slave on *.* to root@'172.16.60.79' identified by '123456';

查看master状态

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      393 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3、在从服务器指定master服务器

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

查看在从服务器上产生的授权信息文件

[root@ha-nginx01 ~]# cd /var/lib/mysql/
[root@ha-nginx01 mysql]# ls
aria_log.00000001  ibdata1      ib_logfile1               mariadb-relay-bin.index  mysql             mysql-bin.index  performance_schema  test
aria_log_control   ib_logfile0  mariadb-relay-bin.000001  master.info              mysql-bin.000001  mysql.sock       relay-log.info
[root@ha-nginx01 mysql]# cat master.info 
18
mysql-bin.000001
393
172.16.60.84
root
123456
3306
60
0





0
1800.000

0

4、开启从服务器 start slave,并查看

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: 172.16.60.84
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 541
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 677
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   #线程已启动
            Slave_SQL_Running: Yes    #线程已启动
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 

5、测试
主服务器添加datebase;

MariaDB [(none)]> create database darena;    
Query OK, 1 row affected (0.00 sec)

从服务器查看

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| darena             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)