=== 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==See the article about [[Collation]]==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://mariadbmysql.comrjweb.org/ Major Hayden https://kbmajor.io/entags/librarydatabase/differences-in-mariadb-in-debian-and-ubuntuhttps://github.com/major
If you're collation ==Tools==Aside from [[mysqldump]], there are also mysqlcheck, mysql_upgrade and character sets are all latin1_swedishother [https://dev.mysql.com/doc/refman/5.7/en/programs-client.html client programs]. #[https://www.mysql.com/products/workbench/ MySQL Workbench] is a great visual tool, but behind several obstacles and Oracles.#[https://dbeaver.io/ DBeaver]#[https://github.com/major/MySQLTuner-perl/blob/master/INTERNALS.md MySQLTuner] by the aforementioned Major Hayden (he made a Python version too)#[https://www.percona.com/software/database-tools/percona-toolkit Percona Toolkit] <code>wget <nowiki>https://downloads.percona.com/downloads/percona-toolkit/3.5.7/binary/redhat/8/x86_64/percona-toolkit-3.5.7-1.el8.x86_64.rpm</nowiki></code> <code>dnf install percona-toolkit-3.5.7-1.el8.x86_64.rpm</code> <code>pt-mysql-summary</code>#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 want rotate them . Information below comes from https://dba.stackexchange.com/questions/41050/is-it-safe-to be UTF-8, then you can set delete-mysql-bin-files '''Don't''' just delete the files. Use MySQL to do it in . Either pass the configuration name of a particular file, or a timestamp and restart MySQL will clear out the database.binary logs up to the one just specified (or the date) ;Example :
<source lang="mysql">
show GLOBAL VARIABLES LIKE 'character_set_%'-- consolidate the logs up to midnight 3 days agoPURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 3 DAY) + INTERVAL 0 SECOND;show VARIABLES LIKE 'collation%'-- consolidate the logs up to the file specifiedPURGE BINARY LOGS TO `mysql-bin.000223`;
</source>
<pre>
# Variable_name, Value
'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'
</pre>
This ;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 mess that I wound 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 with somehow:again, issue
<source lang="mysql">
SET @DATABASE_NAME GLOBAL expire_logs_days = 'wiki_freephile';SELECT TABLE_SCHEMA, COUNT(COLLATION_NAME) AS 'count', COLLATION_NAMEFROM information_schema.columns AS colsWHERE table_schema = @DATABASE_NAMEGROUP BY COLLATION_NAME3;
</source>
To put the setting into effect immediately. And for the next time MySQL is restarted, add to <prett>wiki_freephile, 0, wiki_freephile, 11, binarywiki_freephile, 73, latin1_binwiki_freephile, 69, latin1_swedish_ciwiki_freephile, 1, utf8_general_ci/etc/my.cnf</prett>So, I fixed the defaults for each database:<source lang="mysqlini">ALTER DATABASE wiki_freephile CHARACTER SET = 'binary' COLLATE = 'BINARY';[mysqld] ALTER DATABASE wiki_meta CHARACTER SET = 'binary' COLLATE = 'BINARY';ALTER DATABASE wiki_wiki CHARACTER SET = 'binary' COLLATE expire_logs_days= 'BINARY';3
</source>
You can check Since the status of each table too:<source lang="mysql">SET @DATABASE_NAME = 'wiki_freephile';SELECT TABLE_SCHEMAbinlongs are used to recover the database state after a crash, TABLE_NAMEthis assumes that you are making database backups every day. If you only backup once per week, TABLE_COLLATIONFROM information_schemayou would want at least 7 days worth of binlogs.tablesWHERE TABLE_SCHEMA = @DATABASE_NAMEAND TABLE_COLLATION not in ('binary');</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 = utf8collation-server = utf8_general_ciskip-character-set-client-handshake</pre>Then restart mysql/mariadb:*<code>service mariadb restart</code> for Debian/Ubuntu; *<code>/bin/systemctl restart mariadb.service</code> for Fedora/Centos/RHELTuning Reference==
== Working at the Console ==#https://mariadb.com/kb/en/library/configuring-mariadb-for-optimal-performance#https://www.experts-exchange.com/questions/29060302/Tune-MySQL-to-32GB-RAM.htmlReading output from the #https://www.tecmint.com/mysql command line client is notoriously ugly-mariadb-performance-tuning-and-optimization/#https:/hard/www. With the <code>percona.com/blog/10-mysql-performance-tuning-s settings-after-silent<installation/#https://www.percona.com/blog/2016/10/12/code> or <code>mysql-B 5-7-batch<performance-tuning-immediately-after-installation/#https://code> options, you can get output that is more readablewww.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
== Tools ==Aside from [[mysqldump]]Practically, there are also mysqlcheckthis means that even if the database doesn't show errors, mysql_upgrade and other [https://devit could become vulnerable to attack or corruption at any time.mysql.com/doc/refman/5.7/en/programs-clientAnd, tools like MySQLTuner, VSCode, PHPMyAdmin, etc.html client programs]may not support the old version any longer.
# MySQL Workbench is a great visual tool.# Starting in REL1_35, MediaWiki uses the [https://githubwww.com/major/MySQLTunerdoctrine-perl/blob/master/INTERNALSproject.md MySQLTuner]# http:/org/mysqlsandbox.netprojects/indexdbal.html MySQL sandbox could be useful for playing around with multiple databases in development.Doctrine Database Abstraction Layer] (DBAL)
== 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<br />
== Footnotes ==<References references />
[[Category:Database]]