Tag Archive for 'sql'

SQL: get the database size

I’m going on writing primitive posts about the fascinating world of relational database management systems. :)

MySQL, size of all databases:

mysql> SELECT table_schema \
       "Database name", \
       sum( data_length + index_length ) / 1024 / 1024 \
       "Data Base Size in MB" FROM \
       information_schema.TABLES GROUP BY table_schema;
+--------------------+----------------------+
| Database name      | Data Base Size in MB |
+--------------------+----------------------+
| fluxbb             |           0.04585648 |
| information_schema |           0.00781250 |
| mysql              |           0.60614872 |
| wordpress          |           2.22493362 |
+--------------------+----------------------+
4 rows in set (0.13 sec)

PostgreSQL, size of all databases:

cdr=> SELECT pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) \
      AS size FROM pg_database;
  datname  |  size   
-----------+---------
 template1 | 6705 kB
 template0 | 6697 kB
 postgres  | 6820 kB
 cdr       | 28 GB

Size of ‘acme_cdr’ table:

cdr=> SELECT pg_size_pretty(pg_total_relation_size('acme_cdr'));
 pg_size_pretty 
----------------
 28 GB

MySQL: myisamchk

Decided to get better knowledge of MySQL with crawling on http://dev.mysql.com/doc/refman/YOUR_VERSION/en/ , for example http://dev.mysql.com/doc/refman/5.7/en/ .

Before MySQL 5.5.5, MyISAM was the default storage engine. (The default was changed to InnoDB in MySQL 5.5.5.)
One of my deployments uses MySQL version prior to 5.5.5. While reading the documentation I found a nice utility, ‘myisamchk‘ and instantly decided to check with it one of *.MYI files. I was a little bit astonished when the utilite showed me some problems with one of files, while everything worked fine. This is the silent mode, only errors (if found) are showed:

root@zenwalk:~# myisamchk -s /var/lib/mysql/webapp/doityourself.MYI
myisamchk: MyISAM file /var/lib/mysql/webapp/doityourself.MYI
myisamchk: warning: 12 clients are using or haven't closed the table properly
MyISAM-table '/var/lib/mysql/webapp/doityourself.MYI' is usable but should be fixed

Normal mode:

root@zenwalk:~# myisamchk /var/lib/mysql/webapp/doityourself.MYI
Checking MyISAM file: /var/lib/mysql/webapp/doityourself.MYI
Data records:     170   Deleted blocks:       2
myisamchk: warning: 12 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check data record references index: 2
- check record links
MyISAM-table '/var/lib/mysql/webapp/doityourself.MYI' is usable but should be fixed

So, let’s recover the .MYI file. Fire!

root@zenwalk:~# myisamchk --recover /var/lib/mysql/webapp/doityourself.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/webapp/doityourself.MYI'
Data records: 170
- Fixing index 1
- Fixing index 2

PostgreSQL notes

1. Edit postgresql.conf line listen_addresses = '10.14.1.21' to your appropriate value.
2. Add to pg_hba.conf a line, which will allow connection to the database from certain IP address/subnet: host johnsdb john 10.14.1.201/32 md5
3. service postgresql reload
4. As root, ‘su - postgres‘ , then ‘psql‘ , then:

4.1. postgres=# CREATE USER john WITH ENCRYPTED PASSWORD 'johnstailislong' ;
4.2. postgres=# CREATE DATABASE johnsdb WITH OWNER john ;
( If the database owner is wrong, change it: postgres=# ALTER DATABASE johnsdb OWNER TO john ; )
5. Then connect from outside to the already created database and create a table inside it:
psql -d johnsdb -U john -h 10.14.1.21
johnsdb=> CREATE TABLE johnstable (type varchar(6),name varchar(128),secret varchar(128),context varchar(128),host varchar(128)) WITH (OIDS=FALSE);

Hints:

Change DB’s owner (as postgresql user):
ALTER DATABASE johnsdb OWNER TO john ;

Change table’s owner (as postgresql user):
ALTER TABLE johnstable OWNER TO john ;

Table permissions: (as postgresql user):
GRANT ALL ON TABLE johnstable TO john ;

Change default values in a column:
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT 'green,red' ;

Rename a column:
ALTER TABLE tablename RENAME COLUMN oldcolname TO newcolname;

Change column type:
ALTER TABLE tablename ALTER COLUMN columnname TYPE varchar(30);

Get last N rows:
SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5;

Get first N rows:
SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5;

Select between X and Y values:
SELECT name,callerid FROM sip_conf WHERE name BETWEEN '1301' and '1310' ORDER BY name;

Show databases:

astertest=> \list
                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+-----------+----------+-------------+-------------+-----------------------
 astertest | astertest | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | 
 postgres  | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | 
 template0 | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |           |          |             |             | postgres=CTc/postgres
 template1 | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |           |          |             |             | postgres=CTc/postgres
(4 rows)

Show tables in the current database:

astertest=> \d
                   List of relations
 Schema |          Name          |   Type   |   Owner   
--------+------------------------+----------+-----------
 public | sip_conf_office        | table    | astertest
 public | sip_conf_office_id_seq | sequence | astertest
(2 rows)

Show table structure:

astertest=> \d sip_conf_office
                                     Table "public.sip_conf_office"
     Column     |          Type          |                          Modifiers                           
----------------+------------------------+--------------------------------------------------------------
 id             | integer                | not null default nextval('sip_conf_office_id_seq'::regclass)
 name           | character varying(80)  | not null default ''::character varying
 accountcode    | character varying(20)  | 
 amaflags       | character varying(7)   | 
 callgroup      | character varying(10)  | 
 callerid       | character varying(80)  | 
 canreinvite    | character varying(3)   | default 'no'::character varying
 context        | character varying(80)  | 
 defaultip      | character varying(15)  | 
 dtmfmode       | character varying(7)   | 
 fromuser       | character varying(80)  | 
 fromdomain     | character varying(80)  | 
 host           | character varying(31)  | not null default ''::character varying
 insecure       | character varying(4)   | 
 language       | character varying(2)   | 
 mailbox        | character varying(50)  | 
 md5secret      | character varying(80)  | 
 nat            | character varying(5)   | not null default 'no'::character varying
...

To insert many lines in SQL-syntax from txt file:

psql -U asterisk -h localhost -d asterisk -f /root/peers.txt

… assuming that peers.txt looks like:

INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0001','Operator 0001 <0001>','0001','operators','5060');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0001d','Operator 0001 <0001>','0001d','operators','5061');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0002','Operator 0002 <0002>','0002','operators','5060');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0002d','Operator 0002 <0002>','0002d','operators','5061');
...
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('9999','Operator 9999 <9999>','9999','operators','5060');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('9999d','Operator 9999 <9999>','9999d','operators','5061');

PostgreSQL Database Backup

For a single database mybase located on a server dbserver.local, from a user john :

pg_dump -h dbserver.local -U john mybase > backup_db_mybase.`date +%Y.%m.%d`.sql

This simple trick may do a great deal for you.

PostgreSQL: удаляем пустые строки

Полностью пустая строка удаляется командой:
DELETE FROM tablename WHERE columnname IS NULL;