Re: replication problems
If a slave dies, is there any way to determine that state from an
external point of view (ie: via Perl/DBD) so we can write a script of
some kind to either connect to the slave and issue a START SLAVE or
send the last xx lines of the .err log file to our tech staff?
I tried writing a Perl script to run a query of SHOW SLAVE STATUS and
parsing the 30-or-so fields that would otherwise return, but it's
complaining:
DBD::mysql::st execute failed: Access denied; you need the
SUPER,REPLICATION CLIENT privilege for this operation at ./checkslave.pl
line 19.
Snippet of code:
my $dbh_2 =
DBI-connect(dbi:mysql:ouofa:slave2.domain.org,systemcheck,mypasswd,
{ RaiseError = 1, AutoCommit = 0, PrintError = 0 } ) or die
(SLAVE2 MySQL database offline: $!) ;
my $cur_info = $dbh_2-prepare(SHOW SLAVE STATUS) ;
$cur_info-execute() ;
my @row = $cur_info-fetchrow_array() ;
... line 19 would be the -execute() call.
I did this:
GRANT SUPER,REPLICATION CLIENT to 'syscheck'@'mywebserver.domain.org'
IDENTIFIED BY 'mypasswd' ;
... on the slaves, but that didn't help me any.
How can I check a slave's status without having to manually log in to a
mysql client??
-id
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication problems
One master, two slaves, mysql 4.1.7 installed via compiled source code, on RedHat 8.0 On the master system I did this: grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname1' identified by 'repl_passwd' ; grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname2' identified by 'repl_passwd' ; and on the two slaves: CHANGE MASTER TO MASTER_HOST='master.db.hostname', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_passwd' ; ... as per the documentation. On the slaves, the command: LOAD DATA FROM MASTER responds with ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname1' (using password: YES) or ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname2' (using password: YES) Any tips? /etc/my.cnf on the master sets the server-id to 1, the slaves are 2 and 3 respectively -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
Answered my own question, sorry for the quick posting... When I was trying to do the LOAD DATA FROM MASTER, with the master set as GRANT ALL ON *.* TO 'repl_user'@ ... the slaves were reporting an error that stated I needed to GRANT SUPER,REPLICATION CLIENT on the master, which didn't work. After a little more RTFM-surfing, I found the GRANT REPLICATION SLAVE for the master, and a LOAD DATA worked just fine. -id ian douglas wrote: One master, two slaves, mysql 4.1.7 installed via compiled source code, on RedHat 8.0 On the master system I did this: grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname1' identified by 'repl_passwd' ; grant SUPER,REPLICATION CLIENT on *.* to 'repl_user'@'known.hostname2' identified by 'repl_passwd' ; and on the two slaves: CHANGE MASTER TO MASTER_HOST='master.db.hostname', MASTER_PORT=3306, MASTER_USER='repl_user', MASTER_PASSWORD='repl_passwd' ; ... as per the documentation. On the slaves, the command: LOAD DATA FROM MASTER responds with ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname1' (using password: YES) or ERROR 1218 (08S01): Error connecting to master: Access denied for user 'repl_user'@'known.hostname2' (using password: YES) Any tips? /etc/my.cnf on the master sets the server-id to 1, the slaves are 2 and 3 respectively -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication problems
After a little more RTFM-surfing, I found the GRANT REPLICATION SLAVE for the master, and a LOAD DATA worked just fine. Except that changes made on the master are not automatically picked up by the slaves. Am I missing something? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to use except operation?
Id Study keyword 1 AK1 2 AK2 3 BK1 4 BK2 5 CK1 6 Ck3 SELECT DISTINCT Study FROM yourtablename WHERE keyword='K1' AND NOT keyword='K2' That *should* give you a single entry for 'C' since its keywords do not match both 'K1' *and* 'K2' But your posting *was* a little confusing, so perhaps you can elaborate a little further on what it is you're trying to accomplish? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nebiew migrate access tables to mysql
Put Purge into backticks. `Purge` char(1), It worked great but I would like to know why, thank! It could be that 'purge' is a reserved word in MySQL. I wanted to have a table with a shortened name of 'description' by trying to create a table with a 'desc' field, and MySQL had problems with it too, because 'desc' is a reserved word to 'describe' a table definition. But, creating the table as `desc` varchar(20) worked just fine... -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: nebiew migrate access tables to mysql
replied to him privately with this before I realized he'd sent a different copy with the list CC'd: CREATE TABLE if not exists TASK ( `Payment Date` DATE, ID INT AUTO_INCREMENT, CaseNumber CHAR(12) NOT NULL, Payment_Amount FLOAT(8,2), PRIMARY KEY (ID), KEY CaseNumber ( CaseNumber ) ); spiv007 wrote: What about this? I will not take AUTO INCREMENT I tried removing the underscore and putting `AUTO INCREMENT` , but im getting the same error as before. CREATE TABLE if not exists TASK ( `Payment Date` DATE, ID INT AUTO_INCREMENT, CaseNumber CHAR(12) NOT NULL, Payment_Amount FLOAT(8,2), INDEX CaseNumber ( CaseNumber ), INDEX ID ( ID ), INDEX ( ID ) ); On Fri, 05 Nov 2004 10:22:42 -0800, ian douglas [EMAIL PROTECTED] wrote: Put Purge into backticks. `Purge` char(1), It worked great but I would like to know why, thank! It could be that 'purge' is a reserved word in MySQL. I wanted to have a table with a shortened name of 'description' by trying to create a table with a 'desc' field, and MySQL had problems with it too, because 'desc' is a reserved word to 'describe' a table definition. But, creating the table as `desc` varchar(20) worked just fine... -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slashes in update statement
I'm trying to get a slash in a variable into my database and am having some trouble. If the variable = 1 1/2 it echoes to the screen correctly but it seems to strip the 1/2 off the variable when updating the value to the database. Just FYI to everyone replying: Ed replied to me personally admitting that his field type in MySQL was an int() type, which is why it wasn't adding anything after the space. He has since corrected this, so no further need to give him ideas about how to fix it. -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
In my experience, RedHat in their 'wisdom' put mysql.sock in /tmp/ yet ships their mysql-server.rpm with a my.cnf pointing at /var/lib/mysql/mysql.sock Make the necessary change to /etc/my.cnf to point to /tmp/mysql.sock, or change /etc/rc.d/init.d/mysql to point to /var/lib/mysql/mysql.sock -id Greg Wiggill wrote: hi, having a hack with the whole imp, horde, webmail thing have installed the mysql server and client rpms on redhat8 and just trying to get started. /usr/bin/mysqladmin -u root password password /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! ps -ef | grep mysql root 725 1 0 Oct29 ?00:00:00 /bin/sh /usr/bin/mysqld_safe --d mysql 767 725 0 Oct29 ?00:10:44 /usr/sbin/mysqld --basedir=/ --d root 10902 10556 0 11:42 pts/000:00:00 grep mysql must be missing some basic steps? thanks Greg # This email has been scanned by MailMarshal, an email content filter. # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication basics
Hi everyone, I've been browsing the online archives for a while and haven't found as much data as I'd like to feel really comfortable about replication. I've been a pretty busy developer in terms of using MySQL, but never so much in the way of administration. Also, I've only ever had to deal with a single machine until about a month ago when I was asked to extend our database to multiple machines for security and redundancy. As it stands right now, I have 3 machines running MySQL, set up as hosts db1, db2 and db3. db1 is a master db2 and db3 are set as slaves, and replicate data just fine with the premise that we'd like to add numerous slave machines at any time. Problems I'm having: 1. If a new database is created on db1, this is not replicated on db2 and db3. Should my permissions be GRANT FILE on *.* ... or is there a better way to automate duplicating this database on the slaves? 2. If a database exists on all three machines already and I create a table on db1, this is also not replicated on db2 or db3. 3. I haven't checked yet whether ALTER TABLE ... commands have worked across the replication. 4. Darn RedHat and their logrotate utility: db1 was having MySQL restart once a week, which created db1.001, db1.002, db1.003, etc., every time it restarted, yet db2 and db3 also running logrotate and having MySQL restart because of it, were not updating their master.info files to point at the .002 or .003 or .004 files whenever db1 was restarted. This also happens if db1 reboots. 5. In the event of power failure, or system failure, if db1 is offline, how can I set the systems so either db2 or db3 becomes a master? And if db1 comes back online later, could I set it as a slave to whichever other machine became a master? Can this be automated, or will it always require manual intervention? 6a. I rewrote my Perl applications to connect to db1 and return a handle I call $dbh_w for any SELECT/INSERT/UPDATE/DELETE queries, and to connect to any of db1/db2/db3 for any SELECT queries as $dbh_r. However, we want to use some third-party software that contains a few MB of PHP code, and only connects to the database one time - I'm not sure how much time to allocate (to tell my CTO/CEO) to rewrite the entire application the same way I did my Perl applications. Any advice? 6b. My Perl scripts are 90% reading data back from the database, the PHP scripts are more like 80% writing to the database so being able to load-balance the writing to the database farm is ideal. In my current scenario, the PHP application can only write to db1, which will ultimately cap out the machine. Surely there's a better way than making two connections ($dbh_w/$dbh_r) to the database for each copy of my Perl or PHP processes running? 7. Finally, would 'fake replication' work if the MySQL database files were on a RAID system and mounted via NFS to multiple machines to actually run the MySQL engine? The tables we use have a lot of auto_increment fields, and I'd be worried about data being corrupted or lost. Thanks, Ian Douglas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication basics
1. If a new database is created on db1, this is not replicated on db2 and db3. ... is there a better way to automate duplicating this database on the slaves? Yes put in your my.cnf on the master binlog-ignore-db=mysql,test this means the master will replicate any event to any database other then mysql, test. So, by your statement: binlog-ignore-db= ... and leaving it blank, will tell MySQL to replicate EVERY database? That's the behavior that I want. I will not be ignoring any databases on the master - all data on the master (databases, tables, data) must exist on every system, at least for the time being. Later on, we may replicate only certain tables onto certain slaves depending on our user load and which databases are more frequently used. However, for the foreseeable future, all data must exist on all machines. 2. If a database exists on all three machines already and I create a table on db1, this is also not replicated on db2 or db3. Why not? That's my question too. I haven't seen anything on how to allow this behavior yet. I've seen some text about LOAD TABLE tablename FROM MASTER but I need a way to automate this so there is less manual intervention required. Are you explicitly replicating a set of tables from your slaves via replicate-do-table=foo.bar? I need to replicate everything, and I'm not setting any options like this in the slave's my.cnf. Each slave must be a complete mirror copy of the master. 3. I haven't checked yet whether ALTER TABLE ... commands have worked across the replication. Yes they do. To finish my statement: ... in my implementation - that is, I haven't tested my implementation to know if an ALTER TABLE ... query works or not. 4. Darn RedHat and their logrotate utility: ... created db1.001, db1.002, db1.003, etc., ... This also happens if db1 reboots. Stop the logrotate script. Which I did last week when I realized this was why my slaves hadn't sync'd up in a week... But this past weekend a few transformers blew in the neighborhood around our office and a power surge shut off db1 (master) - when it came back online, it created a db1.002 file and updated its own db1.index file, yet the two slave machines did not update their master.info records. How can I automate the slaves to detect this change, and use the db1.002 file? (db2 and db3 were not shut off, they kept running just fine) 5. In the event of power failure, or system failure, if db1 is offline, how can I set the systems so either db2 or db3 becomes a master? write your own load / failover software. Thanks for the pointer. Was hoping there was some sort of round-robin scenario. 6. [paraphrase] Rewriting Perl and PHP code Look at DBI::Multiplex ... anyone know of a PHP implementation of this? It would sure save me some time. 7. Finally, would 'fake replication' work if the MySQL database files were on a RAID system and mounted via NFS No, not reliable. Noted, thank you for saving me some grief. -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect
It doesn't say access denied so I assume the socket is not even open. Never assume :o) From your windows box, you could try: telnet host.domain.com 3306 ... if it connects, it connected to *something* How can I find out if MySQL is on the port 3306? What Linux command shows all active ports TCP or UDP? On the linux box: nmap localhost From your windows box, you could get a port scanner tool from download.com (I use one called SuperScanner) Possible things I'd personally look for: 1. does your linux box have a firewall script running? iptables -V -l 1b. if so, does disabling the firewall script allow access? 1c. if so, it could be that your linux firewall isn't letting you in 2. is the linux box on a private LAN IP (192.168.x.x or 10.x.x.x etc) and behind a NAT box (internet connection sharing router, or other gateway system)? 2b. if so, is port forwarding enabled to allow port 3306 to be forwarded to your mysql box? 3. is mysql *running*? (okay, this should have been question #1...) 3b. if so, can you connect to mysql locally on the linux box? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I export a set of data nightly
There are lots of ways to do it. My personal favorite is to write it in Perl with the DBI library and tell cron to run that Perl script ... your Perl script could then write the data in any format you see fit, even send it somewhere else using Net::FTP or whatever. Just my $0.02... -id Scott Haneda wrote: My query works: (version 4) SELECT u.id, r.user_id, u.first_name, u.middle_name, u.last_name, u.company, u.department, u.address, u.address2, u.city, u.state, u.country, u.zip, u.phone, u.fax, u.email, DATE_FORMAT(u.updated, '%m/%d/%Y'), DATE_FORMAT(u.added, '%m/%d/%Y'), r.serial, r.product, DATE_FORMAT(r.added, '%m/%d/%Y') FROM user as u INNER JOIN registered_serials as r WHERE u.id = r.user_id LIMIT 10 However, I need to run this once a day on schedule via cron, but I am not sure how to feed this statement into mysql from bash, can someone point me to the correct way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql admin clients - But can I edit a column field?
But can I edit a column field? Can I go into a specific column in a specific record and edit it just like I was in a text editor? That's the action I want. None on the products specifically state they can do it(?) phpMyAdmin will allow you to alter a column name within a table, yes. can't speak for other tools though, but phpMyAdmin has been pretty stable for me for the last number of years. -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to local MySQL server through socket'/var/lib/m ysql/mysql.sock'
[EMAIL PROTECTED] root]# ls -l /var/lib/mysql total 20564 -rw-rw 1 mysql mysql25088 Nov 1 14:10 ib_arch_log_00 -rw-rw 1 mysql mysql 10485760 Nov 2 07:39 ibdata1 -rw-rw 1 mysql mysql 5242880 Nov 2 07:39 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Nov 1 14:10 ib_logfile1 drwx--x--x 2 mysql mysql 4096 Feb 23 2004 mysql/ -rw-rw 1 mysql root 7509 Nov 2 07:39 server6.cma-cgm.com.err drwxr-xr-x 2 mysql mysql 4096 Feb 23 2004 test/ Therein lies your problem: you can't connect to /var/lib/mysql/mysql.sock if it doesn't exist :o) RedHat was notorious for putting mysql.sock in /tmp/ ... perhaps check that path to see if the socket file exists there, and do one of the following: (a) symlink it to /var/lib/mysql: ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock -OR- (b) modify your MySQL configuration (check for /etc/my.cnf): under the [mysqld] heading: socket=/var/lib/mysql/mysql.sock ... set that path and filename to wherever your mysql.sock file lives. You could try using: locate mysql.sock ... to find the file for you as well. -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't connect to local MySQL server through socket'/var/lib/m ysql/mysql.sock'
[EMAIL PROTECTED] root]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock Which is fine ... you neglected to say whether or not that fixed your problem? -id -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
