Sunday, April 7, 2013

MYSQL

mysql> create database kerala_wp1;
Query OK, 1 row affected (0.00 sec)

mysql> create user wp1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON kerala_wp1.* TO 'wp1'@localhost IDENTIFIED BY 'keralainasia';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Query OK, 0 rows affected (0.00 sec)

 

# [mysql dir]/bin/mysql -h hostname -u root -p

mysql> create database [databasename];
mysql> show databases;

mysql> use [db name];
mysql> show tables;

mysql> describe [table name];

mysql> drop database [database name];
mysql> drop table [table name];

mysql> SELECT * FROM [table name];
mysql> show columns from [table name];
grant usage on *.* to bob@localhost identified by ‘passwd’;
grant all privileges on databasename.* to username@localhost;
flush privileges;

SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘passwordhere’);

+ Check, Repair and Optimize All tables in All Databases when you’re running a MySQL server on Linux.
# mysqlcheck –auto-repair –check –optimize –all-databases

OR
# mysqlcheck –all-databases -r #repair databases
# mysqlcheck –all-databases -a #analyze databases
# mysqlcheck –all-databases -o #optimize databases

=> Check, Repair and Optimize Single Database Tables.
# mysqlcheck –auto-repair –check –optimize CpanelUsername_Databasename
# mysqlcheck -ro CpanelUsername_Databasename

=> To repair One Table in database:
# mysqlcheck -ro CpanelUsername_Databasename table_name
Shows you if any need repair:
# myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:
# myisamchk –safe-recover /var/lib/mysql/*/*.MYI

and, if neither “safe-recover” or “recover” option works:
# myisamchk –recover /var/lib/mysql/*/*.MYI

Then use the ‘force’ flag:
# myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI
mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user]…

REVOKE ALL PRIVILEGES OPTION FROM 'wp1'@'localhost';

REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Dump a table from a database.
[mysql dir] mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir] mysql -u username -ppassword databasename < /tmp/databasename.sql
mysql> create database kerala_wp1;
Query OK, 1 row affected (0.00 sec)

mysql> create user wp1;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON kerala_wp1.* TO 'wp1'@localhost IDENTIFIED BY 'keralainasia';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON kerala_wp1.* FROM 'wp1'@localhost;
Query OK, 0 rows affected (0.00 sec)

No comments:

Post a Comment