MySQL configuration

MySQL configuration

PHP/MySQL on Redhat systems

Make sure those packages are installed,

rpm -q \

httpd \

php \

php-cli \

php-gd \

php-mbstring \

php-mcrypt \

php-mysql \

mysql \

mysql-server

Check PHP is enabled with Apache,

cat /etc/httpd/conf.d/php.conf

Enable the daemons and secure MySQL,

service mysqld start

/usr/bin/mysql_secure_installation

service httpd start

chkconfig httpd on

chkconfig mysqld on

chkconfig --list | grep httpd

chkconfig --list | grep mysqld

Secure even more, listen only on localhost,

vi /etc/my.cnf

add,

[mysqld]

...

bind-address=127.0.0.1

apply,

netstat -apne | grep mysql

service mysqld restart

netstat -apne | grep mysql

Ref. www.openfisma.org/mw/index.php?title=Installation

PHP/MySQL installation (NetBSD)

- install apache

- install ap-php (php depedency gets installed with it)

- install mysqld-server [...]

- edit /etc/httpd.conf . For Apache 2.x,

LoadModule php4_module lib/httpd/mod_php4.so

AddHandler application/x-httpd-php .php

and for Apache 1.x,

LoadModule php4_module lib/httpd/mod_php4.so

AddType application/x-httpd-php .php

- check for the init scripts

MySQL installation (HP/UX)

There's a nice HPUX.INSTALL or something into /usr/local/share/doc. It will tell you to add the mysql group and user. But instead of following it to the end just use the mysql provided script to secure the thing and configure the root password,

/usr/local/mysql/bin/mysql_secure_installation

and start the daemon,

/usr/local/mysql/bin/mysqld_safe --user=mysql &

Usage

Connect to MySQL,

mysql -uroot -p

To change mysql root password,

mysqladmin -uroot -p password 'PASSWORD'

Ref. Security options (like e.g. --secure-auth)

dev.mysql.com/doc/refman/4.1/en/privileges-options.html

dev.mysql.com/doc/refman/5.0/en/privileges-options.html

MySQL update

Once you've placed the old /var/lib/mysql in place, check and update tables that need to be updated,

mysql_upgrade -p

Reset mysql's root password

If you lost it, start mysqld like this,

mysqld_safe -skip-grant-tables

or add that argument to the init script.

Then reset the password,

mysql -u root mysql

At the mysql> prompt,

UPDATE user SET Password=PASSWORD(’newrootpassword’) WHERE User=’root’;

FLUSH PRIVILEGES;

Ref. www.mydigitallife.info/2006/06/06/change-and-reset-mysql-root-password/

Backup / restore

Backup all databases,

mysqldump -u'root' -p'PASSWORD' --opt --all-databases --add-drop-database | gzip > /path/to/mysql-all-databases.`date +%Y%m%d`.gz

Note. or -p < PASSWORD

Note. you do get the "create database" statements with this one.

Backup one database,

mysqldump -u'root' -p'PASSWORD' --opt DBNAME

Note. you do not get the "create database" statement with this one.

Restore a database,

mysql -u'root' -p

drop database DBNAME;

^D

mysql -p'PASSWORD' < dbname.sql

Note. you need the "create database" statement

Drop all tables in a database,

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Ref. www.thingy-ma-jig.co.uk/blog/10-10-2006/mysql-drop-all-tables

Notes

Note mysqld or other database engines somehow use swap instead of RAM to store the unsed data.

Create a database with a charset setting,

create database DBNAME charset utf8;

create database DBNAME charset latin1;

Non Apache CGI

If your httpd server isn't PHP capable (bozohttpd? thttpd? what else?), you can still use php as CGI.

Note. on NetBSD, php's CGI version is located here: /usr/pkg/libexec/cgi-bin/php