mysql command

http://www.pantz.org/software/mysql/mysqlcommands.html

• create a MySQL database and set privileges to a user
  

  # mysql -u root
  mysql> create database amarokdb;
  mysql> grant usage on *.* to amarokuser@localhost identified by 'amarokpasswd';
  mysql> grant all privileges on amarokdb.* to amarokuser@localhost ;
  # mysql -u amarokuser -p'amarokpasswd' amarokdb
  

• Reset the Root Password ( http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html )
  
  1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting. --user=mysql
  2. Connect to the mysqld server with this command:

     shell> mysql
     

  3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.

     mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
         ->                   WHERE User='root';
     mysql> FLUSH PRIVILEGES;
     

     The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
  You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networking options).
  
  

  Check out mysql_setpermission extension
  
  

• To login (from unix shell) use -h only if needed.

  # [mysql dir]/bin/mysql -h hostname -u root -p

  Create a database on the sql server.

  mysql> create database [databasename];

  List all databases on the sql server.

  mysql> show databases;

  Switch to a database.

  mysql> use [db name];

  To see all the tables in the db.

  mysql> show tables;

  To see database's field formats.

  mysql> describe [table name];

  To delete a db.

  mysql> drop database [database name];

  To delete a table.

  mysql> drop table [table name];

  Show all data in a table.

  mysql> SELECT * FROM [table name];

  Returns the columns and column information pertaining to the designated table.

  mysql> show columns from [table name];

  Show certain selected rows with the value "whatever".

  mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

  Show all records containing the name "Bob" AND the phone number '3444444'.

  mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

  Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

  mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

  Show all records starting with the letters 'bob' AND the phone number '3444444'.

  mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

  Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

  mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

  Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

  mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

  Show unique records.

  mysql> SELECT DISTINCT [column name] FROM [table name];

  Show selected records sorted in an ascending (asc) or descending (desc).

  mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

  Return number of rows.

  mysql> SELECT COUNT(*) FROM [table name];

  Sum column.

  mysql> SELECT SUM(*) FROM [table name];

  Join tables on common columns.

  mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

  Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

  # mysql -u root -p
  mysql> use mysql;
  mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
  mysql> flush privileges;

  Change a users password from unix shell.

  # [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

  Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

  # mysql -u root -p
  mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
  mysql> flush privileges;

  Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

  # /etc/init.d/mysql stop
  # mysqld_safe --skip-grant-tables &
  # mysql -u root
  mysql> use mysql;
  mysql> update user set password=PASSWORD("newrootpassword") where User='root';
  mysql> flush privileges;
  mysql> quit
  # /etc/init.d/mysql stop
  # /etc/init.d/mysql start

  Set a root password if there is on root password.

  # mysqladmin -u root password newpassword

  Update a root password.

  # mysqladmin -u root -p oldpassword newpassword

  Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

  # mysql -u root -p
  mysql> use mysql;
  mysql> grant usage on *.* to bob@localhost identified by 'passwd';
  mysql> flush privileges;

  Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

  # mysql -u root -p
  mysql> use mysql;
  mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
  mysql> flush privileges;
  
  or
  
  mysql> grant all privileges on databasename.* to username@localhost;
  mysql> flush privileges;

  To update info already in a table.

  mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

  Delete a row(s) from a table.

  mysql> DELETE from [table name] where [field name] = 'whatever';

  Update database permissions/privilages.

  mysql> flush privileges;

  Delete a column.

  mysql> alter table [table name] drop column [column name];

  Add a new column to db.

  mysql> alter table [table name] add column [new column name] varchar (20);

  Change column name.

  mysql> alter table [table name] change [old column name] [new column name] varchar (50);

  Make a unique column so you get no dupes.

  mysql> alter table [table name] add unique ([column name]);

  Make a column bigger.

  mysql> alter table [table name] modify [column name] VARCHAR(3);

  Delete unique from table.

  mysql> alter table [table name] drop index [colmn name];

  Load a CSV file into a table.

  mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

  Dump all databases for backup. Backup file is sql commands to recreate all db's.

  # [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

  Dump one database for backup.

  # [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

  Dump a table from a database.

  # [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

  Restore database (or database table) from backup.

  # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

  Create Table Example 1.

  mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

  Create Table Example 2.

  mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
  
  
• password policy:
  mysql> show variables like 'validate_password%';
  
  Change password policy, in server option file, my.cnf:
  [mysqld]
      plugin-load-add=validate_password.so
      validate_password_length=10
      validate_password_mixed_case_count=1
      validate_password_number_count=2
      validate_password_special_char_count=1
• MYSQL Statements and clauses

  ALTER DATABASE
  ALTER TABLE
  ALTER VIEW
  ANALYZE TABLE
  BACKUP TABLE
  CACHE INDEX
  CHANGE MASTER TO
  CHECK TABLE
  CHECKSUM TABLECOMMIT
  CREATE DATABASE
  CREATE INDEX
  CREATE TABLE
  CREATE VIEW
  DELETE
  DESCRIBE
  DO
  DROP DATABASE
  DROP INDEX
  DROP TABLE
  DROP USER
  DROP VIEW
  EXPLAIN
  FLUSH
  GRANT
  HANDLER
  INSERT
  JOIN
  KILL
  LOAD DATA FROM MASTER
  LOAD DATA INFILE
  LOAD INDEX INTO CACHE
  LOAD TABLE...FROM MASTER
  LOCK TABLES
  OPTIMIZE TABLE
  PURGE MASTER LOGS
  RENAME TABLE
  REPAIR TABLE
  REPLACE
  RESET
  RESET MASTER
  RESET SLAVE
  RESTORE TABLE
  REVOKE
  ROLLBACK
  ROLLBACK TO SAVEPOINT
  SAVEPOINT
  SELECT
  SET
  SET PASSWORD
  SET SQL_LOG_BIN
  SET TRANSACTION
  SHOW BINLOG EVENTS
  SHOW CHARACTER SET
  SHOW COLLATION
  SHOW COLUMNS
  SHOW CREATE DATABASE
  SHOW CREATE TABLE
  SHOW CREATE VIEW
  SHOW DATABASES
  SHOW ENGINES
  SHOW ERRORS
  SHOW GRANTS
  SHOW INDEX
  SHOW INNODB STATUS
  SHOW LOGS
  SHOW MASTER LOGS
  SHOW MASTER STATUS
  SHOW PRIVILEGES
  SHOW PROCESSLIST
  SHOW SLAVE HOSTS
  SHOW SLAVE STATUS
  SHOW STATUS
  SHOW TABLE STATUS
  SHOW TABLES
  SHOW VARIABLES
  SHOW WARNINGS
  START SLAVE
  START TRANSACTION
  STOP SLAVE
  TRUNCATE TABLE
  UNION
  UNLOCK TABLES
  USE
  

  String Functions

  AES_DECRYPT
  AES_ENCRYPT
  ASCII
  BIN
  BINARY
  BIT_LENGTH
  CHAR
  CHAR_LENGTH
  CHARACTER_LENGTH
  COMPRESS
  CONCAT
  CONCAT_WS
  CONV
  DECODE
  DES_DECRYPT
  DES_ENCRYPT
  ELT
  ENCODE
  ENCRYPT
  EXPORT_SET
  FIELD
  FIND_IN_SET
  HEX
  INET_ATON
  INET_NTOA
  INSERT
  INSTR
  LCASE
  LEFT
  LENGTH
  LOAD_FILE
  LOCATE
  LOWER
  LPAD
  LTRIM
  MAKE_SET
  MATCH    AGAINST
  MD5
  MID
  OCT
  OCTET_LENGTH
  OLD_PASSWORD
  ORD
  PASSWORD
  POSITION
  QUOTE
  REPEAT
  REPLACE
  REVERSE
  RIGHT
  RPAD
  RTRIM
  SHA
  SHA1
  SOUNDEX
  SPACE
  STRCMP
  SUBSTRING
  SUBSTRING_INDEX
  TRIM
  UCASE
  UNCOMPRESS
  UNCOMPRESSED_LENGTH
  UNHEX
  UPPER
  

  Date and Time Functions

  ADDDATE
  ADDTIME
  CONVERT_TZ
  CURDATE
  CURRENT_DATE
  CURRENT_TIME
  CURRENT_TIMESTAMP
  CURTIME
  DATE
  DATE_ADD
  DATE_FORMAT
  DATE_SUB
  DATEDIFF
  DAY
  DAYNAME
  DAYOFMONTH
  DAYOFWEEK
  DAYOFYEAR
  EXTRACT
  FROM_DAYS
  FROM_UNIXTIME
  GET_FORMAT
  HOUR
  LAST_DAY
  LOCALTIME
  LOCALTIMESTAMP
  MAKEDATE
  MAKETIME
  MICROSECOND
  MINUTE
  MONTH
  MONTHNAME
  NOW
  PERIOD_ADD
  PERIOD_DIFF
  QUARTER
  SEC_TO_TIME
  SECOND
  STR_TO_DATE
  SUBDATE
  SUBTIME
  SYSDATE
  TIME
  TIMEDIFF
  TIMESTAMP
  TIMESTAMPDIFF
  TIMESTAMPADD
  TIME_FORMAT
  TIME_TO_SEC
  TO_DAYS
  UNIX_TIMESTAMP
  UTC_DATE
  UTC_TIME
  UTC_TIMESTAMP
  WEEK
  WEEKDAY
  WEEKOFYEAR
  YEAR
  YEARWEEK
  

  Mathematical and Aggregate Functions

  ABS
  ACOS
  ASIN
  ATAN
  ATAN2
  AVG
  BIT_AND
  BIT_OR
  BIT_XOR
  CEIL
  CEILING
  COS
  COT
  COUNT
  CRC32
  DEGREES
  EXP
  FLOOR
  FORMAT
  GREATEST
  GROUP_CONCAT
  LEAST
  LN
  LOG
  LOG2
  LOG10
  MAX
  MIN
  MOD
  PI
  POW
  POWER
  RADIANS
  RAND
  ROUND
  SIGN
  SIN
  SQRT
  STD
  STDDEV
  SUM
  TAN
  TRUNCATE
  VARIANCE
  

  Flow Control Functions

  Command-Line Utilities
  comp_err
  isamchk
  make_binary_distribution
  msql2mysql
  my_print_defaults
  myisamchk
  myisamlog
  myisampack
  mysqlaccess
  mysqladmin
  mysqlbinlog
  mysqlbug
  mysqlcheck
  mysqldump
  mysqldumpslow
  mysqlhotcopy
  mysqlimport
  mysqlshow
  perror
  

•