Sanjeev Sabhlok's notes on technology, hardware, gardening

Notes on MYSQL

How to access the mysql database:

Log into mysql

mysql -u [username] -p

This will ask for password. Enter the root password (not blog password!) You'll get the following prompt:

mysql>

You can exit the mysql client by executing any of the following commands: quit, exit, \q, or Ctrl-D.

List the databases

You can list all mysql databases by logging in as an administrator and typing

show databases;

Use a particular database, say mysqlK

Pick the database you want to investigate further:

mysql> use mysqlK;

Find out its tables:

mysql> show tables;

Other handy list of commands (not needed for my purposes) – Also here.

Backing Up Your MySQL Database

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.

mysqldump

 

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

mysql -u [username] -p [databasename] < [databasedump.sql – i.e. file to be restored]

 

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.

other commands

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:

mysql>SHOW VARIABLES;

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.

Print Friendly, PDF & Email

sabhlok

View more posts from this author

Leave a Reply

Your email address will not be published. Required fields are marked *