mysqlmysql的安装和配置、主从部署、数据迁移与备份、增删改查、用户和权限管理、安全性配置、性能监控和调优、P

mysql

1.安装和配置MySQL

安装mysql-5.7.40.zip
# unzip mysql-5.7.40.zip
# cd mysql-5.7.40
# yum localinstall * -y
# cat /var/log/mysql.log |grep password 
# 修改为允许远程访问
use mysql;
update user set host = '%' where user = 'root';
FLUSH PRIVILEGES;

2.主从部署

复制的工作原理：
    要想实现ab复制，那么前提是master上必须开启二进制日志
        1）首先master将数据更新记录到二进制日志文件中
        2）从slave start开始，slave通过I/O线程向master请求二进制日志文件，slave要知道向谁请求从哪个位置点请求
        3）master接收到slave的I/O请求之后，就会从相应的位置点开始，给slave传日志
        4）slave接收到日志后，会写入本地的中继日志中
        5）slave通过sql线程读取中继日志中的内容，在数据库中执行相应的操作，到此为止，master和slave上的数据一致，之后slave服务器进入等待状态，等待master的后续更新

master:192.168.105.225
slave:192.168.105.226
1、master：开启二进制日志
master机器：
# vim /etc/my.cnf
log-bin=binlog 			//开启日志，日志名称为binlog
server-id=1				//设置服务ID，两台机器不能相同
# systemctl restart mysqld
2、master：授权slave机器可以过来读取日志
# mysql -uroot -p'Cc..2023'
mysql> grant replication slave on *.* to repl@192.168.105.226 identified by 'Cc..2023';
mysql> flush privileges;
3、master： 查看日志名称以及pos位置点
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |      600 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
【注意，此时不要再去操作master机器，防止日志更新，pos节点发生改变】
4、slave：修改配置文件，设置server-id
# vim /etc/my.cnf
server-id=2
# systemctl restart mysqld
5、slave：向谁请求从哪个位置点请求日志
# mysql -uroot -p'Cc..2023'
mysql> change master to master_user='repl' , master_password='Cc..2023' , master_host='192.168.105.225' , master_log_file='binlog.000001' , master_log_pos=600;
6、slave：启动同步
mysql> start slave;
mysql> show slave status\G          \\验证IO 和 SQL 是否都是yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.数据迁移与备份

将mysql数据进行备份
# mysqldump -uroot -p'Cc..2023' mysql>mysqlcc
导入sql
# mysql -uroot -p'Cc..2023' -D 数据库名<mysqlcc
可以利用计划任务来避免重复备份任务

4.sql使用增删改查

建库
# create database 数据库名;
删库
# drop database;
查看库
# show databases;
使用库
# use 数据库名；
查看正在使用的库
# select database();
建表
# create table 表名;
查看表结构
# desc 表名;
查看数据库进程
# SHOW PROCESSLIST;

5.用户和权限管理

mysql中的用户信息和权限等都存储在mysql数据库中，有5张主要的表(user,db,tables_priv,columns_priv,procs_priv),最主要的就是user表
user表适用于所有库         *.*
db表适用于所有表           数据库名.*
tables_priv适用于单个表    数据库名.表名
columns_privs适用于单列
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限，适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级，而且除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在procs_priv表中。

查看用户及作用域
# select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
查看用户权限
# show grants for 'root'@'%'
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION;

创建用户
# create user 'it'@'192.168.105.226' identified by 'Cc..2023'
# flush privileges;  //刷新权限表
# select user,host from mysql.user;
+------------------+-----------------+
| user             | host            |
+------------------+-----------------+
| root             | %               |
| it               | 192.168.105.226 |
| mysql.infoschema | localhost       |
| mysql.session    | localhost       |
| mysql.sys        | localhost       |
+------------------+-----------------+
# show grants for 'it'@'192.168.105.226';
+----------------------------------------------+
| Grants for it@192.168.105.226                |
+----------------------------------------------+          -----> USAGE只能进行连接
| GRANT USAGE ON *.* TO `it`@`192.168.105.226` |
+----------------------------------------------+

用户授权及修改密码
# grant all privileges on *.* to 'it'@'192.168.105.226';
# alter user 'it'@'192.168.105.226' identified by 'Dd..2023'
删除权限
revoke all privileges on *.* from 'it'@'localhost';

6.安全性配置

由于mysql存储了大量敏感信息，因此容易被攻击，所以安全性配置是有必要的
1、升级新版本，减少漏洞和缺陷
2、密码策略：保证密码的复杂度和过期策略
3、身份验证：选择加密连接方式，使用安全的SSL连接或VPN连接方式
4、访问控制：限制数据库用户的IP地址
5、及时进行数据备份
6、避免使用默认3306端口
7、使用防火墙和入侵检测系统(IDS)等网络安全设备进行监控
8、限制用户的权限

7.数据库性能监控和调优

性能监控
1、查看数据库状态
# show status;
2、查询数据库进程
# show processlist;
3、监控数据库的慢查询
# vim /etc/my.cnf
slow_query_log=1
# systemctl restart mysqld
查看慢查询日志的位置并进行优化
# show variables like 'slow_query_log_file';

8、PXC集群

简介

Percona XtraDB Cluster（简称PXC）是针对MySQL用户的高可用性和扩展性解决方案，基于Percona Server 。其包括了Write Set REPlication补丁，使用Galera 2.0库，这是一个针对事务性应用程序的同步多主机复制插件。
Percona Server 是MySQL的改进版本，使用 XtraDB 存储引擎，在功能和性能上较 MySQL 有着很显著的提升，如提升了在高负载情况下的 InnoDB 的性能，为 DBA 提供了一些非常有用的性能诊断工具，另外有更多的参数和命令来控制服务器行为。

Percona XtraDB Cluster提供了：
（1）同步复制，事务可以在所有节点上提交
（2）多主机复制，你可以写到任何节点
（3）从（ slave）服务器上的并行应用事件，真正的“并行复制”
（4）自动节点配置。
（5）数据一致性，不再有未同步的从服务器

准备阶段

节点	ip地址	pxc集群名
pxc035	192.168.105.234	pxc
pxc036	192.168.105.235	pxc
pxc037	192.168.105.236	pxc

1、防火墙开启PXC相应端口：
    3306：数据库服务端口
    4444：SST端口
    4567：集群通信端口
    4568：IST端口
SST：State Snapshot Transfer 全量同步
IST：Incremental State Transfer 增量同步

关闭selinux
# setenforce 0     --> /etc/selinux/config    disabled
2、编辑hosts
# vim /etc/hosts 
192.168.105.234 pxc035
192.168.105.235 pxc036
192.168.105.236 pxc037

安装

1、修改密码
# systemctl start mysqld
# cat /var/log/mysqld.log | grep password
# mysql -u root -p‘初始密码’
# ALTER USER 'root'@'localhost' IDENTIFIED BY 'Cc..2023';

2、为SST 操作创建用户并提供权限
# CREATE USER 'pxc'@'localhost' IDENTIFIED BY 'Cc..2023';
# GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'pxc'@'localhost';
# FLUSH PRIVILEGES;
# exit;
# systemctl stop mysql

步骤1、2所有节点都要执行

3、修改配置文件
# vim /etc/my.cnf 
[mysqld]
#PXC集群中MySQL实例的唯一ID，不能重复，必须是数字
server-id=1
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
#PXC集群的名称
wsrep_cluster_name=pxc
#群集成员
wsrep_cluster_address=gcomm://192.168.105.234,192.168.105.235,192.168.105.236
#当前节点的名称
wsrep_node_name=pxc035
#当前节点的IP
wsrep_node_address=192.168.105.234
#同步方法（mysqldump、rsync、xtrabackup）
wsrep_sst_method=xtrabackup-v2
#同步使用的帐户
wsrep_sst_auth= pxc:Cc..2023
#同步严厉模式
pxc_strict_mode=ENFORCING
#基于ROW复制(安全可靠)
binlog_format=ROW
#默认引擎
default_storage_engine=InnoDB 
#主键自增长 不锁表
innodb_autoinc_lock_mode=2

4、启动
第一个节点需要以引导模式启动：
# systemctl start mysql@bootstrap.service
接着在第二和第三个节点上正常启动数据库服务
# systemctl start mysql

5、检查
# mysql -uroot -p'Cc..2023';
# show status like 'wsrep%';
wsrep_cluster_size=3

6、注意
PXC集群允许动态上线下线节点
用什么方式启动的mysql，就用什么方式关闭

9、达梦数据库

准备

1、添加新用户dmdba，用于安装达梦数据库
# groupadd dinstall
# useradd -g dinstall dmdba
# passwd dmdba
2、关闭防火墙、selinux
3、内核限制
# cp /etc/security/limits.conf limits.conf.bak
# vim /etc/security/limits.conf
dmdba soft nofile 65535
dmdba hard nofile 65535
dmdba soft nproc 65535
dmdba hard nproc 65535
dmdba soft stack 8192
dmdba hard stack 8192

安装

1、创建数据库安装目录，并目录所有权改为dmdba
# mkdir -p /data/dm8
# chown -R dmdba:dinstall /data/dm8
2、挂载安装
# mkdir -p /mnt/dm8
# mount dm8_20230418_x86_rh6_64.iso -o loop /mnt/dm8/
切换为dmdba用户，进入到挂载目录下
# su dmdba
# cd /mnt/dm8/
# ./DMInstall.bin -i

3、切换到root用户，执行命令/data/dm8/script/root/root_installer.sh
# su root
# ./root_installer.sh

4、给用户配置环境变量
# vim /home/dmdba/.bash_profile
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:/data/dm8/bin"
export DM_HOME="/data/dm8"
export PATH=$PATH:$DM_HOME/bin:$DM_HOME/tool
# source /home/dmdba/.bash_profile

5、添加数据库实例
dmdba用户下：
# dminit path=/data/dm8/myinst/dm8/data
6、配置数据库开机自启
切换到root用户，执行注册服务的命令
# cd /data/dm8/script/root/
# ./dm_service_installer.sh -t dmserver -p DMSERVER -dm_ini /data/dm8/myinst/dm8/data/DAMENG/dm.ini

7、启动
# systemctl start DmServiceDMSERVER
或
# su dmdba
# DmServiceDMSERVER start

8、连接
# ./disql sysdba/127.0.0.1@SYSDBA:5236

卸载

卸载数据库前，需要停止数据库服务
使用安装数据库的用户，进入到达梦数据库的安装目录下，执行uninstall.sh -i
使用root用户，执行root_uninstaller.sh命令
如果想完全删除dm8的实例配置等其它信息，直接删除安装目录