mysql在test库中创建表stu_mysql安装 创建库和表 mysql数据类型

Top

NSD DBA1 DAY01

1 构建MySQL服务器

1.1 问题

本案例要求熟悉MySQL官方安装包的使用，快速构建一台数据库服务器：

安装MySQL-server、MySQl-client软件包

修改数据库用户root的密码

确认MySQL服务程序运行、root可控

1.2 方案

本课程将使用64位的RHEL 7操作系统，MySQL数据库的版本是5.7.17。

访问http://dev.mysql.com/downloads/mysql/，找到MySQL Community Server下载页面，平台选择“Red Hat Enterprise Linux 7/ Oracle Linux”，然后选择64位的bundle整合包下载，如图-1所示。

图-1

注意：下载MySQL软件时需要以Oracle网站账户登录，如果没有请根据页面提示先注册一个(免费)

1.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：准备工作

1)停止mariadb服务

[root@localhost ~]# systemctl stop mariadb

2)删除/etc/my.cnf配置文件

此配置文件由RHEL自带的mariadb-libs库提供：

[root@localhost ~]# rm -rf /etc/my.cnf

3)删除数据

[root@localhost ~]# rm -rf /var/lib/mysql/*

4)卸载软件包(没有会显示未安装软件包)

[root@localhost ~]# rpm -e --nodeps mariadb-server mariadb

警告：/var/log/mariadb/mariadb.log 已另存为/var/log/mariadb/mariadb.log.rpmsave

步骤二：安装软件包

1)安装mysql时可能会缺少某些依赖包，需提前单独安装

[root@localhost ~]# yum -y install perl-Data-Dumper perl-JSON perl-Time-HiRes

2)物理机传输解压包给虚拟机192.168.4.1

[root@room9pc01 ~]# cd 桌面

[root@room9pc01 桌面]# scp mysql-5.7.17.tar 192.168.4.1:/root/ //给虚拟机传包

root@192.168.4.1's password:

mysql-5.7.17.tar 100% 543MB 95.6MB/s 00:05

3)虚拟机192.168.4.1解压mysql-5.7.17.tar 整合包

[root@localhost ~]# tar -xvf mysql-5.7.17.tar //解压mysql整合包

./mysql-community-client-5.7.17-1.el7.x86_64.rpm

./mysql-community-common-5.7.17-1.el7.x86_64.rpm

./mysql-community-devel-5.7.17-1.el7.x86_64.rpm

./mysql-community-embedded-5.7.17-1.el7.x86_64.rpm

./mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm

./mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm

./mysql-community-libs-5.7.17-1.el7.x86_64.rpm

./mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm

./mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm

./mysql-community-server-5.7.17-1.el7.x86_64.rpm

./mysql-community-test-5.7.17-1.el7.x86_64.rpm

步骤三：启动MySQL数据库服务并设置开机自启

[root@localhost ~]# systemctl start mysqld //启动mysql服务

[root@localhost ~]# systemctl enable mysqld //设置开机自启

[root@localhost ~]# systemctl status mysqld //查看mysql服务状态

● mysqld.service - MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since 二 2018-08-28 10:03:24 CST; 8min ago

Docs: man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Main PID: 4284 (mysqld)

CGroup: /system.slice/mysqld.service

└─4284/usr/sbin/mysqld --daemonize --pid-file=/var/r...

8月 28 10:02:56 localhost.localdomain systemd[1]: Starting MySQ...

8月 28 10:03:24 localhost.localdomain systemd[1]: Started MySQL...

Hint: Some lines were ellipsized, use -l to show in full.

步骤四：连接MySQL服务器，修改密码

查看随机生成的root管理密码

[root@localhost ~]#grep 'temporary password' /var/log/mysqld.log

2017-04-01T18:10:42.948679Z 1 [Note] A temporary password is generated for root@localhost: mtoa>AvAv

2)使用客户端命令mysql连接到MySQL服务器

提示验证时，填入前一步获得的随机密码，验证成功后即可进入“mysql> ”环境：

[root@localhost ~]# mysql -u root -p'mtoa>Av

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 11

Server version: 5.7.17

Copyright (c) 2000, 2016, 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>                                     //登录成功后，进入SQL操作环境

用该密码登录到服务端后，必须马上修改密码，不然会报如下错误：

mysql> show databases;

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

3)执行SET PASSWORD命令修改密码

这个其实与validate_password_policy的值有关，默认为1，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。如果我们不希望密码设置的那么复杂，需要修改两个全局参数：validate_password_policy与validate_password_length。validate_password_length默认值为8,最小值为4，如果你显性指定validate_password_length的值小于4，尽管不会报错，但validate_password_length的值将设为4。

可参考下列指令：

mysql>set global validate_pas