Advertisement
Guest User

Untitled

a guest
Oct 4th, 2016
144
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.22 KB | None | 0 0
  1. 1. How to login MySQL Server?
  2.  
  3. To login into MySQL server use -u option for username and -p for a password. when you enter this command it will prompt you to enter a password.
  4.  
  5. $ mysql -u [username] -p [password]
  6.  
  7. 2. How create normal MySQL user?
  8.  
  9. To run this command you should have root privileges on the MySQL server.
  10.  
  11. $ CREATE USER 'username'@'%' IDENTIFIED BY 'password'; OR $ CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
  12.  
  13. ‘%’ use when you have to provide access to the remote server.
  14. ‘localhost’ use when you have local MySQL server.
  15.  
  16. 3. How to view privileges/Access/Rights for the user?
  17.  
  18. This Mysql command will show you the privileges of given user. What type of rights user has on database like insert,update,delete etc.
  19.  
  20. $ show grants for 'username'@'localhost';
  21.  
  22. 4. How to give or grant privileges to the user?
  23.  
  24. With help of below command, you can give access rights to the user for the database. That user can able to or has access to insert, update or delete on only given database.
  25.  
  26. $ GRANT ALL PRIVILEGES ON DbName.* TO 'username'@'localhost' IDENTIFIED BY 'Password';
  27.  
  28. 5. How to flush privileges?
  29.  
  30. After granting privilege to any user. you need to run flush privileges command which can help to refresh the user privileges.
  31.  
  32. $ FLUSH PRIVILEGES;
  33.  
  34. 6. How to create a MySQL database?
  35.  
  36. To create MySQL database use below simple create database command.
  37.  
  38. $ CREATE DATABASE DbName;
  39.  
  40. 7. How to check or list all databases on the server?
  41.  
  42. Below Mysql command used to list down the all available database on the server.
  43.  
  44. $ SHOW DATABASES;
  45.  
  46. 8. How to check running processlist & Full processlist?
  47.  
  48. With show processlist command you will find the null process, time-consuming process, stuck processes etc. on the server.
  49.  
  50. $ SHOW PROCESSLIST; $ SHOW FULL PROCESSLIST;
  51.  
  52. 10. How to drop database?
  53.  
  54. To drop single database use below drop database command.
  55.  
  56. $ DROP DATABASE DbName;
  57.  
  58. 11. How to change or update password?
  59.  
  60. All MySQL user data will be stored in the mysql database. so before changing or updating any user password. You need to first use mysql database then run update command to change the password.
  61.  
  62. $ use mysql; $ update user set password=PASSWORD('your_new_password') where User='username';
  63.  
  64. 12. How to delete a user?
  65.  
  66. If you want any mysql user which is not in use then run below drop user command.
  67.  
  68. $ DROP USER 'username'@'localhost';
  69.  
  70. 13. How to take backup or dump of Mysql Database?
  71.  
  72. This is an important comma.nd for every sysadmin i.e. mysqldump.
  73. with mysqldump command, you will able to take backup of the database in a .sql format
  74.  
  75. $ mysqldump -u username -p DbName > DbName_date.sql
  76.  
  77. 14. How to restore database dump?
  78.  
  79. For restoring a .sql dump file run below command by specifying the database name.
  80.  
  81. $ mysql -u username -p DbName < DbName_date.sql
  82.  
  83. 15. How to take backup in gzip format?
  84.  
  85. Below command help to take direct database backup in compress format .i.e. .gz
  86.  
  87. $ mysqldump -u userName -p DbName | gzip > DbName_date.sql.gz
  88.  
  89. 16. How to restore dump which is in gzip format?
  90.  
  91. Restoration of database dump which is in .gz format use below gunzip command to a uncompressed file and then MySQL command to restore the database.
  92.  
  93. $ gunzip < DbName_date.sql.gz | mysql -u root -p DbName
  94.  
  95. 17. How to Kill Sleep Mysql queries?
  96.  
  97. To kill any time-consuming or null process then use show processlist command. once processlist open then check for null process PID run kill command on that PID.
  98.  
  99. $ show processlist; $ kill PID1 PID2 PID3;
  100.  
  101. 18. How to check Mysql version?
  102.  
  103. Run below command to check mysql server version.
  104.  
  105. $ mysql -u root -p version Or $ mysql -v
  106.  
  107. 19. How to take a dump of the single table from the database?
  108.  
  109. Sometimes we need to take a dump of the single table from the database. just use table name next to the database name and run mysqldump command.
  110.  
  111. $ mysqldump -u username -p DbName tablename > tablename.sql
  112.  
  113. 20. How to Start|Stop|Restart Mysql server?
  114.  
  115. Run below commands to start,stop,restart mysql service.
  116.  
  117. $ /etc/init.d/mysql start $ /etc/init.d/mysql stop $ /etc/init.d/mysql restart
  118.  
  119. We have tried our best to include almost all of ‘mysql‘ commands with their examples in this article which are daily use by SysAdmins, If still, we have missed anything, please do let us know via comments and don’t forget to share with your friends.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement