Kelly’s MySQL quick tutorial

A very simple tutorial for getting going with MySQL 8 quickly. 

You don’t need tools like MySQLAdmin for basic tasks like creating and deleting databases, adding users and adjusting permissions. This can easily be done from the command line. 

Basic tasks

Log into mysql:

#mysql –u root –p
password: ******

Welcome to MySQL monitor.
...
root@localhost [(none)]>

Create a database:

>create database Frankenstein;
>use Frankenstein;

Create a user and grant privileges:

>CREATE USER 'Frank'@'localhost' identified by 'Fr@nksP@s$w0rD';
>GRANT ALL ON Frankenstein.* TO 'Frank'@'localhost';

Then reload the grant tables in the database using flush privileges. If you forget this step you can also run “mysqladmin reload” to reload the grant tables.

>FLUSH PRIVILEGES;
>exit;

Change user password:

>ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';
>FLUSH PRIVILEGES;

Grant special permissions to a user, such as the PROCESS permission, required for certain backup operations:

>GRANT PROCESS ON *.* TO 'Frank'@'localhost';

The above fixes a permissions change introduced a few years ago (around 2020, Covid era) into MySQL 8.0 and 5.7 that resulted in permission errors like the one below when doing backups:

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Backup and Recovery?

Backup the database often:

mysqldump –u Frank –p Frankenstein > frankenstein-backup-25Aug2023.sql

Restore the database on another system, or revert back if you need to: 

mysql –u Frank –p Frankenstein < frankenstein-db-backup-01Jan2023.sql

Database too big? 

Sometimes databases are much bigger than they should be.  Rogue apps and some WordPress plugins can create large tables of useless data.  Find the offending table by running a query like this within your Frankenstein database:

>SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "Frankenstein"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

For this and other sizing examples, thanks to AJ Welch for his article on Chartio.

Any other suggestions? Leave a comment.