MySQL questions for you

[Onkel Neal]

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