Memo for MySQL

MySQL.md

MySQL

Change root password

systemctl stop mysql
nohup mysqld_safe </dev/null &>/dev/null &
mysql -u root
mysql> use mysql;
# Before Mariadb 10.4
mysql> update user set password=PASSWORD('') where User='root';
# Starting from Mariadb 10.4
mysql> SET PASSWORD FOR 'root'@localhost = PASSWORD("");
mysql> flush privileges;
mysql> quit;

systemctl stop mysql
systemctl start mysql

Create user

Here is the SQL command to create a user and grant all privileges on database. The syntax with single quote drove me crazy!

CREATE DATABASE db;
CREATE USER 'user'@localhost IDENTIFIED BY 'pass';
GRANT ALL PRIVILEGES ON db.* TO 'user'@localhost;
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'user'@localhost;

Create user and database (script)

touch /usr/local/bin/mysql-createdb
chmod 755 /usr/local/bin/mysql-createdb
chmod +x /usr/local/bin/mysql-createdb

Edit the file, add this bash script

#!/bin/bash

EXPECTED_ARGS=3
E_BADARGS=65
MYSQL=`which mysql`

Q1="CREATE DATABASE IF NOT EXISTS $1;"
Q2="GRANT USAGE ON *.* TO $2@localhost IDENTIFIED BY '$3';"
Q3="GRANT ALL PRIVILEGES ON $1.* TO $2@localhost;"
Q4="FLUSH PRIVILEGES;"
SQL="${Q1}${Q2}${Q3}${Q4}"

if [ $# -ne $EXPECTED_ARGS ]
then
  echo "Usage: $0 dbname dbuser dbpass"
  exit $E_BADARGS
fi

$MYSQL -uroot -p -e "$SQL"

Using like below, it will ask for the root password

mysql-createdb <database> <user> <password>

Enable slow query log

Open the my.cnf file with a text editor and add the following block of code under the [mysqld] section:

slow_query_log = 1
slow-query_log_file = /var/log/mysql/slow.log
long_query_time = 2

Create the /var/log/mysql/slow.log file and set its user as the mysql user.

touch /var/log/mysql/slow.log
chown mysql:mysql /var/log/mysql/slow.log
```

Restart MySQL or MariaDB. 

Start monitoring the slow query logfile. 

````sh
mysqldumpslow -a /var/log/mysql/slow.log