software/mysql

TSV File Dumps

To output in tab-delimited format, do something like:

mysql -u root -p -B < some_command.sql > output.tab

Password-less User Setup

On contemporary Debian, to login as root do:

sudo mysql -u root

Then create a new password-less local-only user:

CREATE USER 'bnewbold'@'localhost' IDENTIFIED BY '';
UPDATE mysql.user SET plugin='unix_socket' WHERE user='bnewbold';
GRANT ALL PRIVILEGES ON * . * TO 'bnewbold'@'localhost';
FLUSH PRIVILEGES;

If you get errors about Plugin 'unix_socket' is not loaded, you need to use MariaDB instead.

Fast Imports

Add these to /etc/mysql/my.cnf and restart:

[mysqld]
# Optimizations for fast input
innodb_flush_method= O_DIRECT
innodb_doublewrite=0
innodb_support_xa=0
innodb_checksums=0
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite=off
innodb_checksums=none

# Fast input, uses tons of RAM:
innodb_log_file_size=2G
innodb_buffer_pool_size=4G

If you have a faster disk you want to run from, mount it, stop mysql, and copy over data. This assumes you have effectively no data in mysql already; if you do you’ll need to figure out how to copy just the system/authentication tables over.

sudo cp -r /var/lib/mysql /mnt/fast-disk
sudo chown -R mysql:mysql /mnt/fast-disk

Add the following to /etc/mysql/my.cnf:

[mysqld]
datadir=/mnt/fast-disk/mysql

And restart.

Via: http://dba.stackexchange.com/questions/13446/slow-load-speed-of-data-from-mysqldump

Enabled by --opt by default? https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html