MySQL的安装部署

1、简介：

MySQL 是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL 所使用的 SQL 语言是用于访问数据库的最常用
标准化语言。

关系数据库管理系统(Relational Database Management System，RDBMS)，是将数据组织为相关的行和列的系统，而管理关系数据库的计算机软件就是关系数据库管理系统 。

数据库一般分为以下两种：

• 关系型数据库；MYSQL、Mariadb、Oracle、SQL Server、PostgreSQL、DB2等
• 费关系型数据库：Redis、memcached。

MySQL现阶段有免费版本和付费版本，公司里面使用MySQL 多数都是使用免费版本。

Mariadb 数据库是MySQL 数据库原来的团队，后续独立出来进行开发的完全开源版本。

mysql引擎

MySQL 引擎有很多，企业里面主流的myisam、innodb 两种。

MyISAM：主要强调的是性能，其执行数度比InnoDB类型更快，但不提供事务支持，不支持外键，如果执行大量的SELECT(查询)操作，MyISAM是更好的选择，支持表锁。myisam引擎查询性能高。

InnoDB：提供事务支持事务、外键、行级锁等高级数据库功能，可执行大量的INSERT或UPDATE，

InnoDB引擎写性能高。

2、MySQL的安装部署

MySQL的安装部署方式有三种

• yum/rpmanzhang
• 源码安装
• 二进制包安装

yum安装教程：

yum安装主要是配置好yum仓库；

环境：

• centos7.x安装mysql

配置yum仓库

清华大学的yum地址：https://mirrors.tuna.tsinghua.edu.cn/centos/7/os/x86_64/

key地址：https://mirrors.tuna.tsinghua.edu.cn/centos/7/os/x86_64/RPM-GPG-KEY-CentOS-7

[root@node-130 ~]# cd /etc/yum.repos.d/
[root@node-130 yum.repos.d]# vim qinghua.repo
[qinghua]
name=qinghua.repo
baseurl=https://mirrors.tuna.tsinghua.edu.cn/centos/7/os/x86_64/
enabled=1
gpgcheck=1
gpgkey=https://mirrors.tuna.tsinghua.edu.cn/centos/7/os/x86_64/RPM-GPG-KEY-CentOS-7#生成元数据
[root@node-130 yum.repos.d]# yum repolist all

安装

• Centos7的MySQL需要安装
  • mariadb
  • mariadb-devel
  • mariadb-server
• 因为yum安装，会自动解决依赖关系，所以其余的不用操作

#配置yum源
[root@Node4 yum.repos.d]# vim centos.repo 
# CentOS-Base.repo
#
# The mirror system uses the connecting IP address of the client and the
# update status of each mirror to pick mirrors that are updated to and
# geographically close to the client.  You should use this for CentOS updates
# unless you are manually picking other mirrors.
#
# If the mirrorlist= does not work for you, as a fall back you can try the 
# remarked out baseurl= line instead.
#
#
[base]
name=CentOS-$releasever - Base - 163.com
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
baseurl=http://mirrors.163.com/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7#released updates
[updates]
name=CentOS-$releasever - Updates - 163.com
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
baseurl=http://mirrors.163.com/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7#additional packages that may be useful
[extras]
name=CentOS-$releasever - Extras - 163.com
#mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=extras
baseurl=http://mirrors.163.com/centos/$releasever/extras/$basearch/
gpgcheck=1
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7#additional packages that extend functionality of existing packages
[centosplus]
name=CentOS-$releasever - Plus - 163.com
baseurl=http://mirrors.163.com/centos/$releasever/centosplus/$basearch/
gpgcheck=1
enabled=0
gpgkey=http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7#直接安装
[root@node-130 yum.repos.d]# yum -y install mariadb mariadb-devel mariadb-server#检查是否安装成功；
[root@node-130 yum.repos.d]# rpm -qa mariadb
mariadb-5.5.68-1.el7.x86_64
[root@node-130 yum.repos.d]# rpm -qa | grep mariadb
mariadb-devel-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64#启动mariadb
[root@node0 ~]# systemctl start mariadb
[root@node0 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 

Centos6版本安装mysql

• 安装的软件略有不同
• mysql-server mysql-devel mysql

需要找到yum源，如果直接使用centos7的源会停止安装；

二进制安装教程

二进制的MySQL软件包是具有bin的目录的；

不需要自己进行编译

准备：

mysql的网址：https://dev.mysql.com/downloads/

清华大学地址：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.2.37/bintar-linux-glibc_214-x86/

安装：

[root@node1 ~]# rz
[root@node1 ~]# ls mariadb-10.2.36-linux-systemd-x86_64.tar.gz 
mariadb-10.2.36-linux-systemd-x86_64.tar.gz#解压二进制包，并且指定了位置
[root@node1 ~]# tar -xf mariadb-10.2.36-linux-systemd-x86_64.tar.gz  -C /usr/local/#进入到二进制的mariadb目录--->改个名字
[root@node1 ~]# cd /usr/local/mariadb-10.2.36-linux-systemd-x86_64/
[root@node1 mariadb-10.2.36-linux-systemd-x86_64]# [root@node1 ~]# mv /usr/local/mariadb-10.2.36-linux-systemd-x86_64/ /usr/local/mysql#查看一下内容
[root@node1 ~]# cd /usr/local/mysql/
[root@node1 mysql]# ls
bin      data               include         man         README-wsrep  sql-bench
COPYING  docs               INSTALL-BINARY  mysql-test  scripts       support-files
CREDITS  EXCEPTIONS-CLIENT  lib             README.md   share         THIRDPARTY

准备mysql的配置文件

• small.cnf—>小于60M
• large.cnf---->大于多少
• huge.cnf—>巨大的

#复制一份配置文件到mysql目录下
[root@node1 mysql]# cp support-files/my-large.cnf  ./my.cnf#修改配置文件
[root@node1 mysql]# vim my.cnf 
.......
[mysqld]
datadir=/data/mysql			#添加这一行，指定了mysql的数据目录
basedir=/usr/local/mysql	#指定了mysql的安装目录
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M

定义mysql的脚本文件

#复制二进制中的mysql脚本文件-->改名到到启动文件中
[root@node1 mysql]# cp support-files/mysql.server /etc/init.d/mysqld#确定文件是具有执行权限
[root@node1 mysql]# ll /etc/init.d/mysqld 
-rwxr-xr-x. 1 root root 12245 6月  15 15:50 /etc/init.d/mysqld#这里需要指定mysql启动脚本
[root@node1 mysql]# chown mysql:mysql  /etc/init.d/mysqld 

初始化mysql

#第一：确定是否存在mysql用户
[root@node1 mysql]# useradd -r -s /sbin/nologin mysql
[root@node1 mysql]# id mysql
uid=998(mysql) gid=996(mysql) 组=996(mysql)#第二：准备好数据目录
[root@node1 mysql]# mkdir -p /data/mysql
[root@node1 mysql]# tree /data/
/data/
└── mysql#第三：初始化mysql#安装一些依赖
[root@node5-mariadb ~]# yum -y install libaio
#初始化
[root@node1 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
Installing MariaDB/MySQL system tables in '/data/mysql' ...
OK													#这里显示ok代表成功安装........

启动mysql

[root@node1 mysql]# ps -ef | grep 3306
root       8811   8295  0 16:06 pts/1    00:00:00 grep --color=auto 3306[root@node1 mysql]# systemctl start mariadbFailed to start mariadb.service: Unit not found.[root@node1 mysql]# /etc/init.d/mysqld start
Reloading systemd:                                         [  确定  ]
Starting mysqld (via systemctl):                           [  确定  ][root@node1 mysql]# ps -ef | grep 3306
mysql      9120   8978  0 16:06 ?        00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/data/mysql/node1.pid --socket=/tmp/mysql.sock --port=3306
root       9226   8295  0 16:06 pts/1    00:00:00 grep --color=auto 3306[root@node1 mysql]# vim ~/.bashrc 
# .bashrc
# User specific aliases and functions
alias mysql='/usr/local/mysql/bin/mysql'	#别名方式快速启动mysql
alias nginx='/usr/local/nginx/sbin/nginx'
..........[root@node1 mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.2.31-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 

源码安装mysql-5.7教程

源码部署mysql-5.7版本

• 准备源码文件：mysql-5.7.28.tar.gz和
  • 网站：http://mirrors.163.com/mysql/Downloads/MySQL-5.7/
  • http://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
• 虚拟机最好跳到2G的内存
  • 虚拟机–>关机–>设置–>调整内存大小为2G

安装

Boost库是为C++语言标准库提供扩展的一些C++程序的总称

#准备好源码文件
[root@node2-132 ~]# rz
[root@node2-132 ~]# ls
boost_1_59_0.tar.gz  mysql-5.7.28.tar.gz
[root@node2-132 ~]# mv boost_1_59_0 /usr/local/boost#解压文件-->并且移动到/usr/local/文件下；
[root@node2-132 ~]# tar -xf boost_1_59_0.tar.gz 
[root@node2-132 ~]# mv boost_1_59_0 /usr/local/boost#解压mysql的源码文件
[root@node2-132 ~]# tar -xf mysql-5.7.28.tar.gz 
[root@node2-132 ~]# mv mysql-5.7.28 /usr/local/mysql
[root@node2-132 mysql]# ls								#查看一下文件
BUILD            Doxyfile-perfschema  LICENSE     regex             unittest
client           extra                man         scripts           VERSION
cmake            include              mysql-test  source_downloads  vio
CMakeLists.txt   INSTALL              mysys       sql               win
cmd-line-utils   libbinlogevents      mysys_ssl   sql-common        zlib
config.h.cmake   libbinlogstandalone  packaging   storage
configure.cmake  libmysql             plugin      strings
dbug             libmysqld            rapid       support-files
Docs             libservices          README      testclients

安装对应的依赖环境

• 因为是源码安装；所以系统不会自动解决依赖环境

[root@node2-132 ~]# yum -y install gcc ncurses-devel libaio bison gcc-c++ git cmake openssl-devel

预编译、编译、安装

• 这里耗时很久–>需要等很久很久
• 编译用的是cmake

#一定要注意路径
[root@node2-132 ~]# cd /usr/local/mysql/#预编译：
[root@node2-132 ~]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DMYSQL_DATADIR=/data/mysql \
-DSYSCONFDIR=/usr/local/mysql \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3307 \
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0 \
-DENABLE_DTRACE=0 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost#参数的详细解释：  
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \    #指定了安装的路径
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \			#指定了套接字的路径
-DMYSQL_DATADIR=/data/mysql \						#指定了mysql的数据目录
-DSYSCONFDIR=/usr/local/mysql \						#指定了配置文件的路径
-DMYSQL_USER=mysql \								#启动的人是mysql
-DMYSQL_TCP_PORT=3307 \								#指定了端口
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DDEFAULT_CHARSET=utf8 \							#指定了字符集
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0 \
-DENABLE_DTRACE=0 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost						#指定了这个c++语言的库#编译--->就是这里需要等待很长时间
[root@node2-132 mysql]# make #安装
[root@node2-132 mysql]# make install

准备启动mysql

#创建编译的时候指定的数据目录
[root@node2-132 mysql]# mkdir -p /data/mysql
[root@node2-132 mysql]# tree /data
/data
└── mysql1 directory, 0 files#添加mysql用户
[root@node2-132 mysql]# useradd -r -s /sbin/nologin mysql
[root@node2-132 mysql]# id mysql
uid=998(mysql) gid=996(mysql) groups=996(mysql)#修改数据目录的所有者和所属组都是mysql
[root@node2-132 mysql]# chown mysql:mysql /data/mysql
[root@node2-132 mysql]# ll -d /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Jun 15 17:13 /data/mysql/

准备mysql的配置文件

• 源码安装mysql在/usr/local/mysql中是没有my.cnf配置文件的
• 需要从/etc/my.cnf拷贝过去；

[root@node2-132 mysql]# vim my.cnf
[mysqld]
basedir=/usr/local/mysql/
datadir=/data/mysql/
port=3307
pid-file=/data/mysql/mysql.pid
socket=/data/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/mysql.log

准备mysql的启动文件

#把mysql的启动文件复制到/etc/init.d/目录下，并确认有x权限
[root@node2-132 mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node2-132 mysql]# ll /etc/init.d/mysqld 
-rw-r--r-- 1 root root 10566 Jun 15 18:03 /etc/init.d/mysqld
[root@node2-132 mysql]# chmod +x /etc/init.d/mysqld  
[root@node2-132 mysql]# ll /etc/init.d/mysqld 
-rwxr-xr-x 1 root root 10566 Jun 15 18:03 /etc/init.d/mysqld

初始化mysql

[root@node2-132 mysql]# bin/mysqld --initialize --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql#启动mysql
[root@node2-132 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.log'.
SUCCESS! 
[root@node2-132 mysql]# ps -ef | grep 330
mysql     47967  47739  0 18:10 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=/data/mysql/mysql.log --pid-file=/data/mysql/mysql.pid --socket=/data/mysql/mysql.sock --port=3307

。。

源码安装mysql-5.5教程

准备源码包

[root@node-130 ~]# rz
[root@node-130 ~]# ls
mysql-5.5.60.tar.gz#解压源码包
[root@node-130 ~]# tar -xf mysql-5.5.60.tar.gz  
[root@node-130 ~]# cd mysql-5.5.60/
[root@node-130 mysql-5.5.60]# 

安装依赖

[root@node-130 mysql-5.5.60]# yum -y install gcc gcc-c++ ncurses-devel libaio bison git cmake 

源码预编译、编译安装mysql

#预编译：
[root@node-130 mysql-5.5.60]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_DATADIR=/data/mysql \
-DSYSCONFDIR=/usr/local/mysql/ \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DWITH_XTRADB_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EXTRA_CHARSETS=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_BIG_TABLES=1 \
-DWITH_DEBUG=0#编译
[root@node-130 mysql-5.5.60]# make #安装
[root@node-130 mysql-5.5.60]# make install

准备需要的目录和用户

• 编译的时候已经制定了数据目录
• 制定了mysql用户

[root@node-130 mysql-5.5.60]# useradd -r -s /sbin/nologin mysql
[root@node-130 mysql-5.5.60]# id mysql
uid=998(mysql) gid=996(mysql) 组=996(mysql)[root@node-130 mysql-5.5.60]# mkdir -p /data/mysql
[root@node-130 mysql-5.5.60]# tree /data/mysql
/data/mysql
0 directories, 0 files	#这里需要修改一下数据目录的归属；
[root@node-130 mysql-5.5.60]# chown mysql. /data/mysql

准备配置文件

#把源码文件的配置文件复制到/usr/local/mysql目录中
[root@node-130 mysql-5.5.60]# ls support-files/my-
my-huge.cnf                my-large.cnf               my-small.cnf
my-huge.cnf.sh             my-large.cnf.sh            my-small.cnf.sh
my-innodb-heavy-4G.cnf     my-medium.cnf              
my-innodb-heavy-4G.cnf.sh  my-medium.cnf.sh  [root@node-130 mysql-5.5.60]# cp support-files/my-large.cnf /usr/local/mysql/my.cnf#这里需要修改一下配置文件中指定的数据目录和mysql的安装目录
[root@node-130 mysql-5.5.60]# vim /usr/local/mysql/my.cnf 	
[mysqld]
datadir=/data/mysql
basedir=/usr/local/mysql

准备mysql服务启动

#从源码目录这种复制过去；并且确认是否具有执行权限
[root@node-130 mysql-5.5.60]# cp support-files/mysql.server /etc/init.d/mysqld
[root@node-130 mysql-5.5.60]# ll /etc/init.d/mysqld 
-rw-r--r--. 1 root root 10555 6月  17 07:22 /etc/init.d/mysqld#没有权限就需要添加权限
[root@node-130 mysql-5.5.60]# chmod +x /etc/init.d/mysqld 
[root@node-130 mysql-5.5.60]# ll /etc/init.d/mysqld 
-rwxr-xr-x. 1 root root 10555 6月  17 07:22 /etc/init.d/mysqld

初始化mysql

[root@node-130 mysql-5.5.60]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql#/usr/local/mysql/scripts/mysql_install_db  初始化命令#--user=mysql			#指定用户#--datadir=/data/mysql		#指定了数据目录#--basedir=/usr/local/mysql	#指定了mysql的安装地方#初始化成功就能够看到数据目录有相关的数据
[root@node-130 mysql-5.5.60]# ls /data/mysql/
mysql  mysql-bin.000001  mysql-bin.000002  mysql-bin.index  performance_schema  test

启动mysql

[root@node-130 mysql-5.5.60]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! #登陆
[root@node-130 mysql-5.5.60]# /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.60-log Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

MySQL登陆

安装好mysql之后，需要开启服务

• 注意：源码目录安装的mysql需要绝对路径启动

#开启服务
#开启mysql服务
[root@node1 ~]# /etc/init.d/mysqld  start
Starting mysqld (via systemctl):                           [  确定  ]#然后在登陆
[root@node1 ~]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.36-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> #？可以查看mysql命令
MariaDB [(none)]> ?#查看mysql信息
MariaDB [(none)]> \ls
--------------
/usr/local/mysql/bin/mysql  Ver 15.1 Distrib 10.2.36-MariaDB, for Linux (x86_64) using readline 5.1
Connection id:		10
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.2.36-MariaDB-log MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			5 min 38 sec
.......#快速启动mysql-->创建别名
[root@node1 ~]# vim ~/.bashrc
alias mysql='/usr/local/mysql/bin/mysql'
[root@node1 ~]# source ~/.bashrc 

普通登陆–免密登陆

#免密登陆mysql#默认使用的是3306的默认端口登陆
[root@node1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.2.36-MariaDB-log MariaDB Server		#mysql的版本Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> #查看一下状态
MariaDB [(none)]> status;			#命令需要带上；号，不然不会执行命令
--------------
/usr/local/mysql/bin/mysql  Ver 15.1 Distrib 10.2.36-MariaDB, for Linux (x86_64) using readline 5.1				#mysql的版本信息Connection id:		13
Current database:						#当前没有使用数据库，有的话会显示
Current user:		root@localhost		#当前登录的用户和主机名
SSL:			Not in use				#没有加密
Current pager:		stdout				#
Using outfile:		''
Using delimiter:	;					#使用分号结束命令
Server:			MariaDB					#使用的版本信息
Server version:		10.2.36-MariaDB-log MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock				#连接方式为套接字连接
Uptime:			33 min 46 secThreads: 8  Questions: 18  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.008
--------------

普通登陆–使用tcp套接字登陆

• 使用登陆的方式不同，id也会不同，套接字也会不同
• 一旦tcp套接字无法登陆，需要指定服务器ip连接

#可以使用tcp的套接字登陆
[root@node1 ~]# mysql -h127.0.0.1 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.36-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> status;
--------------
/usr/local/mysql/bin/mysql  Ver 15.1 Distrib 10.2.36-MariaDB, for Linux (x86_64) using readline 5.1Connection id:		14						#明显的id号也会不同
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.2.36-MariaDB-log MariaDB Server
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP			#TCP套接字登陆
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			38 min 16 secThreads: 8  Questions: 22  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.009
--------------

修改mysql的登陆之后的提示符

MariaDB [(none)]> prompt [\u@\h \d]
[root@localhost (none)]#prompt是命令#\u：指的是用户名#\h：指的是主机名#\d：指的是mysql的用户名
#如果想一登录就会有这个效果就需要写入配置文件
--->

mysql登陆后的基础操作

#mysql是用分号来结束指令的#show databases可以查看基础的数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)#如果忘记加分号
MariaDB [(none)]> show databases-> -> -> ;					#后面跟上；号就可以解决#或者斜杠\c也可以
MariaDB [(none)]> show databases-> \c
MariaDB [(none)]>#如果登录后想要查看设备的外面的目录；\！ 执行查看系统的shell命令
MariaDB [(none)]> \! ls /data/mysql
aria_log.00000001  ibdata1	ibtmp1		   mysql-bin.000001   performance_schema
aria_log_control   ib_logfile0	multi-master.info  mysql-bin.index    test
ib_buffer_pool	   ib_logfile1	mysql		   node5-mariadb.pid
MariaDB [(none)]> \! ls /data/mysql --color=auto		#带颜色查看
aria_log.00000001  ibdata1      ibtmp1             mysql-bin.000001   performance_schema
aria_log_control   ib_logfile0  multi-master.info  mysql-bin.index    test
ib_buffer_pool     ib_logfile1  mysql              node5-mariadb.pid

mysql的目录介绍

源码安装

mysql 主配置目录:/usr/local/mysql
mysql 数据目录：/data/mysql
mysql 命令目录：/usr/local/mysql/bin/* 比如：mysql、mysqld等。
mysql 配置文件：/usr/local/mysql/my.cnf
mysql 启动文件：/usr/local/mysql/support-files/mysql.server 或者是/etc/init.d/mysqld

yum安装

# yum 安装mariadb程序：
mariadb 数据目录：/var/lib/mysql
mariadb 命令目录：/usr/bin
mariadb 默认配置文件：/etc/my.cnf
mariadb 日志文件：/var/log/mariadb

3、问题报错

1、初始化mysql的时候直接报错

[root@node1 mysql]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql
Installing MariaDB/MySQL system tables in '/data/mysql' ...
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

解决：

原因是没有对应的一些库文件

–>根据提示是没有libaso这个什么库文件

–>因此直接安装即可解决问题；

[root@node1 mysql]# yum -y install libaio

2、启动mysql的登陆的时候报错

[root@node1 ~]# /usr/local/mysql/bin/mysqld
2021-06-17  6:10:49 140226260264768 [Note] /usr/local/mysql/bin/mysqld (mysqld 10.2.36-MariaDB) starting as process 7415 ...
2021-06-17  6:10:49 140226260264768 [Warning] Can't create test file /var/lib/mysql/node1.lower-test
/usr/local/mysql/bin/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2 "No such file or directory")
2021-06-17  6:10:49 140226260264768 [ERROR] Aborting

解决：

• 报错中明显提醒了，系统中没有mysql的进程

#开启mysql服务
[root@node1 ~]# /etc/init.d/mysqld  start
Starting mysqld (via systemctl):                           [  确定  ]#然后在登陆
[root@node1 ~]# /usr/local/mysql/bin/mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.36-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 

3、套接字无法登陆；并且出现如下报错

##连接数据库失败：
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2)

原因分析：

无法找到套接字，那么需要自定指定套接字

#需要查看一下一下端口信息
[root@node1 ~]# ps -ef |grep mysql
root       7565      1  0 06:11 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/node1.pid
mysql      7707   7565  0 06:11 ?        00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/data/mysql/node1.pid --socket=/tmp/mysql.sock --port=3306
root       7855   7394  0 06:54 pts/0    00:00:00 grep --color=auto mysql#刚才的信息里面可以找到：套接字指定的是 /tmp/mysql.sock#然后需要自己手动指定--即可登录
[root@node1 ~]# mysql -S /tmp/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.2.36-MariaDB-log MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> #每次都得手动指定也是很麻烦的；所以我们可以创建一下环境变量
echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
. /etc/profile.d/mysql.sh

4、启动mysql直接报错

[root@node-130 mysql-5.5.60]# /etc/init.d/mysqld start
Starting MySQL.210617 07:29:16 mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.ERROR! The server quit without updating PID file (/data/mysql/node-130.pid).

原因分析：

这里很明显报错了，没有/var/log/mariadb/mariadb.log这个日志文件，并且这个文件的归属者要是mysql用户

解决：

[root@node-130 mysql-5.5.60]# mkdir -p /var/log/mariadb/
[root@node-130 mysql-5.5.60]# chown -R mysql. /var/log/mariadb/
[root@node3 mysql-5.5.60]# touch /var/log/mariadb/mariadb.log
[root@node3 mysql-5.5.60]# chown -R mysql. /var/log/mariadb/mariadb.log 
[root@node-130 mysql-5.5.60]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 

启动mysql服务，但是无法进入mysql的界面

报错信息如下：

[root@Node3 mysql]# /usr/local/mysql/bin/mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

原因分析：

很明显是没有空白用户登录，需要添加一个用户即可解决问题