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