Mysql基础应用

Mysql2.7.26二进制版本安装

1. 将程序上传到机器上

[root@test01 ~]# mkdir -p /server/tools
[root@test01 ~]# cd /server/tools
[root@test01 tools]# ls
mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@test01 tools]# mkdir /aplication
[root@test01 tools]# tar -xzvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /aplication
[root@test01 aplication]# mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql

2. 卸载原来服务器上的数据库

[root@test01 ~]# rpm -qa|grep mariadb
mariadb-server-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
[root@test01 ~]# yum remove mariadb-server-5.5.68-1.el7.x86_64 mariadb-5.5.68-1.el7.x86_64 mariadb-libs-5.5.68-1.el7.x86_64 -y
[root@test01 ~]# useradd -s /sbin/nologin mysql

3. 设置环境变量

[root@test01 ~]# vim /etc/profile
export PATH=/aplication/mysql/bin:$PATH
[root@test01 ~]# source /etc/profile
[root@test01 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using  EditLine wrapper

4. 挂载新磁盘用于数据存储

[root@test01 ~]# mkfs.xfs /dev/sdb
[root@test01 data]# mkdir /data01
[root@test01 data]# blkid 
/dev/sda1: UUID="f5a37650-aa88-48fb-894f-3e6233d6bfd3" TYPE="xfs" 
/dev/sda2: UUID="sgv8dI-vFJs-1bFw-ejMD-d0vM-TfmD-Apz97Y" TYPE="LVM2_member" 
/dev/sr0: UUID="2018-03-22-19-04-59-00" LABEL="RHEL-7.5 Server.x86_64" TYPE="iso9660" PTTYPE="dos" 
/dev/sdb: UUID="0e7ce1c7-2458-41bc-98af-b5d00c0ec75a" TYPE="xfs" 
/dev/mapper/rhel-root: UUID="d6cfda51-c959-4987-9e6f-10ec4e40a661" TYPE="xfs" 
/dev/mapper/rhel-swap: UUID="7a3182e8-fcaf-43a1-8946-693e6e3f3f38" TYPE="swap" 
/dev/mapper/rhel-home: UUID="e637d99d-3e9c-42e8-aa6d-ebfcece0e7ca" TYPE="xfs" 
/dev/mapper/rhel-var: UUID="10b144d1-21fa-4c7f-8c9a-195a8298037e" TYPE="xfs"
[root@test01 data]# cat /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Tue Sep 28 03:47:03 2021
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/rhel-root   /                       xfs     defaults        0 0
UUID=f5a37650-aa88-48fb-894f-3e6233d6bfd3 /boot                   xfs     defaults        0 0
/dev/mapper/rhel-home   /home                   xfs     defaults        0 0
/dev/mapper/rhel-var    /var                    xfs     defaults        0 0
/dev/mapper/rhel-swap   swap                    swap    defaults        0 0
UUID=0e7ce1c7-2458-41bc-98af-b5d00c0ec75a /data01                   xfs     defaults        0 0
[root@test01 data]# mount -a
[root@test01 data]# df -h
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/rhel-root   20G  4.6G   16G  23% /
devtmpfs               899M     0  899M   0% /dev
tmpfs                  911M     0  911M   0% /dev/shm
tmpfs                  911M  9.6M  902M   2% /run
tmpfs                  911M     0  911M   0% /sys/fs/cgroup
/dev/sda1              297M  126M  172M  43% /boot
/dev/mapper/rhel-var    10G  455M  9.6G   5% /var
/dev/mapper/rhel-home  5.0G   33M  5.0G   1% /home
tmpfs                  183M     0  183M   0% /run/user/0
/dev/sdb                10G   33M   10G   1% /data01

5. 授权

[root@test01 data]# chown -R mysql.mysql /aplication/*
[root@test01 data]# chown -R mysql.mysql /data

6. 初始化数据（创建系统数据）

[root@test01 data]# mkdir /data01/mysql/data -p
[root@test01 data]# chown -R mysql.mysql /data/*
[root@test01 data]# yum install -y libaio-devel
[root@test01 data]# mysqld --initialize --user=mysql --basedir=/aplication/mysql --datadir=/data01/mysql/data

7. 配置文件准备

[root@test01 data]# cat /etc/my.cnf 
[mysqld]
user=mysql
basedir=/aplication/mysql
datadir=/data01/mysql/data
socket=/tmp/mysql.sock
server_id=6
port=3306
[mysql]
socket=/tmp/mysql.sock

8. 启动数据库

[root@test01 data]# cp /aplication/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@test01 data]# service mysqld start
Starting MySQL.Logging to '/data01/mysql/data/test01.err'.
 SUCCESS! 

9. 测试数据库的连接

[root@test01 data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26

Copyright (c) 2000, 2019, 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> quit
Bye

mysql的启动报错日志/data01/mysql/data/test01.err(mysql数据目录加主机名.err）

设置一个管理员密码

[root@test01 data]# mysqladmin -uroot -p password 123456
Enter password: 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@test01 data]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> quit
Bye

管理员用户忘记密码

1. 关闭数据库

[root@test01 data]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 

2. 启动数据库到维护模式

[root@test01 data]# mysqld_safe --skip-grant-tables --skip-networking &

3. 登录并修改密码

[root@test01 data]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@'localhost' identified by '1';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

4. 退出维护模式，重启数据库