User Tools

Site Tools


linux:mysql-install

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

linux:mysql-install [2019/01/21 11:26] (current)
seanburns created
Line 1: Line 1:
 +<markdown>
 +# 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:
 +
 +```bash
 +# 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
 +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:
 +
 +```bash
 +# 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:
 +
 +```bash
 +# mysql -u root -p
 +```
 +
 +And in MySQL, create and set up a new account:
 +
 +```mysql
 +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:
 +
 +```mysql
 +grant PRIVILEGE_OPTION on DATABASE.TABLE to 'USER'@'LOCALHOST';
 +```
 +
 +In practice:
 +
 +```mysql
 +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
 +$ 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
 +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][1]
 +2. [How to Create a New User and Grant Permissions in MySQL][2]
 +3. [MySQL: MySQL 5.7 Reference Manual: 13 SQL Statement Syntax][3]
 +
 +[1]:https://dev.mysql.com/doc/mysql-getting-started/en/
 +[2]:https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql
 +[3]:https://dev.mysql.com/doc/refman/5.7/en/sql-syntax.html
 +</markdown>
linux/mysql-install.txt ยท Last modified: 2019/01/21 11:26 by seanburns