24.0 数据库安装

最新推荐文章于 2022-05-19 02:22:39 发布

csdn_immortal

最新推荐文章于 2022-05-19 02:22:39 发布

阅读量201

点赞数

分类专栏： linux 文章标签： mariadb安装

本文链接：https://blog.csdn.net/csdn_immortal/article/details/83006640

版权

linux 专栏收录该内容

98 篇文章 0 订阅

订阅专栏

数据的时代
   涉及的数据量大
   数据不随程序的结束而消失
   数据被多个应用程序共享
   大数据

数据库的发展史
   萌芽阶段：文件系统
       使用磁盘文件来存储数据
   初级阶段：第一代数据库
       出现了网状模型、层次模型的数据库
   中级阶段：第二代数据库
       关系型数据库和结构化查询语言
   高级阶段：新一代数据库
       “关系-对象”型数据库

文件管理系统的缺点
   编写应用程序不方便
   数据冗余不可避免
   应用程序依赖性
   不支持对文件的并发访问
   数据间联系弱
   难以按用户视图表示数据
   无安全控制功能

数据库管理系统的优点
   相互关联的数据的集合
   较少的数据冗余
   程序与数据相互独立
   保证数据的安全、可靠
   最大限度地保证数据的正确性
   数据可以并发使用并能同时保证一致性

数据库管理系统
   数据库是数据的汇集，它以一定的组织形式存于存储介质上
   DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心
   DBA：负责数据库的规划、设计、协调、维护和管理等工作
   应用程序指以数据库为基础的应用程序

数据库管理系统的基本功能
   数据定义
   数据处理
   数据安全
   数据备份

数据库系统的架构
   单机架构
   大型主机/终端架构
   主从式架构（C/S）
   分布式架构

关系型数据库
   关系：关系就是二维表。并满足如下性质：
   表中的行、列次序并不重要
   行row：表中的每一行，又称为一条记录 record
   列column：表中的每一列，称为属性，字段 field
   主键（Primary key）：用于惟一确定一个记录的字段
   域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值

关系数据库
RDBMS：
MySQL: MySQL, MariaDB, Percona Server #是mysql分支
PostgreSQL: 简称为pgsql，EnterpriseDB 红帽
Oracle
MSSQL IBM
DB2
数据库排名：
https://db-engines.com/en/ranking

实体-联系模型E-R
   实体Entity
   •   客观存在并可以相互区分的客观事物或抽象事件称为实体。
   •   在E-R图中用矩形框表示实体，把实体名写在框内
   属性
   •   实体所具有的特征或性质
   联系
   联系是数据之间的关联集合，是客观存在的应用语义链
       实体内部的联系：指组成实体的各属性之间的联系。如职工实体中，职工号和部门经理号之间有一种关联关系
       实体之间的联系：指不同实体之间联系。例学生选课实体和学生基本信息实体之间
       实体之间的联系用菱形框表示

联系类型
   一对一联系(1:1)
   一对多联系(1:n) 主外键
   多对多联系(m:n) 构建第三张表

事务
事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：
A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

2进制安装包下载
https://downloads.mariadb.org/mariadb/10.2.18/

https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/

yum安装
# MariaDB 10.3 CentOS repository list - created 2018-10-06 09:05 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

国外太慢
[mariadb]
name = MariaDB
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos/7/x86_64/
gpgkey=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

[root@master01 ~ ]#yum install mariadb-server
'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h master01 password 'new-password'

[root@master01 ~ ]#rpm -ql MariaDB-server
[root@master01 ~ ]#systemctl start mariadb
[root@master01 ~ ]#mysql
MariaDB [(none)]> help

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

MariaDB [(none)]> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Table Maintenance
Transactions
User-Defined Functions
Utility

MariaDB [(none)]> \u test #客户端命令，可以不加分号

[root@master01 ~ ]#mysql --help
[root@master01 ~ ]#mysql
MariaDB [(none)]> select user(); #账号名是用户名加上主机名
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

[root@master01 ~ ]#mysql -uxyz -p #匿名用户，用户账号任意
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.10-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[root@master01 ~ ]#mysql mysql
MariaDB [mysql]> show tables;
MariaDB [mysql]> desc user; #查看user表的列

[root@master01 ~ ]#which mysql_secure_installation
/usr/bin/mysql_secure_installation

[root@master01 ~ ]#rpm -qf /usr/bin/mysql_secure_installation
MariaDB-server-10.3.10-1.el7.centos.x86_64

[root@master01 ~ ]#mysql_secure_installation
Enter current password for root (enter for none):
Set root password? [Y/n] Y
New password:
Re-enter new password:
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n]
Reload privilege tables now? [Y/n] y
Thanks for using MariaDB!

Thanks for using MariaDB!
[root@master01 ~ ]#mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@master01 ~ ]#mysql -uroot -p
Enter password:

#上述的密码都是一样的，加slat(盐)让相同的密码显示出来不一样比较好

用户账号
   mysql用户账号由两部分组成：
   'USERNAME'@'HOST‘
   说明：
   HOST限制此用户可通过哪些远程主机连接mysql服务器
   支持使用通配符：
   % 匹配任意长度的任意字符
   172.16.0.0/255.255.0.0 或 172.16.%.%
   _ 匹配任意单个字符

[root@master01 ~ ]#mysql -uroot -pcentos -h 192.168.31.7
ERROR 1130 (HY000): Host 'master01' is not allowed to connect to this MariaDB server
[root@master01 ~ ]#mysql -uroot -pcentos -h 127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.3.10-MariaDB MariaDB Server

https://downloads.mariadb.org/mariadb/10.2.16/

源码
mariadb-10.2.16.tar.gz
二进制
mariadb-10.2.16-linux-x86_64.tar.gz

rpm包安装mariadb=========================================================开始
二进制安装，源码已经编译好，但配置文件需要自己配置好

[root@centos7:~]# fdisk /dev/sdb
类型L
8e LVM
[root@centos7:~]# partprobe
[root@centos7:~]# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 200G 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 199G 0 part /
sdb 8:16 0 20G 0 disk
└─sdb1 8:17 0 1G 0 part
sr0 11:0 1 8.8G 0 rom

[root@centos7:~]# pvcreate /dev/sdb1
Physical volume "/dev/sdb1" successfully created.
[root@centos7:~]# vgcreate vg0 /dev/sdb1
Volume group "vg0" successfully created
[root@centos7:~]# vgdisplay

[root@centos7:~]# lvcreate -n mysql -l +100%FREE vg0
Logical volume "mysql" created.
[root@centos7:~]# lvdisplay

[root@centos7:~]# mkfs.xfs /dev/vg0/mysql
[root@centos7:~]# blkid
/dev/sr0: UUID="2018-05-07-12-53-47-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos"
/dev/sda1: UUID="38ac8148-66d5-47ee-bd6d-ce50ca048533" TYPE="xfs"
/dev/sda2: UUID="fbe53d4c-ab79-4207-a4a7-e370854f796e" TYPE="xfs"
/dev/sdb1: UUID="Dr7ezb-WKku-kIta-S01Y-z0hb-43BU-DVq8Q1" TYPE="LVM2_member"
/dev/mapper/vg0-mysql: UUID="20b97303-99f7-410b-be56-9ae8e06445fd" TYPE="xfs"

[root@centos7:~]# mount /dev/vg0/mysql /app

[root@centos7:~]# vim /etc/fstab
r!blkid /dev/vg0/mysql
UUID=20b97303-99f7-410b-be56-9ae8e06445fd /app xfs defaults 0 0

[root@centos7:~]# useradd -r -s /sbin/nologin -d /app/mysql -m mysql

#[root@centos7:~]# ll /app/mysql/ -d
#drwx------ 3 mysql mysql 78 Jul 25 19:52 /app/mysql/

[root@centos7:~]# tar xvf mariadb-10.2.16-linux-x86_64.tar.gz -C /usr/local/
[root@centos7:~]# cd /usr/local/
[root@centos7:local]# ln -s mariadb-10.2.16-linux-x86_64/ mysql
#[root@centos7:local]# ll
#drwxrwxr-x 12 1021 1004 290 Jun 26 02:07 mariadb-10.2.16-linux-x86_64
[root@centos7:local]# chown -R root.mysql /usr/local/mysql/
[root@centos7:local]# vim /etc/profile.d/mysql.sh
PATH=/usr/local/mysql/bin:$PATH
[root@centos7:local]# . /etc/profile.d/mysql.sh
#[root@centos7:local]# echo $PATH
#/usr/local/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

数据库路径
[root@centos7:local]# cd /usr/local/mysql/
[root@centos7:mysql]# ls scripts/
mysql_install_db

[root@centos7:mysql]# scripts/mysql_install_db --datadir=/app/mysql --user=mysql

[root@centos7:mysql]# ll /etc/my.cnf
-rw-r--r--. 1 root root 570 Jun 8 2017 /etc/my.cnf
[root@centos7:mysql]# rpm -qf /etc/my.cnf
mariadb-libs-5.5.56-2.el7.x86_64
[root@centos7:mysql]# cat /etc/my.cnf
#摸版
[root@centos7:mysql]# ls support-files/
[root@centos7:mysql]# cp support-files/my-huge.cnf /etc/my.cnf
[root@centos7:mysql]# vim /etc/my.cnf
datadir = /app/mysql

启动脚本，有现成的

[root@centos7:mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@centos7:mysql]# chkconfig --list
[root@centos7:mysql]# chkconfig --add mysqld
[root@centos7:mysql]# service mysqld start
[root@centos7:mysql]# ss -ntl
3306

二进制安装完成
[root@centos7:mysql]# mysql_secure_installation #安全加固

***********************************************结束

================================================开始
源码编译安装
[root@cos7:~/mariadb-10.2.16 ]# 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

[root@cos7:~ ]# useradd -r -d /data/mysql -s /sbin/nologin mysql
[root@cos7:~ ]# ll -d /data/mysql
drwxr-xr-x 2 root root 6 Jul 25 20:30 /data/mysql
[root@cos7:~ ]# chown mysql.mysql /data/mysql/
[root@cos7:~ ]# ll -d /data/mysql
drwxr-xr-x 2 mysql mysql 6 Jul 25 20:30 /data/mysql
[root@cos7:~ ]# tar xvf mariadb-10.2.16.tar.gz
编译

[root@cos7:~ ]# cd mariadb-10.2.16/

?   cd mariadb-10.2.15/
cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysqldb/ \
-DSYSCONFDIR=/etc \
-DMYSQL_USER=mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \
-DWITH_DEBUG=0 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

[root@centos7 mariadb-10.2.15] make -j 4 && make install

#二进制已经安装到此目录
/usr/local/mysql
[root@cos7:~ ]# cd /app/mysql/
[root@cos7:/app/mysql ]# ls
[root@cos7:/app/mysql ]# echo PATH=/app/mysql/bin:$PATH > /etc/profile.d/mysql.sh
[root@cos7:/app/mysql ]# . /etc/profile.d/mysql.sh
[root@cos7:/app/mysql ]# echo $PATH
[root@cos7:/app/mysql ]# mkdir /data/mysqldb/ -pv
mkdir: created directory ‘/data/mysqldb/’
[root@cos7:/app/mysql ]# chown mysql.mysql /data/mysqldb/
[root@cos7:~ ]# getent passwd mysql
mysql:x:989:984::/data/mysql:/sbin/nologin
[root@cos7:/app/mysql ]# ./scripts/mysql_install_db --datadir=/data/mysqldb --user=mysql
[root@cos7:/app/mysql ]# cp support-files/my-huge.cnf /etc/my.cnf
[root@cos7:/app/mysql ]# vim /etc/my.cnf
[mysqld]
datadir = /data/mysqldb/ #添加此行

[root@cos7:/app/mysql ]# chown -R mysql.mysql /app/mysql/
[root@cos7:/app/mysql ]# cp support-files/mysql.server /etc/init.d/mysqld
[root@cos7:/app/mysql ]# chkconfig --add mysqld
[root@cos7:/app/mysql ]# chkconfig --list
[root@cos7:/app/mysql ]# service mysql start
Redirecting to /bin/systemctl start mysql.service
[root@cos7:/app/mysql ]# ss -ntl
[root@cos7:/app/mysql ]# mysql
#安全加固
[root@cos7:/app/mysql ]# mysql_secure_installation

=========================================================
一个服务器上跑多个mariadb数据库
二进制安装多实例

实验：yum安装多实例
[root@centos7:~]# rpm -q mariadb-server
package mariadb-server is not installed

1准备目录

[root@centos7:~]# mkdir /mysqldb/{3306,3307,3308}/{etc,bin,data,pid,socket,log} -pv
[root@centos7:~]# tree /mysqldb/
#没有mysql账号
[root@centos7:~]# getent passwd mysql
#先yum源安装一个mariadb-server，不启动服务
[root@centos7:~]# yum install mariadb-server
[root@centos7:~]# chown -R mysql.mysql /mysqldb/

2 生成数据库文件
mysql_install_db --datadir=/mysqldb/3306/data --user=mysql
mysql_install_db --datadir=/mysqldb/3307/data --user=mysql
mysql_install_db --datadir=/mysqldb/3308/data --user=mysql
[root@centos7:~]# tree /mysqldb/

3准备配置文件
[root@centos7:~]# cp /etc/my.cnf /mysqldb/3306/etc/
vim /mysqldb/3306/etc/my.cnf
[mysqld]
port=3306 #默认3306可以不写
datadir= /mysqldb/3306/data
socket=/mysqldb/3306/socket/mysql.sock
[mysqld_safe]
log-error=/mysqldb/3306/log/mariadb.log
pid-file=/mysqldb/3306/pid/mariadb.pid

cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/
cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/
sed -i 's/3306/3307/g' /mysqldb/3307/etc/my.cnf
sed -i 's/3306/3308/g' /mysqldb/3308/etc/my.cnf

#生成启动脚本可参考安装的mariadb的
#[root@centos7:~]# rpm -ql mariadb-server
#[root@centos7:~]# cat /usr/lib/systemd/system/mariadb.service

4生成启动脚本
cp mysqld /mysqldb/3306/bin/
#mysql脚本在本节中
vim /mysqldb/3306/bin/mysqld
cmd_path="/usr/bin"
chmod +x /mysqldb/3306/bin/mysqld

5启动服务
/mysqldb/3306/bin/mysqld start
#[root@centos7:~]# mysql
#ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
#[root@centos7:~]# cat /etc/my.cnf.d/client.cnf
#[root@centos7:~]# mysql -S /mysqldb/3306/socket/mysql.sock

[root@centos7:~]# cp /mysqldb/3306/bin/mysqld /mysqldb/3307/bin/
[root@centos7:~]# cp /mysqldb/3306/bin/mysqld /mysqldb/3308/bin/
[root@centos7:~]# vim /mysqldb/3307/bin/mysqld
port=3307
[root@centos7:~]# vim /mysqldb/3308/bin/mysqld
port=3308

/mysqldb/3307/bin/mysqld start
/mysqldb/3308/bin/mysqld start
/mysqldb/3306/bin/mysqld stop
#[root@centos7:~]# /mysqldb/3306/bin/mysqld stop
#Stoping MySQL...
#/usr/bin/mysqladmin: connect to server at 'localhost' failed
#error: 'Access denied for user 'root'@'localhost' (using password: YES)'
#修改root口令
#[root@centos7:~]# mysqladmin -S /mysqldb/3306/socket/mysql.sock password 'centos'
#[root@centos7:~]# /mysqldb/3306/bin/mysqld stop
#Stoping MySQL...

/mysqldb/3307/bin/mysqld stop
/mysqldb/3308/bin/mysqld stop

=================================================结束

================================================开始
源码编译多实例
基于上述的源码已经编译好，下面开始多实例配置
[root@cos7:~ ]# mkdir /mysqldb/{3306,3307,3308}/{etc,bin,data,pid,socket,log} -pv
#path变量源码编译已经自动配置好
#[root@cos7:~ ]# echo $PATH
#/usr/local/mysql/bin:/app/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin
#安装数据库文件
[root@cos7:/app/mysql ]# scripts/mysql_install_db --datadir=/mysqldb/3306/data --user=mysql
安装上述命令时的安装记录有如下两行
#'./bin/mysqladmin' -u root password 'new-password'
[root@cos7:/app/mysql ]# scripts/mysql_install_db --datadir=/mysqldb/3307/data --user=mysql
[root@cos7:/app/mysql ]# scripts/mysql_install_db --datadir=/mysqldb/3308/data --user=mysql
[root@cos7:~ ]# vim /mysqldb/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/mysqldb/3306/data
socket=/mysqldb/3306/socket/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
#
[mysqld_safe]
log-error=/mysqldb/3306/log/mariadb.log
pid-file=/mysqldb/3306/pid/mariadb.pid
#
# #
# # include all files from the config directory
# #
# !includedir /etc/my.cnf.d
~
[root@cos7:~ ]# cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/
[root@cos7:~ ]# cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/

[root@cos7:/mysqldb/3306/bin ]# ./mysqld start
[root@cos7:/mysqldb/3306/bin ]# ./mysqld stop
MySQL is stopped...

#mysqladmin' -S /mysqldb/3306/socket/mysql.sock password 'centos'

===================================================结束

mysql脚本=========================开始

[root@cos7:/app/mysql ]# cat /mysqldb/3306/bin/mysqld
#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/app/mysql/bin"
mysql_basedir="/mysqldb"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}

function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}

function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}

case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

==========================================================================结束