RE: Recommend A Backup User / Privileges?
Carlos mennens wrote: Can you guys recommend something for me here? I don't really know enough about MySQL 'grant' permissions to determine which would work or if the 'backup' user would require high level privileges. Hi, Set the rights of the script file to rwx-- . So only the owner can read it. Or provide in the home of the user who executes the script a file named .my.cnf . Inside that file you provide a section [mysqld] , where you can specify the user and the password to connect to the database. Also this file has only to be readable for the user who executes the script (rwx--). Bernd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to backup a mix from InnoDB- and MyISAM-tables ?
Hi, I have the problem that i have to backup several databases who include a mix of InnoDB- and MyISAM-tables. I'd like to use mysqldump. The manpage proposes different options for MyISAM- and InnoDB-tables. What is about --single-transaction ? --single-transaction is recommend for InnoDB-tables. MyISAM-tables are not dumped in a consistent state using this option, so it's not the optimum for my scenario. Is --single-transaction a recommendation for InnoDB-tables or is it a must ? What is about --lock-all-tables ? The manpage says --single-transaction is a much better option for InnoDB-tables (see above). But does --lock-all-tables work with InnoDB-tables ? The manpage also says: This is achieved by acquiring a global read lock for the duration of the whole dump. Does that mean that also reading is locked, or does it mean that it is locked to enable reading ? Currently i have the impression that --lock-all-tables is the better way for me dumping databases including both types of tables. Thanks for any answer. Bernd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Array data type
On 14/05/2010 09:54, Joerg Bruehe wrote: Hi Samrat, all! Samrat Kar wrote: Hello, How to store multiple values in a single field? Is there any array data type concept in mysql? Multiple values in a single field would be an explicit violation of the relational model (on which the SQL language is based) and cause all kinds of trouble in your queries. Ever and again, developers use some kind of encoding to store a combination of values (like flags in a bit field) in one database field, but in many cases this makes queries very hard to write, and may prevent optimization of the SQL statement. It depends on your application, especially on whether this field will be used in search conditions (... WHERE combined_field has flag_X ...), to decide about a sensible approach. In general, I would prefer separate fields for different flags, and a separate table for a truly multi-valued field (like multiple postal or mail addresses for a person). If you're merely *storing* the data in the table, and will only ever retrieve it based on other factors - that is, you'll never use that field for any operands including joins and 'where' clauses - then it's often useful to store a flattened array (eg, one created by PHP's serialize() function, javascript JSON or even XML) as a string and then expand it to an array again after retrieving it. That can often be a useful way of storing meta-data about a data object (eg, EXIF data from a photograph), especially where you can't know in advance what the array structure will be when you create the database. However, that's not really an array datatype in MySQL, it's simply a method of storing an array as a string. So it's of fairly limited application, there are cases where it's very useful but it's not a substitute for storing the array values separately using the appropriate table design where you do need to run queries against it. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Array data type
Hi, How to store multiple values in a single field? Is there any array data type concept in mysql? As Jörg said Multiple values in a single field would be an explicit violation of the relational model... then also, if you want to use. this might be this will help you. I used like this in past: In database, I have taken a column as TEXT. In which I have separated a value by *- *(hifen) eg. furniture table there is 2 col , name (person name) - used (furniture's used by that person). value will be : | Name| Used | -- | Prabhat | chair-table-bed | === And in PHP you can easily separate these value. eg, $names = Markus;Nigel;David; To use these names in a meaningful way, we should first separate them into an array ($namearray), using explode()http://au3.php.net/manual/en/function.explode.php : $namearray = explode(;, $names); The end result: $namearray = Array ( [0] = Markus [1] = Nigel [2] = David ) But remember this is VERY bad database design. I had used since, that was required for few days only. Thanks, In database : On Sun, May 16, 2010 at 9:34 PM, Mark Goodge m...@good-stuff.co.uk wrote: On 14/05/2010 09:54, Joerg Bruehe wrote: Hi Samrat, all! Samrat Kar wrote: Hello, How to store multiple values in a single field? Is there any array data type concept in mysql? Multiple values in a single field would be an explicit violation of the relational model (on which the SQL language is based) and cause all kinds of trouble in your queries. Ever and again, developers use some kind of encoding to store a combination of values (like flags in a bit field) in one database field, but in many cases this makes queries very hard to write, and may prevent optimization of the SQL statement. It depends on your application, especially on whether this field will be used in search conditions (... WHERE combined_field has flag_X ...), to decide about a sensible approach. In general, I would prefer separate fields for different flags, and a separate table for a truly multi-valued field (like multiple postal or mail addresses for a person). If you're merely *storing* the data in the table, and will only ever retrieve it based on other factors - that is, you'll never use that field for any operands including joins and 'where' clauses - then it's often useful to store a flattened array (eg, one created by PHP's serialize() function, javascript JSON or even XML) as a string and then expand it to an array again after retrieving it. That can often be a useful way of storing meta-data about a data object (eg, EXIF data from a photograph), especially where you can't know in advance what the array structure will be when you create the database. However, that's not really an array datatype in MySQL, it's simply a method of storing an array as a string. So it's of fairly limited application, there are cases where it's very useful but it's not a substitute for storing the array values separately using the appropriate table design where you do need to run queries against it. Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.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
To get Processlist and Status of MySQL ?
Hi All, What is the other way to get the *processlist* and *status* of mysql server on event that the mysql server cannot be able to reach due to hung or crashed? Cheers, James
