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 'secret';

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

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.

Tools

Aside from mysqldump, there are also mysqlcheck, mysql_upgrade and other client programs.

1. MySQL Workbench is a great visual tool.
2. MySQLTuner
3. http://mysqlsandbox.net/index.html MySQL sandbox could be useful for playing around with multiple databases in development.

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/