数据库的管理
{**数据库**}
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
