MySQL questions for you

Onkel Neal · 03-09-09, 03:12 PM

I know there are some here who know a lot more about MySQL than me, if you have a second, maybe you can help me confirm a few things and fill in some blanks.

As you know, after paring the search table the Subsim forum db is about 1.7GB, so making backups is a ginormis issue. I used to use phpMyAdmin to export the tables. With the 800MB post and search tables, I would have to export them in pieces, usually 50MB at a time. Yes, that's an all day job.

Um, ok, so there's this thing called mysqldump. Never tried it until today.

[root@srv01 root]# mysqldump --opt -Q -u userxxx -p xxx_-_forum > /home/xxx_forum030809b.sql
Enter password: xxxpassword
mysqldump: Got error: 1044: Access denied for user: 'userxxx@localhost' to database 'xxx_-_forum' when using LOCK TABLES
[root@srv01 root]#

What do the ---opt -Q do? "--opt" is to optimize, correct? And the "-Q" ?

So, it gives me an error message about using Lock tables, what can you tell me about that? What does Lock tables do? Keep people from posting and changing the db?

So, I turned the forum OFF and then tried it again, without the opt and Q:

mysqldump --u userxxx -p xxx_-_forum > /home/xxx_forum030809b.sql

And it worked. And I was even able to find the file with FTP and download it.

Yeah, that's always fun, finding stuff with FTP that I can see in SSH.

So, naturally, there's no way Notepad or EditPlus will open a 1.7GB .sql file

I downloaded UltraEdit, it will open the file but if I try to scroll through it, it stops responding. I guess I need more than a single core P2.4 with 1GB of RAM?

When I checked the last of the sql file, it was the Words table and the last few records seems to match the last records in the Words table in phpMyAdmin. Does that indicate the dump captured all the data?

When I view the summary of the db in phpMyAdmin, it says
97 table(s) Sum 24,417,581 -- 1.4 GB

The dump file is 1.7 GB. What is the difference due to?

thanks,
Neal

Onkel Neal · 03-14-09, 01:04 PM

No MySQL experts out there, eh?

Well, one question I am able to answer, the new DuoCore computer will open the 2GB text files much better than the old one. It still takes a few seconds to scroll through the file, but it seems to be functional.

CaptainHaplo · 03-15-09, 02:30 PM

Yea I gotta admit to an utter lack of knowledge here. I am lucky I can SPELL

SQL

Much less work with it.

SUBMAN1 · 03-15-09, 03:04 PM

I'd suggest you learn it. My company pays probably up to $200K/year for a good DBA.

I can probably help you a bit Neal, but I have little time right now.

-S

Onkel Neal · 03-15-09, 04:45 PM

I'm pretty sure I'll never learn much of it, though I wouldn't mind being a DBA at that salary.

03-09-09, 03:12 PM	#1
Onkel Neal Born to Run Silent Join Date: Jan 1997 Location: Cougar Trap, Texas Posts: 21,298 Downloads: 534 Uploads: 224	MySQL questions for you I know there are some here who know a lot more about MySQL than me, if you have a second, maybe you can help me confirm a few things and fill in some blanks. As you know, after paring the search table the Subsim forum db is about 1.7GB, so making backups is a ginormis issue. I used to use phpMyAdmin to export the tables. With the 800MB post and search tables, I would have to export them in pieces, usually 50MB at a time. Yes, that's an all day job. Um, ok, so there's this thing called mysqldump. Never tried it until today. [root@srv01 root]# mysqldump --opt -Q -u userxxx -p xxx_-_forum > /home/xxx_forum030809b.sql Enter password: xxxpassword mysqldump: Got error: 1044: Access denied for user: 'userxxx@localhost' to database 'xxx_-_forum' when using LOCK TABLES [root@srv01 root]# What do the ---opt -Q do? "--opt" is to optimize, correct? And the "-Q" ? So, it gives me an error message about using Lock tables, what can you tell me about that? What does Lock tables do? Keep people from posting and changing the db? So, I turned the forum OFF and then tried it again, without the opt and Q: mysqldump --u userxxx -p xxx_-_forum > /home/xxx_forum030809b.sql And it worked. And I was even able to find the file with FTP and download it. Yeah, that's always fun, finding stuff with FTP that I can see in SSH. So, naturally, there's no way Notepad or EditPlus will open a 1.7GB .sql file I downloaded UltraEdit, it will open the file but if I try to scroll through it, it stops responding. I guess I need more than a single core P2.4 with 1GB of RAM? When I checked the last of the sql file, it was the Words table and the last few records seems to match the last records in the Words table in phpMyAdmin. Does that indicate the dump captured all the data? When I view the summary of the db in phpMyAdmin, it says 97 table(s) Sum 24,417,581 -- 1.4 GB The dump file is 1.7 GB. What is the difference due to? thanks, Neal

03-14-09, 01:04 PM	#2
Onkel Neal Born to Run Silent Join Date: Jan 1997 Location: Cougar Trap, Texas Posts: 21,298 Downloads: 534 Uploads: 224	No MySQL experts out there, eh? Well, one question I am able to answer, the new DuoCore computer will open the 2GB text files much better than the old one. It still takes a few seconds to scroll through the file, but it seems to be functional. __________________ SUBSIM - 26 Years on the Web

03-15-09, 02:30 PM	#3
CaptainHaplo Silent Hunter Join Date: Apr 2007 Posts: 4,404 Downloads: 29 Uploads: 0	Yea I gotta admit to an utter lack of knowledge here. I am lucky I can SPELL SQL Much less work with it. __________________ Good Hunting! Captain Haplo

03-15-09, 03:04 PM	#4
SUBMAN1 Rear Admiral Join Date: Apr 2005 Posts: 11,866 Downloads: 0 Uploads: 0	I'd suggest you learn it. My company pays probably up to $200K/year for a good DBA. I can probably help you a bit Neal, but I have little time right now. -S __________________

03-15-09, 04:45 PM	#5
Onkel Neal Born to Run Silent Join Date: Jan 1997 Location: Cougar Trap, Texas Posts: 21,298 Downloads: 534 Uploads: 224	I'm pretty sure I'll never learn much of it, though I wouldn't mind being a DBA at that salary. __________________ SUBSIM - 26 Years on the Web