3unit8

Red Hat System Administration III

###################单元八

Mariadb数据库

#######################################Mariadb安装

安装mariadb和mariadb-client组件:

# yum groupinstall -y mariadb mariadb-client

启动mariadb服务:

# systemctl start mariadb ; systemctl enable mariadb

校验mariadb的监听端口:

# ss -antlp |grep mysql

编辑/etc/my.cnf文件,在[mysqld]中加入以下参数:

skip-networking=1

# systemctl restart mariadb

# ss -antlp |grep mysql

此时只允许通过套接字文件进行本地连接,阻断所有来自网络的tcp/ip连接。

##########使用mysql_secure_installation工具进行数据库安全设置,根据提示完成操作:

# mysql_secure_installation

登录数据库:

# mysql -u root -p

Enter password: redhat

MariaDB [(none)]> show databases;

+--------------------+

| Database

+--------------------+

| information_schema |

| mysql

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> quit

##################################数据库基本操作SQL

SHOW DATABASES;

CREATE DATABASE database_name;

USE database_name;

SHOW tables;

CREATE TABLE table_name (name VARCHAR(20), sex CHAR(1));

DESCRIBE table_name;

INSERT INTO table_name VALUES ('wxh','M');

SELECT * FROM table_name;

UPDATE table_name SET attribute=value WHERE attribute > value;

DELETE FROM table_name WHERE attribute = value;

DROP TABLE table_name;

DROP DATABASE database_name;

#######################用户和访问权限

创建用户

CREATE USER wxh@localhost identified by 'westos';

CREATE USER lee@'%' identified by 'redhat';

查看用户

MariaDB [(none)]> select user from mysql.user

用户授权

GRANT INSERT,UPDATE,DELETE,SELECT on mariadb.* to wxh@localhost;

GRANT SELECT on mariadb.* lee@'%';

重载授权表

FLUSH PRIVILEGES;

查看用户授权

SHOW GRANTS FOR wxh@localhost;

撤销用户权限

REVOKE DELETE,UPDATE,INSERT on mariadb.* from wxh@localhost;

删除用户

DROP USER wxh@localhost;

##################备份与恢复

备份

# mysqldump -uroot -predhat westos > westos.dump

# mysqldump -uroot -predhat --all-databases > backup.dump ##所有的数据库备份

# mysqldump -uroot -predhat --no-data westos > westos.dump##只备份框架

恢复

# mysqladmin -uroot -predhat create db2

# mysql -uroot -predhat db2 < westos.dump

eg:

[root@httpserver ~]# mysqldump -uroot -phello student > /mnt/student.sql##将student数据库备份到/mnt

[root@httpserver ~]# mysql -uroot -phello -e "DROP DATABASE student;" ##删除student数据库

[root@httpserver ~]# mysqladmin -uroot -phello create student

[root@httpserver ~]# mysql -uroot -phello -e "SHOW DATABASES;"

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| student |

+--------------------+

[root@httpserver ~]# mysql -uroot -phello -e "SHOW TABLES FROM student;"

[root@httpserver ~]# mysql -uroot -phello student < /mnt/student.sql ##恢复student数据库

[root@httpserver ~]# mysql -uroot -phello -e "SHOW TABLES FROM student;"

+-------------------+

| Tables_in_student |

+-------------------+

| linux |

+-------------------+

修改root用户密码

mysqladmin -uroot -predhat password linux ##将root用户密码改为linux

忘了数据库密码怎么办?

# mysqld_safe --skip-grant-tables &

eg:

[root@httpserver ~]# systemctl stop mariadb.service

[root@httpserver ~]# mysqld_safe --skip-grant-tables & ##跳过数据库授权表

[1] 3313

[root@httpserver ~]# 170418 22:11:29 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.

170418 22:11:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql ##按“Enter”

[root@httpserver ~]# mysql

MariaDB [(none)]> select * from mysql.user;

MariaDB [(none)]> update mysql.user set password=password('hello')where user='root'; ##设置root用户密码为hello

[root@httpserver ~]# ps aux |grep mysql

root 3313 0.0 0.0 113248 1620 pts/0 S 22:11 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables

mysql 3468 0.0 5.1 859060 96752 pts/0 Sl 22:11 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock

root 3613 0.0 0.0 112656 984 pts/0 R+ 22:25 0:00 grep --color=auto mysql

[root@httpserver ~]# kill -9 3313

[root@httpserver ~]# kill -9 3468

[1]+ 已杀死 mysqld_safe --skip-grant-tables

[root@httpserver ~]# ps aux |grep mysql

root 3615 0.0 0.0 112656 980 pts/0 S+ 22:26 0:00 grep --color=auto mysql

[root@httpserver ~]# systemctl restart mariadb ##重启mariadb

[root@httpserver ~]# mysql -uroot -phello

#######################################Lab:

• 在serverX和desktopX上执行脚本:# lab mariadb setup

User Password Privileges

mary mary_password select on all tables from legacy database

legacy legacy_password select,insert,update,delete on all tables from legacy database

report report_password select on all tables from legacy database

Name Seller Phone number

HP Joe Doe +1 (432) 754-3509

Dell Luke Skywalker +1 (431) 219-4589

Lenovo Darth Vader +1 (327) 647-6784

install mariadb database groups.

start and enable the mariadb service.

stop and disable the firewall.

create the legacy database.

restore the database backup (/home/student/mariadb.dump).

create users according to the ticket.

insert the new manufacturers.

from your desktopX system,validate your work.

[student@desktopX ~]$ lab mariadb grade

mysql

==========

##设置mysql

yum install mysql mysql-server

[root@httpserver conf.d]# systemctl restart mariadb

[root@httpserver conf.d]# netstat -antlpe |grep mysql ##查看对外端口

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 442908 4633/mysqld

[root@httpserver conf.d]# vim /etc/my.cnf

skip-networking=1 ##安全设置对外端口不开放

[root@httpserver conf.d]# mysql_secure_installation ##第一次安装mysql以后通过这条命令可以对mysql进行设置

[root@httpserver conf.d]# mysql -uroot -predhat ##从本机登录mysql数据库

mysqladmin -uroot -predhat password westos 修改本地mysql root密码

mysqladmin -uroot -predhat -h 192.168.0.188 password westos 修改远程192.168.0.188 mysql服务器 root密码

show databases; 显示数据库

use mysql; 进入数据库

show tables; 显示数据库中的表

desc user; 查看user表的数据结构

flush privileges; 刷新数据库信息

select host.user,password from user; 查询user表中的host，user，password字段

eg：

MariaDB [(none)]> SHOW DATABASES; ##显示数据库

create database westos; 创建westos数据库

use westos;

create table linux( 创建表，username，password字段

username varchar(15) not null,

password varchar(15) not null

);

select * from mysql.user; 查询mysql库下的user表中的所以

alter table linux add age varchar(4); 添加age字段到linux表中

ALTER TABLE linux DROP age 删除age字段

ALTER TABLE linux ADD age VARCHAR(5) AFTER name 在name字段后添加字段age

MariaDB [(none)]> create database redhat

MariaDB [(none)]> use redhat

MariaDB [redhat]> SELECT * FROM student;

+----------+---------+

| username | passwd |

+----------+---------+

| user1 | passwd1 |

| user1 | 123 |

MariaDB [redhat]> delete from student where passwd=123

MariaDB [redhat]> DROP TABLE student;

MariaDB [redhat]> DROP DATABASE redhat；

show tables;

desc linux;

insert into linux values ('user1','passwd1'); 在linux表中插入值为username = user1，password = password1

update linux set password=password('passwd2') where username=user1; 更新linux表中user1 的密码为password2

delete from linux where username=user1; 删除linux表中user1的所以内容

grant select on *.* to user1@localhost identified by 'passwd1'; 授权user1 密码为passwd1 并且只能在本地查询数据库的所以内容

grant all on mysql.* to user2@'%' identified by 'passwd2'; 授权user2 密码为passwd2 可以从远程任意主机登录mysql 并且可以对mysql数据库任意操作

备份

/var/lib/mysql

mysqldump -uroot -predhat mysql > mysql.bak 备份mysql库到mysql.bak

mysql -uroot -predhat westos < mysql.bak 恢复mysql.bak 到westos库

mysql 密码恢复

/etc/init.d/mysqld stop

mysqld_safe --skip-grant-tables & 跳过grant-tables授权表不需要认证登录本地mysql数据库

update mysql.user set password=password('westos') where user='root'; 更新mysql.user 表中条件为root用户的密码为加密westos

/etc/init.d/mysql restart

phpmyadmin

yum install php php-mysql httpd mysql mysql-server

tar jxf phpmyadmin-*.tar.bz2 -C /var/www/html

mv phpmyadmin phpadmin

cp config.sample.inc.php config.inc.php

vim config.inc.php

add

$cfg['blowfish_secret'] = 'test';

/etc/init.d/httpd start

http://192.168.0.188/phpadmin

eg:

[root@httpserver ~]# systemctl start mariadb

[root@httpserver ~]# mysql -uroot -predhat

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 5.5.35-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

3 rows in set (0.00 sec)

MariaDB [(none)]> CREATE database student

-> ;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE student;

Database changed

MariaDB [student]> CREATE TABLE linux(

-> username varchar(40) not null,

-> password varchar(40) not null);

Query OK, 0 rows affected (0.33 sec)

MariaDB [student]> DESC linux;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

MariaDB [student]> ALTER TABLE linux ADD age varchar(5);

Query OK, 0 rows affected (0.54 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [student]> DESC linux;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| age | varchar(5) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

MariaDB [student]> ALTER TABLE linux DROP age;

Query OK, 0 rows affected (0.38 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [student]> DESC linux;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

MariaDB [student]> ALTER TABLE linux ADD age varchar(5) AFTER username;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

MariaDB [student]> DESC linux;

+----------+-------------+------+-----+---------+-------+

+----------+-------------+------+-----+---------+-------+

| age | varchar(5) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

MariaDB [student]> INSERT INTO linux values ('user1','18','123');

Query OK, 1 row affected (0.31 sec)

MariaDB [student]> SELECT * from linux;

+----------+------+----------+

| username | age | password |

+----------+------+----------+

| user1 | 18 | 123 |

+----------+------+----------+

1 row in set (0.00 sec)

MariaDB [student]> INSERT INTO linux values ('user2','25','234');

Query OK, 1 row affected (0.30 sec)

MariaDB [student]> SELECT * from linux;+----------+------+----------+

| username | age | password |

+----------+------+----------+

| user1 | 18 | 123 |

| user2 | 25 | 234 |

+----------+------+----------+

2 rows in set (0.00 sec)

安装数据库

[root@httpserver ~]# lftp 172.25.254.250

lftp 172.25.254.250:~> cd pub/docs/software/

lftp 172.25.254.250:/pub/docs/software> get phpMyAdmin-3.4.0-all-languages.tar.bz2

4548030 bytes transferred

lftp 172.25.254.250:/pub/docs/software> quit

[root@httpserver ~]# mv /root/phpMyAdmin-3.4.0-all-languages.tar.bz2 /www/html/

[root@httpserver ~]# cd /www/html/

[root@httpserver html]# ls

admin index.php test.html

cgi phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@httpserver html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2##解压.tar.bz2的压缩包

[root@httpserver html]# ls

admin phpMyAdmin-3.4.0-all-languages

cgi phpMyAdmin-3.4.0-all-languages.tar.bz2

index.php test.html

[root@httpserver html]# rm -fr phpMyAdmin-3.4.0-all-languages.tar.bz2 ##删除压缩包

[root@httpserver html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin ##重命名为mysqladmin

[root@httpserver html]# cd mysqladmin/

[root@httpserver mysqladmin]# ls

..... README

[root@httpserver mysqladmin]# less README

[root@httpserver mysqladmin]# less Documentation.txt

[root@httpserver mysqladmin]# cp config.sample.inc.php config.inc.php

[root@httpserver mysqladmin]# vim config.inc.php ##

[root@httpserver mysqladmin]# yum search php

php-mysql.x86_64 : A module for PHP applications that use MySQL

: databases

[root@httpserver mysqladmin]# yum install php-mysql.x86_64 -y

[root@httpserver mysqladmin]# systemctl restart httpd

安装论坛

[root@httpserver mysqladmin]# lftp 172.25.254.250

lftp 172.25.254.250:~> cd pub/

lftp 172.25.254.250:/pub> get Discuz_X3.2_SC_UTF8.zip

12486177 bytes transferred

lftp 172.25.254.250:/pub> quit

[root@httpserver mysqladmin]# ls

.........

[root@httpserver mysqladmin]# mv Discuz_X3.2_SC_UTF8.zip /www/html/

[root@httpserver mysqladmin]# ls

.......

[root@httpserver mysqladmin]# cd /var/lib/mysql/

[root@httpserver mysql]# ls

aria_log.00000001 ib_logfile0 mysql.sock

aria_log_control ib_logfile1 performance_schema

ibdata1 mysql student

[root@httpserver mysql]# cd /www/html/

[root@httpserver html]# ls

admin Discuz_X3.2_SC_UTF8.zip mysqladmin

cgi index.php test.html

[root@httpserver html]# unzip Discuz_X3.2_SC_UTF8.zip

Archive: Discuz_X3.2_SC_UTF8.zip

creating: readme/

inflating: readme/changelog.txt

inflating: readme/convert.txt

[root@httpserver html]# chmod 777 upload -R

[root@httpserver html]# setenforce 0

posted @ 2017-04-20 19:17 yangying 阅读(135) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

3unit8

公告