… delete one of MySQL‘s log or data files from disk. The database server will continue to function, however, because the file isn’t really deleted… lose your data permanently. The pmp-check-mysql-deleted-files plugin will alert when files have been deleted but remain open via a filehandle. Wrong…
Post: Be careful rotating MySQL logs
December 9, 2007 By 7 Comments
… – delete log file you do not need and run “FLUSH LOGS” so file is recreated and space reclaimed you can get into serious trouble. File close… deleting MySQL log file – rename it, call “FLUSH LOGS” which will be instant as it will not involve complex delete operation and when you can delete the log file…
Post: Binary log file size matters (sometimes)
May 24, 2012 By 6 Comments
…, new binary log is created and, if expire_logs_days is not zeroish, log files older than expire_logs_days are removed. &LOCK_log mutex is… file system is slow at deleting files (both customers systems ran on ext3, which can be very slow deleting large files), then write operations in MySQL will suffer since that very same mutex is used inside MYSQL_LOG::write() call…
Post: Choosing proper innodb_log_file_size
July 3, 2006 By 12 Comments
… innodb_log_file_size is important for MySQL Performance. However setting it too large will increase recovery time, so in case of MySQL crash… and why large innodb_log_file_size slows down recovery. During startup after crash Innodb scans log files to find log records which only have… be slower with small log files. Undo phase takes considerable time if tranactions are long – Ie if you would delete 10000000 rows in…
Post: How would you compress your MySQL Backup
June 5, 2008 By 29 Comments
… resources which may not be available, especially for CPU bound MySQL Load. The benefit in this case is less space requirement… note the serious leap in compressed file size. Though in this example we used MySQL binary log file which often contains plenty of similar… I delete this particular file. BZIP compression for this file took 298 seconds which is just 3.4 MB/sec though compressed file was…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… mysql -e ‘select * from flexviews.binlog_consumer_status\G’ -uroot *************************** 1. row *************************** server_id: 999 master_log_file: binary_log.000001 master_log_…2 Duplicates: 0 Warnings: 0 And delete data in a second transaction: mysql> delete from test.demo where c1=1…
Post: Filling the tmp partition with persistent connections
August 6, 2012 By 14 Comments
… which files has MySQL opened: # lsof -p 6112|grep ML mysqld 6112 root 38u REG 7,0 106594304 18 /tmp/MLjw4ecJ (deleted) mysqld… /tmp/ML4VPBxK (deleted) mysqld 6112 root 62u REG 7,0 62390272 26 /tmp/ML19sFCA (deleted) There are lot of files in deleted status that… or LOAD DATA INFILE MySQL send the rows to the Binary Log Cache before writing it to the Binary Log Files. If that cache…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…mysql> delete from salaries where emp_no = 10001; Query OK, 17 rows affected (0.15 sec) mysql…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
…-file=/etc/my.cnf –export /tmp/ Then apply the logs to get a consistent backup: # innobackupex –defaults-file=/etc/my.cnf –apply-log…03-10 | | 10008 | 52668 | 2000-03-10 | 2000-07-31 | +——–+——–+————+————+ mysql> DELETE FROM salaries WHERE emp_no=10008; The next step …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… to delete the records of the employee 10008 from the table “salaries”. After the “accidental” deletion of rows you should stop MySQL, take a copy of the salaries.ibd and start it again. Later, we’ll extract those deleted rows from the ibd file… with tables and indexes (with their IDs) to the error log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB…
