Slide

  • LINUX

    LINUX:In 1969 AT&T made a decision to withdraw Multics and go with GECOS (General Electric Comprehensive Operating Supervisor / System), with AT & T in Bells Lab when Multics was withdrawn some of the programmers named Ken Thompson and Dennis Ritchie decided to rewrite operating system in order to support low cost computer..To Know More

    CLICK HERE

  • MICROSOFT

    MICROSOFT:Microsoft was established to develop and sell BASIC interpreters for the Altair 8800. It rose to dominate the home computer operating system market with MS-DOS in the mid-1980s,followed by the Microsoft Windows line of operating systems. To Know More

    CLICK HERE

  • CISCO

    CISCO:During the early 1980s, there was a married couple namely Len and Sandy Bosack who used to work in two different departments of computer located in Stanford University. This couple was facing problem in making their computers communicate with each other To Know More

    CLICK HERE

showinfo=1

Configuring MYSQL


Configuring MYSQL


Requirements
Explanation of requirements.
  1. MySQL and MySQL-server installed on a Centos 5.x system
  2. DBA access rights to MySQL on a Centos 5.x system
Doing the Work
Basic description of what will be done and what is expected.
  1. Database file locations:
In Centos 5.x the location where the raw uncompressed MySQL databases are stored is: /var/lib/mysql
  1. Users and DBA’s (Database Admins) create, delete, manipulate:


 
Note: To create, delete or alter a DBA you'll first need to be logged in as the root mysql DBA or a DBA with full access.
Show all users in the mysql database:
mysql> SELECT * FROM mysql.user;


Delete a null user:
mysql> DELETE FROM mysql.user WHERE user = ' ';

Delete all users who are not root:
mysql> DELETE FROM mysql.user WHERE NOT (host="localhost" AND user="root");

Change root user to a less-guessable name (for security):
mysql> UPDATE mysql.user SET user="mydbadmin" WHERE user="root";

Create a new DBA:
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Add a new user with specific privileges on a specific database:
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass';

Add a new DBA with all privileges to a specific database:
mysql> GRANT ALL PRIVILEGES ON mydatabase.* TO 'username'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;

Change a user or DBA password:
mysql> UPDATE mysql.user SET password=oldpass("newpass") WHERE User='username';

Delete a user or DBA from the database:
mysql> DELETE FROM mysql.user WHERE user="username";
  1. Simple database manipulation:
Show all databases:
mysql> SHOW DATABASES;

Create a database:
mysql> CREATE DATABASE mydatabase;

Use a specific database:
mysql> USE mydatabase;

Delete a database:
mysql> DROP DATABASE mydatabase;

Rename a database from command line (not from within MySQL), there is purposely no space beween -p and mypass:
mysqldump -u dbauser -pmypass databasename > mybackup.sql
mysql -u dbauser -pmypass newdatabasename < mybackup.sql

Show the last 200 queries to your database with the sample table name "queries" and the sample field "query_id":
mysql> SELECT * FROM queries ORDER BY query_id DESC LIMIT 200;

  1. Simple table manipulation:
Show all tables within a previously selected database
mysql> SHOW TABLES;

Show data within a specific table in a previously selected database:
mysql> SELECT * FROM tablename;

Rename a table within a specific database:
mysql> RENAME TABLE first TO second;
or
mysql> ALTER TABLE mytable rename as mynewtable;

Drop a table from a specific database:
mysql> DROP TABLE mytable;

  1. Simple column and field manipulation:
Show columns within a table:
mysql> DESC mytable;
or
mysql> SHOW COLUMNS FROM mytable;

Update a field within a previously selected database table:
mysql> UPDATE mytable SET mycolumn="newinfo" WHERE mycolumn="oldinfo";

Select data within a specific table in a previously selected database:
mysql> SELECT * FROM mytable WHERE mycolumn='mydata' ORDER BY mycolumn2;

Insert data into a table and its columns within a specific database:
mysql> INSERT INTO mytable VALUES('column1data','column2data','column3data','column4data','column5data','column6data','column7data','column8data','column9data');

Delete data from specific fields within a column:
mysql> DELETE FROM mytable WHERE mycolumn="mydata";

Update data in a specific table, column, field:
mysql> UPDATE mytable SET column1="mydata" WHERE column2="mydata";

  1. Simple backup options:
Backup all databases uncompressed from the command line (not from within MySQL):
backup: mysqldump -u root -pmypass --all-databases > alldatabases.sql

restore full: mysql -u username -pmypass < alldatabases.sql (no space in between -p and mypass)

restore single: mysql -u username -pmypass mydb < mydb.sql (no space in between -p and mypass)

Backup all databases compressed from the command line (not from within MySQL):
With bzip2: mysqldump --all-databases | bzip2 -c > databasebackup.sql.bz2 (use bunzip2 to uncompress)

With gzip: mysqldump --all-databases | gzip > databasebackup.sql.gz (use gunzip to uncompress)


Mini script to backup all databases and tables using gzip:
#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/backup/dbbackup-$date.sql.gz

Backup a specific database only:
mysqldump -u username -pmypass databasename > backupfile.sql

Backup database structure only:
mysqldump --no-data --databases databasename > structurebackup.sql

Backup a specific database and specific tables within that database only:
mysqldump --add-drop-table -u username -pmypass databasename table_1 table_2 > databasebackup.sql

The syntax for the command to issue is:
mysqldump -u [username] -p[password] [databasename] [table1 table2 ....] > backupfilename.sql

Backing up only the database structure of specific databases, not the actual data:
mysqldump --no-data --databases db1 db2 db3 > structurebackup.sql


15 comments:

  1. Quality content is the crucial to invite the visitors to visit the site, that's what this website is providing.
    www.bpl-business.org |

    ReplyDelete
  2. This is such a great resource that you providing and you give it away for free.
    http://www.biohealthchip.org |

    ReplyDelete
  3. Thanks so much with this fantastic new web site. I’m very fired up to show it to anyone. It makes me so satisfied your vast understanding and wisdom have a new channel for trying into the world.
    http://www.probusiness-ag.com |

    ReplyDelete
  4. I just read your blog and get the information which searching everywhere. Now it’s become on my top preference of reading for acquiring the updates without hassle.
    halosystech |

    ReplyDelete
  5. You might write about the services on the blog. You should disclose it's refreshing. Your blog conclusion could accelerate your shoppers.
    www.ez-autorental.org |

    ReplyDelete
  6. http://www.winetourtemecula.com |

    Changing your lifestyle can sometimes be challenging.But, if you are one of the many people with diabetes, you must start making adjustments in order to enjoy a healthier existence.

    ReplyDelete
  7. Thank you for another great article. Where else could anyone get that kind of information in such a perfect way of writing? I have a presentation next week, and I am on the look for such information. 
    www.cleantechcalendar.com |

    ReplyDelete
  8. They are now so popular. I am now bookmarked to this site. I am happy to be one of their community.
    kao-ji.net |

    ReplyDelete
  9. This is really a nice and informative, containing all information and also has a great impact on the new technology. Thanks for sharing it
    advancedbraincare.com |

    ReplyDelete
  10. Interesting and amazing how your post is! It Is Useful and helpful for me That I like it very much, and I am looking forward to Hearing from your next..
    www.thesmartechs.com |

    ReplyDelete
  11. I am really glad to be here.I hope to see more great articles here in the future too so keep it up and good luck to all of you!
    logitechonline |

    ReplyDelete
  12. So what does this mean for the future of Rails dev? gw2 gold Does DHH need to find a new gig?
    albanyweddingpages.com |

    ReplyDelete

Note:- Comment as: Option available to post without login select "Anonymous" from the drop down...........

For Latest Updates: Subscribe Now | | Test Your Knowledge, Take a Quiz now Click Here | | Site Best Viewed In Firefox

X