MySQL测试

MySQL 数据库系统

MySQL 特点：
多进程
多用户
高性能
高可靠
易用性

MySQL AB --> SUN --> Oracle

MySQL --> MariaDB

关系型数据库 RDBMS：
商业：Oracle，Sybase，Infomix，SQL Server，DB2
开源：MySQL，PostgreSQL，pgsql，EnterpriseDB

非关系型数据库 NoSQL：
MongoDB，Redis，HBase，memcached

MySQL:
Community Edtion 社区版
Enterprise Edtion 企业版

软件包格式：
.rpm
.exe
.bin（二进制）
源程序

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

MySQL源码安装+LVM 逻辑卷

创建逻辑卷 /dev/mysqlvg/mysqllv

创建一个分区，将类型改为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数据库
[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 //默认字符集为 utf8
-DDEFAULT_COLLATION=utf8_general_ci //默认的字符集校对规则
-DWITH_EXTRA_CHARSETS=all //安装所有字符集
-DSYSCONFDIR=/etc //配置文件存放目录

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

2）优化调整
[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:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭

[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）初始化数据库
[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/ //指定安装目录（产品目录）
--datadir=/usr/local/mysql/data //指定数据目录
--user=mysql //指定用户身份

4）启动mysql服务
[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' //设置成自己的密码
[root@RHEL6-Server1 mysql-5.7.2-m12]# mysql -u root -p123456

my.cnf 配置文件相关介绍，在mysql解压路径support-files/下提供的模版

my-huge.cnf 巨大数据量
my-innodb-heavy-4G.cnf innodb引擎
my-large.cnf 大数据量
my-medium.cnf 测试使用
my-small.cnf 小数据量

MySQL 数据库存储引擎：
myisam
innodb

多个配置文件时的应用顺序，后一个覆盖前一个配置文件
/etc/my.cnf --> /etc/mysql/my.cnf --> $BASEDIR/my.cnf --> ~/.my.cnf

netstat -anpt |grep mysqld
tcp 3306 端口

MySQL 是C/S结构，连接时需要客户端工具，Linux下mysql（如果是通过rpm格式安装软件需要安装mysql 软件包）

连接并登录到MySQL操作环境
mysql -u 指定用户名
-p 指定密码
-h 指定主机
-P 指定端口

[root@RHEL6-Server1 ~]# mysql -u root 默认root为Mysql用户，并为空密码

设置数据库用户的密码
mysqladmin -u root password '123456'

查看有那些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | 保证兼容性，mysql运行当中位于内存中的信息，关机后是空的
| mysql | 最主要的
| test | 用于测试

查看数据库中的数据表信息
mysql> USE mysql; //切换数据库
mysql> show tables; //查看有哪些表

显示数据表的结构（字段）
DESCRIBE [数据库名.]表名
mysql> DESCRIBE user;
mysql> DESCRIBE mysql.user;

创建新的数据库
CREATE DATABASE 数据库名
mysql> CREATE DATABASE auth;

创建新的数据表
CREATE TABLE 表名 (字段定义……)
mysql> USE auth;
mysql> CREATE TABLE users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));

删除指定的数据表
DROP TABLE [数据库名.]表名
mysql> DROP TABLE auth.users;

删除指定的数据库
DROP DATABASE 数据库名
mysql> DROP DATABASE auth;

向数据表中插入新的数据记录
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;

从数据表中查找符合条件的数据记录
SELECT 字段名1,字段名2 …… FROM 表名 WHERE 条件表达式
mysql> SELECT user_name,user_passwd FROM auth.users where user_name= 'zhangsan';

修改、更新数据表中的数据记录
UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE 条件表达式
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; 刷新授权表，让mysql进程重读授权表

在数据表中删除指定的数据记录
DELETE FROM 表名 WHERE 条件表达式
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;

设置用户权限（用户不存在时，则新建用户）
GRANT 权限列表 ON 数据库名.表名 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;

查看用户的权限
SHOW GRANTS FOR 用户名@域名或IP
mysql> SHOW GRANTS FOR 'xiaoqi'@'RHEL6-Server1';
mysql> SHOW GRANTS FOR 'dbuser'@'192.168.4.19';

撤销用户的权限
REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@域名或IP
mysql> REVOKE all ON auth.* FROM 'xiaoqi'@'localhost';

数据库的备份与恢复
方法1：可直接备份目录 /var/local/mysql/var
方法2：使用专用备份工具 mysqldump

备份操作
mysqldump -u 用户名 -p [密码] [选项] [数据库名] [表名] > /备份路径/备份文件名
常见选项：
--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:

恢复操作
mysql -u root -p [数据库名] < /备份路径/备份文件名
[root@www ~]# mysql -u root -p test < mysql-user.sql
Enter password:

MySQL通配符：
_:任意单个字符
%：任意长度的任意字符

用于显示广泛的服务器状态信息
show status;

显示创建特定数据库或表
help create database；
help create tables；

显示授权用户的安全权限
show grants；

显示服务器错误或警告信息
show errors；
show warnings；

显示当前连接用户
mysql> select user();

显示当前时间
mysql> select now();

显示当前用户及时间
mysql> select CURRENT_USER(),CURRENT_TIMESTAMP;
mysql> select user(),now();

显示当前数据库
mysql> select database();

显示服务器状态
mysql> status;