[article_overview]
Install and configure MySQL innodb on Debian Lenny
[/article_overview]
Install mysql
You will be asked to set the root password for mysql
1
|
aptitude install mysql-server
|
Configure mysql to use innodb
innodb allows for row level locking and database transactions
By default the data files are in /var/lib/mysql/ and the binary log files are in /var/log/mysql
For this config, log files, binary log files, and data files will be stored in /home/mysql/
This config has only been tested on a new install of mysql.
You are responsible for
your data.
Choose a partition which has sufficient space on your server.
1
2
3
4
5
6
7
|
mkdir /home/mysql/
mkdir /home/mysql/data
mkdir /home/mysql/binary-log
mkdir /home/mysql/innodb-log
chown -R mysql:mysql /home/mysql/*
cp -R /var/lib/mysql/* /home/mysql/data/.
rm /home/mysql/data/*logfile*
|
Create a custom module to enable and optimize innodb usage.
MySQL on Debian will first read the default MySQL config file at /etc/mysql/my.cnf
and then overwrite and add config options from /etc/mysql/conf.d/*
1
|
vi /etc/mysql/conf.d/custom.cnf
|
MySQL config with comments derived from google, mysql.com, reading, prior MySQL experience, and educated guessing
This config seems to work well for a server with 2GB ram and low traffic.
Adjust as needed.
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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
|
[mysqld]
#
# Skip options
skip_bdb
#skip_innodb
skip_locking
#skip_networking
#
# Server ID must be unique to allow for replication
server_id = 1
#
# User name to run as
user = mysql
#
# Port and the socket
port = 3306
# socket = /tmp/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
#
# Bind to a specific address, otherwise listen to all addresses
bind_address = 127.0.0.1
#
# Maximum number of connections
#
max_connections = 100
#
# Connection backlog, raise this if we run out of
# connections (128 is the linux default)
back_log = 128
#
# Maximum number of connection error per host
max_connect_errors = 100
#
# Connection timeout
connect_timeout = 4
#
#
# Timeout for inactive connections
wait_timeout = 30
#
# Maximum packet length, no single MySQL statement can be longer than this
max_allowed_packet = 16M
#
# Network buffer length (I think this is the linux default)
net_buffer_length = 8K
#
# Set the default character set to utf8
default_character_set = utf8
#
# Set the server character set
character_set_server = utf8
#
# Set the default collation to utf8_general_ci
default_collation = utf8_general_ci
#
# Set the names to utf8 when a client connects
init_connect = 'SET NAMES utf8; SET sql_mode = STRICT_TRANS_TABLES'
skip-external-locking
#
# Server directories
#basedir = /usr/local/mysql
basedir = /usr
#datadir = /var/lib/mysql
datadir = /home/mysql/data
tmpdir = /tmp
#
# Language file location
#language = /usr/local/mysql/share/english
language = /usr/share/mysql/english
# Error log file (need dash in variable name)
log-error = /var/log/mysql/mysqld.err
#
# Log slow queries, time threshold set by 'long_query_time',
log_slow_queries = /var/log/mysql/slow-queries.log
# log_output = FILE # 5.1 only
long_query_time = 10
#
# Log queries which don't use indices in the slow query log
# log_long_format
# Enable this to get a log of all the statements coming from a client,
# this should be used for debugging only as it generates a lot of stuff
# very quickly
#log = /var/log/mysql/queries.log
#
#
# Binary log and replication log file names prefix
log_bin = /home/mysql/binary-log/server1-bin
# relay_log = /home/poplar/mysql/binary-logs/server1-relay-bin
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# Binary log format, see:
# http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
# http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html
#binlog_format = row # 5.1 only
#
# Binary log cache size
binlog_cache_size = 1M
#
# Skip automatic replication start up, replication will have to be
# started manually with 'start slave' once the server is started
skip_slave_start
#
# Make the slave read-only
#read_only
#
# Select which replication errors to skip, see
# http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
#slave_skip_errors = 1062
#
# Select which databases/tables to ignore during replication, see
# http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
#replicate_ignore_db =
#replicate_ignore_table =
#replicate_do_db =
#replicate_do_table =
#
# Number of open tables at any one time
table_cache = 256
#
# Join buffer size for index-less joins
join_buffer_size = 8M
#
# Maximum size for in memory temporary tables, anything
# larger gets spun out to disc
tmp_table_size = 128M
#
# Sort buffer size for ORDER BY and GROUP BY queries, data
# gets spun out to disc if it does not fit
sort_buffer_size = 8M
#
# Thread cache size, concurrency and stack
thread_cache_size = 64
thread_concurrency = 8
thread_stack = 192K
#
# Query cache
#
query_cache_size = 64M
query_cache_type = 1
query_cache_limit = 1M
#
# Default table storage engine when creating new tables
# (comment out when running mysql_install_db)
default_storage_engine = InnoDB
#
# Default transaction isolation level, levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# see: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
transaction_isolation = REPEATABLE-READ
#
# MyISAM options, see:
#
# http://dev.mysql.com/doc/refman/5.1/en/myisam-start.html
key_buffer_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_max_sort_file_size = 512M
myisam_repair_threads = 2
#myisam_recover_options = DEFAULT
#
# InnoDB options, see:
# http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
# Data directory, and data file
innodb_data_home_dir = /home/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
#
# Use one file per table
innodb_file_per_table
#
# Buffer pool size
innodb_buffer_pool_size = 384M # 2GB RAM
#innodb_buffer_pool_size = 2G # 4GB RAM
#innodb_buffer_pool_size = 10G # 16GB RAM
innodb_additional_mem_pool_size = 32M
#
# Transaction log location and sizes
innodb_log_group_home_dir = /home/mysql/innodb-log
innodb_log_files_in_group = 4
innodb_log_file_size = 64M # 2GB RAM
#innodb_log_file_size = 128M # 4GB RAM
#innodb_log_file_size = 512M # 16GB RAM
innodb_log_buffer_size = 8M
#
# Percentage of unwritten dirty pages not to exceed
innodb_max_dirty_pages_pct = 80
#
# Transaction commit policy
innodb_flush_log_at_trx_commit = 1
#
# Timeout to wait for a lock before rolling back a transaction
innodb_lock_wait_timeout = 50
#
# Flush method
innodb_flush_method = O_DIRECT
#
# Number of concurrent threads to run
innodb_thread_concurrency = 32
#
# Autoinc lock mode ('consecutive' lock mode), see:
# http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
#innodb_autoinc_lock_mode = 1 # 5.1 only
#
# Prevent extra locking, we can only use this if we have row
# level replication, see 'binlog_format'
#innodb_locks_unsafe_for_binlog # 5.1 only
#
# Enable fast innodb shutdown (skip full purge and insert buffer merge)
innodb_fast_shutdown = 1
#innodb_fast_shutdown = 0
#
# Dont delay insert, update and delete operations when purge
# operations are lagging
innodb_max_purge_lag = 0
#innodb_max_purge_lag = 1
#
# Force recovery mode, do not mess with unless you really know
# what you are doing, see:
# http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
#innodb_force_recovery = 4
|
Restart the mysql server
1
|
/etc/init.d/mysql restart
|
Check status of mysql
1
|
mysqladmin status -uroot
|
Note:
If you moved the data and binary log files, once the mysql server is working
Delete the default the data files in /var/lib/mysql/ and the binary log files in /var/log/mysql
1
2
|
rm /var/log/mysql.*
rm -r /var/lib/mysql
|
Reference:
http://forge.mysql.com/tools/tool.php?id=138
Again, the custom.cnf settings are fuzzily based on a server with 2GB of memory.
You will need to adjust and monitor and ajust per your server and applications usage
One setting to tweak for security
max_connect_errors
If there are more than this number of interrupted connections from a host, that host is blocked from further connections. You can unblock blocked hosts with the FLUSH HOSTS statement.
1
2
|
# Maximum number of connection error per host
max_connect_errors = 100
|
While you could set this to a low number and use it similiar to
fail2ban to block suspected hackers/dos, connect errors may happen due to bad applications, network issues, or other non-hacking reasons.
So create a cron job to flush hosts every hour so you do not permanently block users.
1
|
vi /etc/cron.d/flush-host
|
Add
1
2
3
4
|
# cron entry to flush mysql hosts
# related to max_connect_errors
7 * * * * root mysqladmin flush-hosts
|
And create a custom my.cnf for root to login
You could pass the username and password in the cron job,
but keeping mysql root credentials in one place in the root dir seems a little better
1
2
3
4
5
|
Add
[client]
user=root
password=<mysql password>
socket=/var/run/mysqld/mysqld.sock
|
Potential MySQL analyzers
These will probably only be somewhat useful once you have some databases and data
with MySQL and your applications have been running for a while
MySQLTuner is a high-performance MySQL tuning script written in perl that will provide you with a snapshot of a MySQL server’s health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL server’s efficiency and performance.
1
2
3
4
|
cd ~
wget http://mysqltuner.com/mysqltuner.pl
chmod 755 mysqltuner.pl
./mysqltuner.pl
|
Reference:
http://mediakey.dk/~cc/optimize-mysql-performance-with-mysqltuner/
day32 tuning-primer
Takes information from "SHOW STATUS LIKE..." and "SHOW VARIABLES LIKE..."
to produce sane recommendations for tuning server variables.
1
2
3
4
|
cd ~
wget http://www.day32.com/MySQL/tuning-primer.sh
chmod 755 tuning-primer.sh
./tuning-primer.sh
|
Refernce:
http://www.day32.com/MySQL/
If you already have apache and php running,
you can also manage mysql from a web based interface
1
|
aptitude install phpmyadmin
|
5