Обновлена 26.11.2014
Ниже будут приведены наиболее часто встречающиеся операции, ошибки а так же их решения. Следите за обновлениями: статья будет дополнятся.
1) Просмотр баз данных
mysql:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | puppet_dash | | test | +--------------------+ 4 rows in set (0.11 sec)
postgresql:
Вариант1
$ psql -l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- 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 (3 rows)
Вариант2
postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- 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 (3 rows)
Вариант3
postgres=# SELECT datname FROM pg_database; datname ----------- template1 template0 postgres (3 rows)
2) Просмотр таблиц
mysql:
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 17 rows in set (0.00 sec)
postgresql:
Вариант1
asterisk=# \dt; List of relations Schema | Name | Type | Owner --------+---------------------+-------+---------- public | bill | table | postgres public | calls | table | postgres (2 rows)
Вариант2
asterisk=# select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE'; table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defi ned_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action ---------------+--------------+---------------------+------------+------------------------------+----------------------+---------------------------+---------- ----------------+------------------------+--------------------+----------+--------------- asterisk | public | bill | BASE TABLE | | | | | | YES | NO | asterisk | public | calls | BASE TABLE | | | | | | YES | NO |
(2 rows)
3) Просмотр структуры таблицы
mysql
mysql> SHOW COLUMNS FROM user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.03 sec)
postgresql:
asterisk=# SELECT column_name FROM information_schema.columns WHERE table_name ='cdr'; column_name ------------- cdr_pkey calldate clid src dst dcontext channel dstchannel lastapp lastdata duration billsec disposition amaflags accountcode uniqueid userfield src1 (18 rows)
4) Загрузить большой дамп.
Если у вас дамп слишком большой и загрузить через phpmyadmin не удаётся (ругается, что размер слишком большой или таймаут истёк) то на помощь приходит консоль mysql.
mysql:
$mysql -u user -p DataBase
Enter password:
mysql>source /path/to/dump.sql
После этого загрузится дамп успешно загрузится.
5)Ошибка “LOCK TABLES”
Если при выполнении дампа базы данных возникает такая ошибка:
mysqldump: Got error: 1044: Access denied for user ‘root’@'localhost’ to database ‘information_schema’ when using LOCK TABLES
то нужно добавить параметр –single-transaction:
/usr/local/bin/mysqldump --single-transaction -uroot -p > dump.sql
6) Просмотр текущих сессий
mysql
Вариант1
mysql> show processlist;
+-----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+------+---------+------+-------+------------------+
| 112 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.04 sec)
Вариант2
# mysqladmin -p processlist
Enter password:
+-----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+----+---------+------+-------+------------------+
| 113 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+
postgresql
Вариант1
postgres=# SELECT * from pg_stat_activity;
datid | datname | procpid | usesysid | usename | application_name | client_addr | client_port | backend_start | xact_start | query_start | waiting | current_query
-------+--------------+---------+----------+--------------+------------------+-------------+-------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------------------
16385 | postfixadmin | 33916 | 16384 | postfixadmin | | 127.0.0.1 | 44414 | 2011-08-05 17:23:27.244198+03 | | 2011-08-05 17:25:30.894716+03 | f | <IDLE>
11874 | postgres | 50281 | 10 | pgsql | psql | | -1 | 2011-08-19 17:21:51.918969+03 | 2011-08-19 17:21:55.192393+03 | 2011-08-19 17:21:55.192393+03 | f | SELECT * from pg_stat_activity;
(2 rows)
Вариант2
postgres=# select now() - query_start as duration,* from pg_stat_activity order by duration desc
Вариант3
# ps axu | grep postgres
pgsql 33916 0.0 3.6 48820 8988 ?? Is Tue08PM 0:00.04 postgres: postfixadmin postfixadmin 127.0.0.1(44414) (postgres)
pgsql 99281 0.0 2.0 47796 4948 ?? Ss Tue07PM 0:11.81 /usr/local/bin/postgres -D /usr/local/pgsql
pgsql 99283 0.0 2.9 47796 7124 ?? Ss Tue07PM 0:32.19 postgres: writer process (postgres)
pgsql 99284 0.0 2.0 47796 4964 ?? Ss Tue07PM 0:38.46 postgres: wal writer process (postgres)
pgsql 99285 0.0 2.2 48820 5548 ?? Ss Tue07PM 0:14.67 postgres: autovacuum launcher process (postgres)
pgsql 99286 0.0 1.8 33596 4408 ?? Ss Tue07PM 0:11.54 postgres: stats collector process (postgres)
7) Просмотр привилегий
mysql
mysql> show grants for 'user'@'host';
postgresql
postgres=# \du
postgres=# SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges WHERE grantee = 'MY_USER'
8 ) Просмотр встроенной справки
– общая
mysql> help
postgres=#\?
– по командам
mysql> help <command>
postgres=#\h <command>
