summaryrefslogtreecommitdiffstats
path: root/software/mysql.page
blob: 560bd9a1c6b59fc1bafef744688279dd7b28e6f9 (plain)
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
---
format: markdown
toc: no
...

### 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