数据库的管理

{**数据库**}

1.安装：

yum install mariadb-server.x86_64 -y安装服务

systemctl start mariadb    开启服务

systemctl stop firewalld.service  关闭火墙

netstat -antlpe | grep mysql

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         42812      2565/mysqld  

vim /etc/my.cnf

[root@server-dns ~]# systemctl restart mariadb.service   

[root@server-dns ~]# netstat -antlpe | grep mysql

[root@server-dns ~]# mysql

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

Your MariaDB connection id is 2

Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> quit;

Bye

mysql -uroot -pwestos 本机登陆数据库

mysql_secure_installation   第一次安装mysql通过此命令设置mysql

SHOW DATABASES;     显示数据库

USE mysql;          进入数据库

SHOW tables;        显示数据库中的表

DESC user;          查看user表中的数据结构

SELECT host,user,passwd FROM user;查询user表中的host,user,passwd字段

flush privileges    刷新数据库信息

[root@server-dns ~]# mysql_secure_installation   

/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current

password for the root user.  If you've just installed MariaDB, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):   **回车

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

Set root password? [Y/n]   **回车

New password:              **输入密码

Re-enter new password:     **确认密码

Password updated successfully!

Reloading privilege tables..

 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them.  This is intended only for testing, and to make the installation

go a bit smoother.  You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n]   **回车

 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]   **回车

 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can

access.  This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n]   **回车

 - Dropping test database...

 ... Success!

 - Removing privileges on test database...

 ... Success!

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n]   **回车

 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

[root@server-dns ~]# systemctl restart mariadb.service   **重启动服务，设置成功

mysql -uroot -predhat   **登陆

[root@server-dns ~]# 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

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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)]> USE mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

MariaDB [mysql]> SELECT Host,User FROM user;

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

| Host      | User |

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

| 127.0.0.1 | root |

| ::1       | root |

| localhost | root |

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

3 rows in set (0.00 sec)

create database logo; 创建logo数据库

CREATE TABLE UTAB ( username varchar(10) not null,passwd varchar(50) not null,age varchar(8) not null );  创建username，passwd，age字段

MariaDB [mysql]> CREATE DATABASE logo;

Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> SHOW DATABASES;

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

| Database           |

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

| information_schema |

| logo               |

| mysql              |

| performance_schema |

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

4 rows in set (0.00 sec)

MariaDB [mysql]> SHOW TABLES logo;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'logo' at line 1

MariaDB [mysql]> USE logo;

Database changed

MariaDB [logo]> CREATE TABLE UTAB ( username varchar(10) not null,passwd varchar(50) not null,age varchar(8) not null );  

Query OK, 0 rows affected (0.17 sec)

MariaDB [logo]> SHOW TABLES;

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

| Tables_in_logo |

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

| UTAB           |

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

1 row in set (0.00 sec)

MariaDB [logo]> DESC UTAB;

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

| Field    | Type        | Null | Key | Default | Extra |

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

| username | varchar(10) | NO   |     | NULL    |       |

| passwd   | varchar(50) | NO   |     | NULL    |       |

| age      | varchar(8)  | NO   |     | NULL    |       |

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

3 rows in set (0.00 sec)

MariaDB [logo]> SELECT * FROM UTAB;

Empty set (0.00 sec)

MariaDB [logo]> INSERT INTOUTAB ('lee','123','20');

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''lee','123','20')' at line 1

MariaDB [logo]> INSERT INTO UTAB VALUES ('lee','123','20');

Query OK, 1 row affected (0.07 sec)

MariaDB [logo]> SELECT * FROM UTAB;

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

| username | passwd | age |

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

| lee      | 123    | 20  |

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

1 row in set (0.00 sec)

MariaDB [logo]> INSERT INTO UTAB VALUES ('westos','123','');

Query OK, 1 row affected (0.07 sec)

MariaDB [logo]> SELECT * FROM UTAB;

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

| username | passwd | age |

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

| lee      | 123    | 20  |

| westos   | 123    |     |

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

2 rows in set (0.00 sec)

MariaDB [logo]> ALTER TABLE UTAB ADD AFTER password class varchar(8); 在UTAB表中插入class字段

MariaDB [logo]> UPDATE UTAB SET class='1'; 更新class值为1

MariaDB [logo]> UPDATE UTAB SET class='2' WHERE username='westos';

更新class字段westos下的值为2

MariaDB [logo]> DELETE FROM UTAB WHERE username='westos'; 删除username中的westos

*.*表示数据库所有表格

GREATE USER westos@'%'表示在任何数据库都可建立（前提数据库端口都开放）

GREATE USER westos@'localhost'表示本机可以创建              

SELECT * FROM mysql.user;  需在root用户下查询

注意：授权必须在root用户下进行操作；

修改密码（密码已知）

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

数据库登陆密码忘记，重置密码：（密码未知）

[root@server-dns ~]# systemctl stop mariadb

[root@server-dns ~]# mysqld_safe --skip-grant-tables &

[1] 6344

[root@server-dns ~]# 161201 07:33:32 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.

161201 07:33:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

重新设定密码

[root@server-dns ~]# mysql -uroot

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

Your MariaDB connection id is 1

Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> UPDATE mysql.user set Password=password  ('redhat');    **设定新密码（redhat）

Query OK, 0 rows affected (0.00 sec)

Rows matched: 3  Changed: 0  Warnings: 0

MariaDB [(none)]> quit;

Bye

[root@server-dns ~]# fg

mysqld_safe --skip-grant-tables

^Z

[1]+  Stopped                 mysqld_safe --skip-grant-tables

[root@server-dns ~]# killall -9 mysqld_safe

[1]+  Killed                  mysqld_safe --skip-grant-tables

[root@server-dns ~]# ps aux | grep mysql

mysql     6499  0.0  5.1 859056 96752 pts/0    Sl   07:33   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      6657  0.0  0.0 112640   936 pts/0    R+   07:39   0:00 grep --color=auto mysql

[root@server-dns ~]# kill -9 6499

[root@server-dns ~]# ps aux | grep mysql

root      6704  0.0  0.0 112640   936 pts/0    R+   07:40   0:00 grep --color=auto mysql

[root@server-dns ~]# systemctl start mariadb

[root@server-dns ~]# mysql -uroot -predhat   **登陆上即修改密码成功

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

Your MariaDB connection id is 2

Server version: 5.5.35-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> quit

数据库图形管理：

yum install httpd -y

systemctl status firewalld.service

firewall-cmd --permanent --add-service=http

firewall-cmd --reload

systemctl start httpd

cd /var/www/html/

ls

vim index.html

lftp 172.25.254.250

yum install lftp -y

lftp 172.25.254.250

ls

tar -jxf phpMyAdmin-3.4.0-all-languages.tar.bz2   解压php安装包

mv phpMyAdmin-3.4.0-all-languages myadmin  重命名（名字任意）

yum install php -y    安装php服务

systemctl restart httpd.service   重启httpd

cd myadmin/

ls

rpm -qa | grep php       查看php版本

rpm -qa | grep mariadb   查看mariadb版本

cp -p config.sample.inc.php config.inc.php

vim config.inc.php

yum install php-mysql.x86_64 -y

systemctl restart httpd.service

在浏览器中搜索172.25.254.117/myadmin  

登陆用户（root）密码

进行操作

收发邮件：

先配置dns

hostnamectl set-hostname maillinux.linux.com   服务器server

vim /etc/sysconfig/network-scripts/ifcfg-eth0

vim /etc/yum.repos.d/rhel_dvd.repo

reboot

yum install bind -y

vim /etc/named.conf

# Generated by NetworkManager

domain example.com

search example.com linux.com

nameserver 172.25.254.117

vim /etc/named.rfc1912.zones

cd /var/named/

ls

cp -p named.localhost westos.com.zone

vim westos.com.zone

cp -p westos.com.zone linux.com.zone

vim linux.com.zone

systemctl restart named

vim /etc/resolv.conf

systemctl stop firewalld.service

systemctl restart named

vim westos.com.zone

cd

systemctl restart named

dig -t mx westos.com

dig -t mx linux.com

1  hostnamectl set-hostname mailwestos.westos.com  服务器client

vim /etc/sysconfig/network-scripts/ifcfg-eth0

vim /etc/yum.repos.d/rhel_dvd.repo

reboot

vim /etc/resolv.conf

# Generated by NetworkManager

domain example.com

search example.com westos.com

nameserver 172.25.254.117

  dig -t mx westos.com

  dig -t mx linux.com

postconf -e "inet_interfaces=localhost"  更改

vim /etc/postfix/main.cf

#myhostname = host.domain.tld -->myhostname = host.domain.tld

#mydomain = domain.tld --> mydomain = westos.com

#myorigin = $mydomain --> myorigin = $mydomain

#inet_interfaces = all --> inet_interfaces = all

inet_interfaces = localhost --> #inet_interfaces = localhost

mydestination = $myhostname, localhost.$mydomain, localhost --> mydestination = $myhostname, $mydomain, localhost

systemctl restart postfix.service   **重启服务

postqueue -f  立即处理邮件

vim generic

postmap generic

ls

postconf -d | grep generic  查看默认设置

postconf -e "smtp_generic_maps = hash:/etc/postfix/generic"   修改选项

systemctl restart postfix.service