前言

mariadb与mysql在使用范围和使用目的上都有所不同，两者的主要功能都是为了通过开源社区在维护中，从而获得GPL授权许可。从关系上来看，mariadb是属于数据库管理系统的开发和升级版本，它仅仅代表MySQL的一个分支。

安装mariadb(单机版)

直接安装到centos7.9系统内

配置yum源
编辑配置文件

vim /etc/yum.repos.d/MariaDB.repo

文件内容

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.28/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

安装

yum install MariaDB-server MariaDB-devel MariaDB-shared

配置文件 参数参考： mysql数据库配置参数总结
编辑配置文件

vim /etc/my.cnf.d/mariadb-server.cnf

配置内容

[server]
[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/data/log/mariadb/mariadb.err
pid-file=/run/mariadb/mariadb.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 16M
thread_cache_size = 32
query_cache_size = 32M
tmp_table_size = 64M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[galera]
[embedded]
[mariadb]
[mariadb-10.3]

创建数据目录

mysql -p  /data/log/mariadb/ && chown -R mysql:mysql   /data/log/mariadb/
mkdir -p /run/mariadb/ && chown -R mysql:mysql /run/mariadb/

数据库初始化

mysql_install_db
chown -R mysql:mysql /data/mysql/

启动

systemctl enable --now mariadb

安全设置

mysql_secure_installation

docker运行mariadb

• 安装docker和docker-compose
  docker和docker-compose一键安装

• 编写docker-compose.yaml

  version: '3.5'
  services:
  mariadb:
    container_name: mariadb
    image: mariadb:10.3.28
    ports:
      - "3306:3306"
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - ./conf/my.cnf:/etc/mysql/my.cnf
      - /data/mysqldb:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: xxxxxxx@123
      TIME_ZONE: Asia/Shanghai
    restart: always
    networks:
      -  mariadb_net
  networks:
   mariadb_net:
    driver: bridge
    driver_opts:
      com.docker.network.enable_ipv6: "false"
    ipam:
      driver: default
      config:
      - subnet: 172.16.237.0/24

启动

docker-compose up -d

mariadb所有配置下载地址

双机主主模式配置

基于binlog的传统模式

启动数据库

#m1执行
cd /opt/mariadb-m-m/m1/
docker-compose  up -d

#m2执行
cd /opt/ly-traffic/mariadb-m-m/m2/
docker-compose  up -d

进入m1节点执行

#进入mariadb
docker  exec -it  mariadb_m1  /bin/bash
mysql -uroot -pUtemyan001db -h 127.0.0.1
#确认系统时间
select now();
#确认字符集
show variables like '%character%';

#查看file和positon
show master status;
图1

#创建同步账号
grant replication slave,replication client on *.* to 'slave'@'49.119.119.%' identified by "slave@123";
flush privileges;

#同步配置
change master to master_host='mariadb_m2',master_user='slave',master_password='slave@123',master_port=3306,master_log_file='mysql-bin.000003', master_log_pos=342,master_connect_retry=30;

#启动
start slave;

#查看slave状态；
show slave status\G

进入m2执行

docker  exec -it  mariadb_m2  /bin/bash
mysql -uroot -pUtemyan001db -h 127.0.0.1
#检查系统时间
select now();
#确认字符集
show variables like '%character%';

#查看file和positon
show master status;
图1

#创建同步账号
grant replication slave,replication client on *.* to 'slave'@'49.119.119.%' identified by "slave@123";
flush privileges;

#同步配置
change master to master_host='mariadb_m1',master_user='slave',master_password='slave@123',master_port=3306,master_log_file='mysql-bin.000003', master_log_pos=342,master_connect_retry=30;

#启动
start slave;

#查看slave状态；
show slave status\G

完成后测试

#m1创建数据库
create database test1;
#m2 查看
show database;

#m2创建数据库
create database test2;
#m1 查看
show database;

#关闭m2,m1创建数据库test3，test2数据库创建表，然后启动m2 看是否自动同步

基于GTID模式

m1到m2的复制

#m1 执行
#查看master状态
show master status;
图3
#查看gtid_binlog
show variables like '%gtid_binlog%';

#m2执行
#设置slave_pos
set global gtid_slave_pos='0-11-7192';

#配置master
change master to  master_host='mariadb_m1',master_user='slave',master_password='slave@123',master_port=3306,master_use_gtid=slave_pos,master_connect_retry=30;

#启动
start slave;

m2到m1的复制

#m1配置
set global gtid_slave_pos='0-12-7192';

#配置master
change master to  master_host='mariadb_m2',master_user='slave',master_password='slave@123',master_port=3306,master_use_gtid=slave_pos,master_connect_retry=30;

#启动
start slave;

配置完成

其他

安装客户端

yum install  mariadb

创建数据库

CREATE DATABASE `xwaf` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
create user 'xwafuser'@'127.0.0.1' IDENTIFIED BY 'xwafuser@123';
grant all privileges on xwaf.* to 'xwafuser'@'127.0.0.1';
flush privileges;