Linux goesZen

Install mysql with

apt-get install mysql

.

The mysql config file can be found at /etc/mysql/my.cnf. Note that additional conf files may be linked from this file.

If you are running mysql on your server, this means a permanently running daemon (a persistent type of program) will be present. It serves database connections on standard port 3306 or 3333, depending on version. The port can be set in

my.cnf

, which might be a tiny optimization in overall security, although I've never felt this was needed. See the daemon like this:

ps aux | grep mysql

. You can monitor its cpu consumption with the top command

top

. Press space to force a refresh (standard intervall in top is about 5 seconds).

On debian, you can start and stop the mysqld daemon with

/etc/init.d/mysql stop|start

. To actually reload a changed config file, "force-reload" might not be enough (had it once..).

You can log into the mysql console to execute operations directly etc. To do so you might need a username and password:

mysql -u <username> -p<password>

Q: Where are the mysql log files?
A: mysql usually writes log files to /var/log/mysql/

Q: How can I optimize mysql, i.e. for large tables?
A: Never just copy and past any changed conf values from forums etc. What might be good for one setup may actually hurt another. So setup a test environment or make gradual changes based on your observations. tuning.primer.sh is an executable sh script from day32.com. It tells you verbosely based on your stats what can be improved in your setup:

cd /usr/bin
wget http://www.day32.com/MySQL/tuning-primer.sh
chmod 755 tuning-primer.sh
sh tuning-primer.sh

Q: How can I optimize mysql?
A: It's a good idea to log time consuming queries. If you already ran tuning-primer.sh, you were already told so. You usually write this log into the /var/log/mysql/ directory. After a few minutes have a peek inside with

tail -f /var/log/mysql/mysql-long.log

. Try optimizing these queries first. Add indexes for frequent looked-up-by table rows, for example the "id" row, which will increase performance instantly.

Q: My mysqld consumes all CPU, cpu is 100% or more. What to do?
A: As it appears one of your tables had grown too big to be handled properly by your setup, probably 1.000.000 entries on a table or more. If possible, shrink the table to get instant relief. Then investigate how you can optimize your mysqld to handle the large table.

Q: How do I reset or change the auto_increment value (the next auto inserted id)?
A: With the following sql query. Note that it will not set the counter to 1. It is more clever and will scan your table for the highest primary id and set the auto-increment value to the next higher value:

ALTER TABLE tablename AUTO_INCREMENT = 1;

More to read:
A good starting point post is Federico's post.
Or the 10 tips that don't suck.