Re: Replication config
Hi Scott
You may use the script below to reload replication if you can ensure that
the master db doesn't change during the dump operation. Otherwise you may
set a lock on the master manually.
Regards, Thomas
#!/bin/bash
#
# replicate-reload
#
# This is free software. There is no warranty at all.
# The program may melt your computer and kill your cat.
# Use at your own risk.
#
# restart new replication of DBASE on localhost; dump from MASTER
#
# Note: No changes to DBASE may take place on the master during
# the dump operation. See comments below.
#
# Set your values here:
DBASE=adbtoreplicate
MASTER=host.domain.tld
MYUSER=useronlocalhost
MYPWD=thisisagoodpassword
# Set replication user and password
REPLUSER=replicationuser
REPLPWD=replicationuserpassword
# End of user configuration
SPACE=' '
TAB=$(echo -ne \t)
MASTER_ALIAS=$(echo $MASTER | sed -e s/\\..*//)
MASTER_POS=$(echo FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; | mysql -u $MYUSER -h $MASTER -p$MYPWD $DBASE \
| sed -e /^${MASTER_ALIAS}-bin/ !d)
#
# Beware: From this point on no changes on the master may be made
# until the dump has finished. If this can't be enforced you
# have to place a lock manually on the master and release it
# once the dump is complete.
#
MASTER_FILE=$(echo $MASTER_POS | cut -s -d $TAB -f 1)
MASTER_LOGPOS=$(echo $MASTER_POS | cut -s -d $TAB -f 2)
#echo MASTER_POS=$MASTER_POS
echo MASTER_FILE=$MASTER_FILE
echo MASTER_LOGPOS=$MASTER_LOGPOS
# Get the dump
echo Dumping '$DBASE' from $MASTER
#
# User: set your own dump options here as needed
mysqldump -u $MYUSER -h $MASTER -p$MYPWD \
--skip-opt \
--add-drop-table \
--max_allowed_packet=1M \
--character-sets-dir=/usr/share/mysql/charsets \
--skip-set-charset \
--extended-insert --lock-all-tables --quick \
--quote-names --master-data=2 $DBASE \
| sed -e /^SET / d ${DBASE}.sql
#
# Note: Changes on the master are allowed from here on
#
echo -e \nCHANGE MASTER TO MASTER_HOST='$MASTER', \
MASTER_USER='$REPLUSER', MASTER_PASSWORD='$REPLPWD', \
MASTER_LOG_FILE='$MASTER_FILE', MASTER_LOG_POS=${MASTER_LOGPOS}; \
${DBASE}.sync.sql
echo STOP SLAVE; | mysql -u $MYUSER -h localhost -p$MYPWD $DBASE
# reload dumped database
echo Reloading '${DBASE}' on localhost
cat ${DBASE}.sql ${DBASE}.sync.sql | mysql \
-u $MYUSER -h localhost -p$MYPWD $DBASE
echo Starting slave $(hostname)
echo START SLAVE; | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
sleep 2
echo SHOW SLAVE STATUS; | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
rm -f ${DBASE}.sql ${DBASE}.sync.sql
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MAC address as primary key - BIGINT or CHAR(12)
Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia
Re: MAC address as primary key - BIGINT or CHAR(12)
Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thursday 14 May 2009 09:53:58 am Fish Kungfu wrote: Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia And, if you use default charsets of anything else, make sure you set this column to CHARACTER SET ascii. A smaller index is a happier index. kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
You might even want to plan for longer MAC addresses. I don't follow developments in that area, but they had to go from IP4 to IP6 and they might have to introduce longer MAC addresses. It isn't hard to change a MySQL field definition, but your applications would be more of a problem. -Original Message- From: Ilia KATZ [mailto:ik...@dane-elec.co.il] Sent: Thursday, May 14, 2009 9:26 AM To: mysql@lists.mysql.com Subject: MAC address as primary key - BIGINT or CHAR(12) Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
Not exactly. The allowed letters are A,B,C,D, E, F. Every 2 characters (not including separators) can be treated as a hexadecimal number that can be represented with one byte. 6 bytes in total. for example: 00:1D:7D:48:08:8F pair value 00 0 1st byte 1D29 2nd byte 7D125 3rd byte 48 72 4th byte 08 8 5th byte 8F143 6th byte the last 2 bytes (of the BIGINT) left unused. Ilia From: Fish Kungfu [mailto:fish.kun...@gmail.com] Sent: Thursday, May 14, 2009 3:54 PM To: Ilia KATZ Cc: mysql@lists.mysql.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia This footnote confirms that this email message has been scanned by PineApp Mail-SeCure for the presence of malicious code, vandals computer viruses.
Re: MAC address as primary key - BIGINT or CHAR(12)
On Thu, 14 May 2009, Ilia KATZ wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia Hi It depends. You may convert the MAC address to a decimal integer and store it as a BIGINT. Use UNSIGNED as well; there are no negative numbers involved. This may gain some speed and saves storage space. The drawback I can see is that these numbers are not human readable, but you may convert back to HEX when retrieving data. And it may break when they start using larger MAC addresses eventually. Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MAC address as primary key - BIGINT or CHAR(12)
Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MAC address as primary key - BIGINT or CHAR(12)
I'm new to MySQL so can't answer the OP's question, but: MAC addresses do not by design contain letters. Native MAC addresses are 48-bit (6-byte) integers: http://standards.ieee.org/getieee802/download/802-2001.pdf The confusion arises because a MAC address is usually /represented/ as hexadecimal, and that might contain letters, but MAC addresses natively are pure 6-byte integers. So the issue is whether you want to index by a 48-bit number or a 12-character ascii string. For efficiency's sake, I'm guessing you'd choose the former. I'll be interested in the answer, though, from someone with experience. -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) To: Ilia KATZ ik...@dane-elec.co.il Cc: mysql@lists.mysql.com Date: Thursday, May 14, 2009, 11:38 AM Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.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: MAC address as primary key - BIGINT or CHAR(12)
As I said in my post, this is a general principle for me. Nominal data should have a data type of some sort of character. You will never run into unexpected problems down the line. On Thu, May 14, 2009 at 11:04 AM, Pete Wilson pete...@yahoo.com wrote: I'm new to MySQL so can't answer the OP's question, but: MAC addresses do not by design contain letters. Native MAC addresses are 48-bit (6-byte) integers: http://standards.ieee.org/getieee802/download/802-2001.pdf The confusion arises because a MAC address is usually /represented/ as hexadecimal, and that might contain letters, but MAC addresses natively are pure 6-byte integers. So the issue is whether you want to index by a 48-bit number or a 12-character ascii string. For efficiency's sake, I'm guessing you'd choose the former. I'll be interested in the answer, though, from someone with experience. -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) To: Ilia KATZ ik...@dane-elec.co.il Cc: mysql@lists.mysql.com Date: Thursday, May 14, 2009, 11:38 AM Definitely CHAR (or VARCHAR). If the format of a MAC address changes at all, you could be in real trouble. Also, if a MAC address can have a leading 0 (I don't know anything about MAC addresses), then storing it as some sort of number could lose that. This is a general rule for me. A field might only contain numbers (at one particular point in time) but if those numbers are really nominal data (in which the size or order does not matter) then they should be CHAR or VARCHAR fields anyway. On Thu, May 14, 2009 at 8:26 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: MAC address as primary key - BIGINT or CHAR(12)
I agree, and didn't mean to say that I disagreed. This is certainly one of the top five principles to follow, imo. Too many times, while trouble-shooting, I've run up on the rock of a binary (meaning: indecipherable) field. What is the cost of including the binary representation (for indexing) and also the readable representation (just for debugging/problem-solving)? (Well, of course I know what the cost is: it's 12 bytes, plus overhead, per row.) It all depends, but in general, would you call that too costly, given the benefit? -- Pete Wilson http://www.pwilson.net/ --- On Thu, 5/14/09, Jim Lyons jlyons4...@gmail.com wrote: From: Jim Lyons jlyons4...@gmail.com As I said in my post, this is a general principle for me. Nominal data should have a data type of some sort of character. You will never run into unexpected problems down the line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
Store as CHAR(12), not VARCHAR because they are ALWAYS a fixed length. It
saves space and is faster to index/lookup.
If you're using IPv4 only
then stick with CHAR(12).
elseif you think you'll go to IPv6 then
if large dataset ( 1M rows say),
plan for it now as ALTER later will take a long time
else
stick with (12) and ALTER later when needed
If you're storing IP addresses, DO NOT store them as characters. Store them
as UNSIGNED INTEGERS and use the INET_ATON() and INET_NTOA() functions.
Aside from being more efficient to store, you will get proper sorting.
Sorting strings will not work the way you want it to.
suggested reading:
http://dev.mysql.com/doc/refman/5.1/en/data-size.html
http://dev.mysql.com/doc/refman/5.0/en/char.html
http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function
_inet-aton
http://us2.php.net/manual/en/function.inet-ntop.php
http://us2.php.net/manual/en/function.inet-pton.php
http://us2.php.net/manual/en/function.ip2long.php
http://us2.php.net/manual/en/function.long2ip.php
I tried to attach a file with many useful networking functions, if it didn't
go through because for some silly reason attachments are not allowed, I also
pasted it here below. You'll have to reformat because this list software I'm
sure will mangle the code. :-\
--- 8
---
?php
/**
* Given a netmask in dotted quad notation, returns the CIDR 'slash'
notation.
*
* @access public
* @return CIDR 'slash' notation
* @param$netmask the dotted quad netmask.
* @param $verify force the result to be false if the netmask is invalid
instead of returning a safe, small range (27)
* @since3.0
*/
function netmask2Range($netmask, $verify = false) {
$CIDRMap = array(
1 = 128.0.0.0,
2 = 192.0.0.0,
3 = 224.0.0.0,
4 = 240.0.0.0,
5 = 248.0.0.0,
6 = 252.0.0.0,
7 = 254.0.0.0,
8 = 255.0.0.0,
9 = 255.128.0.0,
10 = 255.192.0.0,
11 = 255.240.0.0,
12 = 255.240.0.0,
13 = 255.248.0.0,
14 = 255.252.0.0,
15 = 255.254.0.0,
16 = 255.255.0.0,
17 = 255.255.128.0,
18 = 255.255.192.0,
19 = 255.255.224.0,
20 = 255.255.240.0,
21 = 255.255.248.0,
22 = 255.255.252.0,
23 = 255.255.254.0,
24 = 255.255.255.0,
25 = 255.255.255.128,
26 = 255.255.255.192,
27 = 255.255.255.224,
28 = 255.255.255.240,
29 = 255.255.255.248,
30 = 255.255.255.252,
31 = 255.255.255.254,
32 = 255.255.255.255
);
$CIDR = array_search($netmask,$CIDRMap);
if ($CIDR == $verify === false)
$CIDR = 27; // If we can't determine the range we will
default to a small range (/27)
elseif ( $CIDR == $verify !== false )
$CIDR = false; // if verify is set to true and the result is
empty, return false instead of a safe range
return $CIDR;
} //netmask2Range
/**
* This function will return an array of either a negative error code
* or all possible IP addresses in the given range.
*
* @access public
* @return an array of either a negative error code or all possible IP
addresses in the given range
* @param$iprange NNN.NNN.NNN.NNN/CIDR or NNN.NNN.NNN.NNN :
NNN.NNN.NNN.NNN (spaces are okay).
* @since3.0
* @author Daevid Vincent [dae...@]
* @date 10.13.03
*/
function deduceRange($iprange)
{
//check if we're in / notation (CIDR)
Password Reset Not Working
I noticed today that I strangely was unable to login to MySQL as root. I just assumed I forgot the password and decided to reset my root password: 1 - /etc/init.d/mysqld stop 2 - mysqld_safe --skip-grant-tables 3 - mysql -u root 4 - mysql use mysql; mysql mysql update user set password='letmein' where user='root'; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql flush privileges; mysql quit 5 - /etc/init.d/mysqld restart *Now after I do all that, I get the following:* mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I have done this over and over and can't login so I am wondering if something is wrong with MySQL or am I just not properly resetting the password... Someone please help! -- 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
Hi Carlos
Try this
mysql update user set password=password('letmein') where user='root';
This way the password is saved encrypted, thats the way is compared when
you try to log in.
Carlos
On 5/14/2009 5:28 PM, Carlos Williams wrote:
I noticed today that I strangely was unable to login to MySQL as root.
I just assumed I forgot the password and decided to reset my root
password:
1 - /etc/init.d/mysqld stop
2 - mysqld_safe --skip-grant-tables
3 - mysql -u root
4 - mysql use mysql;
mysql mysql update user set password='letmein' where user='root';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql flush privileges;
mysql quit
5 - /etc/init.d/mysqld restart
*Now after I do all that, I get the following:*
mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using
password: YES)
I have done this over and over and can't login so I am wondering if
something is wrong with MySQL or am I just not properly resetting the
password...
Someone please help!
--
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 Thu, May 14, 2009 at 6:31 PM, Carlos Proal carlos.pr...@gmail.com wrote:
Hi Carlos
Try this
mysql update user set password=password('letmein') where user='root';
This way the password is saved encrypted, thats the way is compared when you
try to log in.
Thanks for the reply! I followed both methods in the following URL / link:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix
After doing both successfully, I was unable to login over and over. I
think something is wrong with MySQL. I have never seen this before :(
--
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
Check how many root rows do you have on the user table (select * from
user where user='root';), some times there are several rows with
different grants and probably you are going through and invalid rule.
Carlos
On 5/14/2009 5:39 PM, Carlos Williams wrote:
On Thu, May 14, 2009 at 6:31 PM, Carlos Proal carlos.pr...@gmail.com wrote:
Hi Carlos
Try this
mysql update user set password=password('letmein') where user='root';
This way the password is saved encrypted, thats the way is compared when you
try to log in.
Thanks for the reply! I followed both methods in the following URL / link:
http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix
After doing both successfully, I was unable to login over and over. I
think something is wrong with MySQL. I have never seen this before :(
--
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 Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- 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
try running the command like this select * from user where user='root' \G Capital G is a must. thanks Doug Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- http://sfp.central.sun.com/ * Douglas Nelson * Senior Technical Consultant *Need Sun JES Help?* Software Field Practice Phone +1 877-234-2879/x51438 Mobile 919-259-3719 Email sfp-h...@sun.com http://blogs.sfbay/roller/page/sfp -- 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
The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos On 5/14/2009 5:55 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... -- 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
Carlos Proal wrote: The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos On 5/14/2009 5:55 PM, Carlos Williams wrote: On Thu, May 14, 2009 at 6:44 PM, Carlos Proal carlos.pr...@gmail.com wrote: Check how many root rows do you have on the user table (select * from user where user='root';), some times there are several rows with different grants and probably you are going through and invalid rule. I checked and when I ran the command you suggested: mysql use mysql; Database changed mysql select * from user where user='root'; I get a bunch of gibberish on the screen but the only thing I can make out are two entries for root: | mysql.unixslut.com | root | 6d21bd9609b168e4 | Y | Y | Y | 127.0.0.1| root | 6d21bd9609b168e4 | Y | Y | Y So what does this mean and how can I resolve this? I am trying this from the machine locally so I would assume localhost works fine... Pardon, for butting in, but are you seriou? unix slut ? My first impression based on that would be, man you've been hacked! :-D -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- 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 copy an statement in Mysql console
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: Replication config
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 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. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. I have it set up and working in test. I will redo it again once I get a better handle on it. I am still a little confused on one aspect. In the mysql sample cfg file, the section that has: #Replication Slave there is a very clear OR to use either #1 OR #2. I did the suggestions of #2, issuing #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; on the slave. I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = passw3rd master-port = 3306 # End slave config Am I correct in that this is not needed. I know I for certain need server_id, but is that all I need, and I have redundant data? I figure also better to not have raw user and pass in a cnf file if it is not needed. I would say 99% of the examples on the internets are using both methods, the MySql docs are not entirely clear to me on this specific issue. Conflicting data on the comments for sure. I also have seen a good deal more options specified than I have. To be clear, I am only looking for one way replication, the most basic, a master that is read/write by clients, and a slave that is neither read/ write by clients, but only reading in data from the master, syncing it. Here is a example of other options I am seeing, comments interspersed... server-id = 1 log-bin = /usr/local/mysql/var/bin.log I take it I do not need this if I am ok with default data dir? log-slave-updates I am pretty sure I do not not need this, since I am only doing master to slave, and no cascading replication, or circular rep. log-bin-index = /usr/local/mysql/var/log-bin.index Same as log-bin, of I am ok with default data dir? log-error = /usr/local/mysql/var/error.log Again, if I am ok with default data-dir? relay-log = /usr/local/mysql/var/relay.log I do not seem to have this file anywhere. relay-log-info-file = /usr/local/mysql/var/relay-log.info relay-log-index = /usr/local/mysql/var/relay-log.index I do not see that I have these on the master, I have it on the slave. Maybe all these logs are best to be defined. Perhaps if they are not, the hostname may be used in the naming of the logs, and if a hostname ever changes, I would have issues on getting replication to fine the logs? auto_increment_increment = 10 auto_increment_offset = 1 I am definitely not clear on these. I want a 100% replica. Why do I need to worry of key id collisions at all? Or is This n/a to a simple master/slave setup master-host = other master hostname master-user = replication username master-password = replication password These I wonder if are needed at all, if I use the sql CHANGE replicate-do-db = somedbname1 replicate-do-db = somedbname2 Good to know, wondered how to add more, if you comma seperated
Re: How to copy an statement in Mysql console
On Thu, May 14, 2009 at 7:41 PM, jean claude babin babi...@gmail.com wrote: 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. What you have at the console is history. Use your up and down arrows.. - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- 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 set proper ibdata file?
David, The only metric is as big as it needs to be. Personally I recommend setting innodb-file-per-table in the my.cnf, then configuring the ibdata file to be some small size, with the autoextend option enabled. That way it will take up only as much space as it needs, you'll be able to reduce space effectively by dropping tables. Regards, Gavin Towey Message-ID: 69069d7d0905140247w29e73f81pb394981fd9193...@mail.gmail.com --0016e6476110ee6f5e0469dc372e Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi. I want to know if there're a metric to adjust the innodb's ibdata file size. Any reply is appreciated. -- David Yeung, MySQL Senior Support Engineer, Sun Gold Partner. My Blog:http://yueliangdao0608.cublog.cn Comanpy: http://www.actionsky.com --0016e6476110ee6f5e0469dc372e The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
RE: Select Into OUTFILE problem
Hi Bruce, SELECT ... INTO OUTFILE always creates the file local to the database server. If you want to dump results where your perl script is running you'll have to use another method such as receiving the results of the query normally and writing the file in the perl script. Regards, Gavin Towey I have a bit of perl code that ends with an error: $sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC; $sth = $dbh-prepare($sql); $rv = $sth-execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Select Into OUTFILE problem
Thanks all who replied. After I posted I kept looking and found it... Also had folks point it out to me. Your suggestion is what I ended up doing. Bruce Gavin Towey wrote: Hi Bruce, SELECT … INTO OUTFILE always creates the file local to the database server. If you want to dump results where your perl script is running you’ll have to use another method such as receiving the results of the query normally and writing the file in the perl script. Regards, Gavin Towey I have a bit of perl code that ends with an error: $sql=SELECT convert_tz( a.stamp,'GMT','$tz' ) as ts, a.status, a.reason, a.tl INTO OUTFILE '/application/result.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '\' LINES TERMINATED BY '\n' FROM alerts a WHERE a.stamp BETWEEN ? AND ? ORDER BY a.stamp DESC; $sth = $dbh-prepare($sql); $rv = $sth-execute; DBD::mysql::st execute failed: Can't create/write to file '/application/result.csv' (Errcode: 2) Te database is remote from the system where the perl is executing. Te SQL works as expected when fed to mysql command line client (i.e. mysql -h remote). The outfile ends up in the application directory of the macine running the mysql client. What I'd found is, when the perl code runs the file tries to drop on the database server and the application directory doesn't exist there giving me the error. Any suggestions to get the outfile to drop in the right place would be appreciated. Bruce The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MAC address as primary key - BIGINT or CHAR(12)
A MAC address is just a number, it doesn't contain letters unless you're doing something silly like storing the HEX representation of it. Do not use CHAR! This does DOUBLE for all of you storing IP addresses! Since a MAC address is going to be between 48 and 64 bits, then BIGINT is appropriate. When you select the value you can do SELECT HEX(mac) FROM table; to get a more readable version of it. When you're storing values you can do: INSERT INTO table (mac) VALUES (x'FFEEDDCCBBAA9988'); to convert a hex string to the numeric value. Date: Thu, 14 May 2009 09:53:58 -0400 To: Ilia KATZ ik...@dane-elec.co.il From: Fish Kungfu fish.kun...@gmail.com Cc: mysql@lists.mysql.com Subject: Re: MAC address as primary key - BIGINT or CHAR(12) Message-ID: f76e38f90905140653o4f6513aft103e8c3b526b3...@mail.gmail.com --001636e90cddd7f9c70469dfa8fe Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Since MAC addreses also contain letters, BIGINT wouldn't work. So, yes, I would say go with CHAR(12). On May 14, 2009 9:43 AM, Ilia KATZ ik...@dane-elec.co.il wrote: Hi. Currently I have a table: 1. MAC address defined as BIGINT 2. MAC address set as primary key Should I consider changing it to CHAR(12)? Replies will be appreciated. Ilia The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: MAC address as primary key - BIGINT or CHAR(12)
I've run up on the rock of a binary (meaning: indecipherable) field. SELECT hex(some_binary_field) FROM table; Solved. The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Re: Password Reset Not Working
On Thu, May 14, 2009 at 7:06 PM, Carlos Proal carlos.pr...@gmail.com wrote: The machine mysql.unixslut.com is not the same than localhost, right ??, If you only need root access from localhost you can delete the first row (delete from user where user='root' and host='mysql.unixslut.com';) Carlos, Yes, my machine mysql.unixslut.com is localhost / 127.0.0.1/8. They're both the same machine. It's just that I was told MySQL manages connection for users on a local / domain basis so that is the reason for both entries. In my opinion, both entries are valid. I could be wrong. **No, the server has not been hacked** Thanks for your continued support!] - Carlos -- 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 Thu, May 14, 2009 at 7:01 PM, Douglas Nelson douglas.nel...@sun.com wrote: try running the command like this select * from user where user='root' \G Capital G is a must. I did the following: [r...@mysql ~]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [r...@mysql ~]# mysqld_safe --skip-grant-tables [1] 3072 Starting mysqld daemon with databases from /var/lib/mysql [r...@mysql ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. 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 * from user where user='root' \G *** 1. row *** Host: mysql.unixslut.com User: root Password: 6d24bd789879jhs Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 *** 2. row *** Host: 127.0.0.1 User: root Password: 6d24bd789879jhs Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 2 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: How to copy an statement in Mysql console
Blatantly assuming you are using PuTTy because of your question: click-and-drag the mouse to select text. Then, press Shift+Ctrl+C to copy to the clipboard. Press Shift+Ins to insert text back into the console at the current carot-position. Hope that helps.. Walter Heck On Thu, May 14, 2009 at 5:41 PM, jean claude babin babi...@gmail.com wrote: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
