课程第35/36次

botang

端口转发：/etc/rc.local:

1. iptables -t nat -A PREROUTING -s 192.168.0.0/24 -d 192.168.0.103 -p tcp  --dport 5423 -j DNAT --to-destination 192.168.0.103:80

复制代码

或者可选可以装一下：
yum -y install iptables-services
service iptables save

拷贝/usr/share/doc/httpd=2.4.26/httpd-vhost.conf到/etc/httpd/conf.d/vhosts.conf:

1. <VirtualHost *:80>
   
2.     ServerAdmin webmaster@www3.example.com
   
3.     DocumentRoot "/var/www/html/www3"
   
4.     ServerName www3.example.com
   
5.     ErrorLog "/var/log/httpd/www3-error_log"
   
6.     CustomLog "/var/log/httpd/www3-access_log" common
   
7. </VirtualHost>
   
8. 
   
9. <VirtualHost *:80>
   
10.     ServerAdmin webmaster@server3.example.com
    
11.     DocumentRoot "/var/www/html/server3"
    
12.     ServerName server3.example.com
    
13.     ErrorLog "/var/log/httpd/server3-error_log"
    
14.     CustomLog "/var/log/httpd/server3-access_log" common
    
15. </VirtualHost>
    
16. 
    

复制代码

mod_ssl的配置：

1. <VirtualHost *:80>
   
2.     ServerAdmin webmaster@www3.example.com
   
3.     DocumentRoot "/var/www/html/www3"
   
4.     ServerName www3.example.com
   
5.     ErrorLog "/var/log/httpd/www3-error_log"
   
6.     CustomLog "/var/log/httpd/www3-access_log" common
   
7. </VirtualHost>
   
8. 
   
9. <VirtualHost *:80>
   
10.     ServerAdmin webmaster@server3.example.com
    
11.     DocumentRoot "/var/www/html/server3"
    
12.     ServerName server3.example.com
    
13.     ErrorLog "/var/log/httpd/server3-error_log"
    
14.     CustomLog "/var/log/httpd/server3-access_log" common
    
15. </VirtualHost>
    
16. 
    
17. 
    
18. <VirtualHost *:443>
    
19.     SSLEngine on
    
20.     SSLCACertificateFile /var/www/html/www3/example-ca.crt
    
21.     SSLCertificateFile  /var/www/html/www3/server3.crt
    
22.     SSLCertificateKeyFile  /var/www/html/www3/server3.key
    
23.     ServerAdmin webmaster@www3.example.com
    
24.     DocumentRoot "/var/www/html/www3"
    
25.     ServerName www3.example.com
    
26.     ErrorLog "/var/log/httpd/www3-error_log"
    
27.     CustomLog "/var/log/httpd/www3-access_log" common
    
28. </VirtualHost>
    

复制代码

1. [root@server3 ~]# firewall-cmd  --permanent --add-rich-rule='rule family=ipv4 source address=192.168.1.0/24 service name="http" log level=notice reject'
   
2. success
   
3. [root@server3 ~]# firewall-cmd  --permanent --add-rich-rule='rule family=ipv4 source address=192.168.0.0/24 service name="http" log level=notice accept'
   

复制代码

对应iptables
:Chain IN_public_log (1 references)
target     prot opt source               destination
LOG        tcp  --  192.168.1.0/24       0.0.0.0/0            tcp dpt:80 ctstate                                                                              NEW LOG flags 0 level 5
LOG        tcp  --  192.168.0.0/24       0.0.0.0/0            tcp dpt:80 ctstate                                                                              NEW LOG flags 0 level 5

2019-07-14:---------------------------------------------------------------------------

1. [root@server3 etc]# mysql -u root --password=redhat
   
2. Welcome to the MariaDB monitor.  Commands end with ; or \g.
   
3. Your MariaDB connection id is 2
   
4. Server version: 5.5.60-MariaDB MariaDB Server
   
5. 
   
6. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
7. 
   
8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
9. 
   
10. MariaDB [(none)]>
    

复制代码

不输入密码是不行的：

1. MariaDB [(none)]> exit
   
2. Bye
   
3. [root@server3 etc]# mysql -u root
   
4. ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
   
5. [root@server3 etc]#
   

复制代码

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

1. [root@server3 etc]# mysql -u root --password=redhat
   
2. Welcome to the MariaDB monitor.  Commands end with ; or \g.
   
3. Your MariaDB connection id is 5
   
4. Server version: 5.5.60-MariaDB MariaDB Server
   
5. 
   
6. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
7. 
   
8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
9. 
   
10. MariaDB [(none)]> show databases;
    
11. +--------------------+
    
12. | Database           |
    
13. +--------------------+
    
14. | information_schema |
    
15. | mysql              |
    
16. | performance_schema |
    
17. +--------------------+
    
18. 3 rows in set (0.01 sec)
    
19. 
    
20. MariaDB [(none)]> use mysql;
    
21. Reading table information for completion of table and column names
    
22. You can turn off this feature to get a quicker startup with -A
    
23. 
    
24. Database changed
    
25. MariaDB [mysql]> show tables
    
26.     -> ;
    
27. +---------------------------+
    
28. | Tables_in_mysql           |
    
29. +---------------------------+
    
30. | columns_priv              |
    
31. | db                        |
    
32. | event                     |
    
33. | func                      |
    
34. | general_log               |
    
35. | help_category             |
    
36. | help_keyword              |
    
37. | help_relation             |
    
38. | help_topic                |
    
39. | host                      |
    
40. | ndb_binlog_index          |
    
41. | plugin                    |
    
42. | proc                      |
    
43. | procs_priv                |
    
44. | proxies_priv              |
    
45. | servers                   |
    
46. | slow_log                  |
    
47. | tables_priv               |
    
48. | time_zone                 |
    
49. | time_zone_leap_second     |
    
50. | time_zone_name            |
    
51. | time_zone_transition      |
    
52. | time_zone_transition_type |
    
53. | user                      |
    
54. +---------------------------+
    
55. 24 rows in set (0.00 sec)
    
56. 
    
57. MariaDB [mysql]>
    

复制代码

1. [root@server3 etc]# mysql -u root  -h localhost  --password=redhat
   
2. Welcome to the MariaDB monitor.  Commands end with ; or \g.
   
3. Your MariaDB connection id is 3
   
4. Server version: 5.5.60-MariaDB MariaDB Server
   
5. 
   
6. Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
   
7. 
   
8. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
9. 
   
10. MariaDB [(none)]> show databases;
    
11. +--------------------+
    
12. | Database           |
    
13. +--------------------+
    
14. | information_schema |
    
15. | mysql              |
    
16. | performance_schema |
    
17. +--------------------+
    
18. 3 rows in set (0.00 sec)
    
19. 
    
20. MariaDB [(none)]> create database inventory;
    
21. Query OK, 1 row affected (0.00 sec)
    
22. 
    
23. MariaDB [(none)]> use inventory ;
    
24. Database changed
    
25. MariaDB [inventory]> show tables;
    
26. Empty set (0.00 sec)
    
27. 
    
28. MariaDB [inventory]> create table product (id int(11) ,
    
29.     ->   name varchar(100),
    
30.     -> price double,
    
31.     -> stock int(11),
    
32.     -> id_category int(11),
    
33.     -> id_manufacturer int(11)  ) ;
    
34. Query OK, 0 rows affected (0.02 sec)
    
35. 
    
36. MariaDB [inventory]> show tables;
    
37. +---------------------+
    
38. | Tables_in_inventory |
    
39. +---------------------+
    
40. | product             |
    
41. +---------------------+
    
42. 1 row in set (0.00 sec)
    
43. 
    
44. MariaDB [inventory]> desc  product;
    
45. +-----------------+--------------+------+-----+---------+-------+
    
46. | Field           | Type         | Null | Key | Default | Extra |
    
47. +-----------------+--------------+------+-----+---------+-------+
    
48. | id              | int(11)      | YES  |     | NULL    |       |
    
49. | name            | varchar(100) | YES  |     | NULL    |       |
    
50. | price           | double       | YES  |     | NULL    |       |
    
51. | stock           | int(11)      | YES  |     | NULL    |       |
    
52. | id_category     | int(11)      | YES  |     | NULL    |       |
    
53. | id_manufacturer | int(11)      | YES  |     | NULL    |       |
    
54. +-----------------+--------------+------+-----+---------+-------+
    
55. 6 rows in set (0.01 sec)
    
56. 
    
57. MariaDB [inventory]>
    

复制代码

1. MariaDB [inventory]> select  * from product;
   
2. Empty set (0.00 sec)
   
3. 
   
4. MariaDB [inventory]> alter table product add constraint con1 primary key (id) ;
   
5. Query OK, 0 rows affected (0.02 sec)
   
6. Records: 0  Duplicates: 0  Warnings: 0
   
7. 
   
8. MariaDB [inventory]> desc product;
   
9. +-----------------+--------------+------+-----+---------+-------+
   
10. | Field           | Type         | Null | Key | Default | Extra |
    
11. +-----------------+--------------+------+-----+---------+-------+
    
12. | id              | int(11)      | NO   | PRI | 0       |       |
    
13. | name            | varchar(100) | YES  |     | NULL    |       |
    
14. | price           | double       | YES  |     | NULL    |       |
    
15. | stock           | int(11)      | YES  |     | NULL    |       |
    
16. | id_category     | int(11)      | YES  |     | NULL    |       |
    
17. | id_manufacturer | int(11)      | YES  |     | NULL    |       |
    
18. +-----------------+--------------+------+-----+---------+-------+
    
19. 6 rows in set (0.00 sec)
    
20. 
    
21. MariaDB [inventory]>
    

复制代码

1. MariaDB [inventory]> insert into product values (1 ,'SDSSDP-128G-G25 2.5',82.04,                                                                             30, 3,1 ) ;
   
2. Query OK, 1 row affected (0.00 sec)
   
3. 
   
4. MariaDB [inventory]> select  * from product;
   
5. +----+---------------------+-------+-------+-------------+-----------------+
   
6. | id | name                | price | stock | id_category | id_manufacturer |
   
7. +----+---------------------+-------+-------+-------------+-----------------+
   
8. |  1 | SDSSDP-128G-G25 2.5 | 82.04 |    30 |           3 |               1 |
   
9. +----+---------------------+-------+-------+-------------+-----------------+
   
10. 1 row in set (0.00 sec)
    
11. 
    
12. MariaDB [inventory]>
    

复制代码

1. MariaDB [inventory]> select  * from product where name like '%G%';
   
2. +----+---------------------+-------+-------+-------------+-----------------+
   
3. | id | name                | price | stock | id_category | id_manufacturer |
   
4. +----+---------------------+-------+-------+-------------+-----------------+
   
5. |  1 | SDSSDP-128G-G25 2.5 |  89.9 |    60 |           3 |               1 |
   
6. +----+---------------------+-------+-------+-------------+-----------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. MariaDB [inventory]>  select  * from product where name like '%F%';
   
10. Empty set (0.00 sec)
    
11. 
    
12. MariaDB [inventory]>  select  * from product where name like 'S_S%';
    
13. +----+---------------------+-------+-------+-------------+-----------------+
    
14. | id | name                | price | stock | id_category | id_manufacturer |
    
15. +----+---------------------+-------+-------+-------------+-----------------+
    
16. |  1 | SDSSDP-128G-G25 2.5 |  89.9 |    60 |           3 |               1 |
    
17. +----+---------------------+-------+-------+-------------+-----------------+
    
18. 1 row in set (0.00 sec)
    
19. 
    
20. MariaDB [inventory]>  select  * from product where name like 'S__S%';
    
21. +----+---------------------+-------+-------+-------------+-----------------+
    
22. | id | name                | price | stock | id_category | id_manufacturer |
    
23. +----+---------------------+-------+-------+-------------+-----------------+
    
24. |  1 | SDSSDP-128G-G25 2.5 |  89.9 |    60 |           3 |               1 |
    
25. +----+---------------------+-------+-------+-------------+-----------------+
    
26. 1 row in set (0.00 sec)
    
27. 
    
28. MariaDB [inventory]>  select  * from product where name like 'S___S%';
    
29. Empty set (0.00 sec)
    
30. 
    
31. MariaDB [inventory]>
    

复制代码

1. MariaDB [inventory]>  select  * from product where name like 'S___S%';
   
2. Empty set (0.00 sec)
   
3. 
   
4. MariaDB [inventory]> select  * from product where id_category in ( 1, 2 ) ;
   
5. Empty set (0.00 sec)
   
6. 
   
7. MariaDB [inventory]> select  * from product where id_category in ( 1, 2, 3);
   
8. +----+---------------------+-------+-------+-------------+-----------------+
   
9. | id | name                | price | stock | id_category | id_manufacturer |
   
10. +----+---------------------+-------+-------+-------------+-----------------+
    
11. |  1 | SDSSDP-128G-G25 2.5 |  89.9 |    60 |           3 |               1 |
    
12. +----+---------------------+-------+-------+-------------+-----------------+
    
13. 1 row in set (0.00 sec)
    

复制代码

SQL 分为： DQL（select）   DDL （ alter   create  truncate  drop）   DML （insert  delete  update） TCL （commit  rollback） DCL（grant  revoke）
对于DDL类型的权限，在grant、revoke时要用户退出才能生效。

逻辑备份mariadb:

1. [root@server3 etc]# mysqldump -u root -p inventory > /root/inventory.mdb
   
2. Enter password:
   
3. [root@server3 etc]# cd
   
4. [root@server3 ~]# vim inventory.mdb
   
5. [root@server3 ~]#
   

复制代码

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

1. MariaDB [inventory]> show tables;
   
2. +---------------------+
   
3. | Tables_in_inventory |
   
4. +---------------------+
   
5. | product             |
   
6. +---------------------+
   
7. 1 row in set (0.00 sec)
   
8. 
   
9. MariaDB [inventory]> select  * from product;
   
10. +----+---------------------+-------+-------+-------------+-----------------+
    
11. | id | name                | price | stock | id_category | id_manufacturer |
    
12. +----+---------------------+-------+-------+-------------+-----------------+
    
13. | 10 | SDSSDP-128G-G25 2.5 |  89.9 |    60 |           3 |               1 |
    
14. +----+---------------------+-------+-------+-------------+-----------------+
    
15. 1 row in set (0.00 sec)
    
16. 
    
17. MariaDB [inventory]> drop table product;
    
18. Query OK, 0 rows affected (0.01 sec)
    
19. 
    
20. MariaDB [inventory]> show tables;
    
21. Empty set (0.00 sec)
    
22. 
    
23. MariaDB [inventory]> source /root/inventory2.mdb
    
24. Query OK, 0 rows affected (0.01 sec)
    
25. 
    
26. Query OK, 0 rows affected (0.00 sec)
    
27. 
    
28. Query OK, 0 rows affected (0.00 sec)
    
29. 
    
30. Query OK, 0 rows affected (0.00 sec)
    
31. 
    
32. Query OK, 0 rows affected (0.00 sec)
    
33. 
    
34. Query OK, 0 rows affected (0.00 sec)
    
35. 
    
36. Query OK, 0 rows affected (0.00 sec)
    
37. 
    
38. Query OK, 0 rows affected (0.00 sec)
    
39. 
    
40. Query OK, 0 rows affected (0.00 sec)
    
41. 
    
42. Query OK, 0 rows affected (0.00 sec)
    
43. 
    
44. Query OK, 0 rows affected (0.00 sec)
    
45. 
    
46. Query OK, 0 rows affected (0.00 sec)
    
47. 
    
48. Query OK, 0 rows affected (0.00 sec)
    
49. 
    
50. Query OK, 0 rows affected (0.01 sec)
    
51. 
    
52. Query OK, 0 rows affected (0.00 sec)
    
53. 
    
54. Query OK, 0 rows affected (0.00 sec)
    
55. 
    
56. Query OK, 0 rows affected (0.00 sec)
    
57. 
    
58. Query OK, 1 row affected (0.00 sec)
    
59. 
    
60. Query OK, 0 rows affected (0.00 sec)
    
61. 
    
62. Query OK, 0 rows affected (0.00 sec)
    
63. 
    
64. Query OK, 0 rows affected (0.00 sec)
    
65. 
    
66. Query OK, 0 rows affected (0.00 sec)
    
67. 
    
68. Query OK, 0 rows affected (0.00 sec)
    
69. 
    
70. Query OK, 0 rows affected (0.00 sec)
    
71. 
    
72. Query OK, 0 rows affected (0.00 sec)
    
73. 
    
74. Query OK, 0 rows affected (0.00 sec)
    
75. 
    
76. Query OK, 0 rows affected (0.00 sec)
    
77. 
    
78. Query OK, 0 rows affected (0.00 sec)
    
79. 
    
80. MariaDB [inventory]> show tables;
    
81. +---------------------+
    
82. | Tables_in_inventory |
    
83. +---------------------+
    
84. | product             |
    
85. +---------------------+
    
86. 1 row in set (0.00 sec)
    
87. 
    
88. MariaDB [inventory]>
    

复制代码