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
|
