common mysql commands

from_unixtime()
UNIX_TIMESTAMP()

[mysqld]
#turn off binlog
skip-log-bin

#login without passwd
skip-grant-tables

 

#mysql 8.0 更改密码验证方式
default_authentication_plugin = mysql_native_password

CREATE USER 'admin'@'%' IDENTIFIED with mysql_native_password BY 'Pas5W@rd';
grant select,insert,update,delete,create on *.* to 'admin'@'%';

revoke select,insert from 'admin'@'%'

 

mysql -e 'show processlist' | grep -i root | grep -v processlist

show binary logs;
show binlog events in 'mysql-bin.00001' [from pos] [limit [offset,] rcount]

mysqlbinlog --start-datetime='2021-08-29 5:00:00' --stop-datetime='2021-08-30 5:20:00' --base64-output=decode-rows -v -d dbname mysql-bin.000016 > 29.sql

help command

select table_schema,table_name,table_rows
from information_schema.tables
order by table_rows desc limit 2 \G;

#inport faster
https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster

set global innodb_fast_shutdown = 0

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 512M
max_allowed_packet=256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
skip-log-bin

service mysqld restart --innodb-doublewrite=0

 

#4 ways to start mysql
https://blog.csdn.net/simplemurrina/article/details/80088479