Yes it is safe to delete the log file once mysqld has been shutdown
In light of this, just perform the following steps:
mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start
Starting up mysqld will recreate ib_logfile0
and ib_logfile1
Give it a Try !!!
UPDATE 2011-10-20 16:40 EDT
It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:
SET GLOBAL innodb_max_dirty_pages_pct = 0;
By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop
does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:
[mysqld]
innodb_max_dirty_pages_pct = 0
UPDATE 2013-04-19 16:16 EDT
I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).
Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.
Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave
For this Example, let's assume
- IP of the Master is 10.1.1.20
- IP of the Slave is 10.1.1.30
Here is what you do
STEP01 : Activate Binary Logging on the Old Server
Step01-a) Add this to /etc/my.cnf on the Master
[mysqld]
server-id=101120
log-bin=mysql-bin
Step01-b) # service mysql restart
After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql
STEP02 : Perform mysqldump
On the Master, you can mysqldump the data and record from what point in time it happened.
# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart
What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:
# head -22 MySQLData.sql | tail -1
STEP03 : Load the mysqldump into the Slave
Execute the mysql client loading the mysqldump into the Slave's mysql instance
# mysql -h10.1.1.30 -uroot -p < MySQLData.sql
STEP04 : Create MySQL Replication User on the Master
# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"
STEP05 : Setup the Slave with a Separate Server ID
Add this to /etc/my.cnf on the Slave
[mysqld]
server-id=101130
and # service mysql restart
STEP06 : Setup the Replication on the Slave
Goto the mysql client and run the following command
mysql> CHANGE MASTER TO
MASTER_HOST='10.1.1.20',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1;
STEP07 : Setup the point-in-time Master Log and Position
Back in STEP02, I mentioned viewing the point-in-time position using
# head -22 MySQLData.sql | tail -1
You should see something like this:
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Run it as command in the mysql client on the Slave
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Afterwards, run this command
mysql> SHOW SLAVE STATUS\G
You should see something like this:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.113.232
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 122957100
Relay_Log_File: relay-bin.003666
Relay_Log_Pos: 122957100
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 106
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
STEP08 : Launch Replication
Start up replication with this:
mysql> START SLAVE;
Afterwards, run this command again
mysql> SHOW SLAVE STATUS\G
If you see this
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
CONGRATULATIONS, MySQL Replication is Working !!!
Best Answer
Here is an explanation on how
fdatasync()
works vs howfsync()
worksfdatasync()
flushes all data buffers of a file to disk (before the system call returns). It resemblesfsync()
but is not required to update the metadata, such as access time. Applications that access databases or log files often write a tiny data fragment (e.g., one line in a log file) and then callfsync()
immediately in order to ensure that the written data is physically stored on the harddisk. Unfortunately,fsync()
will always initiate two write operationsIf the modification time is not a part of the transaction concept, then
fdatasync()
can be used to avoid unnecessary inode disk write operations.In English,
O_DSYNC
is faster thanO_DIRECT
sinceO_DIRECT
callsfsync()
twice (one for logs and one for data) andfsync()
verifies data writes via two write operations. UsingO_DSYNC
callsfdatsync()
andfsync()
. You can think offdatasync()
as doing an asynchronousfsync()
(not verfying data).Looking at the numbers,
O_DSYNC
does four write ops, two of which are verified, whilefsync()
does four write operations, all being verfied afterwards.CONCLUSION
O_DSYNC
O_DIRECT
O_DIRECT
I hope this answer helps, and I hope I didn't make things worse for you.