Sometimes it is useful to lookup and alter information directly on the Asterisk MySQL database.
Queries can be done by GUI or Command line.
GUI query
To simplify things you can install a single file GUI tool. For security reasons the file should be deleted after each use.
cd /var/www/html
wget https://www.adminer.org/static/download/4.2.3/adminer-4.2.3.php
Browse to the file as follows:
http://your_PBX_server_ip/adminer-4.2.3.php
See the Command line query section below for instructions on finding the username/password.
If you do not remove the adminer file after use it would be very easy for a hacker to find and gain access using publicly know mysql passwords.
rm -f /var/www/html/adminer-4.2.3.php
Alternatively make it non-browsable when not in use.
chmod 000 /var/www/html/adminer-4.2.3.php
To make it browsable again.
chmod 644 /var/www/html/adminer-4.2.3.php
There are alternative ways to secure this file while still making it easy to use. They are beyond the scope of this FAQ.
Command line query
If you know your MySQL root password you can login using:
mysql -p asterisk
Alternatively you can look up the asterisk database username/password from /etc/freepbx.conf. Depending on the distribution and version it may be called something else. There is also /etc/amportal.conf which contains a lot more information.
So for example assuming the traditional default username/password of asteriskuser/amp109:
mysql -u asteriskuser -p asterisk
amp109
>
The > character indicates we are logged into the MySQL console ready to run queries.
To view all tables in the asterisk MySQL database.
> show tables;
To view all values in the "sip" table.
> select * from sip;
To view the column headings in the "sip" table.
> show columns from sip;
Note that the second column heading is called "keyword".
To view the nat settings of all the devices in the "sip" table.
> select * from sip where keyword = 'nat';
