MySQL

MariaDB 執行檔存放位置

/usr/local/mariadb10/bin/mysql

MySQL 常用指令

登入

mysql -u USER -pPASSWORD

切換DATABASE

USE dbName;

更改 MySQL root 密碼

Method 1
$ /usr/bin/mysqladmin -u root -h localhost password 'password'

Method 2

$ sudo /etc/init.d/mysql stop
$ sudo mysqld --skip-grant-tables &
$ mysql -u root mysql
> UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
> FLUSH PRIVILEGES;
> exit;

Method 3

$ mysql
> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
> flush privileges;

Method 4

$ sudo /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables &
$ mysql -u root mysql
> UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root';
> FLUSH PRIVILEGES;
> exit;

更改 MySQL 一般使用者密碼

**Since MySQL 5.7    -- it removed origin syntax word Password, has changed to authentication_strin**  
`UPDATE mysql.user SET authentication_string=PASSWORD('myPassword') WHERE USER='myUSER'`

database List or table List

SHOW dbName;
SHOW tableName;

建立新的database

CREATE DATABASE databaseName;

建立新的USER

CREATE USER 'userName'@'localhost' IDENTIFIED BY 'newpassword';

指定資料庫使用者及其權限 ALL

GRANT ALL PRIVILEGES ON newdatabase.* TO 'newuser'@'localhost';

刷新權限

FLUSH PRIVILEGES;



修改預設資料庫字元集

sudo vi /etc/mysql/my.cnf

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]

collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8}

sudo systemctl restart mysql.service

檢查字元集

USE your_database_name;
show variables like "character_set_database";

For all the Database you have

SELECT SCHEMA_NAME 'database', default_character_set_name 'charset', DEFAULT_COLLATION_NAME 'collation' FROM information_schema.SCHEMATA;

MySQL 5.7 初始權限設定

ERROR1045, EEROR1698 無法登入時 #

$ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User             | plugin                |
+------------------+-----------------------+
| root             | auth_socket           |
| mysql.sys        | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+

看到這個 auth_socket，那就必須進行以下兩個操作

root 權限適用，但不適用於其他新建的使用者

```bash $ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart # 可以不必等全部完成再做
```

適用root & 一般使用者

```bash $ sudo mysql -u root # I had to use "sudo" since is new installation

mysql> USE mysql;
mysql> CREATE USER 'USER'@'localhost' IDENTIFIED BY '';  #修改user名稱並填入密碼
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;

$ service mysql restart
```

檢查資料庫大小

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema