MYSQL
From Blackcannon wiki
Main_Page --- MYSQL --- SSH --- Laptop --- mail_server --- backup policy --- mincom --- samba --- itc280
| Table of contents |
Good read on how to manage mysql
http://www.livejournal.com/community/debian/233262.html
Dump the sql library
the command line way...its mysqldump -uuser -ppass --opt -Q database > file.sql
Repair Tables
http://dev.mysql.com/doc/refman/5.0/en/repair.html
Import the sql library
mysql -uuser -ppassword database < file.sql
or
(00:02:27) defunkt313: mysqlimport -uuser -ppassword apolloproject < apolloproject.sql
mysql -uuser -ppassword go215 < groupofficenew.sql
Which database am I using?
(23:41:38) sgamb1e: I just need to know which database I'm using
(23:42:20) defunkt313: once inside the mysql shell, you can type "show databases;"
Recovering Root Password
(23:45:35) sgamb1e: can't remember root pass
(23:49:23) defunkt313:
Stop the db engine
/etc/init.d/mysql stop
Restart the daemon
/usr/bin/mysqld_safe --skip-grant-tables &
login to mysql
- mysql -u root (enter)
///////////////////////////////////////////
mysql >> use mysql; mysql >> update user set password=password("new_pass") where user='root'; (Replace new_pass with you new desired password)
mysql >> flush privileges;
mysql >> quit
Change Password
(You must be in the db in question to change passwd)
mysql -h your_hostname mysql
Change the password with GRANT
GRANT ALL PRIVILEGES ON * to root@localhost IDENTIFIED BY 'passwd';
FLUSH PRIVILEGES;
Create a new user
grant all on accounts.* to jsmith@localhost identified by 'Secret15';
More MYSQL made portable
mysqldump --all-databases -p > system_mysql_backup.sql
which you can then re-insert into your new database like this:
mysql -p < system_mysql_backup.sql
