Mysql Commands Cheat sheet for Linux plesk users

Mysql Commands
----------------

mysql -uadmin -p`cat /etc/psa/.psa.shadow` DATABASE_NAME == Access MySQL databases in Plesk

mysql -uadmin -p`cat /etc/psa/.psa.shadow` === root password for MySQL ==> Root is admin user in plesk

dbclient --direct-sql --sql="select * from misc" == windows plesk + mysql == dbclient utility for checking mysql db in plesk

Reset the Plesk MySQL Password
=====================================

1. /etc/init.d/mysqld stop

2. /usr/bin/ ?ls /usr/bin |grep mysql |grep safe? ?skip-grant-tables & (If the above command returns ?No such file or directory,?)

/usr/bin/safe_mysqld ?skip-grant-tables & (If the above command returns ?No such file or directory,?)

/usr/bin/mysqld_safe ?skip-grant-tables &

3. /etc/init.d/mysqld start

4. mysql;

5. use mysql;

6. update user set Password=password(?PLESK_PASSWORD?) where User=?admin?;

7. flush privileges;

8. quit

9. /etc/init.d/mysqld restart

========================================

mysqldump -u admin -p `cat /etc/password/.psa.shadow` database_name > database_name.sql Back Up MySQL Databases From The Command Line

mysqldump -u admin -p `cat /etc/password/.psa.shadow` --databases database_one database_two > two_databases.sql Multiple databases can be backed up at the same time

mysqldump -u admin -p `cat /etc/password/.psa.shadow` --all-databases > all_databases.sql Back up all of the databases on a server:

mysql -u admin -p `cat /etc/password/.psa.shadow` database_name < database_name.sql Restore a single database.

mysql -u admin -p `cat /etc/password/.psa.shadow` database_name < all_databases.sql Restore a single database from dump of all the databases


check and repair MySQL database tables
=====================================

mysql -u admin -p `cat /etc/password/.psa.shadow` check table tablename;

mysql -u admin -p `cat /etc/password/.psa.shadow` repair table tablename;

GRANT privileges in MySQL
==========================

GRANT SELECT ON database.* TO user@'localhost'; If the database user already exists.

GRANT SELECT ON database.* TO user@'localhost' IDENTIFIED BY 'password'; OR // GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password'; // to create a brand new user

FLUSH PRIVILEGES; AFTER APPLYING THE ABOVE COMMANDS FLUSH THE PRIVILEGES;


Configuring Linux Plesk to drop external MySQL connections
===========================================================

# this command makes sure everything on localhost works
iptables -A INPUT -p all -s localhost -d localhost -j ACCEPT
# block all external connection attempts to MySQL
iptables -A INPUT -p tcp ?destination-port 3306 -j REJECT
/etc/init.d/iptables save
/etc/init.d/iptables restart

#check the new definitions
iptables ?list

Chain INPUT (policy ACCEPT)
target prot opt source destination
ACCEPT all ? localhost.localdomain localhost.localdomain
REJECT tcp ? anywhere anywhere tcp dpt:mysql reject-with icmp-port-unreachable

Chain FORWARD (policy ACCEPT)
target prot opt source destination

Chain OUTPUT (policy ACCEPT)
target prot opt source destination

================================================