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?

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.
  • 1
    Won't this move all DBs? – dezso Jun 16 '17 at 13:14
  • I think u cant move only one DB. I have to move ALL db. (i apologize for my bad english) – G3ph4z Jun 16 '17 at 13:25
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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.