MySQL第二次作业–库表操作 ;
1、使用源码安装MySQL 5.7。
2、创建数据库school,字符集为utf8
3、在school数据库中创建Student和Score表
4、授权用户tom,密码mysql,能够从任何地方登录并管理数据库school。
5、使用mysql客户端登录服务器,重置root密码
1、使用源码安装MySQL 5.7。
(1)安装mysql的压缩包
root@localhost ~]# wget -c https://repo.huaweicloud.com/mysql/Downloads/MySQL-5.7/mysql-boost-5.7.37.tar.gz
(2)安装开发工具和开发包
[root@localhost ~]# yum install make gcc gcc-c++ cmake bison-devel ncurses-devel -y
[root@localhost ~]#yum install libaio libaio-devel openssl-devel -y
[root@localhost ~]#yum install perl-Data-Dumper -y
[root@localhost ~]#yum install net-tools -y
(3)解压安装MySQL
[root@localhost ~]#tar xf mysql-boost-5.7.37.tar.gz -C /usr/local/src/
(4)创建用户和组
[root@localhost ~]#cd /usr/local/src/mysql-5.7.37/
[root@localhost mysql-5.7.37]#groupadd -r -g 306 mysql
[root@localhost mysql-5.7.37]#useradd -r -g mysql -u 306 -s /bin/false mysql
(5)添加cmake内容
[root@localhost ~]# cmake -DWITH_BOOST=/usr/local/src/mysql-5.7.37/boost \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DSYSCONFDIR=/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data
(6)编译安装
[root@localhost mysql-5.7.37]#make && make install
(7)为mysql添加主配置文件
[root@localhost ~]# cp /usr/local/src/mysql-5.7.37/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# vim /etc/my.cnf
#修改这四行数据内容
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=mysqld.pid
(8)为mysql提供sysv服务脚本
[root@localhost ~]# ll /etc/init.d/mysqld
-rw-r--r--. 1 root root 10576 Mar 27 05:19 /etc/init.d/mysqld
[root@localhost ~]# chmod +x /etc/init.d/mysqld
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig mysqld on
(9)配置环境变量和加载
[root@localhost ~]# echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
(10)初始化数据库即可
[root@localhost ~]# mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
2、创建数据库school,字符集为utf8
创建数据库
mysql> create database school default character set utf8 collate utf8_general_ci;
3、在school数据库中创建Student和Score表
mysql> create table Student(
-> id int(10) 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> create table Score(
-> id int(10) primary key auto_increment comment '编号',
-> stu_id int(10) not null comment '学号',
-> c_name varchar(20) comment '课程名',
-> grade int(10) comment '分数'
-> );
4、授权用户tom,密码mysql,能够从任何地方登录并管理数据库school。
(1)创建用户前先查看密码策略;
(2)在配置文件中添加修改策略
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
validate_password=off
(3)重启服务查看密码策略
mysql> show variables like "%password%";
查看密码策略
(4)授权用户和给予所有权限
mysql> create user 'tom'@'%' identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on school to 'tom'@'%';
Query OK, 0 rows affected (0.00 sec)
(5)进行连接测试;