MySQL How 2

test

lthompson — Tue, 23 Sep 2008 20:12:23 GMT

ttter

RE: Test podcast

lthompson — Tue, 23 Sep 2008 20:11:43 GMT

asdfasdfasdf

RE: Test podcast

lthompson — Wed, 27 Aug 2008 19:25:36 GMT

test

What is asyncronous?

lthompson — Fri, 06 Jun 2008 03:22:27 GMT

Asyncronous and Syncronous

Not synchronized; that is, not occurring at predetermined or regular intervals. The term asynchronous is usually used to describe communications in which data can be transmitted intermittently rather than in a steady stream. For example, a telephone conversation is asynchronous because both parties can talk whenever they like. If the communication were synchronous, each party would be required to wait a specified interval before speaking.

The difficulty with asynchronous communications is that the receiver must have a way to distinguish between valid data and noise. In computer communications, this is usually accomplished through a special start bit and stop bit at the beginning and end of each piece of data. For this reason, asynchronous communication is sometimes called start-stop transmission.
Most communications between computers and devices are asynchronous

Features of mysqldump and SELECT INTO OUTFILE

lthompson — Fri, 06 Jun 2008 01:59:46 GMT

Differences of mysqldump and SELECT INTO OUTFILE

1) The options in mysqldump can be in any order, because they are true command-line options (that is, they are conceptually used together, but syntactically separate on the mysqldump command line).

2) The options MUST have dashes between the words (e.g., fields-enclosed-by) when use as options with the mysqldump utility, but MUST NOT have dashes when used as options with the SELECT...INTO OUTFILE.

mysqldump example

mysqldump �uroot �p �opt database_name > /tmp/nameofdumpfile.sql

* Specifying the --opt argument when backing up our database should theoretically give us the fastest possible dump for reading back into MySQL server (the "opt" stands for optimize). When we specify the --opt argument, the mysqldump utility creates a more sophisticated set of dump commands, which includes the "DROP TABLE IF EXISTS" statement to delete the table from the database if it already exists when the dump file is being used to restore the database. The dump also includes several table locking statements.

SELECT INTO OUTFILE example

(login to MySQL) mysql �uroot �p
mysql> SELECT a,b,INTO OUTFILE '/tmp/datafile.txt' from table_name;

mysqldump options

Format	Config File	Description	Introduction
--add-drop-database	add-drop-database	Add a DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table	add-drop-table	Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks	add-locks	Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases	all-databases	Dump all tables in all databases
--allow-keywords	allow-keywords	Allow creation of column names that are keywords
--all-tablespaces	all-tablespaces	Adds to a table dump all SQL statements needed to create any tablespaces used by an NDB Cluster table
--comments	comments	Add comments to the dump file
--compact	compact	Produce less verbose output
--compatible=name[,name,...]	compatible	Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert	complete-insert	Use complete INSERT statements that include column names
--create-options	create-options	Include all MySQL-specific table options in the CREATE TABLE statements
--databases	databases	Dump several databases
--debug[=debug_options]	debug	Write a debugging log
--debug-info	debug-info	Print debugging information, memory and CPU statistics when the program exits	5.0.32
--delayed-insert	delayed-insert	Write INSERT DELAYED statements rather than INSERT statements
--delete-master-logs	delete-master-logs	On a master replication server, delete the binary logs after performing the dump operation
--disable-keys	disable-keys	For each table, surround the INSERT statements with disable and enable keys statements
--dump-date	dump-date	Include dump date in "Dump completed on" comment if --comments is given	5.0.52
-E	events	Dump events from the dumped databases
--extended-insert	extended-insert	Use multiple-row INSERT syntax that include several VALUES lists
--fields-enclosed-by=string	fields-enclosed-by	This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-escaped-by	fields-escaped-by	This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-optionally-enclosed-by=string	fields-optionally-enclosed-by	This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--fields-terminated-by=string	fields-terminated-by	This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables	first-slave	Deprecated. Now renamed to --lock-all-tables
--flush-logs	flush-logs	Flush the MySQL server log files before starting the dump
--flush-privileges	flush-privileges	Emit a FLUSH PRIVILEGES statement after dumping the mysql database
--help		Display help message and exit
--hex-blob	hex-blob	Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263)
--ignore-table=db_name.tbl_name	ignore-table	Do not dump the given table
--insert-ignore	insert-ignore	Write INSERT statements with the IGNORE option
--lines-terminated-by=string	lines-terminated-by	This option is used with the -T option and has the same meaning as the corresponding clause for LOAD DATA INFILE
--lock-all-tables	lock-all-tables	Lock all tables across all databases
--lock-tables	lock-tables	Lock all tables before dumping them
--log-error=file_name	log-error	Append warnings and errors to the named file	5.0.42
--master-data[=value]	master-data	Write the binary log filename and position to the output
--max_allowed_packet=value	max_allowed_packet	The maximum packet length to send to or receive from the server
--net_buffer_length=value	net_buffer_length	The buffer size for TCP/IP and socket communication
--no-autocommit	no-autocommit	Enclose the INSERT statements for each dumped table within SET AUTOCOMMIT=0 and COMMIT statements
--no-create-db	no-create-db	This option suppresses the CREATE DATABASE statements
--no-create-info	no-create-info	Do not write CREATE TABLE statements that re-create each dumped table
--no-data	no-data	Do not write any table row information (that is, do not dump table contents)
--no-set-names	no-set-names	Turn off complete-insert
--opt	opt	This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary	order-by-primary	Sorts each table's rows by its primary key, or by its first unique index
--quick	quick	Retrieve rows for a table from the server a row at a time
--quote-names	quote-names	Quote database, table, and column names within backtick characters
--replace	replace	Write REPLACE statements rather than INSERT statements
--result-file=file	result-file	Direct output to a given file
-R	routines	Dump stored routines (functions and procedures) from the dumped databases
--set-charset	set-charset	Add SET NAMES default_character_set to the output
--single-transaction	single-transaction	This option issues a BEGIN SQL statement before dumping data from the server
--skip-add-drop-table	skip-add-drop-table	Do not add
--skip-add-locks	skip-add-locks	Do not add locks
--skip-comments	skip-comments	Do not add comments to the dump file
--skip-compact	skip-compact	Turn off compact
--skip-disable-keys	skip-disable-keys	Do not disable keys
--skip-extended-insert	skip-extended-insert	Turn off extended-insert
--skip-opt	skip-opt	Turn off the options set by opt
--skip-quick	skip-quick	Do not retrieve rows for a table from the server a row at a time
--skip-quote-names	skip-quote-names	Turn off quote names
-skip-set-charset	skip-set-charset	Suppress the SET NAMES statement
--skip-triggers	skip-triggers	Turn off triggers	5.0.11
--skip-tz-utc	skip-tz-utc	Turn off tz-utc
--ssl-ca=file_name	ssl-ca	The path to a file that contains a list of trusted SSL CAs
--ssl-capath=directory_name	ssl-capath	The path to a directory that contains trusted SSL CA certificates in PEM format
--ssl-cert=file_name	ssl-cert	The name of the SSL certificate file to use for establishing a secure connection
--ssl-cipher=cipher_list	ssl-cipher	A list of allowable ciphers to use for SSL encryption
--ssl-key=file_name	ssl-key	The name of the SSL key file to use for establishing a secure connection
--ssl-verify-server-cert	ssl-verify-server-cert	The server's Common Name value in its certificate is verified against the hostname used when connecting to the server
--tab=path	tab	Produce tab-separated data files
--tables	tables	Override the --databases or -B option
--triggers	triggers	Dump triggers for each dumped table
--tz-utc	tz-utc	Add SET TIME_ZONE='+00:00' to the dump file
--verbose		Verbose mode
--version		Display version information and exit
--where='where_condition'	where	Dump only rows selected by the given WHERE condition
--xml	xml	Produce XML output

Select into outfile options

The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCII NUL is escaped to make it easier to view with some pagers.

**Another way to get data if you do not have the proper �file� permissions**

mysql �uuser �p -e "SELECT * from databasename.tablename > file_name

MySQL Show commands

lthompson — Fri, 06 Jun 2008 01:55:44 GMT

SHOW Syntax

Code:

SHOW CHARACTER SET

SHOW COLLATION

SHOW COLUMNS

SHOW CREATE DATABASE

SHOW CREATE PROCEDURE

SHOW CREATE TABLE

SHOW CREATE VIEW

SHOW DATABASES

SHOW ENGINE

SHOW ENGINES

SHOW ERRORS

SHOW GRANTS

SHOW INDEX

SHOW INNODB STATUS

SHOW LOGS

SHOW MUTEX STATUS

SHOW OPEN TABLES

SHOW PRIVILEGES

SHOW PROCEDURE CODE SHOW FUNCTION CODE

SHOW PROCEDURE STATUS and SHOW FUNCTION STATUS

SHOW PROCESSLIST

SHOW PROFILES and SHOW PROFILE

SHOW STATUS

SHOW TABLE STATUS

SHOW TABLES

SHOW TRIGGERS

SHOW VARIABLES

SHOW WARNINGS

SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:

Code:

SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION CODE sp_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE CODE sp_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW 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.

What is ACID

lthompson — Wed, 04 Jun 2008 15:29:34 GMT

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a 'keyword' that business professionals generally look for when evaluating databases. Here is a quick description of what it means to be ACID compliant:

1. Atomicity is an all-or-none proposition. Suppose you define a transaction that contains an UPDATE, an INSERT, and a DELETE statement. With atomicity, these statements are treated as a single unit, and thanks to consistency (the C in ACID) there are only two possible outcomes: either they all change the database or none of them do. This is important in situations like bank transactions where transferring money between accounts could result in disaster if the server were to go down after a DELETE statement but before the corresponding INSERT statement.

2. Consistency guarantees that a transaction never leaves your database in a half-finished state. If one part of the transaction fails, all of the pending changes are rolled back, leaving the database as it was before you initiated the transaction. For instance, when you delete a customer record, you should also delete all of that customer's records from associated tables (such as invoices and line items). A properly configured database wouldn't let you delete the customer record, if that meant leaving its invoices, and other associated records stranded.

3. Isolation keeps transactions separated from each other until they're finished. Transaction isolation is generally configurable in a variety of modes. For example, in one mode, a transaction blocks until the other transaction finishes. In a different mode, a transaction sees obsolete data (from the state the database was in before the previous transaction started). Suppose a user deletes a customer, and before the customer's invoices are deleted, a second user updates one of those invoices. In a blocking transaction scenario, the second user would have to wait for the first user's deletions to complete before issuing the update. The second user would then find out that the customer had been deleted, which is much better than losing changes without knowing about it.

4. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. Hence, even if the database server is unplugged in the middle of a transaction, it will return to a consistent state when it's restarted. The database handles this by storing uncommitted transactions in a transaction log. By virtue of consistency (explained above), a partially completed transaction won't be written to the database in the event of an abnormal termination. However, when the database is restarted after such a termination, it examines the transaction log for completed transactions that had not been committed, and applies them.

RE: Importing databases with foreign key's

shailya2001 — Tue, 15 Apr 2008 16:44:10 GMT

I am looking for a MYSQL DBA for my project. pleae help me guy to locate one. Thanks.

RE: Looking for MYSQL DBA

shailya2001 — Tue, 15 Apr 2008 16:41:36 GMT

hi I may have one

Looking for MYSQL DBA

shailya2001 — Tue, 15 Apr 2008 16:35:13 GMT

Hi Guys,

I am looking for MYSQL DBA, to solver some issues with my server. please help me. Thanks