MySQL Commands Cheatsheet

01/22/2011
Install SQL Buddy
sudo apt-get install apache2 libapache2-mod-php5 php5-mysql
sudo /etc/init.d/apache2 restart

Download SQL Buddy, unzip, and move to /var/www

  • Set the bind address in /etc/mysql/my.cnf to your ip address
  • Open the port in the firewall by running “/sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT” followed by “iptables-save”
  • Grant privileges on remote host as shown below
Login via MySql client:
$ mysql -u XXXXX -pXXXXX db_name
Create a new user:
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
GRANT ALL ON *.* TO 'user1'@'localhost';
CREATE USER 'user1'@'%' IDENTIFIED BY 'pass1';
GRANT ALL ON *.* TO 'user1'@'%';
List users:
select host, user, password, Create_priv from mysql.user;
List databases and tables:
show databases;
show tables;
Rename column:
ALTER TABLE Article change article_id id bigint(20);
Make column non-null:
ALTER TABLE Person MODIFY firstName varchar(255) NOT NULL;
Make column unique:
ALTER TABLE Person ADD UNIQUE INDEX(memberId);
Calculate the database size:
SELECT table_schema "DB Name", sum(data_length + index_length) / 1024 / 1024  "DB size in MB" FROM information_schema.TABLES GROUP BY table_schema;
Be Sociable, Share!