处理mysqldump导出文件案例之文件大小为0

一、mysql版本：
MariaDB [(none)]> select @@version;
+---------------------+
| @@version           |
+---------------------+
| 10.0.20-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)

二、问题描述：
 通过mysqldump导出lots库下的t_order表，没有任何报错，很快完成，检查导出文件大小为0。如下所示：

[apps@mvxl0781 mariadb10_data3306]$ mysqldump -uroot -psafe2016 lots t_order > t_order.sql
[apps@mvxl0781 mariadb10_data3306]$ ls -ltr
-rw-rw---- 1 apps apps        594 Apr 20 23:01 mvxl0781.log
-rw-rw-r-- 1 apps apps          0 Apr 20 23:01 t_order.sql

三、原因分析：
1.检查mysqldump工具的实际路径正常。
[apps@mvxl0781 mariadb10_data3306]$ which mysqldump
/apps/svr/mariadb10/bin/mysqldump
2.采用绝对路径和增加字符集参数导出，问题依旧。
[apps@mvxl0781 mariadb10_data3306]$ /apps/svr/mariadb10/bin/mysqldump --default-character-set=utf8 -uroot -psafe2016 lots t_order >t_order.sql
[apps@mvxl0781 mariadb10_data3306]$ ls -ltr
-rw-rw---- 1 apps apps        594 Apr 20 23:01 mvxl0781.log
-rw-rw-r-- 1 apps apps          0 Apr 20 23:10 t_order.sql

3.开启常规日志查询，检查mysqldump是否与数据库有交互

mysql> set global general_log=1;

mysql> show variables like 'general%'
    -> ;
+------------------+--------------+
| Variable_name    | Value        |
+------------------+--------------+
| general_log      | ON           |
| general_log_file | mvxl0781.log |
+------------------+--------------+
2 rows in set (0.00 sec)

[apps@mvxl0781 mariadb10_data3306]$ mysqldump -uroot -psafe2016 lots t_order > t_order.sql
[apps@mvxl0781 mariadb10_data3306]$ ls -ltr
-rw-rw---- 1 apps apps        594 Apr 20 23:01 mvxl0781.log
-rw-rw-r-- 1 apps apps          0 Apr 20 23:02 t_order.sql

---检查常规日志，没有发现mysqldump与数据库交互
[apps@mvxl0781 mariadb10_data3306]$ cat mvxl0781.log
/apps/svr/mariadb10/bin/mysqld, Version: 10.0.20-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /tmp/mysql3306.sock
Time                 Id Command    Argument
160420 23:01:13   344 Connect   root@localhost as anonymous on
                  344 Query     select @@version_comment limit 1
160420 23:01:16   344 Query     select DATABASE(), USER() limit 1
                  344 Query     select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit 1
                  344 Statistics
160420 23:01:32   344 Quit

4.问题有点怪，检查my.cnf配置文件，看是否能查到原因，mysqldump段有设置了错误日志:

[apps@mvxl0781 mariadb10_data3306]$ vi /etc/my.cnf
[mysqldump]
quick
max_allowed_packet = 2G
log-error=/apps/logs/mysql/dump3306.log
net_buffer_length=8k

检查日志文件，看到下面mysqldump在连接mysql时报错，找到问题的原因了，信息如下：
[apps@mvxl0781 dbdat]$ more /apps/logs/mysql/dump3306.log
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2 "No such file or directory")" w
hen trying to connect
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2 "No such file or directory")" w
hen trying to connect
mysqldump: Got error: 2002: "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2 "No such file or directory")" w
hen trying to connect

原因找到后，问题处理就比较好办了。

四、问题解决：

[apps@mvxl0781 dbdat]$ /apps/svr/mariadb10/bin/mysqldump --socket=/tmp/mysql3306.sock --default-character-set=utf8 -uroot -psafe2016 lots t_order >t_order.sql
[apps@mvxl0781 dbdat]$ ls -ltr
total 733380
drwxr-xr-x 7 apps apps      4096 Nov 17 15:17 mariadb10_data3306_old
-rw-rw-r-- 1 apps apps     12042 Apr 20 17:43 lock.txt
drwxrwxr-x 8 apps apps      4096 Apr 20 21:11 mariadb10_data3306
-rw-rw-r-- 1 apps apps 750953342 Apr 20 23:32 t_order.sql

五、深入研究

由于在配置文件中设置了mysqldump的error log，怀疑导出操作时，报错信息直接写log文件，而不直接提示错误，验证将配置文件中的log文件取消，如下：
vi /etc/my.cnf
[mysqldump]
quick
max_allowed_packet = 2G
#log-error=/apps/logs/mysql/dump3306.log
net_buffer_length=8k

重启mysql后，再用原来的脚本导入，这时报错就提示出来了，这样就很明显能定位到是什么原因，

[apps@mvxl0781 dbdat]$  mysqldump -uroot -psafe2016 lots t_order > t_order1.sql
Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

但同样都是客户端工具，登入操作直接可以正常连接mysql，如下：

[apps@mvxl0781 mariadb10_data3306]$ mysql -uroot -psafe2016
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.0.20-MariaDB-log MariaDB Server
MariaDB [(none)]>

而mysqldump则需要--socket=/tmp/mysql3306.sock，检查my.cnf配置文件中，发现如下：
[mysql]
no-auto-rehash
no-beep
default-character-set = utf8
socket=/tmp/mysql3306.sock
#prompt="\\@\\h \\d \\R:\\m:\\s> "
#tee="/apps/logs/mysql/audit.log"
#pager="less -i -n -S"
net_buffer_length=64K
unbuffered

这时真相一切大白。