192.168.11.40、43 mariadb 10.4主从环境安装记录

192.168.11.40

更改主机名

shell> # hostnamectl set-hostname rxdb1140

关闭防火墙

临时关闭防火墙
systemctl stop firewalld
永久防火墙开机自关闭
systemctl disable firewalld
临时打开防火墙
systemctl start firewalld
防火墙开机启动
systemctl enable firewalld
查看防火墙状态
systemctl status firewalld

关闭selinux

getenforce

临时关闭SELinux
setenforce 0
临时打开SELinux
setenforce 1
查看SELinux状态
getenforce
开机关闭SELinux

编辑/etc/selinux/config文件，将SELINUX的值设置为disabled。下次开机SELinux就不会启动了。

通过yum安装mariadb 10.4

加载yum源

shell> curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
shell> yum clean all
shell> yum install MariaDB-server galera-4 MariaDB-client MariaDB-shared MariaDB-backup MariaDB-common

创建相应的目录

[root@rxdb1140 data]# mkdir mariadbdata
[root@rxdb1140 mariadbdata]# pwd
/data/mariadbdata

将安装文件迁移到/data目录下

shell> # systemctl stop mariadb

shell> mv /var/lib/mysql/ /data/mariadbdata/

编辑/etc/my.cnf.d/server.cnf文件

在/etc/my.cnf.d/server.cnf 加入如下内容

[root@rxdb1140 my.cnf.d]# more server.cnf 
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.4 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.4]
log_bin=mariadb-bin
server-id=1921681140
port=3306
socket=/data/mariadbdata/mysql/mysql.sock
log-error=/data/mariadbdata/mysql/mysqld40.log
datadir=/data/mariadbdata/mysql/
lower_case_table_names=1
innodb_buffer_pool_size=180G
log_slave_updates=1
character-set-server=utf8mb4
expire_logs_days=30
[mysql]
socket=/data/mariadbdata/mysql/mysql.sock
[client]
default-character-set = utf8mb4 

安全设置

shell> # mysql_secure_installation -S /data/mariadbdata/mysql/mysql.sock

创建root远程登录账户

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

192.168.11.43 安装

重复上面的过程

server.cnf 文件修改 server-id，log-error,read only等参数

主从复制

创建复制用户

grant replication slave on *.* to 'repl'@'%' identified by 'I0WW18BdX2P340w5DbZM';

主端备份

shell> mysqldump -uroot -p -S /data/mariadbdata/mysql/mysql.sock --master-data=2 -A | gzip -1 > all_data_20191209.sql.gz
Enter password: 
[root@rxdb1140 ~]# ls -rlht
total 428K
-rw-------. 1 root root 1.6K Dec  5 22:20 anaconda-ks.cfg
-rw-r--r--  1 root root 423K Dec  8 20:51 all_data_20191209.sql.gz

传输备份到11.43

[root@rxdb1143 ~]# zcat all_data_20191209.sql.gz | mysql -uroot -p -S /data/mariadbdata/mysql/mysql.sock
Enter password: 
[root@rxdb1143 ~]# 

获取gtid位置

shell> [root@rxdb1143 ~]# zless all_data_20191209.sql.gz | more
-- MariaDB dump 10.17  Distrib 10.4.10-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	10.4.10-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=532;

--
-- GTID to start replication from
--

-- SET GLOBAL gtid_slave_pos='0-1921681140-1';

开启复制

shell> mysql -uroot -p
mysql> SET GLOBAL gtid_slave_pos='0-1921681140-1';

mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.40',MASTER_USER='repl',MASTER_PASSWORD='I0WW18BdX2P340w5DbZM',MASTER_USE_GTID=slave_pos;

mysql> start slave;

mysql> show slave status \G

测试