mysql数据库详解（续一）

zhendeaini123

　　第三节 配置MYSQL数据库
　　
　　配置mysql数据库通常通过命令行选项、配置文件、和环境变量来进行，并且优先顺序也是命令行最高，环境变量优先级最低。
　　
　　1、配置文件定位
　　mysql的配置文件可以在以下四个位置：(按照查找顺序)
　　1、/etc/my.cnf
　　2、DATADIR/my.cnf               //DATADIR是存储数据库数据的目录。
　　3、通过 --default-extra-file=fielname设置指定的位置。
　　4、~/.my.cnf                        //用户的家目录
　　所以可以在用户的家目录下创建.my.cnf文件，并添加一下内容：
　　[client]
　　user=root
　　host=localhost
　　password=password
　　并设置相应权限，防止被其他用户查看
　　2、基本参数：
　　配置文件：/etc/my.cnf，集中式的多段配置文件
　　[mysqld]
　　datadir=/var/lib/mysql/              //用来定义数据库存储位置,要求存储目录必须是mysql用户以及msyql组所有。
　　back_log                                 //要求mysql具有的链接数量，最大为50
　　max_connections                   //允许同时访问的数量。
　　[mysql]
　　[client]
　　实现开关机自动开启和关闭MySQL服务
　　通常情况下，如果使用rpm方式安装会自动安装此功能，可以通过查看是否存在/etc/rc.d/init.d/mysql来确定，如果通过二进制安装或者编译安装的，需要进入安装目录的mysql/support-files/目录查找mysql.server文件。
　　手动安装：
　　1、cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/
　　2、ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc3.d/s99mysql
　　3、ln -s /etc/rc.d/init.d/mysql.server /etc/rc.d/rc0.d/s01mysql
　　配置文件详解：http://www.cnblogs.com/toby/articles/2198697.html
　　常用命令
　　查看版本信息
　　select version()；
　　显示服务器参数变量、当前数据库的详细配置信息
　　（有些变量可以修改，这些参数可以改变数据库的工作特性。有些立即生效，有些需要重启数据库生效。）
　　show [gloab|session] variables;
　　显示服务器状态变量，记录了当前包括过去时间内mysql的运行统计数据
　　show [gloab|session]status
　　3、MySQL安全
　　
　　用户管理
　　1、创建用户：create user

　　实例：  create user gongbing@192.168.1.0/24>
　　create user gongbing@192.168.1.0.%>　　可以使用通配符
　　%表示任意字符
　　_表示任意单个字符
　　2、用户授权创建和删除用户权限：GRANT REVOKE
　　用户授权： grant all privileges on dbname.tablename to username@host [identified by ‘password’]
　　授予用户username可以通过host对dbname.tablename数据库的表拥有所有权限
　　注意: 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON *.* TO 'pig'@'%';则REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤销该用户对test数据库中user表的Select 权限.
　　3、修改用户密码
　　a）、修改root密码，这种方式适用于知道root密码（mysql数据库的密码不要和linux的密码混淆了哦。）mysqladmin -u username -p  password ‘newpassword’
　　--注意：当使用mysqladmin修改普通用户时
　　[root@gc ~]# [root@gc ~]# mysqladmin -uhive -phive1 password hive
　　mysqladmin: Can't turn off logging; error: 'Access denied; you need (at least one of) the SUPER privilege(s) for this operation'
　　
　　b）、直接更改user表的password字段
　　适用于忘记root、普通用户密码。

　　编辑my.cnf（windows的是mysql.ini）文件，添加skip-grant-tables
　　[mysqld]
　　datadir=/var/lib/mysql
　　socket=/var/lib/mysql/mysql.sock
　　skip-name-resolve
　　skip-grant-tables

　　c）、登陆mysql数据库并修改用户名密码
　　mysql> use mysql;
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　Database changed
　　mysql>  update user set password=password('123123') where user='root';
　　Query OK, 4 rows affected (0.00 sec)
　　Rows matched: 4  Changed: 4  Warnings: 0
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)

　　注意：mysql5.6在通过上面修改密码后要求使用set password再设置一次密码。
　　d）、通过set命令设置
　　语法:SET PASSWORD FOR '用户名'@'主机' = PASSWORD('密码')
　　mysql> create database gongbing;
　　ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
　　mysql> set password for 'root'@'localhost'=password('123123');
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> create database gongbing;
　　Query OK, 1 row affected (0.00 sec)
　　--允许root通过远程登录
　　mysql> set password for 'root'@'%'=password('123123');
　　注意：要使用password函数，但不用flush privileges命令。
　　修改密码摘自：http://blog.csdn.net/lichangzai/article/details/8626591
　　4、删除用户
　　drop user ‘username’@‘localhost‘
　　系统内部安全
　　1、建议将数据目录的权限设置为700
　　2、不要使用root启动MySQL
　　3、注意~/.bash.history和~/.mysql.history目录，防止记录被窃取。
　　4、登录时密码不要直接输入在-p后面。
　　mysql5.5数据库root账户被删除的处理方法：
　　1、关闭mysql，并使用mysqld_safe --skip-grant-tables &重启

　　2、使用grant all privileges on *.* to root@localhost>　　问题：
MySQL报错：The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
　　处理方法：
　　mysql>set global read_only=0;
　　mysql>flush privileges
　　外部网络安全
　　1、修改用户密码
　　方法一、mysqladmin -u root -p  password ‘newpassword’
　　方法二、set password for 'root'@'localhost'=password('root');
　　方法三、use mysql;
　　update user set password=password('new_password') where user='root';
　　flush privileges;
　　2、删除多余账户
　　delete from user where='';
　　小技巧：
　　1）、忘记MySQL的root密码（本地登录拒绝访问）

　　1、safe_mysqld --skip-grant-tables &
　　2、使用root账户登录指定mysql数据库    mysql -u root mysql
　　3、更改密码：update user set password=password(’newpassword’) where user=‘root’
　　4、更新数据库授权表：flush privileges;
　　5、重启数据库并登录。

　　2）、启动MYSQL服务器加上--skip-show-database，可以使数据库用户不能浏览其他数据库。
　　3）、启动mysql服务器加上--log-slow-queriesl=“”，这样mysql会把SQL执行时间超过long_query_time的写入file中。
　　4）、启动msyql服务器不监听任何TCP/IP协议--skip-network。
　　
　　优化查询语句
　　./safe_mysqld --log-slow-queries=file                    //将sql语句查询超时的记录下来。
　　
　　MYSQL用户管理视频来源：http://edu.51cto.com/lession/id-75676.html
　　第四节 MYSQL管理
　　
　　一、使用命令工具
　　
　　MYSQL客户端命令

　　帮助：help
　　quit，\q：退出
　　delimiter，\d：更换结束符；，默认服务器端命令是；作为结束符
　　go，\g：强制将命令发送到mysql上去，可以没有；
　　use，\u：用于设定默认数据库

mysql>use mysql

ego，\G：将命令送到mysql上面，并将结果竖向现实，对乱码有用。

　　mysql> select * from user\G
　　*************************** 1. row ***************************
　　Host: %
　　User: root
　　Password: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257
　　Select_priv: Y
　　Insert_priv: Y
　　Update_priv: Y
　　Delete_priv: Y
　　Create_priv: Y
　　Drop_priv: Y
　　Reload_priv: Y
　　Shutdown_priv: Y
　　Process_priv: Y
　　File_priv: Y
　　Grant_priv: Y
　　References_priv: Y
　　Index_priv: Y
　　Alter_priv: Y
　　Show_db_priv: Y
　　Super_priv: Y
　　Create_tmp_table_priv: Y
　　Lock_tables_priv: Y
　　Execute_priv: Y
　　Repl_slave_priv: Y
　　Repl_client_priv: Y
　　Create_view_priv: Y
　　Show_view_priv: Y
　　Create_routine_priv: Y
　　Alter_routine_priv: Y
　　Create_user_priv: Y
　　Event_priv: Y
　　Trigger_priv: Y
　　Create_tablespace_priv: Y
　　ssl_type:
　　ssl_cipher:
　　x509_issuer:
　　x509_subject:
　　max_questions: 0
　　max_updates: 0
　　max_connections: 0
　　max_user_connections: 0
　　plugin:
　　authentication_string: NULL
　　*************************** 2. row ***************************

system，\!：不退出当前程序的情况下，执行shell命令。

　　mysql> system ls -l
　　total 151680
　　-rwxr-xr-x. 1 root root    25470 Mar  3 05:38 innochecksum
　　-rwxr-xr-x. 1 root root     1458 Mar  3 05:28 msql2mysql
　　-rwxr-xr-x. 1 root root  6162461 Mar  3 05:38 myisamchk
　　-rwxr-xr-x. 1 root root  5768008 Mar  3 05:38 myisam_ftdump
　　-rwxr-xr-x. 1 root root  5749127 Mar  3 05:38 myisamlog
　　-rwxr-xr-x. 1 root root  5833390 Mar  3 05:38 myisampack

status：获取当前服务器状态信息

　　mysql> status;
　　--------------
　　mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
　　Connection id:          69
　　Current database:
　　Current user:           root@localhost
　　SSL:                    Not in use
　　Current pager:          stdout
　　Using outfile:          ''
　　Using delimiter:        ;
　　Server version:         5.5.48 Source distribution
　　Protocol version:       10
　　Connection:             Localhost via UNIX socket
　　Server characterset:    latin1
　　Db     characterset:    latin1
　　Client characterset:    latin1
　　Conn.  characterset:    latin1
　　UNIX socket:            /var/lib/mysql/mysql.sock
　　Uptime:                 4 hours 20 min 52 sec
　　Threads: 3  Questions: 787  Slow queries: 0  Opens: 48  Flush tables: 1  Open tables: 41  Queries per second avg: 0.050
　　--------------

　　1、mysql命令支持交互式，批处理命令式。
　　交互式
　　-h --host=：指定主机名
　　-u --user=：用户名
　　-p --password=：密码
　　-D db_name，--database=： 直接设置某个数据库为默认数据库
　　-e ‘sql_statement；’：将sql语句外置。
　　实例：链接远程192.168.1.2主机，默认库为mysql。
　　mysql -h 192.168.1.2 -D mysql -uroot  -p
　　2、批处理方式：
　　方法一、mysql options  source /path/scripts.sql
　　服务器端命令
　　查看帮助，使用help后面跟随需要了解的命令，比如help create 来了解create后面可以接那些参数，再help create user来详细了解具体使用方法。
　　小技巧：
　　如果公司为了安全考虑，对mysql进行了身份验证，并且登陆时能够直接指定默认数据库可以编写一个my.cnf文件，并放在用户的家目录下。
　　[client]
　　user=mysql
　　passowrd=mysqlpassword
　　database=dtedu
　　3、mysqladmin管理性操作：直接通过mysqladmin [option] 来进行。
　　create databasename；                 //创建一个数据库
　　drop databasename；                   //删除一个数据库及所有包含的表
　　extended-status；                          //给出服务器的扩展状态
　　flush-hosts;                                     //清空所有缓存主机
　　flush-logs；                                    //清空所有日志
　　flush-tables;                                   //清空所有表
　　flush-privileges；                          //重新加载授权表
　　password；                                     //修改密码
　　ping；                                              //检测mysql是否活动
　　processlist；                                     //显示服务器中活动线程列表
　　refresh；                                          //清空所有表并打开和关闭日志文件
　　shutdown；                                     //关闭服务器
　　status；                                             //显示状态信息
　　version；                                           //显示mysql版本
　　4、mysqlbinlog
　　将二进制日志中的项目转换为可读的表单或者SQL语句。
　　5、mysqlcheck
　　用于修复、检查数据库及其中的表。
　　[root@localhost mysql]# mysqlcheck -o mysql
　　选项：

-a 分析指定的表

-c 检查库或者表

-r 修复库、表

-o 优化指定的表

-h 指定需要操作的主机

-u 指定用户名

-p 指定密码

-auto-repair 当检查表有错误的时候自动修复

-e 完全检查（修复方式）

-m 只检查程序

-q 快速检查

-F 检查没有正确关闭的表

-f 遇到错误sql，强制执行。

　　6、mysqlhotcopy
　　需要提前安装perl-DBI和perl-DBD-mysql
　　参见：http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql/INSTALL.pod
　　http://linux.fcbu.com/mysqlhotcopy.htm
　　perl-DBD-mysql源码安装包  http://search.cpan.org/dist/DBD-mysql/
　　用途：用于为活动的数据执行一次安全备份
　　选项：

　　--checkpoint  用于指定存放操作记录的数据库或表
　　--addtodest 增量备份、新的备份自动覆盖原来的备份。

　　示例：备份一个数据库到一个目录中
　　[root@localhost data]# /usr/local/mysql/bin/mysqlhotcopy mysql /root/
　　恢复数据库文件，只需将备份的文件复制到数据库指定的目录下即可
　　[root@localhost data]# cp -rf /root/mysql /usr/local/mysql/data/
　　mysql备份参考：http://www.linuxidc.com/Linux/2015-01/111340.htm
　　7、mysqldump备份数据库
　　参数：
　　-h：指定远程主机ip地址
　　-u：指定远程需要备份主机的用户名
　　-p：指定远程备份主机的密码，可以为空，然后手动输入，这样比较安全。
　　-a：完全备份所有数据库
　　常用命令格式：
　　mysqldump -u root -p databasename >/var/backupmysql/backupname.sql
　　恢复( 导入数据库)
　　前提：如果需要导入的数据库之前没有创建过，那么需要先建立一个同名数据库。create database ec_school，然后才能导入。
　　导入方法：
　　方法一、进入mysql后，如果是对数据库恢复直接使用 source /var/backupmysql/backupname.sql即可。
　　方法二、mysqldump -u root -p ec_school show tables

    -> ;

+---------------------------+

| Tables_in_mysql           |

+---------------------------+

| columns_priv              |

| db                        |

| event                     |

| func                      |

| general_log               |

| help_category             |

| help_keyword              |

| help_relation             |

| help_topic                |

| host                      |

| ndb_binlog_index          |

| plugin                    |

| proc                      |

| procs_priv                |

| proxies_priv              |

| servers                   |

| slow_log                  |

| tables_priv               |

| time_zone                 |

| time_zone_leap_second     |

| time_zone_name            |

| time_zone_transition      |

| time_zone_transition_type |

| user                      |

+---------------------------+

24 rows in set (0.00 sec)

　　4、显示数据库中表的结构
　　mysql> describe user
　　-> ;
　　+------------------------+-----------------------------------+------+-----+---------+-------+
　　| Field                  | Type                              | Null | Key | Default | Extra |
　　+------------------------+-----------------------------------+------+-----+---------+-------+
　　| Host                   | char(60)                          | NO   | PRI |         |       |
　　| User                   | char(16)                          | NO   | PRI |         |       |
　　| Password               | char(41)                          | NO   |     |         |       |
　　5、创建数据库
　　create database databasename；
　　6、创建表
　　use databasename；
　　create table tablename；
　　7、删除数据库、表
　　drop database databasename；
　　drop table tablename；
　　8、清空表中的记录
　　delete from tablename；
　　9、显示表中的记录
　　select * from tablename；
　　三、用户权限管理
　　GRANT和REVOKE
　　1、revoke（撤销权限）
　　撤权和授权的格式基本一样，只是撤权使用的是from指向用户名，而不是to。
　　2、GRANT(授权)

　　语法格式：GRANT privileges （columns） ON what TO user>　　实例：
　　1、授予用户test对数据库mysql有读取并修改数据库内容，但不能创建新表或者删除表

　　grant select,insert,delete,update on mysql.* to test@localhost>　　2、授予用户只对表的某些列可以查看的权限。

　　grant select (treet,city,zip) on mysql.address to mysql@localhost>　　对应privileges所指定的用户权限主要有一下几种表示方法：
权限指定符含义　　alter
　　修改表和索引
　　create
　　创建数据库和表
　　delete
　　删除表中的记录
　　drop
　　删除数据库和表
　　index
　　创建和删除索引
　　insert
　　向表中插入新行
　　select
　　查找表中的数据
　　update
　　更新表的记录，修改，编辑
　　file
读写服务器上的文件process　　查看服务器中执行的进程信息或者杀死进程
　　reload
　　重载授权表或者清空日志，缓存
　　shutdown
　　关闭服务器
　　all
　　所有服务
　　usage
　　特殊的“无权限”权限
　　columns：用于定义用户可以设置的表的“列”，多个“列”之间用“逗号”分割。
　　what：用于确定数据库及表的范围。
　　user：权限授予的用户，他由用户名、主机名组成，可以指定那个用户可以通过那个主机连接到数据库中来。
　　通过username@hostname的方式可以限制用户只能在指定的主机上登陆访问数据库，主机名可以使用%来表示任意主机，192.168.%表示一个网络地址范围，mysql3.23版本之后可以通过直接跟子网掩码号来确定ip的网络地址。
　　with grant option ：被授权用户有再给其他用户授权的能力。
　　小技巧：通过测试发现，手动授权会造成各种问题，建议初始化mysql数据库后，通过phpmyadmin来完成其他用户的权限赋值过程，会比较稳定。
　　配置phpmyadmin可以参考后面的下面的文档，主要是copy config_sample_inc.php文件到htdocs/config_inc.php，并修改参数secret。