Using CheatSheets To Apply Best Practices

CheatSheet: MySql & PSQL

CheatSheet: MySql & PSQL


PRs Welcome

File me Issues or star this repo.

See more CheatSheets from Denny: #denny-cheatsheets

Related post: CheatSheet: Linux Find, CheatSheet: shell

1.1 Mysql packages

Name Comment
Install mysql server apt-get install mysql-server
Install mysql client apt-get install mysql-client libmysqlclient-dev, yum install mysql-devel
Install python mysql library easy_install mysql-python

1.2 Mysql basic

Name Comment
mysql connect mysql -u$username -p$password -P$port -h$mysql_host $db_name
database encoding set names utf8;
List databases show databases;
List tables for current db show tables;
Check table definition describe $table_name;
Run sql in non-interactive way mysql -uUSER -pPASSWORD databasename -e “select * from t limit 10”

1.3 DB Management

Name Comment
Import db mysql -uUSER -pPASSWORD database_name < backup.sql
export db mysqldump -uUSER -pPASSWORD DATABASE > backup.sql
export db without schema mysqldump -uUSER -pPASSWORD DATABASE --no-data=true --add-drop-table=false > backup.sql
Grant access GRANT SUPER ON `DBNAME`.`user` TO 'DBUSER'@'%'
Add column ALTER TABLE expenses ADD COLUMN createtime timestamp not null default now();
Delete Column ALTER TABLE expenses DROP COLUMN createtime;
Delete index DROP INDEX index_name ON table_name;
Create index create index id_index on table_name(col_name) using btree;
Reset password UPDATE mysql.user SET Password=PASSWORD(‘MyNewPass’) WHERE User=’root’; FLUSH PRIVILEGES;

1.4 Sql Statment

Name Comment
Select with regexp in where clause select * from expenses where date regexp '2013-0[4-5]' limit 2;
Select with like in where clause select * from expenses where date like '2013-0%' limit 2;
Select unix timestamp select unix_timestamp(createtime) from expenses limit 1;
Offset limit select * from student limit 4 offset 9
Use replace function UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def');
Use if function select Db, IF(IFNULL(User, “”)=””, DB, User) from db;

1.5 psql – postgresql

Summary Name
Connect postgresql psql -U postgres
Grant access GRANT ALL PRIVILEGES ON DATABASE launchpad_empty to tom;
Postgres configuration /var/lib/pgsql/data/pg_hba.conf
Stop and start command service postgresql status
Promote to super admin ALTER USER myuser WITH SUPERUSER;
List datbases \l
Change database \connect launchpad_dev;
Show tables \ds
Copy databse createdb -O root -T launchpad_dev launchpad_ustack_cn
Drop database drop database launchpad_ustack_cn;
Connect db psql -h myhost -d mydb -U myuser -W
Create user CREATE USER tom WITH PASSWORD 'myPassword';
Create db CREATE DATABASE jerry;

1.6 More Resources

License: Code is licenmysql under MIT License.

Leave a Reply

Your email address will not be published. Required fields are marked *