- 浏览: 242218 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (127)
- vim (3)
- python (44)
- pymysql (1)
- mysql (9)
- macvim (1)
- erlang (3)
- twisted (0)
- tornado (5)
- django (7)
- postgresql (5)
- sql (1)
- java (7)
- tech (4)
- cache (1)
- lifestyle (3)
- html (1)
- ubuntu (2)
- rabbitmq (1)
- algorithm (8)
- Linux (4)
- Pythonista (1)
- thread (1)
- sort (6)
- 设计模式 (1)
- search (1)
- Unix (6)
- Socket (3)
- C (2)
- web (1)
- gc (1)
- php (10)
- macos (1)
最新评论
-
2057:
这个程序有bug。
查找算法学习之二分查找(Python版本)——BinarySearch -
dotjar:
NB
一个Python程序员的进化[转]
引用
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> 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)
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> 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)
以上每个表都存储了关于系统给的信息。保存权限信息的的6个表分别是:user、host、db、tables_priv和columns_priv以及procs_priv。有时,这些表也称为授权表(grant tables)
引用
user表和host表用于确定一个用户是否可以连接MySQL服务器,以及该用户是否具有任何管理员权限。db表和host表确定用户可以访问哪些数据库。tables_priv表确定用户可以使用数据库中的哪些表,而columns_priv表确定用户可以访问表中的哪些列,procs_priv表确定用户可以执行哪些过程。
更新权限:修改什么时候生效
- 1、flush privileges
- 2、mysqladmin flush-privileges
- 3、mysqladmin reload
从操作系统角度来保护MySQL
创建一个专门用来运行mysqld的特定MySQL用户是一个好主意。此外,还可建立只能够由MySQL用户访问的目录。
使用show获取信息:
引用
mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/show.html
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW AUTHORS
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CONTRIBUTORS
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.5/en/extended-show.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/show.html
用explain理解查询操作的工作过程
有两种方式可以调用explain语句
- 第一种:explain table;
- 第二种:可以使用explain语句来查看MySQL是如何来解释并执行一个SELECT查询。
引用
mysql> help explain;
Name: 'EXPLAIN'
Description:
Syntax:
EXPLAIN [explain_type] SELECT select_options
explain_type:
EXTENDED
| PARTITIONS
Or:
EXPLAIN tbl_name
The EXPLAIN statement can be used either as a way to obtain information
about how MySQL executes a statement, or as a synonym for DESCRIBE:
o When you precede a SELECT statement with the keyword EXPLAIN, MySQL
displays information from the optimizer about the query execution
plan. That is, MySQL explains how it would process the statement,
including information about how tables are joined and in which order.
EXPLAIN EXTENDED can be used to obtain additional information.
For information about using EXPLAIN and EXPLAIN EXTENDED to obtain
query execution plan information, see
http://dev.mysql.com/doc/refman/5.5/en/using-explain.html.
o EXPLAIN PARTITIONS is useful only when examining queries involving
partitioned tables. For details, see
http://dev.mysql.com/doc/refman/5.5/en/partitioning-info.html.
o EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS
FROM tbl_name. For information about DESCRIBE and SHOW COLUMNS, see
[HELP DESCRIBE], and [HELP SHOW COLUMNS].
URL: http://dev.mysql.com/doc/refman/5.5/en/explain.html
Name: 'EXPLAIN'
Description:
Syntax:
EXPLAIN [explain_type] SELECT select_options
explain_type:
EXTENDED
| PARTITIONS
Or:
EXPLAIN tbl_name
The EXPLAIN statement can be used either as a way to obtain information
about how MySQL executes a statement, or as a synonym for DESCRIBE:
o When you precede a SELECT statement with the keyword EXPLAIN, MySQL
displays information from the optimizer about the query execution
plan. That is, MySQL explains how it would process the statement,
including information about how tables are joined and in which order.
EXPLAIN EXTENDED can be used to obtain additional information.
For information about using EXPLAIN and EXPLAIN EXTENDED to obtain
query execution plan information, see
http://dev.mysql.com/doc/refman/5.5/en/using-explain.html.
o EXPLAIN PARTITIONS is useful only when examining queries involving
partitioned tables. For details, see
http://dev.mysql.com/doc/refman/5.5/en/partitioning-info.html.
o EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS
FROM tbl_name. For information about DESCRIBE and SHOW COLUMNS, see
[HELP DESCRIBE], and [HELP SHOW COLUMNS].
URL: http://dev.mysql.com/doc/refman/5.5/en/explain.html
使用analyze table
引用
mysql> help analyze table;
Name: 'ANALYZE TABLE'
Description:
Syntax:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
ANALYZE TABLE analyzes and stores the key distribution for a table.
During the analysis, the table is locked with a read lock for InnoDB
and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables.
For MyISAM tables, this statement is equivalent to using myisamchk
--analyze.
For more information on how the analysis works within InnoDB, see
http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html.
MySQL uses the stored key distribution to decide the order in which
tables should be joined when you perform a join on something other than
a constant. In addition, key distributions can be used when deciding
which indexes to use for a specific table within a query.
This statement requires SELECT and INSERT privileges for the table.
ANALYZE TABLE is supported for partitioned tables, and you can use
ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions;
for more information, see [HELP ALTER TABLE], and
http://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
Name: 'ANALYZE TABLE'
Description:
Syntax:
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
tbl_name [, tbl_name] ...
ANALYZE TABLE analyzes and stores the key distribution for a table.
During the analysis, the table is locked with a read lock for InnoDB
and MyISAM. This statement works with InnoDB, NDB, and MyISAM tables.
For MyISAM tables, this statement is equivalent to using myisamchk
--analyze.
For more information on how the analysis works within InnoDB, see
http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html.
MySQL uses the stored key distribution to decide the order in which
tables should be joined when you perform a join on something other than
a constant. In addition, key distributions can be used when deciding
which indexes to use for a specific table within a query.
This statement requires SELECT and INSERT privileges for the table.
ANALYZE TABLE is supported for partitioned tables, and you can use
ALTER TABLE ... ANALYZE PARTITION to analyze one or more partitions;
for more information, see [HELP ALTER TABLE], and
http://dev.mysql.com/doc/refman/5.5/en/partitioning-maintenance.html.
URL: http://dev.mysql.com/doc/refman/5.5/en/analyze-table.html
数据库的优化
- 设计优化:如果可能,尽量避免使用可变长度列(想VARCHAR、TEXT和BLOB)。如果字段长度固定,它们用起来将更快,但是要占用多一点的空间。
- 权限:简化权限提高查询速度。在执行之前通过权限系统检查该查询的过程,该过程越简单,查询速度越快。
- 表的优化:使用 optimize table tablename;
- 使用索引:简化索引,不要创建查询不使用的索引。
- 使用默认值:在尽可能的地方使用列的默认值,只在于默认值不同的时候才插入数据。这样可以减少执行INSERT语句所花的时间。
备份MySQL数据库
- 第一种方式是在复制数据文件时候使用LOCK TABLES命令锁定这些表 LOCK TABLES table lock_type[, table lock_type …]
- 第二种方法是使用mysql_dump命令。 mysqldump --opt --all-databases > all.sql
- 第三种方法是使用mysqlhotcopy脚本 mysqlhotcopy database /path/for/backup
每一个表必须是表的名称,而锁定类型可是是READ或WRITE。对于备份来说,只需要READ锁。在执行备份之前。必须执行FLUSH TABLES;命令来确定对索引所做的任何修改将写入磁盘。
恢复MySQL数据库
如果出现了一个破坏了的表,可以运行带有-r选项的myismchk命令。
如果使用了第一种方法执行备份,可以将数据文件重新复制到安装MySQL的相同位置。
如果使用了第二种方式执行备份,数据库的恢复就需要执行一些操作。首先,必须在导出文件中运行查询。这可以将数据库重新构建至导出该文件时的状态。接着,还应该将数据库更新至保存在二进制日子文件中的状态。
mysqlbinlog hostname-bin.[0-9]* |mysql
LOAD DATA INFILE语句
LOAD DATA INFILE "newbooks.txt" INTO TABLE books;
事务
事务是确保数据库一致的机制,尤其是在发生错误或服务器崩溃情况下确保数据库一致的机制。
ACID原则:
- Atomicity(原子性)——一个事务必须是原子性的;也就是说,它必须作为一个整体完全执行或者不执行。
- Consistency(一致性)——一个事务必须能够使数据库处于一致的状态。
- Isolation(孤立性)——未完全完成的事务不能被数据库的其他用户所见,也就是说在事务完成之前,它们都是孤立的。
- Durability(持续性)——一旦写入到数据库后,事务必须是永久的而且持续的。
屏蔽用户的输入
- 必须使用函数addslashes()在用户数据进入数据库之前过滤该数据。这个函数过滤掉可能引起数据库问题的字符。可使用函数stripslashes()将数据返回到它的原始形式。
- 可以在php.ini配置文件中开始magic_quotes_gpc和magic_quotes_runtime的指令。这些指令将自动地添加和过滤斜杠。magic_quotes_gpc指令用于格式化GET、POST和cookie变量,而magic_qupte_runtime指令用于格式化进出数据库的数据。
- 当传递用户数据给system()或者exec()时,必须使用函数excapeshellcmd()。该函数可以避免任何怀有恶意的用户输入强迫系统运行某些特定命令的字符。
- 可以使用函数strip_tags()从一个字符串中去掉HTML和PHP标记。这样可以避免用户将恶意的脚本植入到用户将恶意的脚本植入到用户的数据中。
- 可以使用函数htmlspecialchars(),该函数将字符转换成它们的HTML等价实体。例如,“<”被转换成“”
参考资料:
PHP&MySQL.Web
发表评论
-
MacOS PHP Warning: move_uploaded_file(): Unable to move '/private/var/tmp/phpgA
2013-09-06 11:45 3263引用Log into your ftp and confirm ... -
Learning PHP-MySQL基础知识
2013-08-28 23:41 1183引用关系数据库中有三种基本的关系类型。根据关系双方所含对象的多 ... -
Learning PHP-错误和异常处理
2013-08-28 23:37 826Try...Catch <?php try{ } ... -
Learning PHP-面向对象的PHP
2013-08-27 00:58 1201面向对象的开发方法试图在系统中引入对象的分类、关系和属性,从而 ... -
Learning PHP-代码重用和函数编写
2013-08-26 00:25 1103代码重用的好处: 在 ... -
Learning PHP-String与正则表达式
2013-08-26 00:22 1143字符串的格式化 字符串的整理:chop()、ltrim()和t ... -
Learning PHP-array
2013-08-21 19:12 970引用存储在数组中的值为数组元素。每个数组元素有一个相关的索引, ... -
Learning PHP -数据的存储与检索
2013-08-21 12:22 1232存储数据有两种基本方法:保存到普通文件,或者保存到数据库中。 ... -
Learning PHP -简介
2013-08-19 22:56 982PHP(PHP:Hypertext Preprocessor, ... -
索引的设计和使用
2013-08-11 16:55 929索引是数据库中用来提 ... -
MySQL支持的数据类型
2013-08-11 16:02 1665本文将介绍MySQL所支持的数据类型,内容整理于书籍。 1、数 ... -
B-tree索引和hash索引
2013-06-28 18:09 1509A B-tree index can be used for ... -
Mysql 存储引擎
2013-02-28 23:36 890MySQL支持的存储引擎包括MyISAM,InnoDB,BDB ... -
DDL、DML、DCL的一些操作
2012-06-26 23:53 9481、修改表类型,语法如下: ALTER TABLE ta ... -
mysql handler与select 性能比对
2011-10-27 17:47 1322mysql> create table test(i ... -
mysql handler操作
2011-10-27 16:19 4419HANDLER tbl_name OPEN [ [AS] ...
相关推荐
cn-deep-learning-vs-machine-learning-ebook(1)
dokumen.pub_learning-php-mysql-amp-javascript-a-step-by-step-guide-to-creating-dynamic-websites-6nbsped-1492093823-9781492093824-a-3947587.pdf
Machine Learning End-to-End guide for Java developers 英文epub 本资源转载自网络,如有侵权,请联系上传者或csdn删除 本资源转载自网络,如有侵权,请联系上传者或csdn删除
CoLight: Learning Network-level Cooperation for Traffic Signal Control
pytorch-Learning-to-See-in-the-Dark代码,CVPR论文 亲测,可行
Learning.PHP.MySQL.&.JavaScript.4th.Edition.2014.12.pdf
学习利用python实现数据的预处理的程序
21个例子学深度学习,tensorflow程序原代码Deep-Learning-21-Examples-master.21个项目玩转深度学习:基于tensorflow的实践详解
Deep-Learning-for-Beginners-master代码Deep-Learning-for-Beginners-master代码Deep-Learning-for-Beginners-master代码Deep-Learning-for-Beginners-master代码Deep-Learning-for-Beginners-master代码
Deep Reinforcement Learning Hands-On English | 21 Jun. 2018 | ISBN: 1788834240 | 546 Pages | EPUB | 12.69 MB
learning-spring-boot-2-0-2learning-spring-boot-2-0-2learning-spring-boot-2-0-2
开源e-learning软件---eConf
Learning Non-Local Range Markov Random Field for Image Restoration, http://gr.xjtu.edu.cn/web/jiansun/4
Springer chapter A Self-learning Anomaly-Based Web Application Firewall
深度学习方面经典问题分析,包含深度学习中文版教程,帮助初学者更好入门
Learning PHP, MySQL & JavaScript, 5th Edition.epub
opengl_learning--C-GLSL-C++.cmake-2020m6
Deep Learning for Signal Processing with MATLAB。Deep learning networks have been used for image classification for many years, but they are powerful tools for signal data as well. A deep learning ...