=== Installation ===
To get started, [https://access.redhat.com/site/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Managing_Confined_Services/chap-Managing_Confined_Services-MySQL.html installing MySQL on RHEL] is as simple as <source lang="bash">yum install mysql-server</source>. However, you might get a message that your system is not registered to Red Hat Subscription Management (RHN in the old days), and thus the packages are not visible. To remedy this, you would of course use <code>subscription-manager</code> to register the host with Red Hat Subscription Management.
</source>
== Create a DB and user ==
<source lang="sql">
CREATE DATABASE mediawiki;
</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">
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? ==
Want to find out what's going on in your MySQL server, but don't want to install client tools and access? Just make sure that the General Query Log is turned on, and tail the log file:
<source lang="sql">
If you want more info than you can get from the direct query logging, then try [http://mtop.sourceforge.net/|mtop]
== Show grants ==
<code>SHOW GRANTS</code> only shows privs for the current user.
<source lang="sql">
</source>
== MariaDB Differences between Ubuntu Fixing CHARSET and Debian COLLATION==Note: When dealing with MariaDB, they have this [https://mariadb.com/kb/en/library/differencescharacter-inset-mariadband-incollation-debian-and-ubuntuoverview/ KB Overview]. For MySQL, there is a dedicated [https://forums.mysql.com/list.php?103 forum for Character Sets, Collation, Unicode]
If Collation is a big word that we don't use everyday. It helps to remember that it basically means 'sorting'. Throwback: when printing documents in the office was a big deal (before the Internet), you'd have to pay extra to get huge sorting trays attached to the printer so that you could print '20 copies of this 5-page document' and each set of 5 pages would be printed into it's own tray. Now collating copiers are the norm and they do it without the massive tray finishers. ===OS-dependent defaults===MariaDB is different in Ubuntu vs. Debian<ref>https://mariadb.com/kb/en/library/differences-in-mariadb-in-debian-and-ubuntu/</ref> (and CentOS / Rocky Linux seem more like Ubuntu than Debian). Debian does the 'right thing' and sets defaults to be UTF-8 compatible, whereas others are still lagging with defaults of <code>latin1</code> for character set (and <code>latin1_swedish_ci</code> collation)!! Debian also enables the <code>unix_socket</code> authentication plugin by default for MariaDB <= 10.4.This allows passwordless login. See the reference for more details. ===Best practice==={{Ambox|text=Get into the habit of specifying CHARACTER SET and COLLATION on all connections and CREATE TABLEs. MySQL and MariaDB are gradually changing from latin1_swedish_ci to utf8mb4_0900_ai_ci. "900" is probably not the last Unicode standard. By explicitly specifying the charset and collation, you maintain control and consistency, even if it is an out-dated pair.}} ===Changing Character Sets and Collations===Character sets and collations can be set from the server level right down to the column level, as well as for client-server communication. For example, the <code>[https://mariadb.com/kb/en/server-system-variables/#collation_connection collation_connection]</code> '''server system variable'''. There is another KB page for [https://mariadb.com/kb/en/setting-character-sets-and-collations/ Setting Character Sets and Collations]. Keep in mind that there a many possible collations per character set, but only one default per character set. So, if you specify a character set, but are silent on the collation, youwill receive the default. Character sets and collations always cascade down, so a column without a specified collation will look for the table default, the table for the database, and the database for the server. It're s therefore possible to have extremely fine-grained control over all the character sets and collations used in your data. Collation names always start with the character set they correspond to (e.g. latin1 charset and latin1_bin collation). If your collation and character sets are all <code>latin1_swedish</code>, but you want them to be <code>UTF-8</code>, then you can set it in the configuration file, and restart the database.
<source lang="mysql">
show GLOBAL VARIABLES LIKE 'character_set_%';
</pre>
Then restart mysql/mariadb:
*<code>service mariadb restart</code> for Debian/Ubuntu;
*<code>/bin/systemctl restart mariadb.service</code> for Fedora/Centos/RHEL
<br /> ===Changing defaults===Depending on what version of MySQL or MariaDB you are using, it can be difficult at best to change the default collation '''for a given character set'''. <ref>https://dba.stackexchange.com/questions/239975/change-default-collation-for-character-set-utf8mb4-to-utf8mb4-unicode-ci</ref> The default relationship between character set and the collation for it will affect you when you use 'CREATE' or 'ALTER' with a 'CHARACTER SET' clause and the collation clause is left out. The admonition is to always specify both the character set and the collation in Connections as well as CREATE statements. Not to confuse things, but the default server-wide character set changed in MariaDB >= 10.6. The <code>character_set_server</code> changed from <code>utf8</code> to <code>utf8mb3</code> <ref>https://mariadb.com/kb/en/server-system-variables/#character_set_server</ref> Show the default collation '''for a given character set''': <code>SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8mb4' ;</code> On my CentOS server, the value is <code>utf8mb4_general_ci</code> and the same for Rocky Linux 8.9 For <code>CHARACTER_SET_NAME = 'binary'</code>, the collation is <code>binary</code> as no other collation is available, nor would make sense. == 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.
==Experts==Rick James https://mysql.rjweb.org/ Major Hayden https://major.io/tags/database/ https://github.com/major == Tools ==
Aside from [[mysqldump]], there are also mysqlcheck, mysql_upgrade and other [https://dev.mysql.com/doc/refman/5.7/en/programs-client.html client programs].
# MySQL Workbench is a great visual tool.# [https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md MySQLTuner]by the aforementioned Major Hayden (he made a Python version too)# 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.
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
; 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">
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://mariadb.com/kb/en/library/configuring-mariadb-for-optimal-performance# https://www.experts-exchange.com/questions/29060302/Tune-MySQL-to-32GB-RAM.html# https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/# https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/# 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/ ==MediaWiki==As of 2024-04-23 MediaWiki only requires MariaDB 10.3.x or MySQL 5.7.x for installation<ref>https://www.mediawiki.org/wiki/Manual:Installation_requirements#Database_server</ref> However, these are out of support. MariaDB is available in versions ranging from [https://mariadb.com/kb/en/changes-and-improvements-in-mariadb-10-4/ 10.4] - 10.11 and 11.0 - 11.5 The latest release in the 10.4 series is 10.4.33, released on 2024-02-07 Practically, this means that even if the database doesn't show errors, it could become vulnerable to attack or corruption at any time. And, tools like MySQLTuner, VSCode, PHPMyAdmin, etc. may not support the old version any longer. Starting in REL1_35, MediaWiki uses the [https://www.doctrine-project.org/projects/dbal.html Doctrine Database Abstraction Layer] (DBAL) <br />
== Footnotes ==<References references />
[[Category:Database]]