February 15, 2006

MySQL Start Script

I installed mysql-server-4.0.21 when I got the server more than one year ago. Although MySQL 4.0 (4.0.26 as for now) only released small patches thereafter, the start script for FreeBSD has made significant changes:

1. Must enable MySQL in /etc/rc.conf.
The following line must be present in /etc/rc.conf:
mysql_enable="YES"

Without this line, "/usr/local/etc/rc.d/mysql-server.sh start" will quit without any errors or warnings. It took me a few hours to figure this out...a simple reminding message would be very helpful.

For a complete list of parameters which can be specified in rc.conf, please refer to mysql-server.sh (appended below).

2. MySQL runs as mysql user instead of root.
Great change.

3. my.cnf is moved from /etc/ to /usr/local/etc/.
Good change. This can avoid confusions since /etc/my.cnf is Linux style.

=======================================
Content of /usr/local/etc/rc.d/mysql-server.sh

Continue reading "MySQL Start Script"

Posted by FreeBSD Newbie at 06:33 PM | Comments (0)

October 27, 2005

MySQL Log File

I didn't know MySQL log file until I tried to clean up unused databases today, the default file path is /var/db/mysql/hostname.err.

In my log file, there are many error messages about one certain table, the error is like:
read_next: Got error 127 when reading table ./database_name/table_name

MySQL tried to repair the table many times according to the log message, but the problem still exists. Made a quick search, it might be a bug of MySQL 4.0, some pages said it could be fixed by repairing the table, which didn't work for me.

Repair table with MySQL command line:
mysql> repair table table_name;

Posted by FreeBSD Newbie at 09:53 PM | Comments (0)

April 19, 2005

Using Perl and MySQL

I moved this Blog to my FreeBSD server, this Blog uses MovableType Blog system which requires perl (as cgi file) and MySQL. To make the posting working, I did the following things:

1. Uncomment the following line in the Apache configuration file /usr/local/etc/apache/httpd.conf.
AddHandler cgi-script .cgi .pl

2. Install perl-mysql module
# cd /usr/ports/databases/p5-Mysql
# make install

Posted by FreeBSD Newbie at 12:54 AM | Comments (0)

January 13, 2005

Create MySQL User

Using grant to create new mysql user, the following two commands will create a new user named "user_name" with password "somepassword", the new user has all the permission on database "database_name".

mysql> grant usage on database_name.* to user_name@localhost IDENTIFIED BY 'somepassword' WITH GRANT OPTION;

mysql> grant all on database_name.* to user_name@localhost;

Test the new account
# mysql -u user_name -p

Posted by FreeBSD Newbie at 08:02 AM | Comments (0)

November 04, 2004

Optimizing MySQL

A ServInt forum member posted the following tutorials for MySQL optimization:
MySQL's Query Cache
Optimizing MySQL: Queries and Indexes
Optimizing MySQL: Hardware and the Mysqld Variables

The first one is directly related to administration, I followed the article and inserted two lines into the file "/etc/my.cnf".

query-cache-type = 1
query-cache-size = 20M

But this doesn't work, this can be checked with the following MySQL command.
mysql> SHOW STATUS LIKE '%qcache%';

I hate it when you followed an article carefully but it didn't work. Tanfwc on ServInt forum shared his my.cnf, it works great after I merged it into mine. If you use this file, make sure your my.cnf doesn't have duplicated entries and insert the code into the right sections.

[mysqld]
max_connections = 500
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Here is the new output with query cache enabled:
mysql> SHOW STATUS LIKE '%qcache%';

+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 671 |
| Qcache_inserts | 17227 |
| Qcache_hits | 46232 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 152 |
| Qcache_free_memory | 31787024 |
| Qcache_free_blocks | 238 |
| Qcache_total_blocks | 1687 |
+-------------------------+----------+
8 rows in set (0.01 sec)

Posted by FreeBSD Newbie at 09:24 AM | Comments (0)