一、数据库三大范式
第一范式:无重复的列,且同一列不能有多个值。
第二范式:属性完全依赖于主键。
第三范式:属性不依赖于其他非主属性。
二、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