The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

mysql -> add/drop/grant/revoke/backup/restore.

Discussion in 'General Discussion' started by billau, Jul 17, 2004.

  1. billau

    billau Well-Known Member

    Joined:
    Dec 24, 2003
    Messages:
    65
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Brisbane, Australia
    mysql -u <username> -p
    Enter password:

    Create database command:
    --------------------------------

    mysql> CREATE DATABASE <database>;

    eg:

    mysql> CREATE DATABASE ACCOUNTS;


    We can now check for the presence of this database by typing:

    mysql> SHOW DATABASES;

    +-------------+
    | Database |
    +-------------+
    | mysql |
    | accounts |
    +-------------+

    USE Database:
    -----------------

    The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or until another USE statement is issued:

    mysql> USE accounts;
    mysql> SELECT COUNT(*) FROM mytable; # selects from db1.mytable
    mysql> USE sales;
    mysql> SELECT COUNT(*) FROM mytable; # selects from db2.mytable

    Making a particular database current by means of the USE statement does not preclude you from accessing tables in other databases. The following example accesses the author table from the db1 database and the editor table from the db2 database:

    mysql> USE accounts;
    mysql> SELECT author_name,editor_name FROM author,sales.editor
    -> WHERE author.editor_id = sales.editor.editor_id;



    Delete / Remove database command:
    --------------------------------------------

    DROP DATABASE <database>

    eg:

    DROP DATABASE accounts;


    Granting Privileges on the new database:
    -----------------------------------------------

    mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost

    or

    mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword';

    mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON vworks.* TO newuser@localhost IDENTIFIED BY 'newpassword';


    mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@192.168.0.2 IDENTIFIED BY 'newpassword';

    Now a user on the machine '192.168.0.2' can connect to the database. To allow a user to connect from anywhere you would use a wildcard '%'

    mysql> GRANT ALL PRIVILEGES ON DatabaseName.* TO Username@localhost IDENTIFIED BY 'newpassword' WITH GRANT OPTION;

    This would allow the user 'newuser' to log into the database and give their friend privileges to SELECT,INSERT,UPDATE or DELETE from the database.


    REVOKING Privileges:
    -------------------------

    For example to REVOKE the privileges assigned to a user called 'user1':

    mysql> REVOKE ALL PRIVILEGES ON DATABASENAME.* FROM user1@localhost;

    Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.

    mysql> REVOKE INSERT,UPDATE,DELETE ON DATABASENAME.* FROM user1@localhost;


    Backing Up DataBase:
    -------------------------

    mysqlhotcopy -u <username> -p <database> /backup/location/


    Which SHOULD copy all the tables (*.frm, *.MYI, *.MYD) into the new directory - the script does require the DBI perl module though. To restore these backup files simply copy them back into your MySQL data directory.


    This is my preferred method of backing up. This outputs the table structure and data in series of SQL commands stored in a text file. The simplified syntax is

    mysqldump -u <username> -p <database> > file.sql

    eg:

    mysqldump -u user1 -p accounts > dump.sql


    Restoring a DataBase from Dump:
    ---------------------------------------

    mysqldump -u <username> -p <database> < file.sql

    eg:

    mysqldump -u user1 -p accounts < dump.sql
     
  2. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16

    For anyone restoring the database, I found the command I had to use was:

    mysql -u dbuser -p database < file.sql

    the mysqldump -u did not work.
     
Loading...

Share This Page