نصب MariaDB
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$ sudo apt install mariadb-server $ sudo mysql_secure_installation $ sudo mysql -uroot Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 160 Server version: 10.3.30-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> UPDATE mysql.user SET plugin = 'mysql_native_password',Password = PASSWORD('root') WHERE User = 'root'; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.004 sec) MariaDB [(none)]> |
دستورات مهم
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
# Load a csv file to table LOAD DATA INFILE '/home/input.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; # rename table RENAME TABLE mytable TO _mytable # add auto increment to a column ALTER TABLE mytable CHANGE id itemid INT(10) AUTO_INCREMENT PRIMARY KEY; # Set auto increment number to 1 ALTER TABLE mytable AUTO_INCREMENT=1 # add index to column ALTER TABLE mytable ADD INDEX inedex_name(field); # add unique index to column ALTER TABLE mytable ADD UNIQUE INDEX index_name(field); # remove index ALTER TABLE mytable DROP INDEX index_name; # insert data from another table INSERT INTO mytable(id, name) SELECT id,name FROM y; # create database with character set CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # change database character set ALTER DATABASE mydatabase CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # see what's going on with table SHOW TABLE STATUS WHERE name LIKE 'x'; # set sql_mode in current mysql session SET sql_mode = ''; # or set it to all mysql session SET GLOBAL sql_mode = ''; # show mysql configurations SHOW VARIABLES: SHOW VARIABLES LIKE '%timeout%'; # show current activities SHOW PROCESSLIST; # or more verbose SHOW FULL PROCESSLIST; # run system commands inside MySQL console, just type command after '\! ' MySQL > \! whoami |
پشتیبان گرفتن از دیتابیس ها
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# to backup schema only use -d or --no-data # backup all databases $ mysqldump -uroot -proot --all-databases > backup.sql # schema only $ mysqldump -uroot -proot -d --all-databases > backup.sql # backup specific database $ mysqldump -uroot -proot my_database > backup.sql # backup specific tables of database $ mysqldump -uroot -proot my_database my_table1 my_table2 > backup.sql # better backup $ mysqldump -uroot --proot --single-transaction --routines --triggers --all-databases > backup.sql # compress backup $ mysqldump -uroot -proot --all-databases | gzip > backup.gz |
چک کردن سلامت دیتابیس ها
|
1 2 |
$ mysqlcheck --verbose -h HOST -u USERNAME -p --auto-repair --optimize --all-databases # you will be prompted to enter password |