Working with mysql-(1)

[Albert.Nawaro]

Create mysql database

To create database,

create database DB_NAME

For example

mysql> create database video_vshare; 
Query OK, 1 row affected (0.01 sec) 

mysql>

Creating mysql user account

To create a mysql user with all privileges on all databases.

GRANT ALL ON *.* TO 'USER_NAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE'; 
GRANT ALL ON *.* TO 'USER_NAME'@'%' IDENTIFIED BY 'PASSWORD_HERE';  
   Create a user with permission to only one database.

GRANT ALL ON DB_NAME.* to 'USER_NAME'@'localhost' IDENTIFIED BY 'PASSWORD_HERE';

Create MySQL User who can only Manage His Own DB

grant create on *.* to 'user'@'localhost' identified by 'pass'; 
FLUSH PRIVILEGES

Repair mysql table

To repair table, you use to use the database.

mysql 
use freebb

Now to check a table.

mysql> check table mithridates_sessions; 
+-----------------------------+-------+----------+----------------------------------------------------------+ 
| Table                       | Op    | Msg_type | Msg_text                                                 | 
+-----------------------------+-------+----------+----------------------------------------------------------+ 
| mithridates_sessions | check | warning  | Table is marked as crashed                               | 
| mithridates_sessions | check | warning  | 6 clients are using or havent closed the table properly | 
| mithridates_sessions | check | error    | Record at pos: 9519 is not remove-marked                 | 
| mithridates_sessions | check | error    | record delete-link-chain corrupted                       | 
| mithridates_sessions | check | error    | Corrupt                                                  | 
+-----------------------------+-------+----------+----------------------------------------------------------+ 
5 rows in set (0.05 sec)  
   
We found the table Corrupt, so repair it with repair table command.
     PHP Code:      mysql> repair table mithridates_sessions; 
+-----------------------------+--------+----------+--------------------------------------+ 
| Table                       | Op     | Msg_type | Msg_text                             | 
+-----------------------------+--------+----------+--------------------------------------+ 
| mithridates_sessions | repair | warning  | Number of rows changed from 94 to 95 | 
| mithridates_sessions | repair | status   | OK                                   | 
+-----------------------------+--------+----------+--------------------------------------+ 
2 rows in set (0.17 sec) 

mysql>

 

[cvrle77]

Awesome, please, continue!

 

[jibinthomas]

you can use the following useful mysql commands

Command to dump the mysql database
mysqldump database_name > database_name.sql

To restore a DB from backup
mysql database_name < database_name.sql

To delete a mysql database
mysqladmin drop database_name