<source lang="sql">
CREATE DATABASE mediawiki;
GRANT ALL PRIVILEGES ON mediawiki.* TO 'example_user'@'localhost' IDENTIFIED BY 'secretsecretpass';
</source>
== Set Password for Root user ==The following command issues a new password for the root user while at the same time dictating which authentication plugin <ref>https://mariadb.com/kb/en/library/authentication-plugin-pam/</ref> to use.<source lang="sql">ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secretpass';</source> Note: Since the introduction of [https://mariadb.com/kb/en/library/development-pluggable-authentication/ Pluggable Authentication] in MySQL 5.7 and also MariaDB since 5.2, there is an a "[https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/ unix_socket]" plugin which authenticates the current user by their login to the OS, and thus NOT a password. If you have a database with no user trouble setting the password, then youit'll s probably want because you need to change it back to regular password also specify the '''mysql_native_password''' auth at the same time <ref>https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/</ref>
== What's going on? ==
# [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.
== 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 ==