4

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?

Alec
  • 919
  • 2
  • 10
  • 25

2 Answers2

5

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.

Community
  • 1
  • 1
RandomSeed
  • 29,301
  • 6
  • 52
  • 87
0

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.

Sanath
  • 4,774
  • 10
  • 51
  • 81
  • Can you refer me to a tutorial that explains this? I honestly don't know what to say to this. :\ – Alec Jun 17 '12 at 07:26
  • I'm not understanding. Will this "dumping" let me keep all the info in the database? All I want to do is if the database is full on one server, then it continues onto the next server and continues like normal. – Alec Jun 17 '12 at 07:43
  • I mean, how does Facebook store so much information? MySQL? What? – Alec Jun 17 '12 at 07:44
  • @Sanath: if I understand your point, your solution is to *transfer* the whole database to another system? If so, then simple `mysqldump` would do. – RandomSeed Jun 17 '12 at 08:20
  • @YaK I am not understanding much at all. Wouldn't transferring just take up the same amount of space? Can't one database work off of multiple servers? – Alec Jun 17 '12 at 08:24
  • @Alec K. I believe Sanath is giving a procedure to move the database to another system. In this case, yes, it would take the same space. But I may be misunderstanding. – RandomSeed Jun 17 '12 at 08:50
  • I don't know the reason for -1 ...but since you face the issue of storage in the current setup, I submitted the easiest way of moving to a new db server with more resources to move the entire database to that server. all the articles that I have provided helps you to make a dump of the database and restore it. imagine you wanted to run the same database in old an new servers.. imagine the issues that are related to it..since the database on server one has utilized all its resources..imagine its performance.. – Sanath Jun 17 '12 at 09:34
  • for security purposes you can use database replication. but this will replicate the database in a separate host ..1to 1 backup as it can be described.. but as this question suggests the current database it full so the user wants a new database to be added...so that the new data will be added to this new instance.. and when retrieving data, these 2 databases will be queried...if anyone has an idea how that sort of a solution can be implemented, please add some thoughts... – Sanath Jun 17 '12 at 09:42
  • @Sanath You should give a quick summary of your solution. Not everyone is prepared to read through these many links you provide (although most - but not all - of them are relevant) before figuring out the principles of your solution. Besides, please note that none of the links you provide in your answer are relevant (the third one being broken) (unlike the useful links you provide in your first comment). Please kindly update your answer and I will gladly take my downvote back. – RandomSeed Jun 17 '12 at 09:55
  • @Sanath: and yes, I should have explained the reason for my downvote in the first place. – RandomSeed Jun 17 '12 at 10:05
  • I removed the unrelated links but the replication link should be useful – Sanath Jun 17 '12 at 10:45
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12652/discussion-between-yak-and-sanath) – RandomSeed Jun 17 '12 at 10:49