1、YUM/二进制/编译安装Mysql

一、数据库三大范式

第一范式：无重复的列，且同一列不能有多个值。

第二范式：属性完全依赖于主键。

第三范式：属性不依赖于其他非主属性。

二、mysql特性

插件式存储引擎
单进程，多线程
开源

三、 配置国内YUM源，安装指定版本mysql

Index of /mariadb/yum/10.7/ | 清华大学开源软件镜像站 | Tsinghua Open Source Mirror

[root@localhost zzj]# vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.7/centos7-amd64/
gpgcheck = 0

[root@localhost zzj]# yum repolist
[root@localhost zzj]# yum -y install mariadb-server

[root@localhost zzj]# vim /etc/yum.repos.d/mysql.repo
[mysql]
name = Mysql
baseurl = https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck = 0

[root@localhost zzj]# yum repolist
[root@localhost zzj]# yum -y install mysql

mysql安全加固（yum安装的mysql存在安全问题，需要进行安全设置） 

[root@localhost zzj]# mysql_secure_installation或者mariadb-secure-installation
Enter current password for root (enter for none):
Switch to unix_socket authentication [Y/n] n       使用套接字登陆就不能远程登陆了
Change the root password? [Y/n] y
Remove anonymous users? [Y/n] y                  移除匿名用户
Disallow root login remotely? [Y/n] y
Remove test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

四、mysql基础

用户账号

mysql用户账号由两部分组成：
 'USERNAME'@'HOST'

HOST限制此用户可通过哪些远程主机连接mysql服务器
支持使用通配符：
 % 匹配任意长度的任意字符
 172.16.0.0/255.255.0.0 或 172.16.%.%
 _ 匹配任意单个字符

常见客户端命令

help      (\h) Display this help.
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.

-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e   “SQL“ 执行SQL命令
-V, --version 显示版本
-v --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置

mysqladmin

#查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -uroot -pcentos   ping
#关闭mysql服务，但mysqladmin命令无法开启
mysqladmin –uroot –pcentos shutdown
#创建数据库testdb
mysqladmin -uroot –pcentos   create testdb 
#删除数据库testdb
mysqladmin -uroot -pcentos   drop testdb
#修改root密码
mysqladmin –uroot –pcentos password ‘root123’
#日志滚动,生成新文件/var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs

五、二进制安装mysql5.6/5.7

二进制安装mysql5.6

# 下载5.6安装包，拷贝至/usr/local/（注意因为是二进制，所以必须在此目录下）
https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz

# 创建mysql用户
[root@localhost local]# groupadd -r -g 306 mysql
[root@localhost local]# useradd -r -g 306 -u 306 -d /data/mysql mysql
[root@localhost local]# id mysql
uid=306(mysql) gid=306(mysql) groups=306(mysql)

# 准备数据目录
[root@localhost local]# mkdir -p /data/mysql
[root@localhost local]# chown mysql.mysql /data/mysql/

# 准备二进制程序
[root@localhost local]# tar -zxf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz
[root@localhost local]# ln -sv mysql-5.6.34-linux-glibc2.5-x86_64/ mysql
[root@localhost local]# chown -R root:root /usr/local/mysql

# 为/data/mysql单独创建分区
# 先扩容磁盘至100G
[root@localhost ~]# lsblk
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0  100G  0 disk
├─sda1            8:1    0    1G  0 part /boot
└─sda2            8:2    0   19G  0 part
  ├─centos-root 253:0    0   17G  0 lvm  /
  └─centos-swap 253:1    0    2G  0 lvm  [SWAP]
sr0              11:0    1  973M  0 rom

[root@localhost ~]# fdisk /dev/sda
Command (m for help): p
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2099199     1048576   83  Linux
/dev/sda2         2099200    41943039    19921920   8e  Linux LVM

Partition type:
   p   primary (2 primary, 0 extended, 2 free)
   e   extended
Select (default p):
Partition number (3,4, default 3): 3
First sector (41943040-209715199, default 41943040):
Using default value 41943040
Last sector, +sectors or +size{K,M,G} (41943040-209715199, default 209715199):
Using default value 209715199
Partition 3 of type Extended and of size 80 GiB is set

Command (m for help): p
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2099199     1048576   83  Linux
/dev/sda2         2099200    41943039    19921920   8e  Linux LVM
/dev/sda3        41943040   209715199    83886080   83  Linux

# 分区完成后要指定id
Command (m for help): t
Partition number (1-3, default 3): 3
Hex code (type L to list all codes): 8e
Changed type of partition 'Linux' to 'Linux LVM'

Command (m for help): p
   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *        2048     2099199     1048576   83  Linux
/dev/sda2         2099200    41943039    19921920   8e  Linux LVM
/dev/sda3        41943040   209715199    83886080   8e  Linux LVM

Command (m for help): w
The partition table has been altered!


[root@localhost ~]# yum -y install lvm2
[root@localhost ~]# pvcreate /dev/sda3
  Physical volume "/dev/sda3" successfully created.
[root@localhost ~]# vgcreate vg0 /dev/sda3
  Volume group "vg0" successfully created
[root@localhost ~]# lvcreate -n mysql -l 100%free vg0
  Logical volume "mysql" created.
[root@localhost ~]# lvs
  LV    VG     Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  root  centos -wi-ao---- <17.00g
  swap  centos -wi-ao----   2.00g
  mysql vg0    -wi-a----- <80.00g
[root@localhost ~]# mkfs.ext4 /dev/vg0/mysql

[root@localhost ~]# blkid /dev/vg0/mysql
/dev/vg0/mysql: UUID="9d03b76c-e272-4761-9dcf-b4b4bde82996" TYPE="ext4"
[root@localhost ~]# vim /etc/fstab
UUID=9d03b76c-e272-4761-9dcf-b4b4bde82996 /data/mysql   ext4 defaults 0 0
[root@localhost ~]# mkdir -p /data/mysql
[root@localhost ~]# mount -a
[root@localhost ~]# lsblk
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0  100G  0 disk
├─sda1            8:1    0    1G  0 part /boot
├─sda2            8:2    0   19G  0 part
│ ├─centos-root 253:0    0   17G  0 lvm  /
│ └─centos-swap 253:1    0    2G  0 lvm  [SWAP]
└─sda3            8:3    0   80G  0 part
  └─vg0-mysql   253:2    0   80G  0 lvm  /data/mysql
sr0              11:0    1  973M  0 rom

# 准备配置文件
[root@localhost mysql]# cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

[root@localhost mysql]# vim /etc/my.cnf
[mysqld]
datadir = /data/mysql
innodb_file_per_table = on #在mariadb5.5以上版的是默认值，可不加
skip_name_resolve = on    #禁止主机名解析，建议使用

# 创建数据库文件
[root@localhost mysql]# yum -y install autoconf  libaio-devel  （不装可能会报Data::Dumper）
[root@localhost mysql]# cd /usr/local/mysql  （必须先切换到该路径）
[root@localhost mysql]# ./scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql

[root@localhost mysql]# ls /data/mysql/
ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  test

# 准备服务文件
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld start
Starting MySQL. SUCCESS!
[root@localhost mysql]# echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost mysql]# source /etc/profile
[root@localhost mysql]# mysql

# 安全加固
[root@localhost mysql]# mysql_secure_installation

二进制安装mysql5.7

#!/bin/bash
. /etc/init.d/functions
SRC_DIR=`pwd`
MYSQL='mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz'
#MYSQL='mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz'
COLOR='echo -e \E[01;31m'
END='\E[0m'
MYSQL_ROOT_PASSWORD=root123
 
 
check (){
 
if [ $UID -ne 0 ]; then
  action "当前用户不是root,安装失败" false
  exit 1
fi
 
cd  $SRC_DIR
if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
		$COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
	return
fi
} 
 
install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
	yum  -y -q install libaio numactl-libs   libaio &> /dev/null
    cd $SRC_DIR
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
    mkdir -p /data/mysql
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }
    chown mysql.mysql /data/mysql
    chown -R mysql.mysql /usr/local/mysql/
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
	ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
user=mysql
server-id=1
basedir=/usr/local/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
                                                                                                  
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
character-set-server=utf8mb4
[mysql]
socket=/data/mysql/mysql.sock
default-character-set=utf8mb4      
prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"    #修改提示符
#!includedir /etc/my.cnf.d
EOF
 
   cat > /etc/systemd/system/mysqld.service <<-EOF
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
EOF
 
    mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
 
    systemctl enable --now mysqld
    
 
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
 
    sleep 5
 
    MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    echo "mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null"
    mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成" 
}
 
 
check
 
install_mysql

编译安装mysql5.7

my.cnf(使用了5.57的my-huge.cnf,部分变量可能不兼容)

# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# MySQL programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /data/mysql/socket/mysql.socket

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /data/mysql/socket/mysql.socket
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id	= 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
#
# binary logging format - mixed recommended 
#binlog_format=mixed

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = @localstatedir@
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = @localstatedir@
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

yum -y install gcc gcc-g++ openssl openssl-devel  ncurses-devel libarchive libtirpc libtirpc-devel  gcc gcc-c++
tar xf cmake-3.22.1.tar.gz
cd cmake-3.22.1/
./configure
gmake
gmake install
groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql
tar -zxf mysql-boost-5.7.37.tar.gz
cd mysql-5.7.37

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql \
-DMYSQL_UNIX_ADDR=/data/mysql/socket/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSET=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=on \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPL_ESTORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_ESTORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_ENBEDDED_SERVER=1 \
-DWITH_DEBUG=0 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=boost/

make install
cp /usr/local/mysql/support-files/my-huge.cnf.sh /etc/my.cnf
vim /etc/my.cnf 修改socket和port
echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile


cd /usr/local/mysql/bin/
mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/mysql/ --user=mysql

cp support-files/mysql.server /etc/init.d/mysqld
mkdir /data/mysql/socket
chmod 1777 /data/mysql/socket/
chmod +x /etc/init.d/mysqld
/etc/init.d/mysqld start
# 修改root密码
mysqladmin -uroot password 'root123' -p
chkconfig mysqld on