1) Is there a pack / compact database function like Access, i.e., it removes deleted entries.
2) Is it a good idea to have one database + many tables or a few databases with relevant tables in them. e.g. if I have a site that a) sells widgets b) teaches Jiu Jitsu both via databases should I have two different databases - WIDGETS with its own tables and JIUJITSU with its tables, or should there just be MYDATABASE and the relevant tables in it.
3) Is it ok to do a manual backup by just copying the /mysql/data/ directory to /mysqlbackups or do I have to do mysqldump
4) If I have a 10 Mb database how is it cached? If certain records are constantly being accessed then are just those cached, the full 10Mb or just chunks at a time?
5)If I have a 80Mb database is there a rule of thumb which says I should have x amount of ram?
6)How do I know the databases are secure? Is there just the master root password to access mysql admin stuff (I use webmin) and the user database for each database. Is there like a guest or anonymous user?
7) How do rights work. Is it possible for someone to interogate the database from off site, e.g. www.mrbadguy/cgi-bin/accessgoodguydatabase.pl
Sorry for the many questions!
Cheers.
[edited by: jatar_k at 10:48 pm (utc) on Jan. 12, 2003]
[edit reason] delinked [/edit]
1) Is there a pack / compact database function like Access, i.e., it removes deleted entries.
There is an OPTIMIZE TABLE command, but only for MyIsam tables. Mysql has differenct table types, that store data in different ways, and not all functions are supported by all table types. Table types are selected on a per table basis, so different tables in a single database can have different types and be stored in different ways.
Normally the table handlers (implementing the different table types) will take care of recovering the space from deleted entries. Don't worry.
2) Is it a good idea to have one database + many tables or a few databases with relevant tables in them. e.g. if I have a site that a) sells widgets b) teaches Jiu Jitsu both via databases should I have two different databases - WIDGETS with its own tables and JIUJITSU with its tables, or should there just be MYDATABASE and the relevant tables in it.
Use a separate database for each project. There is nothing gained from missing databases. In some cases you might see a performance decrease, but in most cases it is just irrelevant. Keep separate stuff separate.
3) Is it ok to do a manual backup by just copying the /mysql/data/ directory to /mysqlbackups or do I have to do mysqldump
Again, it depends on you table type. I have often just copied the directory for a MyIsam database, but using mysqldump is the correct way. With some table handlers, you cannot copy any files, because they handle their table space is different ways. InnoDB has all tables and indexes in a number of separate data files, so you will not get anything meaningful if you copy the data files.
4) If I have a 10 Mb database how is it cached? If certain records are constantly being accessed then are just those cached, the full 10Mb or just chunks at a time?
The database will take care of this. Don't worry.
5)If I have a 80Mb database is there a rule of thumb which says I should have x amount of ram?
This depends of the selected table handlers. MyIsam just has a few settings, but InnoDB has many more. You need to read the section on mysqld optimization in the manual. It can be fairly complicated.
I'd suggest you just start with default values and see how it works out. If you run into problems, try to confront them then. For this kind of advanced issues you might want to subscribe to one or more of the mailing lists on mysql.com.
6)How do I know the databases are secure? Is there just the master root password to access mysql admin stuff (I use webmin) and the user database for each database. Is there like a guest or anonymous user?
Keep the root password private and don't use it in applications. It is for administration.
Create one or more separate users for each application or database. I sometimes have two users, one that can use data and one that can modify data, but it all depends on the spedific situation.
Try to work with minimal privileges in any given situation, while keeping the administration practical. One, two or three levels of privileges might be appropriate.
7) How do rights work. Is it possible for someone to interogate the database from off site, e.g. www.mrbadguy/cgi-bin/accessgoodguydatabase.pl
You can give privileges based on the client hostname/ip, so make sure to restrict access only to the hosts you want. I often have web-application and database on the same server, so I only allow access from localhost, effectively disabling remote access. This will not be suitable for a more complicated setup.
Privileges in mysql can be a bit daunting. Make sure you read the appropriate section of the manual.
Create new users with the grant comnand, such as
grant select,insert,update,create,alter,delete,drop on DATABASE.* to USER@CLIENTHOST identified by 'PASSWORD';
The list of privileges and the words in upper case will have to be replaced.
René
6)How do I know the databases are secure? Is there just the master root password to access mysql admin stuff (I use webmin) and the user database for each database.
It was my understanding that I can set the databases to be only accessed from the local host / server itself and nothing externally.
I downloaded MyAdmin by Dennis Thrysøe and run it on my windows PC. With the right username and password I can connect to the database!
I don't want this to happen. How do I stop it?
I have the Webmin MySQL module. I guess I need to go into Global Options, User Permissions, User, and change the Hosts to something?
All I had to do was to change the host from any to the ip address.
