Home arrow Applications Tips arrow MySQL Tips
MySQL Tips PDF
Sunday, 01 June 2008
Tip 1. Repair a MySQL Database
-----------------------------------------
To repair a MySQL database that contains tables with errors use:

# mysqlcheck -r database_name -u user -p


Tip 2. Nice mysql client prompt
------------------------------------------
If you want to have a nice and useful prompt for mysql client add the following line to your mysql config file, usualy located in /etc/my.cnf.

Next line must be added at section [mysql]:

prompt=(\\u@\\h) [\\d]>\\_


Tip 3. Setting up root password for mysql
-------------------------------------------------------
After you've installed a mysql server is very useful to setup a root password for mysql so nobody will have access to mysql database, without a password, by default. This is a must do to secure your mysql server. Please keep in mind that mysql root password is different from your server's root password.

# mysqladmin password test12345

where test12345 is your mysql root password.


Tip 4. Disable access from outside to your mysql server
-------------------------------------------------------------------------
If you have your applications that need mysql on the same server, and as a security measure you want to cut access to mysql server from outside, add at [mysqld] section in your my.cnf file (usualy located in /etc/my.cnf) the following line:

bind-address=127.0.0.1

If you still need to access it from outside from known hosts, than this will not work for you but you could instead using this rule to block mysql port from firewall and permit access only from your outside hosts to mysql server ip/port.


Tip 5. Enable logs for mysql 5.1
-------------------------------------------------------------------------
If you need to enable general query logging for debugging purposes, you will have to edit the file /usr/local/etc/rc.d/mysql.server and add to command_args section the followings:
 
--log=/var/log/mysql.log --log-output=FILE
 
where --log[=file_name]enables general quey logs to the file "file_name" (if you don't specify anything, it will log default to hostname.log in data directory) and --log-output=value specifies log destination, which can be FILE, TABLE or NONE.
 
If you are using MySQL 5, to enable mysql logging of errors edit /usr/local/etc/rc.d/mysql.server and add:

--log-error=/var/log/mysql.log

 
< Prev   Next >

Other BSD Systems

OpenBSD

Misc

Solaris

Polls

Best BSD firewall?