mysql/sql updates

2 files changed, 64 insertions, 21 deletions

diff --git a/software/mysql.page b/software/mysql.page
index 5a26b2c..45eccff 100644
--- a/software/mysql.page
+++ b/software/mysql.page
@@ -1,4 +1,66 @@
+---
+format: md
+toc: no
+...
 
-Faster imports:
+### TSV File Dumps
 
-http://dba.stackexchange.com/questions/13446/slow-load-speed-of-data-from-mysqldump
+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
diff --git a/software/sql.page b/software/sql.page
index 881a040..2d2a307 100644
--- a/software/sql.page
+++ b/software/sql.page
@@ -5,25 +5,6 @@ toc: no
 
 # SQL
 
-### MySQL-specific Tricks
-
-To output in tab-delimited format, do something like:
-
-    mysql -u root -p -B < some_command.sql > output.tab
-
-### Setup User Permissions
-
-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;
-
 ### UPDATE with JOIN
 
 To do an update based on a join::