MySQL Frequeently Asked Questions -1
1). What is the minimum hardware requirement for MySQL to be installed ?
The following are the minimum hardware requirement for MySQL installation.
Minimum Disk Space 80GB
Minimum RAM 4G
Server with Multiple CPU processor
2). How do I login in MySQL Database ?
From Mysql DB server:
mysql -u-p
eg : mysql -u root srasdev -pmysql
From Mysql client: (Client hostname should be granted from Mysql DB server)
mysql -u-p -h
eg : mysql -u root srasdev -pmysql -h gssie-4200-4.central
3). How do I startup/shutdown the MySQL database ?
Run the following command in $ prompt.
Startup : $MYSQL_HOME/bin/mysqld_safe --user= --datadir= &
eg: $MYSQL_HOME/bin/mysqld_safe --user=mysql --datadir=/mysql/mysql/data &
Shutdown : $MYSQL_HOME/bin/mysqladmin -u-p shutdown
eg: $MYSQL_HOME/bin/mysqladmin -u root -p shutdown
Note: Mysql(db) root user's password required
4). How do to change user password ?
Run the following command in mysql prompt
UPDATE mysql.user SET Password=PASSWORD('newpass')
WHERE User='bob' AND Host='%.loc.gov';
or
GRANT all ON *.* TO 'user'@'host' IDENTIFIED BY 'password';
or
SET PASSWORD FOR 'user_name'@'host_name' = PASSWORD('newpass');
Note: In some cases above commands may fails, so login as root and delete entry from mysql.user
and grant with new password.
For eg:
delete from mysql.user where host='';
GRANT all ON *.* TO 'user'@'host' IDENTIFIED BY 'password';
5). How do I see MYSQL process ?
Command : SHOW [FULL] PROCESSLIST;
Eg: MYSQL>SHOW PROCESSLIST;
6). How to grant privileges to a user ?
Command: GRANT/ALL ON /*.* TO ' someuser ' @ ' somehost ';
Eg: GRANT ALL ON *.* TO 'srasapp'@'sustain-42.central';
GRANT SELECT ON sras.* TO 'srasapp'@'sustain-42.central';
7). How do I grant permission to a specific host ?
Command: GRANT ALL ON *.* TO 'user_name'@'host_name' IDENTIFIED BY 'password' ;
Eg: GRANT ALL ON *.* TO 'sras_app'@'sustain-75.central' IDENTIFIED BY 'srasapp123';
8). How do I grant permission for all hosts ?
Command : GRANT ALL ON *.* TO ' user_name ' @ ' %' IDENTIFIED BY 'password' ;
Eg: GRANT ALL ON *.* TO 'sras_app'@'%' IDENTIFIED BY 'srasapp123';
9). How do I run SQL command through command line ?
Login in MySQL using the command below and you can run sql queries in mysql prompt.
eg:
mysql -u root -p
mysql > use mysql ;
mysql > select * from user ;
mysql > \. < file name.sql >
10). How do I resolve access denied error ?
Grant access to the user with password
Command: GRANT/ALL ON /*.* TO ' someuser ' @ ' somehost ' IDENTIFIED BY '';
eg: GRANT ALL ON *.* TO 'sras_app'@'sustain-75.central' IDENTIFIED BY 'srasapp321';
The following are the minimum hardware requirement for MySQL installation.
Minimum Disk Space 80GB
Minimum RAM 4G
Server with Multiple CPU processor
2). How do I login in MySQL Database ?
From Mysql DB server:
mysql -u
eg : mysql -u root srasdev -pmysql
From Mysql client: (Client hostname should be granted from Mysql DB server)
mysql -u
eg : mysql -u root srasdev -pmysql -h gssie-4200-4.central
3). How do I startup/shutdown the MySQL database ?
Run the following command in $ prompt.
Startup : $MYSQL_HOME/bin/mysqld_safe --user=
eg: $MYSQL_HOME/bin/mysqld_safe --user=mysql --datadir=/mysql/mysql/data &
Shutdown : $MYSQL_HOME/bin/mysqladmin -u
eg: $MYSQL_HOME/bin/mysqladmin -u root -p shutdown
Note: Mysql(db) root user's password required
4). How do to change user password ?
Run the following command in mysql prompt
UPDATE mysql.user SET Password=PASSWORD('newpass')
WHERE User='bob' AND Host='%.loc.gov';
or
GRANT all ON *.* TO 'user'@'host' IDENTIFIED BY 'password';
or
SET PASSWORD FOR 'user_name'@'host_name' = PASSWORD('newpass');
Note: In some cases above commands may fails, so login as root and delete entry from mysql.user
and grant with new password.
For eg:
delete from mysql.user where host='
GRANT all ON *.* TO 'user'@'host' IDENTIFIED BY 'password';
5). How do I see MYSQL process ?
Command : SHOW [FULL] PROCESSLIST;
Eg: MYSQL>SHOW PROCESSLIST;
6). How to grant privileges to a user ?
Command: GRANT
Eg: GRANT ALL ON *.* TO 'srasapp'@'sustain-42.central';
GRANT SELECT ON sras.* TO 'srasapp'@'sustain-42.central';
7). How do I grant permission to a specific host ?
Command: GRANT ALL ON *.* TO 'user_name'@'host_name' IDENTIFIED BY 'password' ;
Eg: GRANT ALL ON *.* TO 'sras_app'@'sustain-75.central' IDENTIFIED BY 'srasapp123';
8). How do I grant permission for all hosts ?
Command : GRANT ALL ON *.* TO ' user_name ' @ ' %' IDENTIFIED BY 'password' ;
Eg: GRANT ALL ON *.* TO 'sras_app'@'%' IDENTIFIED BY 'srasapp123';
9). How do I run SQL command through command line ?
Login in MySQL using the command below and you can run sql queries in mysql prompt.
eg:
mysql -u root -p
mysql > use mysql ;
mysql > select * from user ;
mysql > \. < file name.sql >
10). How do I resolve access denied error ?
Grant access to the user with password
Command: GRANT
eg: GRANT ALL ON *.* TO 'sras_app'@'sustain-75.central' IDENTIFIED BY 'srasapp321';
This post is of your interest if you are looking for MySql based questions and answers. You will lots of questions and answers based on MySql topic. If you have any doubt or question then you can ask it. Thanks for sharing your effort with us.