April 23, 2011
Notes on MYSQL
How to access the mysql database:
Log into mysql
Use a particular database, say mysqlK
Find out its tables:
The superior method is using the mysql_dump command. Usage is from the operating system command line, and is typically something such as
mysqldump –opt –all-databases > all.sql
This command dumps a set of all the SQL required to reconstruct the database to the file called all.sql.
Dump ALL databases for backup. Backup file is sql commands to recreate all db's.
# [mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql
Dump ONE database for backup.
# [mysql dir]/bin/mysqldump -u username -ppassword –databases databasename >/tmp/databasename.sql
A third method is using the mysqlhotcopy script.You can invoke it with mysqlhotcopy database /path/for/backup
You should start and stop the database as described earlier.
The mysqldump client is used to export existing table data, table structures, or both from the MySQL server. If requested, the exported data can include all necessary SQL statements required to re-create the dumped information. Furthermore, you can specify whether to dump one, some, or all databases found on the server, or even just specific tables in a given database.
You can invoke mysqldump using any of the following three syntax variations:
%>mysqldump [options] database [tables]
%>mysqldump [options] –databases [options] database1 [database2…]
%>mysqldump [options] –all-databases [options]
Consider a few examples. The first example dumps just the table structures of all databases found on a local server to a file named output.sql:
%>mysqldump -u root -p –all-databases –no-data > output.sql
Note that the output is being directed to a file; otherwise, the output would be sent to standard output, the screen. Also, keep in mind that the .sql extension is not required. This extension is used here merely for reasons of convenience; you can use any extension you wish. The next example dumps just the data of a single database, corporate:
%>mysqldump -u root -p –no-create-info corporate > output.sql
The final example dumps both the structure and the data of two tables located in the corporate database, including DROP TABLE statements before each CREATE statement.
This is particularly useful when you need to repeatedly re-create an existing database, because attempting to create already existing tables results in an error; thus the need for the DROP TABLE statements.
%>mysqldump -u root -p –add-drop-table corporate product staff > output.sql
Restoring Your MySQL Database
Restore database (or database table) from backup.
# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
If you need to restore your MySQL database, there are, again, a couple of approaches. If the problem is a corrupted table, you can run myisamchk with the -r (repair) option.
If you used the first method from the preceding section for backup, you can copy the data files back into the same locations in a new MySQL installation.
If you used the second method for backup, there are a couple of steps.
First, you need to run the queries in your dump file.This step reconstructs the database up to the point where you dumped that file.
Second, you need to update the database to the point stored in the binary log.You can do this by running the command
mysqlbinlog hostname-bin.[0-9]* | mysql
More information about the process of MySQL backup and recovery can be found at
the MySQL website at http://www.mysql.com.
Viewing MySQL’s Configuration Parameters
To see the present settings, you instead need to execute the mysqladmin client, like so:
%>mysqladmin -u root -p variables
Alternatively, you can log in to the mysql client and execute the following command:
Doing so produces a lengthy list of variable settings.
Useful mysql Options
Like all clients introduced in this chapter, mysql offers a number of useful options.
Many of the most important options are introduced here:
• –auto-rehash: By default, mysql creates hashes of database, table, and column names to facilitate auto-completion (you can auto-complete database, table, and column names with the Tab key). You can disable this behavior with
–no-auto-rehash. If you’d like to re-enable it, use this option. If you don’t plan to use auto-completion, consider disabling this option, which will slightly speed startup time.
• –column-names: By default, mysql includes the column names at the top of each result set. You can disable them with –no-column-names. If you’d like to re-enable this behavior, use this option anew.
• –compress, -C: Enables data compression when communicating between the client and server.
• –database=name, -D: Determines which database will be used. When using mysql interactively, you can also switch between databases as necessary with the USE command.
• –default-character-set=character_set: Sets the character set.
• –disable-tee: If you’ve enabled logging of all queries and the results with the option –tee or with the command tee, you can disable this behavior with this option.
• –execute=query, -e query: Executes a query without having to actually enter the client interface. You can execute multiple queries with this option by separating each with a semicolon. Be sure to enclose the query in quotes so that the shell does not misinterpret it as multiple arguments. For example:
%>mysql -u root -p -e "USE corporate; SELECT * from product;"
• –force, -f: When used in noninteractive mode, MySQL can read and execute queries found in a text file. By default, execution of these queries stops if an error occurs. This option causes execution to continue regardless of errors.
• –host=name, -h: Specifies the connection host.
• –html, -H: Outputs all results in HTML format. See the corresponding tip in the section “Useful mysql Tips” for more information about this option.
• –no-beep, -b: When rapidly typing and executing queries, it’s commonplace for errors to occur, resulting in the annoying beeping error. Use this option to disable the sound.
• –pager[=pagername]: Many queries produce more information than can fit on a single screen. You can tell the client to present results one page at a time by assigning a pager. Examples of valid pagers include the Unix commands more and less. Presently, this command is only valid on the Unix platform. You can also set a pager while inside the mysql client by using the \P command.
• –password, -p: Specifies the password. Note that you shouldn’t supply the password on the command line, as you might the username or host, but rather should wait for the subsequent prompt so that the password isn’t stored in plain text in your command history.
• –port=#, -P: Specifies the host connection port.
• –protocol=name: MySQL supports four connection protocols, including memory, pipe, socket, and tcp. Use this option to specify which protocol you’d like to use:
• –safe-updates, -U: Causes mysql to ignore all DELETE and UPDATE queries in which the WHERE clause is omitted. This is a particularly useful safeguard for preventing accidental mass deletions or modifications. See the section “Useful mysql Tips” for more information about this option.
• –skip-column-names: By default, mysql includes headers containing column names at the top of each result set. You can disable inclusion of these headers with this option.
• –tee=name: Causes mysql to log all commands and the resulting output to the file specified by name. This is particularly useful for debugging purposes. You can disable logging at any time while inside mysql by issuing the command notee, and can later re-enable it with the command tee. See the corresponding tip in the section “Useful mysql Tips” for more information about this option.
• –vertical, -E: Causes mysql to display all query results in a vertical format. This format is often preferable when you’re working with tables that contain several columns. See the corresponding tip in the section “Useful mysql Tips” for more information about this option.
• –xml, -X: Causes all results to be output in XML format. See the corresponding tip in the section “Useful mysql Tips” for more information about this option.