推荐步骤:
➢在 Centos01 上安装 Mysql 数据库服务,生成服务器其配置文件,添加系统服务优化命令初始化 mysql,设置访问密码登录 mysql 数据库
➢ 在 Centos01 的 mysql 服务器上创建数据库,数据库创建表修改表中数据
➢ 授权用户访问 Mysql 数据库增强数据库安全和数据库远程管理
实验步骤:
一部署Mysql数据库系统
1,配置yum仓库安装依赖软件
1) 挂载系统盘
2) 挂载系统光盘到/mnt目录
[root@centos01 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos01 ~]# ls /mnt/
CentOS_BuildTag GPL LiveOS RPM-GPG-KEY-CentOS-7EFI images
Packages RPM-GPG-KEY-CentOS-Testing-7 EULA isolinux repodata TRANS.TBL
3) 删除系统自带yum仓库配置本地yum仓库
[root@centos01]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos01]# ls /etc/yum.repos.d/
local.repo
[root@centos01]# vim /etc/yum.repos.d/local.repo
[local]
name=centos
baseurl=file:///mnt
enabled=1
gpgcheck=0
4) 安装依赖程序
[root@centos01~]# yum -y install ncurses-devel cmake
2,切换mysql程序光盘解压源代码程序
1) 卸载系统光盘挂载点
[root@centos01 ~]# umount /mnt/
[root@centos01 ~]# ls /mnt/
2) 切换mysql程序光盘
3)挂载程序光盘
[root@centos01 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos01 ~]# ls -ld /mnt/mysql-5.5.22.tar.gz
-r-xr-xr-x 1 root root 24475686 6月 26 2014 /mnt/mysql-5.5.22.tar.gz
4) 解压代码程序到/usr/src目录
[root@centos01 ~]# tar zxvf /mnt/mysql-5.5.22.tar.gz -C /usr/src/
[root@centos01 ~]# ls /usr/src/
debug kernels mysql-5.5.22
3,配置安装mysql
1) 配置mysql
[root@centos01 ~]# cd /usr/src/mysql-5.5.22/
[root@centos01 mysql-5.5.22]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\
-DSYSCONFDIR=/etc/ \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all
2) 编译安装mysql
[root@centos01 mysql-5.5.22]# make && make install
[root@centos01 mysql-5.5.22]# ls -ld /usr/local/mysql/
drwxr-xr-x 13 root root 213 3月 12 02:09 /usr/local/mysql/
3) 创建管理mysql组和用户
[root@centos01 mysql-5.5.22]# groupadd mysql
[root@centos01 mysql-5.5.22]# useradd -M -s /sbin/nologin mysql -g mysql
4) 修改目录的使用者
[root@centos01 mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql/
[root@centos01 mysql-5.5.22]# ls -ld /usr/local/mysql/
drwxr-xr-x 13 mysql mysql 213 3月 12 02:09 /usr/local/mysql/
5) 生成mysql主配置文件覆盖原有文件
[root@centos01 mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? y
6) 生产服务器添加执行权限添加系统服务设置开机自动启动
[root@centos01 mysql-5.5.22]# cp support-files/mysql.server /etc/init.d/mysqld
[root@centos01 mysql-5.5.22]# chmod +x /etc/init.d/mysqld
[root@centos01 mysql-5.5.22]# chkconfig --add mysqld
[root@centos01 mysql-5.5.22]# chkconfig --level 35 mysqld on
7)优化mysql执行命令
[root@centos01 mysql-5.5.22]# echo "PATH=$PATH:/usr/local/mysql/bin/" >> /etc/profile
[root@centos01 mysql-5.5.22]# source /etc/profile
8) 初始mysql
[root@centos01 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql
--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
4,控制mysql服务mysql数据库设置密码
1) 启动mysql服务
[root@centos01]# systemctl start mysqld
[root@centos01]# netstat -anptu | grep mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:*
LISTEN 51176/mysqld
2) 空密码登录mysql数据库和退出
[root@centos01]# mysql -urot -p
Enter password:
mysql>exit
Bye
3) 设置mysql密码使用账户管理登录
[root@centos01]# mysqladmin -uroot password
Newpassword:
Confirm new password:
二,mysql数据库标和记录管理
1,数据库管理
1) 登录mysql创建数据库名字YY123
[root@centos01 ~]# mysql -uroot -ppwd@123
mysql> create database HB3060;
Query OK, 1 row affected (0.00 sec)
2) 查看创建的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| HB3060 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
3) 切换到创建HB3060数据库和mysql数据库
mysql> use HB3060;
Database changed
mysql> use mysql;
Database changed
4) 删除HB3060数据库查看创建的数据库
mysql> drop database HB3060;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
2,数据库的管理
1) 创建accp数据库,在accp数据库创建student表格设置表格4列数据库三类为字符串1列数据库为整数类
mysql> create database accp;
Query OK, 1 row affected (0.00 sec)
mysql> create table accp.student (姓名 char(5),年龄 int, 身份证号码 char(18),primary key(身份证号码));
Query OK, 0 rows affected (0.00 sec)
2) 查看创建的表格结构
mysql> desc accp.student;
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| 姓名 | char(4) | YES | | NULL | | |
| 年龄 | int(11) | YES | | NULL | | |
| 电话 | char(11) | YES | | NULL | | |
| 身份证号码 | char(18) | NO | PRI | |
+-----------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3) 切换到accp数据库,查看创建的表
mysql> use accp;
Database changed
mysql> show tables;
+------------------+
| Tables_in_accp |
+------------------+
| student |
+------------------+
2 rows in set (0.00 sec)
4)删除创建的 student 表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)
3,表中记录管理
1) student表中插入连续列数据
mysql> insert into accp.student values (‘uu’',18,'13161295986','111345679124689018');
Query OK, 1 row affected (0.01 sec)
2) student表中插入不连续列数据
mysql> insert into accp.student (姓名,身份证号码) values ('YY','110678654108765416');
Query OK, 1 row affected (0.01 sec)
3) 查看student表中所有数据
mysql> select * from accp.student;
+-----------+--------+-------------+--------------------+
| 姓名 | 年龄 | 电话 | 身份证号码 |
+-------------+--------+-------------+------------------+
| UU | NULL | NULL | 110678654108765416 |
| YY | 18 | 13161295986 | 111345679124689018 |
+-----------+--------+-------------+--------------------+
2 rows in set (0.00 sec)
4)查看student标的姓名和身份证号码
mysql> select 姓名,身份证号码 from accp.student;
+-----------+--------------------+
| 姓名 | 身份证号码 |
+-----------+--------------------+
|UU| 110678654108765416 |
|YY| 111345679124689018 |
+-----------+--------------------+
2 rows in set (0.00 sec)
5)修改student表中数据给UU添加年龄和电话
mysql> update accp.student set 电话='15810350733',年龄=21 where 姓名='UU';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
6) 查看修改数据
mysql> select * from accp.student;
+-----------+--------+-------------+--------------------+
| 姓名 | 年龄 | 电话 | 身份证号码 |
+-----------+--------+-------------+--------------------+
| UU | 21 | 15810350733 | 110678654108765416 |
| YY | 18 | 13161295986 | 111345679124689018 |
+-----------+--------+-------------+--------------------+
2 rows in set (0.00 sec)
7) 查看姓名是uu的数据
mysql> select 姓名,身份证号码 from accp.student where 姓名='胡炎';
+--------+--------------------+
| 姓名 | 身份证号码 |
+--------+--------------------+
| UU | 110678654108765416|
+--------+--------------------+
1 row in set (0.00 sec
8) 删除accp数据库的student中记录名字是UU的记录
mysql> select * from accp.student;
+-----------+--------+-------------+--------------------+
| 姓名 | 年龄 | 电话 | 身份证号码 |
+-----------+--------+-------------+--------------------+
| UU | 21 | 15810350733 | 110678654108765416 |
| YY | 18 | 13161295986 | 111345679124689018 |
+-----------+--------+-------------+--------------------+
2 rows in set (0.00 sec)
mysql> delete from accp.student where 姓名='YY';
Query OK, 1 row affected (0.01 sec)
mysql> select * from accp.student;
+-----------+--------+-------------+--------------------+
| 姓名 | 年龄 | 电话 | 身份证号码 |
+-----------+--------+-------------+--------------------+
| UU | 18 | 13161295986 | 111345679124689018 |
+-----------+--------+-------------+--------------------+
row in set (0.00 sec)
三,授权用户管理数据库和远程管理数据库修改数据库密码
1,授权和撤销授权的配置
1) 授权用户bob对accp数据库下的所有表拥有完全控制权限通过远程计算机192.168.100.20访问
mysql> grant all on accp.* to 'bob'@'192.168.100.20' identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
2) 查看授权的bob用户权限
mysql> show grants for bob@192.168.100.20;
+--------------------------------------------------------------------------------------
---------------------------+
| Grants for bob@192.168.100.20
|
+--------------------------------------------------------------------------------------
---------------------------+
| GRANT USAGE ON *.* TO 'bob'@'192.168.100.20' IDENTIFIED BY PASSWORD
'*760F60073FD235571A5260444301DB22136ED604' |
| GRANT ALL PRIVILEGES ON `accp`.* TO 'bob'@'192.168.100.20'
|
+--------------------------------------------------------------------------------------
---------------------------+
2 rows in set (0.00 sec)
3) 撤销权限
mysql> revoke all on accp.* from 'bob'@'192.168.100.20';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for bob@192.168.100.20;
+--------------------------------------------------------------------------------------
---------------------------+
| Grants for bob@192.168.100.20
|
+--------------------------------------------------------------------------------------
---------------------------+
| GRANT USAGE ON *.* TO 'bob'@'192.168.100.20' IDENTIFIED BY PASSWORD
'*760F60073FD235571A5260444301DB22136ED604' |
+--------------------------------------------------------------------------------------
---------------------------+
1 row in set (0.00 sec)
mysql>
2,权限远程Linux的客户端mysql数据库
1) 挂载Linux系统光盘
[root@centos02 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos02 ~]# ls /mnt/
CentOS_BuildTag EULA images LiveOS repodata RPM-GPG-KEY-CentOSTesting-7
EFI GPL isolinux Packages RPM-GPG-KEY-CentOS-7 TRANS.TB
2) 配置yum仓库
[root@centos02 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos02 ~]# ls /etc/yum.repos.d/
local.repo
[root@centos02 ~]# vim /etc/yum.repos.d/local.repo
[local]
name=centos7
baseurl=file:///mnt
enabled=1
gpgcheck=0
3) 安装mariadb客户
root@centos02 ~]# yum -y install mariadb
4) 在mysql数据库授权tom用户完全控制权限通过主机192.168.100.20访问数据库服务器写入数据
mysql> grant all on accp.* to 'tom'@'192.168.100.20' identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for tom@192.168.100.20;
+--------------------------------------------------------------------------------------
---------------------------+
| Grants for tom@192.168.100.20
|
+--------------------------------------------------------------------------------------
---------------------------+
| GRANT USAGE ON *.* TO 'tom'@'192.168.100.20' IDENTIFIED BY PASSWORD
'*760F60073FD235571A5260444301DB22136ED604' |
| GRANT ALL PRIVILEGES ON `accp`.* TO 'tom'@'192.168.100.20'
|
+--------------------------------------------------------------------------------------
---------------------------+
2 rows in set (0.00 sec)
mysql>
5) Linux系统Mysql客户端远程访问mysql数据库
[root@centos02 ~]# mysql -h 192.168.100.10 -P 3306 -u tom -ppwd@123
MySQL [(none)]> insert into accp.student values ('优优
',20,'11012099911','111188889099997777');
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select * from accp.student;
+-----------+--------+-------------+--------------------+
| 姓名 | 年龄 | 电话 | 身份证号码 |
+-----------+--------+-------------+--------------------+
| 优优 | 20 | 11012099911 | 111188889099997777 |
| YY | 18 | 13161295986 | 111345679124689018 |
+-----------+--------+-------------+--------------------+
2 rows in set (0.00 sec)
MySQL [(none)]>
3,授权远程Windows客户端访问mysql数据库
1) 在 Windows10 客户端安装
2) 接收协议许可条款
3)指定一下安装位置
4)创建桌面图标安装客户端
5)安装完成客户端
6)找到安装目录可执行文件激活客户端
7)mysql 服务器授权 192.168.100.30 通过 alice 用户密码为 pwd@1234 访问任意数据库和表
mysql> grant all on *.* to 'alice'@'192.168.100.30' identified by 'pwd@1234';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for alice@192.168.100.30;
+--------------------------------------------------------------------------------------
--------------------------------------+
| Grants for alice@192.168.100.30
|
+--------------------------------------------------------------------------------------
--------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'alice'@'192.168.100.30' IDENTIFIED BY PASSWORD
'*1012A2D81646DAEC166C0ACA539426FEECA3F616' |
+--------------------------------------------------------------------------------------
--------------------------------------+
1 row in set (0.00 sec)
8)客户端连接mysql
9)测试连接
10)使用客户端查看数据
11)查看用户信息表
mysql> show processlist;