MySQL

From WeWeWeb Wiki
Jump to: navigation, search

MySQL HomePage : http://http://www.mysql.com/

su - mysql // or chown -R mysql.mysql /var/lib/mysql mysql_install_db

mysql -u root mysql


To set root password

UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; FLUSH PRIVILEGES;

or

mysqladmin -u root password new_password


To create a databased

create database <database_name> character set = 'utf8';


To add and grant user privileges

mysql> GRANT ALL PRIVILEGES ON *.* TO monty@localhost
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO monty@'%'
    ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
mysql> GRANT RELOAD,PROCESS ON *.* TO admin@localhost;
mysql> GRANT USAGE ON *.* TO dummy@localhost;

or

mysql> INSERT INTO user VALUES('localhost','monty',PASSWORD('some_pass'),
    ->          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user VALUES('%','monty',PASSWORD('some_pass'),
    ->          'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user SET Host='localhost',User='admin',
    ->           Reload_priv='Y', Process_priv='Y';
mysql> INSERT INTO user (Host,User,Password)
    ->                  VALUES('localhost','dummy',);
mysql> FLUSH PRIVILEGES;

SELECT host,db,user FROM db;

mysqladmin -u root shutdown


To start mysql in windows:-

cd \mysql\bin
mysqld –-console.

To backup (dump) a database

 mysqldump -u USER -pPASSWORD DATABASE > filename.sql

To restore a database

 mysql -u USER -p DBNAME < dump.sql

See http://dev.mysql.com/doc/refman/5.0/en/backup.html


Goto Linux