Hi Techrunnr, this document deals with How to shrink ibdata1 file in /var/lib/mysql/.

By default ibdata1 file in /var/lib/mysql/ normally consists of 4 types of data

  1. Table Data
  2. Table Indexes
  3. MVCC (Multi versioning Concurrency Control) Data
  4. Table Metadata

Follow the below steps to shrink the ibdata1

  1. Take the dump of all the databases
    mysqldump -h{host}-u{user_name} -p{password} {schema_name} > /path_to_backup/schema_name.sql
  2. Stop mysql service
    systemctl stop mysqld
    or
    service mysql stop 
  3. Now we will remove the ibdata1, ib_logfile0, ib_logfile1
    rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile0 ib_/var/lib/mysql/logfile1
  4. Add the below content to the my.cnf file
    [mysqld]
    innodb_file_per_table
    innodb_flush_method=O_DIRECT
    innodb_log_file_size=1G
    innodb_buffer_pool_size=4G
  5. Start the mysql service
    systemctl start mysqld
    or
    service mysql start
  6. ibdata1 contains table information, so we have deleted this file, so databases will be exist, but table information won’t be exit, when you try to get any table info from any database it will throw an error table doesn’t exists, or data engine is not exist, so we have taken the backup already, we will restore this backup. After starting the mysql service ibdata1,  ib_logfile0 and ib_logfile1 will be created. ib_logfile0 and ib_logfile1 files will be created with the nearly 1GB each, ibdata1 will be created with size nearly 10MB
    mysql -h{host}-u{user_name} -p{password} {schema_name} < /path_to_backup/schema_name.sql

 

© 2019, Techrunnr. All rights reserved.

#1
#2
#3
Questions Answered
Articles Written
Overall Points

0 Comments

Leave a Reply

Please wait...

Subscribe to our newsletter

Want to be notified when our article is published? Enter your email address and name below to be the first to know.