Forum Moderators: phranque
Most people recommend that I use mySQL for my site instead of Access due to its limitations. I was a bit worried that it was going to be tough learning how to use a whole new database program, but once I downloaded it and tried it I realized it was easy as pie. However, when I go on my webhost's control panel and use their 'create a mySQL database' function, it doesn't create a file that I can find. Is mySQL different than Access in this way, or am I just not looking in the right place for it? I'm not even sure what extension it would have.
I guess it's not a big deal because I can just create an ODBC as I do with Access databases, but I would feel a little more comfortable if I could "see" my database when I upload files via ftp. Also, I'll need to download the database daily for backup purposes, and I'm not sure how to do this if I can't locate the database file.
I also like to work offline when creating the structure of my database, and then upload the skeleton before using it. Is it possible to create a mySQL database on my computer and then upload it to my server for use? If so, what file(s) would I upload, and to where?
Does phpMyAdmin have the ability to handle all these things? I tried it out and I created a database and a table within it. Then I used the export function and I got a filename.sql file. I created a folder for it in c:\mysql\data folder and put the filename.sql database within. Then I went to c:\mysql\bin\mysql.exe and I got to the prompt and I did a show databases; and it brought up the database folder I had created for filename.sql. However, when I tried to show tables; it didn't find the table I had created. I don't even think it's accessing the filename.sql database, but rather just knows there's a folder there. In the mySQL databases I created in my computer it creates a folder in the c:\mysql\data folder and then puts 3 files in them - a .frm, a .myd, and a .myi file. I guess I don't know what the deal is. Any help would be appreciated.
at least on a linux server (I don't have mySQL on Windoze), all the mySQL databases and tables are stored in the server's central mysql-data-dir, which probably may be /var/lib/mysql/.
From the view and logic of the mysqld server, all the various database tablespace files are the mysqld-server's data, not a unix user-id user's data.
You can use the mysqldump program (or the similar phpMyAdmin function) to export the data
mysqldump --opt database > export-file.sql
for backup purposes.
You can read these data back into mySQL with
mysql database < export-file.sql
The export-file.sql is not a database in database format, but the exported data in an importable meta-format.
So you may first try to do an import on your local mySQL to create a database (consisting of the .frm .myd .myi files) and populate it with the import data out of the .sql export file, before you can see and use them.
Regards,
R.
I guess I'm still a little lost. I've never had much luck with computers, and this time seems no different. For reference, I'm using windows xp. In my c:\mysql\bin folder I have several binary executables, but only two of them work. These are mysql.exe and winmysqladmin.exe. The others, including the mysqlimport.exe and the mysqldump.exe, don't work. I just double click on them and the dos command window comes up for a second and a lot of things scroll by really quickly, then it shuts down before I can read any of it.
I gather from what you said that the .sql file I have needs to get run through some kind of program in order to convert it into the three files I need in order for mysql to recognize it as a database. I just don't know what binary I need to be using in order to do this. In mysql.exe I can create tables and databases and execute sql commands on them. However, when I create a database with my webhost's control panel using myPHPAdmin and export that database, it comes out in a .sql file and I don't know what to do at this point.
I wish I was using linux, where it appears most of these programming languages and tools were meant to be used on, but that's another story. I got linux slackware a few years back and spent no less than 100 hours trying to get it to work, and with no luck. Compiling kernals and figuring out what hardware worked with it and how to set it all up was ridiculous and fruitless. I must be missing some basic idea when it comes to computers, because everybody else seems to be able to figure these things out much more quickly than I.
sorry, but as I said before, I am not familiar with the mySQL on Windoze.
Try to open a black MSDOS console prompt window and then execute the mysqlimport.exe and mysqldump.exe programs from there, so that you can see what they are telling you.
Just try and type
"c:"
"cd c:\mysql\bin"
"mysqlimport"
"mysqlimport (database) (importfile)"
and experiment.
Consult the documentation, because most basic functions that are available on Linux should also be there in the Windoze version.
I never used the mysqlimport-program which seems to load flat data into tables, so you may also directly experiment with the mysql program to try the import of the sql-file.
Good luck.
Regards,
R.
First. Broadly speaking, mysql is pretty similar on windows and linux. Everything Romeo told you, except for specific file paths, will hold true on both systems. The client interface is nearly identical. There is no reason to dispair or to wish that you were on linux (er, okay, there are lots of reasons, but not pertaining to your question).
Second, I recommend that you put mysql-front on your machine. It does lots of things through a GUI, but it always shows you the SQL commands being sent to the server. Start with training wheels and always study the SQL output. After a while you'll find that the GUI is sometimes practical, but more and more it's easier to just type it in (mysql-front allows both easily).
Now, let's back way up. There is a fundamental difference between Access and MySQL. MySQL is built around a client/server model, sort of like your web browser and Apache web server. Your browser does not typically open files, it receives data sent to it by the server. This might be hard to grasp, but mysql is not a program that you use to open and view a database. It is a server that sends the data to a client. Generally speaking, these files are probably being hidden as much for your protection as anything. Opening these files in a readable format and trying to manipulate the data would be about as productive as opening your Access DB in Word and trying to manipulate the data that way.
Access is a program more like Word. You start Access and open files in it.
The way you typically get data from one computer to another is to dump all of your data to a file that includes a full series of SQL commands. This is completely different from your actual data. You can use your mysql client (the shell client that comes with mysql, PhpMyAdmin, mysql-front, or whatever you use) to dump all data to a file. You then import that file to your online database. You can do this by compressing data, FTPing to your server, uncompressing, and using the mysql client to load the data into your DB (this requires shell access). This is the best way for very large files. For smaller files, you can use the PhpMyAdmin file upload interface.
Some programs (mysql-front for sure) guide you through the export part real well and let you export structure only, data only or both.
To use mysql, you must connect to the server and select a database to use. Only then are the tables available. This process is more or less automatic depending on how you are accessing the DB.
mysqlimport.exe and the mysqldump.exe, don't work.
Actually, they probably work, but you can't just double-click and have it do something (AFAIK). These are command-line tools that need arguments like so
mysqldump -uusername -p databasename > textfile
See the manual: [dev.mysql.com...]
Hope that helps
I was wondering if you could expand on what Romeo meant when he said that mysqlimport seems to load "flat" data into tables.
Also, I'm not quite sure I understand why myPHPAdmin is only capable of handling smaller databases. What's considered a large database? Are mysqlimport and mysqldump capable of handling larger databases? If so that's great, because I'd just as soon do all this in a command prompt manually than use a program like myphpadmin.
I'm dealing with very small databases at the moment (just a few kb each), but I expect them to grow exponentially as time passes. I just want to make sure I've got all the tools I'll need to backup the databases at my webhost, download the backups onto my computer, and if something happens to my databases, upload one of my backup dumps and import it.
Thanks again for all the help.
At first I thought it would be best to make separate databases for each function of the web site as well as each news category, but then I realized this might cause difficulty because I wouldn't have a unique identifier for each article. It might also be inefficient to open several connections to these various databases on a single page, as would be necessary when showing articles from different categories.
So, I was wondering if the following setup could potentially be a problem: 1 single mySQL database with 2 tables. 1 table contains all the usernames, passwords, contact information, etc. The other table contains all the articles for every news category. I can see how it will make things significantly easier with only two tables, but is this a mistake given the amount of data I intend to store in the articles category? Worst case scenario I've got 5000 articles at 15k each in the articles table.
There's no reason why the users can't be in a separate database completely; I'll only use their information to verify usernames/passwords when they login.
So, should I go with 1 database with the two tables in it, or 2 databases with users in one and articles in the other, or is there a good reason to make separate databases with a single table each to hold each different category of article?
Thanks.
Also, I'm not quite sure I understand why myPHPAdmin is only capable of handling smaller databases. What's considered a large database?
Not so much a matter of capability as convenience. I just find it a lot easier to do the mysql dump, gzip the file, FTP it compressed, uncompress, feed it to mysql from the command line. Sounds harder than PhpMyAdmin, I know, but that way I never have to worry about timing out and issues like that. I don't have to use the Apache server at all, so if the load is high there is less likely to be a problem (in my experience).
For the purposes of my comment, a large database is anything that takes more time than I like to upload through the http server. PhpMyAdmin seems to take forever to do anything but the simplest tasks. If you have access to the shell and you want to do anything other than the quickest little task, it's just less frustrating in general if you avoid PhpMyadmin.
I just want to make sure I've got all the tools I'll need
If you have one of the following, you have all the tools you need.
a) PhpMyAdmin and a little basic knowledge
b) shell access and an FTP client and *slightly* more basic knowledge.
If you have ever done anything at all from the command line, this won't be a problem for you. Don't worry!
There's no reason why the users can't be in a separate database completely; I'll only use their information to verify usernames/passwords when they login.
Someone please correct me if this is wrong, but I don't think you'll get any gain by splitting the databases and, as you say, incur all the extra overhead of multiple connnections. More importantly, for the future, perhaps you will want to implement something like a user comment system where you will end up with lots of queries that draw on both tables (as well as your comments table, etc etc). Personally, I would definitely do 2 (or more) tables and one DB.
Tom
To design the database itself is a major job, not to be left to chance.
Read up on "database normalization" before you start.
Poorly designed databases will cripple your application
