Difference between revisions of "CLI - Database"

Revision as of 14:11, 27 April 2021

The imRAD use the MariaDB server^[1]. We name it "mysql". In this wiki, you use both terms "MariaDB" and "mysql", but they are the same.

Database Connection Properties

If you run the show system mariadb command, it shows the version of the Database. All Database Connection Properties are securely encrypted. If you want to see properties as plaintext, use the show database property command.
The imRAD consists of three types of Database Connection Properties.

ID	Name	Description
0	MainDB	It is the properties for imRAD main database. If there are two systems for the purpose of the System Failover, one of them is the main system. In other words, an active system is the main system and it has the main database.
2	LocalDB	It is the properties for local database.
3	PeerDB	If you configure the System Failover, these properties are written. If not, no properties for the type 3 exist.

Note that the leading five characters of the password are marked to * for the purpose of security.

LYSH@MyHostName# show database property 0	// Display the database connection properties of the MainDB (1)
  Host:127.0.0.1
  Port:6710
  User-id:lyon
  User-Password:*****y@nu24

Configuring Database Connection Properties

You can configure the host of Database Connection Properties. However, you don't need to configure because If you configure the System Failover, the properties for other types are set automatically. Therefore, If there's no problem, DO NOT configure the Database Connection Properties.

LYSH@MyHostName# configure	
configure# database property create 2 127.0.0.1	// Configuring the host of the LocalDB(2) to 127.0.0.1
configure# exit
LYSH@MyHostName# show database properties 2	// Display the database connection properties of the LocalDB(2)

Configuring Database backup

You can configure to have the system can backup the database. Note that the imRAD is already enabled to backup the database. The default configuration is

db_backup_interval=1
db_backup_hour=3
max_db_backup_file=5

The above configurations mean that the system backup the database at 3 o'clock every 1 day and remain only a recent 5 backup files. In other words, if there are more than 5 backup files after backup, the oldest one is removing.

LYSH@MyHostName# show system backup		// view the current database backup configuration
LYSH@MyHostName# configure
configure# database backup enable		// enable database backup
configure# database backup disable		// disable database backup
configure# database backup hour 5		// Configure the starting hour of database backup
configure# database backup interval 2		// Configure the database backup interval in days
configure# database backup maxfile 3		// Configure the maximum number of database backup files to keep

If you type show files dbbackup, it lists all backup files.

Connect to Database(mysql)

You can access a database directly if necessary by the mysql command. If you want to access a database using "root", you must request an One Time Password (OTP)^[2].

You should bear in mind that accessing the Database should be only for the purpose of maintenance or fixing problems.

mysql

You can verify the status, variables, and so on by using the show mysql {ARG} command.

argument	Description
connection	It shows you information about which protocol is used.^[3]
process	It shows you which threads are running.^[4] ID: The client's process ID. USER: The username associated with the process. HOST: The host the client is connected to. DB: The default database of the process (NULL if no default). COMMAND: The command type. See Thread Command Values. TIME: The amount of time, in seconds, the process has been in its current state. INFO: The statement being executed. PROGRESS: The total progress of the process (0-100%) (see Progress Reporting).
replication	You can see the status of the MySQL replication.
status	It shows you the status of MySQL service.
variable	It shows you the MySQL global variables.

References

[1] ttps://en.wikipedia.org/wiki/MariaDB

[2] ttp://issue.basein.net

[3] ttps://mariadb.com/kb/en/mysql-command-line-client/

[4] ttps://mariadb.com/kb/en/show-processlist/

[1]

[2]

[3]

[4]

@@ Line 1: / Line 1: @@
 __FORCETOC__
-=== Database Connection Properties ===
 The imRAD use the MariaDB server<ref>https://en.wikipedia.org/wiki/MariaDB</ref>. We name it "mysql". In this wiki, you use  both terms "MariaDB" and "mysql", but they are the same.
+=== Database Connection Properties ===
 If you run the <code>show system mariadb</code> command, it shows the version of the Database.
 All Database Connection Properties are securely encrypted. If you want to see properties as plaintext, use the <code>show database property</code> command.<br>
@@ Line 58: / Line 58: @@
 You should bear in mind that accessing the Database should be only for the purpose of maintenance or fixing problems.
+=== mysql ===
+You can verify the status, variables, and so on by using the <code>show mysql {ARG}</code> command.
+{| class="wikitable"
+! style='width:150px' | argument !! Description
+|-
+| connection || It shows you information about which protocol is used.<ref>https://mariadb.com/kb/en/mysql-command-line-client/</ref>
+|-
+| process || It shows you which threads are running.<ref>https://mariadb.com/kb/en/show-processlist/</ref>
+* ID: The client's process ID.
+* USER: The username associated with the process.
+* HOST: The host the client is connected to.
+* DB: The default database of the process (NULL if no default).
+* COMMAND: The command type. See Thread Command Values.
+* TIME:	The amount of time, in seconds, the process has been in its current state.
+* INFO:	The statement being executed.
+* PROGRESS: The total progress of the process (0-100%) (see Progress Reporting).
+|-
+| replication || You can see the status of the MySQL replication.
+|-
+| status || It shows you the status of MySQL service.
+|-
+| variable || It shows you the MySQL global variables.
+|-
+|}
 === References ===