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.

    Copyright 2020. All Rights Reserved