Using MySQL, AdministrationWorkshop RequirementsYou should have access to the MySQL command line client software.Various different PRIVILEGES on the MySQL ServerIntroductionIn the other MySQL Virtual Workshops we have used commands that are pretty much applicable to anyone. This part of the MySQL series is aimed at giving a rudimentary understanding of managing a MySQL database server. As such the task covered here are not really about manipulating data or database structures, but the actual databases themselves.Creating a DatabaseIn order to create a database you need to have the PRIVILEGES- this may be because you are the root user or you (or you systems administrator) has created an admin user that has ALL PRIVILEGES over all databases. In these examples a user called ‘admin’ has been created precisely for this purpose. Creating a database is fairly straightforward.Logging InA reminder of how to start the MySQL Client Software, and as we are not concerned with manipulating just one database we don’t have to specify a database as part of our startup command.$ mysql -u -pEnter password:Create database commandNext we are ready to enter the very simple command to create a database which is:mysql> CREATE DATABASE ; Let’s imagine that we are going to create a ‘vworks’ database (those wishing to create a database for use with the VWs should use this). We would enter the command:mysql> CREATE DATABASE vworks; We can now check for the presence of this database by typing:mysql> SHOW DATABASES;+———–+| Database |+———–+| mysql || vworks |+———–+2 rows in set (0.06 sec)The other database listed (‘mysql’) is the internal database which MySQL uses to manage users, permissions etc. NOTE: Deleting or DROPing a database is similar to the DROP TABLE command issued in Part 4. e.g.DROP DATABASE Granting Privileges on the new databaseNow that we have created a database, we need to decide who gets to use it. This is done by granting permissions for a user to use the database. This has a simplified syntax of:GRANT ON TO [IDENTIFIED BY ] [WITH GRANT OPTION]Where the items in square brackets are optional. The most common use is to give ALL PRIVILEGES on a database to a local user who has to use a password to access the database (in this case vworks).mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@localhost -> IDENTIFIED BY ‘newpassword’; If you are creating a database for use with the rest of the Virtual Workshops you should use this statement, substituting your username and password of choice. There are some other options we will look at. To restrict the user to manipulating data (rather than table or database structures) the statement would be altered to:mysql> GRANT SELECT,INSERT,UPDATE,DELETE -> ON vworks.* -> TO newuser@localhost -> IDENTIFIED BY ‘newpassword’; So that the user can only change the data using SELECT,INSERT,UPDATE or DELETE statements. If you wished to give a non-local user permissions on the database (for use with remote clients) then you could designate an IP or host address from which the user can connect:mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@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 vworks.* -> TO newuser@’%’ -> IDENTIFIED BY ‘newpassword’;You could even decide that a user doesn’t need a password if connecting from a certain machine. mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@192.168.0.2But I think it is sometimes good to provide a password anyway. Finally we’ll look at the WITH GRANT OPTION condition. This allows the user to give others privileges to that database:mysql> GRANT ALL PRIVILEGES -> ON vworks.* -> TO newuser@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.mysql> GRANT SELECT,INSERT,UPDATE,DELETE -> ON vworks.* -> TO friend@localhost -> IDENTIFIED BY ‘friendpass’; The WITH GRANT OPTION usually signifies ownership although it is worth noting that no user can GRANT more privileges that they themselves possess.Revoking privilegesRevoking privileges is almost identical to granting them as you simply substitute RE VOKE…. FROM for GRANT….TO and omit any passwords or other options.For example to REVOKE the privileges assigned to a user called ‘badvworks’:mysql> REVOKE ALL PRIVILEGES -> ON vworks.* -> FROM badvworks@localhost; Or just to remove UPDATE, INSERT and DELETE privileges to that data cannot be changed.mysql> REVOKE INSERT,UPDATE,DELETE -> ON vworks.* -> FROM badvworks@localhost; Backing Up DataThere are several methods we can use to backup data. We are going to look at a couple of utilities that come with MySQL: mysqlhotcopy and mysqldump. mysqlhotcopymysqlhotcopy is a command line utility written in Perl that backs up (to a location you specify) the files which make up a database. You could do this manually, but mysqlhotcopy has the advantage of combining several different commands that lock the tables etc to prevent data corruption. The syntax (as ever) first.$ mysqlhotcopy -u -p /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. mysqldumpThis 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 -p [
From: http://sitestree.com/?p=4720
Categories:5
Tags:
Post Data:2008-06-02 09:17:27
Shop Online: <a href='https://www.ShopForSoul.com/' target='new' rel="noopener">https://www.ShopForSoul.com/</a>
(Big Data, Cloud, Security, Machine Learning): Courses: <a href='http://Training.SitesTree.com' target='new' rel="noopener"> http://Training.SitesTree.com</a>
In Bengali: <a href='http://Bangla.SaLearningSchool.com' target='new' rel="noopener">http://Bangla.SaLearningSchool.com</a>
<a href='http://SitesTree.com' target='new' rel="noopener">http://SitesTree.com</a>
8112223 Canada Inc./JustEtc: <a href='http://JustEtc.net' target='new' rel="noopener">http://JustEtc.net (Software/Web/Mobile/Big-Data/Machine Learning) </a>
Shop Online: <a href='https://www.ShopForSoul.com'> https://www.ShopForSoul.com/</a>
Medium: <a href='https://medium.com/@SayedAhmedCanada' target='new' rel="noopener"> https://medium.com/@SayedAhmedCanada </a>