Re: replicate-rewrite-db
On Fri, Apr 4, 2008 at 6:30 AM, Shanmugam, Dhandapani [EMAIL PROTECTED] wrote: Hi, Hi, Any idea wat replicate-rewrite-db does with example.. It takes statements for one database, and rewrites them into another. An example of the syntax would be this line in the my.cnf file of your slave: replicate-rewrite-db=master_db-master_db_foo Any statement on master_db would be replicated to the slave, but then executed on master_db_foo. So, for example: You execute 'update table1 set foo=4 where bar=2;' on the master_db on your replication master. The statement would get replicated down to the replication slave. When the slave is evaluating your replication rules, it will see that the statement should be rewritten to apply to the master_db_foo. Then 'update table1 set foo=4 where bar=2;' is executed on master_db_foo. Hope that helps, Dan
Re: Security overrides in mysql.cnf
Hi, On 3/19/08 3:51 PM, Brown, Charles [EMAIL PROTECTED] wrote: I inherited a mysql server database. Stuff are not documented. My question is: Are there any security work-arounds in mysql. I have access to the cnf file. I need to get in and dump the database. I was told that the cnf file allows security over rides. Please help I have tried mysql -uroot. It didn't work You can start the server so that you skip loading the grant tables. That should let you get in and change the root password. Then you should have access to do what you need to. Instructions are here: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting- permissions-unix -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Im being dumb!
Ack, listen to Nanni not me. Join order doesn't matter, now that I tested some more :). Off to drink more coffee, Dan On 3/6/08 8:45 AM, Dan Rogart [EMAIL PROTECTED] wrote: Hi, On 3/6/08 8:33 AM, roger.maynard [EMAIL PROTECTED] wrote: I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | SELECT a.ID,a.Description1,b.Description2,c.Description3,d.Description4 FROM TableA a INNER JOIN TableB b ON a.id = b.id INNER JOIN TableC b ON a.id = c.id INNER JOIN TableD b ON a.id = d.id Doesn't give me the result What am I doing wrong? Can I do this? You have to do your joins in a chain: A joins to B, B joins to C, C joins to D, and so on. Here's how I made it work in a simple example: mysql create table a (id int, desc1 varchar(255)); Query OK, 0 rows affected (0.13 sec) mysql create table b (id int, desc2 varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql create table c (id int, desc3 varchar(255)); Query OK, 0 rows affected (0.07 sec) mysql create table d (id int, desc4 varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql insert into a values (1, 'foo'); Query OK, 1 row affected (0.13 sec) mysql insert into b values (1, 'bar'); Query OK, 1 row affected (0.00 sec) mysql insert into c values (1, 'fu'); Query OK, 1 row affected (0.00 sec) mysql insert into d values (1, 'br'); Query OK, 1 row affected (0.00 sec) mysql select a.id,a.desc1,b.desc2,c.desc3,d.desc4 from a - join b on a.id = b.id - join c on b.id = c.id - join d on c.id = d.id; +--+---+---+---+---+ | id | desc1 | desc2 | desc3 | desc4 | +--+---+---+---+---+ |1 | foo | bar | fu| br| +--+---+---+---+---+ Hope that helps, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Im being dumb!
Hi, On 3/6/08 8:33 AM, roger.maynard [EMAIL PROTECTED] wrote: I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | SELECT a.ID,a.Description1,b.Description2,c.Description3,d.Description4 FROM TableA a INNER JOIN TableB b ON a.id = b.id INNER JOIN TableC b ON a.id = c.id INNER JOIN TableD b ON a.id = d.id Doesn't give me the result What am I doing wrong? Can I do this? You have to do your joins in a chain: A joins to B, B joins to C, C joins to D, and so on. Here's how I made it work in a simple example: mysql create table a (id int, desc1 varchar(255)); Query OK, 0 rows affected (0.13 sec) mysql create table b (id int, desc2 varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql create table c (id int, desc3 varchar(255)); Query OK, 0 rows affected (0.07 sec) mysql create table d (id int, desc4 varchar(255)); Query OK, 0 rows affected (0.00 sec) mysql insert into a values (1, 'foo'); Query OK, 1 row affected (0.13 sec) mysql insert into b values (1, 'bar'); Query OK, 1 row affected (0.00 sec) mysql insert into c values (1, 'fu'); Query OK, 1 row affected (0.00 sec) mysql insert into d values (1, 'br'); Query OK, 1 row affected (0.00 sec) mysql select a.id,a.desc1,b.desc2,c.desc3,d.desc4 from a - join b on a.id = b.id - join c on b.id = c.id - join d on c.id = d.id; +--+---+---+---+---+ | id | desc1 | desc2 | desc3 | desc4 | +--+---+---+---+---+ |1 | foo | bar | fu| br| +--+---+---+---+---+ Hope that helps, Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Im being dumb!
On 3/6/08 12:09 PM, Tim McDaniel [EMAIL PROTECTED] wrote: On Thu, 6 Mar 2008, Dan Rogart [EMAIL PROTECTED] wrote: On 3/6/08 8:33 AM, roger.maynard [EMAIL PROTECTED] wrote: I got 4 tables: Table A | ID | Description1 | Table B | ID | Description2 | Table C | ID | Description3 | Table D | ID | Description4 | ALL Ids ARE COMMON Values and NONE are MISSING How can I create | ID | Description 1 | Description 2 | Description 3 | Description 4 | ... Here's how I made it work in a simple example: mysql prompts removed to make it easier to copy and paste, and quoting removed for the same reason. Dan Rogart wrote: create table a (id int, desc1 varchar(255)); create table b (id int, desc2 varchar(255)); create table c (id int, desc3 varchar(255)); create table d (id int, desc4 varchar(255)); insert into a values (1, 'foo'); insert into b values (1, 'bar'); insert into c values (1, 'fu'); insert into d values (1, 'br'); select a.id,a.desc1,b.desc2,c.desc3,d.desc4 from a join b on a.id = b.id join c on b.id = c.id join d on c.id = d.id; Is that last SELECT equivalent to my version here? select a.id, a.desc1, b.desc2, c.desc3, d.desc4 from a, b, c, d where a.id = b.id and a.id = c.id and a.id = d.id; I mean: the two versions get the same result -- is one translated into the other / processed exactly the same internally? If so, um, my version is a little shorter. Yep, they're the same. I think the optimizer parses them exactly the same way too (based on how they EXPLAIN). It's just a question of what's a more readable way for you to write joins - with 4 tables involved, for me the query is more comprehensible at a glance by using JOIN statements. Your mileage may vary, of course :). -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password for system user
Hi, On 3/5/08 5:58 AM, Thufir [EMAIL PROTECTED] wrote: On Tue, 04 Mar 2008 08:44:47 -0500, Dan Rogart wrote: You can have a file called .my.cnf in your home directory that stores it. Ah, thanks. I don't have a .my.cnf file in my home directory, but I do have something in /etc which seems to be what I'm after. I can get it working for logging into MySQL as the root db admin but can't add the rails MySQL user so that user thufir can login to MySQL as rails passing the password from /etc/my.cnf (too many pronouns for that to make sense). Some success: [EMAIL PROTECTED] ~ $ [EMAIL PROTECTED] ~ $ [EMAIL PROTECTED] ~ $ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 20 Server version: 5.0.44-log Gentoo Linux mysql-5.0.44 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] ~ $ head /etc/mysql/my.cnf -n 7 # /etc/mysql/my.cnf: The global mysql configuration file. # $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $ # The following options will be passed to all MySQL clients [client] user= root password= password [EMAIL PROTECTED] ~ $ do I need to create a local .my.cnf file? thanks, Thufir /etc/my.cnf sets things globally, so if you put your root password in there then anyone who logs on to that box can just type 'mysql' and log on to your database instance with root privileges. That may or may not be a problem for you. If you want to easily log in as the user 'rails' when you have logged in to the box as thufir, then yes, you should create a local .my.cnf file in ~/thufir with the rails credentials. I think that should do it for you. -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: password for system user
You can have a file called .my.cnf in your home directory that stores it. This page outlines it pretty well: http://www.modwest.com/help/kb6-242.html In your case, you would just want to use the password = 'foo' part of it. -Dan On 3/4/08 4:10 AM, Thufir [EMAIL PROTECTED] wrote: I understand that there's a configuration so that instead of typing: [EMAIL PROTECTED] ~ $ mysql -u root -ppassword that the password (of password) is stored so that whenever this user connects as root the password is automatically passed. Is this possible? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
You should definitely consider getting rid of them, otherwise people can log
in to MySQL from any host with no credentials.
They are created during installation by the mysql_install_db script.
This tells you how to remove them:
http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html
-Dan
On 3/4/08 7:23 AM, Hiep Nguyen [EMAIL PROTECTED] wrote:
On Mon, 3 Mar 2008, Daniel Brown wrote:
On Mon, Mar 3, 2008 at 2:46 PM, Hiep Nguyen [EMAIL PROTECTED] wrote:
mysql select user,host,password from mysql.user;
+--+--+--+
| user | host | password |
+--+--+--+
| root | localhost| |
| root | dev.jss.com | |
| | dev.jss.com | |
| | localhost| |
+--+--+--+
4 rows in set (0.00 sec)
Okay, I wasn't aware that it's all on the same server. Try this:
USE mysql;
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE
User='root' AND host='dev.jss.com' LIMIT 1;
FLUSH PRIVILEGES;
do i have to worry about those don't have user name? what are they use
for? should i delete them???
t. hiep
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change pw
That error occurs when the user has already been dropped - so it's good news :). You can check for users with blank user names and/or blank passwords by querying the mysql.user table: select user,host,password from mysql.user where user = '' or password = ''; Those are the users you should consider dropping or assigning passwords to. Hope that helps, Dan On 3/4/08 9:57 AM, Hiep Nguyen [EMAIL PROTECTED] wrote: On Tue, 4 Mar 2008, Dan Rogart wrote: You should definitely consider getting rid of them, otherwise people can log in to MySQL from any host with no credentials. They are created during installation by the mysql_install_db script. This tells you how to remove them: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html i followed the instruction and typed: mysql DROP USER ''; ERROR 1396 (HY000): Operation DROP USER failed for ''@'%' mysql DROP USER ''@'localhost'; Query OK, 0 rows affected (0.00 sec) and mysql DROP USER ''@'localhost'; ERROR 1396 (HY000): Operation DROP USER failed for ''@'localhost' what's wrong here??? t. hiep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number of rows not constant
Hi, On 2/21/08 11:41 AM, Mike Spreitzer [EMAIL PROTECTED] wrote: I have a table with millions of rows. I am not sure exactly how many rows it has, I get a different answer every time I ask! What's going on here? This DB is used only by me, and only by explicit commands --- I have no background or on-line tasks using the DB. This is the DB that took 2 days to load and another two days to add a column and index. I decided to let that column+index addition to complete, and it has now completed. I am using the GUI administrator tool; in the Catalogs section I select the relevant schema; in the right hand side I select the Tables tab. The listing for my table (I have only the one) says Type=InnoDB, Row Format = Compact, Data Length = 4.56 G, Index Length = 8.55 G, and Update Time is blank. It is the Rows datum that is surprising --- every time I hit Refresh I get a different number under Rows. It varies between 23 million and 28 million. It is not monotonically increasing, nor monotonically decreasing. Sometimes the number of rows goes up, sometimes it goes down. BTW, before the late addition of a column+index, the Rows datum was 27,413,306. I did not notice this Rows variability before adding a column+index --- but probably would not have, I had no reason to Refresh the display repeatedly. I first noticed this Rows variability during the column+index addition. I am running MySQL 5.0.51a-community on RHEL 4 on a 4-processor (as far as Linux is concerned) Intel 32-bit machine, with storage on the only local HDD. I am using MySQL Administrator version 1.2.12, and it also says I am using MySQL Client Version 5.0.30. I am running the admin tool on the same machine as the MySQL server. That machine is otherwise idle. I monitor CPU, network, and disk with Procmeter3, updated every 5 seconds. It usually reports 0% CPU and 0 disk I/Os per period, and suitably low network traffic. Sometimes I get a spike up to 5 disk I/Os in some 5 second period, presumably to some background thing(s) Linux and/or MySQL is doing. When I hit Refresh, I get 22 or 23 disk I/Os and 2% CPU for about 5 seconds. Thanks, Mike It sounds like the GUI Administrator is using SHOW TABLE STATUS to get the row count for you, since that's much faster than a count(*). SHOW TABLE STATUS can only provide an estimate for the innodb row count and it can vary a lot. The first two comments here are particularly apropos: http://mysqldatabaseadministration.blogspot.com/2006/07/where-did-records-go .html -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very slow restore
You might want to check out Baron Schwartz's maatkit: http://maatkit.sourceforge.net/ It has scripts which let you take dumps and do restores using multiple threads. It might help speed things up for you. -Dan On 2/15/08 1:55 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi: I am restoring a 10 million row table using a dump file created via mysqldump. On a very fast server, It finishes in 8 hours. Is it something normal ? I know the alternative to copy file directly but here is not an option . So can I say mysql does not have its own way for high performance backup and restore for large scale apps? Thanks -Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Crashed InnoDB
Have you tried starting mysqld with innodb_force_recovery = x ? (where x = values defined below) http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html That might get you past the corruption that's killing startup. -Dan On 2/13/08 12:32 PM, Bryan Cantwell [EMAIL PROTECTED] wrote: No input on this one? -Original Message- From: Bryan Cantwell [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 12, 2008 11:51 AM To: mysql@lists.mysql.com Subject: Crashed InnoDB We had a power outage, now the mysql wont start at all. Here is the err file output... Any help on how to recover? 080212 11:35:50 mysqld started 080212 11:35:50 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 080212 11:35:50 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 115 2637413615. InnoDB: Doing recovery: scanned up to log sequence number 115 2637626081 080212 11:35:50 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 080212 11:35:51 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=2093056 max_used_connections=0 max_connections=2500 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3012828 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbf3feaf8, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d4205 0x835537c 0x82c8b43 0x82c97dc 0x8294835 0x8295489 0x82851fd 0x82b02cd 0x8203f89 0x834fcb5 0x8388daa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. You are running a statically-linked LinuxThreads binary on an NPTL system. This can result in crashes on some distributions due to LT/NPTL conflicts. You should either build a dynamically-linked binary, or force LinuxThreads to be used with the LD_ASSUME_KERNEL environment variable. Please consult the documentation for your distribution on how to do that. 080212 11:35:51 mysqld ended
Re: Crashed InnoDB
Does it start up in a stable enough state to run a mysqldump of the tables? -Dan On 2/13/08 3:54 PM, Bryan Cantwell [EMAIL PROTECTED] wrote: I can get mysql to start with that but still complains about corruption If I try to do optimize table for instance, it crashes again I get this now: 080213 14:32:16 InnoDB: Error: page 4246078 log sequence number 53 188440667 InnoDB: is in the future! Current system log sequence number 0 10477. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: for more information. InnoDB: Dump of the tablespace extent descriptor: len 40; hex 00010040caee0004aa fe; asc @ ; InnoDB: Serious error! InnoDB is trying to free page 4246077 InnoDB: though it is already marked as free in the tablespace! InnoDB: The tablespace free space info is corrupt. InnoDB: You may need to dump your InnoDB tables and recreate the whole InnoDB: database! InnoDB: Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. 080213 14:32:16InnoDB: Assertion failure in thread 163851 in file fsp0fsp.c line 2980 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. 080213 14:32:16 - mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=1073741824 read_buffer_size=2093056 max_used_connections=1 max_connections=2500 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 4061404 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0xac68930 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe5f9f88, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x80d4205 0x835537c 0x829e8ca 0x8220478 0x829e2c1 0x829e5b1 0x824d6d9 0x8208702 0x821c16a 0x823077e 0x819f81c 0x81a00d7 0x8193cea 0x8178a32 0x81acb2b 0x81ae855 0x81b0787 0x81b1282 0x81b19f8 0x80f16ea 0x80f359a 0x80f46cb 0x80f5747 0x834fcb5 0x8388daa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0xaca10c8 = optimize table hosts thd-thread_id=2 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. You are running a statically-linked LinuxThreads binary on an NPTL system. This can result in crashes on some distributions due to LT/NPTL conflicts. You should either build a dynamically-linked binary, or force LinuxThreads to be used with the LD_ASSUME_KERNEL environment variable. Please consult the documentation for your distribution on how to do that. Number of processes running now: 0 From: Dan Rogart [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 12:27 PM To: Bryan Cantwell; mysql list Subject: Re: Crashed InnoDB Have you tried starting mysqld with innodb_force_recovery = x ? (where x = values defined below) http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html That might get you past the corruption that's killing startup. -Dan On 2/13/08 12:32 PM, Bryan Cantwell [EMAIL PROTECTED] wrote: No input on this one? -Original Message- From: Bryan Cantwell [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 12, 2008 11:51 AM To: mysql@lists.mysql.com Subject: Crashed InnoDB We had a power outage, now the mysql wont start at all. Here is the err file output... Any help on how to recover? 080212 11:35:50 mysqld started 080212 11:35:50 InnoDB: Database was not shut
Re: MySQL - Replication (Master/Slave) Question
On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote: Correction to a couple of replies I've seen -- a slave server can have more than one master, but not to the same database. That is, Slave reads Database1 and Database3 from Master1 and also reads Database2 from Master2. You may actually be able to get down to the table level, but I'd have to check on that. Not likely, though. As for how to set it all up, don't ask me. I just enjoy the results. :) (apologies if you get a dupe, Baron -- I accidentally hit reply, not reply-to-all) I would be very interested in hearing more about how you set this up, because as far as I know it's impossible for a slave to have more than one master at any given time. Are you using some kind of time based rotation that changes the master info on the slave periodically or something? -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving space disk (innodb)
OPTIMIZE TABLE should reclaim that space, but be aware that it could take a while to run (locking your table all the while) since it just maps to an ALTER TABLE statement which creates a new copy of the table. Depends on how big your tables are. Doc: http://dev.mysql.com/doc/refman/4.1/en/optimize-table.html -Dan -Original Message- From: Tiago Cruz [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 10, 2007 2:14 PM To: mysql@lists.mysql.com Subject: Saving space disk (innodb) Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? set-variable= innodb_buffer_pool_size=500M set-variable= innodb_additional_mem_pool_size=100M set-variable= innodb_log_files_in_group=5 set-variable= innodb_log_file_size=150M set-variable= innodb_log_buffer_size=8M set-variable= innodb_flush_log_at_trx_commit=1 set-variable= innodb_lock_wait_timeout=5 set-variable= innodb_data_home_dir=/dbms/mysql/bin-4.0.17/var set-variable= innodb_data_file_path=ibdata1:1000M:autoextend set-variable= innodb_log_group_home_dir=/dbms/mysql/bin-4.0.17/var Thanks! -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving space disk (innodb)
So, just to clarify: optimize table just defragments the index? Apologies, I misinterpreted the documentation then. Thanks, Dan -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 10, 2007 3:05 PM To: Tiago Cruz Cc: Eric Frazier; Dan Rogart; mysql@lists.mysql.com Subject: Re: Saving space disk (innodb) Hi Tiago, Tiago Cruz wrote: Thank you guys!! I have a lot of MyISAM and a lot of InnoDB on this database. I did one little for to run one OPTIMIZE TABLE in each table that I have, on my database. If this step don't save enough disk space, I'll do the Baron suggestion. It will not shrink your InnoDB files a single byte :-) If you're trying to shrink those, it's a waste of time. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with repeated replication corruption - Could not parse relay log event entry
Frank- I've had the exact same issue crop up in our prod servers - it was very frustrating, as it was intermittent and would affect some of our slaves, but not all. We had a lot of back and forth with MySQL support without really being able to consistently pin down or reproduce the issue. Ultimately we had to perform a RESET SLAVE (back up those relay logs first, just in case) and reset the master log position (see Baron's CHANGE MASTER TO statements below) to the point where it failed. We also increased the max allowed packet size on both the master and the slave, as it seemed like the issue was occurring with large transactions or large single rows the most. Doing those two things seemed to fix the issue. Baron's checksum script is excellent and I can recommend using it if you haven't rolled your own script. One caveat is that if your slave is using different engine types (to use full text indexes, say), I think the checksums are different. But you may not have that problem. I believe your assertion about temp tables is correct, according to this: http://dev.mysql.com/doc/refman/5.0/en/reset-slave.html Good luck, Dan -Original Message- From: Frank Bottone [mailto:[EMAIL PROTECTED] Sent: Monday, October 08, 2007 3:49 PM To: Baron Schwartz Cc: mysql@lists.mysql.com Subject: Re: Problem with repeated replication corruption - Could not parse relay log event entry Baron, Thanks for the quick response. I do have the binlogs still on the master, so I should be able to do that - however I saw a post somewhere (lost the link at this time) saying that resetting the slave will drop any temporary tables which could cause issues. I'm not sure at this point if that would affect me or not. It is definitely worth a shot I guess, since worst case I will still need to resync from the master. I will try this and give the checksum tool a try as well (although, I think I might have crippled myself from the earlier issues we've been having. They only occurred in a spam-related table and we were able to prove out that the messages were clearly spam and could be left out of the slave/backup by just skipping that transaction. The issue was happening frequently enough that digging through the binlogs to get the query to manually replicate became more effort than it was worth, so the systems might be slightly out of sync. Perhaps I can just ignore the checksum differences for that particular table... I'll let you know the results. Thanks, Frank Baron Schwartz wrote: Frank, Frank Bottone wrote: I've been having trouble with my master/slave server - recently I was having a few repeated issues where the mysql slave would stop due to invalid sql syntax, but the queries executed fine on the master. I would have to manually dig through the logs and then find the query to manually execute on the slave, then use skip_counter to resume the replication skipping the corrupted statement on the slave. I thought it might be hardware related since it was only affecting the slave, so I moved it to a different blade (both the servers are blades). However, today I was greeted with a nagios alert that the slave had stopped again. This time, it seems like the relay log is definitely corrupt. I was able to run mysqlbinlog /dev/null on all the master logs, none are corrupt (including the one it had read up to on the slave). The relay log on the slave is though - it reports [EMAIL PROTECTED] mysql]# mysqlbinlog mysql02-relay-bin.010923 /dev/null ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 38210134, event_type: 0 Could not read entry at offset 618730:Error in log format or read error _Nothing too much different in the logs either: _071006 11:18:52 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED] 4:3306', replication started in log 'mysql-bin.000104' at position 906124600 071008 9:07:12 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013) 071008 9:07:13 [Note] Slave I/O thread: Failed reading log event, ... snip ... their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 0 071008 12:15:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'mysql-bin.000105' position 893425700 Any help or ideas tracking this down would be appreciated - I think we are going to have to take down the production database to resync the two and get replication going again. We mainly use the replica for backup purposes in order to avoid downtime during the backup and in the event of a hardware issue with the master. No need to take down the master or re-initialize the slave, given what I've seen so far. Just tell the slave to throw away its relay logs and re-fetch from the master. From the output you showed, CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000105',
