MYSQL数据库1-rpm包/源码包

1-MySQL数据库基础介绍

mysql 下载的官网

mysql下载的官网<https://dev.mysql.com/downloads/mysql/>
https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.38.tar.gz 源码
数据库：是按照[数据结构](http://baike.baidu.com/view/9900.htm)来组织、[存储](http://baike.baidu.com/view/87682.htm)和管理数据的仓库

2-rpm包下MySQL--端口3306

配置文件目录: /etc/my.cnf         
             /etc/my.cnf.d/
数据主目录:  /var/lib/mysql
socket文件: /var/lib/mysql/mysql.sock
端口: 3306
日志文件:/var/log/mysqld.log

2-1首先卸载mariadb，Linux自带数据库

[root@localhost ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# yum remove mariadb-libs.x86_64                       

2-2配置本地yum源

[root@localhost ~]# mount -t iso9660 /dev/cdrom /mnt
mount: /dev/sr0 写保护，将以只读方式挂载
[root@localhost ~]# cd /etc/yum.repos.d/
[root@localhost yum.repos.d]# mkdir /etc/yum.repos.d/old[root@localhost yum.repos.d]# mv /etc/yum.repos.d/* /etc/yum.repos.d/old
mv: 无法将目录"/etc/yum.repos.d/old" 移动至自身的子目录"/etc/yum.repos.d/old/old" 下
[root@localhost yum.repos.d]# vim /etc/yum.repos.d/local.repo  # 配置本地文件,参照2-5

2-3解压MySQL压缩包

[root@localhost yum.repos.d]# cd /
[root@localhost /]# ls
bin   dev  home  lib64  mnt  proc  run   srv  tmp  var
boot  etc  lib   media  opt  root  sbin  sys  usr
[root@localhost /]# rz -E   # 应该先创建目录， mkdir /mysql_rpms，切换到这个目录下后，拖压缩包
rz waiting to receive.
[root@localhost /]# mkdir /mysql_rpms  # 创建一个MySQL文件，用于放源码包、rpm包
[root@localhost /]# ls
dev    mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar  ···
[root@localhost /]# mv /mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar /mysql_rpms/
[root@localhost /]# cd /mysql_rpms/
[root@localhost mysql_rpms]# ls
mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
[root@localhost mysql_rpms]# tar -xvf mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar  #解压缩包，-C就能指定路径了
mysql-community-client-5.7.39-1.el7.x86_64.rpm

2-4 createrepo 解决依赖关系

[root@localhost mysql_rpms]# createrepo /mysql_rpms/  #解决依赖关系,#如果没有createrepo ，则yum install createrepo
[root@localhost mysql_rpms]# ls
mysql-5.7.39-1.el7.x86_64.rpm-bundle.tar
···
repodata  #有这个文件就说明解决依赖关系

2-5 配置MYSQL文件

此部分额外引申解决交换文件问题rm -rf .swp

[root@localhost mysql_rpms]# ls /etc/yum.repos.d/
local.repo  old
[root@localhost mysql_rpms]# vim -r /etc/yum.repos.d/local.repo # 因为意外产生交换文件，参数-r可恢复没来得及保存的文件
[root@localhost mysql_rpms]# rm -rf /etc/yum.repos.d/.local.repo.swp  # 删除交换文件
[root@localhost mysql_rpms]# vim /etc/yum.repos.d/local.repo  # 进行配置
[root@localhost mysql_rpms]# cat /etc/yum.repos.d/local.repo 
[local]  # 挂载部分
name=local
baseurl=file:///mnt
gpgcheck=0
enabled=1

[mysql]
name=mysql 
baseurl=file:///mysql_rpms
gpgcheck=0
enabled=1

2-6 安装MYSQL

[root@localhost mysql_rpms]# yum install mysql-community-server
[root@localhost mysql_rpms]# systemctl restart mysqld   # 启动服务
[root@localhost mysql_rpms]# ss -anplt   # 查看端口
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port           
LISTEN     0      80       [::]:3306                 [::]:*                   users:(("mysqld",pid=55557,fd=21))

可能遇到的问题：PID 已被锁定

yum makecache
/var/run/yum.pid 已被锁定，PID为正在运行
解决办法：
rm -rf /run/yum.p

2-7 修改MySQL初始密码

[root@localhost mysql_rpms]# cat /var/log/mysqld.log |grep password # 获取密码
2023-08-16T05:42:11.975941Z 1 [Note] A temporary password is generated for root@localhost: h4dQ&;0=m#dy

[root@localhost mysql_rpms]# mysql -u root -p'h4dQ&;0=m#dy'  # 登录数据库
mysql> set password=password("Ihxj_100");  # 修改密码，密码设置要求很多
mysql> quit # 退出    # 数据库里面一定要用个；分号结束
Bye
[root@localhost mysql_rpms]# mysql -u root -p'Ihxj_100' # 重新登录

密码不正确: 仅限于新库

[root@localhost ftp]# systemctl stop mysqld
[root@localhost ftp]# rm -rf /var/lib/mysql/*
[root@localhost ftp]# > /var/log/mysqld.log
[root@localhost ftp]# systemctl start mysqld 或者 mysqld --initialize --datadir=/var/lib/mysql
[root@localhost ftp]# chown -R mysql.mysql /var/lib/mysql
[root@localhost ftp]# cat /var/log/mysqld.log | grep password
[root@localhost ftp]# systemctl restart mysqld
[root@localhost ftp]# mysql -u root -pKTdr2+yppDsq

2-8 结合ftp服务的配置

rpm包mysql基本信息
[root@localhost ~]# mkdir /var/ftp/mysql/mysql_rpms 
[root@localhost ~]# tar -xvf mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar -C /var/ftp/mysql/mysql_rpms  # 解压并且指定解压文件路径
[root@localhost ~]# createrepo /var/ftp/mysql/mysql_rpm  # 解决依赖关系，如果没有createrepo，yum install createrepo)
[root@localhost ~]# cp -r /mysql_rpms/ /var/ftp
[root@localhost ~]# ls /var/ftp/
mysql_rps  pub
[root@localhost ~]# systemctl restart vsftpd

[root@localhost ~]# vim /etc/yum.repo.d/local.repo
[local]   # 本地yum配置
name=local
baseurl=file:///mnt
gpgcheck=0
enabled=1

[mysql]
name=mysql 5.7
baseurl=ftp://192.168.10.100/mysql_rpms
gpgcheck=0
enabled=1
[root@localhost ~]#createrepo /var/ftp/mysql_rpms/

或者是：
[root@localhost ~]# vim /etc/yum.repo.d/local.repo
[local]   # 本地yum配置
name=local
baseurl=file:///mnt
gpgcheck=0
enabled=1

[mysql]
name=mysql 5.7
baseurl=file:///var/ftp/mysql_rpms
gpgcheck=0
enabled=1

3-源码包配置MySQL--用于丰富服务器参数

3-1 cmake-install

[root@localhost ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-boost-5.7.38.tar.gz   # 源码编译
[root@localhost ~]# tar -xvf mysql-boost-5.7.38.tar.gz -C /usr/local/src
[root@localhost ~]# yum install cmake   # 安装编译工具
[root@localhost mysql-5.7.38]# cd /usr/local/src/mysql-5.7.38/
[root@localhost mysql-5.7.38]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql   安装路径
-DMYSQL_DATADIR=/usr/local/mysql/data     数据目录
-DSYSCONFDIR=/etc -DWITH_READLINE=1       支持readline
-DMYSQL_TCP_PORT=3306                      端口
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock socket   文件位置
-DENABLED_LOCAL_INFILE=1 local_infile     服务器变量指示能否使用load data local infile                    命令
-DEXTRA_CHARSETS=all                     支持所有字符集
-DDEFAULT_CHARSET=utf8                   支持utf-8
-DDEFAULT_COLLATION=utf8_general_ci      支持utf-8通用字符集
-DDOWNLOAD_BOOST=1                       启动文件
-DWITH_BOOST=/usr/local/boost           启动文件位置

3-2 cmake会遇到一些问题：

防火墙是否关闭：
[root@localhost mysql-5.7.38]# systemctl stop firewalld.service 
[root@localhost mysql-5.7.38]# systemctl status firewalld.service 
cmake88:  #可能因为网速的原因
[root@localhost mysql-5.7.38]#  yum install boost
[root@localhost mysql-5.7.38]# cd /usr/local/boost/
[root@localhost mysql-5.7.38]# ls
[root@localhost mysql-5.7.38]# rm -rf boost_1_59_0.tar.gz 
[root@localhost mysql-5.7.38]# ls
[root@localhost mysql-5.7.38]# rz -E
[root@localhost mysql-5.7.38]# tar -xvf boost_1_59_0.tar.gz  
cmake71：
[root@localhost mysql-5.7.38]# yum install ncurses-devel  #安装相关开发工具
cannot find ··SSL:
[root@localhost mysql-5.7.38]# yum install openssl-devel
其他：
[root@localhost mysql-5.7.38]# yum install gcc-c++

3-3 每次重新cmake时，都需要删除缓存文件

[root@localhost mysql-5.7.38]# rm -rf CMakeCache.txt 
[root@localhost mysql-5.7.38]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_READLINE=1 -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DENABLED_LOCAL_INFILE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost

3-4 做完后make->make install

[root@localhost mysql-5.7.18]# make
[root@localhost mysql-5.7.18]# make install

3-5 内存小的情况，直接在xshell下进行压缩包解压

[root@localhost /]# cd /usr/local/
[root@localhost local]# ls
bin    etc    include  lib64    mysql  share  boost  games  lib      libexec  sbin   src
[root@localhost local]# rm -rf mysql/
[root@localhost local]# ls
bin  boost  etc  games  include  lib  lib64  libexec  sbin  share  src
[root@localhost local]# mv /mysql /usr/local/
[root@localhost local]# ls
bin    etc    include  lib64    mysql  share boost  games  lib      libexec  sbin   src
[root@localhost local]# cd mysql/
[root@localhost mysql]# ls
bin   include  LICENSE  mysql-test  README-test  support-files  docs  lib      man      README      share
[root@localhost mysql]# cd ..

3-6 启动MySQL

[root@localhost ~]# id mysql
uid=27(mysql) gid=27(mysql) 组=27(mysql)
[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# mkdir /usr/local/mysql/data  # 创建数据目录
[root@localhost mysql]# mv /etc/my.cnf /etc/my.cnf.bak   # 不使用rpm产生的配置文件
[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data   # 初始化
2023-08-16T11:17:04.534499Z 1 [Note] A temporary password is generated for root@localhost: Lfm#L00/+BXk
[root@localhost mysql]# chown -R mysql.mysql /usr/local/mysql/   # 修改权限
[root@localhost mysql]# netstat -anplt | grep :3306  # 检查端口
tcp6       0      0 :::3306                 :::*                    LISTEN      55557/mysqld        
[root@localhost mysql]# systemctl stop mysqld  # 关闭服务

[root@localhost mysql]# /usr/local/mysql/bin/mysqld_safe --user=mysql &  # 启动MySQL
[1] 102429···
[root@localhost mysql]# /usr/local/mysql/bin/mysql -uroot -p"Lfm#L00/+BXk"   # 登录
mysql> set password=password('123');   # 修改密码

3-7 切换rpm包到源码包 MySQL的启动方式

[root@localhost ~]# cd /usr/local/mysql/support-files/
[root@localhost support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@localhost support-files]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqldd    # 复制
[root@localhost support-files]# cd /usr/local/mysql
[root@localhost mysql]# chkconfig --add mysqldd
[root@localhost mysql]# pkill mysqld   # 杀掉rpm包进程
[root@localhost mysql]# cd /usr/local/mysql/support-files/
[root@localhost support-files]# service mysqldd restart   # 启动服务
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 
[root@localhost support-files]# vim /etc/profile
PATH=/usr/local/mysql/bin:$PATH
export PATH
[root@localhost support-files]# source /etc/profile  # 测试登录
[root@localhost support-files]# mysql -u root -p123   # 源码包登录方式
mysql> 
源码：/usr/local/mysql/bin/mysql
rpm:/usr/bin/mysql

3-8在命令行执行sql语句,要通过mysql -e参数显示到shell上

[root@localhost support-files]# mysql -u root -p123 -e'show databases'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost support-files]# mysql -u root -p123 -e'show databases\G'    # 在查询数据库信息或者表信息时,可以以\G做为结束符,表示以文本模式输出
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: mysql
···
[root@localhost support-files]# mysql -u root -p123 -e'show databases\G' | grep mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Database: mysql

3-9 MySQL-内部帮助

[root@localhost support-files]# mysql -u root -p123
mysql> help create database;   # 如果需要获取SQL语句的帮助可以用help命令，进一步获取帮助,可以继续使用help命令
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html   # 帮助链接

3-10 数据库基本操作:

mysql> show databases;   # 查看所有数据库

mysql> create database mydb; # 创建数据库,mydb为新建数据库的名称
mysql> use mydb; 切换数据库

mysql> create table user_info(id int,name char(10)); 创建表
mysql> show tables; 显示表
mysql> desc user_info; 描述表(看属性)

mysql> insert into user_info(id, name) values(1,'robin'); 插入数据
mysql> select * from user_info; 查看数据

mysql> drop table user_info; 删除表
mysql> drop database mydb; 删除库

3-11 重置MySQL-root密码

[root@localhost ~]# systemctl stop mysqldd

方法1：
[root@localhost ~]# /usr/local/mysql/bin/mysqld_safe --user=mysql --skip-grant-tables & 跳过授权表启动

方法2：
[root@localhost ~]# vim /etc/my.cnf
配置：
[mysqld]
socket=/var/lib/mysql/mysql.sock
skip-grant-tables

[mysql]
socket=/var/lib/mysql/mysql.sock

[root@localhost ~]# /usr/local/mysql/bin/mysql -u root
mysql> update mysql.user set authentication_string=password('123') where User='root';

[root@localhost ~]#pkill mysqld
[root@localhost ~]# systemctl start mysqldd