MySQL

Installation

To get started, installing MySQL on RHEL is as simple as

yum install mysql-server

. 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 subscription-manager to register the host with Red Hat Subscription Management.

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h `hostname` password 'new-password'
# but it's easier and better to run
/usr/bin/mysql_secure_installation
# which will give you the option of removing the test database and removing the anonymous user created by default.  This is a must for production servers.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd /usr/mysql-test ; perl mysql-test-run.pl

It's a good idea to setup a local configuration file for the system 'root' user, who can connect to the database server as db user 'root' by default when using the mysql command line client (ie. when you are user 'root' and type 'mysql' at the command line in a bash shell)

cat /root/.my.cnf

[client]
user=root
password=giveMeSomeDBgoodne55

Create a DB and user

CREATE DATABASE mediawiki;
GRANT ALL PRIVILEGES ON mediawiki.* TO 'example_user'@'localhost' IDENTIFIED BY 'secretpass';

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 to use.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secretpass';

Note: Since the introduction of Pluggable Authentication in MySQL 5.7 and also MariaDB since 5.2, there is a "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

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:

SHOW VARIABLES LIKE "general_log%";

+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+

mysql> SET GLOBAL general_log = 'ON';

Then you can tail the log file to see what it's doing:

tail -f /var/run/mysqld/mysqld.log

Be sure to turn off the log or your disk will fill up and you will not be happy.

mysql> SET GLOBAL general_log = 'OFF';

If you want more info than you can get from the direct query logging, then try [1]

Show grants

SHOW GRANTS only shows privs for the current user.

show grants;
show grants for 'root'@'localhost';

But this bash one-liner will show you all grants. -B --batch disables interactive behavior and doesn't use the history file -N --skip-column-names -e --execute executes a command and quits (disables --force and history file.)

mysql -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | mysql

Fixing CHARSET and COLLATION

Note: When dealing with MariaDB, they have this KB Overview. For MySQL, there is a dedicated forum for Character Sets, Collation, Unicode

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 on RedHat distros and Ubuntu vs. Debian. Debian does the 'right thing' and sets defaults to be UTF-8 compatible, whereas others are still lagging with defaults of latin1 for character set and latin1_swedish_ci collation!!

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 collation_connection server system variable. There is another KB page for 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, you will 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'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 latin1_swedish, but you want them to be UTF-8, then you can set it in the configuration file, and restart the database.

show GLOBAL VARIABLES LIKE 'character_set_%';
show VARIABLES LIKE 'collation%';

# Variable_name, Value
'collation_connection', 'utf8_general_ci'
'collation_database', 'latin1_swedish_ci'
'collation_server', 'latin1_swedish_ci'

This is the mess that I wound up with somehow:

SET @DATABASE_NAME = 'wiki_freephile';
SELECT 
    TABLE_SCHEMA,
    COUNT(COLLATION_NAME) AS 'count',
    COLLATION_NAME
FROM
    information_schema.columns AS cols
WHERE
    table_schema = @DATABASE_NAME
GROUP BY COLLATION_NAME;

wiki_freephile, 0, 
wiki_freephile, 11, binary
wiki_freephile, 73, latin1_bin
wiki_freephile, 69, latin1_swedish_ci
wiki_freephile, 1, utf8_general_ci

So, I fixed the defaults for each database:

ALTER DATABASE wiki_freephile CHARACTER SET = 'binary' COLLATE = 'BINARY';
ALTER DATABASE wiki_meta CHARACTER SET = 'binary' COLLATE = 'BINARY';
ALTER DATABASE wiki_wiki CHARACTER SET = 'binary' COLLATE = 'BINARY';

You can check the status of each table too:

SET @DATABASE_NAME = 'wiki_freephile';
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM    information_schema.tables
WHERE   TABLE_SCHEMA = @DATABASE_NAME
AND TABLE_COLLATION not in ('binary');

To set the mysql server to utf8 , add the following to /etc/mysql/my.cnf (Debian/Ubuntu) or /etc/my.cnf (Fedora/Centos/RHEL) under the [mysqld] section:

character-set-server = utf8
collation-server = utf8_general_ci
skip-character-set-client-handshake

Then restart mysql/mariadb:

• service mariadb restart for Debian/Ubuntu;
• /bin/systemctl restart mariadb.service for Fedora/Centos/RHEL

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. 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 character_set_server changed from utf8 to utf8mb3

Show the default collation for a given character set:

SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME = 'utf8mb4' ;

On my CentOS server, the value is utf8mb4_general_ci and the same for Rocky Linux 8.9

For CHARACTER_SET_NAME = 'binary', the collation is binary 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 -s --silent or -B --batch 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 client programs.

1. MySQL Workbench is a great visual tool.
2. MySQLTuner by the aforementioned Major Hayden (he made a Python version too)
3. 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 

-- 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`;

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 SHOW SLAVE STATUS\G

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 Relay_Master_Log_File just to be safe. Go back to the DB master and issue PURGE BINARY LOGS TO mysql-bin.000337 where mysql-bin.000337 is the value of Relay_Master_Log_File

Persistence 

To persist this setup so your data directory doesn't fill up again, issue

SET GLOBAL expire_logs_days = 3;

To put the setting into effect immediately. And for the next time MySQL is restarted, add to /etc/my.cnf

[mysqld] 

expire_logs_days=3

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

1. https://mariadb.com/kb/en/library/configuring-mariadb-for-optimal-performance
2. https://www.experts-exchange.com/questions/29060302/Tune-MySQL-to-32GB-RAM.html
3. https://www.tecmint.com/mysql-mariadb-performance-tuning-and-optimization/
4. https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/
5. https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/
6. 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 However, these are out of support. MariaDB is available in versions ranging from 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.