Archive for the ‘mysql’ Category

Creating user, db and granting permission on mysql

November 4, 2008

To create a user-
mysqladmin –user=root -p create cacti
or
mysql -u root -p
CREATE USER ‘user1’@’localhost’ IDENTIFIED BY ‘pass1’;
GRANT ALL ON *.* TO ‘user1’@’localhost’;
flush privileges;

To create database-
create database <name>;

To set required permission

mysql>grant all on blog.* to blogadm@localhost;
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT USAGE ON blog.* to blogadm@localhost IDENTIFIED BY ‘passwd’;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON blog.* to blogadm@”%”;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT USAGE ON blog.* to blogadm@”%” IDENTIFIED BY ‘passwd’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

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