2

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.

0

2 Answers 2

2

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

1
  • Rather than the piecemeal deleting of the datadir, I'd remove all its contents and run mysql_install_db -u mysql (or sudo -u mysql mysqld --initialize` (MySQL-8.0)) for a fresh start.
    – danblack
    Aug 22, 2018 at 23:26
0

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.

2
  • Thank you very much for your answer. I guess the follow up question is 'shouldn't the database itself have that size instead of the ibdata1 file'?
    – Mugurel
    Apr 2, 2015 at 13:14
  • (assuming you are not using innodb_file_per_table) then the ibdata1 file IS (essentially) your database. It contains all the Table Data Pages, Index Pages, Data Dictionary, various Buffers and Redo Logs etc.
    – IGGt
    Apr 2, 2015 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.