Contents |
MySQL
MySQL is the best database management we know. That's what we use.
Basic Admin
To know whether the database server is functional:
sudo service mysql status
Survival kit
SHOW databases;
is may be the most important command. It shows you what's in store.
Create a user
CREATE USER 'laussy' IDENTIFIED BY 'mypass'; GRANT ALL PRIVILEGES ON *.* TO 'laussy';
Export a database
From the command line:
mysqldump -u laussy -p wikilaussy > wikilaussy-24-jan-10.sql
to export the database data (wikilaussy-24-jan-10.sql, a readable file with very long lines) from the database structure (named wikilaussy) above.
Import a database
From the command line:
mysql -u laussy -p wikilaussy < wikilaussy-24-jan-10.sql
to import the database data (wikilaussy-24-jan-10.sql, a readable file with very long lines) into the database structure (named wikilaussy) above.
Change password
Check this recommendation to figure out the temporary password (in /etc/mysql/debian.cnf for me). So indeed log as this debian maintenance guy with the password you 'got' from the cnf file:
mysql -u debian-sys-maint -p
and then use the new syntax:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'; FLUSH PRIVILEGES; exit;
---
See [1]. This below appears to apply to mysql version that are now obsolete!
Stop the MySQL Server.
sudo /etc/init.d/mysql stop
If this does not work, try "mysql stop/waiting" instead~[2]
Start the mysqld configuration.
sudo mysqld --skip-grant-tables &
Login to MySQL as root.
mysql -u root mysql
Replace YOURNEWPASSWORD with your new password!
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit;
Would you encounter with a
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'
then turn to [3]
Recover from files
Normally you dump the database from the server itself. If, however, you only have access to the hard drive (bad things having happened to the system), you can restore the SQL database from files.