Skip to main content

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

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

Popular posts from this blog

Qmail cheat sheet

http://supportlobby.com/blog/category/technical/qmail-technical/

========================
1) To check the mail queue in plesk from command line, you can use the command :

 /var/qmail/bin/qmail-qstat
========================

=========================
2) You can examine the queue with qmail-qread.

/var/qmail/bin/qmail-qread
=========================

=========================
3) From the qread command you get the message id . In the above example , let us assume one of the id is 524514 . Now you can find the file holding the email in/var/qmail/queue with find command.

# find /var/qmail/queue -iname 524514

/var/qmail/queue/remote/22/524514

/var/qmail/queue/mess/22/524514 (mail headers)

/var/qmail/queue/info/22/524514

4) From the mail header you get the IP address

vi /var/qmail/queue/mess/22/524514


Or

Shortcut for the cool guys
---------------------------------

find /var/qmail/queue -iname queu_id | grep mess | xargs less

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

=========================
4) If you wish …

Logical volume vmxxxx_img is used by another device - Error on LVM removal

Hi Folks,

I've faced error while trying to remove an LVM from the server.

The exact LVM error will be "Logical volume vmxxxx_img is used by another device" on executing the lvremove command.

Feel free to remove the following steps to remove the LVM from the server.

Note: Please remember to replace <id> with your VMID in the below section.

----------------------------------------------------------
dmsetup ls dmsetup info -c xen-vm<id>_img dmsetup remove xen-vm<id>_img lvremove -f /dev/xen/vm<id>_img ----------------------------------------------------------

Open VZ cheat Sheet

VZCTL COMMANDS
---------------------

vzctl set 101 --diskspace 1000000:1100000 --save  To set diskspace for container with ID 101

vzctl set 101 --diskinodes 90000:91000 --save     To set inodes for container with ID 101

vzctl set 101 --quotatime 600 --save              To set quotatime for container with ID 101 (The time limit by

                                                       which container is allowed to temporarily exceed  its quota soft limits)

vzctl exec 101 df                                  To execute command df at the server with the CTID 101

vzctl set 101 --quotaugidlimit 100 --save          Set number of user/group id's allowed for internal disk
                                                                     quota,if 0 UID/GUID quota will not be allowed\

 vzctl set 0 --dcachesize 5G --save        Set dcachesize of the hardware node

sysctl fs.fsync-enable=0     or  echo 0 > /proc/sys/fs/fsync-enable        disable fsync calls inside containers .

Add the  …