一文總結Mysql資料庫應用及常見問題

一、安裝mysql

1、CentOS系統

1.）安裝mysql

下載並安裝mysql的repo源

$ wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
$ sudo rpm -ivh mysql-community-release-el7-5.noarch.rpm

安裝mysql

$ sudo yum install -y mysql-server

2.）啟動/重新啟動/關閉服務

service mysqld start | restart | stop

2、alpine系統

alpine系統中安裝mysql實際是開源的MariaDB，MariaDB資料庫是MySQL的一個分支/衍生版，完全相容MySQL，並在擴充套件功能、儲存引擎以及一些新的功能改進方面都強過MySQL，安裝參考

$ apk update

# 安裝資料庫及使用者端
$ apk add mysql mysql-client

# 初始化資料庫
$ mysql_install_db --user=mysql --datadir=/var/lib/mysql

# 啟動服務
$ rc-service mariadb start  # 若沒有rc，安裝：apk add openrc

# 修改密碼
$ mysqladmin -u root password '新root密碼'

# 加入開機啟動
$ rc-update add mariadb default

二、操作mysql

1、登入資料庫

預設初始化直接登入

$ mysql

若提示失敗，說明有密碼，執行：

$ mysql -uroot -p

# 一次性登入
$ mysql -u使用者 -p密碼

2、運算元據庫

# 檢視所有資料庫
mysql> show databases;

# 建立資料庫
mysql> create database xxx charset=utf8;

# 刪除資料庫
mysql> drop database xxx;

# 切換資料庫
mysql> use mysql;

# 檢視表
mysql> show tables;

# 建立表
mysql> create table xxx (
    id int,
    name varchar(20),
    update_time datetime
);

# 刪除表
mysql> drop table xxx;

# 顯示錶結構
mysql> desc xxx;

# 查詢指定a,b欄位的記錄,不知道用*代替
mysql> select a,b form xxx;

# 插入記錄
mysql> insert into xxx(id,user) values(1,'wang',now());

# 刪除記錄
mysql> delete from xxx where name='wang';

# 清屏命令
mysql> system clear;

# 退出
mysql> quit;

3、使用者管理

mysql的user表用來儲存所有使用者許可權，其中host欄位表示指定ip使用者能使用，同名不同host的為兩個使用者，host常見值如下：

localhost：只能伺服器端上使用
192.168.4.%：指定ip段能用
%：萬用字元，表示所有ip使用者都能用，多用於遠端連線

下面新建/刪除使用者操作時，可通過user@'host'指定，若不指定預設為%

# 切換到mysql許可權資料庫
mysql> use mysql;

# 檢視使用者及所屬的host
mysql> select user,host from user;

# 新建使用者並設定密碼
mysql> create user '使用者名稱' identified by '密碼';

# 上面不指定host，預設%，等同於：
mysql> create user '使用者名稱'@'%' identified by '密碼';

# 刪除使用者(只刪除host為%的使用者)
mysql> drop user '使用者名稱';

# 檢視使用者許可權
mysql> show grants for '使用者名稱';

# 設定許可權，並指定資料庫
mysql> grant all privileges on xxxDB.* to '使用者名稱';

# 修改密碼，注意密碼處不能直接password='新密碼'
mysql> update user set password=password('新密碼') where user='使用者名稱';

# 重新整理許可權表
mysql> flush privileges;

4、批次操作

1.）sql指令碼批次執行

$ mysql -uroot -p -Dxxx < ./init.sql    # xxx為資料庫

2.）備份/恢復資料庫

$ mysqldump --all-databases -h127.0.0.1 -u root -p > ./backup/mysql-bak.sql
$ mysqldump --all-databases -h127.0.0.1 -u root -p < ./backup/mysql-bak.sql

三、常見問題

1、root密碼忘記怎麼擦除？

$ mysql –skip-grant-table &
  mysql> use mysql;
  mysql> update user set password=password('新密碼') where user='root';
  mysql> flush privileges;
  mysql> quit;

這裡需要注意的是每句命令需要用分號「;」結尾,執行完以上得操作，root的密碼就被清空。

2、空使用者錯誤

ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'錯誤：

原因：

mysql資料庫的user表裡，存在使用者名稱為空的賬戶即匿名賬戶，導致登入的時候雖然用的是root，但實際是匿名登入的。

處理方案：

# 1.關閉mysql
$ service mysqld stop

# 2.遮蔽許可權
$ mysqld_safe --skip-grant-table # 螢幕出現： Starting demo from .....

# 3.新開起一個終端輸入
$ mysql -uroot mysql
  mysql> update user set password=password('新密碼') where user='root';
  mysql> flush privileges;
  mysql> quit;

3、服務上root能用，遠端不能登入

$ mysql -uroot -p
  mysql> use mysql;
  
  # 先檢視user表host欄位，有無萬用字元'%'，若有直接執行flush privileges;
  mysql> select host from user where user='root';  
  mysql> grant all privileges *.* to 'root'@'%' identified by 'root密碼';
  mysql> flush privileges;
  mysql> quit;

4、遠端連線提示caching_sha2_password錯誤

從mysql5.7版本之後，預設採用了caching_sha2_password驗證方式

mysql> use mysql;
mysql> alter user 'root'@'%' identified with mysql_native_password by 'root密碼';

5、mysql修改預設資料存放

$ mysqladmin -u root -p variables | grep datadir  # 檢視mysql資料庫存放目錄
$ service mysqld stop
$ mv /var/lib/mysql /路徑  # 行動資料庫檔案

$ vi /etc/my.cnf

# 修改datadir和socket兩個欄位，並新增以下：
[mysql] 
socket=/路徑/mysql.sock

$ service mysqld start