CODEWITHSUNDEEP
mysql
Alec
Alec

Reputation: 979

What to do when MySQL has no space left?

Out of curiosity, say you have a huge MySQL database full of user information and it is now full. How would you run the same MySQL database off of the same server and another server with more storage space?

Upvotes: 4

Views: 24777

Answers (2)

RandomSeed
RandomSeed

Reputation: 29769

If the problem is just a matter of storage space, then the simplest solution is to transfer the database to another system with more capacity.

Another solution could be adding a disk to the same system, and

  1. (InnoDB) extend the tablespace to this drive (InnoDB even allows to store each table in a separate file)
  2. (MyIsam) move some *.MYD and/or *.MYI files to this drive

The above files can be moved to a different hard drive (they might need to be symlink'd from their original location).

However, a truely widely scalable solution is a cluster of databases, like MySQL cluster.

You may also want to follow this question, in which this very problem is currently being addressed in a real environment.

[Edit] Detailed description:

The below information is valid on a Linux server only. It may be possible under Windows, but I have no idea. Running a MySQL database on Windows is probably not a good idea in a large scale environment anyways.

The three options rely on the same principle:

  • mount another disk into the file system
  • move some existing MySQL data files to this new hard disk, i.e. to the new directory, and/or instruct MySQL to create new data files in this new directory
  • if required, create a symlink to the new location, in order to trick MySQL into thinking the files have not moved

1. Extend the InnoDB tablespace

The InnoDB engine allows to spread the tablespace across many files. The configuration option to tweak (in my.cnf) is innodb_data_file_path. Example:

innodb_data_file_path=/mnt/hard_disk1/ibdata:50G;/mnt/hard_disk2/ibdata:200G;

... instructs MySQL to create /mnt/hard_disk1/ibdata1, a 50GB file, as well as a 200GB file in /mnt/hard_disk2/ibdata2.

I personnally dislike this feature because MySQL won't use the second file at all until the first one is full, which does not allow for fine tuning. The good side is you just need to edit your configuration file and restart the server. That's it.

2. Store each InnoDb table in a separate file

The innodb_file_per_table option, as the name suggests, instructs MySQL to create one separate file for each new table. "New table" means existing tables will not be impacted after changing the setup. To circumvent that, one can dump, delete, and recreate the table(s) to be "extracted" from the shared tablespace.

These files are [datadir]/[database]/[table].ibd, where [datadir] is defined by the datadir option, and [database] is the name of the database, and [table] is the table name. These files can be moved around just like the MyISAM tables (see next option).

3. Move around the MyISAM files

For each MyISAM table, MySQL create three files named [datadir]/[database]/[table].[type], where [datadir] is defined by the datadir option, [database] is the name of the database, and [table] is the table name.

[type] will be frm for the table description (structure), MYD for the data, MYI for indexes.

Now, regarding options 2. and 3., you notice that all files are roughly located in the same directory, or at least share a parent directory. To actually store them on a new hard disk, there are two options.

1) To move a whole database:

  • stop the sever
  • rename the database directory to another name of your liking, say backup
  • mount the hard disk as datadir/databse
  • move the contents of backup into datadir/databse
  • restart the server

MySQL resumes as if nothing ever happened.

2) To move individual tables:

  • mount the new disk wherever you like
  • stop the server
  • move each file individually to the new hard drive
  • create a symbolic link at its original location, with the same name as the original file
  • restart the server

MySQL resumes in a blissful ignorance.

Upvotes: 5

Sanath
Sanath

Reputation: 4886

as I see, the best solution will be to move the database in to a new server with higher capacity and restore the current database there. The current server lacks critical resources such as disk space and memory, therefore critically hamper the performance of the database.

  1. Install mysql in the new server
  2. Get the database table structure (in terms of CREATE TABLE xxx ) from the existing database (this can be easily one using my sql database management tool such as SQLYog)
  3. Run this DDL scripts of the newly create database in the new server
  4. create a .dat file using the existing database
  5. copy it to the new server and run that .dat file against the new database

further reading

  1. http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/

after the migration, its a matter of changing the applicable data source files for any referring applications that use the same database.

Upvotes: 0

Related Questions