Press "Enter" to skip to content

Kelly’s MySQL quick tutorial

You don’t need tools like MySQLAdmin when basic tasks like creating and deleting databases, adding users and adjusting permissions can easily be done from the command line. I run these commands on a FreeBSD system and is likely the same on Linux, Windows, and other systems.

Basic tasks

Log into mysql:

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

Welcome to MySQL………

Create database:

>create database Frankenstein;
>use Frankenstein;

Create user:

>GRANT ALL PRIVILEGES ON Frankenstein.* TO [email protected] IDENTIFIED BY “bobspassword69” WITH GRANT OPTION;

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. Then exit.

FLUSH PRIVILEGES;
exit;

At this point you should be golden.

On a side note, if you experience a problem where MySQL keeps exiting with an error about mysql.sock on FreeBSD, it’s likely a permissions problem in your system. If you have this problem, try something like this:

“do you already have another mysqld server running on socket: /var/run/mysql/mysql.sock ?”

#chown _mysql /var/run/mysql

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 which was added to MySQL

GRANT PROCESS ON *.* TO [email protected];

The above likely fixes an error introduced in Mysql 5.7 and 8.0 in the year 2020 that might print out something like this:

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

What about backup and recovery?

Now that we have amazing content in our database, we must backup the database to keep it safe like this:

mysqldump –u bob –p Frankenstein > frankenstein-backup-01Jan2023.sql

Whew. Now let’s restore the database into a running MySQL server of our choice:

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

Anything else? Consult the manual, search the web, or add a comment here.

Leave a Reply

Your email address will not be published. Required fields are marked *