<\/p>\n
To login (from unix shell) use -h only if needed.<\/strong><\/p>\n #mysql -h hostname -u root -p<\/p>\n Create a database on the sql server.<\/strong><\/p>\n mysql> create database [databasename];<\/p>\n List all databases on the sql server.<\/strong><\/p>\n mysql> show databases;<\/p>\n Switch to a database.<\/strong><\/p>\n mysql> use [db name];<\/p>\n To see all the tables in the db.<\/strong><\/p>\n mysql> show tables;<\/p>\n To see database\u2019s field formats.<\/strong><\/p>\n mysql> describe [table name];<\/p>\n To delete a database.<\/strong><\/p>\n mysql> drop database [database name];<\/p>\n To delete a table.<\/strong><\/p>\n mysql> drop table [table name];<\/p>\n Show all data in a table.<\/strong><\/p>\n mysql> SELECT * FROM [table name];<\/p>\n Creating a new user.<\/strong><\/p>\n # mysql -u root -p<\/p>\n mysql> use mysql;<\/p>\n mysql> INSERT\u00a0 INTO user (Host,User,Password)\u00a0 VALUES(\u2018%\u2019,’username\u2019,PASSWORD(\u2018password\u2019));<\/p>\n mysql> flush privileges;<\/p>\n Change a users password from unix shell.<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n #mysqladmin -u username -h hostname\u00a0\u00a0-p password \u2018new-password\u2019<\/p>\n Change a users password from MySQL prompt.\u00a0<\/strong><\/p>\n <\/p>\n <\/p>\n # mysql -u root -p<\/p>\n mysql> SET PASSWORD FOR\u00a0 \u2018user\u2019@’hostname\u2019 = PASSWORD(\u2018password\u2019);<\/p>\n mysql> flush privileges;<\/p>\n Recover a MySQL root password.<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n # \/etc\/init.d\/mysql stop<\/p>\n # mysqld_safe \u2013skip-grant-tables\u00a0 &<\/p>\n # mysql -u root<\/p>\n mysql> use mysql;<\/p>\n mysql> update user set\u00a0 password=PASSWORD(\u201cnewrootpassword\u201d) where User=\u2019root\u2019;<\/p>\n mysql> flush\u00a0 privileges;<\/p>\n mysql> quit<\/p>\n # \/etc\/init.d\/mysql stop<\/p>\n # \/etc\/init.d\/mysql\u00a0 start<\/p>\n <\/p>\n Update a root password.<\/strong><\/p>\n # mysqladmin -u root -p oldpassword newpassword<\/p>\n Allow the user \u201cuser1\u201d to connect to the server from localhost\u00a0<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n # mysql -u root -p<\/p>\n mysql> use mysql;<\/p>\n mysql> grant\u00a0 usage on *.* to user1@localhost identified by \u2018password\u2019;<\/p>\n mysql> flush\u00a0 privileges;<\/p>\n Give user privilages for a database.<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n mysql> grant all privileges on\u00a0 databasename.* to username@localhost;<\/p>\n mysql> flush privileges;<\/p>\n or<\/p>\n # mysql -u root -p<\/p>\n mysql> use mysql;<\/p>\n mysql> INSERT\u00a0 INTO user\u00a0 (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv)\u00a0 VALUES (\u2018%\u2019,’databasename\u2019,’username\u2019,’Y\u2019,’Y\u2019,’Y\u2019,’Y\u2019,’Y\u2019,’N\u2019);<\/p>\n mysql>\u00a0 flush privileges;.<\/strong><\/p>\n Load a CSV file into a table.<\/p>\n mysql> LOAD DATA INFILE \u2018\/tmp\/filename.csv\u2019 replace INTO\u00a0 TABLE [table name] FIELDS TERMINATED BY \u2018,\u2019 LINES TERMINATED BY \u2018\\n\u2019\u00a0 (field1,field2,field3);<\/p>\n Dump all databases for backup.<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n #mysqldump -u root -ppassword \u2013opt\u00a0 >\/tmp\/alldatabases_backup.sql<\/p>\n Dump one database for backup.<\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n #mysqldump -u username -ppassword \u2013databases\u00a0 databasename >\/tmp\/databasename.sql<\/p>\n Dump a table from a database.<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n mysqldump -c -u username -ppassword\u00a0 databasename tablename > \/tmp\/tablename.sql<\/p>\n Restore database (or database table) from backup.<\/strong><\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n <\/p>\n mysql -u username -ppassword databasename\u00a0 < \/tmp\/databasename.sql<\/p>\n","protected":false},"excerpt":{"rendered":" Basic MySQL Commands <\/p>\n <\/p>\n To login (from unix shell) use -h only if needed.<\/p>\n #mysql -h hostname -u root -p<\/p>\n Create a database on the sql server.<\/p>\n mysql> create database [databasename];<\/p>\n List all databases on the sql server.<\/p>\n mysql> show databases;<\/p>\n Switch to a database.<\/p>\n mysql> use [db name];<\/p>\n To see all the tables in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2182"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2182"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2182\/revisions"}],"predecessor-version":[{"id":2183,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/2182\/revisions\/2183"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2182"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2182"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2182"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}