016、数据库生态圈概述及部署实战案例
|
Mysql
|
23

1048 字
|
1 小时
本文最后更新于 257 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com

数据库生态圈概述及部署实战案例

一.数据库概述

1.什么是数据库

    数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

    推荐阅读:
        https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93/103728

2.数据库的分类

    RDBMS:
        Oracle, MySQL, Microsoft SQL Server(简称"MSSQL")，PostgreSQL(简称"PGSQL")，ClickHouse。

    NoSQL:
        MangoDB，Elasticsearch，Redis，Hbase

    云数据库:
        RDS(阿里云产品)，TDSQL(腾讯云产品)

    NewSQL:
        TiDB，PolarDB(阿里云)

    主流数据库使用排行榜:
        https://db-engines.com/en/ranking

3.MySQL企业版本选择

    建议选择MySQl 5.7和MySQL 8.0的最新版本进行下载安装哟。

    下载地址:
        https://downloads.mysql.com/archives/community/

二.部署MySQL数据库

1.创建MySQL相关的目录

    创建MySQL相关的目录:
        [root@mysql101.oldboyedu.com ~]# mkdir -pv /oldboyedu/{softwares,data,logs}/mysql

    创建的目录说明:
        "/oldboyedu/softwares/mysql": 
            存放MySQL应用程序。
        "/oldboyedu/data/mysql":
            存放MySQL数据。
        "/oldboyedu/logs/mysql":
            存放MySQL日志

2.创建运行MySQL服务的用户

[root@mysql101.oldboyedu.com ~]# getent passwd mysql
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# useradd -r -s /sbin/nologin -d /oldboyedu/data/mysql -c 'MySQL DataBase Server User' mysql  # 创建一个mysql系统用户
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/data/mysql/
drwxr-xr-x 2 root root 6 1月   7 22:22 /oldboyedu/data/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# install -d /oldboyedu/data/mysql -o mysql -g mysql  # 创建目录时同时指定属主和属组，若目录已经存在也没有关系，直接修改属主和数组!
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/data/mysql/
drwxr-xr-x 2 mysql mysql 6 1月   7 22:22 /oldboyedu/data/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# getent passwd mysql
mysql:x:997:993:MySQL DataBase Server User:/oldboyedu/data/mysql:/sbin/nologin
[root@mysql101.oldboyedu.com ~]#

3.将软件上传到"/oldboyedu/softwares/mysql"目录并解压创建相应的软连接

[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]# ll
总用量 367716
-rw-r--r-- 1 root root 376537503 1月   7 22:37 mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]#
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz  # 解压MySQL软件包的压缩文件
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]#
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]# ln -s mysql-5.7.31-linux-glibc2.12-x86_64 mysql  # 创建符号链接
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]#
[root@mysql101.oldboyedu.com /v/softwares/mysql]# ll -h
总用量 360M
lrwxrwxrwx 1 root root    35 1月   7 22:48 mysql -> mysql-5.7.31-linux-glibc2.12-x86_64
drwxr-xr-x 9 7161 31415  129 6月   2 2020 mysql-5.7.31-linux-glibc2.12-x86_64
-rw-r--r-- 1 root root  360M 1月   7 22:37 mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]#
[root@mysql101.oldboyedu.com /oldboyedu/softwares/mysql]#

4.修改环境变量

[root@mysql101.oldboyedu.com ~]# vim /etc/profile.d/mysql.sh
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# cat /etc/profile.d/mysql.sh
# Add by yinzhengjie
export PATH=/oldboyedu/softwares/mysql/mysql/bin:$PATH
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# source /etc/profile.d/mysql.sh
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.31, for linux-glibc2.12 (x86_64) using  EditLine wrapper
[root@mysql101.oldboyedu.com ~]#

5.修改MySQL运行用户需要的相关目录授权信息

[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/softwares/mysql/
drwxr-xr-x 3 root root 112 1月   7 22:48 /oldboyedu/softwares/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/data/mysql/  # 该目录在创建用户时我们已经使用install命令修改过授权啦~因此无需修改属主属组!
drwxr-xr-x 2 mysql mysql 6 1月   7 22:22 /oldboyedu/data/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/logs/mysql/
drwxr-xr-x 2 root root 6 1月   7 22:22 /oldboyedu/logs/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# chown -R mysql:mysql /oldboyedu/softwares/mysql/ /oldboyedu/logs/mysql/  # 仅需修改未授权的目录!
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/softwares/mysql/
drwxr-xr-x 3 mysql mysql 112 1月   7 22:48 /oldboyedu/softwares/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/data/mysql/
drwxr-xr-x 2 mysql mysql 6 1月   7 22:22 /oldboyedu/data/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/logs/mysql/
drwxr-xr-x 2 mysql mysql 6 1月   7 22:22 /oldboyedu/logs/mysql/
[root@mysql101.oldboyedu.com ~]#

5.删除遗留的其它MySQL分支(比如mariadb的依赖环境和配置文件等)

[root@mysql101.oldboyedu.com ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# yum -y remove mariadb-libs
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# rpm -qa | grep mariadb
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# rm -rf /etc/my.cnf*
[root@mysql101.oldboyedu.com ~]#

三.初始化数据库并设置管理员密码

1.生成数据库的初始数据

[root@mysql101.oldboyedu.com ~]# ll -d /oldboyedu/data/mysql/
drwxr-xr-x 2 mysql mysql 6 1月   7 22:22 /oldboyedu/data/mysql/
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll /oldboyedu/data/mysql/
总用量 0
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# mysqld --initialize-insecure --user=mysql --basedir=/oldboyedu/softwares/mysql/mysql --datadir=/oldboyedu/data/mysql
2021-01-07T15:15:38.205461Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-07T15:15:38.757442Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-07T15:15:38.910745Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-07T15:15:39.052014Z 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: 332ccee4-50fb-11eb-b153-000c29820c67.
2021-01-07T15:15:39.074696Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-07T15:15:39.810037Z 0 [Warning] CA certificate ca.pem is self signed.
2021-01-07T15:15:40.024595Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll /oldboyedu/data/mysql/
总用量 110660
-rw-r----- 1 mysql mysql       56 1月   7 23:15 auto.cnf
-rw------- 1 mysql mysql     1680 1月   7 23:15 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 1月   7 23:15 ca.pem
-rw-r--r-- 1 mysql mysql     1112 1月   7 23:15 client-cert.pem
-rw------- 1 mysql mysql     1680 1月   7 23:15 client-key.pem
-rw-r----- 1 mysql mysql      431 1月   7 23:15 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 1月   7 23:15 ibdata1
-rw-r----- 1 mysql mysql 50331648 1月   7 23:15 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 1月   7 23:15 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 1月   7 23:15 mysql
drwxr-x--- 2 mysql mysql     8192 1月   7 23:15 performance_schema
-rw------- 1 mysql mysql     1680 1月   7 23:15 private_key.pem
-rw-r--r-- 1 mysql mysql      452 1月   7 23:15 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 1月   7 23:15 server-cert.pem
-rw------- 1 mysql mysql     1680 1月   7 23:15 server-key.pem
drwxr-x--- 2 mysql mysql     8192 1月   7 23:15 sys
[root@mysql101.oldboyedu.com ~]#

温馨提示:
    我们使用mysqld命令有很多选项，常用的选项如下所示:
        --initialize-insecure:
            初始化一个数据库实例。
        --user：
            指定将来运行数据库实例的用户。
        --basedir:
            指定数据库实例程序的安装位置。
        --datadir:
            指定数据库实例的数据存放位置。

2.准备MySQL的启动脚本

    将MySQL内置的启动脚本拷贝到系统的脚本管理目录:
        [root@mysql101.oldboyedu.com ~]# ll /oldboyedu/softwares/mysql/mysql/support-files/
        总用量 24
        -rw-r--r-- 1 mysql mysql   773 6月   2 2020 magic
        -rwxr-xr-x 1 mysql mysql  1061 6月   2 2020 mysqld_multi.server
        -rwxr-xr-x 1 mysql mysql   894 6月   2 2020 mysql-log-rotate
        -rwxr-xr-x 1 mysql mysql 10576 6月   2 2020 mysql.server
        [root@mysql101.oldboyedu.com ~]#
        [root@mysql101.oldboyedu.com ~]# cp /oldboyedu/softwares/mysql/mysql/support-files/mysql.server /etc/init.d/mysqld
        [root@mysql101.oldboyedu.com ~]#
        [root@mysql101.oldboyedu.com ~]# chkconfig --add mysqld  # 设置开机自启动脚本
        [root@mysql101.oldboyedu.com ~]#

    在MySQL的脚本文件中指定MySQL数据的自定义安装路径:
        [root@mysql101.oldboyedu.com ~]# cp /etc/init.d/mysqld /etc/init.d/mysqld-`date +%F`
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# grep "^datadir=" /etc/init.d/mysqld
        datadir=
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# sed -r -i "s#^(datadir=)#\1/oldboyedu/data/mysql#" /etc/init.d/mysqld
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# grep "^datadir=" /etc/init.d/mysqld
        datadir=/oldboyedu/data/mysql
        [root@mysql101.oldboyedu.com ~]# 

    在MySQL的脚本文件中指定MySQL程序的自定义安装路径:
        [root@mysql101.oldboyedu.com ~]# grep "^basedir=" /etc/init.d/mysqld
        basedir=
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# sed -r -i "s#^(basedir=)#\1/oldboyedu/softwares/mysql/mysql#" /etc/init.d/mysqld
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# grep "^basedir=" /etc/init.d/mysqld
        basedir=/oldboyedu/softwares/mysql/mysql
        [root@mysql101.oldboyedu.com ~]# 

    重新加载启动脚本:
        [root@mysql101.oldboyedu.com ~]# systemctl daemon-reload  # 由于上面修改了启动脚本，因此我们要执行该命令重新加载一下！
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# systemctl restart mysqld
        [root@mysql101.oldboyedu.com ~]# 
        [root@mysql101.oldboyedu.com ~]# ss -ntl
        State       Recv-Q Send-Q                                                 Local Address:Port                                                                Peer Address:Port              
        LISTEN      0      128                                                                *:22                                                                             *:*                  
        LISTEN      0      80                                                              [::]:3306                                                                        [::]:*                  
        LISTEN      0      128                                                             [::]:22                                                                          [::]:*                  
        [root@mysql101.oldboyedu.com ~]#

3.准备配置文件

[root@mysql101.oldboyedu.com ~]# vim /etc/my.cnf
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/oldboyedu/softwares/mysql/mysql
datadir=/oldboyedu/data/mysql
socket=/tmp/mysql.sock

[mysql]
socket=/tmp/mysql.sock
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# touch /oldboyedu/logs/mysql/mysql.err  # 该文件必须创建，否则在下面执行启动数据库时会报错哟~
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll /oldboyedu/logs/mysql/mysql.err
-rw-r--r-- 1 root root 0 1月   7 23:42 /oldboyedu/logs/mysql/mysql.err
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# chown mysql:mysql /oldboyedu/logs/mysql/mysql.err
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ll /oldboyedu/logs/mysql/mysql.err
-rw-r--r-- 1 mysql mysql 0 1月   7 23:42 /oldboyedu/logs/mysql/mysql.err
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]#

温馨提示:
    以下是上述配置中参数概述:
        [mysqld]  # 配置MySQL守护进程的相关配置
        user=mysql  # 指定守护进程的用户名是mysql
        basedir=/oldboyedu/softwares/mysql/mysql  # 指定数据库实例的程序存储位置
        datadir=/oldboyedu/data/mysql  # 指定数据库实例的数据存储路径
        socket=/tmp/mysql.sock  # 指定数据库实例的本地的套接字文件

        [mysql]  # 配置MySQL客户端的相关配置
        socket=/tmp/mysql.sock  # 指定数据库实例的本地套接字文件，通常和mysqld中的套接字路径一致，因为DBA管理数据库实例时常用的工具就是字符终端的mysql客户端工具。

4.CentOS 6.x+ 系列启动数据库

[root@mysql101.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                     Local Address:Port                                                                    Peer Address:Port
LISTEN      0      128                                                                    *:22                                                                                 *:*
LISTEN      0      128                                                                 [::]:22                                                                              [::]:*
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                     Local Address:Port                                                                    Peer Address:Port
LISTEN      0      128                                                                    *:22                                                                                 *:*
LISTEN      0      128                                                                 [::]:22                                                                              [::]:*
LISTEN      0      80                                                                  [::]:3306                                                                            [::]:*
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                     Local Address:Port                                                                    Peer Address:Port
LISTEN      0      128                                                                    *:22                                                                                 *:*
LISTEN      0      128                                                                 [::]:22                                                                              [::]:*
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]#

5.CentOS 7.x+ 系列启动数据库

[root@mysql101.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                     Local Address:Port                                                                    Peer Address:Port
LISTEN      0      128                                                                    *:22                                                                                 *:*
LISTEN      0      128                                                                 [::]:22                                                                              [::]:*
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# systemctl start mysqld
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                     Local Address:Port                                                                    Peer Address:Port
LISTEN      0      128                                                                    *:22                                                                                 *:*
LISTEN      0      128                                                                 [::]:22                                                                              [::]:*
LISTEN      0      80                                                                  [::]:3306                                                                            [::]:*
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# systemctl stop mysqld
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# ss -ntl
State       Recv-Q Send-Q                                                     Local Address:Port                                                                    Peer Address:Port
LISTEN      0      128                                                                    *:22                                                                                 *:*
LISTEN      0      128                                                                 [::]:22                                                                              [::]:*
[root@mysql101.oldboyedu.com ~]#

6.测试MySQL是否可以正常连接

[root@mysql101.oldboyedu.com ~]# mysql  # 很明显，默认情况下是没有密码的！
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>
mysql> QUIT
Bye
[root@mysql101.oldboyedu.com ~]#

7.设定管理员密码

[root@mysql101.oldboyedu.com ~]# mysqladmin -uroot -p password oldboyedu  # 为我们的数据库设置密码为"oldboyedu"
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@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# mysql  # 我们再次使用空密码登录，发现登录失败啦！
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@mysql101.oldboyedu.com ~]#
[root@mysql101.oldboyedu.com ~]# mysql -uroot -poldboyedu  # 我们直接使用刚刚设置的密码进行登录，即可，但是需要注意的是，生产环境中不要将密码在命令行中直接输入哟~
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.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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>
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>
mysql> QUIT
Bye
[root@mysql101.oldboyedu.com ~]#

四.不同数据库版本的初始化数据库的方式各用不同！:star:

    MySQL 5.6版本初始化方式如下所示:(mysql_install_db脚本在MySQL安装目录的scripts目录下哟~)
        mysql_install_db --user=mysql --basedir=/oldboyedu/softwares/mysql/mysql --datadir=/oldboyedu/data/mysql

    MySQL 5.7+版本(包括MySQL 8.0+版本)均可以采用以下两种的方式进行初始化:
        生成密码为空的管理员用户：(本案例就是采用这种手段)
            mysqld --initialize-insecure --user=mysql --basedir=/oldboyedu/softwares/mysql/mysql --datadir=/oldboyedu/data/mysql
        生成一个12位的4种密码复杂度的临时管理用户，第一次登录时需要修改这个密码:
            mysqld --initialize --user=mysql --basedir=/oldboyedu/softwares/mysql/mysql --datadir=/oldboyedu/data/mysql

      重置密码:
        alter user user() identified by "123";

五.小彩蛋

1.优化PS1变量

vi /etc/profile.d/ps1.sh 
i_set_prompt () {

        #see:  http://misc.flogisoft.com/bash/tip_colors_and_formatting
        #Reset          \e[0m

        ## Formatting:
        #Bold           \e[1m
        #Dim            \e[2m
        #Underlined     \e[4m

        ## 8/16 Colors:         9X                      4X                      10X
        #Default fg     \e[39m                          Default bg
        #Black          \e[30m  Dark gray       bg Black        bg Dark gray
        #Red            \e[31m  Light           bg                      bg Light
        #Green          \e[32m  Light           bg                      bg Light
        #Yellow         \e[33m  Light           bg                      bg Light
        #Blue           \e[34m  Light           bg                      bg Light
        #Magenta        \e[35m  Light           bg                      bg Light
        #Cyan           \e[36m  Light           bg                      bg Light
        #Light gray     \e[37m  White           bg                      bg White

        _last_exit_code=$? # Must come first!
        C_EC='\[\e[1;37m\]'$(printf '%3s' ${_last_exit_code})'\[\e[0m\]'

        #todo: set encoding to UTF-8 !
        FancyX='\342\234\227'      # ✗ ✘
        Checkmark='\342\234\223'   # ✓ 
        C_Fail='\[\e[1;31m\]'${FancyX}'\[\e[0m\]'
        C_Ok='\[\e[32m\]'${Checkmark}'\[\e[0m\]'
        C_Time='\[\e[2;37m\]''\t''\[\e[0m\]'

        C_NormalUser='\[\e[2;33m\]''\u''\[\e[0m\]'
        C_RootUser='\[\e[1;35m\]''\u''\[\e[0m\]'

        if [ $(uname -s) == "Darwin" ]; then
                _ip_addr=$(ipconfig getifaddr $(netstat -nr | awk '{ if ($1 ~/default/) { print $6} }'))
        elif [ $(uname -s) == "Linux" ]; then
                # may print $(NF-2)
                #_ip_addr=$(ip route | awk '/ src / {print $NF}' | head -1 )
                _ip_addr=$(ip route | grep -oP '(?<=src )[0-9.]+' | tail -1 )
        fi

        C_Host='\[\e[1;33m\]'$(hostname -A | awk '{print $1}')'\[\e[0m\]','\[\e[4;32m\]'${_ip_addr:-\h}'\[\e[0m\]'
        C_Pwd='\[\e[36m\]''\w''\[\e[0m\]'
        C_Marker='\[\e[1;37m\]''\$''\[\e[0m\]'

        git diff --exit-code --quiet HEAD >/dev/null 2>&1
        _git_diff_exit_code=$?
        if [ ${_git_diff_exit_code} -eq 1 ]; then
                C_Marker='\[\e[101m\]'*'\[\e[0m\]'" ${C_Marker}"
        elif [ ${_git_diff_exit_code} -eq 0 ]; then
                C_Marker='\[\e[102m\]'='\[\e[0m\]'" ${C_Marker}"
        fi

        # Add a bright white exit status for the last command
        PS1="$C_EC "

        # If it was successful, print a green check mark.
        # Otherwise, print a red X.
        if [[ ${_last_exit_code} -eq 0 ]]; then
                PS1+="$C_Ok "
        else
                PS1+="$C_Fail "
        fi

        # print HH:ii:ss
        PS1+="$C_Time "

        # If root, just print the host in red. Otherwise,
        # print the current user and host in green.
        if [[ $EUID -eq 0 ]]; then
                PS1+="${C_RootUser}@${C_Host}:"
        else
                PS1+="${C_NormalUser}@${C_Host}:"
        fi

        # Print the working directory and prompt marker
        PS1+="$C_Pwd $C_Marker "
}
PROMPT_COMMAND='_set_prompt'

1630575591788

2.登陆提示案例1

rm -f  /etc/motd 
vi /etc/motd 
i 
                                  _oo0oo_
                                 088888880
                                 88" . "88
                                 (| -_- |)
                                  0\ = /0
                               ___/'---'\___
                             .' \\|     |// '.
                            / \\|||  :  |||// \
                           /_ ||||| -:- |||||- \
                          |   | \\\  -  /// |   |
                          | \_|  ''\---/''  |_/ |
                          \  .-\__  '-'  __/-.  /
                        ___'. .'  /--.--\  '. .'___
                     ."" '<  '.___\_<|>_/___.' >'  "".
                    | | : '-  \'.;'\ _ /';.'/ - ' : | |
                    \  \ '_.   \_ __\ /__ _/   .-' /  /
                ====='-.____'.___ \_____/___.-'____.-'=====
                                  '=---='

              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                  佛祖保佑    oldboyedu-linux76    永不死机

3.登陆提示案例2

rm -f  /etc/motd 
vi /etc/motd 
i     .=""=.
    / _  _ \ 
   |  d  b  |    
   \   /\   /    
  ,/'-=\/=-'\,    
 / /        \ \   
| /          \ | 
\/ \        / \/  
    '.    .'    
    _|`~~`|_    
    /|\  /|\

4.登陆提示案例3

rm -f  /etc/motd 
vi /etc/motd 
i
  ┌───┐   ┌───┬───┬───┬───┐ ┌───┬───┬───┬───┐ ┌───┬───┬───┬───┐ ┌───┬───┬───┐ ┌───────────────┐
  │Esc│   │ F1│ F2│ F3│ F4│ │ F5│ F6│ F7│ F8│ │ F9│F10│F11│F12│ │P/S│S L│P/B│ │Oldboyedu Linux│
  └───┘   └───┴───┴───┴───┘ └───┴───┴───┴───┘ └───┴───┴───┴───┘ └───┴───┴───┘ └───────────────┘
  ┌───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───┬───────┐ ┌───┬───┬───┐ ┌───┬───┬───┬───┐
  │~ `│! 1│@ 2│# 3│$ 4│% 5│^ 6│& 7│* 8│( 9│) 0│_ -│+ =│ BacSp │ │Ins│Hom│PUp│ │N L│ / │ * │ - │
  ├───┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─────┤ ├───┼───┼───┤ ├───┼───┼───┼───┤
  │ Tab │ Q │ W │ E │ R │ T │ Y │ U │ I │ O │ P │{ [│} ]│ | \ │ │Del│End│PDn│ │ 7 │ 8 │ 9 │   │
  ├─────┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴┬──┴─────┤ └───┴───┴───┘ ├───┼───┼───┤ + │
  │ Caps │ A │ S │ D │ F │ G │ H │ J │ K │ L │: ;│" '│ Enter  │               │ 4 │ 5 │ 6 │   │
  ├──────┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴─┬─┴────────┤     ┌───┐     ├───┼───┼───┼───┤
  │ Shift  │ Z │ X │ C │ V │ B │ N │ M │< ,│> .│? /│  Shift   │     │ ↑ │     │ 1 │ 2 │ 3 │   │
  ├─────┬──┴─┬─┴──┬┴───┴───┴───┴───┴───┴──┬┴───┼───┴┬────┬────┤ ┌───┼───┼───┐ ├───┴───┼───┤ E││
  │ Ctrl│    │Alt │         Space         │ Alt│    │    │Ctrl│ │ ← │ ↓ │ → │ │   0   │ . │←─┘│
  └─────┴────┴────┴───────────────────────┴────┴────┴────┴────┘ └───┴───┴───┘ └───────┴───┴───┘

5.登陆提示案例4

rm -f  /etc/motd 
vi /etc/motd 
i

                           oldboyedu~linux76~biubui~

                        　　 へ　　　　　／|
                        　　/＼7　　　 ∠＿/
                        　 /　│　　 ／　／
                        　│　Z ＿,＜　／　　 /`ヽ
                        　│　　　　　ヽ　　 /　　〉
                        　 Y　　　　　`　 /　　/
                        　●　　●　　〈　　/
                        　()　 へ　　　　|　＼〈
                        　　> _　 ィ　 │ ／／
                        　 / へ　　 /　＜| ＼＼
                        　 ヽ_　　(_／　 │／／
                        　　7　　　　　　　|／
                        　　＞―r￣￣`―＿

六.可能会遇到的错误

1.mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

    报错信息:
        mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

    报错原因分析:
        缺少libaio.so依赖库

    解决方案:
        安装libaio.so对应的依赖库即可，以CentOS 7.9.2009为例，解决方案为: "yum -y install libaio"

2.[ERROR] --initialize specified but the data directory has files in it. Aborting.

    报错信息:
        2021-01-07T15:16:53.297807Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

    报错原因分析:
        初始化数据时，数据目录不为空，从而导致的报错。这种情况多发生于指定的"--datadir"目录不为空，或者多次执行了"mysqld --initialize-insecure --datadir=..."相关的指令。

    解决方案:
        这种情况下，要么停止初始化数据库(因为你可能这是一个误操作)，要么使用rm命令将"--datadir"指向的目录中文件全部删除，请根据实际情况做出选择。

image-20210709183150603

3.Starting MySQL.2021-01-07T15:36:07.097608Z mysqld_safe error: log-error set to '/oldboyedu/logs/mysql/mysql.log', however file don't exists. Create writable for user 'mysql'.

    报错信息:
        Starting MySQL.2021-01-07T15:36:07.097608Z mysqld_safe error: log-error set to '/oldboyedu/logs/mysql/mysql.log', however file don't exists. Create writable for user 'mysql'.

    报错原因分析:
        报错已经很明显了，说是我们使用了log-error关键字定义了'/oldboyedu/logs/mysql/mysql.log'，但启动程序时发现该文件不存在！
        这里我就得吐槽一下了，可以大胆的猜测一下，其打开文件的方式应该是以追加的方式打开，但是文件不存在程序竟然不会自动创建文件！这一点我不明白为什么程序员要这样设计!

    解决方案:
        既然找到原因了，那就参考我上面的方式，使用touch命令创建出'/oldboyedu/logs/mysql/mysql.log'文件，别忘记最后要修改权限，因为运行用户是mysql。

4.ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

    报错信息:
        ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

    报错原因分析:
        说是没有办法通过'/tmp/mysql.sock'套接字来连接到MySQL服务。这种情况下常见的原因有两种: 一种是有人恶意删除了该套接字文件，另外一种是MySQL Server服务压根没有启动!

    解决方案:
        依次做如下检查:
            (1)'/tmp/mysql.sock'文件是否存在;
            (2)检查MySQL Server服务是否启动成功;
            (3)查看"my.cnf"配置文件，是否mysqld字段和mysql字段配置的套接字路径不一致;

5.FATAL ERROR: please install the following Perl modules before executing ./mysql_install_db

    故障原因:
        缺少Perl模块。

    解决方案:
        安装autoconf即可，CentOS执行"yum -y install autoconf"。

image-20210709154903692

6.ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

    故障原因:
        启动时使用了"--initialize"参数，因此会强制要求修改密码。临时密码并不安全。

    解决方案:
        alter user user() identified by "123";

    温馨提示:
        如下图所示，我们解决方案是针对MySQL 8.0版本。

image-20210709181416089

7.mysqld[23966]: /etc/rc.d/init.d/mysqld: line 259: cd: /oldboyedu/softwares/mysql/mysql: No such file or directory

    故障原因:
        my.cnf配置文件写错了。

    解决方案:
        如果启动脚本中有指定datadir或者basedir，可以不配置my.cnf，如果配置my.cnf则其优先级较高，而脚本的配置不会生效。

image-20210709181912904

谨此笔记，记录过往。凭君阅览，如能收益，莫大奢望。
MySQL基础篇
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																

							
							
						

																

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇 

                    

                    
			        推荐文章
		            

		            

							
002、Mycat配置

							
							

							
010、MySQL触发器

							
							

							
021、MySQL的MHA高可用架构部署实战案例

							
							

							
012、MySQL InnoDB引擎

							
							

							
009、MySQL常用备份工具