Difference between revisions of "MySQL"
Jump to navigation
Jump to search
Rafahsolis (talk | contribs) |
Rafahsolis (talk | contribs) Tag: visualeditor |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | == SQL == | + | ==SQL== |
| − | === List databases === | + | ===List databases=== |
SHOW databases; | SHOW databases; | ||
| − | === Open db === | + | ===Open db=== |
USE dbname; | USE dbname; | ||
or | or | ||
CONNECT dbname; | CONNECT dbname; | ||
| − | === List tables === | + | ===List tables=== |
SHOW TABLES; | SHOW TABLES; | ||
| − | === Show table fields === | + | ===Show table fields=== |
SHOW COLUMNS FROM City; | SHOW COLUMNS FROM City; | ||
DESCRIBE City; | DESCRIBE City; | ||
| − | === add .sql to MySQL database === | + | ===add .sql to MySQL database=== |
TODO: REVISAR | TODO: REVISAR | ||
backup: | backup: | ||
| − | mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql | + | mysqldump -u root -p[root_password] [database_name] [table1 table2 ...]> dumpfilename.sql |
backup .sql.gz | backup .sql.gz | ||
mysql -uroot -ppassword mydb > myfile.sql.gz | mysql -uroot -ppassword mydb > myfile.sql.gz | ||
| Line 29: | Line 29: | ||
zcat /path/to/file.sql.gz | mysql -u 'root' -p 'password' your_database | zcat /path/to/file.sql.gz | mysql -u 'root' -p 'password' your_database | ||
| − | === Save SELECT to file === | + | ===Save SELECT to file=== |
SELECT hostname FROM VTS_sistema WHERE INTO LOCAL OUTFILE '/tmp/hostnames_VTS_sistema' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; | SELECT hostname FROM VTS_sistema WHERE INTO LOCAL OUTFILE '/tmp/hostnames_VTS_sistema' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'; | ||
mysql -h20.1.40.66 -u xe50582 -p -D workflow -e "SELECT hostname FROM controles_sistemas WHERE cod_plataforma='UNX'" > hostnames_unix_workflow.txt | mysql -h20.1.40.66 -u xe50582 -p -D workflow -e "SELECT hostname FROM controles_sistemas WHERE cod_plataforma='UNX'" > hostnames_unix_workflow.txt | ||
| + | |||
| + | ===Create User=== | ||
| + | <syntaxhighlight lang="sql"> | ||
| + | CREATE USER 'owncloud'@'localhost' IDENTIFIED BY 'password'; | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | ===Grant Access to Database=== | ||
| + | <syntaxhighlight lang="sql"> | ||
| + | GRANT ALL ON owncloud.* TO 'ownclouduser'@'localhost' IDENTIFIED BY '4yRQpHDju5WdecKp8WrK' WITH GRANT OPTION; | ||
| + | FLUSH PRIVILEGES; | ||
| + | EXIT; | ||
| + | </syntaxhighlight> | ||
| + | |||
| + | ===Change DB password=== | ||
| + | SET PASSWORD [FOR user] = password_option | ||
| + | |||
| + | SET PASSWORD FOR 'jeffrey'@'localhost' = password_option; | ||
| + | TODO: https://www.zeppelinux.es/corregir-error-1698-28000-access-denied-for-user-rootlocalhost-en-mariadb-mysql/ | ||
Latest revision as of 10:04, 24 February 2020
SQL[edit]
List databases[edit]
SHOW databases;
Open db[edit]
USE dbname;
or
CONNECT dbname;
List tables[edit]
SHOW TABLES;
Show table fields[edit]
SHOW COLUMNS FROM City;
DESCRIBE City;
add .sql to MySQL database[edit]
TODO: REVISAR backup:
mysqldump -u root -p[root_password] [database_name] [table1 table2 ...]> dumpfilename.sql
backup .sql.gz
mysql -uroot -ppassword mydb > myfile.sql.gz
restore:
mysql -u root -p[root_password] [database_name] < dumpfilename.sql
restore .sql.gz
zcat /path/to/file.sql.gz | mysql -u 'root' -p 'password' your_database
Save SELECT to file[edit]
SELECT hostname FROM VTS_sistema WHERE INTO LOCAL OUTFILE '/tmp/hostnames_VTS_sistema' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql -h20.1.40.66 -u xe50582 -p -D workflow -e "SELECT hostname FROM controles_sistemas WHERE cod_plataforma='UNX'" > hostnames_unix_workflow.txt
Create User[edit]
CREATE USER 'owncloud'@'localhost' IDENTIFIED BY 'password';
Grant Access to Database[edit]
GRANT ALL ON owncloud.* TO 'ownclouduser'@'localhost' IDENTIFIED BY '4yRQpHDju5WdecKp8WrK' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
Change DB password[edit]
SET PASSWORD [FOR user] = password_option
SET PASSWORD FOR 'jeffrey'@'localhost' = password_option;