Taogen's Blog

Stay hungry stay foolish.

Junk files are unnecessary files produced by the operating system or software. Junk files keep increasing, but our computer disks are limited. So we need to delete junk files frequently. Otherwise, we may not have enough free space.

Types of Junk Files

Here are common types of junk files:

  • Files in the Recycle Bin.
  • Windows temporary files. These are junk files whose use is temporary and become redundant once the current task is complete.
  • Windows and third-party software leftovers. When you uninstall a program, not all the files associated with the software are deleted.
  • Software cache files.
  • Log files.
  • Downloads. The downloads folder usually takes a chunk of your storage space. Usually, it contains unwanted installers, images, videos, and other redundant documents that accumulate over time.

Empty the Recycle Bin

:: empty Recycle Bin from the disk C
(ECHO Y | rd /s /q %systemdrive%\$RECYCLE.BIN) > %USERPROFILE%\Desktop\delete_files.log 2>&1
:: empty Recycle Bin from the disk D
(ECHO Y | rd /s /q d:\$RECYCLE.BIN) > %USERPROFILE%\Desktop\delete_files.log 2>&1
:: empty Recycle Bin from all disk drives. if used inside a batch file, replace %i with %%i
(FOR %i IN (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) DO (rd /s /q %i:\$RECYCLE.BIN)) > %USERPROFILE%\Desktop\delete_files.log 2>&1

Delete Temporary Files

To view temporary files

%SystemRoot%\explorer.exe %temp%

Delete all temporary files

del /s /q %USERPROFILE%\AppData\Local\Temp\*.* > %USERPROFILE%\Desktop\delete_files.log 2>&1
:: or
del /s /q %temp%\*.* > %USERPROFILE%\Desktop\delete_files.log 2>&1

Delete all empty directories in the temporary files directory

:: if used inside a batch file, replace %d with %%d
for /f "delims=" %d in ('dir /s /b /ad %USERPROFILE%\AppData\Local\Temp ^| sort /r') do rd "%d"

Only delete temporary files that were last modified less than 7 days ago and empty directories

:: if used inside a batch file, replace %d with %%d
((echo Y | FORFILES /s /p "%USERPROFILE%\AppData\Local\Temp" /M "*" -d -7 -c "cmd /c del /q @path") && (for /f "delims=" %d in ('dir /s /b /ad %USERPROFILE%\AppData\Local\Temp ^| sort /r') do rd "%d")) > %USERPROFILE%\Desktop\delete_files.log 2>&1

Delete Windows and Third-Party Software Leftovers

Chrome old version leftovers

"C:\Program Files\Google\Chrome\Application\{old_version}\*.*"

Delete Software Cache Files

Browser

Chat Software

Delete WeChat cache files

del /s /q "%USERPROFILE%\Documents\WeChat Files\*.*" > %USERPROFILE%\Desktop\delete_files.log 2>&1

Delete Log Files

Only delete log files that were last modified less than 7 days ago

cd C:\
(ECHO Y | FORFILES /s /p "C:" /M "*.log" -d -7 -c "cmd /c del /q @path")

Delete all disk drives log files

:: print files to delete
type NUL > %USERPROFILE%\Desktop\delete_files.log
FOR %i IN (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) DO (%i: && (ECHO Y | FORFILES /s /p "%i:" /M "*.log" -d -7 -c "cmd /c echo @path")) >> %USERPROFILE%\Desktop\delete_files.log 2>&1

:: delete
type NUL > %USERPROFILE%\Desktop\delete_files.log
FOR %i IN (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) DO (%i: && (ECHO Y | FORFILES /s /p "%i:" /M "*.log" -d -7 -c "cmd /c del /q @path")) >> %USERPROFILE%\Desktop\delete_files.log 2>&1

WeChat Log Files

del /s /q %USERPROFILE%\AppData\Roaming\Tencent\WeChat\log\*.xlog > %USERPROFILE%\Desktop\delete_files.log 2>&1

Apache Tomcat Log Files

del /q "C:\Program Files\Apache Software Foundation\Tomcat 8.0\logs\major\run.out.*" > %USERPROFILE%\Desktop\delete_files.log 2>&1

Command Usage

del

Deletes one or more files.

Syntax

del <option> <filepath_or_file_pattern>

Parameters

  • /q - Specifies quiet mode. You are not prompted for delete confirmation.
  • /s - Deletes specified files from the current directory and all subdirectories. Displays the names of the files as they are being deleted.
  • /? - Displays help at the command prompt.

rd

Syntax

rd [<drive>:]<path> [/s [/q]]

Parameters

  • /s - Deletes a directory tree (the specified directory and all its subdirectories, including all files).
  • /q - Specifies quiet mode. Does not prompt for confirmation when deleting a directory tree. The /q parameter works only if /s is also specified.
  • /? - Displays help at the command prompt.

forfiles

Selects and runs a command on a file or set of files.

Syntax

forfiles [/P pathname] [/M searchmask] [/S] [/C command] [/D [+ | -] [{<date> | <days>}]]

Parameters

  • /P <pathname> - Specifies the path from which to start the search. By default, searching starts in the current working directory. For example, /p "C:"
  • /M <searchmask> - Searches files according to the specified search mask. The default searchmask is *. For example, /M "*.log".
  • /S - Instructs the forfiles command to search in subdirectories recursively.
  • /C <command> - Runs the specified command on each file. Command strings should be wrapped in double quotes. The default command is "cmd /c echo @file". For example, -c "cmd /c del /q @path"
  • /D [{+\|-}][{<date> | <days>}] - Selects files with a last modified date within the specified time frame. For example, -d -7.

Wildcard

  • * - Match zero or more characters
  • ? - Match one character in that position
  • [ ] - Match a range of characters. For example, [a-l]ook matches book, cook, and look.
  • [ ] - Match specific characters. For example, [bc]ook matches book and cook.
  • ``*` - Match any character as a literal (not a wildcard character)

Run batch file with Task Scheduler

Open “Task Scheduler“ or Windows + R, input taskschd.msc

Right-click the “Task Scheduler Library” branch and select the New Folder option.

Confirm a name for the folder — for example, MyScripts.

Click the OK button.

Expand the “Task Scheduler Library” branch.

Right-click the MyScripts folder.

Select the Create Basic Task option.

In the “Name” field, confirm a name for the task — for example, ClearJunkBatch.

(Optional) In the “Description” field, write a description for the task.

Click the Next button.

Select the Monthly option.

  • Quick note: Task Scheduler lets you choose from different triggers, including a specific date, during startup, or when a user logs in to the computer. In this example, we will select the option to run a task every month, but you may need to configure additional parameters depending on your selection.

Click the Next button.

Use the “Start” settings to confirm the day and time to run the task.

Use the “Monthly” drop-down menu to pick the months of the year to run the task.

Use the “Days” or “On” drop-down menu to confirm the days to run the task.

Click the Next button.

Select the Start a program option to run the batch file.

In the “Program/script” field, click the Browse button.

Select the batch file you want to execute.

Click the Finish button.

References

Clear

Delete files

Auto Answer “Yes/No” to Prompt

Batch file

MySQL Server Configuration Files

Most MySQL programs can read startup options from option files (sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program.

To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

Configuration Files on Windows

On Windows, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).

File Name Purpose
%WINDIR%\my.ini, %WINDIR%\my.cnf Global options
C:\my.ini, C:\my.cnf Global options
BASEDIR\my.ini, BASEDIR\my.cnf Global options
defaults-extra-file The file specified with --defaults-extra-file, if any
%APPDATA%\MySQL\.mylogin.cnf Login path options (clients only)
DATADIR\mysqld-auto.cnf System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)
  • %WINDIR% represents the location of your Windows directory. This is commonly C:\WINDOWS. You can run echo %WINDIR% to view the location.
  • %APPDATA% represents the value of the Windows application data directory. C:\Users\{userName}\AppData\Roaming.
  • BASEDIR represents the MySQL base installation directory. When MySQL 8.0 has been installed using MySQL Installer, this is typically C:\PROGRAMDIR\MySQL\MySQL Server 8.0 in which PROGRAMDIR represents the programs directory (usually Program Files for English-language versions of Windows). Although MySQL Installer places most files under PROGRAMDIR, it installs my.ini under the C:\ProgramData\MySQL\MySQL Server 8.0\ directory (DATADIR) by default.
  • DATADIR represents the MySQL data directory. As used to find mysqld-auto.cnf, its default value is the data directory location built in when MySQL was compiled, but can be changed by --datadir specified as an option-file or command-line option processed before mysqld-auto.cnf is processed. By default, the datadir is set to C:/ProgramData/MySQL/MySQL Server 8.0/Data in the BASEDIR\my.ini (C:\ProgramData\MySQL\MySQL Server 8.0\my.ini). You also can get the DATADIR location by running the SQL statement SELECT @@datadir;.

After you installed MySQL 8.0 on Windows, you only have a configuration file BASEDIR\my.ini (actually C:\ProgramData\MySQL\MySQL Server 8.0\my.ini).

Configuration Files on Unix-Like Systems

On Unix and Unix-like systems, MySQL programs read startup options from the files shown in the following table, in the specified order (files listed first are read first, files read later take precedence).

Note: On Unix platforms, MySQL ignores configuration files that are world-writable. This is intentional as a security measure.

File Name Purpose
/etc/my.cnf Global options
/etc/mysql/my.cnf Global options
SYSCONFDIR/my.cnf Global options
$MYSQL_HOME/my.cnf Server-specific options (server only)
defaults-extra-file The file specified with --defaults-extra-file, if any
~/.my.cnf User-specific options
~/.mylogin.cnf User-specific login path options (clients only)
DATADIR/mysqld-auto.cnf System variables persisted with SET PERSIST or SET PERSIST_ONLY (server only)
  • SYSCONFDIR represents the directory specified with the SYSCONFDIR option to CMake when MySQL was built. By default, this is the etc directory located under the compiled-in installation directory.
  • MYSQL_HOME is an environment variable containing the path to the directory in which the server-specific my.cnf file resides. If MYSQL_HOME is not set and you start the server using the mysqld_safe program, mysqld_safe sets it to BASEDIR, the MySQL base installation directory.
  • DATADIR represents the MySQL data directory. As used to find mysqld-auto.cnf, its default value is the data directory location built in when MySQL was compiled, but can be changed by --datadir specified as an option-file or command-line option processed before mysqld-auto.cnf is processed. By default, the datadir is set to /var/lib/mysql in the /etc/my.cnf or /etc/mysql/mysql. You also can get the DATADIR location by running the SQL statement SELECT @@datadir;.

After you installed MySQL 8.0 on Linux, you only have a configuration file /etc/my.cnf.

Configuration File Inclusions

It is possible to use !include directives in option files to include other option files and !includedir to search specific directories for option files. For example, to include the /home/mydir/myopt.cnf file, use the following directive:

!include /home/mydir/myopt.cnf

To search the /home/mydir directory and read option files found there, use this directive:

!includedir /home/mydir

MySQL makes no guarantee about the order in which option files in the directory are read.

Note: Any files to be found and included using the !includedir directive on Unix operating systems must have file names ending in .cnf. On Windows, this directive checks for files with the .ini or .cnf extension.

Why would you put some directives into separate files instead of just keeping them all in /etc/my.cnf? For modularity.

If you want to deploy some sets of config directives in a modular way, using a directory of individual files is a little easier than editing a single file. You might make a mistake in editing, and accidentally change a different line than you intended.

Also removing some set of configuration options is easy if they are organized into individual files. Just delete one of the files under /etc/my.cnf.d, and restart mysqld, and then it’s done.

Common Configurations

Change port

[client]
port=13306

[mysqld]
port=13306

References

[1] Using Option Files - MySQL Reference Manual

Git

Install Git

On Windows, download git for windows.

On Linux, running the command sudo apt-get install git to install git.

Verify that the installation was successful:

git --version

Git Settings

Setting your user name and email for git

git config --global user.name "taogen"
git config --global user.email "taogenjia@gmail.com"

Check your git settings

git config user.name
git config user.email

Checking for existing SSH keys

Before you generate an SSH key, you can check to see if you have any existing SSH keys.

  1. Open Terminal or Git Bash

  2. Enter ls -al ~/.ssh to see if existing SSH keys are present.

  3. Check the directory listing to see if you already have a public SSH key. By default, the filenames of supported public keys for GitHub are one of the following.

    • id_rsa.pub

    • id_ecdsa.pub

    • id_ed25519.pub

  4. Either generate a new SSH key or upload an existing key.

Generating a new SSH key and adding it to the ssh-agent

Generating a new SSH key

  1. Open Terminal or Git Bash

  2. Paste the text below, substituting in your GitHub email address.

    $ ssh-keygen -t ed25519 -C "your_email@example.com"

    Note: If you are using a legacy system that doesn’t support the Ed25519 algorithm, use:

    $ ssh-keygen -t rsa -b 4096 -C "your_email@example.com"

    After running the above command, you need to enter a file path or use the default file path and enter a passphrase or no passphrase.

    Generating public/private ALGORITHM key pair.
    Enter file in which to save the key (C:/Users/YOU/.ssh/id_ALGORITHM):
    Enter passphrase (empty for no passphrase):
    Enter same passphrase again:
    Your identification has been saved in C:/Users/YOU/.ssh/id_ALGORITHM.
    Your public key has been saved in C:/Users/YOU/.ssh/id_ALGORITHM.pub.
    The key fingerprint is:
    SHA256:24EfhoOdfZYXtdBt42wbDj7nnbO32F6TQsFejz95O/4 your_email@example.com
    The key's randomart image is:
    +--[ED25519 256]--+
    | .. o|
    | . .++|
    | o++.|
    | o = .ooB.|
    | . S = =o* +|
    | * =.+ =o|
    | . o .+=*|
    | +=O|
    | .oBE|
    +----[SHA256]-----+

Adding your SSH key to the ssh-agent

You can secure your SSH keys and configure an authentication agent so that you won’t have to reenter your passphrase every time you use your SSH keys.

  1. Ensure the ssh-agent is running.

Start it manually:

# start the ssh-agent in the background
$ eval "$(ssh-agent -s)"
> Agent pid 59566

Auto-launching the ssh-agent Configuration

You can run ssh-agent automatically when you open bash or Git shell. Copy the following lines and paste them into your ~/.profile or ~/.bashrc file in Git shell:

env=~/.ssh/agent.env

agent_load_env () { test -f "$env" && . "$env" >| /dev/null ; }

agent_start () {
(umask 077; ssh-agent >| "$env")
. "$env" >| /dev/null ; }

agent_load_env

# agent_run_state: 0=agent running w/ key; 1=agent w/o key; 2=agent not running
agent_run_state=$(ssh-add -l >| /dev/null 2>&1; echo $?)

if [ ! "$SSH_AUTH_SOCK" ] || [ $agent_run_state = 2 ]; then
agent_start
ssh-add
elif [ "$SSH_AUTH_SOCK" ] && [ $agent_run_state = 1 ]; then
ssh-add
fi

unset env
  1. Add your SSH private key to the ssh-agent.

If your private key is not stored in one of the default locations (like ~/.ssh/id_rsa), you’ll need to tell your SSH authentication agent where to find it. To add your key to ssh-agent, type ssh-add ~/path/to/my_key.

$ ssh-add ~/.ssh/id_ed25519

Adding a new SSH key to your GitHub account

  1. Open Terminal or Git Bash. Copy the SSH public key to your clipboard.

    $ pbcopy < ~/.ssh/id_ed25519.pub
    # Copies the contents of the id_ed25519.pub file to your clipboard

    or

    $ clip < ~/.ssh/id_ed25519.pub
    # Copies the contents of the id_ed25519.pub file to your clipboard

    or

    $ cat ~/.ssh/id_ed25519.pub
    # Then select and copy the contents of the id_ed25519.pub file
    # displayed in the terminal to your clipboard
  2. GitHub.com -> Settings -> Access - SSH and GPG keys -> New SSH key

Testing your SSH connection

After you’ve set up your SSH key and added it to your account on GitHub.com, you can test your connection.

  1. Open Terminal or Git Bash

  2. Enter the following command

    $ ssh -T git@github.com
    # Attempts to ssh to GitHub

    If you see the following message, you have successfully connected GitHub with SSH.

    > Hi USERNAME! You've successfully authenticated, but GitHub does not
    > provide shell access.

References

[1] Connecting to GitHub with SSH

Frontend responsibilities

  • Layout and style of web pages.
  • Page redirection.
  • Event handling.
  • Form validation and submission.
  • Call API and render the data. Note data shouldn’t be converted and formatted in the frontend.

Backend responsibilities

  • Design data model.
  • Validation and conversion of parameters for HTTP requests.
  • Business logic processing.
  • Build response data with the correct structure and format.

Event Handling

  1. .click(handler) - event handling for specified elements
$("your_selector").click(function(event) {
// do something
});
  1. .on( events [, selector ] [, data ], handler ) - event handling for dynamic elements
$(document).on("click","your_selector", function (event) {
// do something
});

if you know the particular node you’re adding dynamic elements to, you could specify it instead of the document.

$("parent_selector").on("click","your_selector", function (event) {
// do something
});

Passing data to the handler

$(document).on("click", "#your_div", {name: "Jack"}, handler);

function handler(event){
console.log(event.data.name);
}

Deprecated API

As of jQuery 3.0, .bind() and .delegate() have been deprecated. It was superseded by the .on() method for attaching event handlers to a document since jQuery 1.7, so its use was already discouraged.

Get the element that triggered the event

$("your_selector").click(function(event) {
// get the element
console.log(event.target);
console.log(this);

// get the element id
console.log(this.id);
console.log(event.target.id);
console.log($(this).attr('id'));

// Get jQuery object by element
console.log($(this).html());
console.log($(event.target).html());
});

Get the element that triggered the event

  • event.target
  • this

Note: event.target equals this, and equals document.getElementById("your_selector")

Get the element id

  • this.id
  • event.target.id
  • $(this).attr('id')

Get jQuery object by element

  • $(this)
  • $(event.target)

jQuery Events

Form Events

  • .blur(handler)
  • .change(handler)
  • .focus(handler)
  • .focusin(handler)
  • .focusout(handler)
  • .select(handler)
  • .submit(handler)

Keyboard Events

  • .keydown(handler)
  • .keypress(handler)
  • .keyup(handler)

Mouse Events

  • .click(handler)
  • .contextmenu(handler). The contextmenu event is sent to an element when the right button of the mouse is clicked on it, but before the context menu is displayed.
  • .dblclick(handler). The dblclick event is sent to an element when the element is double-clicked.
  • .hover(handlerIn, handlerOut)
  • .mousedown(handler)
  • .mouseenter(handler)
  • .mouseleave(handler)
  • .mousemove(handler)
  • .mouseout(handler)
  • .mouseover(handler)
  • .mouseup(handler)
  • .toggle(handler, handler). Bind two or more handlers to the matched elements, to be executed on alternate clicks.

Kill Long Running Queries

Query information_schema.innodb_trx

  • trx_mysql_thread_id: thread ID
SELECT trx_mysql_thread_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx
where trx_state = "RUNNING" and trx_query like "%SELECT%"
ORDER BY `trx_started`;

kill {trx_mysql_thread_id};

-- To check again by query information_schema.innodb_trx.
-- Sometimes need kill two times.

After killed the query thread. The client receive a error message 2013 - Lost connection to server during query.

Query information_schema.processlist

  • id: thread ID
  • time: cost time in seconds
  • state: Sending Data, executing
SELECT * 
FROM information_schema.processlist
WHERE
info like '%SELECT%'
order by `time` desc;

kill {ID};

-- To check again by query information_schema.processlist.
-- Sometimes need kill two times.

Kill Locked SQL Statements

An Example of Locked SQL Statements

Create a table for test

CREATE TABLE `t_user` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (1, 'Jack', 20);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (2, 'Tom', 30);
INSERT INTO `t_user` (`id`, `name`, `age`) VALUES (3, 'John', 22);

Executing the SQL statement 1 to lock the table

SET autocommit = 0;  
START TRANSACTION;
update t_user set age = 2;

Executing the SQL statement 2, which will wait for the lock.

-- Temporarily set the lock wait timeout to 10 minutes. By default, it is 50 seconds. We need a longer timeout to find out the locked SQL statements.
SET SESSION innodb_lock_wait_timeout = 600;
update t_user set age = 3;

If waiting for lock is timeout (by default, it is 50 seconds), SQL statement 2 will receive a error message

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

After finished the lock test, you can COMMIT or ROLLBACK the transaction of SQL statement 1 and set autocommit to 1.

COMMIT;
ROLLBACK;
SET autocommit = 1;

Get thread IDs and SQL statements of lock-holding and lock-waiting executing SQLs

Query whether some SQL statement threads are waiting for lock

SELECT *
FROM `information_schema`.`innodb_trx`
where trx_state = "LOCK WAIT"
ORDER BY `trx_started`;
  • trx_state: LOCK WAIT
  • trx_started: 2022-10-21 14:13:38
  • trx_mysql_thread_id: 17 (thread ID)
  • trx_query: the executing SQL statement
  • trx_requested_lock_id: 1207867061312:1021:4:2:1207832760632
  • trx_wait_started: 2022-10-21 14:13:38

Query whether some SQL statement treads are running with lock

SELECT *
FROM `information_schema`.`innodb_trx`
where trx_state = "RUNNING" and trx_tables_locked > 0 and trx_rows_locked > 0
ORDER BY `trx_started`;
  • trx_state: RUNNING
  • trx_started: 2022-10-21 14:09:57
  • trx_mysql_thread_id: 16 (thread ID)
  • trx_query: the executing SQL statement
  • trx_tables_locked: 1
  • trx_lock_structs: 2
  • trx_rows_locked: 3
  • trx_row_modified: 2

Get More Locked Information

Query what table is locked

show open tables where in_use > 0;

Query Lock Information

Get transaction IDs and real Thread IDs of lock-holding and lock-waiting executing SQL.

SHOW ENGINE INNODB STATUS;

To find “TRANSACTION xxx, ACTIVE xxx sec” in the result text

lock-holding transaction Information

---TRANSACTION 580438, ACTIVE 1862 sec
2 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 16, OS thread handle 20276, query id 278 localhost ::1 root

lock-waiting transaction Information

---TRANSACTION 580444, ACTIVE 36 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 17, OS thread handle 16228, query id 454 localhost ::1 root updating
update t_user set age = 3
------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED

There are only one lock-holding transaction and one lock-waiting transaction. So we can guess that thread 16 block thread 17, or that transaction 580438 block transaction 580444.

Check lock dependency - what blocks what

MySQL 5.x

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

MySQL 8

Query lock dependency

SELECT * FROM performance_schema.data_lock_waits;
  • BLOCKING_ENGINE_TRANSACTION_ID: lock-holding transaction ID
  • REQUESTING_ENGINE_TRANSACTION_ID: lock-waiting transaction ID

The result is BLOCKING_ENGINE_TRANSACTION_ID blocked REQUESTING_ENGINE_TRANSACTION_ID. We can confirm that transaction 580438 blocked transaction 580444. You can get the real thread IDs from the result of SHOW ENGINE INNODB STATUS;. Therefore, we can confirm that thread 16 blocked thread 17.

Query lock-holding and lock-waiting transaction information

SELECT * FROM performance_schema.data_locks;
  • ENGINE_TRANSATION_ID: transation_id in SHOW ENGINE INNODB STATUS;
  • OBJECT_NAME: table name
  • LOCK_STATUS: “WATING”/“GRANT”

Kill the Locked Tread

kill {thread_ID};
kill 16;

References

Hardware Information

Hardware Information

sudo lshw
sudo lshw -short
sudo lshw -html > lshw.html

CPU

CPU Information

lscpu

CPU Usage

vmstat

echo "CPU Usage: "$[100-$(vmstat 1 2|tail -1|awk '{print $15}')]"%"

/proc/stat

grep 'cpu ' /proc/stat | awk '{usage=($2+$4)*100/($2+$4+$5)} END {print "CPU Usage: " usage "%"}'
cat /proc/stat |grep cpu |tail -1|awk '{print ($5*100)/($2+$3+$4+$5+$6+$7+$8+$9+$10)}'|awk '{print "CPU Usage: " 100-$1 "%"}'

top

top -bn2 | grep '%Cpu' | tail -1 | grep -P '(....|...) id,'|awk '{print "CPU Usage: " 100-$8 "%"}'

Disk

Disk Information

Block Devices Information

lsblk
lsblk -a

Disk Usage

df -h

Folder Disk Space Usage

# all subdirectories size and total size
du -h <folder_name>
# -s total size of a directory
du -sh <folder_name>
# -a all files size, subdirectories size and total size
du -ah <folder_name>
# -c add total usage to the last line
du -ch <folder_name>

File Disk Space Usage

ls -lh .
du -ah <folder_name>

Memory

Memory Information

sudo dmidecode -t memory

Memory Usage

free -h
# the percentage of memory in use of user processes
free | grep Mem | awk '{print $3/$2 * 100.0 "%"}'
# the real percentage of memory in use included OS memory. available / total memory.
# -m: Display the amount of memory in megabytes.
# N: your server total memory in GB.
free -m | grep Mem | awk '{print (N * 1024 - $7)/(N * 1024) * 100.0 "%"}'

Network

Network Traffic

Total network traffic

nload
speedometer -t eth0
bmon

traffic by socket

iftop
iftop -F 192.168.0.1/16

traffic by process ID (PID)

nethogs

Network Speed

speedtest-cli

# install speedtest-cli
sudo apt install speedtest-cli
# or
sudo yum instsall speedtest-cli

# run speed test
speedtest-cli
speedtest-cli --simple
# or
speedtest
speedtest --simple

IP Address

LAN/private IP address

ifconfig
# or
hostname -I
# or
ip route get 1.2.3.4 | awk '{print $7}'

Public IP address

curl ifconfig.me
curl ipinfo.io/ip

Public IP Information

curl ipinfo.io

Check Open Ports

nmap

Nmap adapts its techniques to use the best available methods using the current privilege level, unless you explicitly request something different. The things that Nmap needs root (or sudo) privilege for on Linux are: Sniffing network traffic with libpcap. Sending raw network traffic.

# fast scan top 100 open parts (-F)
sudo nmap --min-hostgroup 100 -sS -n -T4 -F <Target_IP>

# fast scan top 100 open parts (-F) when ping is disabled. Add -Pn.
sudo nmap --min-hostgroup 100 -sS -n -T4 -F -Pn <Target_IP>

# fast scan top 1000 ports (-top-ports)
sudo nmap --min-hostgroup 100 -sS -n -T4 -top-ports 1000 <Target_IP>

# fast scan a range of ports 20-80
sudo nmap --min-hostgroup 100 -sS -n -T4 -p20-80 <Target_IP>

# fast scan some specific ports 80,8080
sudo nmap --min-hostgroup 100 -sS -n -T4 -p80,8080 <Target_IP>

# scan ports are listening for TCP connections
sudo nmap -sT -p- <ip>

# scan for UDP ports use -sU instead of -sT
sudo nmap -sU -p- <ip>
  • Scan method
    • -sS: (TCP SYN scan) - SYN scan is the default and most popular scan option for good reasons. It can be performed quickly, scanning thousands of ports per second on a fast network not hampered by restrictive firewalls. It is also relatively unobtrusive and stealthy since it never completes TCP connections.
    • -sT: (TCP connect scan)
    • -sU: (UDP scans)
  • Faster scan
    • -n: (No DNS resolution) - Tells Nmap to never do reverse DNS resolution on the active IP addresses it finds. Since DNS can be slow even with Nmap’s built-in parallel stub resolver, this option can slash scanning times.
    • -T: Set a timing template
      • -T4: prohibits the dynamic scan delay from exceeding 10 ms for TCP ports. Note that a faster speed can be less accurate if either the connection or the computer at the other end can’t handle it, and is more likely to trigger firewalls or IDSs.
      • -T5: prohibits the dynamic scan delay from exceeding 5 ms for TCP ports.
    • --min-hostgroup numhosts: (Adjust parallel scan group sizes) Nmap has the ability to port scan or version scan multiple hosts in parallel.
  • Speicify ports
    • -F: (Fast (limited port) scan) Scan fewer ports than the default scan. Normally Nmap scans the most common 1,000 ports for each scanned protocol. With -F, this is reduced to 100.
    • –top-ports [number]: to scan the top [number] most common ports.
    • -p-: to scan 65535 TCP ports. Scanning all ports is too slow.
    • -p<from>-<to>: to scan a range of ports.
    • -p<port1>,<port2>: to scan specific ports.
    • -p<from>-<to>,<port1>,<port2>: to scan multiple ports.
  • Other
    • -Pn: (No ping) This option skips the host discovery stage altogether. When ping is disabled on target server, we need add -Pn to skip ping.

States of nmap

  • Accessible states
    • open: An application is actively accepting TCP connections, UDP datagrams or SCTP associations on this port.
    • closed: A closed port is accessible (it receives and responds to Nmap probe packets), but there is no application listening on it.
    • unfiltered: The unfiltered state means that a port is accessible, but Nmap is unable to determine whether it is open or closed.
  • Inaccessible states
    • filtered: Nmap cannot determine whether the port is open because packet filtering prevents its probes from reaching the port. The filtering could be from a dedicated firewall device, router rules, or host-based firewall software. These ports frustrate attackers because they provide so little information.
    • open|filtered: Nmap places ports in this state when it is unable to determine whether a port is open or filtered.
    • closed|filtered: This state is used when Nmap is unable to determine whether a port is closed or filtered. It is only used for the IP ID idle scan.

Operating System Information

Operating System

Linux Distro name and version

cat /etc/os-release
cat /etc/*-release
# or
lsb_release -a
# or
hostnamectl

Linux kernel version

uname -a
uname -r
uname -mrs
# or
cat /proc/version

System hostname and related settings

hostnamectl

Start date and time of operating system

uptime -s
uptime
# start time of the pid=1 proccess
ps -p 1 -o lstart

Environment Variables

Environment variables

env
# or
printenv

PATH

echo -e ${PATH//:/\\n}

Processes

Processes Management

View Processes

top
ps -ef
ps aux

View listening ports

sudo lsof -i -P -n | grep LISTEN
sudo netstat -tulpn | grep LISTEN
sudo ss -tulpn | grep LISTEN
sudo nmap -sTU -O IP-address-Here

Kill a Process

kill <PID>
kill -9 <PID>

Kills a process by searching for its name

pkill -9 -f YOUR_PROCESS_NAME
# or
pgrep -f YOUR_PROCESS_NAME | xargs kill -9
# or
ps -ef | grep YOUR_PROCESS_NAME | awk '{print $2}' | head -1 | xargs kill -9
# or
ps -ef | grep YOUR_PROCESS_NAME | awk '{print $2}' | tail -1 | xargs kill -9

Process Information

Process start time

ps -p <pid> -o lstart,etime

Process basic information

ps -p <pid> -o pid,cmd,lstart,etime,pcpu,pmem,rss,thcount
  • lstart: accurate start time. e.g. Thu Nov 14 13:42:17 2019
  • start: start time of today or date. e.g. 13:42:17 or Nov 14
  • etime: elapsed time since the process was started, in the form [[DD-]hh:]mm:ss.
  • etimes: elapsed time since the process was started, in seconds
  • pid: process ID.
  • cmd: simple name of executable
  • pcpu: %CPU
  • pmem: %MEM
  • rss: memory use in bytes
  • thcount: thread count

References

[1] 10 Commands to Collect System and Hardware Info in Linux

[2] bash shell configuration files

[3] Configuration Files in Linux

MySQL Full-Text Index

MySQL 5.7.6 supports full-text index for Chinese, Japanese, and Korean (CJK).

The built-in MySQL full-text parser uses the white space between words as a delimiter to determine where words begin and end, which is a limitation when working with ideographic languages that do not use word delimiters. To address this limitation, MySQL provides an ngram full-text parser that supports Chinese, Japanese, and Korean (CJK). The ngram full-text parser is supported for use with InnoDB and MyISAM.

Create a Full-Text Index

Creating a FULLTEXT Index that Uses the ngram Parser

CREATE FULLTEXT INDEX content_fulltext ON table_name(column1, column2,...) with parser ngram;

Full-Text Searches

Natural Language Full-Text Searches

SELECT COUNT(*) FROM articles
WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
-- or
SELECT COUNT(*) FROM articles
WHERE MATCH (title,body) AGAINST ('database');

the columns named in the MATCH() function (title and body) are the same as those named in the definition of the article table’s FULLTEXT index. To search the title or body separately, you would create separate FULLTEXT indexes for each column.

Boolean Full-Text Searches

-- retrieves all the rows that contain the word “MySQL” but that do not contain the word “YourSQL”
SELECT * FROM articles WHERE MATCH (title,body)
AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
  • + stands for AND
  • - stands for NOT
  • [no operator] implies OR. The word is optional, but the rows that contain it are rated higher.
  • “”. A phrase that is enclosed within double quote (") characters matches only rows that contain the phrase literally, as it was typed.

References

[1] ngram Full-Text Parser - MySQL 5.7 Documentation

[2] MySQL 全文索引

[3] MySql5.6全文索引 及 5.7 中文索引插件ngram

A column should represent only one dimension/concept/meaning

Why:

  1. It creates confusion. It makes your code complex and hard to understand.

For example, status column should only represent enable or disable. If you want to represent if it’s formal, you can add a column formal.

Multiple groups of the same structure columns should be put into another separate table

Why:

  1. It may cause business logic process to become more complex.
  2. low scalability. It’s hard to add a new type of group of columns and add new columns for each group.

How:

report (id, name, s1_content, s1_status, s1_submit_time, s2_content, s2_status, s2_submit_time,...)

=>

report (id, name)

report_section (id, report_id, content, status, submit_time)

Moving TEXT/BLOB column to a separate table

Why

If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.

MySQL you are discouraged from keeping TEXT data (and BLOB, as written elsewhere) in tables frequently searched.

References

[1] MySQL Documentation Chapter 8 Optimization

[2] MySQL Table with TEXT column - Stack Overflow

Basic Selectors

Select All

$("*")

Select by element id

$("#id")  

Select by element class name

$(".className")  

Select by element tag name

$("div")  

Select by multiple condition union

$("selector1,selector2,...,selectorN ")

Select by tag name and class name

$('div.my-class')

Hierarchical Selectors

Select Children and Descendant

$("form > input") // all direct children
$("table td") // select all descendant

Select After

$("label + input") // select all input after label

Select all Siblings

$("#prev ~ div") // select all siblings

Selector Filters

Filter by Position

$("tr:first")
$("tr:last")
$("tr:even")
$("tr:odd")
$("tr:eq(1)")
$("tr:gt(1)") // greater than
$("tr:lt(3)") // less than

Filter by Content

// :contains
$("div:contains(hello)") // select all div contains text 'hello'

// :empty
$("td:empty") // select all td text is empty

// :has()
$("div:has(p)") // select all div has <p> element

// :parent()
$("div :parent(p)") // select all div contained by <p> element

Filter by Attribute

// [attr]
$("div[id]") // all <div> have id attr

// [attr="value"]
$("div[id=div1]")

// [attr!="value"] not equal
$("div[id!=div1]")

// [attr^="value"] attr value start with
$("div[id^=test]")] // all div, 'id' attr value start with 'test'

// [att|="value"] attr value equals or start with
$("div[id|=test]")]

// [attr$="value"] attr value end with
$("div[id$=test]") // all div, 'id' attr value end with 'test'

// [attr*="value"] attr value contains
$("div[id*=test]") // all div, 'id' attr value contains 'test'

// [name~="value"] attr value split with whitespace. splited values contains
$( "input[name~='man']" )

Filter by attribute and class name

$('.myclass[reference="12345"]')
$("input[reference=12345].myclass")

Filter by Visibility

// :hidden
$("input:hidden")

// :visible
$("input:visible")

Select Child

// :nth-child
$("ul li:nth-child(2)") // match ul child li second

// :first-child
$("ul li:first-child")

// :last-child
$("ul li:last-child")

// :only-child
$("ul li:only-child") // match ul only contain one li child

Select Form

Filter by element name

:button	//Selects all button elements and elements of type button.
:input //Selects all input, textarea, select and button elements.

Filter by input element type

:checkbox
:file
:image
:text
:password
:radio
:reset
:submit

Filter by status of form element

:checked 	//Matches all elements that are checked or selected.
:selected
$("option:selected[value=1]")
:focus //Selects element if it is currently focused.
:disabled
:enabled //Selects all elements that are enabled.

:Not

// :not()
$("input:not(:checked)")
$("#form1 input:not('#id')")

Others

:header // select all header elements, such as <H1>,<H2>
:animated

Filter Methods

Filter Methods

filter("p") // get all <p> elements in jquery dom set
$("li").has("span") //get all li has child element 'span'
not("p, .name") // get all not <p> and class=name elements in jquery dom set

Position Method

first()
last()
eq("") // index begin with 0

Hierarchical Methods

Select parent or ancestors

parent() // one parent
parents() // all ancestors
$("span").parents("ul"); // all ancestors
$("span").parentsUntil("div") // returns all ancestor elements between two given arguments

Select children or descendants

children("") 
children()
find(""); // find descendants
// the find() is similar to the children() method,but childern() only find children not descendants.

Select siblings

siblings() 
siblings("") // get other dom of same level, don't contain self
next()
nextAll()
nextUntil()
prev()
prevAll()
prevUntil()

References

[1] Selectors - jQuery Documentation

0%