Jun 27, 2024

Configure mysql server on ubuntu

MYSQL config buntu

Justin Napolitano

pythonmysqldatabases

projects

1 Minute, 42 Seconds

2024-06-27 00:00 +0000

Why

I installed mysql in the previous post. Now I need to setup users, create a db, and create a table.

Parts of this series

MYSQL Resources

Create a new user


mysql -u root -p

Create some users

In my case I will create 4 users accounts.

Cobra@localhost
Cobra@jnapolitano.com
admin@localhost
dummy@localhost

dummy is just used to test service connection and has not access grants or writes


CREATE USER 'cobra'@'localhost'
  IDENTIFIED BY 'password';
GRANT ALL
  ON *.*
  TO 'cobra'@'localhost'
  WITH GRANT OPTION;

 

CREATE USER 'admin'@'localhost'
  IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS
  ON *.*
  TO 'admin'@'localhost';

CREATE USER 'dummy'@'localhost';

ACTUALLY enter a password above. Do not use password as the user password'

Logout of root

enter EXIT in terminal

In my case I pass mysql -u cobra -p back to the terminal.

Create a DB

I will create a db for my personal website. The first command to run is


CREATE DATABASE jnapolitano;

Use your new db


USE jnapolitano;

Create the posts table


CREATE TABLE posts
(
  id                    BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())),  # Unique ID for the record. This a smallish and not a very high performance db this should be fine. 
  author                BINARY(16),                                         # id of author
  publish_date          DATE,                                               # publish date
  description           VARCHAR(150),                                       # post description
  link                  VARCHAR(150),                                       # Link to post
  title                 VARCHAR(150),                                       # title of hte post
  PRIMARY KEY           (id)                                                # Make the id the primary key
);

Create the authors table


CREATE TABLE authors
(
  id                    BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())),  # Unique ID for the record. This a smallish and not a very high performance db this should be fine. 
  name                  VARCHAR(150),                                      # name of author
  PRIMARY KEY           (id)                                                # Make the id the primary key
);

Create the mastodon post table

I will be adding support for other systems. I am starting with mastodon.


CREATE TABLE mastodon_posts
(
  id                    BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID())),  # Unique ID for the record. This a smallish and not a very high performance db this should be fine. 
  post_id               BINARY(16),                                         # name of author
  mastodon_post         VARCHAR(150),                                       # THE POST ID.. if it returns
  PRIMARY KEY           (id)                                                # Make the id the primary key
);

Why