Convert mysql files to .sql files

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

 

If these are standard DBF files, why can't you just change the extension?

 

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?

 

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

 

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.

 

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