Mysql basic

From FoppaWiki

Very basic mysql syntax

select:

mysql> show databases;
mysql> use webhotel;
mysql> show tables;
mysql> describe webhotel_ftp;
mysql> select * from 'webhotel_ftp' limit 1;
mysql> select * from webhotel_ftp where homedir like '%skybear%';

grant:

grant ALL PRIVILEGES ON *.* TO 'root'@'89.104.192' IDENTIFIED BY 'PaSsWoRd' WITH GRANT OPTION;
update user set Password = PASSWORD('PaSsWoRd') where User = 'root';
update ftpd set Dir = '/var/websites/html' where Dir = '/var/www/html';
show grants for 
revoke
DELETE FROM user WHERE User = 'root' AND Host= '89.104.192';
FLUSH PRIVILEGES;

mysqldump:

mysqldump -u root -p gallery2 > gallery2backup.sql
mysql -u root -p gallery2 < gallery2backup.sql

dump database directly from another mysql. This is only used in the situation where you want "sync" or update a database from another:

mysqldump DATABASE TABLE --host=10.0.0.12 --user=USERNAME --password='PASSWORD' --skip-add-locks --no-create-db --no-create-info --quick --single-transaction --quote-names 
| sed 's/INSERT INTO/REPLACE INTO/g' | mysql --host=localhost --user=USERNAME --password='PASSWORD' DATABASE

dump tables from database loop:

for i in $(ls -a | grep -i ".frm" | cut -d. -f1); do mysqldump -u USERNAME -p'PASSWORD' --skip-opt --quick DBNAME $i | gzip -v -9 > /output/directory/$i.sql.gz ; done 

copy a table:

CREATE TABLE recipes_new LIKE production.recipes;
INSERT recipes_new SELECT * FROM production.recipes;

update a table:

update release_mails set bit_bucket = '0' where bit_bucket = '2' and created > '2011-01-01';

show how table is created:

show create table DomesticNumber\G

create a table,insert and delete from that table, and empty it with truncate:

CREATE DATABASE door_control;
CREATE TABLE IF NOT EXISTS `door_control`.`user` ( `userid` INT AUTO_INCREMENT NOT NULL, `username` VARCHAR(3) NULL, PRIMARY KEY (`userid`)) 
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 COLLATE = utf8_bin;
INSERT INTO user (username) VALUES ('pej'),('mfo'),('lhj);
DELETE FROM user WHERE userid = 3;
TRUNCATE table user;

enable log in my.cnf

[mysqld]
log             = /var/log/mysqld.log
log-error       = /var/log/mysqld.err