OVH FreeBSD adventures 5: databases.
Recovered 2026-07-13 from the Wayback Machine capture of 2009-12-08 of my first blog (“Living in (this|their?) world”). Exact original publication date unknown; 2009-12-08 is the capture date (upper bound). Parts 1-4 of this series were never archived and are lost.
Setting up MySQL
Edit /etc/make.conf
.if (${.CURDIR} == "/usr/ports/databases/mysql51-server") || (${.CURDIR} == "/usr/ports/databases/mysql51-client")
WITH_CHARSET=utf8
WITH_XCHARSET=all
WITH_COLLATION=utf8_bin
WITH_OPENSSL=yes
BUILD_OPTIMIZED=yes
.endif
portmaster databases/mysql51-server databases/mysql51-client
Edit /etc/rc.conf
mysql_enable="YES"
/usr/local/etc/rc.d/mysql-server start
Basic setup
mysql -u root mysql
mysql> update user set Password=password('password_here') where User='root';
mysql> delete from user where User='';
mysql> delete from db where User='';
mysql> delete from user where Password='';
mysql> flush privileges;
Edit ~/.my.cnf
[client]
user = root
password = "password_here"
Now databases could be exported (using mysqldump) and imported.
Database export
mysqldump plop |bzip2 -c > plop.sql.bz2
Database and user creation
mysql
mysql> CREATE DATABASE plop CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> GRANT ALL PRIVILEGES ON plop.* TO 'plop'@'localhost' IDENTIFIED BY 'plop';
Database import
bunzip2 plop.sql.bz2
mysql plop < plop.sql
Setting up PostgreSQL
portmaster databases/postgresql84-server databases/postgresql84-client
Edit /etc/rc.conf
postgresql_enable="YES"
Initial setup
/usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql start
Database export
sudo su - postgres
pg_dump redmine | bzip2 -c > redmine.sql.bz2
Database and user creation
sudo su - pgsql
createuser -P -D -R -S redmine
createdb -O redmine -E UTF8 redmine
Database import
psql -d redmine -f redmine.sql