mariadb first login

 好用工具 https://www.adminer.org/

# mark bind address
vim /etc/mysql/mariadb.conf.d/50-server.cnf
#bind-address = 127.0.0.1

# add password for root
sudo mysqladmin --user=root password "newpassword"
# 不顯示在命令列上
sudo mysqladmin --user=root password

# login mariadb
sudo mysql -u root -p
  use mysql;
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY '密碼' WITH GRANT OPTION;
  FLUSH PRIVILEGES;
  exit;

# update password
Update user SET password=PASSWORD("密碼") WHERE user="使用者";
# mariadb 10.4+ 以後要用這樣改
SET PASSWORD FOR '使用者'@'位置' = PASSWORD('密碼');

# restart mariadb
sudo systemctl restart mariadb.service

# create database unicode_ci容量較大但支援較佳, general_ci則相反. utf8mb4才是4位元的真正utf8
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# delete database
DROP DATABASE mydatabase;
# delete tables
USE DATABASE_NAME;
DROP TABLE table_name;
如果要重改資料庫, 記得先備份, 通常是放在匯出後的上方
DROP DATABASE IF EXISTS db_name;
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db_name;

# create user
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

打過的指令會被存在 /home/username/.mysql_history 裡, 要小心.

備份與還原
# mysqldump -u root -p --all-databases --lock-tables=false > backup.sql;
# mysqldump -h hostname -u root -p database_name > backup.sql;
# mysqldump -h hostname -u root -pPASSWORD database_name > backup.sql;
只備份某幾個表
# mysqldump -h hostanme -u root -p database_name t1 t2 t3 > backup_tables.sql
排除某幾個表
# mysqldump -h hostname -u root -p database_name --ignore-table=Table1 --ignore-table=Table2 > backup.sql;
還原
# mysql -u root -p < backup.sql

忘記密碼
# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD("password") WHERE User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql start

# 檢查使用者權限
SELECT user,host FROM db WHERE db='name';
SELECT user,host FROM tables_priv WHERE db='name';
SELECT user,host FROM columns_priv WHERE db='name';
SELECT user,host FROM procs_priv WHERE db='name';

# 有時候使用者不能登入
# 主要是被plugin unix_socket這個的問題
sudo mysql -uroot -p
SELECT user, plugin FROM mysql.user;
# 有就清掉
UPDATE mysql.user SET plugin="";
# 加上root密碼
UPDATE mysql.user SET password=PASSWORD("your_new_password") WHERE user="root";
# 刷新一下
FLUSH PRIVILEGES;

# 查目前編碼設定
SHOW VARIABLES LIKE 'collation%';

# 查詢編碼
SELECT 
    default_character_set_name, 
    default_collation_name 
FROM information_schema.schemata 
WHERE schema_name = 'DATABASE_NAME';

# 查詢表編碼
SELECT 
    table_schema, 
    table_name, 
    table_collation 
FROM information_schema.tables 
WHERE table_schema = 'TABLE_NAME';