Geek Answers Handbook

Mysql table size limit

Is it possible to limit the size (in size or disk lines) of a single mysql table?

If this is not possible, then what is the easiest way to run multiple mysql engines on the same physical computer? (My plan is to install one mysql instance data file on a separate disk partition).

+4
source share
6 answers

This is very similar to question No. 1561612 , except that the restriction must be enforced for each circuit. Unfortunately, there is no really well-supported way to do this. Please see the answers there and see if they answer your question.

+1
source
  • Quotas: MySQL is not directly supported. you can emulate something using a file system that supports user quotation for each user, setting file ownership for your users and setting a quota for each user at the OS level. note that by default InnoDB uses one table file for all databases, but there is an option to tell it to use the file for the table . however - note that this may affect performance.
  • Running multiple instances on the same computer is possible using a different configuration file for each server.

where, at a minimum, you want to change them from user to user:

[mysqld] port=PORT_NUMBER datadir=/home/USER/mysql tmpdir=/home/USER/tmp/mysql

to start the series, run:

 mysqld_safe --defaults-file=user_specific_my.cnf

to stop the server, run:

 mysqladmin -u root -pROOT_PASSWORD --port=3307 -h 127.0.0.1 shutdown

To connect to the server, the user must use:

 mysql --port=USER_PORT -h 127.0.0.1 -pPASSWORD
+1
source

If you have a (relatively) small amount of data, you can try creating a table with a MEMORY storage engine. This has a configurable limit size , but of course you are limited by the amount of available memory on your box.

If your data size requires disk-based labels, then one direct way to limit the size of the table is to perform a task that periodically clears parts of the table. This is probably a better option than using a small partition - many MySQL commands work poorly when the disk is full.

In addition, you can run as many MySQL instances on your computer as you want - if you connect via TCP / IP, you just need to assign each one a different port number.

0
source

You can use the myisampack utility, which creates compressed tables (but only for the MyISAM engine). Tables become read-only with static data types.

0
source

If they are MyISAM tables, the toy can specify the directories in which the data and index files will be stored.

According to the user interface:

 Data Directory: ___________________ Directory where to put the tables data file Index Directory: __________________ Directory where to put the tables index file This works only for MyISAM tables and not on some operating systems (Windows)

So, I think this means that you can move files for a table if you are not on a Windows host.

0
source

To enforce quotas, there are several different approaches, I think that what I found on this site is probably the very sound I came across,

To direct mysql to different data directories, put symbolic links in your linux setup or try using a configuration file. MySQL will let you specify where the database files will be saved through these conf files, read here to install Windows or find information about my.cnf

Congratulations on your deceased, so I'm leaving.

0
source

More articles:


All Articles