Exporting records 1

[CatPlus]

Hello

Is there a way for me to get to the tables and export records without having access to login info like user name, password, etc.


The company is closed and may need financial transactions in the future which they want to import into an Access database.

The MySQL database is owned by us, is on a server under our possession.

The developer is asking for an exhorbitant amount to do this and says he has removed the schema and backup the application and software

Your guidance will be appreciated

Thank you

 

[TonyGroves]

If you have access to the database files, then you could install MySQL on another machine, copy across the data files, and do whatever you want with them.

Otherwise, unfortunately you're stuck with having to provide credentials in order to access your database.

 

[CatPlus]

Hello Tony

Thanks for your response!

Can I install MySQL on the same machine? Or cant I access the tables without installing MySQL.

Have a good weekend
Mickey

 

[TonyGroves]

You could install a second copy of MySQL on the machine, but it's quite complicated. I don't know of any way to read MySQL tables without MySQL.

What you could do is just replace the user-permissions tables. I've put a set of basic MySQL user-permissions tables online, at

http://homepage.eircom.net/~tonygroves/temp/mysql.tar.gz

. If you stop the MySQL server, back-up your existing MySQL system-data directory (it's called "mysql" and contains files named user.MYD and so on), unzip the archive into that directory, overwriting existing files, and restart the server, you should then be able to connect using the username "root" with no password, which will allow you full access to your data.

Alternatively, you could back-up your entire MySQL data directory, uninstall MySQL, re-install MySQL, and copy the non-system data directories back into the new data directory.

 

[CatPlus]

Thank you Tony,

The information you have provided is invaluable and I appreciate the time you have taken to explain it all!

One more question.

I believe I can install MySQL on a Windows XP or Windows Server 2003 operating system. If I did that, can I copy the *.myd files on MS o/s and re-start MySQL server?

Would the *.myd files be sufficient or do I need some more files?

Regards
Mickey

 

[TonyGroves]

You need the *.MYD (data) and *.frm (table definition) files. *.MYI (indexes) should also be copied, but they can be re-created if necessary.

 

[CatPlus]

Tony,

Thank you Tony!

You are the best!! Appreiate the valuable info you have shared with me

Regards
Mickey

 

[CatPlus]

And hello again Tony,

In Oracle, you can backup all tables and rows with the following prompt line command:

"exp user_name/user_password file=...\backup_file_name.dmp log=...\log_file_name.log"

Can you point me to a similar utility I can use to backup the tables and rows from a MySQL database.

I am told that the user name is 'root' and password is 'sysadmin'

I would then get the .dmp and .log sent to me which I would restore on my machines running MySQL.

I need to extract data of all tables into Access tables

Thanks for your help!

Regards
Mickey

 

[TonyGroves]

You can use the "mysqldump" command. You can choose between two types of output:
(1) create a text file containing all the SQL commands needed to recreate and populate the database. This file can then be fed into the "mysql" command to restore the backup.
(2) for each table, create a SQL file for table recreation and a delimited file containing the data. The data files can then be imported back into the database using data-import tools.

 

[CatPlus]

Thanks again Tony..

Will "mysqldump -u root -p password > file.sql" get me one file with all the available table definitions and data?

I am not sure if I understood (1) and (2) of your reply. I am trying to give the user one command line parameter and get him to upload the file to my FTP account

Thanks for your help!

Mickey

 

[TonyGroves]

If what you want is one file containing everything, then the command you suggested would work, except that you need to name the database:
[tt]
mysqldump -u root -ppassword dbname >file.sql
[/tt]

 

[CatPlus]

Thanks Tony...

You are the greatest!

Appreciate your help!

Mickey

 

[CatPlus]

Good morning Tony,

Sorry to bother you again...

Will you please verify the exact syntax:

You said...
mysqldump -u root -ppassword dbname >file.sql

Is there a space between -u and user name 'root' and
no space between -p(and actual password)?

If user name was 'root' and password was 'mickey' and dbname was 'catplus', then would the syntax read:

(1) mysqldump -u root -pmickey catplus >dump_file_name.sql
or
(2) mysqldump -u root -p mickey catplus >dump_file_name.sql

Thanks again

Mickey

 

[TonyGroves]

According to the manual, at

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

: "If you use the short option form (-p), you cannot have a space between the option and the password."

-u follows the normal syntax, with a space.

 

[CatPlus]

Thanks Tony!

You have been a big help!

Have agreat day

Mickey