Database

-- create
CREATE DATABASE `dbname` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

User

-- create user
CREATE USER 'test' @'10.10.10.1' IDENTIFIED WITH mysql_native_password BY '123456' REQUIRE SSL PASSWORD EXPIRE INTERVAL 90 DAY;
-- grant privileges
GRANT ALL PRIVILEGES ON `dbname`.* TO 'test' @'10.10.10.1';
-- grant SELECT
-- select, insert, update, delete
GRANT SELECT ON`invent`.* TO 'test' @'10.10.10.1';
-- flush
FLUSH PRIVILEGES;
-- select user
SELECT
    `host`,
    `user`,
    `plugin`,
    `authentication_string` 
FROM
    `user`;
-- show user grants
SHOW GRANTS FOR 'test' @'10.10.10.1';
-- revoke privileges
REVOKE ALL PRIVILEGES ON `invent`.* FROM 'test' @'10.10.10.1';
-- delete user
DROP USER 'test' @'10.10.10.1';
-- flush privileges
FLUSH PRIVILEGES;
-- modify password
ALTER USER 'test' @'10.10.10.1' IDENTIFIED WITH mysql_native_password BY '123456';
-- select user
SELECT
    `USER`,
    `HOST`,
    password_last_changed,
    password_lifetime,
    password_expired 
FROM
    mysql.USER;

Config

-- select all columns
SELECT * FROM information_schema.`COLUMNS` WHERE table_name = 'sys_user';
-- version
SELECT version();
-- is suport ssl
SHOW VARIABLES LIKE '%ssl%';
-- port
SHOW VARIABLES LIKE 'port';
-- datadir
SHOW VARIABLES LIKE 'datadir';
-- update table name to upper
SELECT
    concat( "alter table ", TABLE_NAME, ' rename to ', upper( TABLE_NAME ), ' ;' ) 
FROM
    information_schema.TABLES t 
WHERE
    TABLE_SCHEMA = 'dbname';
SHOW GLOBAL VARIABLES;
SHOW STATUS LIKE 'Threads%';
SHOW VARIABLES LIKE '%max_connections%';
SHOW GLOBAL VARIABLES LIKE "max%connections";
SHOW PROCESSLIST;
SHOW VARIABLES LIKE 'validate_password%';
SHOW VARIABLES LIKE "%connection_control%";
-- errors
USE PERFORMANCE_SCHEMA;
SELECT * FROM host_cache;
-- select login failed user and times
SELECT * FROM connection_control_failed_login_attempts;# clearn hosts cache
FLUSH HOSTS;
# log
SHOW VARIABLES LIKE "general_log%";

SET GLOBAL general_log = 'ON';

SET GLOBAL general_log_file = '/var/log/mysql/general_log.log';

mysqldump

mysqldump -h127.0.0.1 -uroot -P3306 -p \
--no-autocommit --no-create-db \
--ignore-table=dbname.beh_survey_data \
--ignore-table=dbname.beh_uwb_data \
--ignore-table=dbname.beh_uwb_tempdata \
--databases dbname > dbname.sql;

Import

-- close log,0：off，1：on

SET sql_log_bin = OFF;
SELECT @@sql_log_bin;

-- 0：off，1：on（default）
SET autocommit = 0;
SELECT @@autocommit;

-- modify packet
SELECT @@max_allowed_packet;-- 1GB

SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;