MySQL Commands

Selecting a database:

mysql> USE database;

Listing databases:

mysql> SHOW DATABASES;

Listing tables in a db:

mysql> SHOW TABLES;

Describing the format of a table:

mysql> DESCRIBE table;

Creating a database:

mysql> CREATE DATABASE db_name;

Creating a table:

……..SHOW TABLE STATUS FROM tutorial;
show status;

mysql> CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));
Ex: mysql> CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:

mysql> LOAD DATA LOCAL INFILE “infile.txt” INTO TABLE table_name;
(Use \n for NULL)

Inserting one row at a time:

mysql> INSERT INTO table_name VALUES (‘MyName’, ‘MyOwner’, ‘2002-08-31’);
(Use NULL for NULL)

Retrieving information (general):

mysql> SELECT from_columns FROM table WHERE conditions;

All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = “value”;
Multiple critera: SELECT * FROM TABLE WHERE rec1 = “value1” AND rec2 = “value2”;

Reloading a new data set into existing table:

mysql> SET AUTOCOMMIT=1; # used for quick recreation of table
mysql> DELETE FROM pet;
mysql> LOAD DATA LOCAL INFILE “infile.txt” INTO TABLE table;

Fixing all records with a certain value:

mysql> UPDATE table SET column_name = “new_value” WHERE record_name = “value”;

Selecting specific columns:

mysql> SELECT column_name FROM table;

Retrieving unique output records:

mysql> SELECT DISTINCT column_name FROM table;

Sorting:

mysql> SELECT col1, col2 FROM table ORDER BY col2;
Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

mysql> SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];
MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

mysql> SELECT * FROM table WHERE rec LIKE “blah%”;
(% is wildcard – arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like “_____”;
(_ is any single character)

Extended Regular Expression Matching:

mysql> SELECT * FROM table WHERE rec RLIKE “^b$”;
(. for char, […] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use “REGEXP BINARY”

Counting Rows:

mysql> SELECT COUNT(*) FROM table;

Grouping with Counting:

mysql> SELECT owner, COUNT(*) FROM table GROUP BY owner;
(GROUP BY groups together all records for each ‘owner’)

Selecting from multiple tables:

(Example)
mysql> SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;
(You can join a table to itself to compare by using ‘AS’)

Currently selected database:

mysql> SELECT DATABASE();

Maximum value:

mysql> SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:

mysql> CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);
mysql> INSERT INTO table (name) VALUES (“tom”),(“dick”),(“harry”);

Adding a column to an already-created table:

mysql> ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

Removing a column:

mysql> ALTER TABLE tbl DROP COLUMN col;
(Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

# mysql -u user -p < batch_file
(Use -t for nice table layout and -vvv for command echoing.)
Alternatively: mysql> source batch_file;

Backing up a database with mysqldump:

# mysqldump –opt -u username -p database > database_backup.sql
(Use ‘mysqldump –opt –all-databases > all_backup.sql’ to backup everything.)
(More info at MySQL’s docs.)

Deleting all the rows in a table

mysql> TRUNCATE TABLE PET;

————————————————————————————————————————————————–

**MySQL server is running or not under Linux / UNIX operating systems?

You can use mysql startup script or mysqladmin command to find out if it is running on Linux.
Then you can use ps command and telnet command too (it is not reliable but it works.). mysqladmin is a utility for performing administrative operations.
You can also use shell script to monitor MySQL server. You can use mysqladmin as follows:

# mysqladmin -u root -p status
Output:

Enter password:
Uptime: 4 Threads: 1 Questions: 62 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 45 Queries per second avg: 15.500

Under Debian Linux you can type following command to find out if MySQL server is running or not

# /etc/init.d/mysql status Output:

If you are using RedHat of Fedora then you can use following script”

# service mysqld status

OR

# /etc/init.d/mysqld status

————————————————————————————————————————————————————-

** 1. How to change the MySQL root user password?

# mysqladmin -u root -ptmppassword password ‘newpassword’

# mysql -u root -pnewpassword

2. How to check whether MySQL Server is up and running?

# mysqladmin -u root -p ping
Enter password:
mysqld is alive

3. How do I find out what version of MySQL I am running?

# mysqladmin -u root -ptmppassword version

4. What is the current status of MySQL server?

# mysqladmin -u root -ptmppassword status

Uptime: 9267148
Threads: 1 Questions: 231977 Slow queries: 0 Opens: 17067
Flush tables: 1 Open tables: 64 Queries per second avg: 0.25

The status command displays the following information:

Uptime: Uptime of the mysql server in seconds
Threads: Total number of clients connected to the server.
Questions: Total number of queries the server has executed since the startup.
Slow queries: Total number of queries whose execution time waas more than long_query_time variable’s value.
Opens: Total number of tables opened by the server.
Flush tables: How many times the tables were flushed.
Open tables: Total number of open tables in the database.

5. How to view all the MySQL Server status variable and it’s current value?

# mysqladmin -u root -ptmppassword extended-status

6. How to display all MySQL server system variables and the values?

# mysqladmin -u root -ptmppassword variables

7. How to display all the running process/queries in the mysql database?

# mysqladmin -u root -ptmppassword processlist

You can use this command effectively to debug any performance issue and identify the query that is causing problems,
by running the command automatically every 1 second as shown below.

# mysqladmin -u root -ptmppassword -i 1 processlist

8. How to create a MySQL Database?

# mysqladmin -u root -ptmppassword create testdb

9. How to Delete/Drop an existing MySQL database?

# mysqladmin -u root -ptmppassword drop testdb

10. How to reload/refresh the privilege or the grants tables?

# mysqladmin -u root -ptmppassword reload;

Refresh command will flush all the tables and close/open log files.

# mysqladmin -u root -ptmppassword refresh

11. What is the safe method to shutdown the MySQL server?

# mysqladmin -u root -ptmppassword shutdown

# mysql -u root -ptmppassword
ERROR 2002 (HY000): Can’t connect to local MySQL server
through socket ‘/var/lib/mysql/mysql.sock’

Note: You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server.
To start the server, execute “/etc/rc.d/init.d/mysql start”

12. List of all mysqladmin flush commands.

# mysqladmin -u root -ptmppassword flush-hosts
# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads

flush-hosts: Flush all information in the host cache.
flush-privileges: Reload the grant tables (same as reload).
flush-status: Clear status variables.
flush-threads: Flush the thread cache.

13. How to kill a hanging MySQL Client Process?

First identify the hanging MySQL client process using the processlist command.

# mysqladmin -u root -ptmppassword processlist
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 20 | root | localhost | | Sleep | 64 | | |
| 24 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

Now, use the kill command and pass the process_id as shown below. To kill multiple process you can pass comma separated process id’s.

# mysqladmin -u root -ptmppassword kill 20

# mysqladmin -u root -ptmppassword processlist
+—-+——+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———–+—-+———+——+——-+——————+
| 26 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———–+—-+———+——+——-+——————+

14. How to start and stop MySQL replication on a slave server?

# mysqladmin -u root -ptmppassword stop-slave
Slave stopped

# mysqladmin -u root -ptmppassword start-slave
mysqladmin: Error starting slave: The server is not configured as slave;
fix in config file or with CHANGE MASTER TO

15. How to combine multiple mysqladmin commands together?

# mysqladmin -u root -ptmppassword process status version

# mysqladmin -u root -ptmppassword pro stat ver

Use the option -h, to connect to a remote MySQL server and execute the mysqladmin commands as shown below.

# mysqladmin -h 192.168.1.112 -u root -ptmppassword pro stat ver

————————————————————————————————————————————————————–

Running and Shutting down MySQL Server:

First check if your MySQL server is running or not. You can use the following command to check this:

ps -ef | grep mysqld

If your MySql is running, then you will see mysqld process listed out in your result.
If server is not running, then you can start it by using the following command:

root@host# cd /usr/bin
./safe_mysqld &

Now, if you want to shut down an already running MySQL server, then you can do it by using the following command:

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Setting Up a MySQL User Account:

For adding a new user to MySQL, you just need to add a new entry to user table in database mysql.

Below is an example of adding new user guest with SELECT, INSERT and UPDATE privileges with the password guest123;
the SQL query is:

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES (‘localhost’, ‘guest’,
PASSWORD(‘guest123’), ‘Y’, ‘Y’, ‘Y’);

mysql> FLUSH PRIVILEGES;

mysql> SELECT host, user, password FROM user WHERE user = ‘guest’;

When adding a new user, remember to encrypt the new password using PASSWORD() function provided by MySQL.
As you can see in the above example the password mypass is encrypted to 6f8c114b58f2ce9e.

Notice the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables.
If you don’t use it, then you won’t be able to connect to mysql using the new user account at least until the server is rebooted.

You can also specify other privileges to a new user by setting the values of following columns in user table to ‘Y’
when executing the INSERT query or you can update them later using UPDATE query.

Select_priv
Insert_priv
Update_priv
Delete_priv
Create_priv
Drop_priv
Reload_priv
Shutdown_priv
Process_priv
File_priv
Grant_priv
References_priv
Index_priv
Alter_priv

Another way of adding user account is by using GRANT SQL command; following example will add user zara with
password zara123 for a particular database called TUTORIALS.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO ‘zara’@’localhost’
-> IDENTIFIED BY ‘zara123’;

This will also create an entry in mysql database table called user.

—————————————————————————————————————————

NOTE: MySQL does not terminate a command until you give a semi colon (;) at the end of SQL command.

The /etc/my.cnf File Configuration:

Most of the cases, you should not touch this file. By default, it will have the following entries:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Here, you can specify a different directory for error log, otherwise you should not change any entry in this table.

Here is the list of important MySQL commands, which you will use time to time to work with MySQL database:

USE Databasename : This will be used to select a particular database in MySQL workarea.
SHOW DATABASES: Lists the databases that are accessible by the MySQL DBMS.
SHOW TABLES: Shows the tables in the database once a database has been selected with the use command.
SHOW COLUMNS FROM tablename: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults,
and other information for a table.
SHOW INDEX FROM tablename: Presents the details of all indexes on the table, including the PRIMARY KEY.
SHOW TABLE STATUS LIKE tablenameG: Reports details of the MySQL DBMS performance and statistics.

—————————————————————————————————————————————————————–

Capturing Output from MySQL
—————————
If you want to divert the output of a SQL query to a log file, use the \T command along with a filename.

For instance, to write the output of a query to query.txt, do the following:

mysql> \T output.txt
Logging to file ‘query.txt’

———————————————————–

Exiting the mysql Program

To exit the mysql program, use the \q command.

Alternatively, you can type quit or exit to leave the program

—————————————————————

If you want to run a single host command without exiting mysql, use the \! command.

The following example runs the system command pwd on a UNIX/Linux system to determine the path of the current working directory:

mysql> \! pwd
/home/chris/public_html
——————————————————————

–SHOW create database world;

–Find MySQL database size

If you need to find out the size of MySQL databases you use from terminal, the following query will list all the databases with their respective sizes:

mysql> SELECT table_schema “database”, sum(data_length + index_length)/1024/1024 “size in MB” FROM information_schema.TABLES GROUP BY table_schema;

The result you’ll get will be something like:

| database | size in MB |
+——————–+—————-+
| test1 | 13542.68241349 |
| test2 | 1522.23837675 |
| test3 | 26532.27326164 |
| information_schema | 0.00390626 |
+——————–+—————-+
4 rows in set (0.02 sec)

If you have large databases, you can show the result in gigabytes with this query:

mysql> SELECT table_schema “database”, sum(data_length + index_length)/1024/1024/1024 “size in GB” FROM information_schema.TABLES GROUP BY table_schema;

—2nd Method–Locate the MySQL stored data—

Access this article to find out where does MySQL database saved the data.

Windows
Locate the MySQL ibdata1 file, right click on the file and click the properties, see the size? 🙂

Linux
Locate the MySQL ibdata1 file

mkyong@myserver:/var/lib/mysql$ ls -lh
total 1.5G

—————————————————————————————————————————-

CREATE TABLE IF NOT EXISTS newauthor

CREATE TABLE author_copy LIKE author;

CREATE TABLE author_copy AS SELECT * FROM author;

SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=1;

SELECT @@FOREIGN_KEY_CHECKS

# To turn off foreign key constraint globally, do the following:

SET GLOBAL FOREIGN_KEY_CHECKS=0;

and remember to set it back when you are done

SET GLOBAL FOREIGN_KEY_CHECKS=1;

SELECT @@FOREIGN_KEY_CHECKS

WARNING: You should only do this when you are doing single user mode maintenance. As it might resulted in data inconsistency.
For example, it will be very helpful when you are uploading large amount of data using a mysqldump output.

SHOW TABLE STATUS FROM test;

SHOW CREATE TABLE foobar;

SHOW CHARACTER SET;

SHOW COLLATION LIKE ‘latin1%’;

SET NAMES ‘utf8′;

SHOW PLUGINS;

INSERT INTO testpurchase SELECT * FROM purchase;

INSERT INTO testpurchase SELECT * FROM purchase WHERE YEAR(invoice_dt)=’2008’;

Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: