Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    How do I execute a MySQL script from command line on an database?

    I'm guessing something like this?

    >mysql database_name < script.sql

    I know that's how you restore a dump. Is it the same if I want to execute an update script?

  2.  

  3. #2
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    That should do it, yes.

  4. #3
    Member
    Join Date
    Apr 2007
    Posts
    96
    Member #
    15165
    Quote Originally Posted by transio
    How do I execute a MySQL script from command line on an database?
    Something that use to throw me off way, way back was

    /usr/bin/mysql -u root -p somedb < commandlist.sql

    The -p switch just means to interactively ask for a password.
    Not that the password is somedb.

    So now a days, I would use

    /usr/bin/mysql --user=root --password --database=somedb < commandlist.sql

    Or you could even put a sql comman in your text file at the top.

    USE somedb;

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Thanks guys!

  6. #5
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Alternatively, and probably faster since it bypasses the shell:
    List of all MySQL commands:
    Note that all text commands must be first on line and end with ';'
    ? (\?) Synonym for `help'.
    clear (\c) Clear command.
    connect (\r) Reconnect to the server. Optional arguments are db and host.
    delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as new delimiter.
    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.
    help (\h) Display this help.
    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.
    prompt (\R) Change your mysql prompt.
    quit (\q) Quit mysql.
    rehash (\#) Rebuild completion hash.
    source (\.) Execute an SQL script file. Takes a file name as an argument.
    status (\s) Get status information from the server.
    system (\!) Execute a system shell command.
    tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
    use (\u) Use another database. Takes database name as argument.
    charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
    warnings (\W) Show warnings after every statement.
    nowarning (\w) Don't show warnings after every statement.
    Pointless complaining: the psql command interpreter is much more versatile.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  7. #6
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Eh. With the overhead caused by actually executing the SQL, the overhead induced by reading in the file and piping it to the mysql commands' stdin should be negligible anyway.

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Quote Originally Posted by Shadowfiend
    Eh. With the overhead caused by actually executing the SQL, the overhead induced by reading in the file and piping it to the mysql commands' stdin should be negligible anyway.
    Especially considering it involves altering and updating multi-million-record tables. The entire script takes about 2 hours to execute.

  9. #8
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    Lol. Yes, then, I think we can categorically say that the file redirection will be the least of the computer's worries :-D


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 10:32 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com