MySQL安装

[root@localhost mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
--2021-05-05 05:56:58--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
正在解析主机 downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
正在连接 downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz [跟随至新的 URL]
--2021-05-05 05:57:02--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 184.86.92.235
正在连接 cdn.mysql.com (cdn.mysql.com)|184.86.92.235|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：661718255 (631M) [application/x-tar-gz]
正在保存至: “mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz”

mysql-5.7.33-linux-glibc2 100%[=====================================>] 631.06M  5.20MB/s  用时 2m 2s   

2021-05-05 05:59:05 (5.16 MB/s) - 已保存 “mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz” [661718255/661718255])

创建用户

[root@localhost mysql]# useradd -r -M -s /sbin/nologin mysql

解压至指定目录

[root@localhost mysql]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local

创建软链接或者修改目录名

[root@localhost ~]# cd /usr/local
[root@localhost local]# mv mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ls
apache  apr  apr-util  bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

修改属主和属组

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 13 root  root  152 4月  27 04:44 apache
drwxr-xr-x.  6 root  root   58 4月  27 04:38 apr
drwxr-xr-x.  5 root  root   43 4月  27 04:40 apr-util
drwxr-xr-x.  2 root  root    6 5月  18 2020 bin
drwxr-xr-x.  2 root  root    6 5月  18 2020 etc
drwxr-xr-x.  2 root  root    6 5月  18 2020 games
drwxr-xr-x.  2 root  root    6 5月  18 2020 include
drwxr-xr-x.  2 root  root    6 5月  18 2020 lib
drwxr-xr-x.  3 root  root   17 4月  23 04:55 lib64
drwxr-xr-x.  2 root  root    6 5月  18 2020 libexec
drwxr-xr-x.  9 mysql mysql 129 5月   5 06:00 mysql
drwxr-xr-x.  2 root  root    6 5月  18 2020 sbin
drwxr-xr-x.  5 root  root   49 4月  23 04:55 share
drwxr-xr-x.  2 root  root    6 5月  18 2020 src

设置环境变量（因为不是用yum装的，找不到mysql程序）

[root@localhost mysql]# vim /etc/profile.d/mysql.sh
[root@localhost mysql]# cat /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost mysql]# . /etc/profile.d/mysql.sh
[root@localhost mysql]# which mysql
/usr/local/mysql/bin/mysql

创建存放数据的目录并修改属主属组
需要一个空间大的目录，或者将目录创建后将硬盘设备挂载在此目录上

[root@localhost mysql]# mkdir /opt/mysql_data
[root@localhost mysql]# chown -R mysql.mysql /opt/mysql_data/
[root@localhost mysql]# ll /opt
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月   5 06:11 mysql_data

初始化并保存密码

[root@localhost mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/mysql_data/
2021-05-05T10:12:31.870885Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-05T10:12:32.084213Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-05T10:12:32.114750Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-05T10:12:32.170393Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 67b16796-ad8a-11eb-a539-000c2951a382.
2021-05-05T10:12:32.171487Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-05T10:12:32.702774Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-05T10:12:32.767933Z 1 [Note] A temporary password is generated for root@localhost: 8?oTP!:G#iWh
[root@localhost mysql]# vim password
[root@localhost mysql]# cat password
8?oTP!:G#iWh

写配置文件

[root@localhost mysql]#  vim /etc/my.cnf
[root@localhost mysql]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql_data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql_data/mysql.pid
user = mysql
skip-name-resolve

配置启动脚本和开机自启

[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/mysql_data#g' /etc/init.d/mysqld
[root@localhost ~]# cd mysql
[root@localhost mysql]#  head -47 /etc/init.d/mysqld |tail -2
basedir=/usr/local/mysql
datadir=/opt/mysql_data
[root@localhost mysql]# service mysqld start
Starting MySQL.Logging to '/opt/mysql_data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost mysql]# chkconfig  mysqld on
[root@localhost mysql]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关
[root@localhost mysql]#  ss -anlt
State       Recv-Q      Send-Q           Local Address:Port           Peer Address:Port     Process     
LISTEN      0           128                    0.0.0.0:111                 0.0.0.0:*                    
LISTEN      0           32               192.168.122.1:53                  0.0.0.0:*                    
LISTEN      0           128                    0.0.0.0:22                  0.0.0.0:*                    
LISTEN      0           5                    127.0.0.1:631                 0.0.0.0:*                    
LISTEN      0           80                           *:3306                      *:*                    
LISTEN      0           128                       [::]:111                    [::]:*                    
LISTEN      0           128                          *:80                        *:*                    
LISTEN      0           128                       [::]:22                     [::]:*                    
LISTEN      0           5                        [::1]:631                    [::]:*        

头文件和库文件配置

[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@localhost mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib

[root@localhost mysql]# ldconfig         //重新读取数据文件

启动并设置密码

[root@localhost mysql]# cd /usr/local
[root@localhost local]#  mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

[root@localhost local]# dnf provides libncurses.so.5
上次元数据过期检查：0:02:46 前，执行于 2021年05月05日 星期三 06时18分44秒。
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
仓库        ：baseos
匹配来源：
提供    : libncurses.so.5

[root@localhost local]# dnf -y install ncurses-compat-libs
上次元数据过期检查：0:02:55 前，执行于 2021年05月05日 星期三 06时18分44秒。
软件包 ncurses-compat-libs-6.1-7.20180224.el8.x86_64 已安装。
依赖关系解决。
无需任何处理。
完毕！
                                                       
[root@localhost local]#  mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> set password for root@localhost = password('root');          //修改MySQL密码为root
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -proot
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 8
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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备份工具mysqldump

mysqldump 语法：

	mysqldump [OPTIONS] database [tables ...]												//备份数据表
    mysqldump [OPTIONS] --all-databases [OPTIONS]									//备份全部数据库
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]			//备份某个数据库

	-uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替

//备份整个数据库(全备)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huang              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use huang;
Database changed

mysql> show tables;
Empty set (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# ls
anaconda-ks.cfg  initial-setup-ks.cfg  mysql  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# mysqldump -uroot -p --all-databases > all-20210505.sql
Enter password: 
[root@localhost ~]# ls
all-20210505.sql  initial-setup-ks.cfg  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
anaconda-ks.cfg   mysql

备份某个数据库`

`[root@localhost ~]# mysqldump -uroot -p --databases huang > huang_db_20210505.sql
Enter password: 
[root@localhost ~]# ls
all-20210505.sql  huang_db_20210505.sql  mysql
anaconda-ks.cfg   initial-setup-ks.cfg   mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

mysql数据恢复

//模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huang              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database huang;
Query OK, 0 rows affected (0.00 sec)

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


//恢复数据库
[root@localhost ~]# ls
all-20210505.sql  huang_db_20210505.sql  mysql
anaconda-ks.cfg   initial-setup-ks.cfg   mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]#  mysql -uroot -p < huang_db_20210505.sql 
Enter password: 
[root@localhost ~]#  mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| huang              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+