-------------------------------------------------------------------------------
Commands to learn to use databases...

mysql  --user {username}  --host {network_name}  --password
Password: *****

  SHOW DATABASES;             What database (table sets) you have access to.
  USE {database};             Work in this database set
  SHOW TABLES;                What tables are in this database
  DESCRIBE {table};           What columns and types are in this table
  SHOW CREATE TABLE {table};  The command to use to re-recreate the table
                              (does not show REFERENCES comments)

  # Change your own password (also see below)
  SET PASSWORD = 'new-password';
  ALTER USER 'root'@'%' IDENTIFIED BY 'new-password';
  ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

--------
List Accounts on server
  USE mysql;
  # General list...
  SELECT User,Host FROM user;
  # MySQL 8
  SELECT User,Host,plugin,authentication_string FROM user;
  # MySQL 5 ...
  SELECT User,Host,plugin,password FROM user;


List 'known databases' and the users with access...
  show databases;
  use mysql;
  SELECT Db,User FROM db;

--------
Create Table

  use {your database}
  create table {table} ({name} {type},... );

to modify you need to use    alter table ....

Add data to tables....

  load data
  insert

List Table Index
  show indexes from {table}

---------
Uploading SQL datafiles

  mysql -h [host} -u {user} -p {database}  < file.sql

OR if a 'login path' has been setup (see below)

  mysql --login-path={path} < file.sql

OR from interactive mysql

  mysql> use db_name;
  mysql> source file.sql;

To speed up the upload time of large SQL files turn off autocommit.
(you can do this in the SQL file too).

  mysql> set autocommit=0;  source file.sql;  commit;

-------------------------------------------------------------------------------
Administer

Initialise MySQL.

  https://dev.mysql.com/doc/refman/8.0/en/postinstallation.html

  Start Mysql for the first time on a completely empty data directory.
    systemctl stop mysqld

    datadir=$( grep -oP '^datadir=\K.*' /etc/my.cnf.d/mysql-server.cnf )
    echo $datadir
    ls -Fla $datadir
    rm -r $datadir/*
    rm -r /var/lib/mysql/*   # precaution
    :>/var/log/mysql/mysqld.log

  Start MySQL
    systemctl start mysqld

  Check it is working (uses anonymous login)
    mysqladmin version

  Login as root (add -p if a password was set)
    mysql -u root

  Root password is empty under RHEL (initialised insecure)
  But if it was set, you can get it using...
    grep -oP ' root@localhost: \K.*' /var/log/mysql/mysqld.log

  If you do not know the root password, edit the "my.cnf" files
  and add  "skip-grant-tables" under "[mysqld]".  Restart mysql
  and then set password (see next).  Remove this afterwards

  Set password (newer sha2 password style)
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';

  Create remote root
    CREATE USER 'root'@'%'
           IDENTIFIED WITH mysql_native_password BY 'new-password';
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

Accounts...

  Create a User (with access from older clients)
    CREATE USER 's106441'@'%'
           IDENTIFIED WITH mysql_native_password BY 'password';
    FLUSH PRIVILEGES;

  Or using a hashed string
    CREATE USER 's106441'@'%'
           IDENTIFIED WITH mysql_native_password AS 'password_hash';

  Creating a Database and grant normal user access
    CREATE DATABASE s106441db;
    GRANT ALL ON s106441db.* TO 's106441'@'%';
    FLUSH PRIVILEGES;

  List Accounts
    SELECT user,host FROM mysql.user;
  For MySQL v5  you can add the  field 'password'
    SELECT user,host,password FROM mysql.user;
  For MySQL v8 add fields 'plugin,authentication_string'
    SELECT user,host,plugin,authentication_string FROM mysql.user;


  COPY password from one user to another (OLD)
    UPDATE mysql.user
    SET password = (
        SELECT password FROM mysql.user
        WHERE user='root' AND password <> ''
      )
      WHERE user='root' AND password = '';
    FLUSH PRIVILEGES;

  Delete Database
    DROP DATABASE s106441db;
    DELETE FROM db WHERE Db='$database';

  Delete User
    REVOKE ALL PRIVILEGES, GRANT OPTION FROM 's106441'\@'%';
    DROP USER 's106441'\@'%';
    FLUSH PRIVILEGES;

-------------------------------------------------------------------------------
Anonymous users and other user problems

Sometimes you will login using an anonymous user rather than the current user.

To see anonymous users run this query:

   use mysql;
   SELECT user,host FROM mysql.user WHERE user='';

To see that you authenticated as such, run this:

  SELECT USER(),CURRENT_USER();

This will show how you tried to login and how mysql allowed you to login.

To fix... Run these two queries:

   DELETE FROM mysql.user WHERE user='';
   FLUSH PRIVILEGES;

That should do it !!!

Something else to watch out for is having multiple root users.

  SELECT user,host FROM mysql.user WHERE user='root';

You may have entries like this...

  root@localhost
  root@127.0.0.1
  root@server-host-name

mysql may authenticate against ANY of these users!

-------------------------------------------------------------------------------
Security

MySQL Recommendation...

Replace the options...
   --user=???  --password=???? --host=???
with
   --login-path=????

Where login-path refers to a prepared login details stored in a
encrypted file which only the current user has file access too...
For example:  "$HOME/.mylogin.cnf"

The command "mysql_config_editor" is used to create and control
this encrypted configuration file.

Create a login path...

   mysql_config_editor set --login-path=local \
      --host=localhost --user=root --password

To see available login paths (password is never shown)

   mysql_config_editor print --all

To use...

   mysql --login-path=local ....

   mysqldump --login-path=local ....

Reference
  http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

NOTE: After it is set up the password is NOT needed to be stored or used 'in
the clear'. It is up to the DBA's to keep a track of passwords started in
these secure files.

I do not know how the password is stored in the file, or even if the original
password can even be extracted from the file.


ALTERNATIVE 1

Instead of the encrypted ".mylogin.cnf" you can use a
plain text credentials file

  /root/mysql-credentials.cnf
    [client]
    user=root
    password=the_password
    host=localhost

  mysql --defaults-file=/root/mysql-credentials.cnf ....

WARNING: the password will be visible to anyone that can read the file,
But it is still better than being visible in "ps" (process listing).


ALTERNATIVE 2...

Put password in environment variable  MYSQL_PWD

  MYSQL_PWD="passwd" mysql --host=mysql.server --user=username ...

WARNING: The password will be visible in a "ps" listings, and environment
variables cannot be removed by the running process!  Not that mysql even
attempted to do that.


ALTERNATIVE 3...

Give password via a password program to a non-interactive mysql command

  sshpass -p secret | mysql -u root --password -e 'statement'

Or use expect to feed the mysql command, and continue interactively

  expect -c "
    spawn mysql --host=localhost --user=root --password
    expect -nocase \"Enter password:\" {send \"secret\r\"; interact}

ALTERNATIVE 4...

This is the same as ALTERNATIVE 1 but the password is provided to the
mysql command by the use of named pipes and special BASH features.

  mysql --defaults-file=<(`
            printf "[client]\nuser = %s\npassword = %s" "$user" "$pwd"\
        ) -e "statement"

Since the printf is a builtin within BASH it doesn't show up in "ps" and so the
password is safe.  Except the command line is saved into ".bash_history".

Of course the script still needs to get that password from somewhere, so the
script needs to be secured or the source of the password the script uses needs
to be secure.

-------------------------------------------------------------------------------
Stopping a 'home sub-directory' being thought of as databases

The problem is that automysqlbackup reports...
  mysqldump: Incorrect database name

For example....
   mysqldump: Got error: 1102: "Incorrect database name '#mysql50#.cache'"
   when selecting the database

These are caused by non-database directories appearing in the mysql database
directory.  The MySQL database directory is typically given on the mysqld
daemon command (do a 'wide' process listing), and is typically but not always
in "/var/lib/mysql".  It may also be defined in the "my.cnf" configuration
files.

Note that the "abrt" program generates directories in a users home!  As such
for RH7 mysql servers, directories like ".config" and ".cache" will often
re-appear!

You can see the server thinks these directories are databases (when they
aren't) ...

  mysql --user root --host localhost --password
  Enter password:
  ...
  MariaDB [(none)]> show databases;
  MariaDB [(none)]> use mysql;
  MariaDB [mysql]> SELECT Db,User FROM db;

You will see the database is listed as existing, but no user (other than root)
has access to that database.  That is the database is junk.


There are 3 solutions...
Typically I do ALL three to ensure it does not happen again!


1/Delete the offending directories!

  If the sub-directory is empty or does not contain .MYD, .MYI, or .frm files
  then it is probably not a valid database.  And not listed properly in the
  above output.

  You can just delete the sub-directories.   This is the case for ".cache" and
  ".config" sub-directories (created by the 'abrt' system).  However it is
  likely that they will eventually re-appear (maybe).

     rm -r /var/lib/mysql/.cache
     rm -r /var/lib/mysql/.config

  They simply should just NOT exist in this directory!  First they are for
  'user home' directories not application homes,  and second '.' and '#' are
  not allowed in database names.


2/Tell MySQL Server to ignore them -- PREFERRED SOLUTION
  That is tell it they are not databases and it should nto list them.
  This fixes the cause of the error.

  Add directory to the mysql configuration "my.cnf" file in the "[mysqld]"
  section.. And restart MySQL server...

    vi /etc/my.cnf.d/server.cnf
      # ignore certain directories which are NOT mysql databases
      ignore-db-dir='lost+found'
      ignore-db-dir='.cache'
      ignore-db-dir='.config'
      ignore-db-dir='.ssh'

  After the server had been restarted the following will show if the options
  has taken effect...

    show global variables like 'ignore_db_dirs';

  This is now being used on na-prd-nagios and is set specifically in
  "/etc/my.cnf.d/server.cnf"  or
  "/etc/opt/rh/rh-mysql56/my.cnf.d/mysql-server.cnf"


3/Modify dump script, to prevent it reporting an error.
  But getting the "automysqlbackup" script to ignore these 'databases' and
  stop it from trying to back them up, does not appear to be a simple matter.

  All attempts to use $DBEXCLUDE fails! -- I have no idea why!
  #  DBEXCLUDE="information_schema performance_schema"
  #  DBEXCLUDE="$DBEXCLUDE #mysql50#.cache #mysql50#.config"

  If this is to be done it may be better to do it programmically
    if [ "$DBNAMES" = "all" ]; then
      DBNAMES="`mysql --user=$USERNAME --password=$PASSWORD --host=$DBHOST \
                      --batch --skip-column-names -e "show databases" |
                  sed 's/ /%/g;/^#mysql/d'`" # clean and remove 'dot' sub-dirs


POST CLEANUP: After applying ANY of the above actions the database backups of
these directories should also be removed from whereever the automysqlbackup
scipt saves them.

For example
   rm -r /store/backups/mysql/*/\#*


-------------------------------------------------------------------------------
mysqldump: Got error: 23: Out of resources when opening file

1/
  From https://blog.hostonnet.com/mysql-out-of-resources-when-opening-file

  Into the appropriate "my.cnf" files add..

  Check maximum allowed files
    cat /proc/sys/fs/file-max
    cat /proc/sys/fs/file-nr

  Change file-max (if needed)
    vi /etc/sysctl.conf
      fs.file-max=65536
    sysctl -a

  =======8<--------
  [mysqld_safe]
  log-error=/var/log/mysqld.log
  pid-file=/var/run/mysqld/mysqld.pid
  open_files_limit = 50000
  =======8<--------

  Restart server and check

    mysql> show global variables like 'open%';

I can see this on elemental, and yet it is on the mysql command line!

-------------------------------------------------------------------------------
Safe snapshots of MySQL tables (for backups) on LVM systems

#!/usr/bin/perl
#
# On a LINUX LVM system, get mysql to flush and do a LVM snapshot
# before making a backup of the mysql snapshot
#
#

$SnapDir="/snapshots/mysql";
$SnapDev="/dev/asylum/mysql_snapshot";
$SnapSize="128M";
$MySQLDev="/dev/asylum/mysql";

$OnOff=$ARGV[0];    # Are we creating or removing a mysql table snapshot
chomp $OnOff;

if ($ARGV[1] eq "-v" || $ARGV[1] eq "-v\n") {
  $Verbose="y";
  $MountParams="-v -t ext4 -o ro,noatime,nodiratime,noexec,nosuid,nodev,barrier=0";
  $UMountParams="-v";
} else {
  $Verbose="n";
  $MountParams="-t ext4 -o ro,noatime,nodiratime,noexec,nosuid,nodev,barrier=0";
  $UMountParams="";
}

if ($OnOff eq "on") {

  # connect mysql ROOT
  use DBI;
  $DB=DBI->connect("DBI:mysql:mysql" . ";mysql_read_default_file=/root/.my.cnf", root, $password)
    or die "Could not connect to MySQL\n";

  # flush and lock all tables
  $DB->do("flush tables with read lock;")
    or die "Can't flush and lock tables: ",$DB->errstr,"\n";

  # create the LVM snapshot device
    system ("lvcreate --size $SnapSize --snapshot --name mysql_snapshot $MySQLDev > /dev/null 2>&1") == 0
      or die "Can't create snapshot: $?\n";

  # unlock the tables so MySQL can continue serving clients
  # After this we don't have to talk to MySQL at all anymore.
  $DB->do("unlock tables");
  $DB->disconnect;


  # mount up the snapshot volume
  system ("mount $MountParams $SnapDev $SnapDir") == 0
    or die "Unable to mount snapshot: $?\n";


} elsif ($OnOff eq "off") {

  # Unmount the snapshot
  system ("umount $UMountParams $SnapDir") == 0
    or die "Can't unmount snapshot: $?\n";

  # Delete the snapshot volume
  system ("lvremove --force $SnapDev > /dev/null 2>&1") == 0
    or system ("lvremove --force $SnapDev > /dev/null") == 0
      or die "Can't remove the snapshot volume: $?\n";

} else {
  die "Invalid parameter used on command line.\n";
}

-------------------------------------------------------------------------------
MySQL Swap space insanity

The problem we are seeing on na-prd-ownclouddb-0
where mysql just gobbles all swap space, while memory is free...
  The MySQL “swap insanity” problem and the effects of the NUMA architecture
    https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Essentially add a NUMA process configuration command as part of running mysql.
Look for "A small change, to big effect" if you feel TLDR

Also an small follow up
  A brief update on NUMA and MySQL.
    https://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/
which also adds a extra 'pre-command' to reset NUMA before hand.

-------------------------------------------------------------------------------
Binlog Purging

Mysql directory is full of binlogs, which are used for slave replication.
WARNING: you can't simply delete files as "binlog.index" keeps a list of the
files that are present.

Login to mysql and run
   PURGE BINARY LOGS BEFORE NOW();
which will purge all but the last one.

Erase all binary logs before last midnight
  PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND;

Erase all binary logs before midnight 3 days ago
  PURGE BINARY LOGS BEFORE DATE(NOW()) + INTERVAL 0 SECOND - INTERVAL 3 DAY;

To get binlog rotating automatically set this in the approopriate "my.cnf"
  [mysqld]
  expire_logs_days=3
Then login and run in mysql
  SET GLOBAL expire_logs_days=3;

You can also try
  RESET MASTER;
Which deleted all binary logs listed in the index file, and creates a new empty
one.  It should only be used while there are no replication slaves running.
  https://dba.stackexchange.com/questions/47046

This also is supposed to remove binlogs that are older than 3 days
   mysqladmin -u username -p flush-logs
As this is part logrotate (if set up), then it can handle this

-------------------------------------------------------------------------------