<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 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 trouble setting the password, it's probably because you need to 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? ==
</source>
To set the mysql server to utf8<ref>https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/</ref>, add the following to <code>/etc/mysql/my.cnf</code> (Debian/Ubuntu) or <code>/etc/my.cnf</code> (Fedora/Centos/RHEL) under the '''<nowiki>[mysqld]</nowiki>''' section:
<pre>
character-set-server = utf8
*<code>service mariadb restart</code> for Debian/Ubuntu;
*<code>/bin/systemctl restart mariadb.service</code> for Fedora/Centos/RHEL
<ref>https://mariadb.com/kb/en/library/setting-character-sets-and-collations/ https://scottlinux.com/2017/03/04/mysql-mariadb-set-character-set-and-collation-to-utf8/</ref>
== Working at the Console ==
Reading output from the mysql command line client is notoriously ugly/hard. With the <code>-s --silent</code> or <code>-B --batch</code> options, you can get output that is more readable.
# [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 ==
# https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/
# https://www.percona.com/blog/2009/11/16/table_cache-negative-scalability/
== Footnotes ==
<References />
[[Category:Database]]