Database migration from shell (help please :) )


#1

I am trying to migrate an older dreamhost/msql database to a new db. I can connect through shell just fine, have ftp’d my database (too large to migrate through phpmyadmin) and I execute the command given in the migrate instructions (think I did it right) and get the following…but the db is still empty:

mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2
Copyright © 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
-?, --help Display this help and exit.
-I, --help Synonym for -?
–auto-rehash Enable automatic rehashing. One doesn’t need to use
’rehash’ to get table and field completion, but startup
and reconnecting may take a longer time. Disable with
–disable-auto-rehash.
-A, --no-auto-rehash
No automatic rehashing. One has to use ‘rehash’ to get
table and field completion. This gives a quicker start of
mysql and disables rehashing on reconnect. WARNING:
options deprecated; use --disable-auto-rehash instead.
-B, --batch Don’t use history file. Disable interactive behavior.
(Enables --silent)
–character-sets-dir=name
Directory where character sets are.
–default-character-set=name
Set the default character set.
-C, --compress Use compression in server/client protocol.
-#, --debug[=#] This is a non-debug version. Catch this and exit
-D, --database=name Database to use.
–delimiter=name Delimiter to be used.
-e, --execute=name Execute command and quit. (Disables --force and history
file)
-E, --vertical Print the output of a query (rows) vertically.
-f, --force Continue even if we get an sql error.
-G, --named-commands
Enable named commands. Named commands mean this program’s
internal commands; see mysql> help . When enabled, the
named commands can be used from any line of the query,
otherwise only from the first line, before an enter.
Disable with --disable-named-commands. This option is
disabled by default.
-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 (:wink: Since version 10.9 the client now
starts with this option ENABLED by default! Disable with
’-G’. Long format commands still work from the first
line. WARNING: option deprecated; use
–disable-named-commands instead.
-i, --ignore-spaces Ignore space after function names.
–local-infile Enable/disable LOAD DATA LOCAL INFILE.
-b, --no-beep Turn off beep on error.
-h, --host=name Connect to host.
-H, --html Produce HTML output.
-X, --xml Produce XML output
–line-numbers Write line numbers for errors.
-L, --skip-line-numbers
Don’t write line number for errors. WARNING: -L is
deprecated, use long version of this option instead.
-n, --unbuffered Flush buffer after each query.
–column-names Write column names in results.
-N, --skip-column-names
Don’t write column names in results. WARNING: -N is
deprecated, use long version of this options instead.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
–variable-name=value.
–sigint-ignore Ignore SIGINT (CTRL-C)
-o, --one-database Only update the default database. This is useful for
skipping updates to other database in the update log.
–pager[=name] Pager to use to display results. If you don’t supply an
option the default pager is taken from 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. Disable with --disable-pager. This
option is disabled by default.
–no-pager Disable pager and print to stdout. See interactive help
(\h) also. WARNING: option deprecated; use
–disable-pager instead.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it’s asked from the tty. WARNING: This is
insecure as the password is visible for anyone through
/proc for a short time.
-P, --port=# Port number to use for connection.
–prompt=name Set the mysql prompt to this value.
–protocol=name The protocol of connection (tcp,socket,pipe,memory).
-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 fields without conversion. Used with --batch.
–reconnect Reconnect if the connection is lost. Disable with
–disable-reconnect. This option is enabled by default.
-s, --silent Be more silent. Print results with a tab as separator,
each row on new line.
-S, --socket=name Socket file to use for connection.
–ssl Enable SSL for connection (automatically enabled with
other flags). Disable with --skip-ssl.
–ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
–ssl).
–ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
–ssl-cert=name X509 cert in PEM format (implies --ssl).
–ssl-cipher=name SSL cipher to use (implies --ssl).
–ssl-key=name X509 key in PEM format (implies --ssl).
–ssl-verify-server-cert
Verify server’s “Common Name” in its cert against
hostname used when connecting. This option is disabled by
default.
-t, --table Output in table format.
-T, --debug-info Print some debug info at exit.
–tee=name Append everything into outfile. See interactive help (\h)
also. Does not work in batch mode. Disable with
–disable-tee. This option is disabled by default.
–no-tee Disable outfile. See interactive help (\h) also. WARNING:
option deprecated; use --disable-tee instead
-u, --user=name User for login if not current user.
-U, --safe-updates Only allow UPDATE and DELETE that uses keys.
-U, --i-am-a-dummy Synonym for option --safe-updates, -U.
-v, --verbose Write more. (-v -v -v gives the table output format).
-V, --version Output version information and exit.
-w, --wait Wait and retry if connection is down.
–connect_timeout=# Number of seconds before connection timeout.
–max_allowed_packet=#
Max packet length to send to, or receive from server
–net_buffer_length=#
Buffer for TCP/IP and socket communication
–select_limit=# Automatic limit for SELECT when using --safe-updates
–max_join_size=# Automatic limit for rows in a join when using
–safe-updates
–secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol
–show-warnings Show warnings after every statement.

Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
–print-defaults Print the program argument list and exit
–no-defaults Don’t read default options from any options file
–defaults-file=# Only read default options from the given file #
–defaults-extra-file=# Read this file after the global files are read

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)


auto-rehash TRUE
character-sets-dir (No default value)
default-character-set latin1
compress FALSE
database (No default value)
delimiter ;
vertical FALSE
force FALSE
named-commands FALSE
local-infile FALSE
no-beep FALSE
host (my hostname)(edited out)
html FALSE
xml FALSE
line-numbers TRUE
unbuffered FALSE
column-names TRUE
sigint-ignore FALSE
port 3306
prompt mysql>
quick FALSE
raw FALSE
reconnect FALSE
socket /var/run/mysqld/mysqld.sock
ssl FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-verify-server-cert FALSE
table FALSE
debug-info FALSE
user musedem
safe-updates FALSE
i-am-a-dummy FALSE
connect_timeout 0
max_allowed_packet 16777216
net_buffer_length 16384
select_limit 1000
max_join_size 1000000
secure-auth FALSE
show-warnings FALSE
[caelum]$
[hr]
My favorite line in the above is the “i am a dummy” because I certainly feel like a dummy…lol. New to the whole migration thing, but can usually follow directions and I know how to use google…

Thanks for any help you can offer.


#2

Hmmm…well…I’ve still not had any luck, but will keep trying. Went on and broke the 232mb sql file into smaller pieces and importing through phpmyadmin, but that is obviously taking a lot of time…at least it is a start.


#3

What you posted yesterday was the help message mysql prints to the terminal when it cannot recognize the options you provided at startup

How are you “migrating” this database?

I use mysqldump to export a production database, and then mysql to import it to a development database several times per month. It takes less than 5 minutes to import a 126MB dump