#################dns集群############

1.dns集群的基础配置

需求:一台dns服务器不能满足大量的访问需求,需要增加辅助dns服务器


辅助dns端 (client) :

vim /etc/resolv.conf 修改内容为:

3 nameserver 172.25.254.207


vim /etc/named.conf 修改为:

11         listen-on port 53 { any; };

17         allow-query     { any; };

31         dnssec-validation no;


vim /etc/named.rfc1912.zones

 25 zone "westos.com" IN {

 26         type slave;

 27         file "slaves/westos.com.zone";

 28         allow-update { none; };

 29         masters { 172.25.254.107; };

 30 };

ll /var/named --> 查看slaves目录信息


主dns端 (dns) :

vim /etc/named.conf 修改为:

11         listen-on port 53 { any; };

17         allow-query     { any; };

31         dnssec-validation no;


vim /etc/named.rfc1912.zones 修改内容为:

 25 zone "westos.com" IN {

 26         type master;

 27         file "westos.com.zone";

 28         allow-update { none; };

 29    allow-transfer { 172.25.254.207; }; ##允许谁同步

 30 };

systemctl restart named


测试 (desktop端):

systemctl restart named

dig www.westos.com


@@实例:

<1> client端操作不变


<2> dns端操作:

vim /etc/named.conf 修改为:

11         listen-on port 53 { any; };

17         allow-query     { any; };

31         dnssec-validation no;


vim /etc/named.rfc1912.zones 修改内容为:

 25 zone "westos.com" IN {

 26         type master;

 27         file "westos.com.zone";

 28         allow-update { key lzt; };

 29         allow-transfer { 172.25.254.207; };

 30 };

systemctl restart named


<3> 测试:

[root@client slaves]# systemctl restart named

[root@client slaves]# dig www.westos.com

;www.westos.com. IN A


;; ANSWER SECTION:

www.westos.com. 86400 IN A 172.25.0.108


;; AUTHORITY SECTION:

westos.com. 86400 IN NS dns.westos.com.


;; ADDITIONAL SECTION:

dns.westos.com. 86400 IN A 172.25.0.107


;; Query time: 1 msec

;; SERVER: 172.25.254.207#53(172.25.254.207)


2.自动同步dns服务

client端不做改变


dns端:

vim /etc/named.rfc1912.zones 修改内容为:

 25 zone "westos.com" IN {

 26         type master;

 27         file "westos.com.zone";

 28         allow-update { none; };

 29         allow-transfer { 172.25.254.207; };

 30         also-notify { 172.25.254.207; }; ##当dns文件被改变后,向谁推送新的数据

 31 };

vim /var/named/westos.com.zone 修改内容为:

 2 @       IN SOA  dns.westos.com. root.westos.com. (

 3                  2016112601    ; serial

 9 dns             A       172.25.0.107  

10 www             A       172.25.0.112


systemctl restart named

dns同步时,本质是比较 serial 前的标识 (2016112601) 的不同,每一次对 /var/named/westos.com.zone 内容做修改时都应该改变标识符。


测试:

在client主机测试:

dig www.westos.com


实例:


[root@client slaves]# dig www.westos.com

;www.westos.com. IN A


;; ANSWER SECTION:

www.westos.com. 86400 IN A 172.25.0.112


;; AUTHORITY SECTION:

westos.com. 86400 IN NS dns.westos.com.


;; ADDITIONAL SECTION:

dns.westos.com. 86400 IN A 172.25.0.107


;; Query time: 0 msec

;; SERVER: 172.25.254.207#53(172.25.254.207)


3.远程修改dns服务


dns端:

vim /etc/named.rfc1912.zones 修改内容为:

zone "westos.com" IN {

        type master;

        file "westos.com.zone";

        allow-update { 172.25.254.207; }; ##允许谁去更新

        allow-transfer { 172.25.254.207; };

        also-notify { 172.25.254.207; };

};

chmod 770 /var/named

cp -p /var/named/westos.com.zone  /mnt/westos.com.zone


client端:

nsupdate

> server 172.25.254.107

> update delete www.westos.com

> send

update failed: SERVFAIL ##报错原因:在 主dns 端漏做 chmod 770 /var/named

> server 172.25.254.107

> update delete www.westos.com ##删除www.westos.com

> send ##发送

> server 172.25.254.107

> update add www.westos.com 86400 A 172.25.254.1 ##添加 www.westos.com 对应的ip为 172.25.254.1,此处的 86400 代表的是dns一般保存周期为一天,即,86400 秒

> send

> quit


恢复:rm -fr /var/named/westos.com.zone /var/named/westos.com.zone.jnl

cp -p /mnt/westos.com.zone /var/named/westos.com.zone


实例:

client 端:

[root@client ~]# dig www.westos.com ##此处是删除了 www.westos.com 的效果


; <<>> DiG 9.9.4-RedHat-9.9.4-14.el7 <<>> www.westos.com

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NXDOMAIN, id: 38482

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 0, AUTHORITY: 1, ADDITIONAL: 1


; AUTHORITY SECTION:

westos.com. 10800 IN SOA dns.westos.com.westos.com. root.westos.com. 2016112907 86400 3600 604800 10800


;; Query time: 1 msec

;; SERVER: 172.25.254.207#53(172.25.254.207)

;; WHEN: Wed Nov 30 01:07:30 EST 2016

;; MSG SIZE  rcvd: 99


[root@client ~]# dig dns.westos.com


;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 6513

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1


;; OPT PSEUDOSECTION:

; EDNS: version: 0, flags:; udp: 4096

;; QUESTION SECTION:

;dns.westos.com. IN A


;; ANSWER SECTION:

dns.westos.com. 86400 IN A 172.25.254.107


;; AUTHORITY SECTION:

westos.com. 86400 IN NS dns.westos.com.


;; WHEN: Wed Nov 30 01:07:39 EST 2016

;; MSG SIZE  rcvd: 73


[root@client ~]# dig www.westos.com ##此处是添加 www.westos.com 对应的ip为 172.25.254.1 的情况


;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 20619

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 2


;; OPT PSEUDOSECTION:

; EDNS: version: 0, flags:; udp: 4096

;; QUESTION SECTION:

;www.westos.com. IN A


;; ANSWER SECTION:

www.westos.com. 86400 IN A 172.25.254.1


;; AUTHORITY SECTION:

westos.com. 86400 IN NS dns.westos.com.


;; ADDITIONAL SECTION:

dns.westos.com. 86400 IN A 172.25.254.207


;; WHEN: Wed Nov 30 01:09:53 EST 2016

;; MSG SIZE  rcvd: 93


4.设置密钥远程修改dns服务


dns端:

cp -p /etc/rndc.key /etc/westos.key ##将系统密码文件连同权限复制一份为自己名称的密码文件

cd /mnt/

dnssec-keygen -a HMAC-MD5 -b 128 -n HOST westos

cat /mnt/Kwestos.+157+08237.private

cat /mnt/Kwestos.+157+08237.key ##可以发现两个文件中密文相同


vim /etc/westos.key

将原始文件内容(如下)修改:

key "rndc-key" {

        algorithm hmac-md5;

        secret "KgE1w+fRiEQRov1RhPqUDg==";

};

          |        

          | ##修改成下面的内容

\|/

key "westos" { ##第一处修改点:名字修改为westos

        algorithm hmac-md5;

        secret "0QsS0gNcgoJPBCerborLmQ=="; ##第二处修改点:密文修改为 /mnt/Kwestos.+157+08237.key或者/mnt/Kwestos.+157+08237.private 文件中的密文,然后保存退出

};


vim /etc/named.conf

修改内容为:

 42 };

 43 include "/etc/westos.key"; ##修改点-->将密码所在的文件包含进去

 44 logging {


scp Kwestos.+157+08237.* root@172.25.254.207:/mnt/ ##将密钥发送给client端

systemctl restart named


client端:

cd /mnt/

nsupdate -k Kwestos.+157+08237.private 

或者

nsupdate -k /mnt/Kwestos.+157+08237.private

> server 172.25.254.207

> update add www.westos.com 86400 A 172.25.254.22 ##再次添加一个www.westos.com所对应的ip为 172.25.254.22

> send

> quit


恢复:rm -fr /var/named/westos.com.zone /var/named/westos.com.zone.jnl

cp -p /mnt/westos.com.zone /var/named/westos.com.zone


实例:

[root@client ~]# dig www.westos.com


;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 63371

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 1, ADDITIONAL: 2


;; OPT PSEUDOSECTION:

; EDNS: version: 0, flags:; udp: 4096

;; QUESTION SECTION:

;www.westos.com. IN A


;; ANSWER SECTION:

www.westos.com. 86400 IN A 172.25.254.22

www.westos.com. 86400 IN A 172.25.254.220


;; AUTHORITY SECTION:

westos.com. 86400 IN NS dns.westos.com.


;; ADDITIONAL SECTION:

dns.westos.com. 86400 IN A 172.25.254.207


;; WHEN: Wed Nov 30 02:15:42 EST 2016

;; MSG SIZE  rcvd: 109



5.使用dhcp服务自动配置dns服务(ddns 或 花生壳)

DHCP + DNS = DDNS


dns端:

安装dhcp服务

yum install dhcp -y

systemctl start dhcpd

systemctl start firewalld 或者 将dhcp服务添加到防火墙允许的服务中去

将selinux设置为警告模式

cp /usr/share/doc/dhcp-4.2.5/dhcp.conf.example /etc/dhcp/dhcpd.conf

vim /etc/dhcp/dhcpd.conf

修改内容为:

  7 option domain-name "westos.com";

  8 option domain-name-servers 172.25.254.107;


 14 ddns-update-style interim;

 删除 27,28 行

 30 subnet 172.25.254.0 netmask 255.255.255.0 {

 31   range 172.25.254.207 172.25.254.210;

 32   option routers 172.25.254.107;

 33 }

 34 

 35 key westos {

 36         algorithm hmac-md5;

 37         secret 0QsS0gNcgoJPBCerborLmQ==;

 38 };

 39 

 40 zone westos.com. {

 41         primary 127.0.0.1;

 42         key westos;

 43 }

systemctl restart dhcpd

systemctl restart named


client端:

设置主机名为 music.westos.com

systemctl start firewalld 或者 将dhcp服务添加到防火墙允许的服务中去

将selinux设置为警告模式

将/etc/resolv.conf内容修改为

namedserver 172.25.254.107

配置/etc/sysconfig/network-scripts/ifcfg-eth0 文件:

vim /etc/sysconfig/network-scripts/ifcfg-eth0 内容修改为:

2 BOOTPROTO=dhcp

systemctl restart network

dig music.westos.com


测试:

[root@music ~]# dig music.westos.com


; <<>> DiG 9.9.4-RedHat-9.9.4-14.el7 <<>> music.westos.com

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 53938

;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 2


;; OPT PSEUDOSECTION:

; EDNS: version: 0, flags:; udp: 4096

;; QUESTION SECTION:

;music.westos.com. IN A


;; ANSWER SECTION:

music.westos.com. 300 IN A 172.25.254.207


;; AUTHORITY SECTION:

westos.com. 86400 IN NS dns.westos.com.


;; ADDITIONAL SECTION:

dns.westos.com. 86400 IN A 172.25.254.107


;; Query time: 1 msec

;; SERVER: 172.25.254.107#53(172.25.254.107)

;; WHEN: Wed Nov 30 19:57:24 EST 2016

;; MSG SIZE  rcvd: 95



#########数据库###########

1.安装与基本操作

yum install mysql mysql-server


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

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

mysql_secure_installation 第一次安装mysql以后通过这条命令可以对mysql进行设置


mysql -uroot -predhat 从本机登录mysql数据库


show databases; 显示数据库

use mysql; 进入数据库

show tables; 显示数据库中的表

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

flush privileges; 刷新数据库信息

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



create database westos; 创建westos数据库

use westos;

create table linux (username varchar(15) not null,password varchar(15) not null); 创建表linux,username,password字段

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


show tables;

desc linux;


实例:

[root@client-dns mnt]# yum install mariadb-server.x86_64 -y

Complete!

[root@client-dns mnt]# systemctl start mariadb

[root@client-dns mnt]# systemctl stop firewalld

[root@client-dns mnt]# vim /etc/my.cnf

[root@client-dns mnt]# mysql

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

Your MariaDB connection id is 2

MariaDB [(none)]> quit

Bye


[root@client-dns mnt]# mysql_secure_installation 

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

Set root password? [Y/n] 

New password: ##输入密码

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

Password updated successfully!

Reloading privilege tables..


 ... Success!


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

 ... Success!


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

 ... Success!


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

 - Dropping test database...

 ... Success!

 - Removing privileges on test database...

 ... Success!


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

 ... Success!


[root@client-dns mnt]# systemctl restart mariadb.service 

[root@client-dns mnt]# mysql -uroot -predhat

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

Your MariaDB connection id is 2

MariaDB [(none)]> quit

Bye

[root@client-dns mnt]# mysql -uroot -p

Enter password: 

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

MariaDB [(none)]> quit

Bye



[root@client-dns mnt]# mysql -uroot -predhat ##登录

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

Your MariaDB connection id is 4

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.01 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


Database changed

MariaDB [mysql]> SHOW TABLES;

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

| Tables_in_mysql           |

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

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| ndb_binlog_index          |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| servers                   |

| slow_log                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

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

24 rows in set (0.00 sec)


MariaDB [mysql]> DESC USER;

ERROR 1146 (42S02): Table 'mysql.USER' doesn't exist

MariaDB [mysql]> DESC user;

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

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

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

| Host                   | char(60)                          | NO   | PRI |         |       |

| User                   | char(16)                          | NO   | PRI |         |       |

| Password               | char(41)                          | NO   |     |         |       |

| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |

| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |

| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |

| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |

| File_priv              | enum('N','Y')                     | NO   |     | N       |       |

| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |

| References_priv        | enum('N','Y')                     | NO   |     | N       |       |

| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |

| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |

| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |

| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |

| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |

| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |

| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |

| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |

| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |

| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |

| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |

| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |

| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |

| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |

| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |

| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |

| ssl_cipher             | blob                              | NO   |     | NULL    |       |

| x509_issuer            | blob                              | NO   |     | NULL    |       |

| x509_subject           | blob                              | NO   |     | NULL    |       |

| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |

| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |

| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |

| max_user_connections   | int(11)                           | NO   |     | 0       |       |

| plugin                 | char(64)                          | NO   |     |         |       |

| authentication_string  | text                              | NO   |     | NULL    |       |

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

42 rows in set (0.00 sec)



MariaDB [mysql]> SELECT Host,User,Password from user;

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

| Host      | User | Password                                  |

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

| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |

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

3 rows in set (0.00 sec)


MariaDB [mysql]> quit

Bye

[root@client-dns mnt]# mysql -uroot -predhat

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

Your MariaDB connection id is 5

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)]> CREATE DATABASE westos;

Query OK, 1 row affected (0.00 sec)


MariaDB [(none)]> use westos;

Database changed

MariaDB [westos]> CREATE TABLE Utab (username varchar(16) not null, passwd varchar(8) not null,age varchar(4));

Query OK, 0 rows affected (0.40 sec)


MariaDB [westos]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| westos             |

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

4 rows in set (0.00 sec)


MariaDB [westos]> show tables;

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

| Tables_in_westos |

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

| Utab             |

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

1 row in set (0.01 sec)


MariaDB [westos]> desc Utab;

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

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

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

| username | varchar(16) | NO   |     | NULL    |       |

| passwd   | varchar(8)  | NO   |     | NULL    |       |

| age      | varchar(4)  | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)


MariaDB [westos]> select * from Utab;

Empty set (0.00 sec)


MariaDB [westos]> insert into Utab values ('小正太','redhat','18');

Query OK, 1 row affected, 1 warning (0.30 sec)


MariaDB [westos]> insert into Utab values('lzt','redhat','20');

Query OK, 1 row affected (0.07 sec)


MariaDB [westos]> select * from Utab;

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

| username | passwd | age  |

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

| ???      | redhat | 18   |

| lzt      | redhat | 20   |

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

2 rows in set (0.00 sec)


MariaDB [westos]> ALTER TABLE Utab ADD class varchar(10) AFTER password;

ERROR 1054 (42S22): Unknown column 'password' in 'Utab'

MariaDB [westos]> ALTER TABLE Utab ADD class varchar(10) AFTER passwd;

Query OK, 2 rows affected (0.10 sec)               

Records: 2  Duplicates: 0  Warnings: 0


MariaDB [westos]> SELECT * FROM Utab;

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

| username | passwd | class | age  |

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

| ???      | redhat | NULL  | 18   |

| lzt      | redhat | NULL  | 20   |

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

2 rows in set (0.00 sec)


MariaDB [westos]> UPDATE Utab SET class='1' WHERE username='lzt';

Query OK, 1 row affected (0.34 sec)

Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [westos]> select * from Utab;

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

| username | passwd | class | age  |

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

| ???      | redhat | NULL  | 18   |

| lzt      | redhat | 1     | 20   |

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

2 rows in set (0.00 sec)


MariaDB [westos]> UPDATE Utab SET class='1';

Query OK, 1 row affected (0.29 sec)

Rows matched: 2  Changed: 1  Warnings: 0


MariaDB [westos]> select * from Utab;

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

| username | passwd | class | age  |

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

| ???      | redhat | 1     | 18   |

| lzt      | redhat | 1     | 20   |

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

2 rows in set (0.00 sec)


MariaDB [westos]> delete from Utab where username='lzt';

Query OK, 1 row affected (0.30 sec)


MariaDB [westos]> select * from Utab;

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

| username | passwd | class | age  |

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

| ???      | redhat | 1     | 18   |

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

1 row in set (0.00 sec)


MariaDB [westos]> drop table Utab;

Query OK, 0 rows affected (0.04 sec)


MariaDB [westos]> select * from Utab;

ERROR 1146 (42S02): Table 'westos.Utab' doesn't exist

MariaDB [westos]> drop database westos;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

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

3 rows in set (0.00 sec)



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

update linux set password=password('passwd2') where username=user1;(此种改密方式后的密码为加密密文) 更新linux表中user1 的密码为password2

update linux set 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数据库任意操作



2.备份

/var/lib/mysql

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


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


备份实例:

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

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

Your MariaDB connection id is 8


MariaDB [(none)]> SHOW DATABASES;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

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

3 rows in set (0.00 sec)


MariaDB [(none)]> CREATE DATABASE westos;

Query OK, 1 row affected (0.00 sec)


MariaDB [(none)]> USE westos;


MariaDB [westos]> CREATE TABLE lzt (username varchar(10) not null,password varchar(10) not null,age varchar(4));

Query OK, 0 rows affected (0.07 sec)


MariaDB [westos]> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| westos             |

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

4 rows in set (0.00 sec)


MariaDB [westos]> show lzt;

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 'lzt' at line 1

MariaDB [westos]> desc lzt;

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

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

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

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

| password | varchar(10) | NO   |     | NULL    |       |

| age      | varchar(4)  | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)


MariaDB [westos]> insert into lzt values('lp','westos',20);

Query OK, 1 row affected (0.34 sec)


MariaDB [westos]> select * from lzt;

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

| username | password | age  |

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

| lp       | westos   | 20   |

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

1 row in set (0.01 sec)


MariaDB [westos]> quit

Bye


[root@music ~]# mysqldump -uroot -predhat westos

-- MySQL dump 10.14  Distrib 5.5.35-MariaDB, for Linux (x86_64)

--

-- Host: localhost    Database: westos

-- ------------------------------------------------------

-- Server version 5.5.35-MariaDB


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


--

-- Table structure for table `lzt`

--


DROP TABLE IF EXISTS `lzt`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `lzt` (

  `username` varchar(10) NOT NULL,

  `password` varchar(10) NOT NULL,

  `age` varchar(4) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;


--

-- Dumping data for table `lzt`

--


LOCK TABLES `lzt` WRITE;

/*!40000 ALTER TABLE `lzt` DISABLE KEYS */;

INSERT INTO `lzt` VALUES ('lp','westos','20');

/*!40000 ALTER TABLE `lzt` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;


/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;


-- Dump completed on 2016-11-26 21:32:12


[root@music ~]# mysqldump -uroot -predhat westos > /mnt/westos.sql

[root@music ~]# mysql -uroot -predhat -e "show databases;"

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| westos             |

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


[root@music ~]# mysql -uroot -predhat -e "drop database westos;"

[root@music ~]# mysql -uroot -predhat -e "show databases;"

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

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

[root@music ~]# mysql -uroot -predhat westos < /mnt/westos.sql 

ERROR 1049 (42000): Unknown database 'westos'

[root@music ~]# mysql -uroot -predhat -e "create database westos;"

[root@music ~]# mysql -uroot -predhat westos < /mnt/westos.sql 

[root@music ~]# mysql -uroot -predhat -e "select * from westos.lzt;"

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

| username | password | age  |

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

| lp       | westos   | 20   |

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


3.mysql 密码恢复

systmctl stop  mariadb


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

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


systemctl restart mariadb


忘记密码实例:

systemctl stop mariadb

mysqld_safe --skip-grant-tables &

mysql -uroot 

MariaDB [(none)]> UPDATE mysql.user set Password=password ('westos') WHERE User='root';

MariaDB [(none)]> quit

Bye

[root@music ~]# fg

mysqld_safe --skip-grant-tables

^Z

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

[root@music ~]# killall -9 1

1: no process found

[root@music ~]# killall -9 mysqld_safe

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

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

mysql     2616  0.1  5.0 859060 95360 pts/0    Sl   20:52   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      2684  0.0  0.0 112640   936 pts/0    R+   20:57   0:00 grep --color=auto mysql

[root@music ~]# kill -9 2616

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

root      2694  0.0  0.0 112640   932 pts/0    R+   20:58   0:00 grep --color=auto mysql

[root@music ~]# systemctl start mariadb

[root@music ~]# mysql -uroot -pwestos


MariaDB [(none)]> quit

Bye

[root@music ~]# mysqladmin -uroot -pwestos password redhat

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



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';



4.创建用户 

('localhost'表示'本地','%'表示'服务器端','identified' 表示 '密码')

CREATE USER wxh@localhost identified by 'westos'; ##创建本地用户,并设置密码

CREATE USER lee@'%' identified by 'redhat'; ##创建服务器端用户(只要有账户和密码,就可以在数据库开放的主机上登录)

用户授权

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;


实例:

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

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


MariaDB [(none)]> CREATE USER lzt@localhost identified by 'lzt';

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> quit;

Bye

[root@music ~]# mysql -ulzt -plzt

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

Your MariaDB connection id is 3


MariaDB [(none)]> CREATE DATABASE nihao;

ERROR 1044 (42000): Access denied for user 'lzt'@'localhost' to database 'nihao'

MariaDB [(none)]> quit;

Bye

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

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

Your MariaDB connection id is 4


MariaDB [(none)]> GRANT CREATE on *.* to lzt@localhost;

Query OK, 0 rows affected (0.01 sec)


MariaDB [(none)]> GRANT SELECT on *.* to lzt@localhost;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> SHOW GRANTS FOR lzt@localhost;

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

| Grants for lzt@localhost                                                                                            |

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

| GRANT SELECT, CREATE ON *.* TO 'lzt'@'localhost' IDENTIFIED BY PASSWORD '*0B5204298A57D2948EB6AF6312B83D940E6D4C9F' |

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

1 row in set (0.00 sec)


MariaDB [(none)]> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> GRANT INSERT on *.* to lzt@localhost;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> SHOW GRANTS FOR lzt@localhost;

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

| Grants for lzt@localhost                                                                                                    |

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

| GRANT SELECT, INSERT, CREATE ON *.* TO 'lzt'@'localhost' IDENTIFIED BY PASSWORD '*0B5204298A57D2948EB6AF6312B83D940E6D4C9F' |

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

1 row in set (0.01 sec)


MariaDB [(none)]> SHOW GRANTS FOR lzt@localhost;

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

| Grants for lzt@localhost                                                                                                    |

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

| GRANT SELECT, INSERT, CREATE ON *.* TO 'lzt'@'localhost' IDENTIFIED BY PASSWORD '*0B5204298A57D2948EB6AF6312B83D940E6D4C9F' |

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

1 row in set (0.00 sec)


MariaDB [(none)]> SELECT * FROM mysql.user;

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

| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |

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

| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |

| localhost | lzt  | *0B5204298A57D2948EB6AF6312B83D940E6D4C9F | Y           | Y           | N           | N           | Y           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |

4 rows in set (0.00 sec)


MariaDB [(none)]> REVOKE CREATE on *.* from lzt@localhost;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> REVOKE INSERT on *.* from lzt@localhost;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> REVOKE SELECT on *.* from lzt@localhost;

Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> DROP USER lzt@localhost;

Query OK, 0 rows affected (0.00 sec)



5.图形管理数据库:

[root@music ~]# yum install httpd -y

[root@music ~]# cd /var/www/html/

[root@music html]# lftp 172.25.254.250

lftp 172.25.254.250:~> cd pub/         

lftp 172.25.254.250:/pub> cd docs

cd ok, cwd=/pub/docs

lftp 172.25.254.250:/pub/docs> cd 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@music html]# ls

phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@music html]# tar -jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 

[root@music html]# ls

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

[root@music html]# rm -fr phpMyAdmin-3.4.0-all-languages.tar.bz2 

[root@music html]# ls

phpMyAdmin-3.4.0-all-languages

[root@music html]# firewall-cmd --permanent --add-service=http

success

[root@music html]# firewall-cmd --reload

success

[root@music html]# yum install php -y

[root@music html]# yum install php-mysql.x86_64 -y

[root@music html]# mv phpMyAdmin-3.4.0-all-languages myadmin

[root@music html]# cp -p config.sample.inc.php config.inc.php

[root@music myadmin]# vim config.inc.php 

修改第 17 行:

 $cfg['blowfish_secret'] = 'westos';(此处添加任意字符即可) /* YOU MUST FILL IN THIS FOR COOKIE AUTH! *    /

[root@music myadmin]# systemctl start httpd

[root@music myadmin]# ifconfig

eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500

        inet 172.25.254.207  netmask 255.255.0.0  broadcast 172.25.255.255


测试:

在另外一台主机的浏览器里输入 172.25.254.207/myadmin 即可登录数据库



#############配置电子邮件的传输########

1.基础配置

server主机:

修改ip为 172.25.254.207

systemctl restart network

配置yum源为 172.25.254.250/rhel7

vim /etc/resolv.conf

添加内容为: nameserver 172.25.254.107

修改主机名为 mailwestos.westos.com


desktop主机:

修改ip为 172.25.254.107

systemctl restart network

配置yum源为 172.25.254.250/rhel7

vim /etc/resolv.conf

添加内容为: nameserver 172.25.254.107

修改主机名为 maillinux.linux.com

下载dns服务:

yum install bind -y

配置 /etc/named.conf文件

11 //      listen-on port 53 { 127.0.0.1; };

12 //      listen-on-v6 port 53 { ::1; };

17 //      allow-query     { localhost; };

32         nssec-validation no;


配置/etc/named.rfc1912.zones

先对模块复制两次(y6y --> p)

 26 zone "westos.com" IN {

 27         type master;

 28         file "westos.com.zone";

 29         allow-update { none; };

 30 };

 31 zone "linux.com" IN {

 32         type master;

 33         file "linux.com.zone";

 34         allow-update { none; };

 35 };


cp -p named.localhost westos.com.zone

配置 /var/named/westos.com.zone 文件为:

$TTL 1D

@       IN SOA  dns.westos.com root.westos.com. (

                                        0       ; serial

                                        1D      ; refresh

                                        1H      ; retry

                                        1W      ; expire

                                        3H )    ; minimum

                NS      dns.westos.com.

dns             A       172.25.254.107

westos.com.     MX 1    172.25.254.207.


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

配置 /var/named/westos.com.zone 文件为:

$TTL 1D

@       IN SOA  dns.westos.com root.westos.com. (

                                        0       ; serial

                                        1D      ; refresh

                                        1H      ; retry

                                        1W      ; expire

                                        3H )    ; minimum

                NS      dns.westos.com.

dns             A       172.25.254.107

linux.com.      MX 1    172.25.254.107.


systemctl restart named

systemctl stop firewalld

测试:

 dig -t MX westos.com

 dig -t MX linux.com


2.邮件服务配置

desktop端:

vim /etc/postfix/main.cf 

内容为:

75 myhostname = maillinux.linux.com

83 mydomain = linux.com

99 myorigin = $mydomain

113 inet_interfaces = all

116 #inet_interfaces = localhost

164 mydestination = $myhostname, $mydomain, localhost


server端:

75 myhostname = mailwestos.westos.com

83 mydomain = westos.com

99 myorigin = $mydomain

113 inet_interfaces = all

116 #inet_interfaces = localhost

164 mydestination = $myhostname, $mydomain, localhost


3.别名发送:

要求:

desktop(linux.com) 主机向 server(westos.com)主机发送邮件,使用别名发送:mail lzt@westos.com (server主机里没有lzt用户)

此时需要配置接收方(server主机) /etc/aliases

 97 lzt:            root ##别名

 98 all:    :include:/etc/alluser ##群发


vim /etc/alluser 修改内容为:

root

student

 

 postalias /etc/aliases

 systemctl restart postfix.service 

测试:

使用dekstop主机发送邮件

mail lzt@westos.com

mail all@westos.com


4.出站地址伪装

server (westos.com)主机:

vim /etc/postfix/generic 修改内容为:

在最后一行添加:root@westos.com 179010687@qq.com

postmap /etc/postfix/generic

postconf -d | grep generic 会出来一段字符,选择最后的一行字符(如下),进行复制:

smtp_generic_maps =

postconf -e "smtp_generic_maps = hash:/etc/postfix/generic" ##此处用到上条语句复制的字符

systemctl restart postfix.servic


测试:

使用server(westos.com)向dsktop(linux.com)主机:

mail root@linux.com 

在dsktop(linux.com)主机查看邮件来源


实例结果:

[root@mailwestos postfix]# mail root@linux.com ##在westos.com上操作

Subject: tt

gg

.

EOT


[root@maillinux named]# mail ##在linux.com上操作

>N  5 root                  Sun Nov 27 03:38  21/741   "tt"

& 5

Message  5:

From 1790106870@qq.com  Sun Nov 27 03:38:39 2016

Return-Path: <1790106870@qq.com>

X-Original-To: root@linux.com


5.检测25端口

软件下载 yum install telnet -y

测试 telnet 172.25.254.207 25

Trying 172.25.254.207...

Connected to 172.25.254.207.

Escape character is '^]'.

220 mailwestos.westos.com ESMTP Postfix

ehlo hello ##打招呼,检测端口是否正常,若出现下面 250 信息,则表示正常

250-mailwestos.westos.com

250-PIPELINING

250-SIZE 10240000

250-VRFY

250-ETRN

250-ENHANCEDSTATUSCODES

250-8BITMIME

250 DSN

mail from:root@westos.com ##邮件的发送方

250 2.1.0 Ok

rcpt to:root@linux.com ##邮件的接收方

250 2.1.5 Ok

data ##邮件名字

354 End data with <CR><LF>.<CR><LF>

ddd ##邮件内容

fff

.

250 2.0.0 Ok: queued as 38E0C17E849

quit ##退出telnet

221 2.0.0 Bye

Connection closed by foreign host.


实例:

在真机上做测验:

[root@foundation7 ~]# yum install telnet -y

[root@foundation7 ~]# telnet 172.25.254.207 25

Trying 172.25.254.207...

Connected to 172.25.254.207.

Escape character is '^]'.

220 mailwestos.westos.com ESMTP Postfix

ehlo hello

250-mailwestos.westos.com

250-PIPELINING

250-SIZE 10240000

250-VRFY

250-ETRN

250-ENHANCEDSTATUSCODES

250-8BITMIME

250 DSN

mail from:root@westos.com

250 2.1.0 Ok

rcpt to:root@linux.com

250 2.1.5 Ok

data

354 End data with <CR><LF>.<CR><LF>

ddd

fff

.

250 2.0.0 Ok: queued as 38E0C17E849

quit

221 2.0.0 Bye

Connection closed by foreign host.

在linux.com主机上查看邮件

[root@maillinux named]# mail

Heirloom Mail version 12.5 7/5/10.  Type ? for help.

"/var/spool/mail/root": 6 messages 1 new 3 unread

     5 root                  Sun Nov 27 03:38  22/752   "tt"

>N  6 1790106870@qq.com     Sun Nov 27 04:03  14/505 ##此处显示邮件发送方为 1790106870@qq.com 是因为在westos.com上有出站地址伪装   

& 6

Message  6:

From 1790106870@qq.com  Sun Nov 27 04:03:11 2016

Return-Path: <1790106870@qq.com>

X-Original-To: root@linux.com

Delivered-To: root@linux.com

Status: R


ddd

fff


& q

Held 6 messages in /var/spool/mail/root

You have mail in /var/spool/mail/root