a. at Linux level you can make soft link to database dir to particular database ie schema. I don't is it possible at mysql level? b. There is no relation between storage engine and table space. Yes, Innodb uses tablespace you can read more on both. c. http://mysql-tips.blogspot.com/2005/04/setup-new-users-in-mysql.html
On Thu, Oct 1, 2009 at 2:10 PM, F.A.I.Z.A.L <sac.fai...@gmail.com> wrote: > thanks kumar. > > Storage > > in datadir contain only one location.is it possible to have multiple > location to store datafiles.if yes, how? > > what is the different between storage engine and tablespace in mysql? > > account creation > > *create user 'test'@'%' identified by 'test'; > grant usage on test.* to 'test'@'%' identified by 'test'; > grant select,insert,update,delete,create,drop on test.* to 'test'@'%'; > * > > I used 'USAGE' clause this time but even after that i can able to see other > schema and i can able to 'select' tables from other schema. > > how i can restrict this. only default schema should be accessible to the > user > > thanks in advance.. > > Best Regards > Faizal S > GSM : 9840118673 > Blog: http://oradbapro.blogspot.com > > > On Thu, Oct 1, 2009 at 1:13 PM, prabhat kumar <aim.prab...@gmail.com>wrote: > >> A. For transaction *innoDB* is best. >> >> B. *ALTER TABLE table_name ENGINE = InnoDB; >> * but* *if you tons of tables so better export all tables in file and >> replace all occurs of *myisam* with *InnoDB *the import it. >> and enable InnoDB engine. >> http://www.linux.com/archive/articles/46370 >> >> C. 1. Stop MySQL. >> 2. Copy current mysql dir to new drive. >> 3. Modify the current location with new location in /etc/my.cnf file >> , datadir variable. >> 4. Start MySQL. >> note: *datadir=/path/to/datadir/mysq*l >> http://docdb.fnal.gov/doc/my.cnf.html >> >> >> >> >> On Thu, Oct 1, 2009 at 12:53 PM, F.A.I.Z.A.L <sac.fai...@gmail.com>wrote: >> >>> hi Kumar >>> >>> thanks for your info.. >>> >>> another doubt.. which storage engine is best for transaction.. >>> >>> in my server. the default engine is myisam. now i changed one of my >>> schema to innoDB. is that correct. >>> >>> now i want to create another schema in different location. how to create >>> that? because current mountpoint space is very less, in this situation how >>> can move existing schemas to new location? >>> >>> thanks in advance.. >>> >>> >>> >>> Best Regards >>> Faizal S >>> GSM : 9840118673 >>> Blog: http://oradbapro.blogspot.com >>> >>> >>> On Thu, Oct 1, 2009 at 12:28 PM, prabhat kumar <aim.prab...@gmail.com>wrote: >>> >>>> >>>> a. INFORMATION_SCHEMA is the information database ie metadata, the place >>>> that stores information about all the other databases that the MySQL server >>>> maintains. Inside INFORMATION_SCHEMA there are several read-only tables. >>>> They are actually views, not base tables, so there are no files associated >>>> with them. >>>> >>>> Each MySQL user has the right to access these tables, but can see only >>>> the rows in the tables that correspond to objects for which the user has >>>> the >>>> proper access privileges. >>>> >>>> http://dev.mysql.com/doc/refman/5.0/en/information-schema.html >>>> >>>> b. Its depend on your requirement like your user will perform only DML >>>> query or also want DDL operations. >>>> >>>> http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html >>>> >>>> >>>> On Thu, Oct 1, 2009 at 12:08 PM, F.A.I.Z.A.L <sac.fai...@gmail.com>wrote: >>>> >>>>> thanks kumar.. >>>>> >>>>> now its working >>>>> >>>>> i have another doubt >>>>> >>>>> i created an user with limited privilege on particular schema. >>>>> schema name: test >>>>> >>>>> *mysql> create user 'test'@'%' identified by 'test'; >>>>> mysql> grant select,insert,update,delete,create,drop on test.* to >>>>> 'test'@'%';* >>>>> >>>>> after that i login with 'test' user using mysql query browser. but i >>>>> can able to see other schemas like 'information_Schema' and 'test_schema' >>>>> created by other. >>>>> >>>>> how can i avoid this. i want to see only my default schema.. >>>>> >>>>> 2. for created new mysql database or schema what are the privileges are >>>>> required.. >>>>> >>>>> please help.. >>>>> >>>>> Best Regards >>>>> Faizal S >>>>> GSM : 9840118673 >>>>> Blog: http://oradbapro.blogspot.com >>>>> >>>>> >>>>> On Thu, Oct 1, 2009 at 11:09 AM, prabhat kumar >>>>> <aim.prab...@gmail.com>wrote: >>>>> >>>>>> Use : mysql -u root -p (it will prompt for password) and if its not a >>>>>> local host also add -h ipaddress >>>>>> >>>>>> >>>>>> On Thu, Oct 1, 2009 at 11:01 AM, F.A.I.Z.A.L <sac.fai...@gmail.com>wrote: >>>>>> >>>>>>> Hi >>>>>>> >>>>>>> iam getting the following error while login into mysql server. >>>>>>> >>>>>>> andd141# mysql -u root >>>>>>> ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using >>>>>>> password: NO) >>>>>>> >>>>>>> >>>>>>> Can someone please help me understand why I am not able to login from >>>>>>> root? >>>>>>> >>>>>>> >>>>>>> Best Regards >>>>>>> Faizal S >>>>>>> GSM : 9840118673 >>>>>>> Blog: http://oradbapro.blogspot.com >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Best Regards, >>>>>> >>>>>> Prabhat Kumar >>>>>> MySQL DBA >>>>>> Datavail-India Mumbai >>>>>> Mobile : 91-9987681929 >>>>>> www.datavail.com >>>>>> >>>>>> My Blog: http://adminlinux.blogspot.com >>>>>> My LinkedIn: http://www.linkedin.com/in/profileprabhat >>>>>> >>>>> >>>>> >>>> >>>> >>>> -- >>>> Best Regards, >>>> >>>> Prabhat Kumar >>>> MySQL DBA >>>> Datavail-India Mumbai >>>> Mobile : 91-9987681929 >>>> www.datavail.com >>>> >>>> My Blog: http://adminlinux.blogspot.com >>>> My LinkedIn: http://www.linkedin.com/in/profileprabhat >>>> >>> >>> >> >> >> -- >> Best Regards, >> >> Prabhat Kumar >> MySQL DBA >> Datavail-India Mumbai >> Mobile : 91-9987681929 >> www.datavail.com >> >> My Blog: http://adminlinux.blogspot.com >> My LinkedIn: http://www.linkedin.com/in/profileprabhat >> > > -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat