User Tools

Site Tools


linux:mysql-install

MySQL Server Administration

Fri 09 Nov 2018

Install and Set Up MySQL

First, install MySQL Community Server, and then log into the MySQL shell under the MySQL root account:

# dnf info community-mysql-server
# dnf install community-mysql-server
# systemctl start mysqld.service
# systemctl status mysqld.service
# systemctl enable mysqld.service
# mysql -u root

Next, create a password for the MySQL root account. Note that this is not the same as the root account on the Linux system. That is, these are two different accounts: Linux root and MySQL root. Then exit MySQL.

mysql> alter user 'root'@'localhost' identified by 'aNewPassword4!';
mysql> \q

Now, secure the installation. From the Bash shell, run the following command, and then respond to the questions as follows:

# mysql_secure_installation
VALIDATE PASSWORD: Y
PASSWORD STRENGTH: 1
CHANGE ROOT PASSWORD: N
REMOVE ANONYMOUS USERS: Y
DISALLOW ROOT LOGIN REMOTELY: Y
REMOVE TEST DATABASE: Y
RELOAD PRIVILEGE TABLES: Y

Create and Set Up a Regular User Account

Now, log back into the MySQL shell as the MySQL Root user:

# mysql -u root -p

And in MySQL, create and set up a new account:

mysql> create user 'sean'@'localhost' identified by 'an0ldP4ssPhrase!';
msyql> flush privileges;

Now let's create a pet database for user 'sean'. This user will be granted all privileges on this database, including all tables in it. Other than granting all privileges, we could also grant specific privileges, including: CREATE, DROP, DELETE, INSERT, SELECT, UPDATE, and GRANT OPTION.

The syntax of the grant command below is this:

grant PRIVILEGE_OPTION on DATABASE.TABLE to 'USER'@'LOCALHOST';

In practice:

mysql> create database pets;
mysql> grant all privileges on pets.* to 'sean'@'localhost';
mysql> show databases;
mysql> \q

Logging in as Regular User and Creating Tables

Now, we can start doing MySQL work.

$ mysql -u sean -p
mysql> show databases;
mysql> use pets;
mysql> create table cats
    -> (
    -> id int unsigned not null auto_increment,
    -> name varchar(150) not null,
    -> owner varchar(150) not null,
    -> birth date not null,
    -> primary key (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
...
mysql> describe cats;
...

Adding records into the table

We'll use the INSERT command to add records:

mysql> insert into cats (name, owner, birth) values
    -> ('Sandy', 'Lennon', '2015-01-03'),
    -> ('Cookie', 'Casey', '2013-11-13'),
    -> ('Charlie', 'River', '2016-05-21');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from cats;
mysql> select owner from cats;
mysql> select birth from cats;
mysql> select name, owner from cats;
mysql> select name from cats;
...
mysql> select name from cats where name='Sandy';
...
mysql> select owner from cats where name='Cookie';
...
mysql> delete from cats where name='Cookie';
mysql> select * from cats;
mysql> alter table cats add gender char(1) after name;
mysql> describe cats;
mysql> update cats set gender="F" where id="1";
mysql> update cats set gender="M" where id="3";
mysql> select * from cats;
mysql> insert into cats (name, gender, owner, birth) values
    -> ('Bob', 'M', 'Alice', '2009-10-01');
mysql> select * from cats;
mysql> select name, gender from cats where birth < "2015-01-01";
mysql> \q

References and Read More

  1. MySQL: Getting Started with MySQL
  2. How to Create a New User and Grant Permissions in MySQL
  3. MySQL: MySQL 5.7 Reference Manual: 13 SQL Statement Syntax
linux/mysql-install.txt · Last modified: 2019/01/21 11:26 by seanburns