Table of Contents for
MySQL in a Nutshell, 2nd Edition

Version ebook / Retour

Cover image for bash Cookbook, 2nd Edition MySQL in a Nutshell, 2nd Edition by Russell J.T. Dyer Published by O'Reilly Media, Inc., 2008
  1. Cover
  2. MySQL in a Nutshell
  3. MySQL in a Nutshell
  4. Dedication
  5. Preface
  6. The Purpose of This Book
  7. How This Book Is Organized
  8. Conventions Used in This Book
  9. Using Code Examples
  10. Request for Comments
  11. Safari® Enabled
  12. Acknowledgments
  13. I. Introduction and Tutorials
  14. 1. Introduction to MySQL
  15. The Value of MySQL
  16. The MySQL Package
  17. Licensing
  18. Mailing Lists
  19. Books and Other Publications
  20. 2. Installing MySQL
  21. Choosing a Distribution
  22. Unix Source Distributions
  23. Unix Binary Distributions
  24. Linux RPM Distributions
  25. Macintosh OS X Distributions
  26. Novell NetWare Distributions
  27. Windows Distributions
  28. Postinstallation
  29. 3. MySQL Basics
  30. The mysql Client
  31. Creating a Database and Tables
  32. Show Me
  33. Inserting Data
  34. Selecting Data
  35. Ordering, Limiting, and Grouping
  36. Analyzing and Manipulating Data
  37. Changing Data
  38. Deleting Data
  39. Searching Data
  40. Importing Data in Bulk
  41. Command-Line Interface
  42. Conclusion
  43. II. SQL Statements and Functions
  44. 4. Security and User Statements and Functions
  45. Statements and Functions
  46. SQL Statements in Alphabetical Order
  47. CREATE USER
  48. DROP USER
  49. FLUSH
  50. GRANT
  51. RENAME USER
  52. RESET
  53. REVOKE
  54. SET PASSWORD
  55. SHOW GRANTS
  56. SHOW PRIVILEGES
  57. Functions in Alphabetical Order
  58. AES_DECRYPT()
  59. AES_ENCRYPT()
  60. CURRENT_USER()
  61. DECODE()
  62. DES_DECRYPT()
  63. DES_ENCRYPT()
  64. ENCODE()
  65. ENCRYPT()
  66. MD5()
  67. OLD_PASSWORD()
  68. PASSWORD()
  69. SESSION_USER()
  70. SHA()
  71. SHA1()
  72. SYSTEM_USER()
  73. USER()
  74. 5. Database and Table Schema Statements
  75. Statements and Clauses in Alphabetical Order
  76. ALTER DATABASE
  77. ALTER SCHEMA
  78. ALTER SERVER
  79. ALTER TABLE
  80. ALTER VIEW
  81. CREATE DATABASE
  82. CREATE INDEX
  83. CREATE SCHEMA
  84. CREATE SERVER
  85. CREATE TABLE
  86. CREATE VIEW
  87. DESCRIBE
  88. DROP DATABASE
  89. DROP INDEX
  90. DROP SERVER
  91. DROP TABLE
  92. DROP VIEW
  93. RENAME DATABASE
  94. RENAME TABLE
  95. SHOW CHARACTER SET
  96. SHOW COLLATION
  97. SHOW COLUMNS
  98. SHOW CREATE DATABASE
  99. SHOW CREATE TABLE
  100. SHOW CREATE VIEW
  101. SHOW DATABASES
  102. SHOW INDEXES
  103. SHOW SCHEMAS
  104. SHOW TABLE STATUS
  105. SHOW TABLES
  106. SHOW VIEWS
  107. 6. Data Manipulation Statements and Functions
  108. Statements and Functions Grouped by Characteristics
  109. Statements and Clauses in Alphabetical Order
  110. BEGIN
  111. COMMIT
  112. DELETE
  113. DO
  114. EXPLAIN
  115. HANDLER
  116. HELP
  117. INSERT
  118. JOIN
  119. LIMIT
  120. LOAD DATA INFILE
  121. RELEASE SAVEPOINT
  122. REPLACE
  123. ROLLBACK
  124. ROLLBACK TO SAVEPOINT
  125. SAVEPOINT
  126. SELECT
  127. SET
  128. SET TRANSACTION
  129. SHOW ERRORS
  130. SHOW WARNINGS
  131. START TRANSACTION
  132. TRUNCATE
  133. UNION
  134. UPDATE
  135. USE
  136. XA
  137. Functions in Alphabetical Order
  138. ANALYSE()
  139. BENCHMARK()
  140. DATABASE()
  141. FOUND_ROWS()
  142. LAST_INSERT_ID()
  143. ROW_COUNT()
  144. SCHEMA()
  145. 7. Table and Server Administration Statements and Functions
  146. Statements and Clauses in Alphabetical Order
  147. ALTER SERVER
  148. ANALYZE TABLE
  149. BACKUP TABLE
  150. CACHE INDEX
  151. CHECK TABLE
  152. CHECKSUM TABLE
  153. CREATE SERVER
  154. FLUSH
  155. KILL
  156. LOAD INDEX INTO CACHE
  157. LOCK TABLES
  158. OPTIMIZE TABLE
  159. REPAIR TABLE
  160. RESET
  161. RESTORE TABLE
  162. SET
  163. SHOW ENGINE
  164. SHOW ENGINES
  165. SHOW OPEN TABLES
  166. SHOW PLUGINS
  167. SHOW PROCESSLIST
  168. SHOW STATUS
  169. SHOW TABLE STATUS
  170. SHOW VARIABLES
  171. UNLOCK TABLES
  172. Functions in Alphabetical Order
  173. CONNECTION_ID()
  174. GET_LOCK()
  175. IS_FREE_LOCK()
  176. IS_USED_LOCK()
  177. RELEASE_LOCK()
  178. UUID()
  179. VERSION()
  180. 8. Replication Statements and Functions
  181. Merits of Replication
  182. Replication Process
  183. The Replication User Account
  184. Configuring the Servers
  185. Copying Databases and Starting Replication
  186. Starting Replication
  187. Backups with Replication
  188. SQL Statements and Functions in Alphabetical Order
  189. CHANGE MASTER TO
  190. LOAD DATA FROM MASTER
  191. LOAD TABLE...FROM MASTER
  192. MASTER_POS_WAIT()
  193. PURGE MASTER LOGS
  194. RESET MASTER
  195. RESET SLAVE
  196. SET GLOBAL SQL_SLAVE_SKIP_COUNTER
  197. SET SQL_LOG_BIN
  198. SHOW BINLOG EVENTS
  199. SHOW BINARY LOGS
  200. SHOW MASTER LOGS
  201. SHOW MASTER STATUS
  202. SHOW SLAVE HOSTS
  203. SHOW SLAVE STATUS
  204. START SLAVE
  205. STOP SLAVE
  206. Replication States
  207. 9. Stored Routines Statements
  208. Statements in Alphabetical Order
  209. ALTER EVENT
  210. ALTER FUNCTION
  211. ALTER PROCEDURE
  212. ALTER TRIGGER
  213. BEGIN...END
  214. CALL
  215. CLOSE
  216. CREATE EVENT
  217. CREATE FUNCTION
  218. CREATE PROCEDURE
  219. CREATE TRIGGER
  220. DECLARE
  221. DELIMITER
  222. DROP EVENT
  223. DROP FUNCTION
  224. DROP PREPARE
  225. DROP PROCEDURE
  226. DROP TRIGGER
  227. EXECUTE
  228. FETCH
  229. OPEN
  230. PREPARE
  231. SHOW CREATE EVENT
  232. SHOW CREATE FUNCTION
  233. SHOW CREATE PROCEDURE
  234. SHOW EVENTS
  235. SHOW FUNCTION CODE
  236. SHOW FUNCTION STATUS
  237. SHOW PROCEDURE CODE
  238. SHOW PROCEDURE STATUS
  239. SHOW TRIGGERS
  240. 10. Aggregate Clauses, Aggregate Functions, and Subqueries
  241. Aggregate Functions in Alphabetical Order
  242. AVG()
  243. BIT_AND()
  244. BIT_OR()
  245. BIT_XOR()
  246. COUNT()
  247. GROUP_CONCAT()
  248. MAX()
  249. MIN()
  250. STD()
  251. STDDEV()
  252. STDDEV_POP()
  253. STDDEV_SAMP()
  254. SUM()
  255. VAR_POP()
  256. VAR_SAMP()
  257. VARIANCE()
  258. Subqueries
  259. 11. String Functions
  260. String Functions Grouped by Type
  261. String Functions in Alphabetical Order
  262. ASCII()
  263. BIN()
  264. BINARY
  265. BIT_LENGTH()
  266. CAST()
  267. CHAR()
  268. CHAR_LENGTH()
  269. CHARACTER_LENGTH()
  270. CHARSET()
  271. COALESCE()
  272. COERCIBILITY()
  273. COLLATION()
  274. COMPRESS()
  275. CONCAT()
  276. CONCAT_WS()
  277. CONVERT()
  278. CRC32()
  279. ELT()
  280. EXPORT_SET()
  281. FIELD()
  282. FIND_IN_SET()
  283. HEX()
  284. INSERT()
  285. INSTR()
  286. INTERVAL()
  287. LCASE()
  288. LEFT()
  289. LENGTH()
  290. LOAD_FILE()
  291. LOCATE()
  292. LOWER()
  293. LPAD()
  294. LTRIM()
  295. MAKE_SET()
  296. MATCH() AGAINST()
  297. MID()
  298. OCTET_LENGTH()
  299. ORD()
  300. POSITION()
  301. QUOTE()
  302. REPEAT()
  303. REPLACE()
  304. REVERSE()
  305. RIGHT()
  306. RPAD()
  307. RTRIM()
  308. SOUNDEX()
  309. SPACE()
  310. STRCMP()
  311. SUBSTR()
  312. SUBSTRING()
  313. SUBSTRING_INDEX()
  314. TRIM()
  315. UCASE()
  316. UNCOMPRESS()
  317. UNCOMPRESSED_LENGTH()
  318. UNHEX()
  319. UPPER()
  320. 12. Date and Time Functions
  321. Date and Time Functions Grouped by Type
  322. Date and Time Functions in Alphabetical Order
  323. ADDDATE()
  324. ADDTIME()
  325. CONVERT_TZ()
  326. CURDATE()
  327. CURRENT_DATE()
  328. CURRENT_TIME()
  329. CURRENT_TIMESTAMP()
  330. CURTIME()
  331. DATE()
  332. DATE_ADD()
  333. DATE_FORMAT()
  334. DATE_SUB()
  335. DATEDIFF()
  336. DAY()
  337. DAYNAME()
  338. DAYOFMONTH()
  339. DAYOFWEEK()
  340. DAYOFYEAR()
  341. EXTRACT()
  342. FROM_DAYS()
  343. FROM_UNIXTIME()
  344. GET_FORMAT()
  345. HOUR()
  346. LAST_DAY()
  347. LOCALTIME()
  348. LOCALTIMESTAMP()
  349. MAKEDATE()
  350. MAKETIME()
  351. MICROSECOND()
  352. MINUTE()
  353. MONTH()
  354. MONTHNAME()
  355. NOW()
  356. PERIOD_ADD()
  357. PERIOD_DIFF()
  358. QUARTER()
  359. SEC_TO_TIME()
  360. SECOND()
  361. SLEEP()
  362. STR_TO_DATE()
  363. SUBDATE()
  364. SUBTIME()
  365. SYSDATE()
  366. TIME()
  367. TIME_FORMAT()
  368. TIME_TO_SEC()
  369. TIMEDIFF()
  370. TIMESTAMP()
  371. TIMESTAMPADD()
  372. TIMESTAMPDIFF()
  373. TO_DAYS()
  374. UNIX_TIMESTAMP()
  375. UTC_DATE()
  376. UTC_TIME()
  377. UTC_TIMESTAMP()
  378. WEEK()
  379. WEEKDAY()
  380. WEEKOFYEAR()
  381. YEAR()
  382. YEARWEEK()
  383. 13. Mathematical Functions
  384. Functions in Alphabetical Order
  385. ABS()
  386. ACOS()
  387. ASIN()
  388. ATAN()
  389. ATAN2()
  390. BIT_COUNT()
  391. CEIL()
  392. CEILING()
  393. CONV()
  394. COS()
  395. COT()
  396. DEGREES()
  397. EXP()
  398. FLOOR()
  399. FORMAT()
  400. GREATEST()
  401. INET_ATON()
  402. INET_NTOA()
  403. LEAST()
  404. LN()
  405. LOG()
  406. LOG2()
  407. LOG10()
  408. MOD()
  409. OCT()
  410. PI()
  411. POW()
  412. POWER()
  413. RADIANS()
  414. RAND()
  415. ROUND()
  416. SIGN()
  417. SIN()
  418. SQRT()
  419. TAN()
  420. TRUNCATE()
  421. 14. Flow Control Functions
  422. Functions in Alphabetical Order
  423. CASE
  424. IF()
  425. IFNULL()
  426. ISNULL()
  427. NULLIF()
  428. III. MySQL Server and Client Tools
  429. 15. MySQL Server and Client
  430. mysql Client
  431. mysql
  432. mysqld Server
  433. mysqld
  434. mysqld_multi
  435. mysqld_multi
  436. mysqld_safe
  437. mysqld_safe
  438. 16. Command-Line Utilities
  439. comp_err
  440. make_binary_distribution
  441. msql2mysql
  442. my_print_defaults
  443. myisam_ftdump
  444. myisamchk
  445. myisamlog
  446. myisampack
  447. mysql_convert_table_format
  448. mysql_find_rows
  449. mysql_fix_extensions
  450. mysql_fix_privilege_tables
  451. mysql_setpermission
  452. mysql_tableinfo
  453. mysql_upgrade
  454. mysql_waitpid
  455. mysql_zap
  456. mysqlaccess
  457. mysqladmin
  458. mysqlbinlog
  459. mysqlbug
  460. mysqlcheck
  461. mysqldump
  462. mysqldumpslow
  463. mysqlhotcopy
  464. mysqlimport
  465. mysqlshow
  466. mysqlslap
  467. perror
  468. replace
  469. resolveip
  470. resolve_stack_dump
  471. IV. APIs and Connectors
  472. 17. C API
  473. Using C with MySQL
  474. Functions in Alphabetical Order
  475. mysql_affected_rows()
  476. mysql_autocommit()
  477. mysql_change_user()
  478. mysql_character_set_name()
  479. mysql_close()
  480. mysql_commit()
  481. mysql_connect()
  482. mysql_create_db()
  483. mysql_data_seek()
  484. mysql_debug()
  485. mysql_drop_db()
  486. mysql_dump_debug_info()
  487. mysql_eof()
  488. mysql_errno()
  489. mysql_error()
  490. mysql_escape_string()
  491. mysql_fetch_field()
  492. mysql_fetch_field_direct()
  493. mysql_fetch_fields()
  494. mysql_fetch_lengths()
  495. mysql_fetch_row()
  496. mysql_field_count()
  497. mysql_field_seek()
  498. mysql_field_tell()
  499. mysql_free_result()
  500. mysql_get_client_info()
  501. mysql_get_character_set_info()
  502. mysql_get_client_version()
  503. mysql_get_host_info()
  504. mysql_get_proto_info()
  505. mysql_get_server_info()
  506. mysql_get_server_version()
  507. mysql_get_ssl_cipher()
  508. mysql_hex_string()
  509. mysql_info()
  510. mysql_init()
  511. mysql_insert_id()
  512. mysql_kill()
  513. mysql_library_end()
  514. mysql_library_init()
  515. mysql_list_dbs()
  516. mysql_list_fields()
  517. mysql_list_processes()
  518. mysql_list_tables()
  519. mysql_more_results()
  520. mysql_next_result()
  521. mysql_num_fields()
  522. mysql_num_rows()
  523. mysql_options()
  524. mysql_ping()
  525. mysql_query()
  526. mysql_real_connect()
  527. mysql_real_escape_string()
  528. mysql_real_query()
  529. mysql_reload()
  530. mysql_refresh()
  531. mysql_rollback()
  532. mysql_row_seek()
  533. mysql_row_tell()
  534. mysql_select_db()
  535. mysql_set_character_set()
  536. mysql_set_local_infile_default()
  537. mysql_set_local_infile_handler()
  538. mysql_set_server_option()
  539. mysql_shutdown()
  540. mysql_sqlstate()
  541. mysql_ssl_set()
  542. mysql_stat()
  543. mysql_store_result()
  544. mysql_thread_end()
  545. mysql_thread_id()
  546. mysql_thread_init()
  547. mysql_thread_safe()
  548. mysql_use_result()
  549. mysql_warning_count()
  550. C API Datatypes
  551. 18. Perl API
  552. Using Perl DBI with MySQL
  553. Perl DBI Reference
  554. available_drivers()
  555. begin_work()
  556. bind_col()
  557. bind_columns()
  558. bind_param()
  559. bind_param_array()
  560. bind_param_inout()
  561. can()
  562. clone()
  563. column_info()
  564. commit()
  565. connect()
  566. connect_cached()
  567. data_diff()
  568. data_sources()
  569. data_string_desc()
  570. data_string_diff()
  571. disconnect()
  572. do()
  573. dump_results()
  574. err()
  575. errstr()
  576. execute()
  577. execute_array()
  578. execute_for_fetch()
  579. fetch()
  580. fetchall_arrayref()
  581. fetchall_hashref()
  582. fetchrow_array()
  583. fetchrow_arrayref()
  584. fetchrow_hashref()
  585. finish()
  586. foreign_key_info()
  587. func()
  588. get_info()
  589. installed_drivers()
  590. installed_versions()
  591. last_insert_id()
  592. looks_like_number()
  593. neat()
  594. neat_list()
  595. parse_dsn()
  596. parse_trace_flag()
  597. parse_trace_flags()
  598. ping()
  599. prepare()
  600. prepare_cached()
  601. primary_key()
  602. primary_key_info()
  603. private_attribute_info()
  604. quote()
  605. quote_identifier()
  606. rollback()
  607. rows()
  608. selectall_arrayref()
  609. selectall_hashref()
  610. selectcol_arrayref()
  611. selectrow_array()
  612. selectrow_arrayref()
  613. selectrow_hashref()
  614. set_err()
  615. state()
  616. statistics_info()
  617. swap_inner_handle()
  618. table_info()
  619. tables()
  620. take_imp_data()
  621. trace()
  622. trace_msg()
  623. type_info()
  624. type_info_all()
  625. Attributes for Handles
  626. 19. PHP API
  627. Using PHP with MySQL
  628. PHP MySQL Functions in Alphabetical Order
  629. mysql_affected_rows()
  630. mysql_change_user()
  631. mysql_client_encoding()
  632. mysql_close()
  633. mysql_connect()
  634. mysql_create_db()
  635. mysql_data_seek()
  636. mysql_db_name()
  637. mysql_db_query()
  638. mysql_drop_db()
  639. mysql_errno()
  640. mysql_error()
  641. mysql_escape_string()
  642. mysql_fetch_array()
  643. mysql_fetch_assoc()
  644. mysql_fetch_field()
  645. mysql_fetch_lengths()
  646. mysql_fetch_object()
  647. mysql_fetch_row()
  648. mysql_field_flags()
  649. mysql_field_len()
  650. mysql_field_name()
  651. mysql_field_seek()
  652. mysql_field_table()
  653. mysql_field_type()
  654. mysql_free_result()
  655. mysql_get_client_info()
  656. mysql_get_host_info()
  657. mysql_get_proto_info()
  658. mysql_get_server_info()
  659. mysql_info()
  660. mysql_insert_id()
  661. mysql_list_dbs()
  662. mysql_list_fields()
  663. mysql_list_processes()
  664. mysql_list_tables()
  665. mysql_num_fields()
  666. mysql_num_rows()
  667. mysql_pconnect()
  668. mysql_ping()
  669. mysql_query()
  670. mysql_real_escape_string()
  671. mysql_result()
  672. mysql_select_db()
  673. mysql_set_charset()
  674. mysql_stat()
  675. mysql_tablename()
  676. mysql_thread_id()
  677. mysql_unbuffered_query()
  678. V. Appendixes
  679. A. Data Types
  680. Numeric Data Types
  681. BIT
  682. TINYINT
  683. BOOL, BOOLEAN
  684. SMALLINT
  685. MEDIUMINT
  686. INT, INTEGER
  687. BIGINT, SERIAL
  688. FLOAT
  689. DOUBLE, DOUBLE PRECISION
  690. DEC, DECIMAL, FIXED, NUMERIC
  691. Date and Time Data Types
  692. String Data Types
  693. CHAR
  694. VARCHAR
  695. BINARY
  696. VARBINARY
  697. TINYBLOB
  698. TINYTEXT
  699. BLOB
  700. TEXT
  701. MEDIUMBLOB
  702. MEDIUMTEXT
  703. LONGBLOB
  704. LONGTEXT
  705. ENUM
  706. SET
  707. B. Operators
  708. Arithmetic Operators
  709. Relational Operators
  710. Logical Operators
  711. Bitwise Operators
  712. Regular Expressions
  713. C. Server and Environment Variables
  714. Index
  715. About the Author
  716. Colophon
  717. Copyright

Name

mysqld_safe

Synopsis

mysqld_safe [options]

mysqld_safe is recommended utility for starting the MySQL server because the server is restarted automatically if it dies unexpectedly. The utility is available on Unix and Novell NetWare systems.

Although options may be entered from the command line, they should be included in the options file (e.g., my.cnf) under the heading [mysqld_safe]. Options specific to mysqld_safe should not be passed on the command line because they will be passed to the mysqld server, which will try to interpret them. Therefore, options are shown here as they would appear in the configuration file, without initial hyphens. mysqld_safe can also accept options for the mysqld server, but the configuration file is also better for these because it ensures they will be passed to the daemon when it’s reloaded after a crash:

autoclose

On Novell NetWare systems, when mysqld_safe closes, the related screen does not close automatically without user interaction. Use this option to have the screen close automatically.

basedir=path

This option is necessary and is used to specify the path to the directory where MySQL files are installed.

core-file-size=value

This option sets the maximum size set for the core file to create if the daemon dies.

datadir=path

This option specifies the directory that contains datafiles (i.e., table files).

defaults-extra-file=filename

This option specifies an additional options file to use after the default file is read. When used at the command line, this has to be the first option, except that --defaults-file must precede it if used.

defaults-file=filename

This option specifies the default options file for the server; it can be used to substitute special options for the normal default options files. When used at the command line, this has to be the first option given.

err-log=filename

This option specifies the path to the error log for error messages outside the daemon, such as errors when starting.

ledir=path

This option is necessary for running mysqld_safe. It specifies the path where the daemons may be found.

log-error[=filename]

This option enables logging of error messages and server startup messages, optionally specifying a log file. The default log file is host.err in MySQL’s data directory, where host is the host’s name.

mysqld=daemon

This option is required when using a binary distribution and the data directory for MySQL is not in the location originally set by the distribution. With it you specify which daemon to start (i.e., mysqld). This daemon program must be in the same directory given with the ledir option.

mysqld-version=[max]

This option specifies which daemon to use by providing the suffix of the daemon’s name. A value of max starts mysqld-max, whereas a blank value ensures mysqld is started.

nice=number

This option employs the nice utility to give scheduling priority to the value given.

no-defaults

This option instructs the script not to refer to configuration files for options. When used at the command line, this has to be the first option given.

open-files-limit=number

This option limits the number of files the daemon may open. Only root may use this option.

pid-file=filename

This option specifies the file that will store the server’s process identifier.

port=port

This option specifies the TCP/IP port number to which mysqld_safe should listen for incoming connections. Unless started by the root filesystem user, the port number should be 1024 or higher.

skip-kill-mysqld

When mysqld_safe is started on a Linux system, if this option is not used, any mysqld processes that are running will be terminated by it. Use this option to allow existing servers to stay up.

skip-syslog

This option causes the daemon not to log errors to the system’s syslog facility. The MySQL-specific log will still be written. This option is available as of version 5.1.20 of MySQL. See syslog below for more information related to this option.

socket=filename

This option provides the name of the server’s socket file for local connections.

syslog

On operating systems that support the logger program, this option instructs the daemon to log error messages to the related syslog. This option is available as of version 5.1.20 of MySQL. See skip-syslog above for more information related to this option.

syslog-tag

When writing error messages to syslog, this option marks each message with mysqld or mysqld_safe, depending on the source of the error. This option is available as of version 5.1.21 of MySQL. See syslog and skip-syslog previously for more information related to this option.

timezone=zone

This option sets the environment variable TZ for the timezone of the server.

user=user

This option specifies the username or user ID number for the user that starts the server.