دستوراتی که در MySQL استفاده میکنم

پشتیبان گرفتن از دیتابیس ها

# 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

# 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

چک کردن سلامت دیتابیس ها

$ mysqlcheck --verbose -h HOST -u USERNAME -p --auto-repair --optimize --all-databases
# you will be prompted to enter password

1 2	$ mysqlcheck --verbose -h HOST -u USERNAME -p --auto-repair --optimize --all-databases # you will be prompted to enter password

دستورات مهم

# 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

# 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

دستوراتی که در MySQL استفاده میکنم

نوشته های مشابه

دیدگاهتان را بنویسید لغو پاسخ