Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2011
    Posts
    3

    Unanswered: Extracting a table from a MySQL serverís database to a txt file

    Hi guys. I was wondering if you could help me with scripting a batch file to extract a table from a MySQL serverís database to a txt file. I have to confess to have very little knowledge of MySQL, but have a clear idea of what I need to achieve.

    I have the relevant log in details, and have been able to log into the server and can view the database and table concerned using the front end of MySQL workbench 5.2 CE. The machine has Win XP Pro SP3 operating system. A former colleague wrote a batch file which used to copy the table and save it as a txt file somewhere, that batch has recently stopped working. The syntax goes like this:

    __________________________________________________ _____________

    C:
    cd C:\Program Files\MySQL\MySQL Workbench 5.2 CE

    mysql --user=username --password=something --host=serveraddress --port=9999 --database=databasename --execute="select * from tablename;" > nameoutput.txt

    move nameoutput.txt C:\Temp

    __________________________________________________ _____________


    I have disguised the specific names in italic. I would be most grateful if someone can have a look at this structure and tell me what need to be changed in order for it to work. I know I have correct log in and names involved since I was able to get in using the normal client.

    Any help will be greatly appreciated. Many thanks in advance!

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have a few questions:

    1. What is the next step for the file contents? In other words what are you going to do with the contents of the table dump? For example, is this being stored for reference purposes or is this going to be used somewhere else?
    2. This looks fine as a batch. Do you have a specific error message when you run this?
    3. Port 9999 is not the default port for accessing the database. Are you sure about this?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Aug 2011
    Posts
    3
    The content of the table is being stored for reference purpose, the file is going to be used in another process which is already working. So I just need to get this file dump working. The port number is not 9999, I just didn't put the exact one there. Thank you for letting me know that the structure of the batch looks fine.

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am not sure now what you were asking. Is there a problem or you were just looking for confirmation of the processes?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Aug 2011
    Posts
    3
    Sorry, you have already answered my question in your first post: the scripting is fine. Just tried to run that line and it returned with access denied, so I am going to take the issue to my vendor who looks after the server.

    Thanks again.

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think this has something to do with the username, password and the host from which you are trying to access the MySQL server.

    When you create a user in MySQL there are 3 things that determine the type of access, the username, the password and the host from which you are attempting to connect to the database.

    So for example, it is possible to have multiple usernames "arthur" all having different types of access to the MySQL server. To differentiate between each one, there is also a host. By default the host is localhost. This means that if you connect to MySQL from the same host that the MySQL server resides on it will be accepted. If on the other hand you attempt to access from a different host(machine) and that host (or wildcard %) is not defined then you will get access denied.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •