Re: Migrate HUGE Database
You misunderstand. I realize that putting the password on the command line is insecure in the presence of ps auxwww and Process Explorer and such. I'm kvetching about how mysql and mysqldump parse their arguments, that * -uUSERNAME and -pPASSWORD are parsed similarly * -u USERNAME and -p UNRELATED_ARGUMENT are not It's quite simple; -p, --password takes an optional argument which it will only look for if a) the short form is immediately followed by a non-space char b) the long form is followed by an equals sign In all other cases, parsing of the command line continues. If the next item starts with '-', it's a switch. If not, switch parsing ends and mysql starts slurping normal arguments. -pPASSWORD -p SOMETHINGELSE --password=PASSWORD --password SOMETHINGELSE Tim: sorry about the premature post.. gmail keyboard flub.. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Wed, 12 Mar 2008, Michael Dykman [EMAIL PROTECTED] wrote: You misunderstand. I realize that putting the password on the command line is insecure in the presence of ps auxwww and Process Explorer and such. I'm kvetching about how mysql and mysqldump parse their arguments, that * -uUSERNAME and -pPASSWORD are parsed similarly * -u USERNAME and -p UNRELATED_ARGUMENT are not It's quite simple; -p, --password takes an optional argument which it will only look for if a) the short form is immediately followed by a non-space char I thought I'd made it clear above that I understand the rule. I am complaining (in a forum where no maintainer is likely to be present, unfortunately) that it was a bad design that made -p be parsed differently from -u and likely all the other switches. A wise consistency is useful, because users have to remember fewer rules. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Wed, Mar 12, 2008 at 3:26 PM, Tim McDaniel [EMAIL PROTECTED] wrote:
On Wed, 12 Mar 2008, Michael Dykman [EMAIL PROTECTED] wrote:
It's quite simple; -p, --password takes an optional argument which
it will only look for if
a) the short form is immediately followed by a non-space char
I thought I'd made it clear above that I understand the rule. I am
complaining (in a forum where no maintainer is likely to be present,
unfortunately) that it was a bad design that made -p be parsed
differently from -u and likely all the other switches. A wise
consistency is useful, because users have to remember fewer rules.
It's not unheard of for those who maintain the code and
information to subscribe to the general lists as well. I'm a PHP/docs
maintainer and am very active on the PHP-General list, for example.
The same applies for several others, and several other projects (I'm
also on with Mozilla, Mandriva, KDE, etc.).
My guess - and keep in mind, it's *strictly* a guess - is that -p
differs from -u and the like in case your password begins with a
space, which is valid for MySQL passwords. If you still wanted to
pass a space-starting password from the command line, you can pass -p'
123' as the flag, but then you can't have single quotes. You will
also need to escape some special characters passed in from the command
line as a password this way. Exclamation points (!), question
marks(?), hashmarks (#), adz/amphora (@), and some others will work,
but if you have parentheses, quotes, backticks, or some other
characters, they won't.
An off-the-wall guess could be that everyone expects to have a
space between the parameter value. If you have a flag that requires
no data between itself and the value, it does break the pattern -
which then forces someone who may not want to read the manual, man
mysql, or check out the --help data prior to jumping in head-first to
discover that there are two ways of performing the action. And with
that forced lesson, hopefully they'll learn that the prompt is the
safer way of doing this. Again, though, that's the off-the-wall
possibility. A Learn or Die ultimatum.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
How very inconsistent and obnoxious. But yet far more secure. FWIW, if you're transferring between machines you can gzip the output of mysqldump to compress it, resulting in far less transfer time. Eg. mysqldump -u username -p database_name | gzip -c dump.sql.gz IIRC -- Richard Heyes Employ me: http://www.phpguru.org/cv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: Migrate HUGE Database
Hi Terry, you should try using mysql command line tools. Aloha! Claudio Nanni -Messaggio originale- Da: Terry Babbey [mailto:[EMAIL PROTECTED] Inviato: lunedì 10 marzo 2008 19.30 A: mysql@lists.mysql.com Oggetto: Migrate HUGE Database Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293 Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Tue, 11 Mar 2008, Richard Heyes [EMAIL PROTECTED] wrote: How very inconsistent and obnoxious. But yet far more secure. You misunderstand. I realize that putting the password on the command line is insecure in the presence of ps auxwww and Process Explorer and such. I'm kvetching about how mysql and mysqldump parse their arguments, that * -uUSERNAME and -pPASSWORD are parsed similarly * -u USERNAME and -p UNRELATED_ARGUMENT are not -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrate HUGE Database
Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293
Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED] wrote:
Hello,
I have a huge database that I would like to migrate from a server
running 4.0.16 to a server running the Windows version 5.0.45. The
database is approximately 3,500,000 records. I get timeout errors using
PHPMyAdmin to export the data.
If you have shell access, do the following:
mysqldump -u username -p database_name database_name.sql
To explain:
-u username Replace 'username' with the database username.
-p This signifies that you'll use
a password (at a prompt)
database_nameThe full name of the database to dump
Redirects all output to a
file, deleting previous data, if any
database_name.sql The SQL output file, written to the
current directory.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 11:29 AM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293 First off 3.5M is not huge to many of us... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html would be the way to go. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, 10 Mar 2008 at 14:29 -0400, [EMAIL PROTECTED] confabulated: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Perhap you can use mysqldump to dump out what needs to be moved and import the resulting dump into the other server after copying the dump over. I'm sure there are other ways of doing this. - _|_ |_| | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrate HUGE Database
You can take this a step further
nohup mysqldump -hhost of Linux Machine -uusername -ppassword
--all-databases --routines --triggers | mysql -hhost of Windows Machine -A
This will pipe all the data directly to Windows machine without an intermittent
file. Even if you logout of Linux, it should keep going
-Original Message-
From: Daniel Brown [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2008 2:40 PM
To: Terry Babbey
Cc: mysql@lists.mysql.com
Subject: Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED] wrote:
Hello,
I have a huge database that I would like to migrate from a server
running 4.0.16 to a server running the Windows version 5.0.45. The
database is approximately 3,500,000 records. I get timeout errors using
PHPMyAdmin to export the data.
If you have shell access, do the following:
mysqldump -u username -p database_name database_name.sql
To explain:
-u username Replace 'username' with the database username.
-p This signifies that you'll use
a password (at a prompt)
database_nameThe full name of the database to dump
Redirects all output to a
file, deleting previous data, if any
database_name.sql The SQL output file, written to the
current directory.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: mysqldump -u username -p database_name database_name.sql To explain: -u usernameReplace 'username' with the database username. -p This signifies that you'll use a password (at a prompt) database_name The full name of the database to dump Redirects all output to a file, deleting previous data, if any I was a bit puzzled seeing -p database_name, as I was expecting that to be seen as the password. After a bit of experimentation, it turns out that (for example) -uUSERNAME is treated the same as -u USERNAME but -pPASSWORD interprets PASSWORD as the password, but -p WORD prompts for the password and uses WORD as the next argument (no relation to the password), in this case as the database name. How very inconsistent and obnoxious. Daniel, thank you for the prompt to look at this. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
Tim McDaniel wrote: I was a bit puzzled seeing -p database_name, ... How very inconsistent and obnoxious. It's best to think of -p as never taking an argument, always asking interactively. Many operating systems will let a processes access the command line parameters of another process, making it possible to get the password in the clear if you pass it to a program this way. It's nice to know that you can pass it this way if absolutely necessary, but I try not to use it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
mysqldump from the commandline. You are most likely running into php execution time limits using phpmyadmin OR you could probably just copying the underlying files, .frm,MYI and MYD I've successfully done that with myisam databases going from version 4 - 5 on tables exceeding 50M rows. Not sure about innoDB though. On Mon, Mar 10, 2008 at 1:29 PM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293 -- Help build our city at http://free-dc.myminicity.com !
RE: Migrate HUGE Database
Thanks to all for the quick replies.
Yes, the mysqldump worked perfectly. Boy do I feel like a newbie now!
If I use the method below, will that transfer the mysql admin database
too with the user information?
Thanks,
Terry
-Original Message-
From: Rolando Edwards [mailto:[EMAIL PROTECTED]
Sent: March 10, 2008 2:52 PM
To: Daniel Brown; Terry Babbey
Cc: mysql@lists.mysql.com
Subject: RE: Migrate HUGE Database
You can take this a step further
nohup mysqldump -hhost of Linux Machine -uusername -ppassword
--all-databases --routines --triggers | mysql -hhost of Windows
Machine -A
This will pipe all the data directly to Windows machine without an
intermittent file. Even if you logout of Linux, it should keep going
-Original Message-
From: Daniel Brown [mailto:[EMAIL PROTECTED]
Sent: Monday, March 10, 2008 2:40 PM
To: Terry Babbey
Cc: mysql@lists.mysql.com
Subject: Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED]
wrote:
Hello,
I have a huge database that I would like to migrate from a server
running 4.0.16 to a server running the Windows version 5.0.45. The
database is approximately 3,500,000 records. I get timeout errors
using
PHPMyAdmin to export the data.
If you have shell access, do the following:
mysqldump -u username -p database_name database_name.sql
To explain:
-u username Replace 'username' with the database
username.
-p This signifies that you'll use
a password (at a prompt)
database_nameThe full name of the database to dump
Redirects all output to a
file, deleting previous data, if any
database_name.sql The SQL output file, written to the
current directory.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 4:05 PM, Terry Babbey [EMAIL PROTECTED] wrote:
Thanks to all for the quick replies.
Yes, the mysqldump worked perfectly. Boy do I feel like a newbie now!
If I use the method below, will that transfer the mysql admin database
too with the user information?
Thanks,
Terry
nohup mysqldump -hhost of Linux Machine -uusername -ppassword
--all-databases --routines --triggers | mysql -hhost of Windows
Machine -A
Yes, the --all-databases flag recursively copies all databases and
tables, including the 'mysql' database with user and time zone
information.
--
/Dan
Daniel P. Brown
Senior Unix Geek
? while(1) { $me = $mind--; sleep(86400); } ?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: On Mon, Mar 10, 2008 at 4:05 PM, Terry Babbey [EMAIL PROTECTED] wrote: If I use the method below, will that transfer the mysql admin database too with the user information? nohup mysqldump -hhost of Linux Machine -uusername -ppassword --all-databases --routines --triggers | mysql -hhost of Windows Machine -A Yes, the --all-databases flag recursively copies all databases and tables, including the 'mysql' database with user and time zone information. To expand on that: mysqldump outputs plain SQL statements, which are readable text unless you have character data or identifiers that are UNICODE or something. (You can deduce that by noting that mysql is the normal CLI-type interface, and the pipe just feeds the output of mysqlsump into mysql as if you'd typed the CREATE TABLE, INSERT, etc. commands yourself.) So if you have any questions about what mysqldump outputs, you can feed its output into a pager program like less or more, or into a temporary file, and just look at it. One way to reduce a mountain of output would be to just mysqldump specific databases or tables. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
