QUESTION:
How to access MySQL Database?
RESOLUTION:
To access the MySQL database in Traverse, log into the machine with the DGE and, using command prompt or terminal, do the following:
For Windows:
- Navigate to <Traverse Home>\bin.
- Run the command: dgedb.init.cmd admin dge
For Linux:
- Navigate to <Traverse Home>/etc.
- Run the command: ./dgedb.init admin dge
This will connect to MySQL and you will enter the interface. From here, you can issue MySQL commands, these are:
- ? (\?) | Synonym for 'help'.
- clear (\c) | Clear the current input statement.
- connect (\r) | Reconnect to the server. Optional arguments are db and host.
- delimiter (\d) | Set statement delimiter.
- ego (\G) | Send command to mysql server, display result vertically.
- exit (\q) | Exit MySQL. Same as quit.
- go (\g) | Send command to mysql server.
- help (\h) | Display help.
- notee (\t) | Don't write into outfile.
- print (\p) | Print current command.
- quit (\q) | Quit mysql.
- rehash (\#) | Rebuild completion hash.
- source (\.) | Execute an SQL script file. Takes a file name as an argument.
- status (\s) | Get status information from the server.
- tee (\T) | Set outfile [to_outfile]. Append everything into given outfile.
- use (\u) | Use another database. Takes database name as argument.
- charset (\C) | Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
- warnings (\W) | Show warnings after every statement.
- nowarning (\w) | Don't show warnings after every statement.
Some common commands that may be helpful for troubleshooting are:
- show table status; | Show the statuses of every table in the database.
- show processlist; | Show all current connections to the databases.
- show status; | Shows the statuses of all the database threads.
- show index from <table name>; | Shows all the primary keys for the table specified in <table name>.
- show tables; | Shows all the tables in the current database.
Alternatively, you can create a script to run these commands to output this data to a text file without having to connect to the MySQL database.
- Create a text file with the commands you want to run. One command per line. Each line must end with a semicolon (;).
- Navigate to <Traverse Home>.
- Run the command:
apps\mysql\bin\mysql --defaults-file=etc\mysql.conf -u root --password= < <Text file> > <Output file>
<Text file> is the file you created with the MySQL commands and the <Output file> is the file and path where you want the output to be written to.
Remember that this is only to be used to see the status of the database. DO NOT RUN ANY COMMANDS THAT WILL MODIFY THE DATABASE UNLESS YOU WERE INSTRUCTED TO BY A TRAVERSE TECHNICIAN.
APPLIES TO:
All versions of Traverse