Mysql commands
From Logicalwebhost-Wiki
- there's a good tutorial here: http://www-css.fnal.gov/dsg/external/freeware/mysqlAdmin.html
| create database somedatabasename; | creates a new database |
| create table bikes ( id int unsigned not null auto_increment primary key, make varchar(20), model varchar(20), part_name varchar(60), year varchar(20), thumper_pn varchar(20), mfr_pn varchar(30), description text, price varchar(15), image_path_and_name varchar(50), info_link varchar(60) ); | example of how to fill up a table with stuff |
| delete from user where user='username'; | deletes a user, almost easier than rename, just delete then and then re-add them using the grant command below |
| DELETE from USERTABLE where homedir = '/clients/campforest.com/pdf/'; | deletes some entry that fits the command |
| describe some_table; | shows what fields are in a table, you have to know the name of the table, which you can get with the describe command |
| drop database somedatabase; | deletes a database |
| drop table sometable; | deletes a table from a database |
| mysqldump -u root -p somedatabase > dumpfilename.sql; | backs up a database |
| mysqldump --user=root -p --all-databases > all_db_backup062806.sql | backs up ALL your databases |
| grant all privileges on whateverdatabase.* TO 'whateveruser'@'localhost' identified by 'some_password' with grant option; INSERT INTO `users` (`email`, `password`, `quota`) VALUES ('sales@example.com', ENCRYPT('secret'), 100485760); INSERT INTO `domains` (`domain`) VALUES ('example.com'); | add a user |
| my.cnf | that's the main mysql config file |
| mysql -u root -p some_db_name < some_db_backup.sql | restores a single database, you can also use it to restore all databases you just backed up |
| mysqladmin -u root password new_password | sets the password after you install mysql |
| mysqladmin -u root -p password old_password | resets the password if you typed it wrong |
| select * from user; | shows you all the users on the machine |
| set password for root@localhost=old_password('enter_new_password_here') ; | resets a password from inside mysql |
| show tables; | show tables for a database |
| mytop -u root -p whatever -d specific_db | shows system load and other stuff for a single db |
| mytop -u root -p whatever | shows the whole mysql load, pretty cool :) |
| use somedatabasename; | changes databases |
