MySQL and import database

Matsaki

Matsaki

Registered
I always have problem with MySQL :(

I tried Webmin, Navicat, CocoaMySQL etc.

I am trying to reinstall and make my localhost MySQL working. I managed to install MySQL 4.1.10. Then I use one of the above MySQL admin clients to import a rady db_mysql.sql file of 114Mb. Always the import stops after half the import whatever client I use. No error repport? (the file is ok)

Is there another way to import the db? Also ie Navicat refuse to connect to localhost, and other client will.
 
1. split sql file into smaller sql files (you can do the split in any point as far as you don't break an sql statement)
2. pipe sql files to mysql client from terminal (ie: cat db.sql | mysql -u root -p targetdb)
 
Sounds like you've got an error in your sql file.

I take it that that the sql file is a database dump containing tables and data from either some form of MySQL or another sql compliant database.

I'd do what the last posted stated and break up the sql file, if its a data transfer. You could make two files one creating the database and tables and the other containing the data import statements.


----------
A slightly different form of an import command line:

/usr/local/mysql/bin/mysql -uMYSQLUSER -pPASSWORD < ./gymnastics.sql


----------

One more point I use "PHPMyAdmin" to manage the MySQL database, I always found it to WORK, the others either lacked functionality or wouldn't connect.
 
I have a sneaking suspicion that when you import your old data you're also trying to overwrite the 'mysql' database. You'll get an error if you try to do that and the database exists.

IF this is the problem, try this solution:
1. Drop the mysql database but don't reload privileges;
2. Import all your old data;
3. Reload privileges.

This will give you an identical database to the old one.
 
What I do is that I download a MySQL dump as "data+structure" with Navicat. Then I just create a new local database with the same Name. And then try to do an import. Some (simple) tables works fine, while other more complex tables with A LOT of information in goes half way, and the stops. No error message or nothing. Just stops?

Looking in Navicat I don't find
Reload privileges
Click to expand...
I only find "Flush privileges"
 
mysql> FLUSH PRIVILEGES;

Also make sure you spell privileges properly ;) Not to be a jerk, I've done it lots of times, but it won't work if you type FLUSH PRIVELEGES or PRIVALEGES.
 
thewelshman said:
One more point I use "PHPMyAdmin" to manage the MySQL database, I always found it to WORK, the others either lacked functionality or wouldn't connect.
Click to expand...

I agree... I've used a number of MySQL client programs and phpMyAdmin does a great job with huge data imports / exports.

In fact I just got done doing a table import of 1000 records that no other client could parse. I wanted to test the limits of phpMyAdmin, so I didn't separate the code at all... I did it all with one big copy / paste.

I wouldn't normally recommend this approach, but it's nice to know you can do it if need be.

I frequently have problems with doing such an import with my terminal. At first I thought my problems were related to buffer size, but even smaller 10 record table imports have failed or (timed out) when using the terminal.
 
MySQL's developers have ported the GUI Administrator to Mac OS X, it came out just a few days ago, and works really well. It beats all the others hands down as far as tuning MySQL goes. Navicat is still the champion if you want to work with other server types too, but if youre only server is MySQL you really should use MySQL's own Administrator.

You'll find it here:
http://dev.mysql.com/downloads/administrator/1.0.html
 
First impressions seem ok, and offers potentially more than the other GUI's (but not phpMyAdmin).

There are things in phpMyAdmin that, at first glance, don't seem to be included in "MySQL Admin"

Example: If I select catalogs (show databases) from the menu, I get the tables and so forth, how do I get to the data contained in a table. Unfortunately there are many instances where there is a need to alter a piece of data after it has been imported into a database, or a correction to a data item needs to be made.

This maybe quite simple, just couldn't see how to do it straight off in "MySQL Admin",
 
Searching and editing the contents of your database is not part of administrating tasks, that's what users do...

Try YourSQL for that (also free and not a potential security hazard as phpMyAdmin is). Beware of web based administration tools, they may seem elegant and easy to use, but they carry the penalty of being hacking targets. Also, with an application like MySQL Administrator, Navicat or similar, you only have to secure your MySQL server.

With Webmin, phpMyAdmin and the likes you have to secure your web server too. Two gates to watch. With several users, that means a hundred percent extra work with few -- if any -- benefits.

It's true that the apps used to cost money, and phpMyAdmin/Webmin are free, but with the arrival of MySQL Administrator as a free tool that argument is out the window. It's also true that the web based administration tools allow you to use any computer on the Internet to manage your database(s), and the applications have to be downloaded first, but to me that is a small price to pay for cutting security problems in half.
 
Sorry mate but you are just plain wrong, DBAs and the like constantly change items in databases, I see it every single day. I don't mean to be testy but if you are going to pump a product then its got to do at least what other products already do, you've now owned up to saying it doesn't.

Yes "MySQL Administrator" does look interesting and works like a charm, but the falls over on simple things, see the example in the last post.

Examples of GUI front ends that do all this already.
Top end - Oracle DB Studio
Bottom End: Toad, CocoMySQL, phpMyAdmin

Security is part of what I do, you start to protect your site from the Systems and Networks level first, then figure out your security policy for your target services you are providing, get a proper security plan together then do your install, not apply a widget as an afterthought.

On another note building a secure environment from an intellectual standpoint is a small price to pay for having a system thats properly understood and not hope that some product is going to plug everything. Example: MicroSoft
 
The advanced configuration options you have in MySQL Administrator are only available in one other place: the CLI. No other GUI tool I've come across lets you get that far in under the hood.

As for DBAs mucking about with the contents of the database, in my experience (some twenty-odd years) that is more of an exception, and not nearly as necessary as being able to change table formats, setting indexing options, changing character sets, collations, doing backups and restoring, repairing and checking tables etc.

True, sometimes you do need to change something, but you might not need that in the same tool. YMMV.
 
I think we have a difference on emphasis here, i.e. what we use these tools for.

What I was using as an example is contained in phpMyAdmin and CocoaMySQL. Ther are many occasions where no tool suffices an I have to resort to something like PERL etc.

I'm a UNIX system Admin (some 20 odd years) and get buzzed all the time to either make changes to a databases by DBAs (for a host of reasons) or I have to give the DBA persmissions to do so. Especially in the field I'm in at present, where companies are eating each other's data when they get acquired.

Functionality in the same tool YES, it was a simple enough example I used and its not there. I wouldn't want two tools modifying the same database at the same time.

From a UNIX admin point of view, we are moving away from the standard tools provided to backup the database, we either use COLD backups of the data area (done usually at night) or SNAPSHOTting, i.e. block level changes to disk are written to snapshot storage and then written to tape (no downtime). Although, historically we still do dumps, just in case, but we never use really them, the only time we do use dumps is when we need to import data from elsewhere.

The types of changes to databases you are talking about, nobody onsite is permitted to change, that would very probably break the Application using the database and not usually well liked by the users.

Of course I'm not just talking about MySQL here, the same rules apply though.

I've got all three open here, and in a lot of cases a bunch of stuff is mirrored in each, for what I need out of them. Except that I don't seem to be able to edit a data point(s) in a table using "MySQL Administrator".
 
You must log in or register to reply here.
Back
Top