1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Convert mysql files to .sql files

Discussion in 'MySQL' started by countrydj, Nov 26, 2020.

  1. #1
    My databases are all mysql. They are stored as databse directory, and database.frm, database.MYD, database.MYI.
    I need to convert tese to .sql in order to upload to another provider.
    Can anybody tell me how to do it.
    Thankyou, John C
    SEMrush
     
    countrydj, Nov 26, 2020 IP
    SEMrush
  2. mmerlinn

    mmerlinn Prominent Member

    Messages:
    3,203
    Likes Received:
    818
    Best Answers:
    7
    Trophy Points:
    320
    #2
    If these are standard DBF files, why can't you just change the extension?
     
    mmerlinn, Nov 26, 2020 IP
  3. countrydj

    countrydj Active Member

    Messages:
    41
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #3
    I have been told by the new hoster, NAMECHEAP, that I need to upload my database as .sql.
    Since there are 3 file extensions, how can I change them all to .sql?
     
    countrydj, Nov 30, 2020 IP
  4. countrydj

    countrydj Active Member

    Messages:
    41
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #4
     
    countrydj, Nov 30, 2020 IP
  5. countrydj

    countrydj Active Member

    Messages:
    41
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    91
    #5
    Another posibility, if it is possible, is to host the website on one server and host the mysql files on another server (my own).
    Has anybody any idea if this is possible, and if so, how to do it.
    Thanks,
    John C
     
    countrydj, Nov 30, 2020 IP
  6. myreseller home

    myreseller home Greenhorn

    Messages:
    28
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    13
    #6
    To “convert it to .sql” you need the data in a running MySQL server. You’d then run the mysqldump tool to query the live server and write the data to a .sql file.
     
    myreseller home, Apr 26, 2022 IP
  7. JEET

    JEET Notable Member

    Messages:
    3,753
    Likes Received:
    491
    Best Answers:
    19
    Trophy Points:
    235
    #7
    This is an old post, but adding an answer anyways, might help someone else.

    The formats you mentioned are raw mysql files.
    That is how MYSQL stores your database on the machine.

    The .SQL format is just one single file of all of those, in plain text format.
    This file is a common used file to transfer databases from one server to another.

    To convert your mysql database into single SQL file, use the "mysql dump" tool. Its already available as executable in same bin directory where mysql is installed.

    For linux use this command to convert database to SQL format:

    mysqldump --add-drop-table -u 'Db_username_here' -p 'db_name_here' > /path_to_directory/db_name.sql

    Run that command in putty or terminal where your database is already stored.
    Change the values according to your username and database name and path where you want mysql to make the new SQL file.
    Remove the " ' " sign also
    When you will enter that command in putty or linux terminal, then it will ask database password. Enter it and hit ENTER key on keyboard.

    Depending on database size, it can take few or many minutes to create the SQL file.
    Cursor will keep blinking in an empty line during that time.
    Once done, you will be returned to command prompt.

    If you have mysql installed on windows, then use this command instead:

    full_path_to_mysql_bin\mysqldump.exe --add-drop-table -u 'Db_username_here' -p 'db_name_here' > path_to_directory\db_name.sql

    From there same thing will happen.
    It will ask password, and after that will create SQL file.

    To use database on separate machine, and website on separate, you simply need to change one line in your PHP etc code.

    Find this, or a similar line:

    $database_host = "localhost";

    The variable name could be anything else, like $dbName, or $db_name etc etc.
    That does not matters.
    The second part is what matters i.e "localhost".

    Change that to full IP of server which has database.
    Using our example, new setting will look something like this:

    $database_host = "123.222.12.47";

    Whatever IP you would use.
    Note, you cannot use IP "127.0.0.1", that is same as localhost, won't work.

    After that, open your website in browser.
    If the website server successfully connected to database server, you will see your website normally.
    If connection failed, then you will see some error message.

    One common problem in this cross server connection is that database server has refused the connection.
    Reason is that your "website server" is not authorized to make the connection.
    This is common problem in CPanel.
    To solve this, do the following.

    Login to CPanel on database server.
    Note, these settings need to be done on database server, not website server.

    Login to cpanel.
    Look for "Remote MYSQL" link. Click it.
    It wil show you a form, or button to "Add access host"
    Here, enter the IP of your "website server", and save.

    Wait for a minute or two, then open your website in browser.
    If you did everything right, then cross server connection will be made and website will work.

    Enjoy
     
    JEET, Jun 6, 2022 IP