Nethence Newdoc Olddoc Lab Your IP BBDock  


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

UnixWindowsOracleObsoleteHardwareDIYMechanicsScriptsConfigs

Setting up MySQL
 
http://pbraun.nethence.com/unix/www/apache2.html
http://pbraun.nethence.com/unix/databases/mysql.html
 
Post-installation
If you are upgrading,
mysql_upgrade -u root -p
 
If it's a fresh installation, secure the service,
/usr/local/bin/mysql_secure_installation
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
Ref. https://bugzilla.redhat.com/show_bug.cgi?id=1020055
 
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,
bind-address=127.0.0.1
apply,
service mysqld restart
netstat -apne | grep mysqld
 
Refs.
http://www.openfisma.org/mw/index.php?title=Installation
 
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,
/usr/local/mysql/bin/mysql_secure_installation
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;
Ref. http://alvinalexander.com/blog/post/mysql/show-users-i-ve-created-in-mysql-database
 
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,
'USERNAME'@'MYSQL_SERVER'
 
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)
http://dev.mysql.com/doc/refman/4.1/en/privileges-options.html
http://dev.mysql.com/doc/refman/5.0/en/privileges-options.html
 
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. http://www.mydigitallife.info/2006/06/06/change-and-reset-mysql-root-password/
 
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;
^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. http://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

(obsolete, see the new doc)