The MySQL command-line tool.
# To connect to a database:
mysql <database_name>
# To connect to a database, user will be prompted for a password:
mysql -u <user> --password <database_name>
# To connect to a database on another host:
mysql -h <database_host> <database_name>
# To connect to a database through a Unix socket:
mysql --socket <path/to/socket.sock>
# To execute SQL statements in a script file (batch file):
mysql -e "source <filename.sql>" <database_name>
# To restore a database from a backup created with 'mysqldump' (user will be prompted for a password):
mysql --user <user> --password <database_name> < <path/to/backup.sql>
# To restore all databases from a backup (user will be prompted for a password):
mysql --user <user> --password < <path/to/backup.sql>
# ---
# To connect to a database
$ mysql -h localhost -u root -p
# To backup all databases
$ mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql
# To restore all databases
$ mysql -u root -p < ~/fulldump.sql
# Create database with utf-8 collation and utf-8 charset
$ mysql> create database <database_name> character set utf8 collate utf8_general_ci;
# Create a table with an auto-incrementing primary key (id), w/ utf-8
$ mysql> create table <table_name> (`id` int unsigned auto_increment, primary key (`id`)) default character set utf8 collate utf8_general_ci;
# List all databases on the sql server.
$ mysql> show databases;
# Switch to a database.
$ mysql> use <database_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;
# To add a user and give rights on the given database
$ mysql> GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;
# To create 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.
$ mysqladmin -u username -h hostname.blah.org -p password 'new-password'
# To 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;
# To 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
# To update the root password
$ mysqladmin -u root -p oldpassword newpassword
# To allow user "bob" to connect to server from localhost using 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
$ mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
# Dump one database for backup
$ mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
# Dump a table from a database
$ mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
# Restore database (or database table) from backup
$ mysql -u username -ppassword databasename < /tmp/databasename.sql
# Create tables
$ 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));
$ mysql> create table <table_name> (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');
# references
# - http://www.pantz.org/software/mysql/mysqlcommands.html