Blender, graphic, software, open source, Linux LibreOffice, open source, openoffice Gimp, graphic, software, open source, Linux kernel, Linux, software, open source Linux, distributions, Ubuntu, Linux Mint, Fedora, Mandriva Jamin, gpl, library, open source matroska, multimedia, container, linux pcman, file manager, linux LuninuX, distribition, Linux, open source Linux, infographic, history

Tuesday, May 12, 2009

MySQL Client-Side Scripts and Utilities

Overview of the Client-Side Scripts and Utilities
All MySQL clients that communicate with the server using the mysqlclient library use
the following environment variables:
Name Description
MYSQL_UNIX_PORT The default socket; used for connections to localhost
MYSQL_TCP_PORT The default TCP/IP port
MYSQL_PWD The default password
MYSQL_DEBUG Debug-trace options when debugging
TMPDIR The directory where temporary tables/files are created
Use of MYSQL_PWD is insecure. See Section 4.2.7 [Connecting], page 183.
The ‘mysql’ client uses the file named in the MYSQL_HISTFILE environment variable to save
the command-line history. The default value for the history file is ‘$HOME/.mysql_history’,
where $HOME is the value of the HOME environment variable. See Appendix F [Environment
variables], page 695.
All MySQL programs take many di erent options. However, every MySQL program pro-
vides a --help option that you can use to get a full description of the program’s di erent
options. For example, try mysql --help.
You can override default options for all standard client programs with an option file. Sec-
tion 4.1.2 [Option files], page 168.
The list below brie y describes the MySQL programs:
myisamchk
Utility to describe, check, optimise, and repair MySQL tables. Because
myisamchk has many functions, it is described in its own chapter. See Chapter 4
[MySQL Database Administration], page 164.
make_binary_distribution
Makes a binary release of a compiled MySQL. This could be sent by FTP to
‘/pub/mysql/Incoming’ on support.mysql.com for the convenience of other
MySQL users.
msql2mysql
A shell script that converts mSQL programs to MySQL. It doesn’t handle all
cases, but it gives a good start when converting.



mysqlaccess
A script that checks the access privileges for a host, user, and database combi-
nation.
mysqladmin
Utility for performing administrative operations, such as creating or dropping
databases, reloading the grant tables, ushing tables to disk, and reopening
log files. mysqladmin can also be used to retrieve version, process, and status
information from the server.
mysqlbug The MySQL bug report script. This script should always be used when filing a
bug report to the MySQL list.
mysqld The SQL daemon. This should always be running.
mysqldump
Dumps a MySQL database into a file as SQL statements or as tab-separated
text files. Enhanced freeware originally by Igor Romanenko.
mysqlimport
Imports text files into their respective tables using LOAD DATA INFILE.
Displays information about databases, tables, columns, and indexes.
mysql_install_db
Creates the MySQL grant tables with default privileges. This is usually exe-
cuted only once, when first installing MySQL on a system.
replace A utility program that is used by msql2mysql, but that has more general ap-
plicability as well. replace changes strings in place in files or on the standard
input. Uses a finite state machine to match longer strings first. Can be used to
swap strings. For example, this command swaps a and b in the given files:
shell> replace a b b a -- file1 file2 ...
4.8.2 The Command-line Tool
mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and
non-interactive use. When used interactively, query results are presented in an ASCII-table
format. When used non-interactively (for example, as a filter), the result is presented in
tab-separated format. (The output format can be changed using command-line options.)
You can run scripts simply like this:
shell> mysql database <> output.tab
If you have problems due to insu cient memory in the client, use the --quick option! This
forces mysql to use mysql_use_result() rather than mysql_store_result() to retrieve
the result set.
Using mysql is very easy. Just start it as follows: mysql database or mysql --user=user_
name --password=your_password database. Type a SQL statement, end it with ‘;’, ‘\g’,
or ‘\G’ and press Enter.

mysql supports the following options:
-?, --help
Display this help and exit.
-A, --no-auto-rehash
No automatic rehashing. One has to use ’rehash’ to get table and field comple-
tion. This gives a quicker start of mysql.
-B, --batch
Print results with a tab as separator, each row on a new line. Doesn’t use
history file.
--character-sets-dir=...
Directory where character sets are located.
-C, --compress
Use compression in server/client protocol.
-#, --debug[=...]
Debug log. Default is ’d:t:o,/tmp/mysql.trace’.
-D, --database=...
Database to use. This is mainly useful in the my.cnf file.
--default-character-set=...
Set the default character set.
-e, --execute=...
Execute command and quit. (Output like with –batch)
-E, --vertical
Print the output of a query (rows) vertically. Without this option you can also
force this output by ending your statements with \G.
-f, --force
Continue even if we get a SQL error.
-g, --no-named-commands
Named commands are disabled. Use \* form only, or use named commands
only in the beginning of a line ending with a semicolon (‘;’). Since Version
10.9, the client now starts with this option enabled by default! With the -g
option, long format commands will still work from the first line, however.
-G, --enable-named-commands
Named commands are enabled. Long format commands are allowed as well as
shortened \* commands.
-i, --ignore-space
Ignore space after function names.
-h, --host=...
Connect to the given host.
-H, --html
Produce HTML output.


-L, --skip-line-numbers
Don’t write line number for errors. Useful when one wants to compare result
files that includes error messages
--no-pager
Disable pager and print to stdout. See interactive help (\h) also.
--no-tee Disable outfile. See interactive help (\h) also.
-n, --unbuffered
Flush bu er after each query.
-N, --skip-column-names
Don’t write column names in results.
-O, --set-variable var=option
Give a variable a value. --help lists variables.
-o, --one-database
Only update the default database. This is useful for skipping updates to other
database in the update log.
--pager[=...]
Output type. Default is your ENV variable PAGER. Valid pagers are less, more,
cat [> filename], etc. See interactive help (\h) also. This option does not work
in batch mode. Pager works only in Unix.
-p[password], --password[=...]
Password to use when connecting to server. If a password is not given on the
command line, you will be prompted for it. Note that if you use the short form
-p you can’t have a space between the option and the password.
-P --port=...
TCP/IP port number to use for connection.
-q, --quick
Don’t cache result, print it row-by-row. This may slow down the server if the
output is suspended. Doesn’t use history file.
-r, --raw Write column values without escape conversion. Used with --batch
-s, --silent
Be more silent.
-S --socket=...
Socket file to use for connection.
-t --table
Output in table format. This is default in non-batch mode.
-T, --debug-info
Print some debug information at exit.
--tee=...
Append everything into outfile. See interactive help (\h) also. Does not work
in batch mode.

-u, --user=#
User for login if not current user.
-U, --safe-updates[=#], --i-am-a-dummy[=#]
Only allow UPDATE and DELETE that uses keys. See below for more information
about this option. You can reset this option if you have it in your my.cnf file
by using --safe-updates=0.
-v, --verbose
More verbose output (-v -v -v gives the table output format).
-V, --version
Output version information and exit.
-w, --wait
Wait and retry if connection is down instead of aborting.
You can also set the following variables with -O or --set-variable:
Variable Name Default Description
connect timeout 0 Number of seconds before timeout
connection.
max allowed packet 16777216 Max packetlength to send/receive from to
server
net bu er length 16384 Bu er for TCP/IP and socket
communication
select limit 1000 Automatic limit for SELECT when using
–i-am-a-dummy
max join size 1000000 Automatic limit for rows in a join when us-
ing –i-am-a-dummy.
If you type ’help’ on the command line, mysql will print out the commands that it supports:
mysql> help
MySQL commands:
help (\h) Display this text.
? (\h) Synonym for ‘help’.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don’t write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute a SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.
From the above, pager only works in Unix.
The status command gives you some information about the connection and the server you
are using. If you are running in the --safe-updates mode, status will also print the
values for the mysql variables that a ect your queries.
A useful startup option for beginners (introduced in MySQL Version 3.23.11) is --safe-
updates (or --i-am-a-dummy for users that has at some time done a DELETE FROM table_
name but forgot the WHERE clause). When using this option, mysql sends the following
command to the MySQL server when opening the connection:
SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=#select_limit#,
SQL_MAX_JOIN_SIZE=#max_join_size#"
where #select_limit# and #max_join_size# are variables that can be set from the mysql
command line. See Section 5.5.6 [SET OPTION], page 334.
The e ect of the above is:
• You are not allowed to do an UPDATE or DELETE statement if you don’t have a key
constraint in the WHERE part. One can, however, force an UPDATE/DELETE by using
LIMIT:
UPDATE table_name SET not_key_column=# WHERE not_key_column=# LIMIT 1;
• All big results are automatically limited to #select_limit# rows.
• SELECT’s that will probably need to examine more than #max_join_size row combi-
nations will be aborted.

Some useful hints about the mysql client:
Some data is much more readable when displayed vertically, instead of the usual horizontal
box type output. For example longer text, which includes new lines, is often much easier
to be read with vertical output.
mysql> select * from mails where length(txt) <>
sbj: UTF-8
txt: >>>>> "Thimble" == Thimble Smith writes:
Thimble> Hi. I think this is a good idea. Is anyone familiar with UTF-8
Thimble> or Unicode? Otherwise I’ll put this on my TODO list and see what
Thimble> happens.
Yes, please do that.
Regards,
Monty
file: inbox-jani-1

Vacation Home Rentals
hash: 190402944
1 row in set (0.09 sec)
• For logging, you can use the tee option. The tee can be started with option --tee=...,
or from the command line interactively with command tee. All the data displayed on
the screen will also be appended into a given file. This can be very useful for debugging
purposes also. The tee can be disabled from the command line with command notee.
Executing tee again starts logging again. Without a parameter the previous file will
be used. Note that tee will ush the results into the file after each command, just
before the command line appears again waiting for the next command.
• Browsing, or searching the results in the interactive mode in Unix less, more, or any
other similar program, is now possible with option --pager[=...]. Without argument,
mysql client will look for environment variable PAGER and set pager to that. pager
can be started from the interactive command line with command pager and disabled
with command nopager.

The command takes an argument optionally and the pager
will be set to that. Command pager can be called without an argument, but this
requires that the option --pager was used, or the pager will default to stdout. pager
works only in Unix, since it uses the popen() function, which doesn’t exist in Windows.
In Windows, the tee option can be used instead, although it may not be as handy as
pager can be in some situations.
• A few tips about pager: You can use it to write to a file:
mysql> pager cat > /tmp/log.txt
and the results will only go to a file. You can also pass any options for the programs
that you want to use with the pager:
mysql> pager less -n -i -S


From the above do note the option ’-S’. You may find it very useful when browsing the
results; try the option with horizontal output (end commands with ’\g’, or ’;’) and with
vertical output (end commands with ’\G’). Sometimes a very wide result set is hard to
be read from the screen, with option -S to less you can browse the results within the
interactive less from left to right, preventing lines longer than your screen from being
continued to the next line. This can make the result set much more readable. You can
swith the mode between on and o within the interactive less with ’-S’. See the ’h’ for
more help about less.
• Last (unless you already understood this from the above examples ;) you can combine
very complex ways to handle the results, for example the following would send the
results to two files in two di erent directories, on two di erent hard-disks mounted on
/dr1 and /dr2, yet let the results still be seen on the screen via less:
mysql> pager cat | tee /dr1/tmp/res.txt | tee /dr2/tmp/res2.txt | less -n -i -S
• You can also combine the two functions above; have the tee enabled, pager set to
’less’ and you will be able to browse the results in unix ’less’ and still have everything
appended into a file the same time. The di erence between Unix tee used with the
pager and the mysql client in-built tee, is that the in-built tee works even if you don’t
have the Unix tee available. The in-built tee also logs everything that is printed on
the screen, where the Unix tee used with pager doesn’t log quite that much. Last,
but not least, the interactive tee is more handy to switch on and o , when you want
to log something into a file, but want to be able to turn the feature o sometimes.

Mysqladmin, Administrating a MySQL Server
A utility for performing administrative operations. The syntax is:
shell> mysqladmin [OPTIONS] command [command-option] command ...
You can get a list of the options your version of mysqladmin supports by executing
mysqladmin --help.
The current mysqladmin supports the following commands:
create databasename
Create a new database.
drop databasename
Delete a database and all its tables.
extended-status
Gives an extended status message from the server.
flush-hosts
Flush all cached hosts.
flush-logs
Flush all logs.
flush-tables
Flush all tables.
flush-privileges
Reload grant tables (same as reload).
kill id,id,...
Kill mysql threads.
password Set a new password. Change old password to new-password.
ping Check if mysqld is alive.
processlist
Show list of active threads in server.
reload Reload grant tables.
refresh Flush all tables and close and open logfiles.
shutdown Take server down.
slave-start
Start slave replication thread.
slave-stop
Stop slave replication thread.
status Gives a short status message from the server.
variables
Prints variables available.

version Get version info from server.
All commands can be shortened to their unique prefix. For example:
shell> mysqladmin proc stat
+----+-------+-----------+----+-------------+------+-------+------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+-----------+----+-------------+------+-------+------+
| 6 | monty | localhost | | Processlist | 0 | | |
+----+-------+-----------+----+-------------+------+-------+------+
Uptime: 10077 Threads: 1 Questions: 9 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 2 Memory in use: 1092K Max memory used: 1116K
The mysqladmin status command result has the following columns:
Uptime
Number of seconds the MySQL server has been up.
Threads
Number of active threads (clients).
Questions Number of questions from clients since mysqld was started.
Slow queries Queries that have taken more than long_query_time sec-
onds. See Section 4.9.5 [Slow query log], page 284.
Opens
How many tables mysqld has opened.
Flush tables Number of flush ..., refresh, and reload commands.
Open tables Number of tables that are open now.
Memory in use Memory allocated directly by the mysqld code (only available
when MySQL is compiled with –with-debug=full).
Max memory used Maximum memory allocated directly by the mysqld code
(only available when MySQL is compiled with –with-
debug=full).

If you do myslqadmin shutdown on a socket (in other words, on a the computer where
mysqld is running), mysqladmin will wait until the MySQL pid-file is removed to ensure
that the mysqld server has stopped properly.

Using mysqlcheck for Table Maintenance and Crash Recovery
Since MySQL version 3.23.38 you will be able to use a new checking and repairing tool for
MyISAM tables. The di erence to myisamchk is that mysqlcheck should be used when the
mysqld server is running, where as myisamchk should be used when it is not. The benefit
is that you no longer have to take the server down for checking or repairing your tables.
mysqlcheck uses MySQL server commands CHECK, REPAIR, ANALYZE and OPTIMIZE in a
convenient way for the user.
There are three alternative ways to invoke mysqlcheck:
shell> mysqlcheck [OPTIONS] database [tables]
shell> mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [OPTIONS] --all-databases
So it can be used in a similar way as mysqldump when it comes to what databases and
tables you want to choose.
mysqlcheck does have a special feature compared to the other clients; the default behavior,
checking tables (-c), can be changed by renaming the binary. So if you want to have a tool
that repairs tables by default, you should just copy mysqlcheck to your harddrive with a
new name, mysqlrepair, or alternatively make a symbolic link to mysqlrepair and name
the symbolic link as mysqlrepair. If you invoke mysqlrepair now, it will repair tables by
default.
The names that you can use to change mysqlcheck default behavior are here:
mysqlrepair: The default option will be -r
mysqlanalyze: The default option will be -a
mysqloptimize: The default option will be -o
The options available for mysqlcheck are listed here, please check what your version sup-
ports with mysqlcheck --help.
-A, --all-databases
Check all the databases. This will be same as –databases with all databases
selected
-1, --all-in-1
Instead of making one query for each table, execute all queries in 1 query
separately for each database. Table names will be in a comma separated list.
-a, --analyze
Analyse given tables.
--auto-repair
If a checked table is corrupted, automatically fix it. Repairing will be done
after all tables have been checked, if corrupted ones were found.
-#, --debug=...
Output debug log. Often this is ’d:t:o,filename’
--character-sets-dir=...
Directory where character sets are
-c, --check
Check table for errors
-C, --check-only-changed
Check only tables that have changed since last check or haven’t been closed
properly.
--compress
Use compression in server/client protocol.
-?, --help
Display this help message and exit.
-B, --databases
To check several databases. Note the di erence in usage; In this case no tables
are given. All name arguments are regarded as database names.
--default-character-set=...
Set the default character set
-F, --fast
Check only tables that hasn’t been closed properly
-f, --force
Continue even if we get an sql-error.
-e, --extended
If you are using this option with CHECK TABLE, it will ensure that the table
is 100 percent consistent, but will take a long time.
If you are using this option with REPAIR TABLE, it will run an extended
repair on the table, which may not only take a long time to execute, but may
produce a lot of garbage rows also!
-h, --host=...
Connect to host.
-m, --medium-check
Faster than extended-check, but only finds 99.99 percent of all errors. Should
be good enough for most cases.
-o, --optimize
Optimise table
-p, --password[=...]
Password to use when connecting to server. If password is not given it’s solicited
on the tty.
-P, --port=...
Port number to use for connection.
-q, --quick
If you are using this option with CHECK TABLE, it prevents the check from
scanning the rows to check for wrong links. This is the fastest check.
If you are using this option with REPAIR TABLE, it will try to repair only the
index tree. This is the fastest repair method for a table.
-r, --repair
Can fix almost anything except unique keys that aren’t unique.
-s, --silent
Print only error messages.
-S, --socket=...
Socket file to use for connection.
--tables Overrides option –databases (-B).
-u, --user=#
User for login if not current user.
-v, --verbose
Print info about the various stages.
-V, --version
Output version information and exit.


Mysqldump, Dumping Table Structure and Data
Utility to dump a database or a collection of database for backup or for transferring the
data to another SQL server (not necessarily a MySQL server). The dump will contain SQL
statements to create the table and/or populate the table.
If you are doing a backup on the server, you should consider using the mysqlhotcopy
instead. See Section 4.8.6 [mysqlhotcopy], page 276.
shell> mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
If you don’t give any tables or use the --databases or --all-databases, the whole
database(s) will be dumped.
You can get a list of the options your version of mysqldump supports by executing mysqldump
--help.
Note that if you run mysqldump without --quick or --opt, mysqldump will load the whole
result set into memory before dumping the result. This will probably be a problem if you
are dumping a big database.
Note that if you are using a new copy of the mysqldump program and you are going to do
a dump that will be read into a very old MySQL server, you should not use the --opt or
-e options.
mysqldump supports the following options:
--add-locks
Add LOCK TABLES before and UNLOCK TABLE after each table dump. (To get
faster inserts into MySQL.)
--add-drop-table
Add a drop table before each create statement.
-A, --all-databases
Dump all the databases. This will be same as --databases with all databases
selected.
-a, --all Include all MySQL-specific create options.
--allow-keywords
Allow creation of column names that are keywords. This works by prefixing
each column name with the table name.
-c, --complete-insert
Use complete insert statements (with column names).
-C, --compress
Compress all information between the client and the server if both support
compression.
-B, --databases
To dump several databases. Note the di erence in usage. In this case no tables
are given. All name arguments are regarded as database names. USE db_name;
will be included in the output before each new database.
--delayed
Insert rows with the INSERT DELAYED command.
-e, --extended-insert
Use the new multiline INSERT syntax. (Gives more compact and faster inserts
statements.)
-#, --debug[=option_string]
Trace usage of the program (for debugging).
--help Display a help message and exit.
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
These options are used with the -T option and have the same meaning as the
corresponding clauses for LOAD DATA INFILE.
-F, --flush-logs
Flush log file in the MySQL server before starting the dump.
-f, --force,
Continue even if we get a SQL error during a table dump.
-h, --host=..
Dump data from the MySQL server on the named host. The default host is
localhost.
-l, --lock-tables.
Lock all tables before starting the dump. The tables are locked with READ
LOCAL to allow concurrent inserts in the case of MyISAM tables.
-n, --no-create-db
’CREATE DATABASE /*!32312 IF NOT EXISTS*/ db name;’ will not be
put in the output. The above line will be added otherwise, if –databases or
–all-databases option was given.
-t, --no-create-info
Don’t write table creation information (the CREATE TABLE statement).
-d, --no-data
Don’t write any row information for the table. This is very useful if you just
want to get a dump of the structure for a table!
--opt Same as --quick --add-drop-table --add-locks --extended-insert --
lock-tables. Should give you the fastest possible dump for reading into a
MySQL server.
-pyour_pass, --password[=your_pass]
The password to use when connecting to the server. If you specify no
‘=your_pass’ part, mysqldump you will be prompted for a password.
-P port_num, --port=port_num
The TCP/IP port number to use for connecting to a host. (This is used for
connections to hosts other than localhost, for which Unix sockets are used.)
-q, --quick
Don’t bu er query, dump directly to stdout. Uses mysql_use_result() to do
this.
-r, --result-file=...
Direct output to a given file. This option should be used in MSDOS, because
it prevents new line ’\n’ from being converted to ’\n\r’ (new line + carriage
return).
-S /path/to/socket, --socket=/path/to/socket
The socket file to use when connecting to localhost (which is the default host).
--tables Overrides option –databases (-B).
-T, --tab=path-to-some-directory
Creates a table_name.sql file, that contains the SQL CREATE commands,
and a table_name.txt file, that contains the data, for each give table. NOTE:
This only works if mysqldump is run on the same machine as the mysqld daemon.
The format of the .txt file is made according to the --fields-xxx and --
lines--xxx options.
-u user_name, --user=user_name
The MySQL user name to use when connecting to the server. The default value
is your Unix login name.
-O var=option, --set-variable var=option
Set the value of a variable. The possible variables are listed below.
-v, --verbose
Verbose mode. Print out more information on what the program does.
-V, --version

Print version information and exit.
-w, --where=’where-condition’
Dump only selected records. Note that quotes are mandatory:
"--where=user=’jimf’" "-wuserid>1" "-wuserid<1" net_buffer_length="#,"> backup-file.sql

mysql database < host="remote-host"> my_databases.sql
If all the databases are wanted, one can use:
mysqldump --all-databases > all_databases.sql

Mysqlhotcopy, Copying MySQL Databases and Tables
mysqlhotcopy is a perl script that uses LOCK TABLES, FLUSH TABLES and cp or scp to quickly
make a backup of a database. It’s the fastest way to make a backup of the database, of
single tables but it can only be run on the same machine where the database directories
are.
mysqlhotcopy db_name [/path/to/new_directory]
mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
mysqlhotcopy db_name./regex/
mysqlhotcopy supports the following options:
-?, --help
Display a help screen and exit
-u, --user=#
User for database login
-p, --password=#
Password to use when connecting to server
-P, --port=#
Port to use when connecting to local server
-S, --socket=#
Socket to use when connecting to local server
--allowold
Don’t abort if target already exists (rename it old)
--keepold
Don’t delete previous (now renamed) target when done
--noindices
Don’t include full index files in copy to make the backup smaller and faster The
indexes can later be reconstructed with myisamchk -rq..

--method=#
Method for copy (cp or scp).
-q, --quiet
Be silent except for errors
--debug Enable debug
-n, --dryrun
Report actions without doing them
--regexp=#
Copy all databases with names matching regexp
--suffix=#
Su x for names of copied databases
--checkpoint=#
Insert checkpoint entry into specified db.table
--flushlog
Flush logs once all tables are locked.
--tmpdir=#
Temporary directory (instead of /tmp).

You can use perldoc mysqlhotcopy to get a more complete documentation for mysqlhotcopy.
mysqlhotcopy reads the groups [client] and [mysqlhotcopy] from the option files.
To be able to execute mysqlhotcopy you need write access to the backup directory, SELECT
privilege to the tables you are about to copy and the MySQL Reload privilege (to be able
to execute FLUSH TABLES).

Mysqlimport, Importing Data from Text Files
mysqlimport provides a command-line interface to the LOAD DATA INFILE SQL statement.
Most options to mysqlimport correspond directly to the same options to LOAD DATA INFILE.

mysqlimport is invoked like this:
shell> mysqlimport [options] database textfile1 [textfile2....]
For each text file named on the command line, mysqlimport strips any extension from the
filename and uses the result to determine which table to import the file’s contents into. For
example, files named ‘patient.txt’, ‘patient.text’, and ‘patient’ would all be imported
into a table named patient.
mysqlimport supports the following options:
-c, --columns=...
This option takes a comma-separated list of field names as an argument. The
field list is used to create a proper LOAD DATA INFILE command, which is then
passed to MySQL. See Section 6.4.9 [LOAD DATA], page 414.
-C, --compress
Compress all information between the client and the server if both support
compression.
-#, --debug[=option_string]
Trace usage of the program (for debugging).
-d, --delete
Empty the table before importing the text file.
--fields-terminated-by=...
--fields-enclosed-by=...
--fields-optionally-enclosed-by=...
--fields-escaped-by=...
--lines-terminated-by=...
These options have the same meaning as the corresponding clauses for LOAD
DATA INFILE. See Section 6.4.9 [LOAD DATA], page 414.
-f, --force
Ignore errors. For example, if a table for a text file doesn’t exist, continue
processing any remaining files. Without --force, mysqlimport exits if a table
doesn’t exist.
--help Display a help message and exit.
-h host_name, --host=host_name
Import data to the MySQL server on the named host. The default host is
localhost.
-i, --ignore
See the description for the --replace option.
-l, --lock-tables
Lock all tables for writing before processing any text files. This ensures that
all tables are synchronised on the server.
-L, --local
Read input files from the client. By default, text files are assumed to be on the
server if you connect to localhost (which is the default host).
-pyour_pass, --password[=your_pass]
The password to use when connecting to the server. If you specify no
‘=your_pass’ part, mysqlimport you will be prompted for a password.
-P port_num, --port=port_num
The TCP/IP port number to use for connecting to a host. (This is used for
connections to hosts other than localhost, for which Unix sockets are used.)
-r, --replace
The --replace and --ignore options control handling of input records that
duplicate existing records on unique key values. If you specify --replace, new
rows replace existing rows that have the same unique key value. If you specify
--ignore, input rows that duplicate an existing row on a unique key value are
skipped. If you don’t specify either option, an error occurs when a duplicate
key value is found, and the rest of the text file is ignored.


-s, --silent
Silent mode. Write output only when errors occur.
-S /path/to/socket, --socket=/path/to/socket
The socket file to use when connecting to localhost (which is the default host).
-u user_name, --user=user_name
The MySQL user name to use when connecting to the server. The default value
is your Unix login name.
-v, --verbose
Verbose mode. Print out more information what the program does.
-V, --version
Print version information and exit.
Here is a sample run using mysqlimport:
$ mysql --version
mysql Ver 9.33 Distrib 3.22.25, for pc-linux-gnu (i686)
$ uname -a
Linux xxx.com 2.2.5-15 #1 Mon Apr 19 22:21:09 EDT 1999 i586 unknown
$ mysql -e ’CREATE TABLE imptest(id INT, n VARCHAR(30))’ test
$ ed
a
100 Max Sydow
101 Count Dracula
.
w imptest.txt
32
q
$ od -c imptest.txt
0000000 1 0 0 \t M a x S y d o w \n 1 0
0000020 1 \t C o u n t D r a c u l a \n
0000040
$ mysqlimport --local test imptest.txt
test.imptest: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
$ mysql -e ’SELECT * FROM imptest’ test
+------+---------------+
| id | n |
+------+---------------+
| 100 | Max Sydow |
| 101 | Count Dracula |
+------+---------------+

Related Post


Yahoo!    Personals


123inkjets.com    - Printer Ink, Toner, & More

  • Get Paid     to Blog About the Things You Love


iPowerWeb    Web Hosting


Linux Links


No comments:

Recent Posts

Linux News

My Blog List

BlogESfera Directorio de Blogs Hispanos - Agrega tu Blog Add to Technorati Favorites BlogItalia.it - La directory italiana dei blog Il Bloggatore Find the best blogs at Blogs.com. AddThis Social Bookmark Button page counter Mi Ping en TotalPing.com