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

Sequel Pro is a great GUI MySQL tool in macOS (it’s open source and of course totally FREE) !

But from last year, their automated build system has been broken, so their built versions are outdated :

• Stable version 1.1.2 (Apr 3, 2016)
• Nightly version 3477d22387 (Sep 6, 2017)

UPDATED : Nightly versions have been released from Oct 29, check it at Test Builds

Even their codebase bas been updated every week but we can’t use the latest build within a lot of bugfixes. So here is the guide to build SequelPro from source using XCode 10

Step 1 : Clone source code

1
2

$ git clone https://github.com/sequelpro/sequelpro.git --depth=1
$ cd sequelpro

Step 2 : Change build config to Release

1

$ sed -i '' -e 's/Debug/Release/g' Makefile

Step 3 : Remove i386 (32 bits) from ARCHS environment variable

1

$ find . -type f -name "*.pbxproj" -exec sed -i '' -e 's/ARCHS_STANDARD_32_64_BIT/ARCHS_STANDARD_64_BIT/g' {} +

Step 4 : Build

1

$ make

Step 5 : Copy to Application folder

1

$ cp -R build/Release/Sequel\ Pro.app /Applications/Sequel\ Pro.app

ENJOY !!! 😎

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 !

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 ;)

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

Adding -D to dump only data structure

Example :

1

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

When a PHP process connects to MySQL server, the connection can be persistent if your PHP config has mysql.allow_persistent or mysqli.allow_persistent. (PDO has the attribute ATTR_PERSISTENT)

1

$dbh = new PDO('DSN', 'KhanhDepZai', 'QuenMatKhauCMNR', [PDO::ATTR_PERSISTENT => TRUE]);

Object destruction

PHP destruct an object automatically when an object lost all its references.

Example code:

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45

<?php

$x = null;

function klog($x) {
    echo $x . ' => ';
}

class A {
    private $k;
    function __construct($k) {
        $this->k = $k;
    }

    function b() {
        klog('[b]');
    }

    function __destruct() {
        klog("[{$this->k} has been killed]");
    }
}

function c($k) {
    return new A($k);
}

function d() {
    c('d')->b();
}

function e() {
    global $x;
    $x = c('e');
    $x->b();
    klog('[e]');
}

function f() {
    klog('[f]');
}

d();
e();
f();

Result:

1

[b] => [d has been killed] => [b] => [e] => [f] => [e has been killed] =>

Reducing PDO persistent connections in PHP long-run process (connect to multiples databases)

Instead of using a service object, we should use a factory design pattern for each job (each connection). PHP will close MySQL connection because it destructs object PDO. Then we can reduce the number of connections to MySQL at a same time.

I learned this case when implement a web-consumer (long-run process) to run database migration for multiples databases.

Before fixing this, our MySQL server had been crashed because of a huge opened connections.

Now, everything works like a charm !