Create User and Database
Create user webapp, database webappdb and grant all privileges to user.
To generate random passwords I use: https://passwordsgenerator.net/
CREATE DATABASE webappdb;
CREATE USER 'webapp'@'%' identified by "LbAh52s6MnKQZasH";
GRANT ALL PRIVILEGES ON webappdb.* TO 'webapp'@'%'
Grant read-only permission
Grant read-only permission to user webapp_ro to database webappdb
GRANT SELECT ON webappdb.* TO 'webapp_ro'@'%';
Export to CSV from mysql shell
SELECT *
FROM <>
WHERE <>
INTO OUTFILE '/tmp/output.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Load CSV from mysql shell
LOAD DATA LOCAL INFILE '<path-to-csv-file>'
INTO TABLE <table_name>
enclosed by '"'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
Create index
CREATE INDEX <index_name> ON <table_name>(<column_name>);
Get size of tables
SELECT
TABLE_SCHEMA AS `Database`,
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
information_schema.TABLES
ORDER BY
(DATA_LENGTH + INDEX_LENGTH)
DESC;