MySQL
October 14, 2014 — 16:37

Author: silver  Category: linux windows  Comments: 0

Show databases:

mysql -u root -p -e 'show databases;'

Create new database:

CREATE database username
GRANT ALL ON username.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Create admin user:

$ mysql --user=root -p mysql
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'KNOWNPW';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

Password reset root user:

Linux:

$ /etc/init.d/mysql stop
$ mysqld_safe --skip-grant-tables 
$ mysql --user=root mysql
SELECT * FROM user;
update user set Password=PASSWORD('MyNewPass') where user='root';
flush privileges;
exit
$ /etc/init.d/mysql start

Windows:

C:\mysql\bin\mysqld-nt --init-file=C:\\mysql-init.txt --console
UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Various:

(run mysql interactively)

Set password hash:

set password for 'root'@'localhost' = '*H4SHH4SHH4SHH4SHH4SHH4SHH4SHH4SHH4SHH4SH';

Show user:

SELECT user, host FROM mysql.user WHERE user='root'

List databases/tables:

SHOW databases;
SHOW tables;

Import from cli:

source path/to/file.sql;

INSERT IF NOT EXISTS:

INSERT IGNORE INTO

Dump binary data

use option --hex-blob