mysql 操作

$ sudo mysql -u root -p
[sudo] password for user: 輸入 os root 密碼
Enter password: 輸入 mysql root 密碼

顯示所有 database
> show databases;

進入某個資料庫
> use db_name;

顯示 資料庫內 所有資料表
> show tables;

顯示 資料表的欄位
> describe tab_name;
顯示 資料表的欄位細節
> show full columns from tab_name;

顯示所有使用者
> describe mysql.user;
> select host, user from mysql.user;

顯示使用者權限
> describe mysql.db;
> select host, db, user from mysql.db;

顯示 grant 的權限
> show grants for 'user'@'%';
> show grants for 'user'@'localhost';

建立使用者
> create user 'user'@'hostname' identified by 'password';
hostname 可以為 ip 或 % 表示任何ip
刪除使用者
> drop user 'user'@'hostname';

grant 權限
> grant all on db_name.* to 'user'@'localhost';
移除 grant 權限
> revoke all privileges on db_name.table from 'user'@'hostname';

建立資料庫
> create database db_name;
刪除資料庫
> drop database db_name;

建立資料表
> create table tab_name (
  id int not null auto_increment,
  name varchar(50) not null,
  desc varchar(200),
  price int not null,
  primary key(id)
);
刪除資料表
> drop table tab_name;

執行 sql 檔
> source file.sql
> \. file.sql
shell 直接執行 sql
$ mysql -u root -p < file.sql

執行 shell
> system ls
> \! ls

gitea in ubuntu

參考 如何在Ubuntu 18.04上安裝Gitea

安裝 Nginx
$ sudo apt update
$ sudo apt -y install nginx
安裝完成可以用瀏覽器測試 http://localhost
自動啟動 nginx
$ sudo systemctl stop nginx.service
$ sudo systemctl start nginx.service
$ sudo systemctl restart nginx.service
$ sudo systemctl reload nginx.service
$ sudo systemctl enable nginx.service

安裝 Git
$ sudo apt -y install git
$ git --version

安裝 MariaDB
$ sudo apt -y install mariadb-server mariadb-client
$ sudo systemctl stop mariadb.service
$ sudo systemctl start mariadb.service
$ sudo systemctl restart mariadb.service
$ sudo systemctl reload mariadb.service
$ sudo systemctl enable mariadb.service

建立 MariaDB root 密碼，並禁止遠端連接
$ sudo mysql_secure_installation
Enter current password for root (enter for none): Just press the Enter
Set root password? [Y/n]: Y
New password: Enter password
Re-enter new password: Repeat password
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

重新啟動 MariaDB
$ sudo systemctl restart mariadb.service

建立 gitea 資料庫, giteauser 使用者

$ sudo mysql -u root -p

CREATE DATABASE gitea;

CREATE USER 'giteauser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON gitea.* TO 'giteauser'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

建立 git 使用者，以便運行 gitea
$ sudo adduser --system --shell /bin/bash --gecos 'Git Version Control' --group --disabled-password --home /home/git git
$ sudo mkdir -p /var/lib/gitea/{custom,data,indexers,public,log}
$ sudo chown git:git /var/lib/gitea/{data,indexers,log}
$ sudo chmod 750 /var/lib/gitea/{data,indexers,log}
$ sudo mkdir /etc/gitea
$ sudo chown root:git /etc/gitea
$ sudo chmod 770 /etc/gitea

下載並安裝 gitea
$ sudo wget -O gitea https://dl.gitea.io/gitea/1.5.0/gitea-1.5.0-linux-amd64
$ sudo chmod +x gitea
$ sudo cp gitea /usr/local/bin/gitea
$ sudo vi /etc/systemd/system/gitea.service
[Unit]
Description=Gitea (Git with a cup of tea)
After=syslog.target
After=network.target
#After=mysqld.service
#After=postgresql.service
#After=memcached.service
#After=redis.service

[Service]
# Modify these two values and uncomment them if you have
# repos with lots of files and get an HTTP error 500 because
# of that
###
#LimitMEMLOCK=infinity
#LimitNOFILE=65535
RestartSec=2s
Type=simple
User=git
Group=git
WorkingDirectory=/var/lib/gitea/
ExecStart=/usr/local/bin/gitea web -c /etc/gitea/app.ini
Restart=always
Environment=USER=git HOME=/home/git GITEA_WORK_DIR=/var/lib/gitea
# If you want to bind Gitea to a port below 1024 uncomment
# the two values below
###
#CapabilityBoundingSet=CAP_NET_BIND_SERVICE
#AmbientCapabilities=CAP_NET_BIND_SERVICE

[Install]
WantedBy=multi-user.target

$ sudo systemctl daemon-reload
$ sudo systemctl enable gitea
$ sudo systemctl start gitea
$ sudo system status gitea

使用 Nginx 代理 gitea
$ sudo vi /etc/nginx/sites-available/git
upstream gitea {
 server 127.0.0.1:3000;
}

server {
 listen 80 default_server;
 listen [::]:80 default_server;
 server_name example.com;
 root /var/lib/gitea/public;
 access_log off;
 error_log off;

 location / {
 try_files maintain.html $uri $uri/index.html @node;
 }

 location @node {
 client_max_body_size 0;
 proxy_pass http://localhost:3000;
 proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
 proxy_set_header X-Real-IP $remote_addr;
 proxy_set_header Host $http_host;
 proxy_set_header X-Forwarded-Proto $scheme;
 proxy_max_temp_file_size 0;
 proxy_redirect off;
 proxy_read_timeout 120;
 }
}

$ sudo ln -s /etc/nginx/sites-available/git /etc/nginx/sites-enabled

$ sudo systemctl reload nginx.service

開啟網頁設定 Gitea

http://localhost/install

---

錯誤排除

最後安裝動作 http://localhost:3000 出現下列錯誤
Index column size too large. The maximum column size is 767 bytes

sudo mysql -u root -p
[sudo] password for user: (os 的密碼)
Enter password:(mysql 的密碼)
MariaDB [(none)]> show global variables like 'innodb_large%';
MariaDB [(none)]> set global innodb_large_prefix = ON;
MariaDB [(none)]> set global innodb_default_row_format=dynamic;

MariaDB [(none)]> select user, host from mysql.user;
MariaDB [(none)]> show grants for 'giteauser'@'localhost';
MariaDB [(none)]> revoke all privileges, grant option from 'giteauser'@'localhost';
MariaDB [(none)]> drop user 'giteauser'@'localhost';
MariaDB [(none)]> drop database gitea;

http://localhost:3000/install
只能執行一次，下一次就回 404
sudo vi /etc/gitea/app.ini
INSTALL_LOCK=false
sudo systemctl stop gitea
sudo systemctl start gitea

http://localhost:3000/install

主要填寫 Database

Username=giteauser

Password=

點開 Server and Third-Party Service Settings

Disable Self-Registration

Require Sign-In to View Pages

點開 Administrator Account Settings

建立第一個 gitea 管理者

Kennel 升級紀錄

Windows 10 回復原廠設定不能使用
出現 "重設您的電腦時發生問題 沒有做任何變更"
從官方網站下載 Windows 10
此時會有兩種選項，一是升級本身的電腦，二是建立安裝媒體(USB 隨身碟 8G)
兩者皆有測試過
似乎授權已經寫入 BIOS, 所以即使換硬碟重灌也可以直接啟用

部分的硬碟分割區無法刪除
需用 命令題是字元 執行 diskpart
DISKPART> list disk
DISKPART> select disk 1
DISKPART> list disk

DISKPART> list partition
DISKPART> clean

資料庫使用
mariadb-10.2.14-winx64.msi
備份使用
dump.bat
@echo off
set dt=%date:~0,10%
rem date format is "YYYY-MM-DD"
set dy=%dt:~0,4%
set dm=%dt:~5,2%
set dd=%dt:~8,2%
rem echo %dt%
set TODAY=%dy%%dm%%dd%

dump.bat

call today.bat
"c:\Program Files\MariaDB 10.2\bin\mysqldump.exe" -u root -pmark1234 -B kennel util>mariadb_%today%.sql
"c:\Program Files\7-Zip\7z.exe" a -tzip mariadb_%today%.sql.zip mariadb_%today%.sql
del mariadb_%today%.sql

開發環境使用
jdk-8u172-windows-i586.exe (給舊的 Eclipse 3 使用 32位元環境)
jdk-8u172-windows-x64.exe
eclipse-jee-oxygen-3a-win32-x86_64.zip
External JARs 換位置了，之前可以隨便放，現在必須放在 WEB-INF\lib
否則程式執行時會發生 ClassNotFoundException
使用 Tomcat v9.0 搭配 java 10 時，發生
-Djava.endorsed.dirs=C:\Program Files\Apache Software Foundation\Tomcat 9.0\endorsed is not supported.
錯誤，即使將 Run/Debug Configurations/Arguments/VM arguments 內的

-Djava.endorsed.dirs="C:\Program Files\Apache Software Foundation\Tomcat 9.0\endorsed"

刪除，下次執行時，依舊又出現，換成舊版的 java 即可解決

網站使用
apache-tomcat-9.0.7.exe (不能搭配 java 10)
修改 config 下的 server.xml 和 context.xml 請注意權限
若不修改權限，作業系統會搞鬼，讓你以為修改成功，浪費時間抓蟲
Tomcat v6.0 時, server.xml 為

<Context docBase="Main" path="/Kennel" reloadable="true" source="org.eclipse.jst.jee.server:Main">
<Resource name="jdbc/MainDS" auth="Container" type="javax.sql.DataSource" maxActive="4" maxIdle="2" maxWait="4" username="xxxx" password="xxxx" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://127.0.0.1:3306/?useUnicode=true&amp;amp;characterEncoding=big5"></Resource></Context>
Tomcat v9.0 時, server.xml 不用動, 反而是修改 context.xml
    <Resource 

    name="jdbc/MainDS" 

    auth="Container" 

    type="javax.sql.DataSource" 

    maxTotal="4" 

    maxIdle="2" 

    maxWaitMillis="-1" 

    username="xxxx" 

    password="xxxx" 

    driverClassName="org.mariadb.jdbc.Driver" 

    url="jdbc:mariadb://localhost:3306/?useUnicode=true&amp;characterEncoding=utf8">

    </Resource>

注意其中 maxActive 改成 maxTotal, maxWait 變成 maxWaitMillis

另外 DBCP 也有變化，由

<Resource

    name="jdbc/MainDb"

    scope="Shareable"

    type="javax.sql.DataSource"

    factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"

    maxTotal="4" maxIdle="2" maxWaitMillis="-1"

    url="jdbc:mariadb://localhost:3306"

    driverClassName="org.mariadb.jdbc.Driver"

    username="xxxx"

    password="xxxx"

    />

變成

<Resource

    name="jdbc/MainDb"

    scope="Shareable"

    type="javax.sql.DataSource"

    factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"

    maxTotal="4" maxIdle="2" maxWaitMillis="-1"

    url="jdbc:mariadb://localhost:3306"

    driverClassName="org.mariadb.jdbc.Driver"

    username="xxxx"

    password="xxxx"

    />

MySQL 的替代者 MariaDB

MySQL 有閉源的風險，用 MariaDB 取代

安裝時在 Custom Setup 時可以設定資料庫位置
MariaDB Server/Database instance

執行 HeidiSQL
按 Menu/文件/加載SQL文件
選擇 sql 檔 和 UTF-8編碼

Linux 下搬移資料位置
1.Stop MariaDB using the following command:
 service mariadb stop
2.Copy the existing data directory using the following command:
 sudo cp -R -p /oldpath /newpath
3.edit the MariaDB configuration file /etc/my.cnf.d/server.cnf(my.ini)
4.Restart MariaDB with the command:
 service mariadb start


使用命令安裝，指定資料位置
msiexec /i path-to-package.msi [PROPERTY_1=VALUE_1 ... PROPERTY_N=VALUE_N] /qn
INSTALLDIR:%ProgramFiles%\MariaDB<version>\
DATADIR:INSTALLDIR\data