Mysql介紹和實踐總結

本文首先介紹mysql的安裝和基本使用、進階操作、講解mysql的導入導出和自動備份，然後介紹安全模式修改密碼和mysql的全文本搜索功能，最後記錄了個人使用mysql中遇到的問題集。

開始安裝：

sudo apt-get install mysql-common mysql-server

簡單使用

建庫

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

建表

create table MyClass(id int(4) not null primary key auto_increment,name char(20) not null,sex int(4) not null default '0',degree double(16,2));

增

insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

刪

delete from MyClass where id=1;

改

update MyClass set name='Mary' where id=1;

查

select * from MyClass;

顯示所有的view

select * from information_schema.TABLES where table_type='view' AND table_schema = '資料庫名';

進一步操作

創建用戶：

create user xxx identified by ‘password’;

重命名：

rename user aaa to bbb；

刪除用戶：

drop user aaa;

顯示許可權：

show grants for aaa(用戶);

授予許可權：

grant select on xxx(資料庫).* to aaa(用戶);

授予某個資料庫的全部許可權：

grant all on  xxx(資料庫).* to aaa(用戶);
grant all on  xxx(資料庫).* to aaa(用戶)@localhost;

取消授權：

revoke all on *.* from aaa(用戶)@localhost;

修改許可權

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%’   WITH GRANT OPTION;

以上操作完成之後記得刷新許可權：

flush privileges;

導入導出

導出數據和表結構：

mysqldump -uroot -p abc(資料庫名) > abc.sql
敲回車後輸入密碼

只導出表結構

mysqldump -uroot -p -d abc > abc.sql

導入資料庫
1、首先建空資料庫

mysql> create database abc;

2、導入資料庫

mysql -u root -p abc(資料庫名) < abc.sql

資料庫自動備份

新建備份腳本xxx.sh,輸入以下內容

#!/bin/bash

# 要備份的資料庫名，多個資料庫用空格分開
databases=("db1", "db2") 

# 備份文件要保存的目錄，註意當前用戶必須用戶保存目錄的讀寫許可權
basepath='/root/backup/mysql/'

if [ ! -d "$basepath" ]; then
  mkdir -p "$basepath"
fi

# 迴圈databases數組
for db in ${databases[*]}
  do
    # 備份資料庫生成SQL文件
    nice -n 19 /usr/bin/mysqldump -uroot -pcd32d5e86e --database $db > $basepath$db-$(date +%Y%m%d).sql
    
    # 將生成的SQL文件壓縮
    nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz -C $basepath $db-$(date +%Y%m%d).sql
    
    # 刪除7天之前的備份數據
    find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
  done

  # 刪除生成的SQL文件
  rm -rf $basepath/*.sql

使用crontab設置定時任務，在終端輸入crontab -e，加入以下內容，此任務為每天3點自動執行。

0 3 * * * bash xxx.sh(此處填寫腳本絕對地址)

開啟日誌記錄

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
binlog-format = row

安全模式操作

進入安全模式修改密碼

mysqld_safe --skip-grant-tables &

select user,host,password from user where user="root"

不同版本的mysql修改用戶密碼方式不一樣,需要查看mysql->user中的密碼欄位，如果不是password的話就是authentication_string。

authentication_string的修改方式不太一樣：

use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

如果不是authentication_string，則可用以下方法。

update user set password=PASSWORD("your_password") where user="root" and host=“localhost"

新操作

Mysql全文本搜索

Mysql5.6之後支持InnoDB，中文的全文本搜索，內置使用n-gram為分詞處理器，還支持中文～。

創建索引

create fulltext index ngram_idx on tag(Title) with parser ngram;
或
alter table tag add fulltext index ngram_idx(Title) with parser ngram;

獲取支持的最小分詞長度

SHOW VARIABLES LIKE 'ft_min_word_len';
//unix系統可在/etc/my.cnf中修改
[mysqld]
ft_min_word_len = 1

開始使用

select Title,match(Title) against('清水') from tag ;

可能出現的問題集：

• 描述

2017-05-04T01:21:32.004560Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-05-04T01:21:32.023009Z mysqld_safe A mysqld process already exists

解決方法：

$ sudo killall mysqld

• 描述

2017-05-04T01:22:26.486677Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-05-04T01:22:26.488204Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

解決方法：

sudo mkdir -p /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld

• 描述

$ sudo /etc/init.d/mysql start
ies: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[....] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

解決方法：

當前文件夾不是實際目錄導致
cd到一個實際目錄位置即可

• 描述

sudo /etc/init.d/mysql start
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[....] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

按照提示:See "systemctl status mysql.service" and "journalctl -xe" for details.
但是並麽有什麼卵用，直接看mysql的log：/var/log/mysql/error.log
2017-05-04T01:37:56.583745Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

解決方法：

殺掉所有mysqld進程：killall mysqld 
再次sudo /etc/init.d/mysql start 成功

• 描述

dpkg被鎖定

解決方法

sudo rm /var/cache/apt/archives/lock
sudo rm /var/lib/dpkg/lock