m (→Change password) |
m (→MySQL) |
||
| (6 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | [[Image:mysql-logo.png|200px|right]] | ||
| + | |||
= MySQL = | = MySQL = | ||
| − | MySQL is the best database we know. That's what we use. | + | MySQL is the best database management we know. That's what we use. |
| + | |||
| + | == Basic Admin == | ||
| + | |||
| + | To know whether the database server is functional: | ||
| + | <pre> | ||
| + | sudo service mysql status | ||
| + | </pre> | ||
== Survival kit == | == Survival kit == | ||
| Line 15: | Line 24: | ||
GRANT ALL PRIVILEGES ON *.* TO 'laussy'; | GRANT ALL PRIVILEGES ON *.* TO 'laussy'; | ||
</pre> | </pre> | ||
| + | |||
| + | === Export a database === | ||
| + | |||
| + | From the command line: | ||
| + | |||
| + | <pre> | ||
| + | mysqldump -u laussy -p wikilaussy > wikilaussy-24-jan-10.sql | ||
| + | </pre> | ||
| + | |||
| + | 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 === | === Import a database === | ||
| Line 28: | Line 47: | ||
=== Change password === | === Change password === | ||
| − | See [http://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/] | + | Check [https://askubuntu.com/a/1110137/2068661 this recommendation] to figure out the temporary password (in <tt>/etc/mysql/debian.cnf</tt> for me). So indeed log as this debian maintenance guy with the password you 'got' from the <tt>cnf</tt> file: |
| + | <pre> | ||
| + | mysql -u debian-sys-maint -p | ||
| + | </pre> | ||
| + | |||
| + | and then use the new syntax: | ||
| + | <pre> | ||
| + | ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword'; | ||
| + | FLUSH PRIVILEGES; | ||
| + | exit; | ||
| + | </pre> | ||
| + | |||
| + | --- | ||
| + | |||
| + | See [http://ubuntu.flowconsult.at/en/mysql-set-change-reset-root-password/]. This below appears to apply to mysql version that are now obsolete! | ||
Stop the MySQL Server. | Stop the MySQL Server. | ||
| Line 43: | Line 76: | ||
Replace YOURNEWPASSWORD with your new password! | Replace YOURNEWPASSWORD with your new password! | ||
UPDATE user SET Password=PASSWORD('YOURNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES; exit; | 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 [https://stackoverflow.com/questions/37879448/mysql-fails-on-mysql-error-1524-hy000-plugin-auth-socket-is-not-loaded] | ||
| + | |||
| + | === 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 [https://stackoverflow.com/questions/484750/restoring-mysql-database-from-physical-files restore the SQL database from files]. | ||
== Links == | == Links == | ||
* [http://blog.urfix.com/linux-command-line-mysql-awesome/ Linux command line and MySQL]. | * [http://blog.urfix.com/linux-command-line-mysql-awesome/ Linux command line and MySQL]. | ||
Latest revision as of 00:07, 20 December 2024
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.