MySQL 第二次练习（源码安装、数据库和表的建立）

1、使用源码安装MySQL 5.7。 

安装依赖包和开发包：

[root@localhost ~]# yum install make gcc gcc-c++ cmake bison-devel ncurses-devel -y
[root@localhost mysql-5.7.36]# yum install  openssl-devel -y

 上传从官网下载的源码到虚拟机：

[root@localhost ~]# ls
anaconda-ks.cfg  mysql-boost-5.7.36.tar.gz

创建组和用户：

[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -r -g mysql -s /bin/false mysql

 解压：

[root@localhost ~]# tar xf mysql-boost-5.7.36.tar.gz -C /usr/local/src/

 编译安装：

[root@localhost ~]# cd /usr/local/src/mysql-5.7.36/
[root@localhost mysql-5.7.36]# mkdir -p /data/mysql
[root@localhost mysql-5.7.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DSYSCONFDIR=/etc -DWITH_BOOST=/usr/local/src/mysql-5.7.36/boost
[root@localhost mysql-5.7.36]# make
[root@localhost mysql-5.7.36]# make install

 为MySQL提供一个服务脚本

[root@localhost mysql-5.7.36]# cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/
[root@localhost mysql-5.7.36]# chkconfig --add mysql.server  //添加为系统服务
[root@localhost mysql-5.7.36]# chkconfig  mysql.server on //设置开机自启动

 设置环境变量：

[root@localhost mysql-5.7.36]# vi /etc/profile.d/mysql.sh
添加如下：
export PATH=$PATH:/usr/local/mysql/bin

[root@localhost mysql-5.7.36]# source /etc/profile.d/mysql.sh

初始化：

[root@localhost mysql-5.7.36]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

修改：

[root@localhost mysql-5.7.36]# vi /etc/my.cnf
datadir=/data/mysql 
socket=/tmp/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/data/mysql/mysqld.pid

启动服务：

[root@localhost mysql-5.7.36]# systemctl start mysql.server

 临时登录：

[root@localhost mysql-5.7.36]# mysql -uroot -p'f0&%#l_6fUn!'
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 2
Server version: 5.7.36

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> 

2、创建数据库school，字符集为utf8 

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> \q
Bye

 修改字符集为UTF8

mysql> alter database school character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> show create database school;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

3、在school数据库中创建Student和Score表 

mysql> use school
Database changed
mysql> create table Student
    -> (Id int primary key auto_increment
    -> comment '学号',
    -> Name varchar(20) not null
    -> comment '姓名',
    -> Sex varchar(4) 
    -> comment '性别',
    -> Birth year 
    -> comment '出生年份',
    -> Department varchar(20) not null
    -> comment '院系',
    -> Address varchar(50)
    -> comment '家庭住址');
mysql> desc Student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| Id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name       | varchar(20) | YES  |     | NULL    |                |
| Sex        | varchar(4)  | YES  |     | NULL    |                |
| Birth      | year(4)     | YES  |     | NULL    |                |
| Department | varchar(20) | NO   |     | NULL    |                |
| Address    | varchar(50) | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> create table Score
    -> (Id int primary key auto_increment comment '编号',
    -> Stu_id int not null comment '学号',
    -> C_name varchar(20) comment '课程名',
    -> Grade int comment '分数');
Query OK, 0 rows affected (0.00 sec)

mysql> desc Score;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| Id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| Stu_id | int(11)     | NO   |     | NULL    |                |
| C_name | varchar(20) | YES  |     | NULL    |                |
| Grade  | int(11)     | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

4、授权用户tom，密码mysql，能够从任何地方登录并管理数据库school。 

mysql> create user 'tom'@'%' identified by 'MySQL@123';
Query OK, 0 rows affected (0.20 sec)

mysql> grant all on school to tom@'%';
Query OK, 0 rows affected (0.55 sec)

5、使用mysql客户端登录服务器，重置root密码

[root@localhost mysql]# mysql -uroot -p'f0&%#l_6fUn!'
mysql> alter user root@localhost identified by 'MySQL@123';
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye
 
 
[root@localhost ~]# mysql -uroot -p'MySQL@123'
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 21
Server version: 5.7.36 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.