Home > MySQL

Posts in MySQL

Disable Remote Access for MySQL

March 19, 2011 Posted in MySQL

I’m seeing messages like below in mysql error log on my new server:

110318 10:48:02 [Warning] IP address ‘x.x.x.x’ could not be resolved: no reverse address mapping.

I don’t need to access mysql remotely, it should be disabled for either performance or security. This can be changed by adding “skip-networking” in my.cnf, mysql will not listen on a TCP/IP port at all.

[mysqld]
skip-networking

MySQL Backup and Restore

March 12, 2011 Posted in MySQL

Backup single database
# mysqldump -u root -p db_name | gzip > sql.gz

Restore
# gunzip < sql.gz | mysql -u root -p db_name

Stop the Most Annoying Spammers

August 3, 2009 Posted in Apache, MySQL

Some spammers are very stupid and persistent,  they don’t care about the results, and keep spamming for months and even years. They combine manual and automate ways to post spams, the only way to stop them is blocking their IPs.

Read More

Monitor MySQL Log File

March 2, 2009 Posted in MySQL

I rarely check MySQL log file, but recently I got some table corruption errors, and it’s hard to notice these errors, especially when these tables are not critical. I realized that it’s necessary to monitor MySQL log file. According to MySQL manual, before version 5.1.20, it’s not possible to log MySQL errors to syslog. I’m still using 5.0, so I added the following line to cron job of my root account:

30 2 * * * tail -n 50 /var/db/mysql/hostname.err

Now I get notification email daily (no email if it’s empty), not a bad solution.

MySQL Optimization

January 20, 2009 Posted in MySQL

I only optimized MySQl on a VPS several years ago, it seems that my server never had to. I was just aware of those my.cnf examples coming along with the installation.

There are 5 example files under /usr/local/share/mysql/:

my-huge.cnf
my-large.cnf
my-medium.cnf
my-small.cnf
my-innodb-heavy-4G.cnf

Read More

MySQL Backup and my.cnf

October 21, 2008 Posted in MySQL

Check MySQL version when you restore database.

Recently I switched to a different hosting provider, I always directly tar the MySQL data directory, but this time I got lots of problems. Some websites worked well, some gave lots of table-corrupted errors. Later I found it’s due to the MySQL versions :-( . On my old server it’s 5.0, and 5.1 on the new one. Reinstalling 5.0 solved the problem. Read More

MySQL Start Script

February 15, 2006 Posted in MySQL

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: Read More

MySQL Log File

October 27, 2005 Posted in MySQL

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. Read More

Using Perl and MySQL

April 19, 2005 Posted in 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: Read More

Create MySQL User

January 13, 2005 Posted in MySQL

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”. Read More

Optimizing MySQL

November 4, 2004 Posted in MySQL

A ServInt forum member posted the following tutorials for MySQL optimization: Read More



Archives