Phine Solutions web work notes

VPS with 726M ram

—- starting my.cnf —

[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqld]
skip-networking
skip-innodb
skip-locking

log-slow-queries
long_query_time=3

#log-bin=fmrbinlog
#binlog-do-db=fmr_fmr

key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 16M
thread_concurrency = 2

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

—- ending my.cnf —-Â

Note: key_buffer and table_cache are shared by all connections; read_buffer is for each thread.

further adjusted key_buffer down to 128M based on the tuning-primer.sh recommendation.

show some settings:

mysql>show variables;

mysql>show status;

Almost all my sites are database driven using MySQL. I use phpMyAdmin for data manipulation most of the time. But I also need a way to keep track of the design and quickly analyze which tables to touch in case there is need for data repair. I though about drawing them on paper but it can be hard to put down 30 tables.

Recently I tried a couple of free product and the goal is to use the reverse engineering functionality to create a visual table schema so I can refer to from time to time.

First I tried to use the MySQL GUI tool from the mysql.com.

The package includes some nice tools but I was only interested in the MySQLWorkbench. Once connected to the database, it can reverse engineer the table design and present it on a nice graphic page. I re-arranged the tables and decided to print it out. And here is when the problem came. No matter how I tried the tool won't print for me. It seemed to hang on my printing command the started to "not responding". After spending a couple of hours I had to give up on my nicely arranged table schema and move on.

After some research I discovered DBDesigner.

Besides other nice features it also has the reverse engineering feature. And most importantly, it works from top to bottom. After some work I have my table schema printed out and stick to the wall now.

Both tools can be downloaded as zip file and extracted directly to your hard drive without an "installation" process. And both tools offer a lot of nice features and what I was doing was just scratching the surface. Since MySQLWorkbench is still an alpha product  I can't complain too much. And hat off to DBDesigner, it really makes my work easier.

The regular query log:

The MYSQL query can be logged by starting mysqld with –log[=file]

or adding this line in my.cnf file: 

log=/var/log/mysql.log (/var/log/mysql.log is the actual file path)

Since the query log will record every query happening on every connection it is more or less a overhead for the mysql for server.

The logrotate utility can be used to manage the log files generated in this way. 

The binary log

The binary log records every updates happened in the database. As the name suggests, it is stored in a binary form . The parameters that are related to this setting:

log-bin[=binary-log-file-name]

binlog-ignore-db=the-db-to-ignore

binlog-do-db=the-db-updates-need-logging 

The log file(s) will be managed by the MYSQL server and each file will be appended with an index as the new file gets rolled in. The files look something like this:  mydb-bin.000001, mydb-bin.000002,…

Since it's in binary form, we can't view them directly. This command comes in for viewing the binary log:

% mysqlbinlog mydb-bin.001

The error log

The error log stores all the errors (obviously). In a Linux system, it can be found under /var/lib/mysql, which is the default installation path for MYSQL. Ideally you should only see messages like "mysqld started" and "mysqld ended"… type of messages.

The slow log 

This is a neat feature of MYSQL. Basically this log stores the "slow" queries that have run on the server. The default one will be servername-slow.log. To define "slow", you can add this to the my.cnf:

long-query-time=1 (comment: one second is too slow!) 

MYSQL reference manual. 

Create a new user: 

grant all on mydb.* to myuser@localhost identified by 'mypass';

show grants for ‘username’@'localhost’

revoke all privileges on db_name.* from ‘username’@'localhost’

drop user ‘username’@'localhost’