Mysql数据库系统
1.安装依赖
- 挂载到/mnt目录[root@centos02 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos02 ~]# ls /mnt/
CentOS_BuildTag GPL LiveOS RPM-GPG-KEY-CentOS-7
EFI images Packages RPM-GPG-KEY-CentOS-Testing-7
EULA isolinux repodata TRANS.TBL
[root@centos02 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos02 ~]# ls /etc/yum.repos.d/
local.repo
- 删除系统自带的yum仓库配置本地的yum仓库
[root@centos02 ~]# rm -rf /etc/yum.repos.d/CentOS-*
[root@centos02 ~]# ls /etc/yum.repos.d/
local.repo
[root@centos02 ~]# cat /etc/yum.repos.d/local.repo
[local]
name=centos
baseurl=file:///mnt
enabled=1
gpgcheck=0
[root@centos02 ~]# yum -y install ncurses-devel cmake
2.解压源代码程序
- 卸载光盘挂载点[root@centos02 ~]# umount /mnt/
[root@centos02 ~]# ls /mnt/ - 挂载Mysql光盘[root@centos02 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos02 ~]# 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.ta
3)解压到/usr/src/目录
[root@centos02 ~]# tar zxf /mnt/mysql-5.5.22.tar.gz -C /usr/src/
[root@centos02 ~]# ls /usr/src/
debug kernels mysql-5.5.22
3.配置安装Mysql
- 配置mysql[root@centos02 ~]# cd /usr/src/mysql-5.5.22/
[root@centos02 mysql-5.5.22]# cmake
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DSYSCONFDIR=/etc
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EXTRA_CHARSET=all
- 编译安装MySQL
[root@centos02 mysql-5.5.22]# make && make install
[root@centos02 mysql-5.5.22]# ls -ld /usr/local/mysql
drwxr-xr-x 13 root root 213 3月 12 21:43 /usr/local/mysql
- 创建mysql组合用户[root@centos02 mysql-5.5.22]# groupadd mysql
[root@centos02 mysql-5.5.22]# useradd -M -s /sbin/nologin mysql -g mysql - 修改目录的所有者[root@centos02 mysql-5.5.22]# chown -R mysql:mysql /usr/local/mysql
[root@centos02 mysql-5.5.22]# ls -ld /usr/local/mysql
drwxr-xr-x 13 mysql mysql 213 3月 12 21:43 /usr/local/mysql - 生成MySQL主配置文件覆盖原有文件[root@centos02 mysql-5.5.22]# cp support-files/my-medium.cnf /etc/my.cnf
cp:是否覆盖"/etc/my.cnf"? y - 生成服务添加执行权限添加为系统服务设置开机自启
[root@centos02 mysql-5.5.22]# cp support-files/mysql.server /etc/init.d/mysqld
[root@centos02 mysql-5.5.22]# chmod +x /etc/init.d/mysqld
[root@centos02 mysql-5.5.22]# chkconfig --add mysqld
[root@centos02 mysql-5.5.22]# chkconfig --level 35 mysqld on
7)优化MySQL执行命令再初始化MySQL
# /etc/profile
PATH=$PATH:/usr/local/mysql/bin/
[root@centos02 mysql-5.5.22]# source /etc/profile
[root@centos02 mysql-5.5.22]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
4.控制MySQL服务MySQL数据库设置密码
- 启动MySQL服务[root@centos02 ~]# systemctl start mysql
ne[root@centos02 ~]# netstat -anptu | grep mysqld
11833/mysqld - 设置MySQL数据库密码使用账户密码登录管理
root@centos02 ~]# mysqladmin -uroot password
New password:
Confirm new password:
MySQL数据库和记录管理
数据库管理
- 登录MySQL创建数据库[root@centos02 ~]# mysql -uroot -ppwd@123
mysql> create database cyb;
Query OK, 1 row affected (0.00 sec) - 查看创建的数据库mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cyb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec) - 切换到创建的数据库和MySQL数据库mysql> use cyb;
Database changed
mysql> use mysql;
Database changed - 删除自己的数据库查看创建的数据库
mysql> drop database cyb;
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数据库表的管理
- 创建自己名字的数据库,在自己名字的数据库创建aaa表,设置三列数据三类为字符串1列数据为整类mysql> create database cyb;
Query OK, 1 row affected (0.00 sec)
mysql> create table cyb.aaa (姓名 char(5),年龄 int,身份证号码 char (18),primary key(身份证 号码));
Query OK, 0 rows affected (0.00 sec) - 查看创建的表格
mysql> desc cyb.aaa;
+-----------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| 姓名 | char(5) | YES | | NULL | |
| 年龄 | int(11) | YES | | NULL | |
| 身份证号码 | char(18) | NO | PRI | | |
+-----------------+----------+------+-----+---------+-------+
- rows in set (0.00 sec)
3.表中的管理
1)aaa表中插入连续列数据
mysql> insert into cyb.aaa values ('崔',18,'111111111111111111');
Query OK, 1 row affected (0.00 sec)
2)aaa表中插入不连续列数据
mysql> insert into cyb.aaa (姓名,身份证号码) values ('彪','121111111111111111');
Query OK, 1 row affected (0.00 sec)
3)查看表中的所有数据
mysql> select * from cyb.aaa;
+--------+--------+--------------------+
| 姓名 | 年龄 | 身份证号码 |
+--------+--------+--------------------+
| 崔 | 18 | 111111111111111111 |
| 彪 | NULL | 121111111111111111 |
+--------+--------+--------------------+
2 rows in set (0.00 sec)
4)查看表中的姓名和身份证号码
mysql> select 姓名,身份证号码 from cyb.aaa;
+--------+--------------------+
| 姓名 | 身份证号码 |
+--------+--------------------+
| 崔 | 111111111111111111 |
| 彪 | 121111111111111111 |
+--------+--------------------+
2 rows in set (0.00 sec)
5)修改aaa表中数据给彪添加年龄并查看数据
mysql> update cyb.aaa set 年龄=21 where 姓名='彪‘;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from cyb.aaa;
+--------+--------+--------------------+
| 姓名 | 年龄 | 身份证号码 |
+--------+--------+--------------------+
| 崔 | 18 | 111111111111111111 |
| 彪 | 21 | 121111111111111111 |
+--------+--------+--------------------+
2 rows in set (0.00 sec)
6)查看姓名是彪的数据
mysql> select 姓名,身份证号码 from cyb.aaa where 姓名='彪';
+--------+--------------------+
| 姓名 | 身份证号码 |
+--------+--------------------+
| 彪 | 121111111111111111 |
+--------+--------------------+
1 row in set (0.00 sec)
三.授权用户管理数据库
1.授权和撤销权限的配置
1)授权用户lpl对cyb数据库下的所有表拥有完全控制权限通过远程计算机192.168.100.30进行访问
mysql> grant all on cyb.* to 'lpl'@'192.168.100.30' identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
2)查看授权lpl用户的权限
mysql> show grants for lpl@192.168.100.30;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for lpl@192.168.100.30 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lpl'@'192.168.100.30' IDENTIFIED BY PASSWORD '*760F60073FD235571A5260444301DB22136ED604' |
| GRANT ALL PRIVILEGES ON `cyb`.* TO 'lpl'@'192.168.100.30' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3)撤销权限
mysql> revoke all on cyb.* from 'lpl'@'192.168.100.30';
Query OK, 0 rows affected (0.00 sec)
授权远程Linux的客户端访问数据库
1)挂载Linux光盘
root@centos03 ~]# mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护,将以只读方式挂载
[root@centos03 ~]# ls /mnt/
CentOS_BuildTag GPL LiveOS RPM-GPG-KEY-CentOS-7
EFI images Packages RPM-GPG-KEY-CentOS-Testing-7
EULA isolinux repodata TRANS.TBL
2)配置yum仓库
root@centos03 ~]# rm -rf /etc/yum.repos.d/Centos-*
[root@centos03 ~]# ls /etc/yum.repos.d/
local.repo
[root@centos03 ~]# cat /etc/yum.repos.d/local.repo
[local]
name=centos
baseurl=file:///mnt
enabled=1
gpgcheck=0
3)安装mariadb客户
[root@centos03 ~]# yum -y install mariadb
4)在MySQL数据库授权lpl用户完全控制权限通过主机192.168.100.30进行访问数据库写入数据
mysql> grant all on cyb.* to 'lpl'@'192.168.100.30' identified by 'pwd@123';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for lpl@192.168.100.30;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for lpl@192.168.100.30 |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lpl'@'192.168.100.30' IDENTIFIED BY PASSWORD '*760F60073FD235571A5260444301DB22136ED604' |
| GRANT ALL PRIVILEGES ON `cyb`.* TO 'lpl'@'192.168.100.30' |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5)Linux系统MySQL客户端远程访问MySQL数据库
[root@centos03 ~]# mysql -h 192.168.100.20 -P 3306 -u lpl -ppwd@123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select * from cyb.aaa;
+--------+--------+--------------------+
| 姓名 | 年龄 | 身份证号码 |
+--------+--------+--------------------+
| 崔 | 18 | 111111111111111111 |
| 彪 | 21 | 121111111111111111 |
+--------+--------+--------------------+
2 rows in set (0.00 sec)