MySQL

SQL

List databases

SHOW databases;

Open db

USE dbname;

or

CONNECT dbname;

List tables

SHOW TABLES;

Show table fields

SHOW COLUMNS FROM City;

DESCRIBE City;

add .sql to MySQL database

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

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

CREATE USER 'owncloud'@'localhost' IDENTIFIED BY 'password';

Grant Access to Database

GRANT ALL ON owncloud.* TO 'ownclouduser'@'localhost' IDENTIFIED BY '4yRQpHDju5WdecKp8WrK' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

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/