-----------------------------
----------------------------- php + MariaDB -----------------------------
-----------------------------
++++++++++++++++++++++++++++++++++++++++++++++++ httpd
# dnf list | grep httpd
centos-logos-httpd.noarch 85.8-2.el8 baseos
httpd.x86_64 2.4.37-43.module_el8.5.0+1022+b541f3b1 appstream
httpd-devel.x86_64 2.4.37-43.module_el8.5.0+1022+b541f3b1 appstream
httpd-filesystem.noarch 2.4.37-43.module_el8.5.0+1022+b541f3b1 appstream
httpd-manual.noarch 2.4.37-43.module_el8.5.0+1022+b541f3b1 appstream
httpd-tools.x86_64 2.4.37-43.module_el8.5.0+1022+b541f3b1 appstream
keycloak-httpd-client-install.noarch 1.0-2.el8 appstream
libmicrohttpd.i686 1:0.9.59-2.el8 baseos
libmicrohttpd.x86_64 1:0.9.59-2.el8 baseos
python3-keycloak-httpd-client-install.noarch 1.0-2.el8 appstream
# dnf -y install httpd
# dnf list installed httpd
已安裝軟體包
httpd.x86_64 2.4.37-43.module_el8.5.0+1022+b541f3b1
# ls /usr/sbin | grep httpd
httpd
# ls /usr/lib/systemd/system | grep httpd
httpd.service
httpd@.service
httpd.service.d
httpd.socket
httpd.socket.d
# ls /etc/httpd/conf
httpd.conf magic
# ls /var/www
cgi-bin html
# systemctl start httpd
# systemctl enable httpd
# systemctl status httpd
# ps -aux | grep httpd
root 2046 0.1 0.6 282900 11596 ? Ss 08:01 0:00 /usr/sbin/httpd -DFOREGROUND
apache 2047 0.0 0.4 296780 8416 ? S 08:01 0:00 /usr/sbin/httpd -DFOREGROUND
apache 2048 0.0 0.6 1813320 12108 ? Sl 08:01 0:00 /usr/sbin/httpd -DFOREGROUND
apache 2049 0.0 0.7 1944448 14156 ? Sl 08:01 0:00 /usr/sbin/httpd -DFOREGROUND
apache 2050 0.0 0.8 1813320 16188 ? Sl 08:01 0:00 /usr/sbin/httpd -DFOREGROUND
root 2285 0.0 0.0 12136 1104 pts/0 S+ 08:02 0:00 grep --color=auto httpd
# firewall-cmd --get-services | grep http
...
http https imap imaps ipp ipp-client ipsec irc ircs iscsi-target isns jenkins kadmin kdeconnect kerberos ...
# firewall-cmd --get-zones
block dmz drop external home internal nm-shared public trusted work
# firewall-cmd --get-active-zone
public
interfaces: ens33
# firewall-cmd --zone=public --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens33
sources:
services: cockpit dhcpv6-client ssh
ports:
protocols:
masquerade: no
forward-ports:
source-ports:
icmp-blocks:
rich rules:
# firewall-cmd --permanent --zone=public --add-service=http
# firewall-cmd --permanent --zone=public --list-all
# firewall-cmd --reload
HostOS
http://172.31.145.99/
# vi /var/www/html/01.html
<h1>
This page is on /var/www/html...
</h1>
HostOS
http://172.31.147.99/01.html++++++++++++++++++++++++++++++++++++++++++++++++設定虛擬目錄 Alias
++++++++++++++++++++++++++++++++++++++++++++++++
////////////////////////////////////////////////////////系統目錄下
# ls /
bin boot dev etc home lib lib64 media mnt opt proc root run sbin srv sys tmp usr var
# mkdir /opt/web
# vi /opt/web/02.html
<h1>
This page is on /opt/web...
</h1>
# ls -l /
總計 24
lrwxrwxrwx. 1 root root 7 11月 3 2020 bin -> usr/bin
dr-xr-xr-x. 5 root root 4096 10月 15 19:23 boot
drwxr-xr-x. 20 root root 3120 1月 5 07:48 dev
drwxr-xr-x. 84 root root 8192 1月 5 07:55 etc
drwxr-xr-x. 2 root root 6 1月 4 16:07 home
lrwxrwxrwx. 1 root root 7 11月 3 2020 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 11月 3 2020 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 11月 3 2020 media
drwxr-xr-x. 3 root root 18 10月 15 19:02 mnt
drwxr-xr-x. 3 root root 17 1月 5 08:22 opt
dr-xr-xr-x. 164 root root 0 1月 5 07:48 proc
dr-xr-x---. 2 root root 135 10月 15 19:36 root
drwxr-xr-x. 26 root root 740 1月 5 07:55 run
lrwxrwxrwx. 1 root root 8 11月 3 2020 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 11月 3 2020 srv
dr-xr-xr-x. 13 root root 0 1月 5 07:48 sys
drwxrwxrwt. 13 root root 4096 1月 5 08:01 tmp
drwxr-xr-x. 12 root root 144 10月 15 19:01 usr
drwxr-xr-x. 21 root root 4096 1月 5 07:55 var
# ls -l /opt
drwxr-xr-x. 2 root root 21 1月 5 08:25 web
# ls -l /opt/web
-rw-r--r--. 1 root root 40 1月 5 08:23 02.html
# vi /etc/httpd/conf/httpd.conf
... //最後加上
Alias /opt_web /opt/web
<Directory /opt/web>
Require all granted
</Directory>
# systemctl restart httpd.service
http://172.31.147.99/opt_web/02.html
This page is on /opt/web...
//////////////////////////////////////////////////////// home目錄下
# mkdir /home/web
# vi /home/web/03.html
<h1>
This page is on /home/web...
</h1>
# ls -l /
...
drwxr-xr-x. 3 root root 17 1月 6 08:01 home
...
# ls -l /home
drwxr-xr-x. 2 root root 21 1月 6 08:03 web
# ls -l /home/web
-rw-r--r--. 1 root root 41 1月 6 08:03 03.html
# vi /etc/httpd/conf/httpd.conf
...
Alias /home_web /home/web
<Directory /home/web>
Require all granted
</Directory>
# systemctl restart httpd.service
http://172.31.147.99/home_web/03.html
Forbidden
You don't have permission to access this resource.
# tail -1 /var/log/httpd/error_log
[Thu Jan 06 08:13:12.616691 2022] [core:error] [pid 1839:tid 139953681770240] (13)Permission denied: [client 172.31.147.29:49884] AH00132: file permissions deny server access: /home/web/03.html
# man ls
...
-Z, --context
print any security context of each file
...
# ls -Z /var/www
system_u:object_r:httpd_sys_script_exec_t:s0 cgi-bin system_u:object_r:httpd_sys_content_t:s0 html
# ls -Z /var/www/html
unconfined_u:object_r:httpd_sys_content_t:s0 01.html
# ls -Z /opt
unconfined_u:object_r:usr_t:s0 web
# ls -Z /opt/web
unconfined_u:object_r:usr_t:s0 02.html
# ls -Z /home
unconfined_u:object_r:user_home_dir_t:s0 web
# ls -Z /home/web
unconfined_u:object_r:user_home_t:s0 03.html
# chcon -t httpd_sys_content_t /home/web
# ls -Z /home
unconfined_u:object_r:httpd_sys_content_t:s0 web
# ls -Z /home/web
unconfined_u:object_r:user_home_t:s0 03.html
# chcon -t httpd_sys_content_t /home/web/03.html
# ls -Z /home/web
unconfined_u:object_r:httpd_sys_content_t:s0 03.html
http://172.31.147.99/home_web/3.html
This page is on /home/web ...
# vi /home/web/04.html
This page is on /home/web TOO ....
# ls -Z /home/web
unconfined_u:object_r:httpd_sys_content_t:s0 03.html
unconfined_u:object_r:httpd_sys_content_t:s0 04.html
http://172.31.147.99/home_web/04.html
This page is on /home/web TOO ....
@@@
若要在 /home 下建立網站虛擬目錄，先
# chcon -t httpd_sys_content_t /home/web
再建立網頁，就不須再變更各網頁
@@@
----------------------------- UserDir個人網頁 -----------------------------
# firewall-cmd --zone=public --list-all
public (active)
target: default
icmp-block-inversion: no
interfaces: ens33
sources:
services: cockpit dhcpv6-client http ssh
...
@@@
//使用者家目錄的參考，建立 /etc/skel/public_html 這個目錄，那麼未來新增使用者後，在他的家目錄下就會有 public_html 那個目錄了
@@@
# useradd kk2
# passwd kk2
更改使用者 kk2 的密碼。
新 密碼：
...
# ls /home/kk2
public_html
# ls -l /home
drwx------. 3 kk2 kk2 81 1月 7 11:47 kk2
# chmod 711 /home/kk2
# ls -l /home
drwx--x--x. 3 kk2 kk2 81 1月 7 11:47 kk2
# ls -l /home/kk2
drwxr-xr-x. 2 kk2 kk2 6 1月 7 11:46 public_html
# vi /home/kk2/public_html/index.html
<h1>
This page is on /home/kk2/public_html...
</h1>
# vi /etc/httpd/conf.d/userdir.conf
# UserDir disabled
UserDir public_html
# systemctl restart httpd.service
http://172.31.145.99/~kk2/
Forbidden
You don't have permission to access this resource.
# tail -1 /var/log/httpd/error_log
[Fri Jan 07 12:00:19.818519 2022] [core:error] [pid 10425:tid 139701288851200] (13)Permission denied: [client 172.31.145.100:56402] AH00035: access to /~kk2/index.html denied (filesystem path '/home/kk2/public_html/index.html') because search permissions are missing on a component of the path
# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: enforcing
Mode from config file: enforcing
Policy MLS status: enabled
Policy deny_unknown status: allowed
Memory protection checking: actual (secure)
Max kernel policy version: 33
@@@@@@作法1
//Enforcing：SELinux 正在執行; Permissive ：臨時關閉; 如果是 Disabled, 即已經關閉
# getenforce
Enforcing
# setenforce 0
# getenforce
Permissive
http://172.31.145.99/~kk2/
This page is on /home/kk2/public_html...
# setenforce 1
# getenforce
Enforcing
@@@@@@作法2
# getsebool -a | grep httpd
httpd_anon_write --> off
httpd_builtin_scripting --> on
httpd_can_check_spam --> off
httpd_can_connect_ftp --> off
httpd_can_connect_ldap --> off
httpd_can_connect_mythtv --> off
httpd_can_connect_zabbix --> off
httpd_can_network_connect --> off
httpd_can_network_connect_cobbler --> off
httpd_can_network_connect_db --> off
httpd_can_network_memcache --> off
httpd_can_network_relay --> off
httpd_can_sendmail --> off
httpd_dbus_avahi --> off
httpd_dbus_sssd --> off
httpd_dontaudit_search_dirs --> off
httpd_enable_cgi --> on
httpd_enable_ftp_server --> off
httpd_enable_homedirs --> off
httpd_execmem --> off
httpd_graceful_shutdown --> off
httpd_manage_ipa --> off
httpd_mod_auth_ntlm_winbind --> off
httpd_mod_auth_pam --> off
httpd_read_user_content --> off
httpd_run_ipa --> off
httpd_run_preupgrade --> off
httpd_run_stickshift --> off
httpd_serve_cobbler_files --> off
httpd_setrlimit --> off
httpd_ssi_exec --> off
httpd_sys_script_anon_write --> off
httpd_tmp_exec --> off
httpd_tty_comm --> off
httpd_unified --> off
httpd_use_cifs --> off
httpd_use_fusefs --> off
httpd_use_gpg --> off
httpd_use_nfs --> off
httpd_use_opencryptoki --> off
httpd_use_openstack --> off
httpd_use_sasl --> off
httpd_verify_dns --> off
# setsebool -P httpd_enable_homedirs on
http://172.31.145.99/~kk2/
This page is on /home/kk2/public_html...
@@@@@@作法3
****************************************************
永久關閉 SELinux
# vi /etc/sysconfig/selinux
...
#SELINUX=enforcing
SELINUX=disabled
儲存檔案後重新啟動系統便會永久關閉 SELinux
****************************************************
---------------------------- VirtualHost 虛擬主機 --------------------------
# mkdir /var/www/asus
# vi /var/www/asus/index.html
This is <h1>Asus</h1> web ...
# mkdir /var/www/acer
# vi /var/www/acer/index.html
Thiss is <h1>Acer</h1> web ...
# vi /etc/httpd/conf/httpd.conf
...
<VirtualHost *:80>
DocumentRoot /var/www/html
ServerName 172.31.145.99
</VirtualHost>
<VirtualHost *:80>
DocumentRoot /var/www/asus
ServerName web.asus.com.tw
</VirtualHost>
<Directory "/var/www/asus">
Options Indexes
AllowOverride None
Require all granted
</Directory>
<VirtualHost *:80>
DocumentRoot /var/www/html
ServerName 172.31.145.99
</VirtualHost>
<VirtualHost *:80>
DocumentRoot /var/www/acer
ServerName web.acer.com.tw
</VirtualHost>
<Directory "/var/www/acer">
Options Indexes
AllowOverride None
Require all granted
</Directory>
...
# systemctl restart httpd.service
修改 windows10 hosts 檔
以系統管理員身分，執行記事本
檔案-->開啟舊檔：C:\Windows\System32\drivers\etc\hosts
新增資料：
172.31.145.99 web.asus.com.tw web.acer.com.tw
完成後-->檔案-->儲存檔案
http://web.asus.com.tw/
This is
Asus
web...
http://web.acer.com.tw/
This is
Acer
web..
++++++++++++++++++++++++++++++++++++++++++++++++ php
# dnf list | grep php
php.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-bcmath.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-cli.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-common.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-dba.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-dbg.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-devel.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-embedded.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-enchant.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-fpm.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-gd.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-gmp.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-intl.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
p-json.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-ldap.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-mbstring.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-mysqlnd.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-odbc.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-opcache.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-pdo.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-pear.noarch 1:1.10.5-9.module_el8.2.0+313+b04d0a66 AppStream
php-pecl-apcu.x86_64 5.1.12-2.module_el8.2.0+313+b04d0a66 AppStream
php-pecl-apcu-devel.x86_64 5.1.12-2.module_el8.2.0+313+b04d0a66 AppStream
php-pecl-zip.x86_64 1.15.3-1.module_el8.2.0+313+b04d0a66 AppStream
php-pgsql.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-process.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-recode.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-snmp.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-soap.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-xml.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
php-xmlrpc.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 AppStream
# dnf -y install php php-fpm php-gd php-mbstring php-mysqlnd
# dnf list installed | grep php
php.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-cli.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-common.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-fpm.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-gd.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-mbstring.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-mysqlnd.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
php-pdo.x86_64 7.2.24-1.module_el8.2.0+313+b04d0a66 @AppStream
# systemctl restart httpd
# ls /usr/sbin | grep php
php-fpm
# ls /usr/lib/systemd/system | grep php
php-fpm.service
# systemctl | grep php
php-fpm.service
# ls /etc/php.ini
# vi /var/www/html/test.php
<?php
phpinfo();
php?>
http://172.31.147.99/test.php
# vi /var/www/html/0.php
<?php
ini_set("display_errors","On");
// //
# vi /etc/php.ini
...
display_errors = On
...
# systemctl restart httpd
無效!!!!!!
// //
echo "kk<br>";
echo "瀏覽器中文編碼是否正常？<br>";
$sid="s01";
$str1="select * from tblmen where sid='".$sid."'";
$str2="select * from tblmen where sid='$sid'"; //php 字串以雙引號包起來，單引號會解析出其內容
echo $str1."<br>";
echo $str2."<br>";
echo $str3;
phpinfo();
?>
http://172.31.145.99/0.php
kk
編碼是否正常？
select * from tblmen where sid='s01'
select * from tblmen where sid='s01'
Notice: Undefined variable: str3 in /var/www/html/1.php on line 15
...
//刪除編輯錯誤時所產生的暫存檔
# rm -rf /var/www/html/.1.php.swp
++++++++++++++++++++++++++++++++++++++++++++++++MariaDB
# dnf list | grep mariadb
mariadb.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-backup.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-common.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-connector-c.i686 3.0.7-1.el8 AppStream
mariadb-connector-c.x86_64 3.0.7-1.el8 AppStream
mariadb-connector-c-config.noarch 3.0.7-1.el8 AppStream
mariadb-connector-c-devel.i686 3.0.7-1.el8 AppStream
mariadb-connector-c-devel.x86_64 3.0.7-1.el8 AppStream
mariadb-connector-odbc.x86_64 3.0.7-1.el8 AppStream
mariadb-devel.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-embedded.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-embedded-devel.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-errmsg.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-gssapi-server.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-java-client.noarch 2.2.5-3.el8 AppStream
mariadb-oqgraph-engine.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-server.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-server-galera.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-server-utils.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
mariadb-test.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 AppStream
# dnf -y install mariadb-server
# dnf list installed | grep mariadb
mariadb.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
mariadb-backup.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
mariadb-common.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
mariadb-connector-c.x86_64 3.0.7-1.el8 @AppStream
mariadb-connector-c-config.noarch 3.0.7-1.el8 @AppStream
mariadb-errmsg.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
mariadb-gssapi-server.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
mariadb-server.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
mariadb-server-utils.x86_64 3:10.3.17-1.module_el8.1.0+257+48736ea6 @AppStream
# ls /usr/lib/systemd/system | grep mariadb
mariadb@bootstrap.service.d mariadb.service mariadb@.service
# systemctl start mariadb
# systemctl enable mariadb
# systemctl status mariadb
# mysqladmin -u root password db123456
# mysql -u root -p
Enter password:
MariaDB [(none)]> select @@datadir;
+-----------------+
| @@datadir |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
?????????????????????????????????????????????????????????????????????? 無效
--------------- 資料庫字元集 character set 和連線校對 collaction -------------
MariaDB [(none)]> set character_set_database='utf8';
MariaDB [(none)]> set collation_database='utf8_unicode_ci';
MariaDB [(none)]> show variables like '%database';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| character_set_database | utf8 |
| collation_database | utf8_unicode_ci |
| skip_show_database | OFF |
+------------------------+-----------------+
MariaDB [(none)]> create database kkdb;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kkdb |
| mysql |
| performance_schema |
+--------------------+
MariaDB [(none)]> show create database kkdb \G;
*************************** 1. row ***************************
Database: kkdb
Create Database: CREATE DATABASE `kkdb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]> use kkdb;
MariaDB [kkdb]> create table tblmen (mid int primary key auto_increment, na varchar(20), accnt varchar(20), pwd varchar(20));
MariaDB [kkdb]> show tables;
+----------------+
| Tables_in_kkdb |
+----------------+
| tblmen |
+----------------+
MariaDB [kkdb]> describe tblmen;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| mid | int(11) | NO | PRI | NULL | auto_increment |
| na | varchar(20) | YES | | NULL | |
| accnt | varchar(20) | YES | | NULL | |
| pwd | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
MariaDB [kkdb]> show create table tblmen \G;
*************************** 1. row ***************************
Table: tblmen
Create Table: CREATE TABLE `tblmen` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`na` varchar(20) DEFAULT NULL,
`accnt` varchar(20) DEFAULT NULL,
`pwd` varchar(20) DEFAULT NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
?????? 如此作法有問題........
MariaDB [(none)]> show variables like '%character_set%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
MariaDB [(none)]> set character_set_database='utf8';
MariaDB [(none)]> show variables like '%character_set%';
+--------------------------+------------------------------+
| Variable_name | Value |
+--------------------------+------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mariadb/charsets/ |
+--------------------------+------------------------------+
MariaDB [(none)]> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
MariaDB [(none)]> show variables like '%database%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| character_set_database | utf8 |
| collation_database | utf8_general_ci |
| skip_show_database | OFF |
+------------------------+-----------------+
MariaDB [(none)]> create database testdb;
MariaDB [(none)]> use testdb;
MariaDB [testdb]> show variables like '%database%';
+------------------------+-------------------+
| Variable_name | Value |
+------------------------+-------------------+
| character_set_database | latin1 |
| collation_database | latin1_swedish_ci |
| skip_show_database | OFF |
+------------------------+-------------------+
MariaDB [testdb]> set character_set_database='utf8';
MariaDB [testdb]> show variables like '%database%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| character_set_database | utf8 |
| collation_database | utf8_general_ci |
| skip_show_database | OFF |
+------------------------+-----------------+
MariaDB [testdb]> show variables like '%database%';
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| character_set_database | utf8 |
| collation_database | utf8_general_ci |
| skip_show_database | OFF |
+------------------------+-----------------+
MariaDB [testdb]> create table tblmen (mid int primary key auto_increment, na varchar(20), accnt varchar(20), pwd varchar(20));
MariaDB [testdb]> show create table tblmen \G;
*************************** 1. row ***************************
Table: tblmen
Create Table: CREATE TABLE `tblmen` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`na` varchar(20) DEFAULT NULL,
`accnt` varchar(20) DEFAULT NULL,
`pwd` varchar(20) DEFAULT NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
??????????????????????????????????????????????????????????????????????
MariaDB [(none)]> create database kkdb character set='utf8' collate='utf8_unicode_ci';
MariaDB [(none)]> show variables like '%database';
+------------------------+-------------------+
| Variable_name | Value |
+------------------------+-------------------+
| character_set_database | latin1 |
| collation_database | latin1_swedish_ci |
| skip_show_database | OFF |
+------------------------+-------------------+
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kkdb |
| mysql |
| performance_schema |
+--------------------+
MariaDB [(none)]> use kkdb;
MariaDB [kkdb]> create table tblmen (mid int primary key auto_increment, na varchar(20), accnt varchar(20), pwd varchar(20));
MariaDB [kkdb]> show tables;
+----------------+
| Tables_in_kkdb |
+----------------+
| tblmen |
+----------------+
MariaDB [kkdb]> describe tblmen;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| mid | int(11) | NO | PRI | NULL | auto_increment |
| na | varchar(20) | YES | | NULL | |
| accnt | varchar(20) | YES | | NULL | |
| pwd | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
MariaDB [kkdb]> show create table tblmen \G;
*************************** 1. row ***************************
Table: tblmen
Create Table: CREATE TABLE `tblmen` (
`mid` int(11) NOT NULL AUTO_INCREMENT,
`na` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`accnt` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`pwd` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
MariaDB [kkdb]> insert into tblmen values(1,'蔡英九','act1','pwd1'); //AUTO_INCREMENT 無效
MariaDB [kkdb]> insert into tblmen (na,accnt,pwd) values('關羽','act5','pwd5'); //ok! 要寫出欄名
MariaDB [kkdb]> select * from tblmen;
+-----+-----------+-------+------+
| mid | na | accnt | pwd |
+-----+-----------+-------+------+
| 1 | 蔡英九 | act1 | pwd1 |
+-----+-----------+-------+------+
===========================
# vi /var/www/html/LinkServer.php
<?php
$host = 'localhost';
$db = 'kkdb';
$user = 'root';
$pwd = 'db123456';
$db_link=new pdo("mysql:host=$host;dbname=$db", $user, $pwd);
$db_link->exec("set character set utf8");
?>
===========================
# vi /var/www/html/list.php
<?php
include("LinkServer.php");
$rt=$db_link->query("select * from tblmen");
$rows=$rt->fetch();
?>
<html>
<head></head>
<body>
<h1 align="center">學生系統</h1>
<p align="center"><a href="add.php">新增學生資料</a> <a href="query.php">查詢學生資料</a></p>
<table border=1 align="center">
<tr><td>編號</td><td>名字</td><td>帳號</td><td>密碼</td></tr>
<?php
while (!empty($rows)){
echo "<tr>";
echo "<td>".$rows["mid"]."</td>";
echo "<td>".$rows["na"]."</td>";
echo "<td>".$rows["accnt"]."</td>";
echo "<td>".$rows["pwd"]."</td>";
echo "<td><a href='update.php?mid=".$rows["mid"]."'>修改</a></td>";
echo "<td><a href='delete.php?mid=".$rows["mid"]."'>刪除</a></td>";
echo "</tr>";
$rows=$rt->fetch();
}
$db_link=null;
?>
</table>
</body>
</html>
===========================
# vi /var/www/html/add.php
<?php
if ($_POST["act"]=="add"){
include("LinkServer.php");
//不用mid，可自動寫入
$rt=$db_link->query("INSERT INTO `tblmen`(`na`, `accnt`, `pwd`) VALUES ('".$_POST["na"]."','".$_POST["accnt"]."','".$_POST["pwd"]."')");
// $rt=$db_link->query("INSERT INTO `tblmen`(`mid`, `na`, `accnt`, `pwd`) VALUES //('".$_POST["mid"]."','".$_POST["na"]."','".$_POST["accnt"]."','".$_POST["pwd"]."')");
header("Location:list.php");
}
?>
<html>
<head></head>
<body>
<h1 align="center">新增資料</h1>
<p align="center"><a href="list.php">返回主畫面</a></p>
<form action="" method="post" name="formAdd">
<table align="center">
//mid 輸入欄位 disableded
<tr><td>編號</td><td><input type="text" name="mid" disabled></td></tr>
//<tr><td>編號</td><td><input type="text" name="mid"></td></tr>
<tr><td>名字</td><td><input type="text" name="na"></td></tr>
<tr><td>帳號</td><td><input type="text" name="accnt"></td></tr>
<tr><td>密碼</td><td><input type="text" name="pwd"></td></tr>
<tr><td></td><td>
<input type="hidden" value="add" name="act">
<input type="submit" value="新增" name="ok">
<input type="reset" value="清除" name="erase">
</td></tr>
</table>
</form>
</body>
</html>
===========================
# vi /var/www/html/update.php
<?php
include("LinkServer.php");
if ($_POST["act"]=="update"){
$str="update tblmen set na='".$_POST["na"]."',accnt='".$_POST["accnt"]."',pwd='".$_POST["pwd"]."' where mid =".$_POST["mid"];
header("Location:list.php");
}
else{
$str="select * from tblmen where mid=".$_GET["mid"];
}
$rt=$db_link->query($str);
$rows=$rt->fetch();
?>
<html>
<head></head>
<body>
<h1 align="center">修改資料</h1>
<p align="center"><a href="list.php">返回主畫面</a></p>
<form action="" method="post" name="formUpdate">
<table align="center">
<tr><td>編號</td><td><input type="text" name="mid" value="<?php echo $rows["mid"]; ?>" disabled></td></tr>
<tr><td>名字</td><td><input type="text" name="na" value="<?php echo $rows["na"]; ?>"></td></tr>
<tr><td>帳號</td><td><input type="text" name="accnt" value="<?php echo $rows["accnt"]; ?>"></td></tr>
<tr><td>密碼</td><td><input type="text" name="pwd" value="<?php echo $rows["pwd"]; ?>"></td></tr>
<tr><td></td><td>
<input type="hidden" value="<?php echo $rows["mid"]; ?>" name="mid">
<input type="hidden" value="update" name="act">
<input type="submit" value="更新" name="ok">
<input type="reset" value="清除" name="erase">
</td></tr>
</table>
</form>
</body>
</html>
===========================
#vi /var/www/html/query.php
<html>
<head></head>
<body>
<h1 align="center">會員查詢</h1>
<p align="center"><a href="list.php">返回主畫面</a></p>
<form action="" method="post" name="formQuery">
<table align="center">
<tr><td>姓名</td><td><input type="text" name="na"></td><td></td><td></td></tr>
<tr><td></td><td>
<input type="hidden" value="query" name="act">
<input type="submit" value="查詢" name="ok">
</td><td></td><td></td></tr>
</table>
<?php
if($_POST["act"]=="query"){
include("LinkServer.php");
$rt=$db_link->query("select * from tblmen where na LIKE '%".$_POST["na"]."%'");
$rows=$rt->fetch();
echo "<table align='center' >";
echo "<tr><td>編號</td><td>名字</td><td>帳號</td><td>密碼</td></tr>";
echo "<tr><td>".$rows["mid"]."</td><td>".$rows["na"]."</td><td>".$rows["accnt"]."</td><td>".$rows["pwd"]."</td></tr>";
echo "</table>";
}
?>
</form>
</body>
</html>
===========================
# vi /var/www/html/delete.php
<?php
include("LinkServer.php");
$rt=$db_link->query("delete from tblmen where mid ='".$_GET["mid"]."'");
?>
<html>
<head></head>
<body>
<h1 align="center">資料刪除完畢！</h1>
<p align="center"><a href="list.php">返回主畫面</a></p>
</body>
</html>
===========================
# vi /var/www/html/index.php
<?php
if ($_POST["act"]=="login"){
include("LinkServer.php");
$rt=$db_link->query("select * from tblmen where accnt ='".$_POST["accnt"]."' and pwd ='".$_POST["pwd"]."'");
$data_cnt=$rt->rowCount();
if ($data_cnt==1){
header("Location:list.php");
}else{
header("Location:index.php");
}
}
?>
<html>
<head></head>
<body>
<h1 align="center">會員登入</h1>
<form action="" method="post" name="formAdd">
<table align="center">
<tr><td>帳號</td><td><input type="text" name="accnt"></td></tr>
<tr><td>密碼</td><td><input type="text" name="pwd"></td></tr>
<tr><td></td><td>
<input type="hidden" value="login" name="act">
<input type="submit" value="登入" name="ok">
</td></tr>
</table>
</form>
</body>
</html>
===========================
Table 匯出、匯入
# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
//欄位分隔符號，預設"\t"
//每行結束符號，預設"\n"
MariaDB [kkdb]> select * from tblmen into outfile 'tblmen.txt';
MariaDB [kkdb]> Ctrl-C -- exit!
Aborted
#
# cat /var/lib/mysql/kkdb/tblmen.txt
1 蔡英九 act1 pwd1
2 蘇太多 act2 pwd2
3 陳偉 act3 pwd3
4 趙少主 act44 pwd44
# vi /var/lib/mysql/kkdb/tblmen.txt
1 蔡英九 act1 pwd1
2 蘇太多 act2 pwd2
3 陳偉 act3 pwd3
4 趙少主 act44 pwd44
5 王政正 act5 pwd5
6 韓國仁 act6 pwd6
---------------------------------
刪除原資料(清空)再匯入
---------------------------------
MariaDB [kkdb]> delet from tblmen;
MariaDB [kkdb]> load data infile 'tblmen.txt' into table tblmen;
MariaDB [kkdb]> select * from tblmen;
+-----+-----------+-------+-------+
| mid | na | accnt | pwd |
+-----+-----------+-------+-------+
| 1 | 蔡英九 | act1 | pwd1 |
| 2 | 蘇太多 | act2 | pwd2 |
| 3 | 陳偉 | act3 | pwd3 |
| 4 | 趙少主 | act44 | pwd44 |
| 5 | 王政正 | act5 | pwd5 |
| 6 | 韓國仁 | act6 | pwd6 |
+-----+-----------+-------+-------+
---------------------------------
在原有資料後匯入新資料
---------------------------------
MariaDB [kkdb]> select * from tblmen;
+-----+-----------+---------+-------+
| mid | na | accnt | pwd |
+-----+-----------+---------+-------+
| 1 | 蕭蕭 | accnt01 | pwd01 |
| 6 | 老樹 | accnt6 | pwd6 |
| 7 | 公投 | accnt1 | pwd1 |
| 8 | 擁核 | accnt2 | pwd2 |
| 9 | 陳偉 | act3 | pwd3 |
| 10 | 趙少主 | act44 | pwd44 |
| 11 | 王政正 | act5 | pwd5 |
| 12 | 韓國仁 | act6 | pwd6 |
+-----+-----------+---------+-------+
8 rows in set (0.000 sec)
//原空的mid 可以再回收使用
# vi /var/lib/mysql/tblmen.txt
3 蕭蕭 accnt01 pwd01
13 杜莎 accnt13 pwd13
MariaDB [kkdb]> load data infile "tblmen.text" into table tblmen;
Query OK, 2 rows affected (0.001 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [kkdb]> select * from tblmen;
+-----+-----------+---------+-------+
| mid | na | accnt | pwd |
+-----+-----------+---------+-------+
| 1 | 蕭蕭 | accnt01 | pwd01 |
| 3 | 蕭蕭 | accnt01 | pwd01 |
| 6 | 老樹 | accnt6 | pwd6 |
| 7 | 公投 | accnt1 | pwd1 |
| 8 | 擁核 | accnt2 | pwd2 |
| 9 | 陳偉 | act3 | pwd3 |
| 10 | 趙少主 | act44 | pwd44 |
| 11 | 王政正 | act5 | pwd5 |
| 12 | 韓國仁 | act6 | pwd6 |
| 13 | 杜莎 | accnt13 | pwd13 |
+-----+-----------+---------+-------+
10 rows in set (0.000 sec)
Database 備份、還原
//備份資料庫，沒寫 table 代表全部 database
# mysqldump -u root -p kkdb > kkdb.bk.sql
Enter password:
# ls
anaconda-ks.cfg kkdb.bk.sql
//刪除database
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kkdb |
| mysql |
| performance_schema |
+--------------------+
MariaDB [(none)]> drop database kkdb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './kkdb', errno: 39 "Directory not empty")
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kkdb |
| mysql |
| performance_schema |
+--------------------+
MariaDB [(none)]> use kkdb;
MariaDB [kkdb]> show tables;
Empty set (0.000 sec)
?????database 還在 但 table 已刪除
?????因為 在var/lib/mysql/kkdb/ 有曾經匯出的 table 資料
# ls /var/lib/mysql/kkdb/tblmen.txt
/var/lib/mysql/kkdb/tblmen.txt
# rm /var/lib/mysql/kkdb/tblmen.txt
rm：是否移除普通檔案'/var/lib/mysql/kkdb/tblmen.txt'? y
?????刪除在var/lib/mysql/kkdb/ 曾經匯出的 table 資料 即可完整刪除database
MariaDB [(none)]> drop database kkdb;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
# ls /var/lib/mysql/kkdb
ls: 無法存取 '/var/lib/mysql/kkdb': No such file or directory
//資料庫還原
//先建立空資料庫
MariaDB [(none)]> create database kkdb character set='utf8' collate='utf8_unicode_ci';
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kkdb |
| mysql |
| performance_schema |
+--------------------+
//還原
# mysql -u root -p kkdb < kkdb.bk.sql
Enter password:
# ls /var/lib/mysql/kkdb
db.opt tblmen.frm tblmen.ibd
MariaDB [(none)]> use kkdb;
MariaDB [kkdb]> show tables;
+----------------+
| Tables_in_kkdb |
+----------------+
| tblmen |
+----------------+
MariaDB [kkdb]> select * from tblmen;
+-----+-----------+-------+-------+
| mid | na | accnt | pwd |
+-----+-----------+-------+-------+
| 1 | 蔡英九 | act1 | pwd1 |
| 2 | 蘇太多 | act2 | pwd2 |
| 3 | 陳偉 | act3 | pwd3 |
| 4 | 趙少主 | act44 | pwd44 |
| 5 | 王政正 | act5 | pwd5 |
| 6 | 韓國仁 | act6 | pwd6 |
+-----+-----------+-------+-------+
-----------------------------------------------------------
沒有留言:
張貼留言