Re: Replication config
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. Also, how do I set the slave to be read only? I set read-only in my.cnf and it made all databases read only. I want to limit just the replicated database to be read only. The rest of them are production databases in normal use by clients. I suppose just not having a login and pass to the replicated database is more than enough? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to copy an statement in Mysql console
History (Arrow Up/Down) COPY: Select (click + drag)PASTE: right click (or middle click) 2009/5/15 jean claude babin babi...@gmail.com Hello, I'm pretty new in MySql. I would like to know how you can copy a statement like mysql INSERT INTO customers ( firstname, surname, title, phone) values ( 'Jhon', 'Campbell', 'Mr', 345 987 5500) in the MySql console in order to avoid typing it each time you want to fill a new record. I tried to right click my mouse,it doesn't select what I need to highlight. Please,any advice is welcome. JC
Re: selecting the 'best' match
Hi! blackwater dev wrote: I have a hold car data such as color, model, make, year, etc. I want to allow the user to answer some questions and I'll present them with the car that 'best' matches their criteria. How do I do this? I still want to return ones that don't match exactly but want the closer matches ordered at the top: Table:cars columns: car_id, make, model, year, color, condition So if the user enterrs: model: Toyota year: 1998 condition:great color: blue I would show them a blue 1998 good conditioned camry first but farther down in the list might still have a blue good condition 98 Honda. Returning records in some specified order requires to sort them, and for this you need some criteria. In most cases, these are values of these records (like a name or size), but this doesn't match your application. You want to order by a distance: How much deviates this record from the perfect match? Mathematically, your records are points in some n-dimensional space (dimensions being model, year, condition, color, probably several others), and your perfect match is one specific such point. If all your dimensions were numerical (like year) or at least ordered (like condition: broken, poor, average, good, great), you could define a distance within each dimension, and then combine these to n-dimensional. Comparison to geometry: When you know the distances in x, y, and z dimension, you can use Pythagoras' formula: dist = sqrt ( (x1 - x)**2 + (y1 - y)**2 + (z1 - z)**2 ) But with non-numerical and even un-ordered data, your choices are pretty arbitrary: What is the distance between colors? Which one is closer to a Toyota - a Ford or a Volkswagen? So I doubt you can define a closeness (or distance) function. In your case, I propose to try UNION: SELECT ... WHERE model = UserModel AND color = UserColor ORDER BY condition, ABS (year - UserYear) UNION SELECT ... WHERE model = UserModel AND color != UserColor ORDER BY condition, ABS (year - UserYear) UNION SELECT ... WHERE model = !UserModel AND color = UserColor ORDER BY condition, ABS (year - UserYear) UNION SELECT ... WHERE model = !UserModel AND color = !UserColor ORDER BY condition, ABS (year - UserYear) This is based on the assumption that the exact match on the model is more important than on the color. If your experience with buyers differs, adapt the order. And if you think a mismatch in both model and color makes the car irrelevant, drop the last SELECT in the UNION. But as soon as the potential buyer says doesn't matter, you would need a different SELECT (one that doesn't differ by that field), so the whole thing calls for a program that generates the whole SQL statement based on the user input. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com (+49 30) 417 01 487 Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Inserting a default null date
Hi, I have a table with a column like: date date default null, If I enter an empty string in it, the default null value is added (as it should). But if I enter an invalid date by mistake, the date -00-00 date date is entered instead of the default null, and this is not good. Can I do something to force MySQL to insert a null date if the entered date is an invalid one? Thank you. -- Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Inserting a default null date
Hi Octavian,
One approach is to use a trigger,
mysql set sql_mode = '';
mysql create table temp_date(d date default null);
mysql create trigger temp_date_bi before insert on temp_date for each row set
new.d = if(new.d = '-00-00', null, new.d);
mysql insert into temp_date(d) values('2009-13-99');
mysql select * from temp_date;
+--+
| d|
+--+
| NULL |
+--+
1 row in set (0.01 sec)
mysql insert into temp_date(d) values('2009-11-19');
mysql select * from temp_date;
++
| d |
++
| NULL |
| 2009-11-19 |
++
2 rows in set (0.07 sec)
Cheers,
-Janek
CMDEV 5.0
On Fri, 2009-05-15 at 11:32 +0300, Octavian Rasnita wrote:
Hi,
I have a table with a column like:
date date default null,
If I enter an empty string in it, the default null value is added (as it
should).
But if I enter an invalid date by mistake, the date -00-00 date date is
entered instead of the default null, and this is not good.
Can I do something to force MySQL to insert a null date if the entered date
is an invalid one?
Thank you.
--
Octavian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Inserting a default null date
Hi, I have a table with a column like: date date default null, If I enter an empty string in it, the default null value is added (as it should). But if I enter an invalid date by mistake, the date -00-00 date date is entered instead of the default null, and this is not good. Can I do something to force MySQL to insert a null date if the entered date is an invalid one? That's silly, if the data you want to insert is considered to be invalid, reject it, don't replace it with NULL. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download FREE! Database Workbench Lite for MySQL! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Inserting a default null date
Agreed. And don't forget to listen to the warnings MySQL sends back,
e.g.:
mysql create table temp_date(d date default null);
Query OK, 0 rows affected (0.15 sec)
mysql insert into temp_date(d) values('2009-13-99');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql show warnings;
+-+--++
| Level | Code | Message|
+-+--++
| Warning | 1265 | Data truncated for column 'd' at row 1 |
+-+--++
1 row in set (0.00 sec)
mysql select * from temp_date;
++
| d |
++
| -00-00 |
++
1 row in set (0.01 sec)
Andrew
-Original Message-
From: Martijn Tonies [mailto:m.ton...@upscene.com]
Sent: 15 May 2009 14:43
To: mysql@lists.mysql.com
Subject: Re: Inserting a default null date
Hi,
I have a table with a column like:
date date default null,
If I enter an empty string in it, the default null value is added (as
it
should).
But if I enter an invalid date by mistake, the date -00-00 date
date
is entered instead of the default null, and this is not good.
Can I do something to force MySQL to insert a null date if the entered
date is an invalid one?
That's silly, if the data you want to insert is considered to be
invalid,
reject it,
don't replace it with NULL.
With regards,
Martijn Tonies
Upscene Productions
http://www.upscene.com
Download FREE! Database Workbench Lite for MySQL!
Database questions? Check the forum:
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Can someone please explain why I have 3 entries for root or if this is normal behavior for MySQL? I thought after a installation of MySQL, you normally have 2: 1 - localhost 2 - host.domain.com For some reason I had a 3rd entry: 3 - 127.0.0.1 I don't know if I did the right thing but I ran the following command: update user set host=localhost where host='127.0.0.1'; This basically changed the 127.0.0.1 entry in mysql databased, host section to localhost. I am not sure if MySQL needs to have the 3rd host entry for root that equals 127.0.0.1 or if it generally should just have the localhost and fqdn. Can anyone please clear this up for me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
It is important to understand deeply mysql client access control.
Basically you need only one root account from the localhost for
administration purposes.
Keep in mind that when you login specifying 'localhost' (either by the
-h flag or implicit) MySQL will use the mysql client socket interface,
if you specify '127.0.0.1' it will use the TCP/IP port (3306).
'localhost' is a sort of keyword telling the client to use the unix
socket file, '127.0.0.1' is bound to the tcp/ip port.
Host matching is always done BEFORE user matching.
MySQL sorts HOSTS with more specific entries on top and less ones on
bottom ('%' wildcarded entries)
MySQL sorts USERS with more specific entries on top and less ones on
bottom ('' empty user)
Bottom line, you need a triplet of USER-HOST-PASS for each account.
having localhost AND 127.0.0.1 it is only related to the different
connection method (socket , tcpip)
for any other (remote) account tcp-ip will be used.
I don't know if you are now more ore less confused!
Claudio
Carlos Williams wrote:
Can someone please explain why I have 3 entries for root or if this is
normal behavior for MySQL? I thought after a installation of MySQL,
you normally have 2:
1 - localhost
2 - host.domain.com
For some reason I had a 3rd entry:
3 - 127.0.0.1
I don't know if I did the right thing but I ran the following command:
update user set host=localhost where host='127.0.0.1';
This basically changed the 127.0.0.1 entry in mysql databased, host
section to localhost.
I am not sure if MySQL needs to have the 3rd host entry for root that
equals 127.0.0.1 or if it generally should just have the localhost and
fqdn.
Can anyone please clear this up for me?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni claudio.na...@gmail.com wrote: I don't know if you are now more ore less confused! Claudio I would say less because you basically explained that I need to have localhost 127.0.0.1. Now my problem is that no longer have this and would like to know what I can do to resolve this by re-adding the 127.0.0.1 host / root user parameter back into MySQL. mysql use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select user, host, password from user; +---++--+ | user | host | password | +---++--+ | root | host.domain.com | 032c41e8435273a7 | | root | localhost | 032c41e8435273a7 | | roundcube | localhost | 032c41e8435273a7 | | mrbs | localhost | 6322a1af59897de4 | | phpbb| localhost | 5d2e19393cc5ef67 | +---++--+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Password Reset Not Working
Exactly, you need as many rows as many combination user/host we can also say that an account in MySQL is not the username BUT the username AND host combination. If you want to duplicate any account (also the root/localhost) do this: mysql show grants for 'root'@'localhost'; then have fun! Claudio Carlos Williams wrote: On Fri, May 15, 2009 at 3:57 PM, Claudio Nanni claudio.na...@gmail.com wrote: I don't know if you are now more ore less confused! Claudio I would say less because you basically explained that I need to have localhost 127.0.0.1. Now my problem is that no longer have this and would like to know what I can do to resolve this by re-adding the 127.0.0.1 host / root user parameter back into MySQL. mysql use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select user, host, password from user; +---++--+ | user | host | password | +---++--+ | root | host.domain.com | 032c41e8435273a7 | | root | localhost | 032c41e8435273a7 | | roundcube | localhost | 032c41e8435273a7 | | mrbs | localhost | 6322a1af59897de4 | | phpbb| localhost | 5d2e19393cc5ef67 | +---++--+ 5 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
xtrabackup
Anyone using this? I'm looking to install it to see how it works. I read in some of the documentation that it could be used to set up a replication server and would automatically roll forward using binlogs. I've done rollforward using binlogs manually before but I was wondering what kind of mechanism xtrabackup uses to do this. I'm currently needing to restore a replication server and since there's no real hurry on it. I thought I'd give xtrabackup a try. Sounds like it could be a useful tool but the documentation I've found so far is a bit lacking as far as details are concerned. If anyone could give me some information, or point me to some good documentation, It would be much appreciated. Thanks, --Walt
How to capture MySQL queries sent to server?
I'm running Windows XP and I'd like to capture the SQL commands that are sent to the MySQL server. Is there a way to do this on Windows? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to capture MySQL queries sent to server?
Turn On General Logging In my.ini insert this option in the [mysqld] section log=C:\MySQLLogging.txt and restart mysql Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM : RolandoLogicWorx Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Friday, May 15, 2009 6:09 PM To: mysql@lists.mysql.com Subject: How to capture MySQL queries sent to server? I'm running Windows XP and I'd like to capture the SQL commands that are sent to the MySQL server. Is there a way to do this on Windows? TIA Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
