Configuring/Change MySQL InnoDB files location

InnoDB is a database engine(compatible with ACID) transactions with insurance for MySQL that has commit, rollback and recovery capabilities to protect the User data.

  • 1 - First stop your database
  • [root@xxxx2 mysql]# /etc/init.d/mysqld stop
    Stopping mysqld:                                           [  OK  ]
  • 2- Changes the my.cnf file by adding the following lines, where innodb_data_home_dir should receive the specific path for your system.
  •  [mysqld]
    #InnoDB file location
    innodb_data_home_dir=/home/mysql_dados
  • 3- Copy the ibdata1 file to the new location.
  •  cp /var/lib/mysql/ibdata1 home/mysql_dados/
  • 4- Start the Database .
  •  [root@BIH002 mysql]# /etc/init.d/mysqld start
    Starting mysqld:                                           [  OK  ]
  • 5- Log in the database and check the value of the parameter .
  •  [root@BIH002 mysql]# mysql -uroot -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 2
    Server version: 5.1.69-log Source distribution
    
     mysql> SHOW VARIABLES LIKE 'innodb_data_home_dir';
    +----------------------+-------------------+
    | Variable_name        | Value             |
    +----------------------+-------------------+
    | innodb_data_home_dir | /home/mysql_dados |
    +----------------------+-------------------+
    1 row in set (0.00 sec)

    Changing the InnoDB location is done.