190 lines
6.7 KiB
Markdown
190 lines
6.7 KiB
Markdown
|
||
- MariaDB
|
||
https://www.mariadbtutorial.com/getting-started/connect-to-mariadb/
|
||
|
||
After installing mariaDB use this command to setup database directory
|
||
`mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql`
|
||
do this `rm -rf /var/lib/mysql/*` to undo this initialization process
|
||
|
||
To start mariadbd at boot time you have to copy
|
||
**support-files/mariadb.service** to the right place for your system
|
||
U can do this in systemd init system with
|
||
`systemctl enable --now mariadb.service`
|
||
|
||
|
||
Two all-privilege accounts were created.
|
||
One is **root@localhost**, it has no password, but you need to
|
||
be system 'root' user to connect. Use, for example, `sudo mysql`
|
||
The second is **mysql@localhost**, it has no password either, but
|
||
you need to be the system 'mysql' user to connect.
|
||
After connecting you can set the password, if you would need to be
|
||
able to connect as any of these users with a password and without sudo
|
||
|
||
See the MariaDB Knowledgebase at https://mariadb.com/kb
|
||
|
||
You can start the MariaDB daemon with:
|
||
`cd '/usr' ; /usr/bin/mariadb-safe --datadir='/var/lib/mysql'`
|
||
|
||
You can test the MariaDB daemon with mariadb-test-run.pl
|
||
`cd '/usr/mariadb-test' ; perl mariadb-test-run.pl`
|
||
|
||
Please report any problems at https://mariadb.org/jira
|
||
|
||
The latest information about MariaDB is available at https://mariadb.org/.
|
||
|
||
Consider joining MariaDB's strong and vibrant community:
|
||
https://mariadb.org/get-involved/
|
||
|
||
- More
|
||
To load mysql shell in command prompt:
|
||
|
||
`mysql -u root -p`
|
||
Here root is owner_name. This will prompt for password which is ‘password’.
|
||
or
|
||
`mysql -h localhost -P 3306 -u root -p test`
|
||
|
||
- To connect to remote mysql
|
||
To connect to MariaDB on a specific host, you use the -h option:
|
||
|
||
`mysql -u [username] -p[password] -h [hostname]`
|
||
or to disable ssl check when connecting
|
||
`mysql --skip-ssl -u proj1_wishrocky -P 3306 -p94db734d1935c57ac05eab8987a959c98f074706 -h r77rn.h.filess.io proj1_wishrocky`
|
||
or (for newer versions)
|
||
` mysql --ssl-mode=DISABLED -u proj1_wishrocky -P 3306 -p94db734d1935c57ac05eab8987a959c98f074706 -h r77rn.h.filess.io proj1_wishrocky`
|
||
|
||
|
||
|
||
To get list of database:
|
||
|
||
`show databases;`
|
||
|
||
To create database
|
||
```sql
|
||
drop database if exists database_name;
|
||
create database database_name;
|
||
```
|
||
|
||
To load database
|
||
|
||
`use hospitals;`
|
||
|
||
hospitals here is name of database
|
||
|
||
Once database is loaded, to get list of tables:
|
||
|
||
`show tables;`
|
||
|
||
To get schema of tables:
|
||
|
||
`desc patients;` or `describe patients;`
|
||
|
||
where patients is name of table
|
||
|
||
- To create table
|
||
```sql
|
||
drop table if exists table_name;
|
||
create table table_name(....);
|
||
```
|
||
|
||
- Creating table
|
||
|
||
```sql
|
||
create table project (
|
||
emp_id int not null,
|
||
id int not null,
|
||
name varchar(30),
|
||
location varchar(40),
|
||
primary key(emp_id)
|
||
);
|
||
```
|
||
- Adding Auto increment
|
||
- SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
|
||
|
||
```sql
|
||
-- To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
|
||
ALTER TABLE Persons AUTO_INCREMENT=100;
|
||
|
||
alter table employee
|
||
modify emp_id int not null auto_increment;
|
||
```
|
||
- Change mysql password if forget
|
||
- Stop mysql service
|
||
`sudo systemctl stop mysql`
|
||
- start it in safe mode
|
||
`sudo mysqld_safe --skip-grant-tables --skip-networking`
|
||
- Then in other terminal type
|
||
`mysql -u root`
|
||
- when in mysql shell
|
||
`FLUSH PRIVILEGES;`
|
||
`ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';`
|
||
- close the terminal of the safe mode and start mysql service again
|
||
`sudo systemctl start mysql`
|
||
- running sql script in mysql
|
||
`mysql -u username -p`
|
||
then
|
||
`SOURCE /path/to/your/script.sql;`
|
||
Alternatively, you can execute the script directly from the command line without entering the MySQL shell
|
||
`mysql -u username -p < /path/to/your/script.sql`
|
||
- if else use in mysql
|
||
```sql
|
||
SET @primaryKeyValue = 13; /* Your primary key value here */
|
||
|
||
IF EXISTS (SELECT 1 FROM TableB WHERE foreignKeyColumn = @primaryKeyValue) THEN
|
||
-- Update the record in TableA
|
||
UPDATE TableA
|
||
SET someField = 'newValue' -- Set your field and value accordingly
|
||
WHERE primaryKeyColumn = @primaryKeyValue;
|
||
ELSE
|
||
-- Delete the record in TableA
|
||
DELETE FROM TableA
|
||
WHERE primaryKeyColumn = @primaryKeyValue;
|
||
END IF;
|
||
|
||
```
|
||
- creating and using procedures
|
||
```sql
|
||
-- to replace semicolon with // for procedures
|
||
DELIMITER //
|
||
|
||
CREATE PROCEDURE if not EXISTS UpdateOrDeleteQuestion(IN primaryKeyValue INT)
|
||
BEGIN
|
||
IF EXISTS (SELECT 1 FROM quiz_questions WHERE questionID = primaryKeyValue) THEN
|
||
-- Update the record in questions
|
||
UPDATE questions
|
||
SET isDeleted = TRUE
|
||
WHERE id = primaryKeyValue;
|
||
ELSE
|
||
-- Delete the record in questions
|
||
DELETE FROM questions
|
||
WHERE id = primaryKeyValue;
|
||
END IF;
|
||
END //
|
||
|
||
DELIMITER ;
|
||
|
||
-- Now you can call the procedure with the primary key value
|
||
CALL UpdateOrDeleteQuestion(2);
|
||
-- to delete it
|
||
drop procedure UpdateOrDeleteQuestion;
|
||
```
|
||
|
||
- to run mysql in docker first setup `/var/lib/mysql` by
|
||
`mysql_install_db --user=root --basedir=/usr --datadir=/var/lib/mysql`
|
||
then
|
||
```bash
|
||
mysqld_safe --user=root &
|
||
|
||
# Wait for MySQL to initialize (this is important)
|
||
sleep 5
|
||
|
||
# other scripts to start (like backend services)
|
||
# npm start
|
||
```
|
||
in order to check if mysql is listening on localhost:3306 try this
|
||
`netstat -tuln | grep 3306`
|
||
|
||
|
||
|
||
|id|username|discriminator|avatar|accent_color|banner|theme_colors|pronouns|phone|desktop|mobile|premium|premium_type|bot|bio|system|nsfw_allowed|mfa_enabled|totp_secret|totp_last_ticket|created_at|premium_since|verified|disabled|deleted|email|flags|public_flags|purchased_flags|premium_usage_flags|rights|data|fingerprints|settingsIndex|webauthn_enabled|badge_ids|avatar_decoration_data|display_name_styles|collectibles|primary_guild|
|
||
|--|--------|-------------|------|------------|------|------------|--------|-----|-------|------|-------|------------|---|---|------|------------|-----------|-----------|----------------|----------|-------------|--------|--------|-------|-----|-----|------------|---------------|-------------------|------|----|------------|-------------|----------------|---------|----------------------|-------------------|------------|-------------|
|
||
|1471020127947448357|qwerty|3217|68d3b54bae93fdb947e64cb7df2584c5||c587b1be12f1491329d90359d89dc6bf||||false|false|true|2|false||false|true|false|||2026-02-11 11:18:48.735|2026-02-11 11:18:48.735|true|false|false|qwerty@email.com|0|0|0|0|875069521787904|{"hash":"$2b$12$I4fHr7GfGjbVttWsYFdZXue0uN2DMeSKbSAzu77JdZWS7fS4iJUsC","valid_tokens_since":"2026-02-11T05:48:48.735Z"}||1|false||||||
|