Skip to content

09. Database

Jump to bottom
AJ NOURI edited this page Feb 13, 2017 · 1 revision

Some useful tools for inspecting and troubleshooting service-related databases

MySQL/MariaDB

Service control:

service mysql status

Connect to mysql:

  • With the password: mysql -u root -p
  • Without password:
    include the user root and the password in the configuration file /etc/mysql/my.cnf under [mysql] and [mysqldump] sections.

[mysql]
user = root
password = mypasswd
...
[mysqldump]
user = root
password = mypasswd

show databases:

MariaDB [nova]> show databases;

+--------------------+
| Database |
+--------------------+
| cinder |
| glance |
| gnocchi |
| heat |
| information_schema |
| keystone |
| mysql |
| neutron |
| nova |
| nova_api |
| performance_schema |
| test |
+--------------------+
12 rows in set (0.00 sec)

Change to a database:

MariaDB [(none)]> use nova

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [nova]>

show tables:

MariaDB [nova]> show tables;

+--------------------------------------------+
| Tables_in_nova |
+--------------------------------------------+
| agent_builds |
| aggregate_hosts |
| aggregate_metadata |
| aggregates |
| allocations |
| block_device_mapping |
| bw_usage_cache |
| cells |
| certificates |
... | virtual_interfaces |
| volume_id_mappings |
| volume_usage_cache |
+--------------------------------------------+
110 rows in set (0.01 sec)

Inspect table parameters. ex: instance parameters

MariaDB [nova]> describe instances;

+--------------------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------+------+-----+---------+----------------+
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| deleted_at | datetime | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| internal_id | int(11) | YES | | NULL | |
| user_id | varchar(255) | YES | | NULL | |
| project_id | varchar(255) | YES | MUL | NULL | |
| image_ref | varchar(255) | YES | | NULL | |
| kernel_id | varchar(255) | YES | | NULL | |
| ramdisk_id | varchar(255) | YES | | NULL | |
| launch_index | int(11) | YES | | NULL | |
| key_name | varchar(255) | YES | | NULL | |
| key_data | mediumtext | YES | | NULL | |
| power_state | int(11) | YES | | NULL | |
| vm_state | varchar(255) | YES | | NULL | |
| memory_mb | int(11) | YES | | NULL | |
| vcpus | int(11) | YES | | NULL | |
| hostname | varchar(255) | YES | | NULL | |
| host | varchar(255) | YES | MUL | NULL | |
...

list specific parameters:

MariaDB [nova]> select id, hostname, vm_state, key_name, user_id, project_id from instances;

+----+------------------------------+----------+----------+----------------------------------+---------------------- ------------+
| id | hostname | vm_state | key_name | user_id | project_id |
+----+------------------------------+----------+----------+----------------------------------+----------------------------------+
| 1 | u1404-i1 | error | kp2 | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 2 | gns3-server | error | kp1 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 3 | u1404-i1 | deleted | kp2 | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 4 | u1404-i1 | deleted | kp2 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 5 | xenial-i1 | error | kp2 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 6 | xenial1 | error | kp2 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 7 | i2 | deleted | kp2 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 8 | glance | deleted | kp1 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 9 | i11 | deleted | kp1 | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 10 | i1 | deleted | kp3 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 11 | instance1 | error | kp3 | 5ca817e783e04cb698deb35607aac770 | 11d2ce4715634f85829773e7b55ba33a |
| 12 | heat1-heat-i1-dkvlsfal57zt | deleted | kp3 | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 13 | heat1-heat-i1-6tngiugxu3g7 | deleted | kp3 | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 14 | heat2-instance2-olbannfocbk7 | deleted | NULL | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 15 | heat2-instance2-v7dgsjk7m5zi | deleted | NULL | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 16 | heat2-instance1-m6dvdregpzpx | deleted | NULL | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 17 | heat1-heat-i1-rfdydf6kcvop | deleted | kp3 | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 18 | heat2-instance2-wal5ijdvligd | deleted | NULL | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 19 | heat2-instance1-2l3fng4ykvmp | deleted | NULL | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
| 20 | test-server | deleted | NULL | 6994b730621e4ab1a85dcb4a3088309f | 15c30f06d4024134b54e1d3ee284f1ea |
+----+------------------------------+----------+----------+----------------------------------+----------------------------------+
20 rows in set (0.00 sec)

ex: Delete a user directly from the database:

MariaDB [(none)]> use keystone;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed MariaDB [keystone]> describe user; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | id | varchar(64) | NO | PRI | NULL | | | extra | text | YES | | NULL | | | enabled | tinyint(1) | YES | | NULL | | | default_project_id | varchar(64) | YES | | NULL | | | created_at | datetime | YES | | NULL | | | last_active_at | date | YES | | NULL | | +--------------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)

MariaDB [keystone]> select id, extra, default_project_id from user;

+----------------------------------+-----------------------------------+--------------------+ | id | extra | default_project_id | +----------------------------------+-----------------------------------+--------------------+ | 100c1a9fa3994d7497b2549314c83df4 | {"email": "cinder@localhost"} | NULL | | 4a5cb478b0ce4c68b86b3129a47fffc3 | {"email": "glance@localhost"} | NULL | | 5ca817e783e04cb698deb35607aac770 | {"email": "admin@project1.com"} | NULL | | 5f44e742473647229eeee5bad14425c3 | {"email": "heat@localhost"} | NULL | | 5fc114b98269453d8b764eb5505a9402 | {"email": "swift@localhost"} | NULL | | 66202680af654e7494459f371e68e1a7 | {"email": "neutron@localhost"} | NULL | | 66a48707b77246318d4d381b6cacbc00 | {"email": "ceilometer@localhost"} | NULL | | 6994b730621e4ab1a85dcb4a3088309f | {"email": "root@localhost"} | NULL | | 7b5b0726a7274a5693d22105e99285f1 | {"email": "heat_admin@localhost"} | NULL | | 8c47095978c84c47aafff99ba537cbcb | {"email": "aodh@localhost"} | NULL | | 978ec08aba894c8086ca43dc5060a6e4 | {"email": "heat-cfn@localhost"} | NULL | | a4fe9711d8b64c5793346987209c0afc | {"email": "nova@localhost"} | NULL | | c097ee5ea2224f1db4f6cf4851464084 | {"email": "gnocchi@localhost"} | NULL | | ffa279619a4946a0a6147383b0f93c8e | {} | NULL | +----------------------------------+-----------------------------------+--------------------+ 14 rows in set (0.00 sec)

SET FOREIGN_KEY_CHECKS=0; delete from user where id = SET FOREIGN_KEY_CHECKS=1;

Show granted privileges for users:

SELECT user, host FROM mysql.user; SHOW GRANTS FOR 'root'@'localhost'; SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user; SHOW GRANTS FOR 'cinder'@'%'; ... ...

Backup

a individual table from a database:

mysqldump db_name table_name  >  table_name-$(date +%d-%m).sql

individual databases:

An example of script [https://github.com/AJNOURI/COA/blob/master/mysql_backup.sh] (https://github.com/AJNOURI/COA/blob/master/mysql_backup.sh)

#!/bin/bash

for db in $(echo "show databases;" | mysql -h localhost)
do
if [ "$db" != "Database" ] && [ "$db" != "information_schema" ] && [ "$db" != "performance_schema" ] ; then
mysqldump -h localhost $db > /root/$db-$(date +%d-%m).sql
fi
done

All-databases in one file:

mysqldump -u root -p --all-databases > alldbs_backup-$(date +%d-%m).sql
ls *.sql

alldbs_backup-08-01.sql

Restore a database

If the database doesn't exist anymore, create it and restore from the bqckup file
mysql -u root -p[pass] [db_to_restore] < [backupfile.sql]

Clone this wiki locally