mySQL

Table of Contents General Install As-Is Container Install / Start Brand new in-memory install Persist data Connect MySQL prompt from inside MySQL prompt from OS bash IDE from outside from node.js Connect Root User User Data management Export Import Client Sample schema Misc

General

Cheatsheet

Naming conventions:

• user: lowercase, snake_case (dash requires putting all name between apostrophes: 'my-db')

List:

• get variable : SHOW VARIABLES LIKE "%version%";
• change user's password : ALTER USER '<USER_NAME>'@'localhost' IDENTIFIED BY '<NEW_PASSWORD>';
• get users : SELECT user, host, plugin FROM mysql.user;
• :

Install

As-Is

All details here

Steps:

• install server using apt sudo apt install mysql-server
• secure installation sudo mysql_secure_installation
  • activate Validate password plugin with Strong level
  • enter root password
  • remove anonymous users, remote root login
• initialize data directory mysqld --initialize
• check if mysql service is started systemctl status mysql.service
• check connection sudo mysqladmin -p -u root version
• create administrator
  • create user : CREATE USER '<USER_NAME>'@'localhost' IDENTIFIED BY '<USER_PASSWORD>';
  • grant all privileges: GRANT ALL PRIVILEGES ON *.* TO '<USER_NAME>'@'localhost' WITH GRANT OPTION;
  • exit MySQL CLI exit
• disable autocommit
  • check if enabled SELECT @@autocommit.
  • locate all config files location mysqladmin --help
  • create user-based config file ~/.my.cnf
  • add service section echo '[mysqld]' >> ~/.my.cnf
  • add disable autocommit option echo 'init_connect='SET autocommit=0'' >> ~/.my.cnf
  • check content head ~/.my.cnf
  • restart service service mysql restart
• login
• perform usual actions on a sample database
  • create a database CREATE DATABASE test_db;
  • enter database use test_db
  • create a table CREATE TABLE foo (foo_id TINYINT, foo_name TEXT, PRIMARY KEY(foo_id) );
  • turn off autocommit SET autocommit = 0;
  • insert data INSERT INTO foo (foo_id, foo_name) VALUES (0, 'foo' ); INSERT INTO foo (foo_id, foo_name) VALUES (1, 'bar' );
  • commit COMMIT;
  • query data: SELECT * FROM foo;
  • delete data: DELETE FROM foo WHERE foo_id = 0;
  • drop table DROP TABLE foo;
  • drop database DROP DATABASE test_db;

All in

CREATE DATABASE test_db;
use test_db;
CREATE TABLE foo (foo_id TINYINT, foo_name TEXT, PRIMARY KEY(foo_id) ); 
SET autocommit = 0;
INSERT INTO foo (foo_id, foo_name) VALUES (0, 'foo' ); 
INSERT INTO foo (foo_id, foo_name) VALUES (1, 'bar' );
COMMIT;

Container

See here

Install / Start

Brand new in-memory install

Steps:

• connect registry docker login
• pull image docker pull mysql/mysql-server
• start a MySQL server docker run --name=mysql_container -d mysql/mysql-server
• wait until status turns to (healthy) docker ps

Persist data

Details here And there (DockerHub)

Warning: Each volume will be around 200 MB !

First run:

docker run -d \
  -e MYSQL_ROOT_PASSWORD=<EXPECTED_PASSWORD> \
  -v /media/pedro/data/Code/DB/MySQL/docker-data-volume:/var/lib/mysql \
  --name mysql_container \
  mysql/mysql-server

Next one: docker start mysql_container

You can remove the container, the data will be there

docker stop mysql_container
docker rm mysql_container

To check volume mapping of container docker inspect mysql_container | grep volume

Export

• all databases (inc. system): docker exec mysql_container sh -c 'exec mysqldump --all-databases -uroot -p<MYSQL_ROOT_PASSWORD>' > <DUMP_FILE_PATH>/<DUMP_FILE_NAME>
• single database: docker exec mysql_container sh -c 'exec mysqldump --databases <DATABBASE_NAME> -uroot -p<MYSQL_ROOT_PASSWORD>' > <DUMP_FILE_PATH>/<DUMP_FILE_NAME>

Connect

MySQL prompt from inside

Steps:

• get the mysql root user's password docker logs mysql 2>&1 | grep GENERATED , eg [Entrypoint] GENERATED ROOT PASSWORD: uB#ylOzF0J0DXowIh*@mYMenUR
• connect to prompt as root docker exec -it mysql_container mysql -uroot -p
• change root's password ALTER USER 'root'@'localhost' IDENTIFIED BY '<PASSWORD>';
• once done, exit (one time)

MySQL prompt from OS

Steps:

• get container IP: docker inspect mysql_container | grep IPAddress
• connect : mysql -u<USER_NAME> -p -h<IP_ADDRESS>

bash

docker exec -it mysql_container bash

IDE from outside

Activate mySQL native authentication:

ALTER USER '<USER_NAME>'@'%' IDENTIFIED WITH mysql_native_password BY '<PASSWORD>';
FLUSH PRIVILEGES;

Then:

• alter authentication policy to native mode WITH mysql_native_password
• get container IP: docker inspect mysql_container | grep IPAddress
• get mysql port : docker ps

from node.js

See here

Connect

Root

Simple: sudo mysql

User

Is MySQL username is the same as OS username ?

• • yes: mysql -p
  • no: mysql -u <USER_NAME> -p

User

Create:

• CREATE USER '<USER_NAME>'@'localhost' IDENTIFIED BY '<USER_PASSWORD>';

Data management

Export

Use > mysqldump -u <USER_NAME> -p <DATABASE_NAME> > <FILE_NAME>

Import

Use < mysql -u <USER_NAME> -p <DATABASE_NAME> < <FILE_NAME>

Client

parameters

Sample schema

This is the official MySql employee repository:

• self supporting in employees schema;
• contains 300k users, resulting 150 Mbytes;
• contains tests scripts.

Steps:

• clone repo: git clone https://github.com/datacharmer/test_db sample_employee_dump
• enter repo: cd sample_employee_dump
• create schema : mysql -u<USER_NAME> -p -h<CONTAINER_IP> -t < employees.sql ( < 5 minutes )

Misc

If you get Client does not support authentication protocol requested by server; consider upgrading MySQL client:

• workaround: activate mySQL native authentication
• definitive fix : PR here