MySQL Cheat Sheet This article introduces some basic MySql database concepts including definitions, database connections sql, sql to import and create a MySql database. The examples will use the mysql command line (as opposed to phpmyadmin ). MySQL command line cheat sheet The mysql command line utility Many administrative tasks can or should be done on your local machine, even though your database lives on the cloud. You can do some of them through a visual user interface, but that's not covered here.
This document is designed to be a cheat-sheet for MySQL. I don’t plan to cover everything, just most things that a novice MySQL DBA is likely to need often or in a hurry.
If you are going to provide a database service to other machines edit /etc/mysql/my.cnf and set the bind-address parameter to a suitable value. A value of 0.0.0.0 will cause it to accept connections on any of the server’s addresses. I recommend using a private address range (10.0.0.0/8, 192.168.0.0/16, or 172.16.0.0/12) for such database connections and ideally a
back-end VLAN or Ethernet switch that doesn’t carry any public data.
Mysql Cheat Sheet Pdf
For the purpose of this post let’s consider the MySQL server to have a private IP address of 192.168.42.1. So you want the my.cnf file to have bind-address = 192.168.42.1
This saves using mysql client parameters -u parameter for the username, “-p for the password, and specifying the database name on the command line. Note that using the “ -pPASSWORD ” command-line option to the mysql client is insecure on multi-user systems as (in the absence of any security system such as SE Linux) any user can briefly. Tag: Linux Command Line Cheat Sheet. Linux: System and Hardware Cheat Sheet # Display Linux system information. Uname -a # Display kernel release information. Uname -r # Display the linux kernel name. Uname -s # Get the linux kernel version. Window MySQL SQL Injection Cheat Sheet.
To start mysql administration use the command mysql -u root. In Debian the root account has no password by default, on CentOS 5.x starting mysql for the first time gives a message:
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h server password ‘new-password’
That is wrong, for the second mysqladmin command you need a “-p” option (or you can reverse the order of the commands).
There is also the /usr/bin/mysql_secure_installation script that has an interactive dialog for locking down the MySQL database.
If you lose the administration password the recovery process is as follows:
- Stop the mysqld, this may require killing the daemon if the password for the system account used for shutdown access is also lost.
- Start mysqld with the --skip-grant-tables option.
- Use SQL commands such as “UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';” to recover the passwords you need.
- Use the SQL command “FLUSH PRIVILEGES;“
- Restart mysqld in the normal manner.
For an account to automatically login to mysql you need to create a file named ~/.my.cnf with the following contents:
[client]
user=USERNAME
password=PASSWORD
database=DBNAME
Replace USERNAME. PASSWORD, and DBNAME with the appropriate values. They are all optional parameters. This saves using mysql client parameters -u parameter for the username, “-p for the password, and specifying the database name on the command line. Note that using the “-pPASSWORD” command-line option to the mysql client is insecure on multi-user systems as (in the absence of any security system such as SE Linux) any user can briefly see the password via ps.
Note that the presence of the database= option in the config file breaks mysqlshow and mysqldump for MySQL 5.1.51 (and presumably earlier versions too). So it’s often a bad idea to use it.
To grant all access to a new database:
CREATE DATABASE foo_db;
USE foo_db;
GRANT ALL PRIVILEGES ON foo_db.* to 'user'@'10.1.2.3' IDENTIFIED BY 'pass';
Where 10.1.2.3 is the client address and pass is the password. Replace 10.1.2.3 with % if you want to allow access from any client address.
Mysql Command Line Cheat Sheet
Note that if you use “foo_db” instead of “foo_db.*” then you will end up granting access to foo_db.foo_db (a table named foo_db in the foo_db database) which generally is not what you want.
To grant read-only access replace “ALL PRIVILEGES” with “SELECT“.
To show what is granted to the current user run “SHOW GRANTS;” .
To show the privs for a particular user run “SHOW GRANTS FOR ‘user’@’10.1.2.3’;”
To show all entries in the user table (user-name, password, and hostname):
USE mysql;
SELECT Host,User,Password FROM user;
To do the same thing at the command-line:
echo “SELECT Host,User,Password FROM user;” | mysql mysql
To revoke access:
REVOKE ALL PRIVILEGES ON foo_db.* FROM user@10.1.2.3 IDENTIFIED BY ‘pass’;
To test a user’s access connect as the user with a command such as the following:
mysql -u user -h 10.1.2.4 -p foo_db
Then test that the user can create tables with the following mysql commands:
CREATE TABLE test (id INT);
DROP TABLE test;
To list all databases that are active on the selected server run “mysqlshow“, it uses the same methods of determining the username and password as the mysql client program.
To list all tables in a database run “SHOW TABLES;” . For more detail select from INFORMATION_SCHEMA.TABLES or run “SHOW TABLE STATUS;”
For example to see the engine that is used for each table you can use the command echo “SELECT table_schema, table_name, engine FROM INFORMATION_SCHEMA.TABLES;” |mysql.
But INFORMATION_SCHEMA.TABLES is only in Mysql 5 and above, for prior versions you can use mysqldump -d to get the schema, or “SHOW CREATE TABLE table_name;” at the command-line.
Also the mysqldump program can be used to display the tables in a database via “mysqlshow database” or the columns in a table via “mysqlshow database table“.
To list active connections: “SHOW PROCESSLIST;”
The program mysqldump is used to make a SQL dump of the database. EG: “mysqldump mysql” to dump the system tables. The data compresses well (being plain text of a regular format) so piping it through “gzip -9” is a good idea. To backup the system database you could run “mysqldump mysql | gzip -9 > mysql.sql.gz“. To restore simply run “mysql -u user database < file“, in the case of the previous example “zcat mysql.sql.gz | mysql -u root database“.
To dump only selected tables you can run “mysqldump database table1 [table2]“.
The option --skip-extended-insert means that a single INSERT statement will be used for each row. This gives a bigger dump file but allows running diff on multiple dump files.
The option --all-databases or -A dumps all databases.
The option --add-locks causes the tables to be locked on insert and improves performance.
Note that mysqldump blocks other database write operations so don’t pipe it through less or any other process that won’t read all the data in a small amount of time.
mysqldump -d DB_NAME dumps the schema.
The option --single-transaction causes mysqldump to use a transaction for the dump (so that the database can be used in the mean time). This only works with INNODB. To convert a table to INNODB the following command can be used:
ALTER TABLE tablename ENGINE = INNODB;
To create a slave run mysqldump with the --master-data=1.
When a master has it’s binary logs get too big a command such as “PURGE MASTER LOGS BEFORE ‘2008-12-02 22:46:26’;” will purge the old logs. An alternate version is of the form “PURGE MASTER LOGS TO ‘mysql-bin.010’;“. The MySQL documentation describes how to view the slave status to make sure that this doesn’t break replication.
No related posts.
Here are the most commonly used SQL commands and the mostcommonly used options for each.There are many more commands and options than listed here.In other words, the syntaxes as I have listed them are farfrom complete.See the links at the bottom for more complete syntaxes and morecommands.| MySQL Command-Line | ||
| What | How | Example(s) |
| Running MySQL | mysql -uusername -ppassword | mysql -ucusack2RO -pegbdf5s |
| Importing | mysql -uusername -ppassword < filename | mysql -usomeDB -pblah < myNewDB.sql |
| Dumping (Saving) | mysqldump -uusername -ppassworddatabase [tables] > filename | mysqldump -ume -pblah myDB > My.sql mysqldump -ume -pblah myDB table1 table2 > my.sql |
| Common MySQL Column Types | ||
| Purpose | Data Type | Example |
| Integers | int(M) | int(5) |
| Floating-point (real) numbers | float(M,D) | float(12,3) |
| Double-precision floating-point | double(M,D) | double(20,3) |
| Dates and times | timestamp(M) | timestamp(8) (for YYYYMMDD) timestamp(12) (for YYYYMMDDHHMMSS) |
| Fixed-length strings | char(M) | char(10) |
| Variable-length strings | varchar(M) | varchar(20) |
| A large amount of text | blob | blob |
| Values chosen from a list | enum('value1',value2',...) | enum('apples','oranges','bananas') |
| M is maximum to display, and D is precision to the right of the decimal. | ||
| MySQL Mathematical Functions | ||
| What | How | |
| Count rows per group | COUNT(column | *) | |
| Average value of group | AVG(column) | |
| Minumum value of group | MIN(column) | |
| Maximum value of group | MAX(column) | |
| Sum values in a group | SUM(column) | |
| Absolute value | abs(number) | |
| Rounding numbers | round(number) | |
| Largest integer not greater | floor(number) | |
| Smallest integer not smaller | ceiling(number) | |
| Square root | sqrt(number) | |
| nth power | pow(base,exponent) | |
| random number n, 0<n < 1 | rand() | |
| sin (similar cos, etc.) | sin(number) | |
| MySQL String Functions | ||
| What | How | |
| Compare strings | strcmp(string1,string2) | |
| Convert to lower case | lower(string) | |
| Convert to upper case | upper(string) | |
| Left-trim whitespace (similar right) | ltrim(string) | |
| Substring of string | substring(string,index1,index2) | |
| Encrypt password | password(string) | |
| Encode string | encode(string,key) | |
| Decode string | decode(string,key) | |
| Get date | curdate() | |
| Get time | curtime() | |
| Extract day name from date string | dayname(string) | |
| Extract day number from date string | dayofweek(string) | |
| Extract month from date string | monthname(string) | |
| Basic MySQL Commands | ||
| What | How | Example(s) |
| List all databases | SHOW DATABASES; | SHOW DATABASES; |
| Create database | CREATE DATABASE database; | CREATE DATABASE PhoneDB; |
| Use a database | USE database; | USE PhonDB; |
| List tables in the database | SHOW TABLES; | SHOW TABLES; |
| Show the structure of a table | DESCRIBE table; SHOW COLUMNS FROM table; | DESCRIBE Animals; SHOW COLUMNS FROM Animals; |
| Delete a database (Careful!) | DROP DATABASE database; | DROP DATABASE PhoneDB; |
| SQL Commands: Modifying | ||
| What | How | Example(s) |
| Create table | CREATE TABLE table ( column1type [[NOT] NULL] [AUTO_INCREMENT], column2type [[NOT] NULL] [AUTO_INCREMENT], ... other options, PRIMARY KEY (column(s)) ); | CREATE TABLE Students ( LastName varchar(30) NOT NULL, FirstName varchar(30) NOT NULL, StudentID int NOT NULL, Major varchar(20), Dorm varchar(20), PRIMARY KEY (StudentID) ); |
| Insert data | INSERT INTO table VALUES (list of values); INSERT INTO table SET column1=value1, column2=value2, ... columnk=valuek; INSERT INTO table (column1,column2,...) VALUES (value1,value2...); | INSERT INTO Students VALUES ('Smith','John',123456789,'Math','Selleck'); INSERT INTO Students SET FirstName='John', LastName='Smith', StudentID=123456789, Major='Math'; INSERT INTO Students (StudentID,FirstName,LastName) VALUES (123456789,'John','Smith'); |
| Insert/Select | INSERT INTO table (column1,column2,...) SELECT statement; (See below) | INSERT INTO Students (StudentID,FirstName,LastName) SELECT StudentID,FirstName,LastName FROM OtherStudentTable; WHERE LastName like '%son'; |
| Delete data | DELETE FROM table [WHERE condition(s)]; (Omit WHERE to delete all data) | DELETE FROM Students WHERE LastName='Smith'; DELETE FROM Students WHERE LastName like '%Smith%'; AND FirstName='John'; DELETE FROM Students; |
| Updating Data | UPDATE table SET column1=value1, column2=value2, ... columnk=valuek [WHERE condition(s)]; | UPDATE Students SET LastName='Jones' WHERE StudentID=987654321; UPDATE Students SET LastName='Jones', Major='Theatre' WHERE StudentID=987654321 OR (MAJOR='Art' AND FirstName='Pete'); |
| Insert column | ALTER TABLE table ADD COLUMN columntypeoptions; | ALTER TABLE Students ADD COLUMN Hometown varchar(20); |
| Delete column | ALTER TABLE table DROP COLUMN column; | ALTER TABLE Students DROP COLUMN Dorm; |
| Delete table (Careful!) | DROP TABLE [IF EXISTS] table; | DROP TABLE Animals; |
| SQL Commands: Querying | ||
| What | How | Example(s) |
| All columns | SELECT * FROM table; | SELECT * FROM Students; |
| Some columns | SELECT column1,column2,... FROM table; | SELECT LastName, FirstName FROM Students; |
| Some rows/ columns | SELECT column1,column2,... FROM table [WHERE condition(s)]; | SELECT LastName,FirstName FROM Students WHERE StudentID LIKE '%123%'; |
| No Repeats | SELECT [DISTINCT] column(s) FROM table; | SELECT DISTINCT LastName FROM Students; |
| Ordering | SELECT column1,column2,... FROM table [ORDER BY column(s) [DESC]]; | SELECT LastName,FirstName FROM Students ORDER BY LastName, FirstName DESC; |
| Column Aliases | SELECT column1 [AS alias1], column2 [AS alias2], ... FROM table1; | SELECT LastName,FirstName AS First FROM Students; |
| Grouping | SELECT column1,column2,... FROM table [GROUP BY column(s)]; | SELECT LastName,COUNT(*) FROM Students GROUP BY LastName; |
| Group Filtering | SELECT column1,column2,... FROM table [GROUP BY column(s)] [HAVING condition(s)]; | SELECT LastName,COUNT(*) FROM Students GROUP BY LastName HAVING LastName like '%son'; |
| Joins | SELECT column1,column2,... FROM table1,table2,... [WHERE condition(s)]; | SELECT LastName,Points FROM Students,Assignments WHERE AssignmentID=12 AND Students.StudentID=Assignments.StudentID; |
| Table Aliases | SELECT column1,column2,... FROM table1 [alias1], table2 [alias2],... [WHERE condition(s)]; | SELECT LastName,Points FROM Students S,Assignments A WHERE S.StudentID=A.StudentID AND A.AssignmentID=12; |
| Everything | SELECT [DISTINCT] column1 [AS alias1], column2 [AS alias2], ... FROM table1 [alias1], table2 [alias2],... [WHERE condition(s)] [GROUP BY column(s)] [HAVING condition(s)] [ORDER BY column(s) [DESC]]; | SELECT Points, COUNT(*) AS Cnt FROM Students S,Assignments A WHERE S.StudentID=A.StudentID AND A.AssignmentID=12 GROUP BY Points HAVING Points > 10 ORDER BY Cnt, Points DESC; |
Mysql Command Line Cheat Sheet Download
For more details, see the following pages from MySQL.com.