Please do not just delete them in the OS.
You need to let mysqld do that for you. Here is how mysqld manages it:
The file mysql-bin.[index]
keeps a list of all binary logs mysqld has generated and auto-rotated. The mechanisms for cleaning out the binlogs in conjunction with mysql-bin.[index]
are:
PURGE BINARY LOGS TO 'binlogname';
PURGE BINARY LOGS BEFORE 'datetimestamp';
These will clear all binary logs before the binlog or timestamp you just specified.
For example, if you run
PURGE BINARY LOGS TO 'mysql-bin.000223';
this will erase all binary logs before mysql-bin.000223
.
If you run
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;
this will erase all binary logs before midnight 3 days ago.
If you want to have binlog rotated away automatically and keep 3 days woth, simply set this:
mysql> SET GLOBAL expire_logs_days = 3;
then add this to /etc/my.cnf
[mysqld]
expire_logs_days=3
and mysqld will delete them logs for you
SHOW SLAVE STATUS\G
This is critical. When you run SHOW SLAVE STATUS\G
, you will see two binary logs from the Master:
Master_Log_File
Relay_Master_Log_File
When replication has little or no lag these are usually the same value. When there is a lot of replication lag, these values are different. Just to make it simple, choose whatever Relay_Master_Log_File
is, and go back to the Master and run
PURGE BINARY LOGS TO 'Whatever Relay_Master_Log_File Is';
That way, replication is not interrupted.
I've done that but I've still sometimes had it prompt to reinitialize the whole thing.
The snapshot will only contain the new table that has been added.
Also, we frequently remove a table from replication but need to add it back in later, are there scripts out there to preserve the replication settings so this process is easier?
Script out replication - right click - script to file. Change the parameters @immediate_sync
and @allow_anonymous
should be set to 0
.
Run the script post completion of your work.
Also, check my answer for Transactional replication altering tables and adding stored procedures that will help when you are adding columns to existing replication.
Best Answer
Yes, you can drop the indexes on the subscriber if no one is using them. Better yet, remove them from the publication article, or create a post snapshot script to drop whatever indexes you want. Either way on next initialization, the indexes will not be created, or they will be dropped.