#TIL : The safest way to reset root password of MySQL Server

When you get stucked in this error message “Access denied for user ‘[email protected]’ …”, you search the way to reset the root password on the Internet, but life is Hard ! (No answer makes you feel it’s right way, even some do not work)

So to solve this problem, we need to understand MySQL Authentication

Step 1 : Disable MySQL Authentication by skip loading grant-tables on loading MySQL server

Open MySQL server config file, it might be in /etc/mysql/mysql.conf.d/mysqld.cnf. Add this line to section mysql

1
2
[mysqld]
skip-grant-tables

DANGER : BE CAREFULL ! AFTER RESETTING SERVER, YOUR MYSQL SERVER ALLOWS ANY CONNECTION FROM ANY USER FROM ANY HOST BY ANY PASSWORD

So safe way is to make sure that you are the only one connect MySQL, by

  • change to listening port of the server
1
2
3
4
[mysqld]
skip-grant-tables
port=6033
bind-address = 127.0.0.1
  • disable access through MySQL socket
1
$ sudo chmod 400 /var/run/mysqld/mysqld.sock

Step 2 : Restart the MySQL server

1
$ sudo systemctl restart mysql

Step 3 : Connect to mysql server by mysql cli, now you can connect free

1
$ mysql -h 127.0.0.1 -P 6033

Step 4 : Analyze mysql.user table

1
2
3
4
5
6
7
8
9
mysql> use mysql              
Database changed
mysql> select Host, User, plugin, password_expired, account_locked from user where User = 'root';
+-----------+------------------+-----------------------+------------------+----------------+
| Host | User | plugin | password_expired | account_locked |
+-----------+------------------+-----------------------+------------------+----------------+
| % | root | mysql_native_password | N | N |
+-----------+------------------+-----------------------+------------------+----------------+
4 rows in set (0.00 sec)

These fields meaning :

  • Host : allowed client host name or IP address
    • 127.0.0.1 : allow local clients connect via TCP
    • localhost : allow local clients connect via local UNIX socket file /var/run/mysqld/mysqld.sock
    • % : any wildcard, allow from all hosts
  • User : allowed user name
    • root : allow root user
  • plugin :
    • mysql_native_password : use hashing function of MySQL PASSWORD('YOURPASSWORD'), stored in authentication_string field (MySQL 5.7+) or password field (MySQL 5.6 or older)
    • auth_socket : use socket
  • password_expired :
    • Y : password is expired
    • N : password is not expired (still working)
  • account_locked :
    • Y : account is locked
    • N : account is not locked (still working)

Step 5 : Reset your password

Rewrite your sql command by replacing NEWPASSWORD and WHERE statement to match account we analyze in Step 4

MySQL 5.7+

1
mysql> update user set plugin = 'mysql_native_password', authentication_string = PASSWORD('NEWPASSWORD'), password_expired = 'N', account_locked = 'N' where Host = '%' and User = 'root';

MySQL 5.6 or older

1
mysql> update user set plugin = 'mysql_native_password', password = PASSWORD('NEWPASSWORD'), password_expired = 'N', account_locked = 'N' where Host = '%' and User = 'root';

Make sure that we changed 1 row by checking the result log : Query OK, 1 rows affected (0.00 sec)

Step 6 : Flushing privileges

1
2
mysql> flush privileges;
mysql> quit;

Step 7 : Rollback all config changes

Update your mysql server config file, make sure to comment out skip-grant-tables

1
2
3
4
[mysqld]
# skip-grant-tables
port=3306
bind-address = 127.0.0.1
1
$ sudo systemctl restart mysql

Trying to connect to MySQL server with your new password

1
$ mysql -u root -h 127.0.0.1 -p

If anything works perfectly, last step is enabling access to socket file

1
$ sudo chmod 777 /var/run/mysqld/mysqld.sock

HOPE IT HELP ! WE SOLVE PROBLEMS BY UNDERSTANDING IT !

#TIL : Curl override Name Resolution with specific IP address

You can overrride the Name Resolution with specific IP address without adding the hostname to /etc/hosts file by using --resolve option.

Syntax :

1
--resolve <host:port:address>

It will resolve IP address when connect to host on port

Example :

This will connect 127.0.0.1

1
$ curl --resolve google.com:80:127.0.0.1 "http://google.com/"

But this won’t connect 127.0.0.1, because we use 443 port for https

1
$ curl --resolve google.com:80:127.0.0.1 "https://google.com/"

For cover all ports, use * wildcard

1
$ curl --resolve google.com:*:127.0.0.1 "https://google.com/"

#TIL : View DNS history of a domain

You can check the history of a domain (A Record). It’s useful in case you forgot the old IP of domain.

Check it here : http://viewdns.info/iphistory/

Example : this is Github A record history http://viewdns.info/iphistory/?domain=github.com

#TIL : Create tiny chat channel via netcat

In a network, you can create a tiny chatting channel using netcat. It’s lightweight TCP protocol with plain-text transmission, so be carefully on using.

First, create a channel by picking port number (ex: 7777)

1
$ sudo nc -l 0.0.0.0 7777

Then, tell you friend your IP and channel port. He will use this info to connect the channel

1
$ nc 192.168.1.2 7777

Finnally, start chatting !! Each message will be send when you press [Enter]

Note: End the session by press Ctrl + D

netcat chatting

#TIL : Send a file through networking via netcat

If you’re working on 2 machines in same networking and want to send a file from machine A to machine B. But you don’t have USB, floopy disk :lol: or insanse Bluetooth. There is simple way to send a file to another computer without setting up SSH or SMB (althrough these way are safer than it).

On the machine A (with IP address : 192.168.1.2)

1
$ cat data.txt | sudo nc -l 0.0.0.0 6666

On the machine B

1
$ nc 192.168.1.2 6666 > here_the_data.txt

Have fun playing net😼 !! ;)

#TIL : Create a sequence of numbers

In the past, every time I want to create a sequence of numbers. I have to use something like MS EXCEL, then copy it and paste to text editor. It’s tricky way and slow !

Now, I can use the handy tool seq to achieve that

man seq

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SEQ(1)                                          User Commands                                         SEQ(1)            

NAME
seq - print a sequence of numbers

SYNOPSIS
seq [OPTION]... LAST
seq [OPTION]... FIRST LAST
seq [OPTION]... FIRST INCREMENT LAST

DESCRIPTION
Print numbers from FIRST to LAST, in steps of INCREMENT.

Mandatory arguments to long options are mandatory for short options too.

-f, --format=FORMAT
use printf style floating-point FORMAT

-s, --separator=STRING
use STRING to separate numbers (default: \n)

-w, --equal-width
equalize width by padding with leading zeroes

So we have 3 main arguments (same as for loop) :

  • FIRST
  • INCREMENT
  • LAST

And 3 options :

  • format : you can use string format like This is number %g
  • separetor : default is new line
  • equal width : padding with leading zeroes

Example :

1
2
3
4
5
6
$ seq -f"This is number %g" 3 4 20
This is number 3
This is number 7
This is number 11
This is number 15
This is number 19
1
2
$ seq -w -s", " 10
01, 02, 03, 04, 05, 06, 07, 08, 09, 10

#TIL : Cut file content from line to line

In case you have a big file which contains a lot of content (2+ GB). And you only need a small part from the file (the part is continuous string from line X to line Y).

You have many ways to achieve that :

  1. Use vi editor and delete from line 1 to line (X-1) by press [X-1]dd then go to line (Y-X+2) and delete to last line by press dG
  2. Use sed -n '[X][Y]p' [input_file] > [output_file]. Example : sed -n '15,68p' a.sql > b.sql
  3. Use head and tail trick : head -n[Y] [input_file] | tail -n[Y-X+1] > [output_file]

I personally recommend using the sed way, it’s faster and simpler to remember.