MySQL replication – ibdata1 huge

MySQLreplication

I'm trying to implement MySQL replication in production after I successfully implemented it on my dev environment. Basically, what I did is export the dump using the following :

mysqldump –single-transaction –flush-logs –master-data=2 –disable-keys –no-autocommit –databases mydatabase > mydatabase.sql

When I imported the file the configuration on the slave looked like this :

[mysqld]
datadir=/data1/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

The problem is that the ibdata1 file is HUGE (384G). After I restored it, I added the following in my.cnf and restarted MySQL :

# replication implementation
log-bin=mysql-bin
binlog_format=mixed
read-only=1
relay-log=mysql-relay-bin

My question is : should I go ahead with this if the ibdata1 file is so big or should I add these options before I perform the import? Thanks in advance for any insight.

Best Answer

If you want to shrink ibdata1, so that it should only contain the metadata, You may try these steps

To shrink ibdata1 once and for all you must do the following:

  1. MySQLDump all databases into a SQL text file (as bkp_all_db.sql)
  2. Drop all databases (except mysql schema)
  3. Stop MySQL /etc/init.d/mysql stop

  4. Add the following lines to /etc/my.cnf

    [mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

  5. Now remove ibdata1, so that there should only be the mysql schema in /var/lib/mysql

rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile

  1. Start MySQL instance, This will recreate ibdata1, ib_logfile0 and ib_logfile1 at 1G each

    service mysqld start

  2. Reload bkp_all_db.sql into mysql (import). ibdata1 will grow but only contain table metadata

http://mysqlrockstar.blogspot.in/2014/07/mysql-ibdata1-file-shrink.html

Related Question