jimfog Posted July 4, 2012 Share Posted July 4, 2012 I installed MySQL separately-till now I had it as a part of XAMPP. So I have kept the old MySQL files where in it(in a data file) the tables exist which I wish to import to the new installation. As far as I know in order to import a table into MySQL(I use PHPMyAdmin) it must be an sql file. Nonetheless, in the old NySQL installation the files that contain the tables that I want to import have extensions like .frm and MYI. I tried to import these but could not. I get this message actually: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'þþ' at line 1 What can I do? Link to comment Share on other sites More sharing options...
niche Posted July 5, 2012 Share Posted July 5, 2012 Import them as an ascii file. Just make sure the structure has been established. Link to comment Share on other sites More sharing options...
jimfog Posted July 5, 2012 Author Share Posted July 5, 2012 Import them as an ascii file. Just make sure the structure has been established.And how do I do that? Link to comment Share on other sites More sharing options...
niche Posted July 5, 2012 Share Posted July 5, 2012 Goto the import tab on phpmyadmin. Look for the box that says "Format of imported file" and use either the "CSV" option or the " I cant remember which one I used last. As I remember, one worked for me. The other didn't. Link to comment Share on other sites More sharing options...
jimfog Posted July 5, 2012 Author Share Posted July 5, 2012 I tried some options and I get this error message: Notice: Array to string conversion in C:\Apache24\htdocs\phpMyAdmin\libraries\Error.class.php on line 202 Link to comment Share on other sites More sharing options...
justsomeguy Posted July 5, 2012 Share Posted July 5, 2012 The files that MySQL creates are binary files, those aren't things that you can import using phpMyAdmin. There may be a way to use the MySQL command line to create a database based on existing files, or the normal way would be to export the data from the old system to a SQL format and then import it to the new system. There are some examples here, the full reference for MySQL command line options is in their documentation. http://www.mydigitallife.info/how-to-backup-and-restore-export-and-import-mysql-databases-tutorial/ Link to comment Share on other sites More sharing options...
Guest So Called Posted July 5, 2012 Share Posted July 5, 2012 I've avoided commenting because my answer seemed to me to be overly simplistic. But here it is: Both systems should have phpMyAdmin installed. Use PMA to export file from first system in SQL format, use PMA on second system to import SQL. SQL files are intended to be portable. Database raw files are generally not intended to be portable. Those .FRM and .MYI files sound non-portable to me. Use PMA and .use SQL file type. Note: You may have some problem with database name on old file not matching new database name. If you can't pick new host with same database name then you may have to manually edit your .SQL file. I've had that happen since my shared hosting assigns database names and does not let me pick. Link to comment Share on other sites More sharing options...
niche Posted July 5, 2012 Share Posted July 5, 2012 The files that MySQL creates are binary files, those aren't things that you can import using phpMyAdmin. I'm certain I've imported csv files into sql tables using phpmyadmin, but this says I can't. Am I understanding you correctly? Link to comment Share on other sites More sharing options...
justsomeguy Posted July 5, 2012 Share Posted July 5, 2012 I'm certain I've imported csv files into sql tables using phpmyadmin, but this says I can't. Am I understanding you correctly?No, you're not. I'm saying that MySQL does not store data natively as .CSV files, it stores them as binary files. You can use phpMyAdmin to export to a CSV file, and import that file, but that's not the same thing as trying to import the native files that the storage engines create to store the tables. We're dealing with files that were created by MyISAM or InnoDB or whatever that are used to store the structure, indexes, and data that are in the actual tables, not a text format that you've used a tool to export the data to. Link to comment Share on other sites More sharing options...
Guest So Called Posted July 5, 2012 Share Posted July 5, 2012 CSV files are text files. PMA has CSV listed as one of the import/export options but I've never tried it. BTW, being able to import/export databases (like in SQL format) is extremely important because this is how you back up and restore your database (or at least that's how you do it on a remote server like as in shared hosting). If you cannot export/export your database you cannot backup/restore your database, and if you can't do that then you can't protect your site from loss of data. Everybody using a MySQL database should make certain that they can perform these functions. Excepting of course databases used for tutorials or other situations where the loss of the database is of no importance. I recommend as a first experiment using phpMyAdmin's default settings for both export and import. Then revise if problems are encountered. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 5, 2012 Share Posted July 5, 2012 MySQL also comes with native tools for exporting and importing. The mysqldump program is used to export entire servers or databases, it's what phpMyAdmin uses for some things. Link to comment Share on other sites More sharing options...
Guest So Called Posted July 5, 2012 Share Posted July 5, 2012 BTW the best way to experiment with import/export in a remote environment (shared hosting) is to create a new database. Export the data from your old database and import it to the new database. If it appears to have worked then change your script server, database name, database user, database password to the new database to see if the operation succeeded. If everything is fine then you did it. If not, you can just change the database name, user, password etc. back to revert to the old (working) database. The alternative (wrong way) would be to export the database then import back into the same database. The problem is that if that fails you've screwed your database and have no way to fall back. Link to comment Share on other sites More sharing options...
niche Posted July 5, 2012 Share Posted July 5, 2012 Thanks for the clarification jsg Per your #9 post. I did say in my #2 post, that the structure would need to be there before importing the ascii (csv). Link to comment Share on other sites More sharing options...
jimfog Posted July 6, 2012 Author Share Posted July 6, 2012 One question, not so relevant. I have installed MySQL-how am i going to understand that is actually running? Link to comment Share on other sites More sharing options...
justsomeguy Posted July 6, 2012 Share Posted July 6, 2012 If it's on Windows, you can pull up the list of services to find the MySQL service and make sure the status is "started". Link to comment Share on other sites More sharing options...
jimfog Posted July 6, 2012 Author Share Posted July 6, 2012 Ok, now to return on the topic. Unfortunately I do not have the old system so as to export from there in a CSV format, just the new PHPMyAdmin. What can I do? Link to comment Share on other sites More sharing options...
boen_robot Posted July 6, 2012 Share Posted July 6, 2012 How did you generate those .frm and .myi files? Whatever utility you used, THAT should probably contain means to also restore from those files. Link to comment Share on other sites More sharing options...
justsomeguy Posted July 6, 2012 Share Posted July 6, 2012 Those are MySQL's native files, that's where it stores the databases. http://www.google.com/search?client=opera&rls=en&q=mysql+restore+frm+file&sourceid=opera&ie=utf-8&oe=utf-8&channel=suggest Link to comment Share on other sites More sharing options...
jimfog Posted July 7, 2012 Author Share Posted July 7, 2012 Here is how the situation now, I managed to found in my system the old PHPMyAdmin installation. So now we have the new PHPMyAdmin installation under Apache24/htdocs(in which I want to import the database) AND the old PHPMyAdmin folder/files somewhere in my PC, not in htdocs-along of course with MySQL tables i wish to import in the NEW PHPMyAdmin. Given the above what can I do? I have not installed yet Mysql server 5.1. I want to see what can we do with what we have in our hands now. Link to comment Share on other sites More sharing options...
Guest So Called Posted July 7, 2012 Share Posted July 7, 2012 phpMyAdmin files have nothing useful to import. PMA is just a tool. Other than having a configuration file with your MySQL login data it's just a program. You need to export your old MySQL files before you can import anything. It doesn't appear that your old MySQL native files are going to do you any good. First step: export old file into SQL format. Link to comment Share on other sites More sharing options...
jimfog Posted July 7, 2012 Author Share Posted July 7, 2012 CORRECTION: I meant the old MySQL files(data etc...) You say to export them, how?I must use a tool, what tool? PHPMyAdmin DOES not connect with these folders-these folders are just somewhere in my system.I remind that these files are myi, frm files. Could I use mysqldump? Link to comment Share on other sites More sharing options...
Guest So Called Posted July 7, 2012 Share Posted July 7, 2012 Use phpMySQL to connect with your old MySQL installation that uses those files. Export. Then connect phpMySQL to new MySQL server and import. The topic has been skirting around something that you probably don't want to hear. If you deleted your old MySQL installation before exporting the files then IMO there is a good chance you will not be able to recover them. There may be a small chance that you can reinstall your old MySQL and have access to the old files. Why didn't you export them when you had the chance? And why didn't you back them up? (Same thing as exporting.) Link to comment Share on other sites More sharing options...
jimfog Posted July 7, 2012 Author Share Posted July 7, 2012 Use phpMySQL to connect with your old MySQL installation that uses those files. Export. Then connect phpMySQL to new MySQL server and import.How, PMA is under hdocs, when I start it, it connects with the mysql server which has been installed under program files-how I can make PMA connect with the mysql files that are somewhere else in the system?The topic has been skirting around something that you probably don't want to hear. If you deleted your old MySQL installation before exporting the files then IMO there is a good chance you will not be able to recover them. There may be a small chance that you can reinstall your old MySQL and have access to the old files.. IF the files are permanently lost I have my alternatives-no need to mention them mowWhy didn't you export them when you had the chance? And why didn't you back them up? (Same thing as exporting.). As I said above I have my alternatives Link to comment Share on other sites More sharing options...
Guest So Called Posted July 7, 2012 Share Posted July 7, 2012 How, PMA is under hdocs, when I start it, it connects with the mysql server which has been installed under program files-how I can make PMA connect with the mysql files that are somewhere else in the system?phpMyAdmin does not connect with MySQL files. It connects to a MySQL server. The server is the element that is responsible for connecting with the files. If you want those files you're going to have to find a way to connect a MySQL server to them. I have my doubts that it's possible without some serious expert hacking. phpMyAdmin understands only import files. You can get a list of the file types from the drop-down menu in the PMA import/export section. The way PMA works is that when you import files it connects with your MySQL server, and then reads the SQL import file and generates a set of transactions with the MySQL server to get the server to store your data in the server's database. phpMySQL is not designed to connect with raw data. That's why there's so many import/export file types. It's understood that the old database will be exported by PMA or some similar program under the old file system, which turns the data into a format that PMA understands, one of the most common formats being SQL I think you should start looking at your alternatives. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.