MySQL数据库搭建

MySQL安装

  1. 安装信息

    • 操作系统:centos8
    • MySQL版本:8.0.19
    • 安装方式:rpm
  2. 安装

    • 上传安装文件到服务器
    • 使用root用户安装,自动创建mysql用户

    • 客户端

    1
    2
    3
    rpm -ivh mysql-community-common-8.0.19-1.el8.x86_64.rpm 
    rpm -ivh mysql-community-libs-8.0.19-1.el8.x86_64.rpm
    rpm -ivh mysql-community-client-8.0.19-1.el8.x86_64.rpm
    • 服务端

      1
      rpm -ivh mysql-community-server-8.0.19-1.el8.x86_64.rpm
  3. 初始化

    mysqld —initialize

  4. 修改用户组

    chown -R mysql:mysql /var/lib/mysql

  5. 检查进程状态

    systemctl status mysqld

  6. 启动

    systemctl start mysqld

  7. 查看root密码

    cat /var/log/mysqld.log | grep password
    2020-07-06T06:18:47.050833Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kaUb3H9p!OlG

  8. MySQL安全配置

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    mysql_secure_installation

    Securing the MySQL server deployment.

    Enter password for user root:

    The existing password for the user account root has expired. Please set a new password.

    New password:

    Re-enter new password:

    VALIDATE PASSWORD COMPONENT can be used to test passwords
    and improve security. It checks the strength of password
    and allows the users to set only those passwords which are
    secure enough. Would you like to setup VALIDATE PASSWORD component?

    Press y|Y for Yes, any other key for No: no
    Using existing password for root.
    Change the password for root ? ((Press y|Y for Yes, any other key for No) : no

    ... skipping.
    By default, a MySQL installation has an anonymous user,
    allowing anyone to log into MySQL without having to have
    a user account created for them. This is intended only for
    testing, and to make the installation go a bit smoother.
    You should remove them before moving into a production
    environment.

    Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
    Success.


    Normally, root should only be allowed to connect from
    'localhost'. This ensures that someone cannot guess at
    the root password from the network.

    Disallow root login remotely? (Press y|Y for Yes, any other key for No) : no

    ... skipping.
    By default, MySQL comes with a database named 'test' that
    anyone can access. This is also intended only for testing,
    and should be removed before moving into a production
    environment.


    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
    - Dropping test database...
    Success.

    - Removing privileges on test database...
    Success.

    Reloading the privilege tables will ensure that all changes
    made so far will take effect immediately.

    Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
    Success.

    All done!
  1. 检查客户端连接

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    mysql -uroot -p

    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 11
    Server version: 8.0.19 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>
  2. 设置Mysql数据库root密码

    1
    2
    3
    mysql> use mysql
    mysql> update user set authentication_string='' where user = 'root';
    mysql> alter user 'root'@'localhost' identified by 'Root@123';
  3. 测试新密码是否生效:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    mysql> exit

    # mysql -uroot -p
    Enter password: [输入新设置的密码]
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 12
    Server version: 8.0.19 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>
  4. 开启MySQL远程访问

    1. 一般情况不允许,不安全

    2. 连接mysql服务器

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      mysql> use mysql
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A

      Database changed
      mysql> select user,host from user;
      +------------------+-----------+
      | user | host |
      +------------------+-----------+
      | mysql.infoschema | localhost |
      | mysql.session | localhost |
      | mysql.sys | localhost |
      | root | localhost |
      +------------------+-----------+
      4 rows in set (0.00 sec)
    3. 更新user表中的localhost为“%”

      1
      mysql> update user set host='%' where user = 'root';
    4. root用户授权

      1
      2
      msyql> alter user 'root'@'%' identified with mysql_native_password by 'Root@123';
      mysql> flush privileges;
    5. 测试远程连接

      1
      mysql -h 192.168.1.103 -uroot -p

      数据库客户端工具测试

      1. Navicat
      2. DataGrip
      3. HeidiSQL(开源)
      4. MySQL官方工具

      MySQL存储引擎

      1. MyISAM:不支持事务,查询速度快
      2. InnoDB:默认存储引擎,全事务支持引擎
      3. Memory:内存存储引擎,速度太快
  5. 导入SQL脚本

    1
    2
    3
    4
    mysql> create database world;
    mysql> use world
    mysql> source /root/tools/MySQL-8.0.19/world.sql
    msyql> show tables;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> show create table city\G
    *************************** 1. row ***************************
    Table: city
    Create Table: CREATE TABLE `city` (
    `ID` int NOT NULL AUTO_INCREMENT,
    `Name` varchar(40) NOT NULL DEFAULT '',
    `CountryCode` varchar(5) NOT NULL DEFAULT '',
    `District` varchar(30) NOT NULL DEFAULT '',
    `Population` int NOT NULL DEFAULT '0',
    PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

    mysql>
作者

江风引雨

发布于

2020-07-22

更新于

2020-08-21

许可协议

CC BY 4.0

评论