MySQL: Difference between revisions
→Create a DB and user: Add reference about auth plugins |
Add info about expiring MySQL binary logs |
||
| Line 145: | Line 145: | ||
# [https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md MySQLTuner] | # [https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md MySQLTuner] | ||
# http://mysqlsandbox.net/index.html MySQL sandbox could be useful for playing around with multiple databases in development. | # http://mysqlsandbox.net/index.html MySQL sandbox could be useful for playing around with multiple databases in development. | ||
== Logs == | |||
Your data directory can fill up with binary logs unless you rotate them. | |||
Information below comes from https://dba.stackexchange.com/questions/41050/is-it-safe-to-delete-mysql-bin-files | |||
'''Don't''' just delete the files. Use MySQL to do it. | |||
Either pass the name of a particular file, or a timestamp and MySQL will clear out the binary logs up to the one just specified (or the date) | |||
; Example : | |||
<source lang="mysql"> | |||
-- consolidate the logs up to midnight 3 days ago | |||
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND; | |||
-- consolidate the logs up to the file specified | |||
PURGE BINARY LOGS TO `mysql-bin.000223`; | |||
</source> | |||
; Which File to choose?: | |||
If you want to know which log files are in use so that you can remove superfluous ones without interrupting replication, go to your slave database and issue <tt>SHOW SLAVE STATUS\G | |||
</tt> | |||
You'll see entries for | |||
* Master_Log_File | |||
* Relay_Master_Log_File | |||
When replication has little or no lag these are usually the same value. If there is a lot of replication lag, they will be different values. Choose the value for <tt>Relay_Master_Log_File</tt> just to be safe. Go back to the DB master and issue <tt>PURGE BINARY LOGS TO mysql-bin.000337</tt> where mysql-bin.000337 is the value of <tt>Relay_Master_Log_File</tt> | |||
; Persistence : | |||
To persist this setup so your data directory doesn't fill up again, issue | |||
<source lang="mysql"> | |||
SET GLOBAL expire_logs_days = 3; | |||
</source> | |||
To put the setting into effect immediately. And for the next time MySQL is restarted, add to <tt>/etc/my.cnf</tt> | |||
<source lang="ini"> | |||
[mysqld] | |||
expire_logs_days=3 | |||
</source> | |||
Since the binlongs are used to recover the database state after a crash, this assumes that you are making database backups every day. If you only backup once per week, you would want at least 7 days worth of binlogs. | |||
== Tuning Reference == | == Tuning Reference == | ||