Mysql – Managing ibtmp1 File Growth on MySQL 5.7

MySQLmysql-5.7

We recently upgraded to MySQL 5.7, and now we're into production we're finding the ibtmp1 file is growing on a few servers. Mostly it's not a problem, but we have had a couple of scary moments where we thought we might fill up disks and whatnot.

From the (slightly vague) docs on the subject, it looks like the file can only grow in size, and the only way to reduce it is to restart the server (I take note of the comment about bad queries using up lots of temp space being the cause of the problem).

Is there actually any way to manage this file, whilst keeping the server online? I know disk space isn't such a limiting factor these days, but in the cloudy world we now live in, it costs money to keep extending disks without ever being able to shrink them again.

Best Answer

Unfortunately, all you can do is what said in my earlier post

SET GLOBAL innodb_fast_shutdown = 0;
Shutdown mysqld
delete ibtmp1
Start MySQL DB

ALTERNATIVE

Attach another volume to the VM and map ibtmp1 to that other volume using

innodb_temp_data_file_path (As of MySQL 5.7.1)
innodb_tmpdir (As of MySQL 5.7.11)

Please be sure to read these docs carefully. Also, try to optimize your queries to avoid make large tmp tables.

Related Solutions

MySQL 5.7 – Where is the thesql_secret file on Windows 7

My blind guess would be to look in %APPDATA%\MySQL on your system as follows:

cd %APPDATA%
cd MySQL
dir .my_secret

For those who use the MySQL no-install Zip File (such as myself), you will not see one.

If .my_secret is not on your Windows servers, I have an alternative. You could start up mysqld manually with skip-grant-tables, set the password

net stop mysql
cd "C:\Program Files (x86)\MySQL\MySQL 5.7\bin"
start mysqld --skip-grant-tables
mysql -ANe"update mysql.user set authentication_string=PASSWORD('mynewpassword') where user='root'"
mysqladmin shutdown
net start mysql
mysql -uroot -p
Enter password: (enter the password 'mynewpassword' and hit <Enter>)

Give it a Try !!!

MySQL Replication: How to restore master FROM slave

1) Backups:

You can verify if your backup is successful by tailing the mysqldump for "dump completed successfully" to verify the success of a mysqldump.
Invalid backup is as-good-as (or as-bad-as) no backup. It's a good idea employ a backup-validation process by restoring it to a provisioned location as possible. Do it.
If you want to stick to logical backup, try using with myduper/myloader, which will be quicker than mysqldump.
Again physical backups are faster than logical and this is specifically true for large sizes. If you can giveup on MySQLDump which is slow for restores and especially if you have all-innodb, you should consider using xtrabackup from Percona (for hotbackup). Check installing and configuring xtrabackup with holland framework.

2) Restoring master from slave.

As such there are few questions and articles available but i will quickly notedown steps I can makeout for restoring master from slave.

Considering your master is M and slave is S. (M --> S)
M is down.
S is promoted to Master ( readonly=false, binlogging is enabled)
M is later fixed and ready to join but need a restore.
Restore M from latest backup, mark it as readonly=true.
Upon restore, make it slave of S. (S --> M)
Once it catches up, you may want to failback to original master.
Stop production traffic, Mark M as readonly=false, S as readonly=true.
Mark S as slave of M (should be simple change master to from present binlog of M)
Thus making them active-passive master-master pair.
Move traffic to M.
You might want to break the master-master but it's fine to keep active-passive.
Making sure nothing writes to Passive master.

Hope this helps.

Best Answer

ALTERNATIVE

Related Solutions

MySQL 5.7 – Where is the thesql_secret file on Windows 7

MySQL Replication: How to restore master FROM slave

Related Question