#TIL : Export MySQL data by specified query to CSV file

To export data from MySQL by specified query to CSV file, you can use this command

1
$ mysql -B -u username -p database_name -h dbhost -e "SELECT * FROM table_name;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

Tip from : https://stackoverflow.com/a/25427665

#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 : Sleeping connections in MySQL

When you check your MySQL process list via command show processlist;, it will show you a useful table which provide all current connection details.

“Sleep” state connections are most connection pointer waiting for the timeout to terminate. Then they still count as a connection. (Can cause MySQL connection limit error, which default equal 150 connections)

So next time, remember to close your connection before terminating your app.

Every connection counts ;)

#TIL : Mycli : a new good cli MySql Client

This tool is written in Python with super cool features (auto-complete and colors).

Worth a shot !

Install

1
$ pip install mycli

Usage

1
$ mycli -h 127.0.0.1 -P 3306 -u root

Screencast

mycli

#TIL : Mysql dumping only table structure

Adding -D to dump only data structure

Example :

1
$ mysqldump -h 127.0.0.1 -u root -p"something" -D database1 > db.sql

#TIL : UNION vs UNION ALL

The difference is UNION command will sort and remove duplicated rows (RETURNED ONLY DISTINCT ROWS)

Examples :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
mysql> select '1', '2' union select '2', '1' union select '3', '4' union select '1', '2';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| 2 | 1 |
| 3 | 4 |
+---+---+
3 rows in set (0.00 sec)

mysql> select '1', '2' union select '2', '1' union select '3', '4' union select '1', '3';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| 2 | 1 |
| 3 | 4 |
| 1 | 3 |
+---+---+
4 rows in set (0.00 sec)

mysql> select '1', '2' union all select '2', '1' union all select '3', '4' union all select '1', '2';
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
| 2 | 1 |
| 3 | 4 |
| 1 | 2 |
+---+---+
4 rows in set (0.00 sec)

Tips

In case there will be no duplicates, using UNION ALL will tell the server to skip that (useless, expensive) step.

#TIL : String problems can cause logical bugs in application

Example table

1
2
3
4
5
6
7
8
9
mysql> describe `test`;
+------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+-------------------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| created_at | timestamp | YES | MUL | CURRENT_TIMESTAMP | |
+------------+-------------+------+-----+-------------------+----------------+
3 rows in set (0.00 sec)

Here is dump file of the table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;

INSERT INTO `test` (`id`, `name`, `created_at`)
VALUES
(1,'abc','2017-02-16 17:28:59'),
(2,' ABC','2017-02-16 17:29:14'),
(3,'ABC ','2017-02-16 17:29:21'),
(4,'aBc','2017-02-16 17:29:31');

/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;

Case Insensitive

When creating database, table and column, we have to set the default string COLLATION for them. And if we use collation end withs _ci, it means we ignore the Case Sensitive (or Case Insensitive).

Then "abc" == "ABC" or "abc" == "aBc" or "abc" = "Abc" or … (X = Y <=> UPPER(X) == UPPER(Y))

1
2
3
4
5
6
7
mysql> select ("abc" = "ABC") as `case1`, ("abc" = "aBc") as `case2`, ("abc" = "Abc") as `case3`, ("abc" = "abcd") as `wrong`;
+-------+-------+-------+-------+
| case1 | case2 | case3 | wrong |
+-------+-------+-------+-------+
| 1 | 1 | 1 | 0 |
+-------+-------+-------+-------+
1 row in set (0.00 sec)

String Trimming

Check this out !

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> select `id`, CONCAT("'", `name`, "'") as `name_with_quote`, `created_at` from test;
+----+-----------------+---------------------+
| id | name_with_quote | created_at |
+----+-----------------+---------------------+
| 1 | 'abc' | 2017-02-16 17:28:59 |
| 2 | ' ABC' | 2017-02-16 17:29:14 |
| 3 | 'ABC ' | 2017-02-16 17:29:21 |
| 4 | 'aBc' | 2017-02-16 17:29:31 |
+----+-----------------+---------------------+
4 rows in set (0.00 sec)

mysql> select `id`, CONCAT("'", `name`, "'") as `name_with_quote`, `created_at` from test where `name` = 'abc';
+----+-----------------+---------------------+
| id | name_with_quote | created_at |
+----+-----------------+---------------------+
| 1 | 'abc' | 2017-02-16 17:28:59 |
| 3 | 'ABC ' | 2017-02-16 17:29:21 |
| 4 | 'aBc' | 2017-02-16 17:29:31 |
+----+-----------------+---------------------+
3 rows in set (0.01 sec)

mysql> select `id`, CONCAT("'", `name`, "'") as `name_with_quote`, `created_at` from test where `name` = ' abc';
+----+-----------------+---------------------+
| id | name_with_quote | created_at |
+----+-----------------+---------------------+
| 2 | ' ABC' | 2017-02-16 17:29:14 |
+----+-----------------+---------------------+
1 row in set (0.00 sec)

BAAMMMMM !!!

holy-shit-the-illuminati-is-real

MySQL do right trimming the string value before comparing.

So you must be becareful to trim value before storing to MySQL to make everything consistent.

Storing Emoji in MySQL

Because Emoji use utf-8 4 bytes, so we must use the utf8mb4 charset (utf8 multi-bytes 4).

It’s safe when migrating charset from utf8 to utf8mb4 😎 , but not the reverse way 😅