Create database
Grant permisssions to database for user
Set root password for the first time
Change user password
Show information about table including engine and character set:
Change character set for table
Show character set and collation for database
select your database, use database and then type
Restore database from sql file
Make a backup of database with mysqldump
Watch permissions:
Grant reload privilege, it is impossible to grant it for one database, it's global privilege:
mysqlhotcopy programm needs the following user rights: SELECT, RELOAD, LOCK TABLES, for example:
Show users:
Delete user:
Revoke grants from user
Show MySQL error codes description:
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.
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;
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.
