mysql -u root db_name < file.sql
Dump a Database (with special characters):
mysqldump -Q –add-drop-table db_name > file.sql
Dump a Database, 4.0 compatible:
mysqldump –compatible=mysql40 –add-drop-table –quote-name db_name > file.sql
Dump Multiple Databases:
mysqldump -Q –add-drop-table –databases db_name1 db_name2 > file.sql
Restore Multiple Databases (must be done as root):
mysql -u root < file.sql
Log into MySQL prompt as the user (or root):
mysql -u username -p
Show Databases: (will only show databases the user has access to. Root has all.)
show databases;
Drop a whole database:
drop database user_databasename;
Create a database: *only a root mysql user can use this command
create database user_newdb;
Select a database to work on:
use user_testdb;
Drop a specific table:
drop table table_name;
Show all MySQL Processes:
show full processlist;
for checking error of mysql through shell while it is not starting
binary of mysql –defaults-file=/etc/my.cnf –basedir=/usr –datadir=/var/lib/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-locking
to find binary of mysql
# which mysql==============================
give privilege
mysql>grant all privileges on dbname.* to user@localhost identified by ‘password’;mysql> flush privileges;
==========================
entering mysql
#mysql -u mysqlusername -ppassword
or
mysql -u mysqlusername -p=========================
/tmp consist of mysql link file. if not present following command is used to create one/tmp#ln -s /var/lib/mysql/mysql.sock or
/tmp#ln -s /var/lib/mysql/mysql.sock mysql.sock===============================
Repair a mysql database
shell/telnet/ssh type while mysql is running
mysqlcheck -r -u mysqlusername -p databasename
myisamchk repair functions while mysql is shutdown/stopped in shell/telnet/ssh type :
myisamchk -r -u root -p databasename
