Hot answers tagged

48

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here. The key part of the question is: I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf You can do this from an included option file using the option skip-log-...


31

I would instead recommend the official method, which I reproduce here for convenience: To change the number or the size of InnoDB log files in MySQL 5.6.7 or earlier, use the following instructions. The procedure to use depends on the value of innodb_fast_shutdown, which determines whether or not to bring the system tablespace fully up-to-date ...


21

You were close. Your last idea is actually the way to go: log_statement = none log_min_duration_statement = 10000 Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV. This related ...


18

There are 3 methods that MySQL can use to write to the binary logs: STATEMENT This means that every SQL statement on the master is recorded in the log, and executed on the slave. This can cause problems if the SQL statement contains statements such as "NOW()", "RAND()" and anything non-deterministic. This also requires support from the storage engine in ...


17

ibdata1 The file ibdata1 is the system tablespace for the InnoDB infrastructure. It contains several classes for information vital for InnoDB Table Data Pages Table Index Pages Data Dictionary MVCC Control Data Undo Space Rollback Segments Double Write Buffer (Pages Written in the Background to avoid OS caching) Insert Buffer (Changes to Secondary ...


13

Jeff, Brent here. We put a lot of documentation in that URL - make sure you copy/paste the URL into your web browser to read the full documentation. If you're in simple recovery model, and you've got log files larger than the database, there are lots of possible causes: Someone started a transaction, locked their workstation, and went home for the weekend ...


13

SQL Server's transaction log files are made up of Virtual Log Files (VLFs). When you shrink a log file, it will only release unused VLFs at the end of the file. There is always going to be at least one VLF that is being used for current activity. Consider this simplified example. You have a Log file that is made up of 6 equally sized VLFs. For simplicity, ...


12

Like almost everything else, it depends on your hardware, configuration and workload. If you're running few expensive statements it'll make little difference; if you're running lots of tiny simple statements it'll make more difference. If your logs are on the same file system or even just the same disk as the database, the logging will make more ...


11

It sounds like log_statement is set to all. If you wish to prevent passwords from appearing in the logs while log_statement is set to a value that captures ALTER USER / ALTER ROLE then you'll want to override that when changing passwords. e.g. BEGIN; SET LOCAL log_statement = 'none'; ALTER USER ... SET PASSWORD ...; COMMIT; You must be a superuser to do ...


11

The innodb_page_cleaners default value was changed from 1 to 4 in MySQL 5.7.8. If the number of page cleaner threads exceeds the number of buffer pool instances, innodb_page_cleaners is automatically set to the same value as innodb_buffer_pool_instances Check innodb_buffer_pool_instances with: mysql> SHOW GLOBAL VARIABLES LIKE '...


10

Should I remove the id field? I would recommend keeping it. You may not need the field now, but in the future, it can really help you out -- what if you need to store details of the files for each log entry? I don't know how large this table will get and how quickly, but adding a column to a large table is typically an expensive operation. If the table is ...


10

Why doesn't the log file shrink after my backups? Is it because there are uncommitted transactions? The actual NTFS log file doesn't "shrink" from a transaction log backup, but VLFs (Virtual Log Files) within the transaction log are marked for reuse (because they are now backed up and persisted on media) allowing the wrap-around of transaction log use to ...


10

You can backup the transaction log from either of the replicas. Doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable (provided no other stoppers are in place like active transactions, etc.). To do a test, in a non-production environment, setup an availability group ...


10

There's a chicken-and-egg problem there. PostgreSQL reads postgresql.conf to decide where to log, and how. So it cannot really log errors in postgresql.conf to the PostgreSQL logs, unless it uses some kind of fallback/default log. Instead, it uses the Windows Event Log, which is what Windows applications are supposed to do anyway. (You can have PostgreSQL ...


9

If database was in full recovery mode then there might be a chance to recover some data and get insights on what was done by reading transaction log. Unfortunately this is not supported by default but there are ways to do this. You can try using third party tools such as ApexSQL Log or SQL Log Rescue (free but SQL 2000 only). Another option is to try ...


9

There is no mysql option to ignore a table in binary log. If you could move the table to a separate database you might use binlog-ignore-db option, but this has its gotchas. You should understand how MySQL evaluates replication rules. If you can write a wrapper script to load the data instead of mysqlimport (the manpage says that it's a command line ...


9

You could use the general log. In fact, you should try using the MySQL table version of the general log. If you run this: SHOW CREATE TABLE mysql.general_log\G You should see something like this: mysql> show create table mysql.general_log\G *************************** 1. row *************************** Table: general_log Create Table: CREATE ...


9

In MySQL 8 it is the option disable_log_bin which has to be provided without any parameter in the my.cnf file in the [mysqld] section. [mysqld] disable_log_bin some-other-option = any-value


8

To rotate out logs more than 7 days old, add this to my.cnf [mysqld] expire-logs-days=7 then restart mysql. To perform this manually, run this command: mysql> PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY) + INTERVAL 0 SECOND; This will erase binary logs back to Midnight 7 days ago. WARNING !!! Do not erase binary logs from the OS level ...


8

There is no technical problem for SQL Server if the drive where the log file exists has no free space, assuming the log itself doesn't run out of available, unused, VLF entries. If the log runs out of available space, you'll not be able to commit any transactions until you resolve the problem. If the entire drive is consumed by the log file, the only ...


8

Well log backups don't change the size of the log file, and the only reason you'd need space on that drive is if you were backing up the log there (which is the same kind of bad idea as putting your house key and the backup on the same key ring). But a slightly different question, do you expect none of your log files to ever grow? Leaving a bit of space at ...


7

SELECT REPLACE(CAST(SERVERPROPERTY('ErrorLogFileName') AS nvarchar(512)), 'ERRORLOG', '') EDIT: Cleaned it up a bit with a little more googling to verify; Also to note that this particular argument "ErrorLogFileName" to the SERVERPROPERTY function is undocumented and therefore unsupported by Microsoft. Use at your own risk. etc. etc.


7

Take a step back and ask, why are we copying log files? SQL Server LDF files contain transaction log data, but they're not terribly useful by themselves. To work with a database, you need both the data files and log files. In order to access those files directly via the file system, though, you'll either need to stop SQL Server or use snapshot ...


7

AFAIK, the only way to do this is to use the WITH LOG option of the RAISERROR function. Note from that MSDN page that there are certain security requirements that must be met for you to do this. That said, the SQL Server error log really isn't meant for application-based logging. If you need to add monitoring/logging to your application, this should ...


7

1 - what is creating these files? Those files are called binary logs. They contain a list of all completed SQL statements you have executed. Your mysqld process created them and autorotates at 1GB. Any restart of mysqld or issuing FLUSH LOGS; will close one binary log and open another They got created because there is a line in my.ini that looks like this:...


7

Reading the documentation is in order. That comes from someone who just takes interest and never used Postgres and found the answer in the first google link: http://www.postgresql.org/docs/current/static/xfunc-volatility.html You may want to put particular emphasis on that part of stable: This category allows the optimizer to optimize multiple calls of ...


7

You could have InnoDB clean things up as follows: signal mysqld to have InnoDB flush everything out of the logs on shutdown shutdown mysqld delete the logs start mysqld No crash recovery will happen because there will be nothing to recover. MYSQL_USER=root MYSQL_PASS=rootpassword MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h127.0.0.1 -P3306 --protocol=...


7

What I am trying to understand is why this is occurring, and how to prevent it. Maintenance Plans do not have much logic that you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes. You are better off implementing Ola's backup solution as well as Index ...


7

Is the TCP port SQL Server is listening on open globally? If so, yes I'd be concerned. If there is a password that can be brute-forced or guessed, or an exploit that allows someone to bypass authentication, eventually your database could be compromised. You'll also be vulnerable to attacks that don't require access, such as someone filling up the drive that ...


Only top voted, non community-wiki answers of a minimum length are eligible