MySQL

mysqldump returning Errcode 13

• Last tested on Ubuntu 14.04.2 LTS (trusty)

When I try to create a CSV file from a table in the database I'd get an Errcode 13. Here I'm trying to extract a table called enc_codes3 from a database called testdb

mhan@dbserver:~$ mysqldump --tab=./testdump -uroot -p --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a -t testdb enc_codes3
Enter password:
mysqldump: Got error: 1: Can't create/write to file '/home/mhan/testdump/enc_codes3.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'

Make sure that the mysql daemon (mysql:mysql on Ubuntu) is able to write to the folder.

apparmor status can be checked via sudo aa-status.

Disable apparmor temporary ( service apparmor teardown ) execute the above command and then restart it again ( service apparmor start ).

Moving all of the databases from one server to another

• Last tested on Ubuntu 16.04.01 LTS (xenial)

Log in as an admin on MySQL Console and lock the database to allow only read operations.

mysql> flush tables with read lock;
mysql> set global read_only = on;
mysql> exit

Dump all of the databases into a file.

$ mysqldump --lock-all-tables -u root -p --all-databases > dbs.sql

Copy the dump to the new server. RSYNC is preferred over SCP, especially if the file is large.

$ rsync -tvz --progress dbs.sql mhan@newserver.com:~/files/
or
$ scp dbs.sql mhan@newserver.com:~/files/

The DB can be (optionally) unlocked. This may or may not be a good thing to do in your case. Do it at your own risk.

mysql> set global read_only = off;
mysql> unlock tables;
mysql> exit

On the new server, execute this command to import the new SQL dump.

$ mysql -u root -p < ~/files/dbs.sql

IMPORTANT: If your file is large, or you just have a lot of records, you may want to make sure you have something bigger than 16M for max_allowed_packet attribute in your my.cnf (usually found under /etc/mysql/ or /etc/mysql/mysql.conf.d/) on your new server where you're doing the import, else the server could hang on a large insert operation and your MySQL server may actually decide to go away, literally. On one of the servers I had it for 1024M just for this operation and brought it back low afterwards.