Mysql基礎系列（一）

一、簡介

MySQL是最流行的開放源碼SQL資料庫管理系統，它是由MySQL AB公司開發、釋出并支援的。有以下特點：

MySQL是一種資料庫管理系統。
MySQL是一種關聯資料庫管理系統。
MySQL軟體是一種開放源碼軟體。
MySQL資料庫伺服器具有快速、可靠和易于使用的特點。
MySQL伺服器工作在用戶端/伺服器模式下，或嵌入式系統中。
有大量可用的共享MySQL軟體。

MySQL表最大能達到多少？

InnoDB存儲引擎将InnoDB表儲存在一個表空間内，該表空間可由數個檔案建立。這樣，表的大小就能超過單獨檔案的最大容量。表空間可包括原始磁盤分區，進而使得很大的表成為可能。表空間的最大容量為64TB。

二、安裝MySQL

下載下傳MySQL位址：http://dev.mysql.com/downloads/mysql/

CentOS 安裝：

yum install mysql-server

Ubuntu 安裝：

1. sudo apt-get install mysql-server
2. sudo apt-get isntall mysql-client
3. sudo apt-get install libmysqlclient-dev

# 檢測是否安裝成功（是否為LISTEN狀态）
sudo netstat -tap | grep mysql

編譯安裝MySQL-5.5.32：

# 安裝依賴包
yum install ncurses-devel gcc gcc-c++ -y

# 建立目錄
mkdir -p /home/oldsuo/tools

# 安裝cmake軟體，gmake編譯安裝
cd /home/oldsuo/tools/
tar xf cmake-2.8.8.tar.gz
cd cmake-2.8.8
./configure
#CMake has bootstrapped.  Now run gmake.
gmake
gmake install
cd ../


# 開始安裝mysql
# 建立使用者群組
groupadd mysql
useradd mysql -s /sbin/nologin -M -g mysql

# 解壓編譯MySQL
tar zxf mysql-5.5.32.tar.gz 
cd mysql-5.5.32
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 \
-DMYSQL_DATADIR=/application/mysql-5.5.32/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0

#-- Build files have been written to: /home/oldsuo/tools/mysql-5.5.32
提示: 編譯時可配置的選項很多，具體可參考結尾附錄或官方文檔：

make
#[100%] Built target my_safe_process
make install
ln -s /application/mysql-5.5.32/ /application/mysql
如果上述操作未出現錯誤，則MySQL5.5.32軟體cmake方式的安裝就算成功了。


#拷貝配置檔案
cp mysql-5.5.32/support-files/my-small.cnf /etc/my.cnf

#添加變量，并使之生效
echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile
source /etc/profile
echo $PATH

#授權使用者及/tmp/臨時檔案目錄
chown -R mysql.mysql /application/mysql/data/
chmod -R 1777 /tmp/

#初始化資料庫
cd /application/mysql/scripts/
./mysql_install_db --basedir=/application/mysql/ --datadir=/application/mysql/data/ --user=mysql
cd ../

#啟動資料庫
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
/etc/init.d/mysqld  start

#檢查端口
netstat -lntup|grep 3306

編譯安裝完後一般安全操作：

1、删除不必要的使用者和庫：

#檢視使用者和主機列，從mysql.user裡檢視
select user,host from mysql.user;

#删除使用者名為空的庫，并檢查
delete from mysql.user where user='';
select user,host from mysql.user;

#删除主機名為localhost.localdomain的庫，并檢查
delete from mysql.user where host='localhost.localdomain';
select user,host from mysql.user;

#删除主機名為::1的庫，并檢查。::1庫的作用為IPV6
delete from mysql.user where host='::1';

#删除test庫
drop database test;

2、添加額外管理者：（可不删root使用者）

# 添加額外管理者，system作為管理者，oldsuo為密碼
mysql> delete from mysql.user;
Query OK, 2 rows affected (0.00 sec)
mysql> grant all privileges on *.* to system@'localhost' identified by 'oldsuo' with grant option;
Query OK, 0 rows affected (0.00 sec)
# 重新整理MySQL的系統權限相關表，使配置生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| system | localhost |
+--------+-----------+
row in set (0.00 sec)
mysql>

3、設定登入密碼并開機自啟：

#設定密碼，并登陸
/usr/local/mysql/bin/mysqladmin -u root password 'oldsuo'
mysql -usystem -p

#開機啟動mysqld，并檢查
chkconfig mysqld on
chkconfig --list mysqld

三、字元集

對于新手來說，字元集亂碼問題無疑是頭痛的問題，小編就帶你不在頭痛，從此幸福。

1、字元集簡介：

字元集，character set，就是一套表示字元的符号和這些的符号的底層編碼；而校驗規則，則是在字元集内用于比較字元的一套規則。簡單的說，字元集就是一套文字元号及其編碼、比較規則的集合，第一個計算機字元集ASC2，MySQL資料庫字元集包括字元集和校對規則兩個概念，字元集是定義資料庫裡面的内容字元串的存儲方式，而校對規則是定義比較字元串的方式。

建議：中英文環境選擇utf8

2、檢視設定字元集

# 檢視MySQL字元集設定情況
show variables like 'character_set%';
# 檢視庫的字元集
show create database db;
# 檢視表的字元集
show create table db_tb\G
# 查詢所有
show collation;

# 設定表的字元集
set tables utf8;

show create database nick_defailt\G            #檢視nick_defailt庫字元集
mysql -uroot -p -e "SHOW CHARACTER SET;"
show variables like 'character_set%';

mysql> show variables like 'character_set%';
+-----------------------------------------+------------------------------------------------------------+
| Variable_name            | Value                                |
+----------------------------------------+-------------------------------------------------------------+
| character_set_client      | utf8                                   |
| character_set_connection  | utf8                                   |
| character_set_database   | utf8                                   |
| character_set_filesystem   | binary                                 |
| character_set_results     | utf8                                   |
| character_set_server      | utf8                                   |
| character_set_system     | utf8                                   |
| character_sets_dir        | /usr/local/mysql/share/mysql/charsets/     |
+----------------------------------------+--------------------------------------------------------------+
rows in set (0.00 sec)

mysql> show create database nick_defailt \G            
*************************** 1. row ***************************
       Database: data
Create Database: CREATE DATABASE `data` /*!40100 DEFAULT CHARACTER SET utf8 */
row in set (0.00 sec)

3、MySQL資料亂碼及解決方法

1>    系統方面
cat /etc/sysconfig/i18n 
LANG="zh_CN.UTF-8"

2>    用戶端（程式），調整字元集為latin1。
mysql> set names latin1;        #臨時生效
Query OK, 0 rows affected (0.00 sec)
    #更改my.cnf用戶端子產品的參數，實作set name latin1 的效果，并且永久生效。
    [client]
default-character-set=latin1
#無需重新開機服務，登出就生效，相當于set name latin1。

3>    服務端，更改my.cnf參數
[mysqld]
default-character-set=latin1        #适合5.1及以前版本
character-set-server=latin1         #适合5.5

4>    庫、表、程式
#建表指定utf8字元集
mysql> create database nick_defailtsss DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

4、将utf8字元集修改成GBK字元集的實際過程

1>    導出表結構
#以utf8格式導出
mysqldump -uroot -p --default-character-set=utf8 -d nick_defailt>alltable.sql
--default-character-set=gbk        #表示已GBK字元集連接配接 –d 隻表示表結構

2>    編輯alltable.sql 将utf8改成gbk。

3>    確定資料庫不在更新，導出所有資料
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=utf8 nick_defailt>alldata.sql

4>    打開alldata.sql将set name utf8 修改成 set names gbk(或者修改系統的服務端和用戶端)

5>    建庫
create database oldsuo default charset gbk;

6>    建立表，執行alltable.sql
mysql -uroot -p oldsuo <alltable.sql

7>    導入資料
mysql -uroot -p oldsuo <alltable.sql

四、存儲引擎

MySQL最常用存儲引擎Myisam和Innodb。mysql 5.5.5以後預設存儲引擎為Innodb。

MySQL的每種引擎在MySQL裡是通過插件的方式使用的，MySQL可以支援多種存儲引擎。

建議：使用 Innodb引擎，因為支援復原，後續部落格會講。

1、引擎對應系統檔案

1)    MyISAM引擎系統庫表對應檔案
[root@mysql 3306]# ll /data/3306/data/mysql/
-rw-rw----. 1 mysql mysql  10630 10月 31 16:05 user.frm            #儲存表的定義
-rw-rw----. 1 mysql mysql   1140 10月 31 18:40 user.MYD            #資料檔案
-rw-rw----. 1 mysql mysql   2048 10月 31 18:40 user.MYI            #索引檔案

[root@mysql 3306]# file data/mysql/user.frm 
data/mysql/user.frm: MySQL table definition file Version 9
[root@mysql 3306]# file data/mysql/user.MYD 
data/mysql/user.MYD: DBase 3 data file (167514107 records)
[root@mysql 3306]# file data/mysql/user.MYI
data/mysql/user.MYI: MySQL MISAM compressed data file Version 1

2)    InnoDB引擎

[root@mysql 3306]# ll data/
-rw-rw----. 1 mysql mysql 134217728 10月 31 20:05 ibdata1

2、修改引擎

建立後引擎的修改
文法：    ALTER TABLE student ENGINE = INNODB;
         ALTER TABLE student ENGINE = MyISAM;

修改執行個體

mysql> use teacher;
Database changed
mysql> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                    |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.01 sec)

mysql> ALTER TABLE student ENGINE = MyISAM;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table student;          
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                    |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec)

3、建表指定引擎

mysql> create table mess (                  
    -> id int(4) not null,                 
    -> name char(20) not null,
    -> age tinyint(2) NOT NULL default '0',
    -> dept varchar(16) default NULL
    -> ) ENGINE=MyISAM CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

五、基本語句指令

運作相關：

1、    單執行個體mysql啟動
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL                                             [确定]
#mysqld_safe –user=mysql &

2、    檢視MySQL端口
[root@localhost ~]# ss -lntup|grep 3306
tcp    LISTEN     0      50                     *:3306                  *:*      users:(("mysqld",19651,10))

3、    檢視MySQL程序
[root@localhost ~]# ps -ef|grep mysql|grep -v grep
root     19543     1  0 Oct10 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
mysql    19651 19543  0 Oct10 ?        00:05:04 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --log-error=/usr/local/mysql/data/localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306

4、    MySQL啟動原理
/etc/init.d/mysqld 是一個shell啟動腳本，啟動後最終會調用mysqld_safe腳本，最後調用mysqld服務啟動mysql。
        "$manager" \
        --mysqld-safe-compatible \
        --user="$user" \
        --pid-file="$pid_file" >/dev/null 2>&1 &

5、關閉資料庫
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL....                                    [确定]

6、    檢視mysql資料庫裡操作指令曆史
cat /root/.mysql_history

7、    強制linux不記錄敏感曆史指令
HISTCONTROL=ignorespace

8、    mysql設定密碼
/usr/local/mysql/bin/mysqladmin -u root password 'admin'

9、    mysql修改密碼，與多執行個體指定sock修改密碼
mysqladmin -uroot -passwd password 'admin    '
mysqladmin -uroot -passwd password 'admin' -S /data/3306/mysql.sock

#登陸mysql資料庫
mysql -uroot –p

#檢視有哪些庫
show databases;
#删除test庫
drop database test;

#使用test庫
use test;
#檢視有哪些表
show tables;
#檢視problem表的所有内容
select * from problem;


#檢視目前版本
select version();
#檢視目前使用者
select user();
#檢視使用者和主機列，從mysql.user裡檢視
select user,host from mysql.user;
#删除前為空，後為localhost的庫
drop user ""@localhost；

#重新整理權限
flush privileges;

#跳出資料庫執行指令
system ls;

六、破解mysql登入密碼

1> 普通方式
#> service mysqld stop
#>mysqld_safe --skip-grant-tables &
輸入 mysql -uroot -p 回車進入
>use mysql;
> update user set password=PASSWORD("newpass")where user="root";
更改密碼為 newpassord
> flush privileges; 更新權限
> quit 退出
service mysqld restart
mysql -uroot -p新密碼進入

2> 普通方式的簡寫
service mysqld stop
mysqld_safe --skip-grant-tables --user=mysql &
mysql
update mysql.user set password=PASSWORD("newpass")where user="root" and host='localhost';
flush privileges;
mysqladmin -uroot -pnewpass shutdown
/etc/init.d/mysqld start
mysql -uroot -pnewpass        #登陸

3>多執行個體方式
killall mysqld
mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &
mysql –u root –p –S /data/3306/mysql.sock        #指定sock登陸
update mysql.user set password=PASSWORD("newpass")where user="root";
flush privileges;
mysqladmin -uroot -pnewpass shutdown
/etc/init.d/mysqld start
mysql -uroot -pnewpass        #登陸

Mysql基礎系列（一）

一、簡介

二、安裝MySQL

編譯安裝完後一般安全操作：

三、字元集

1、字元集簡介：

2、檢視設定字元集

3、MySQL資料亂碼及解決方法

4、将utf8字元集修改成GBK字元集的實際過程

四、存儲引擎

1、引擎對應系統檔案

2、修改引擎

3、建表指定引擎

五、基本語句指令

六、破解mysql登入密碼

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述