CAPTAINS - DBA
type: channel
link: https://captains.top/index.php/category/DBA/
description: 数据库信息类
items: MySQL二进制安装
items: MYSQL增加审计日志
items: MONGODB 数据备份与恢复
items: REDIS 集群搭建
items: MYSQL主主复制HA
items: MONGODB副本集部署

MySQL二进制安装
type: item
date: 2022-07-11T07:28:00+00:00
description: MySQL二进制安装1、概述此次二进制安装是针对Linux操作系统无法使用root权限，只能使用普通用户部署安装MySQL；本次MySQL二进制安装采用的版本：5.7.372、环境操作系统为centos7.x安装必要的依赖包，此项由管理员安装，提前让管理员安装以下依赖包$ yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl perl-Data-Dumper libaio -y3、登陆服务器使用普通账户 mysql所有数据操作都将存在/home/mysql下，或有mysql账户权限的目录；4、创建数据目录# 创建数据目录 $ pwd /home/mysql $ mkdir hdata/mysql_data/{data,logs} -p # 创建MySQL软件存放目录 $ pwd /home/mysql $ mkdir software/{src,mysql} -p5、下载MySQL二进制包登陆MySQL官网下载通用版mysql 5.7.37网址 https://downloads.mysql.com/archives/community/6、解压安装包# 解压 $ pwd /home/mysql $ cd software/src $ tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar $ mv mysql-5.7.37-linux-glibc2.12-x86_64 mysql $ mv mysql ../7、创建MySQL配置文件# 创建mysql配置文件 $ cd software/mysql $ mkdir conf $ vi my.cnf [client] port = 3308 socket = /tmp/mysql.sock [mysqld] port = 3308 user = mysql character-set-server = utf8 default_storage_engine = innodb log_timestamps = SYSTEM basedir = /home/mysql/software/mysql datadir=/home/mysql/hdata/mysql_data/data max_connections = 5000 max_connect_errors = 1000 table_open_cache = 1024 max_allowed_packet = 128M open_files_limit = 65535 socket=/tmp/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 binlog_format = MIXED query_cache_size = 128M query_cache_limit = 4M sort_buffer_size = 16M join_buffer_size = 16M wait_timeout=31536000 interactive_timeout=31536000 innodb_buffer_pool_size = 1024M innodb_file_per_table = 1 innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_purge_threads = 2 innodb_flush_log_at_trx_commit = 1 innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_log_buffer_size = 16M innodb_max_dirty_pages_pct = 80 innodb_rollback_on_timeout = on innodb_lock_wait_timeout = 28800 innodb_data_file_path=ibdata1:1024M:autoextend innodb_undo_tablespaces=3 lower_case_table_names = 1 slow_query_log = 1 long_…

MYSQL增加审计日志
type: item
date: 2021-09-10T01:52:00+00:00
description: [TOC]概述mysql5.7.32增加审计日志功能，记录数据库的操作获取审计功能插件下载5.5.68-MariaDB插件路径： /usr/lib64/mysql/plugin/server_audit.so 配置mysql5.7.321、获取mysql插件路径mysql> show global variables like '%plugin_dir%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | plugin_dir | /usr/lib64/mysql/plugin/ | +---------------+--------------------------+ 1 row in set (0.01 sec)2、分发插件# 将插件放置此目录 $ cd /usr/lib64/mysql/plugin/ $ ll server_audit.so3、安装插件INSTALL PLUGIN server_audit SONAME 'server_audit.so';4、查看变量开启情况，默认都是关闭mysql> show variables like '%audit%';5、配置开启$ vim /etc/my.cnf # 新增以下配置 server_audit_logging=on server_audit_file_path =/hdata/mysql_data/log/server_audit.log server_audit_file_rotate_size=10000000 server_audit=FORCE_PLUS_PERMANEN6、重启mysql$ systemctl restart mysqld7、查看配置情况mysql> show variables like '%audit%'; +-------------------------------+----------------------------------------+ | Variable_name | Value | +-------------------------------+----------------------------------------+ | server_audit_events | | | server_audit_excl_users | | | server_audit_file_path | /hdata/mysql_data/log/server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 10000000 | | server_audit_file_rotations | 9 | | server_audit_incl_users | | | server_audit_loc_info | | | server_audit_logging | ON | | server_audit_mode | 1 | | server_audit_output_type | file | | server_audit_query_log_limit | 1024 | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+----------------------------------------+ 16 rows in set (0.00 sec)

MONGODB 数据备份与恢复
type: item
date: 2021-07-26T07:48:00+00:00
description: mongodb备份与恢复备份恢复mongodb备份与恢复备份mongodump --host=127.0.0.1 --port=27017 -u root -p passwd --authenticationDatabase admin -d dbname -o /data/mongodb_data恢复mongorestore --host 127.0.0.1 --port 27017 -u root -p passwd --authenticationDatabase admin -d dbname /data/mongodb_data/dbnameCSDN地址：https://blog.csdn.net/qq_16125927/article/details/119111402

REDIS 集群搭建
type: item
date: 2021-07-20T15:19:00+00:00
description: redis编译安装1.下载redis源码包2.编译安装3.同样操作放到另外5台服务器4.启动6台redis5.创建redis集群redis编译安装1.下载redis源码包下载源码包http://download.redis.io/releases/redis-5.0.12.tar.gz解决依赖yum install epel-release gcc make wget -y关闭防火墙及selinux2.编译安装解压源码包mkdir -pv /opt/software && tar xf redis-5.0.12.tar.gz -C /opt/software编译cd /opt/software/redis-5.0.12 make PREFIX=/opt/software/redis install创建配置文件及日志目录mkdir /opt/software/redis/{etc,log} -pv复制配置文件cd /opt/software/redis-5.0.12 cp redis.conf ../redis/etc/修改日志路径sed -i 's@logfile ""@logfile "/opt/software/redis/log/redis.log"@' /opt/software/redis/etc/redis.conf修改配置文件vim /opt/software/redis/etc/redis.conf 69 bind 0.0.0.0 136 daemonize yes 158 pidfile /var/run/redis_6379.pid 171 logfile "/opt/software/redis/log/redis.log" 263 dir /opt/software/redis/data 294 masterauth 1qaz@WSX 295 requirepass 1qaz@WSX 834 cluster-enabled yes 842 cluster-config-file nodes-6379.conf修改系统配置vim /etc/sysctl.conf net.core.somaxconn = 512 vm.overcommit_memory = 1 # 生效 sysctl -p其他系统参数echo never > /sys/kernel/mm/transparent_hugepage/enabled echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' >> /etc/rc.local chmod a+x /etc/rc.local创建redis账户useradd -r redis -s /sbin/nologin chown -R redis.redis /opt/software/redis编写service文件[Unit] Description=Redis persistent key-value database After=network.target After=network-online.target Wants=network-online.target [Service] ExecStart=/opt/software/redis/bin/redis-server /opt/software/redis/etc/redis.conf --supervised systemd ExecReload=/bin/kill -s HUP $MAINPID ExecStop=/bin/kill -s QUIT $MAINPID Type=notify User=redis Group=redis RuntimeDirectory=redis RuntimeDirectoryMode=0755 [Install] WantedBy=multi-user.target讲redis.service放到指定路径/etc/systemd/system/3.同样操作放到另外5台服务器4.启动6台redis5.创建redis集群cd /opt/software/redis/bin ./redis-cli -a 1qaz@WSX --cluster create 192.168.103.245:6379 192.168.103.248:6379 192.168.103.214:6379 192.168.103.193:6379 192.168.103.119:6379 192.168.103.134:6379 --cluster-replicas 1登陆redis./redis-cli # 检查集群 127.0.0.1:63…

MYSQL主主复制HA
type: item
date: 2021-07-20T06:14:00+00:00
description: 1. 数据库安装1.1 mysql安装1.1.1 安装mysqld1.1.2 创建mysql主主复制1.1.3 安装keepalived1. 数据库安装1.1 mysql安装1.1.1 安装mysqldPS ：分别在docloud008和docloud012上按照以下步骤安装mysqld配置mysql yum源并安装 yum install http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm -y安装mysqlyum install mysql-community-server -y启动mysqlsystemctl start mysqld && systemctl enable mysqld修改mysql密码#获取默认密码 sudo grep 'temporary password' /var/log/mysqld.log # 登陆mysql mysql -uroot -p #修改mysql密码 set password for 'root'@'localhost'=password('MyNewPass4!'); # 修改远程登录密码 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root用户的密码' WITH GRANT OPTION; # 刷新 flush privileges;配置mysql的配置文件#停止mysql服务 systemctl stop mysqld # 修改配置文件 vim /etc/my.cnf [mysqld] port=3308 #设置UTF-8编码 character_set_server=utf8 init_connect='SET NAMES utf8' #datadir=/var/lib/mysql #修改数据存储目录 datadir=/hdata/mysql_data/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted #security risks symbolic-links=0 #修改日志存储目录 log-error=/hdata/mysql_data/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #开启binlog #binlog日志，即binary log，是二进制日志文件。它有两个作用，一是增量备份，#即只备份新增的内容；二是用于主从复制等，即主节点维护了一个binlog日志文#件，从节点从binlog中同步数据。 #当数据被误操作删除时，我们可以通过binlog日志恢复数据。 server-id=1 # 注意，集群没唯一id，master01为1，master02为2 log-bin=/hdata/mysql_data/binlog/bin-log #修改最大写入包 max_allowed_packet=10485760 #最大连接数 max_connections=1000 #设置默认时区 default-time-zone = '+08:00'将/var/lib/mysql拷贝到/hdata/mysql_datacd /var/lib sudo cp -r mysql /hdata/mysql_data/创建存储目录mkdir -pv /hdata/mysql_data/{log,binlog}修改mysql数据目录和日志目录文件夹属性sudo chown -R mysql:mysql /hdata/mysql_data重启mysqlsystemctl start mysqld1.1.2 创建mysql主主复制在master01上增加以下配置[root@docloud008 ~]# vim /etc/my.cnf # 主主复制配置 binlog_format = mixed relay-log = relay-bin relay-log-index = slave-relay-bin.index auto-increment-increment = 2 auto-increment-offset = 1在master02上增加以下配置[root@docloud012 ~]# vim /etc/my.cnf # 主主复制配置 binlog_format = mixed relay-log = relay-bin relay-log-index = slave-relay-bin.index auto-increment…

MONGODB副本集部署
type: item
date: 2021-07-19T08:37:00+00:00
description: 1、mongodb副本集部署服务器信息1.1、 集群拓扑图1.2、下载二进制包1.3、规划数据目录1.4、其他服务器部署1.5、创建mongodb配置文件1.6、 分别启动mongodb1.7、登陆任意一个节点1.8、测试1.9、开启用户认证1.10、启动集群1.11、验证集群状态1.12、导入数据测试账户密码认证及数据是否同步1、mongodb副本集部署服务器信息序号hostnameip配置1mongodb01192.168.0.2164核8g1T2mongodb02192.168.0.2174核8g1T3mongodb03192.168.0.2184核8g1T1.1、 集群拓扑图1.2、下载二进制包本次部署集群所使用mongodb版本为3.6.20官网下载地址：https://www.mongodb.com/try/download/community1.3、规划数据目录在mongodb_01上创建安装目录[root@mongodb_01 ~]# mkdir /hdata/mongo_data/{data,log,conf,keyfile} -pv mkdir: 已创建目录 "/hdata" mkdir: 已创建目录 "/hdata/mongo_data" mkdir: 已创建目录 "/hdata/mongo_data/data" mkdir: 已创建目录 "/hdata/mongo_data/log" mkdir: 已创建目录 "/hdata/mongo_data/conf" mkdir: 已创建目录 "/hdata/mongo_data/keyfile"软件路径[root@mongodb_01 ~]# mkdir /opt/software -pv mkdir: 已创建目录 "/opt/software"将mongodb二进制文件解压到/opt/software[root@mongodb_01 software]# tar xf mongodb-linux-x86_64-rhel70-3.6.20.tgz -C /opt/software/ [root@mongodb_01 software]# ll 总用量 119116 drwxr-xr-x. 3 root root 135 9月 21 15:03 mongodb-linux-x86_64-rhel70-3.6.20 -rw-r--r--. 1 root root 121973145 9月 21 15:00 mongodb-linux-x86_64-rhel70-3.6.20.tgz1.4、其他服务器部署分别在mongodb02和03上安装第五章3节3.3操作1.5、创建mongodb配置文件创建配置文件[root@mongodb_01 ~]# vim /hdata/mongo_data/conf/mongod.conf # mongod.conf systemLog: destination: file logAppend: true path: /hdata/mongo_data/log/mongod.log storage: dbPath: /hdata/mongo_data/data journal: enabled: true processManagement: fork: true # fork and run in background pidFilePath: /hdata/mongo_data/log/mongod.pid # location of pidfile timeZoneInfo: /usr/share/zoneinfo net: port: 27018 bindIp: 0.0.0.0 # Listen to local interface only, comment to listen on all interfaces. #security: # authorization: enabled # keyFile: /home/mongodb/37017/keyfile/keyfile # 设置副本集名称 replication: replSetName: "rs01"将mongodb_01的配置文件拷贝到另外2个节点# 拷贝到mongodb_02 [root@mongodb_01 ~]# scp /hdata/mongo_data/conf/mongod.conf mongodb_02:/hdata/mongo_data/conf/ # 拷贝到mongodb_03上 [root@mongodb_01 ~]# scp /hdata/mongo_data/conf/mongod.conf mongodb_03:/hdata/mongo…