July 29th, 2010 by jde
FreeBSD 8.1
MySQL 5.1.48
MySQL is a very fast, reliable and light-weight database server – with MySQL you can go a long way with a single-server setup. The thing I really like about it, is that it is a transparent easy-to-use system – you don’t need a master degree to run it… and by the way, it’s free.
Installing MySQL
Install MySQL prior to any other software which depend on it. This ensures that you get your preferred version.
You need two applications … the MySQL server and the MySQL client. Both are found in /usr/ports/databases/. If you install the server first the client will also be installed automatically.
# cd /usr/ports/databases/mysql51-server/
# make install clean
After installation you must enable mysql in /etc/rc.conf to make it start up automatically after a reboot. Add this line:
mysql_enable="YES"
Setting the root password
When MySQL is installed and enabled, you can start it and choose a password for root…
# rehash
# cd /usr/local/etc/rc.d/mysql-server start
# mysqladmin -u root password my_secret_password
Change “my_secret_password” to a password of your own choice. If you use copy/paste and accidently set the password to “my_secret_password” (as I did – doh!), you can change it again like this:
# mysqladmin -u root -p password new_secret_password
Enter password: my_secret_password
How to start and stop the MySQL server
This is how to start and stop the server
cd /usr/local/etc/rc.d/
./mysql-server stop
./mysql-server start
You can check the processlist to see if it’s actually running:
ps -ax | grep mysql
65368 p0 S 0:00.3-8 [mysqld]
Unless you’ve started mysql with the skip-networking option, you should also see it listening on port 3306:
Use [CTRL]+[C] to quit the connection after line 6:
telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to localhost.example.tld.
Escape character is '^]'.
8
5.0.18-logR'i\qEDc,H|X#Dmw2/3T0
Connection closed by foreign host.
If you are having trouble stopping mysql, use mysqladmin to shut it down:
# mysqladmin -u root -p shutdown
Password:
After this, you can start it again with ./mysql-server start as shown earlier.
When you want to login and do stuff with MySQL, this is how to do it
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.48
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.08 sec)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> exit
Bye
If you want to type characters with umlauts, accents or special characters like æ, ø and å, you have to enable your shell / console for 8 Bit encoding.. click here to see how.
Security
Quote from freebsd diary
I only connect to my database server from localhost. So there is no need for network connections. Therefore, I add this flag to mysql-server: –skip-networking. This also eliminates the possibility that someone will connect to your database server over the network/Internet.
That sounds reasonable, so let’s open up /etc/rc.conf and insert this line:
mysql_args=" --skip-networking"
Then stop and start the MySQL server again.
Also:
Only give users access to do exactly what they need… no less, and especially no more.
Do not allow access via phpMyAdmin to users who do not need it (like system users).
Admin interface for MySQL
There is a nice and user friendly webbased gui for MySQL called phpMyAdmin which is described here.
MySQL configuration file
The configuration file for MySQL is the place for fine-tuning the database server. MySQL comes with 4 different examples for small, medium, large and huge systems. These files are located in /usr/local/share/mysql. In each of these example configurations (my-small.cnf, my-medium.cnf and so on…) you’ll find information on when you should use each of them based on available memory. To use one of them, just overwrite the current configuration, by copying it to /usr/local/etc/my.cnf.
Most of the variables in the configuration file, can also be set at runtime. phpMyAdmin is very helpful when you need an overview of the configuration, and hints on tuning.
When running a busy site, it is of cause important to use the correct type of configuration. But the real memory consumer is badly created indexes on tables. One faulty index on a frequently used table can completely take the breath away from any system… so the very first thing to do on a server with very high load, is to look at the slow-queries log. Your load averages can go from 30 to 0 in a minute, when a bad index is fixed.
You can log all queries, errors and/or slow queries.. the decision is yours. Add these entries in my.cnf to use one or more of these log options:
log=/var/log/mysql/mysql.log
log-error=/var/log/mysql/mysql-err.log
log-slow-queries=/var/log/mysql/mysql-slow-queries.log
log-queries-not-using-indexes
.. and set the correct permissions on the log files.:
# cd /var/log/
# mkdir mysql
# cd mysql
# touch mysql-err.log
# touch mysql.log
# touch mysql-slow-queries.log
# chmod 660 *
# chown mysql:mysql *
The ‘log’ entry causes each and every query to be logged. Use this only if you are debugging and application, since it is a performance killer.
‘log-error’ will log anything that goes wrong. ‘log-slow-queries’ will, as the name says, log any query that didn’t finish within n seconds (n is determined by the long_query_time configuration value). The presence of ‘log-queries-not-using-indexes’ ensures that the slow-queries log also contains any queries which does not use indexes.
Be aware that the log-* entries in the config file cannot be set at runtime. You must restart MySQL whenever you make changes.
As usual, make sure that you rotate the logfiles with newsyslog.
So let’s install the sieve ports…
