MYSQL

Install

• apt-get install mysql-server
• connector: python -m pip install mysql-connector

• enter into mysql: mysql -u root -p

Setup

• Allow remote access: nano /etc/mysql/my.cnf: bind-address = 127.0.0.1 change to bind-address = 0.0.0.0
• Or just comment is # bind-address = 0.0.0.0

• sudo ufw allow mysql

• create new remote user:

CREATE USER 'hechao'@'localhost' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;

CREATE USER 'hechao'@'%' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'WITH GRANT OPTION;

• OR

grant all privileges on *.* to 'root'@'%' identified by 'password';
flush privileges;

• Where first * is database, second * is tables, monty is login name, % for any ip address

Operations

• Update: UPDATE mysql.user SET Password = PASSWORD('password') WHERE User = 'root';

Database

• CREATE DATABASE demodb;

create tables

• normal: CREATE TABLE stock (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), date DATE(255), share INT(100));
• datetime, varchar, float, int: CREATE TABLE stock (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), date2 Datetime, price FLOAT(50,3), share INT(100));
• CREATE TABLE sum1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), total_share INT(100), total_cost FLOAT(50,3), avg_price FLOAT(50,3));

show tables

• show tables;

table insert

• insert into stock(name, date2, share) values ('DBC', '19600101', 1);
• insert into stock(name, date2, share) values ('DBC', '1960-01-01', 2);

example data

• insert into stock(name, date2, share, price) values ('DBC', now(), 1, 14.3);
• insert into stock(name, date2, share, price) values ('DBC', now(), 2, 14.87);

ignore

• insert ignore into sum1 (name) values ('test2')

table drop

• drop table stock;

table select

• SELECT * FROM stock.stock;
• SELECT User, Host, authentication_string FROM mysql.user;

INSERT INTO sum2(name, total_share, total_cost, avg_price)
SELECT name, 
SUM(share) 'total_share', 
round(SUM(price * share),2) 'total_cost', 
round(SUM(price * share)/SUM(share),2) 'avg_price' 
FROM stock 
GROUP BY name;

table delete

• DELETE FROM stock WHERE name = 'DBC';
• DELETE FROM stock WHERE ID = 3;

Cell

Content Update

• update stock set date2='20190401' where id=29;
• update stock, sum1 set sum1.total_share= (select SUM(share) from stock where sum1.name=stock.name);
• update stock, sum1 set sum1.total_cost= (select round(SUM(price * share),2) from stock where sum1.name=stock.name);
• update stock, sum1 set sum1.avg_price= (select round(SUM(price * share)/SUM(share),2) from stock where sum1.name=stock.name);

Column Update

• Add column

ALTER TABLE vendors
ADD COLUMN phone VARCHAR(15) AFTER name;

ALTER TABLE vendors
ADD COLUMN last_buy VARCHAR(15);

ALTER TABLE stock 
ADD COLUMN last_buy Datetime;

alter table sum1 
add column last_price float(50,3);

• drop

alter table stock 
drop column last_buy;

• Reference - http://www.mysqltutorial.org/mysql-add-column/

Other operations

• Make change: FLUSH PRIVILEGES;
• service mysql restart
• systemctl start mysql
• systemctl enable mysql

Output - dump

• mysqldump -u root -p test>test.sql

Input

• mysql -u root -p
• create database stock
• exit
• mysql -u root -p stock < stock.sql

Workflow

Example for mediawiki:

• show databases;
• use edwik;
• SHOW COLUMNS FROM page;
• select * from page where page_title='RISC-V';
• delete from page where page_id=4975;

Docker

dump sql

• docker exec db sh -c 'exec mysqldump -uroot -ppassword wordpress' > /web/import/test2.sql

import sql

• docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /web/import/blogdb.sql