mysql 全集（上）

原创

chainaxxboy 2015-01-07 05:21:23 ©著作权

文章标签 mysql 全集 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者chainaxxboy的原创作品，谢绝转载，否则将追究法律责任

1、关系数据库、非关系数据库概念，及常用产品；

   a、关系型数据库归集为简单的二元关系（即二维表格形式），通过结构化查询语言SQL语句来对数据进行存取，有效的保持了数据的一致性；

    产品范例：MySQL、oracle

   b、非关系形数据库NOsql是作为关系型数据库的补充；为的是高性能、高并发、易扩展、易部署。

    范例: 典型产品：                   memchached，redis（多数据类型支持），monagdb，
          非关系新数据库的种类        Key-Value 典型产品memcached、redis 新浪
          列存储：                     Cassandre \HBase
          面向文档数据库：            MongoDB、CouchDB
          图形数据库：               Neo4J 、InfoGird

参考资料：SQL介绍 http://baike.baidu.com/view/595350.htm?fromId=34

SQL分类 http://baike.baidu.com/view/595350.htm?fromId=34

2、MYSQL安装方式：
   a、Yum安装 yum install MySQL-server（适合要求不高、公司内部）
   b、门户网站根据企业的需求把源码包制作成rpm，搭建yum仓库， yum install xxx -y
   c、编译安装MySQL：
    （MySQL5.0xx-5.1xx产品系列最常规的编译方式，linux运维常规选择）
     （MySQL5.5采用cmake或gmake方式编译；（linux运维选择）
   d、二进制免编译安装；
     （适合各类MySQL产品系列，直接解压下载的安装包，初始化即可完成MySQL的安装启动，专业dba的方式）
   注:建议首选5.5以上的版本，安装数量少推荐cmake，数量多直接二进制免编译安装。

安装见附件

3、mysql 单实例：

a、MySQL多实例：一台机器上开启不同的端口，运行多个MySQL进程来提供服务；

b、MySQL多实例关系：公用一套MySQL安装程序，使用相同（也可以不同）的my.cnf配置文件、启动程序、数据文件、在提供服务时，多实例MySQL在逻辑上看来是独自独立的，多个实例的自身是根据配置文件对应的设定值，来取得服务器的相关硬件资源多少。注：nginx、apache、redis、memcache都可以实现;
c、优势：节约资源，提供更多的服务；
劣势：当某个服务实例并发很高或者查询慢时，整个实例会消耗整个主机更多的内存、cpu、磁盘io资源，导致服务器上其它的实例提供的服务质量下降；
d、适应范围：并发访问不大的业务；

安装见附件

4、 MySQL优雅启动、关闭；

a、启动命令: /etc/init.d/mysqld start

    （来源于 /tools/mysql-5.5.32-linux2.6-x86_64/support-files/mysql.server ）
     查看端口：ss -lnt | grep 3306
b、启动原理：
     /etc/init.d/mysqld 调用mysqld_safe脚本，最后调用mysqld服务启动mysql服务启动mysql

查看A：/etc/init.d/mysqld
283 $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null 2>&1 &

查看B：ps -ef | grep mysql | grep -v grep
root 1624 1 0 14:01 tty1 00:00:00 /bin/sh /application/mysql

/bin/mysqld_safe --datadir=/application/mysql/data/ --pid- file=/application/mysql/data//A.pid
mysql 1862 1624 0 14:01 tty1 00:00:02 /application/mysql/bin/mysqld --basedir=/application/mysql --datadir=/application/mysql/data/ --plugin-dir=/application/mysql/lib/plugin --user=mysql --log-error=/application/mysql/data//A.err --pid-file=/application/mysql/data//A.pid --socket=/tmp/mysql.sock --port=3306

c、关闭数据库 ： mysqladmin -uroot -p123456 shutdown

查看：/etc/init.d/mysqld

if [ ! -e "$mysql_sock" ];then

printf "MySQL isstopped...\n"

exit

else

printf "StopingMySQL...\n"

${CmdPath}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

/etc/init.d/mysqld stop （ kill -USR `cat path/pid`）

    d、多实例启动和关闭：
         /data/3306/mysql start
         /data/3307/mysql stop

5、mysql登录、修改登陆提示符、及解决明文密码泄漏：
a、：mysql 适用刚装完系统无密码情况的登录方式，不要密码
b、：mysql -uroot                       刚装完系统无密码情况的登录方式，不要密码
c、：mysql -uroot -p                    标准的dba命令行登录命令
d、：mysql -uroot -p'123456'            非脚本一般不这样用，密码明文会泄漏密码

e、：mysql -uroot -p -S /data/3306/mysql.sock

#(多实例通过mysql的-S命令指定不同的sock文件登录不同的服务中)

f、：远程多实例的登录无需指定sock路径： mysql -uroot -p -h 127.0.0.1 -P3307

g、修改mysql登录提示符： prompt \u@oldboy \r:\m:\s-> #u系统用户 r主机名临时的；
永久生效：在my.cnf配置文件中【mysql】模块下添加：prompt=\\u@oldbo\\r:\\m:\\s->

附：history功能解决明文密码泄漏
history 参数： -c 清空历史记录

-d 21 清楚某条历史记录

echo "HISTCONTROL=ignorespace" >>vi /etc/profile

source /etc/profile

# cat /root/.mysql_historyt 注意:加空格登陆

6、mysql 登录后操作，及密码设置+密码丢失：

a、帮助：

mysql 中的帮助命令和linux命令的man、help（help linux 内置命令帮助）是类似的；
使用：mysql> help use 即可；

b、系统命令使用：

mysql> system ls /tmp （系统中使用）
show databases like 'my%'; 查看以my开头的数据 %所有

   c、删除所有mysql中的用户，包括root超级用户；
      mysql> delete from mysql.user;

   d、增加system并提升为超级管理用，及和root等价的用户，只是名字不同；
      mysql> grant all privileges on *.* to system@'localhost' identified by'oldboy123' with grant option;

   e、为mysql设置密码（密码为空时）：
      mysqladmin -u root passwd 123456              #passwd后无需添加单引号或双引号；
      多实例：
      mysqladmin -u root password oldboy -S /data/3307/mysql.sock

      单实例命令行新密码替换旧密码：
    mysqladmin -uroot -p123456 password 654321     #-p后不要有空格和标点符号；
      多实例命令行新密码替换旧密码：
    mysqladmin -u root -poldboy password 123456 -S /data/3307/mysql.sock

   f、sql语句密码修改：
      update mysql.user set password='654321' where user='root' and host='localhost';
      #明文密码：不可以直接登录，flush privileges;权限刷新:
    查看密码：
      select user,host,password from mysql.user;

指定函数加密密码：
update mysql.user set password=password(123456) where user='root' and host='localhost';

#注意where 必须指定条件，不然就全改了；password （）函数更改密码；flush privileges;

   g、找回丢失的密码（思想就是跳过授权表）：
单实例：停止数据库；最好是优雅关闭数据库：
                mysqladmin -uroot -p shutdown
          /etc/init.d/mysqld stop
                检查：ps -ef | grep mysql；
                /application/mysql/bin/mysqld_safe --skip-grant-tables & #跳过授权表

                更新密码：
                update mysql.user set password=password(123456) where user='root' and host='localhost';
              flush privileges;

#在万不得已的情况下使用pkill 、killall、kill -9 ，最好别用。强行终止数据库服务器时，会让数据库产生更多的文件碎片，当碎片达到一定程度时，数据库会崩溃。暴力不好！
              #重新启动数据库，重新登录。

       多实例 ：
       /bin/sh /application/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf --skip-grant-tables &
mysql -S /data/3307/mysql.sock
        .....

7、mysql 用户创建及授权：

a、 help查看grant命令帮助:
CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

     GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
     GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
     GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

#USAGE 表示用户只可以登录，没有其它权限，操作的时候显示Access denied；

help查看create命令帮助：

CREATE USER 'jeffrey'@'localhost' IDENTIFIED WITH my_auth_plugin;

     范例：
     创建用户：create user username@localhost identified by "password";
     查看用户权限：show grants for username@localhost;
     在授权：grant all on dbname.* to username@localhost;
     查看权限：show grants for username@localhost;

b、查看系统中所有的用户：
      select host,user from mysql.user;
      删除系统的多余帐号语法drop user"user"@"主机域" 注意引号以是单引号或双引号；
      范例： drop user ''@'moban2'    #如果为空直接为空即可；
      #如果drop删除不了（一般是特殊字符或大写），可以用下面的方式删除：
      范例： delete from mysql.user where user='root' and host='127.0.0.0.1';

c、确定mysql 可以授权的权限：
     ⑴帮助查看：help revoke （权限收回）
       REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
    ⑵权限查看：show grants for username@localhost;
    ⑶收回插入权限：revoke insert on *.* from 'user_name'@'localhost'; #注意此处指定数据库
    ⑷登录数据库后权限查看：show grants for username@localhost;
    ⑸退出数据库后：
mysql -uroot -p123456 -e "show grants for "username"@"localhost";" | grep -i grant | tail -1|tr ',' '\n' >all.privileges
      以下为数据库中的权限：

SELECT 查询\INSERT 插入 \UPDATE 更新\DELETE 删除 \CREATE 创建库和表

DROP 删除库和表\INDEX 索引\ALTER 修改 \CREATE TEMPORARY TABLES 创建临时表

LOCK TABLES 锁表\ EXECUTE 执行\ CREATE VIEW 创建视图SHOW VIEW 显示视图

CREATE ROUTINE 创建存储过程\ALTER ROUTINE 修改存储过程 EVENT 事件\ TRIGGER 触发器
或者：select * from mysql.user\G;

   d、常用的创建方法，使用grant命令创建用户的时，进行权限授权：
      范例：grant all privileges on db1.* to user_name@localhost identified by "password";
             授权命令对应权限（all所有权限）   目标：库和表用户名和客户端主机                         用户密码
             flush privileges;
             select host,user from mysql.user;        #查看创建的用户：

             show grants for user_name@localhost;     #查看创建用户的权限；

   e、授权局域网内主机远程连接数据库,常见的使用%匹配方法
      范例： grant all on   *.* to username_2@'10.10.36.%' identified by "123456";
              flush privileges;
              mysql -u username_2 -p -h 10.10.36.170 #登录使用-h指定主机，-P指定端口

f、针对博客、cms 等产品安装期间要采用最小化原则：
范例：grant select,insert,update,delete,create,drop on blog.* to blog@'10.10.36.%' identified by "password";

#除了select,insert,update,delete4个权限外，还需要create,drop等危险权

生产数据库后收回权限（最好评估）

范例：revoke create,drop on blog.* from blog@'10.10.36.%';

8、常用查询（简）：

1、使用帮助：范例：help drop database #这里可以没有；号
2、查看数据库当前的用户： select user(); #（）表示系统的函数；

   3、查看系统中所有的用户：         select host,user from mysql.user;
   4、查看数据库的版本：             select version();
   5、显示数据库字符集：             SHOW CHARACTER SET;
   6、设定字符集：                   set names gbk;
   7、查看当前的时间：               select now();
   8、显示所有数据库：               show databases;
   9、显示当前数据库：               select database();

      #显示某一个可以使用like或where：
       范例:show databases where "name";
            show databases like "name";
            show databases like 'data%';            %所有
10、创建数据库：                  create database name;
11、查看建库语句:                 show create database name;

                                    show create database name\G;
12、删除数据库：                  drop database name;
13、连接切换数据库：              use name;
14、查看当前数据库的表：          show tables;
15、查看指定库中的表:             show tables from name;
#显示字符集，默认是latin1，支持中文没有问题。网页、程序的字符集要和数据库的字符集相匹配，包括客户端、服务端、java程序；
16、查看建结构：                  desc student;

                                    show columns from student;
17、查看以建表语句：         show create table student\G;#发送命令给mysqlserver垂直显示；
18、删除表：                      drop tables student;
                                    delete from test where id=2;    #指定条件删除；

19、truncate清空表中所有内容：    truncate table test;
#truncate相当于直接清空mysql 物理文件， delete 逻辑删除，按行删；

20、-e 命令行与mysql交互          mysql -uroot -p123456 -e "show databases;"
21、查看数据库连接概况；          show processlist;
#注意观察时间比较长的语句可能为慢查询，需要优化；
                                    show full processlist;         #完整的显示；
22、查进程：                      show variables;
                                                范例：mysql -uroot -p123456 -e "show variables;" | grep logbin
23、查状态：                      show status
24、查全局状态：                  show global status
                                                范例： mysql -uroot -p123456 -e "show global status;"   | grep select
                                               show global status like "%insert%";

9、数据库字符集设置与更改;

http://hiboyandhigirl.blog.51cto.com/9150052/1577235 #附件

10、mysql 客户端工具使用excel手册见附件（命令详解） #附件

MYSQL客户端工具	作用
mysqlaccess	检查访问权限的客户端
mysqladmin	管理mysql服务器的客户端
mysqlbinlog	处理二进制日志文件的实用工具
mysqlcheck	表维护和维修程序
mysqldump	数据库备份程序
mysqlhotcopy	数据库备份程序
mysqlimport	数据导入程序
mysqlshow	显示数据库、表和列信息
myisamog	显示MyISAM日志文件内容
perror	解释错误代码
replace	字符串替换实用工具
mysqlzap	杀死符合某一模式的进程