Hot answers tagged

53

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 ...


24

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

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 ...


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 ...


16

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


14

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 ...


14

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 ...


12

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 '...


11

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 ...


11

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 ...


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 ...


10

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 ...


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 ...


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

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

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 ...


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 ...


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 --...


7

You can login with (IP,Port Number) to remote server.By default the SQL Server don't log the logins. If you have pretty clean logs then you shall not get login history data. if you have configure SQL Login Audit before pretty clean log. Then some point of time there will be changes that to get the 'SQL Login Audit' details through TSQL like EXEC ...


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

I know that only root can write to /var/log ... You can change that. As root user (or with sudo) in the shell: mkdir /var/log/postgresql chmod 0700 /var/log/postgresql chown postgres:postgres /var/log/postgresql And in postgresql.conf: log_destination = 'stderr' logging_collector = on log_directory = '/var/log/postgresql' log_filename = '...


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 ...


6

You forgot one thing binlog-do-db=our_database binlog-ignore-db=mysql binlog-ignore-db=test replicate-do-db=our_database log-slave-updates The option log-slave-updates takes SQL that was read from relay logs and records them in the Slave Binary Logs when the SQL is completed. Please be very careful. You should never use binlog-do-db and binlog-...


6

If you don't have joins on this table, no updates and no deletions, then you don't need keys at all. If this is not the case, and messageId is unique, then you can make it a primary key. If it's not unique, but (messageId, exportedDateTime) is, then make this a composite primary key. If even the (messageId, exportedDateTime) combination can give ...


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