{"id":287,"date":"2009-09-04T18:37:24","date_gmt":"2009-09-05T02:37:24","guid":{"rendered":"http:\/\/jim-zimmerman.com\/blog\/?p=287"},"modified":"2022-07-13T16:09:42","modified_gmt":"2022-07-13T23:09:42","slug":"mysql-commands-2","status":"publish","type":"post","link":"https:\/\/jim-zimmerman.com\/?p=287","title":{"rendered":"MySQL Commands"},"content":{"rendered":"

I intend to add to this as I have time.<\/p>\n

To display the schema of a table:<\/p>\n

mysql> desc tablename<\/em>;<\/p><\/blockquote>\n

To restore a database from backup:<\/p>\n

mysql -u root -p
\nmysql> create database databasename<\/em>;
\nmysql> use databasename<\/em>;
\nmysql> source databasebackupfile<\/em>;
\nNote: I back my databases up to a file name of databasename.sql<\/em>.<\/p><\/blockquote>\n

To update all records in a column of a table:<\/p>\n

mysql> update tablename<\/em> set columnname<\/em>=’value<\/em>‘;<\/p><\/blockquote>\n

To update one record in a column of a table. Note: othercolumnname needs to be unique to the record.:<\/p>\n

mysql> update tablename<\/em> set columnname<\/em>=’value<\/em>‘ where othercolumnname<\/em>=`somevalue<\/em>‘;<\/p><\/blockquote>\n

To create an account:<\/p>\n

GRANT ALL PRIVILEGES ON databasename<\/em>.* TO ‘username<\/em>‘@’hostname<\/em>‘ IDENTIFIED BY ‘password<\/em>‘;
\nGRANT ALL PRIVILEGES ON databasename<\/em>.* TO ‘username<\/em>‘@’localhost’ IDENTIFIED BY ‘password<\/em>‘;
\nFLUSH PRIVILEGES;<\/p><\/blockquote>\n

To make mysqld listen only localhost:<\/p>\n

vi \/etc\/my.cnf
\n…
\nbind-address=127.0.0.1
\n…<\/p><\/blockquote>\n

To count the number of rows in a table:<\/p>\n

SELECT COUNT(*) FROM tablename;<\/p><\/blockquote>\n

To remove formatting and heading from a SELECT statement and delimit using a TAB character:<\/p>\n

mysql mydatabase -N -B -u myuser -p -e “myselect statement;”<\/p><\/blockquote>\n

How to convert timestamp to date from a SELECT statement:<\/p>\n

SELECT column1,DATE_FORMAT(FROM_UNIXTIME(`column2`), ‘%b %e %Y’) AS ‘date_formatted’ FROM tablename;<\/p><\/blockquote>\n

Here is a simpler example, where timestampcol is the name of date column you want to convert:<\/p>\n

SELECT FROM_UNIXTIME(timestampcol) FROM tablename<\/em>;<\/p><\/blockquote>\n

How to get the time an hour ago:<\/p>\n

SELECT TIME(DATE_SUB(NOW(), INTERVAL 1 HOUR));<\/p><\/blockquote>\n

To access a MySql database from a script without having the password stored in the script:
\nAdd the information to the .my.cny file in the user’s home directory that the script will run under.<\/p>\n

Edit or create the file:<\/p>\n

# vi ~\/.my.cny<\/p><\/blockquote>\n

Protect the file:<\/p>\n

# chmod 600 ~\/.my.cny<\/p><\/blockquote>\n

Sample file contents:<\/p>\n

# cat ~\/.my.cny
\n[client]
\nuser=mysqlusername
\npassword=mysqlpassword
\ndatabase=defaultdatabase<\/p><\/blockquote>\n

Then, to use it:<\/p>\n

# mysql < mysqlscript > my.output<\/p><\/blockquote>\n

Display table information:<\/p>\n

show table status from databasename<\/em>;<\/p><\/blockquote>\n

How to backup one table:<\/p>\n

# mysqldump -u mysqlusername<\/em> -h mysqlhostname<\/em> -p databasename<\/em> tablename<\/em> > tablename<\/em>.sql<\/p><\/blockquote>\n

To search or output of a query. This would also apply to a SELECT statement.:
\nSHOW TABLES LIKE ‘%whatever<\/em>%’;<\/p>\n

To limit the number of rows:
\nSELECT * FROM mytable<\/em>LIMIT 100;<\/p>\n

To sort output based on a particular column:
\nSELECT * FROM mytable <\/em>ORDER BY mycolumn <\/em>DESC;<\/p>\n

To get the size of a databse:
\nSELECT table_schema “Database Name”, SUM( data_length + index_length)\/1024\/1024
\n“Database Size (MB)” FROM information_schema.TABLES where table_schema = ‘mydatabase<\/em>‘;<\/p>\n

To get the size of all the tables in a database:
\nSELECT table_name “Table”, table_rows “Number of Rows”, round(((data_length + index_length)\/1024\/1024),2)
\n“Size of Table” FROM information_schema.TABLES WHERE table_schema = “mydatabase<\/em>“;<\/p>\n

To allow an account to export data to a file:
\nGRANT FILE ON *.* TO ‘username<\/em>‘@’hostname<\/em>‘;<\/p>\n","protected":false},"excerpt":{"rendered":"

I intend to add to this as I have time. To display the schema of a table: mysql> desc tablename; To restore a database from backup: mysql -u root -p mysql> create database databasename; mysql> use databasename; mysql> source databasebackupfile; Note: I back my databases up to a file name of databasename.sql. To update all [&hellip<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-287","post","type-post","status-publish","format-standard","hentry","category-documentation"],"share_on_mastodon":{"url":"","error":""},"_links":{"self":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=287"}],"version-history":[{"count":20,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/287\/revisions"}],"predecessor-version":[{"id":1899,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=\/wp\/v2\/posts\/287\/revisions\/1899"}],"wp:attachment":[{"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jim-zimmerman.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}