MySQL tips

Create database

create database employees;

Grant permisssions to database for user

GRANT ALL ON database.* TO user@localhost IDENTIFIED BY "password";

Set root password for the first time

mysqladmin -u root password NEWPASSWORD

Change user password

UPDATE mysql.user SET Password=PASSWORD('foobar') WHERE User='john' AND Host='localhost';
FLUSH PRIVILEGES;

Show information about table including engine and character set:

show create table table_name;

Change character set for table 

alter table table_name convert to character set utf8 collate utf8_general_ci;

Show character set and collation for database
select your database, use database and then type

show variables like "character_set_database";
show variables like "collation_database";

Restore database from sql file 

mysql -u user -pozttbNUfQx -h hostname database < dump.sql

Make a backup of database with mysqldump

mysqldump -u $mysql_u -p$mysql_p $databases > db.sql

Watch permissions:

show grants for user@host;

Grant reload privilege, it is impossible to grant it for one database, it's global privilege:

GRANT reload ON *.* TO user@localhost;

mysqlhotcopy programm needs the following user rights: SELECT, RELOAD, LOCK TABLES, for example:

grant lock tables, select on database.* to user@localhost;

grant reload on *.* to user@localhost;

Show users:

SELECT Host,User from mysql.user;

Delete user:

drop user user@host;

Revoke grants from user

revoke all on db.table from user@localhost;

Show MySQL error codes description:

perror number_of_code

Error 24If during mysqldump or mysqlhotcopy you recieve error 24, that file not found, you can resolve this issue in two ways. First, you can edit option open_files_limit in mysql.cnf. It's default value is 1024. Another way is to add --single-transaction to mysqlhotcopy.