Nethence Newdoc Olddoc Lab Your IP BBDock  

Warning: those guides are mostly obsolete, please have a look at the new documentation.


Setting up MySQL
If you are upgrading,
mysql_upgrade -u root -p
If it's a fresh installation, secure the service,
Note. never mind this error when using mariadb, it's a leftover and has been fixed in MariaDB-10.0.15.
find_mysql_client: command not found
Check the permissions,
ls -ld /var/db/mysql/
chown mysql:mysql /var/db/mysql/
chmod 700 /var/db/mysql/
Listen only on localhost,
cd /etc/
cp my.cnf my.cnf.dist
vi my.cnf
add that to the [mysqld] paragraph,
service mysqld restart
netstat -apne | grep mysqld
Post-installation on 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,
and start the daemon,
/usr/local/mysql/bin/mysqld_safe --user=mysql &
Usage & Administration
Connect to MySQL,
mysql -u root -p
Show databases,
show databases;
Create a database,
create database DBNAME;
Remove a database,
drop database DBNAME;
Show users,
select host, user, password from mysql.user;
Create a user and grand write access to the database,
create user USERNAME identified by 'PASSWORD';
grant all privileges on DBNAME.* to USERNAME;
or only read,
#grant usage on DBNAME.* to USERNAME;
Note. single quotes aronud the password are mandatory.
Note. those two lines may be combined:
grant all privileges on roundcubemail.* to roundcube@localhost identified by 'PASSWORD';
Note. on remote instances, use,
Remove a user,
drop user USERNAME@localhost;
Reset mysql's root password
Change mysql root password,
mysqladmin -u root -p password 'PASSWORD'
Ref. Security options (like e.g. --secure-auth)
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’;
Migrations / Upgrades
Cold migration with upgrade
Once you've placed the old /var/lib/mysql in place, check and update tables that need to be updated,
mysql_upgrade -p
Export / Import a database on another server
Note. you may connect to a MySQL server remotely using,
-h remote_mysql_srv
Export / Import all databases,
mysqldump -u root -p --all-databases | gzip > all-databases.sql.gz
(send the archive to the new server)
gunzip all-databases.sql.gz
mysql -u root -p < /var/tmp/all-databases.sql.sql
Export / Import only one database,
mysqldump -uUSER_OR_ROOT dbname | gzip > dbname.sql.gz
(send the archive to the new server)
gunzip dbname.sql.gz
mysql -p -uUSER DBNAME < dbname.sql
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;
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]
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

(obsolete, see the new doc)