MySQL測試

MySQL 數(shù)據(jù)庫系統(tǒng)

MySQL 特點：
多進(jìn)程
多用戶
高性能
高可靠
易用性

MySQL AB --> SUN --> Oracle

MySQL --> MariaDB

關(guān)系型數(shù)據(jù)庫 RDBMS：
商業(yè)：Oracle，Sybase，Infomix，SQL Server，DB2
開源：MySQL，PostgreSQL，pgsql，EnterpriseDB

非關(guān)系型數(shù)據(jù)庫 NoSQL：
MongoDB，Redis，HBase，memcached

MySQL:
Community Edtion 社區(qū)版
Enterprise Edtion 企業(yè)版

軟件包格式：
.rpm
.exe
.bin（二進(jìn)制）
源程序

RPM 包安裝方法：
yum -y install mysql-server mysql
service mysqld start
netstat -ntpl |grep :3306

MySQL源碼安裝+LVM 邏輯卷

創(chuàng)建邏輯卷 /dev/mysqlvg/mysqllv

創(chuàng)建一個分區(qū)，將類型改為8e并保存退出

[root@RHEL6-Server1 ~]# fdisk -l /dev/sdb
Device Boot Start End Blocks Id System
/dev/sdb1 1 10443 83883366 8e Linux LVM

[root@RHEL6-Server1 ~]# partprobe /dev/sdb
[root@RHEL6-Server1 ~]# pvcreate /dev/sdb1
[root@RHEL6-Server1 ~]# vgcreate mysqlvg /dev/sdb1
[root@RHEL6-Server1 ~]# lvcreate -L 60G -n mysqllv mysqlvg
[root@RHEL6-Server1 ~]# mkfs.ext4 /dev/mysqlvg/mysqllv
[root@RHEL6-Server1 ~]# mkdir /usr/local/mysql/data -p
[root@RHEL6-Server1 ~]# mount /dev/mysqlvg/mysqllv /usr/local/mysql/data

配置自動掛載
[root@RHEL6-Server1 ~]# vim /etc/fstab
/dev/mysqlvg/mysqllv /usr/local/mysql/data ext4 defaults 0 0
[root@RHEL6-Server1 ~]# mount -a

===============================================================================================================

1）安裝Mysql數(shù)據(jù)庫
[root@RHEL6-Server1 ~]# rpm -e mysql-server mysql
[root@RHEL6-Server1 ~]# yum -y install ncurses-devel

安裝配置工具cmake
[root@RHEL6-Server1 ~]# tar xf cmake-2.8.12.tar.gz -C /usr/src/
[root@RHEL6-Server1 ~]# cd /usr/src/cmake-2.8.12/
[root@RHEL6-Server1 cmake-2.8.12]# ./configure && gmake && gmake install

[root@RHEL6-Server1 ~]# groupadd mysql
[root@RHEL6-Server1 ~]# useradd -M -s /sbin/nologin -g mysql mysql
[root@RHEL6-Server1 ~]# tar xf mysql-5.7.2-m12.tar.gz -C /usr/src/
[root@RHEL6-Server1 ~]# cd /usr/src/mysql-5.7.2-m12/
[root@RHEL6-Server1 mysql-5.7.2-m12]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc && make && make install

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql //主程序安裝目錄
-DDEFAULT_CHARSET=utf8 //默認(rèn)字符集為 utf8
-DDEFAULT_COLLATION=utf8_general_ci //默認(rèn)的字符集校對規(guī)則
-DWITH_EXTRA_CHARSETS=all //安裝所有字符集
-DSYSCONFDIR=/etc //配置文件存放目錄

[root@RHEL6-Server1 mysql-5.7.2-m12]# make && make install

2）優(yōu)化調(diào)整
[root@RHEL6-Server1 mysql-5.7.2-m12]# cp support-files/my-default.cnf /etc/my.cnf
[root@RHEL6-Server1 mysql-5.7.2-m12]# cp support-files/mysql.server /etc/init.d/mysqld
[root@RHEL6-Server1 mysql-5.7.2-m12]# chmod +x /etc/init.d/mysqld
[root@RHEL6-Server1 mysql-5.7.2-m12]# chkconfig --add mysqld
[root@RHEL6-Server1 mysql-5.7.2-m12]# chkconfig --list mysqld
mysqld 0:關(guān)閉 1:關(guān)閉 2:啟用 3:啟用 4:啟用 5:啟用 6:關(guān)閉

[root@RHEL6-Server1 mysql-5.7.2-m12]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@RHEL6-Server1 mysql-5.7.2-m12]# . /etc/profile = source /etc/profile
3）初始化數(shù)據(jù)庫
[root@RHEL6-Server1 mysql-5.7.2-m12]# chown -R mysql:mysql /usr/local/mysql/
[root@RHEL6-Server1 mysql-5.7.2-m12]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql

--basedir=/usr/local/mysql/ //指定安裝目錄（產(chǎn)品目錄）
--datadir=/usr/local/mysql/data //指定數(shù)據(jù)目錄
--user=mysql //指定用戶身份

4）啟動mysql服務(wù)
[root@RHEL6-Server1 mysql-5.7.2-m12]# service mysqld start
Starting MySQL.............. [確定]
[root@RHEL6-Server1 mysql-5.7.2-m12]# mysqladmin -u root password '123456' //設(shè)置成自己的密碼
[root@RHEL6-Server1 mysql-5.7.2-m12]# mysql -u root -p123456

my.cnf 配置文件相關(guān)介紹，在mysql解壓路徑support-files/下提供的模版

my-huge.cnf 巨大數(shù)據(jù)量
my-innodb-heavy-4G.cnf innodb引擎
my-large.cnf 大數(shù)據(jù)量
my-medium.cnf 測試使用
my-small.cnf 小數(shù)據(jù)量

MySQL 數(shù)據(jù)庫存儲引擎：
myisam
innodb

多個配置文件時的應(yīng)用順序，后一個覆蓋前一個配置文件
/etc/my.cnf --> /etc/mysql/my.cnf --> $BASEDIR/my.cnf --> ~/.my.cnf

netstat -anpt |grep mysqld
tcp 3306 端口

MySQL 是C/S結(jié)構(gòu)，連接時需要客戶端工具，Linux下mysql（如果是通過rpm格式安裝軟件需要安裝mysql 軟件包）

連接并登錄到MySQL操作環(huán)境
mysql -u 指定用戶名
-p 指定密碼
-h 指定主機
-P 指定端口

[root@RHEL6-Server1 ~]# mysql -u root 默認(rèn)root為Mysql用戶，并為空密碼

設(shè)置數(shù)據(jù)庫用戶的密碼
mysqladmin -u root password '123456'

查看有那些數(shù)據(jù)庫
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | 保證兼容性，mysql運行當(dāng)中位于內(nèi)存中的信息，關(guān)機后是空的
| mysql | 最主要的
| test | 用于測試

查看數(shù)據(jù)庫中的數(shù)據(jù)表信息
mysql> USE mysql; //切換數(shù)據(jù)庫
mysql> show tables; //查看有哪些表

顯示數(shù)據(jù)表的結(jié)構(gòu)（字段）
DESCRIBE [數(shù)據(jù)庫名.]表名
mysql> DESCRIBE user;
mysql> DESCRIBE mysql.user;

創(chuàng)建新的數(shù)據(jù)庫
CREATE DATABASE 數(shù)據(jù)庫名
mysql> CREATE DATABASE auth;

創(chuàng)建新的數(shù)據(jù)表
CREATE TABLE 表名 (字段定義……)
mysql> USE auth;
mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

刪除指定的數(shù)據(jù)表
DROP TABLE [數(shù)據(jù)庫名.]表名
mysql> DROP TABLE auth.users;

刪除指定的數(shù)據(jù)庫
DROP DATABASE 數(shù)據(jù)庫名
mysql> DROP DATABASE auth;

向數(shù)據(jù)表中插入新的數(shù)據(jù)記錄
INSERT INTO 表名(字段1, 字段2, ……) VALUES(字段1的值, 字段2的值, ……)
mysql> use auth;
mysql> INSERT INTO users(user_name,user_passwd) VALUES('zhangsan', PASSWORD('123456'));
mysql> INSERT INTO users VALUES('lisi', PASSWORD('654321'));
mysql> select * from auth.users;

從數(shù)據(jù)表中查找符合條件的數(shù)據(jù)記錄
SELECT 字段名1,字段名2 …… FROM 表名 WHERE 條件表達(dá)式
mysql> SELECT user_name,user_passwd FROM auth.users where user_name= 'zhangsan';

修改、更新數(shù)據(jù)表中的數(shù)據(jù)記錄
UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE 條件表達(dá)式
mysql> UPDATE auth.users SET user_passwd=PASSWORD('') WHERE user_name='lisi';
mysql> select * from auth.users;

mysql> UPDATE mysql.user SET password=PASSWORD('123456') WHERE user='root';
mysql> FLUSH PRIVILEGES; 刷新授權(quán)表，讓mysql進(jìn)程重讀授權(quán)表

在數(shù)據(jù)表中刪除指定的數(shù)據(jù)記錄
DELETE FROM 表名 WHERE 條件表達(dá)式
mysql> SELECT user,host,password FROM mysql.user WHERE user='';
mysql> DELETE FROM mysql.user WHERE user='';
mysql> DELETE FROM auth.users WHERE user_name='lisi';
mysql> SELECT * FROM auth.users;

設(shè)置用戶權(quán)限（用戶不存在時，則新建用戶）
GRANT 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 TO 用戶名@來源地址 [ IDENTIFIED BY ‘密碼’ ]
mysql> grant all on . to 'root'@'192.168.6.131' IDENTIFIED BY '123456';
mysql> GRANT select ON auth. TO 'xiaoqi'@'localhost' IDENTIFIED BY '123456';
[root@www ~]# mysql -u xiaoqi -p
……
mysql> SELECT FROM auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM mysql.user;

查看用戶的權(quán)限
SHOW GRANTS FOR 用戶名@域名或IP
mysql> SHOW GRANTS FOR 'xiaoqi'@'RHEL6-Server1';
mysql> SHOW GRANTS FOR 'dbuser'@'192.168.4.19';

撤銷用戶的權(quán)限
REVOKE 權(quán)限列表 ON 數(shù)據(jù)庫名.表名 FROM 用戶名@域名或IP
mysql> REVOKE all ON auth.* FROM 'xiaoqi'@'localhost';

數(shù)據(jù)庫的備份與恢復(fù)
方法1：可直接備份目錄 /var/local/mysql/var
方法2：使用專用備份工具 mysqldump

備份操作
mysqldump -u 用戶名 -p [密碼] [選項] [數(shù)據(jù)庫名] [表名] > /備份路徑/備份文件名
常見選項：
--all-databases
--opt

[root@www ~]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password:
[root@www ~]# mysqldump -u root -p --database auth > auth.sql
Enter password:
[root@www ~]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:

恢復(fù)操作
mysql -u root -p [數(shù)據(jù)庫名] < /備份路徑/備份文件名
[root@www ~]# mysql -u root -p test < mysql-user.sql
Enter password:

MySQL通配符：
_:任意單個字符
%：任意長度的任意字符

用于顯示廣泛的服務(wù)器狀態(tài)信息
show status;

顯示創(chuàng)建特定數(shù)據(jù)庫或表
help create database；
help create tables；

顯示授權(quán)用戶的安全權(quán)限
show grants；

顯示服務(wù)器錯誤或警告信息
show errors；
show warnings；

顯示當(dāng)前連接用戶
mysql> select user();

顯示當(dāng)前時間
mysql> select now();

顯示當(dāng)前用戶及時間
mysql> select CURRENT_USER(),CURRENT_TIMESTAMP;
mysql> select user(),now();

顯示當(dāng)前數(shù)據(jù)庫
mysql> select database();

顯示服務(wù)器狀態(tài)
mysql> status;