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 管理者

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