Mysql basic operations June 9, 2008

To enter into a mysql database

mysql -u root -p

To view the databases-

>show databases;

To view the tables-

>show tables;

To use a specific database

>use <database>;

To drop a database

>drop <database>;

To reset mysql user password including root password

>Update User set Password=md5(‘test’) where Name=oracle;

>SET PASSWORD FOR root@localhost=PASSWORD(‘thyht’),

To view information about a specific user

>select * from users where Name=’root’;

Resetting mysql root password June 9, 2008

1.stop mysql

2.mysqld_safe –skip-grant-tables &

3.run mysql and follow the steps

>use mysql

>update user set Password=PASSWORD(‘*****’) WHERE User=’root’;

>flush privileges;

4. stop & start mysql

Backup(dump) and Restoring a mysql DB May 21, 2008

• Dumping all databases of a hosts

    $ mysqldump -u root -psecret --all-databases > DATABASE.sql

other options can be used with this

    --skip-opt --force --no-data --no-create-info (see man page of mysqldump for detail)

• Dumping a particular database

    $ mysqldump -u root --databases design > design.sql

• Restoring backup of all databases

    $ mysql -u root -psecret < DATABASE.sql

• Restoring backup of a particular database

    $ mysql -u root -psecret -D design < design.sql

Simple backup scripts

• mysqldump -A –password=xxxxxx | gzip >/mysql_backup/`date +%m%d%y_%T`.sql.gz