MySQL replication - ibdata1 huge

Question

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.

Answer 1

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

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

   service mysqld start

7. 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

Answer 2

The config items you added in should have little to no impact on the situation. n.b. you will probably also want log-slave-updates, (details) but leave that out until after you've imported the data, of you'll have 100's of GB's of binary logs created.

I think the real question is whether your OS is happy with a single file that size, bearing in mind that it wont get smaller, but may get larger.

If it's going to be an issue you may want to look at innodb_file_per_table (details) which 'basically' splits the data between ibdata1 and a file for each table. (This involves reloading the dumpfile to take affect) (this is on by default from MySQL 5.6.6)

There are pros and cons for both options.