Mysql common questions and answers

CREATE USER

CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘mypass’;

Create user without password.

CREATE USER ‘jeffrey’@’localhost’;

DROP User

DROP USER ‘jeffrey’@’localhost’;

GRANT

Steps:

CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘mypass’;

GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;

GRANT SELECT ON db2.invoice TO ‘jeffrey’@’localhost’;

GRANT USAGE ON *.* TO ‘jeffrey’@’localhost’ WITH MAX_QUERIES_PER_HOUR 90;

RENAME USER

RENAME USER ‘jeffrey’@’localhost’ TO ‘jeff’@’127.0.0.1’;

SET PASSWORD

SET PASSWORD FOR ‘bob’@’%.loc.gov’ = PASSWORD(‘newpass‘);

Or

UPDATE mysql.user SET Password=PASSWORD(‘newpass‘)

WHERE User=’bob’ AND Host=’%.loc.gov’;

FLUSH PRIVILEGES;

Or

GRANT USAGE ON *.* TO ‘bob’@’%.loc.gov’ IDENTIFIED BY ‘newpass‘;

MYSQLDUMP BACKUP

Syntax:

shell> mysqldump [options] db_name [tbl_name ...]

shell> mysqldump [options] --databases db_name ...

shell> mysqldump [options] --all-databases

examples:

mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

MYSQLDUMP RESTORE

Syntax:

mysql db_name < script.sql > output.tab

Examples:

mysql -u root -p[root_password] [database_name] < dumpfilename.sql

RECOVER/RESET mysql root password.

·Stop mysql service

#/etc/init.d/mysql stop

·Start mysql server w/o password

#mysqld_safe –skip-grant-tables &

·Connect to mysql server using mysql client

# mysql -u root

·Setup new password for mysql root user

mysql> use mysql; mysql> update user set password=PASSWORD(“NEW-ROOT-PASSWORD”) where User=’root’; mysql> flush privileges; mysql> quit

·Stop mysql server.

/etc/init.d/mysql stop

·Start server in normal mode & test

# /etc/init.d/mysql start # mysql -u root -p