0

I am using mysql version 5.5.54-0ubuntu0.12.04.1 (Ubuntu) with few DB & one of the DB is growing very rapidly and eating up disk space.

I have mounted secondary disk & would like this specific DB only to use this new added disk.

can someone please provide some suggestions how it can be done? can I move single DB to new disk or I have to move all DBs / mysql to new ? how?

iBBi
  • 111
  • 5

3 Answers3

0

You should try this: How can I move a MySQL database to another drive?

  1. Shutdown mysql.
  2. Move all the files in your current data directory to the new location (check out the location in step 3 - datadir parameter).
  3. Locate my.ini file (it is in the mysql installation directory). Change datadir parameter value to point to the new location.
  4. Start mysql.
Crowor
  • 52
  • 1
  • 6
0

I think you can't change your Database directory with one command.

You must move each of table to new Driver:

For do this (tested on MySQL 5.6):

  1. Make sure innodb_file_per_table is ON
  2. Run SHOW CREATE TABLE for each table and save all create command.

  3. Run each create command and add _new to table name and DATA DIRECTORY = 'path' at the end of file too all of them for example:

    CREATE TABLE t1_new (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';

  4. run Insert for all tables to move data to _new table example:

    INSERT INTO t1_new select * from t1

  5. After move all tables to new table you can drop gracefully all old tables

HamoonDBA
  • 637
  • 4
  • 10
0

thank you all for your valueable suggestions. I was not able to move single DB only. I found solutions to move all DB :( any how this is how I did. still searching to move single DB solution,,,

this is how I managed to move mysql db to new drive.

-Stop mysql service ** -copy /var/lib/mysql folder to new drive using rsync to preserve permissions. ** -edit mysql inf file and point new folder for data dir ** -add entry for new folder in apparmour start mysql ,

All done. documented here.

iBBi
  • 111
  • 5