Summary of MySQL commands

To start, stop, restart mysql server:

$ /etc/init.d/mysqld start
$ /etc/init.d/mysqld stop
$ /etc/init.d/mysqld restart
$ /etc/init.d/mysql start
$ /etc/init.d/mysql stop
$ /etc/init.d/mysql restart

Tip: Redhat Linux also supports service command, which can be use to start, restart, stop any service:

# service mysqld start
# service mysqld stop
# service mysqld restart

Setup a password for root user & Login:

# mysqladmin -u root password NEWPASSWORD
# mysql -u root -p
mysql> use mysql;
mysql> UPDATE user SET Password=PASSWORD('your_new_password') where USER='The_user_@_localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit 

Getting the LOST password of root user:

# /etc/init.d/mysqld stop
# /etc/init.d/mysqld --skip-grant-tables
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('123456') WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit;

 Export and Import Dump:

# mysqldump -u "your_username" -p --lock-tables "your_database" > your_database_dump.sql
# mysqldump -u "your_username" -p --lock-tables --databases DB1 [DB2 DB3...] > your_database_dump.sql
# mysqldump -u "your_username" -p --lock-tables --all-databases > your_database_dump.sql
# mysql -u "your username" -p"your_database" < database_dump.sql
# mysql -u "your_username" -p < your_database_dump.sql

Privilege:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'192.168.3.10' identified by 'yourpassword' ;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, CREATE TEMPORARY TABLES ON db_name.* TO 'user_name'@'%'
mysql> Flush Privileges;
mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';

 ERROR 1153 (08S01) at line xxx: Got a packet bigger than 'max_allowed_packet' bytes:

# vi /etc/my.cnf

[mysqld]
max_allowed_packet=100M

#/etc/init.d/mysqld restart
mysql> show variables like 'max_a%'