Question regards mysqldump and replication
Hi. I have a question regarding mysql replication and mysqldump. I have a master (A). All my clients insert/update/delete only to this master. Then I have a Slave (B). This slave only replicates the master. There are no other processes changing/inserting data into the Slave. The slave also logs binlog so I could replicate from that server as well. Now I want a chained Slave ( like A - B - C , C being the chained slave). So my idea is: stop replication on B so no changes during dump, dump its master status, mysqldump all databases. Then load the dump on C and configure slave on C according to the master status from B. I did that and end up in hundreds of duplicate key errors. How can that be? What should I do now? Do I need to wait for some settling after I have stop slave on B and before starting the actual mysqldump? Mysql Version is 5.0.51b on A and B, 5.0.77 on C, operating system is linux. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Bug?: mysqldump for view definitions
Hi I have a testview defined as mysql create table testview (a int); Query OK, 0 rows affected (0.01 sec) mysql create view view_of_testview as (select * from testview); Query OK, 0 rows affected (0.00 sec) I create a dump of this view definition with mysqldump --tab=/tmp test view_of_testview The resulting /tmp/view_of_testview.sql is: ## -- MySQL dump 10.11 -- -- Host: localhostDatabase: test -- -- -- Server version 5.0.51b-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Final view structure for view `view_of_testview` -- /*!50001 DROP TABLE `view_of_testview`*/; /*!50001 DROP VIEW IF EXISTS `view_of_testview`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 [EMAIL PROTECTED] SQL SECURITY DEFINER */ /*!50001 VIEW `view_of_testview` AS (select `testview`.`a` AS `a` from `testview`) */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-07-29 12:38:32 ## If I drop the view and try to reload this dump with mysql test /tmp/view_of_testview.sql I get ERROR 1051 (42S02) at line 20: Unknown table 'view_of_testview' and the view is not created. However, if I create the dump using mysqldump test view_of_testview /tmp/view_of_testview.sql2 I get this dump: # -- MySQL dump 10.11 -- -- Host: localhostDatabase: test -- -- -- Server version 5.0.51b-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Temporary table structure for view `view_of_testview` -- DROP TABLE IF EXISTS `view_of_testview`; /*!50001 DROP VIEW IF EXISTS `view_of_testview`*/; /*!50001 CREATE TABLE `view_of_testview` ( `a` int(11) ) */; -- -- Final view structure for view `view_of_testview` -- /*!50001 DROP TABLE `view_of_testview`*/; /*!50001 DROP VIEW IF EXISTS `view_of_testview`*/; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 [EMAIL PROTECTED] SQL SECURITY DEFINER */ /*!50001 VIEW `view_of_testview` AS (select `testview`.`a` AS `a` from `testview`) */; /*!40103 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40014 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40101 SET [EMAIL PROTECTED] */; /*!40111 SET [EMAIL PROTECTED] */; -- Dump completed on 2008-07-29 12:39:39 # Notice the Temporary table structure lines! Loading this dump works fine, but I think that it does not work --tab might be a bug. Right now, my backup script needs to see whether a table is actually a table or a view in order to make appropriate/useable dumps. My mysql version is 5.0.51b on Linux 2.6.22 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb_buffer_pool_size on 16 GB machine
Unfortunately, right now I can't. Someone mentioned getting rid of myisam configuration values might help. Which ones would that be? Regards Dominik Xuekun Hu wrote: I can alloc 14GB to innodb_buffer_pool_size, even 15GB on my 16GB system. However I used mysql6.0, not mysql5.0. Maybe you can try newer mysql version. Thx, Xuekun -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb_buffer_pool_size on 16 GB machine
Hi I installed a 64bit Linux and compiled and installed 5.0.51b. This is to be an innodb only system. The machine has 16 GB of memory and I can see all of that with free. Except for mysql, there is nothing running on that system. free -m total used free sharedbuffers cached Mem: 16071182 15888 0 10 51 -/+ buffers/cache:120 15951 Swap: 2055 0 2055 So according to the performance blog and the mysql manual, I tried to configure innodb_buffer_pool_size to 14 GB. But mysql does not start and reports 080724 13:29:30 mysqld started 080724 13:29:30 InnoDB: Error: cannot allocate 652288000 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 14917625712 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Note that in most 32-bit computers the process InnoDB: memory space is limited to 2 GB or 4 GB. InnoDB: We keep retrying the allocation for 60 seconds... Lowering the configuration value in 1GB steps to 11 GB starts the server up okay, but I guess I could use more. What's going on? Here's my.cnf [client] port= 3306 socket = /tmp/mysql-test.sock [mysqld] port= 3306 socket = /tmp/mysql-test.sock skip-locking key_buffer = 10M max_allowed_packet = 128M table_cache = 256 sort_buffer_size = 4M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M auto_increment_increment=2 auto_increment_offset=1 skip-name-resolve max_connections=600 old-passwords default-storage-engine=innodb default-table-type=innodb log-bin=binlog/mysql-bin log-slave-updates relay-log=JBOSS03-relay-bin long-query-time=1 log-slow-queries log-queries-not-using-indexes server-id = 5012 innodb_buffer_pool_size = 11000M innodb_additional_mem_pool_size = 200M innodb_log_file_size = 1000M innodb_log_buffer_size = 24M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_file_per_table innodb_flush_method=O_DIRECT [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql on 32 bit pae linux with 16 GB of memory
Hi I have a new machine to put mysql onto. It has 16 GB of RAM. I don't have exactly the best experience with 64bit operating system, so I'd personally like to stick to 32 bit. Does it make sense to install it with a 32 bit PAE kernel? Will mysql be able to use the full amount of memory? From what I read, even PAE kernels still limit threads to only use a maximum of 4 GB of memory. In an innodb only setup - do I need 64bit OS to use all RAM? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT [solved] Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Moon's Father schrieb: You can manually restart the slave process. Sure I can. But that's not sufficient. For now, I scripted some log watch thing that re-starts the slave in the particular situation. But this is not good. On Wed, May 21, 2008 at 9:11 PM, Dominik Klein [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Hello mysql list I posted this problem to the list earlier this month: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5) I was then told to upgrade to the newest version, which I did and which seemed to solve the problem. Today, I got this in my log: 080521 14:18:22 [ERROR] Error reading packet from server: Out of memory (Needed 2848 bytes) ( server_errno=5) 080521 14:18:22 [ERROR] Stopping slave I/O thread due to out-of-memory error from master Now, not only does it report an error, it also stops the slave process instead of auto re-starting it as it did in version 5.0.45 (which I used before). So all slave machines do not replicate until I manually start the slave again. What can I do about this problem? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NOT [solved] Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Hello mysql list I posted this problem to the list earlier this month: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5) I was then told to upgrade to the newest version, which I did and which seemed to solve the problem. Today, I got this in my log: 080521 14:18:22 [ERROR] Error reading packet from server: Out of memory (Needed 2848 bytes) ( server_errno=5) 080521 14:18:22 [ERROR] Stopping slave I/O thread due to out-of-memory error from master Now, not only does it report an error, it also stops the slave process instead of auto re-starting it as it did in version 5.0.45 (which I used before). So all slave machines do not replicate until I manually start the slave again. What can I do about this problem? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[solved] Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Upgrade to 5.0.51b seems to have solved the problem. On wednesday, I saw the problem about a dozen times during a peak time. Upgraded wednesday night and have not seen the error since. Thanks Dominik Juan Eduardo Moreno wrote: Hi, This error occur when slave servers could incorrectly interpret an out-of-memory error from the master and reconnect using the wrong binary log position. This was fix in 5.0.48 version. Please, try to update your version ( from 5.0.45) of mysql and try again. regards, Juan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug: describe table show default NULL, but that's not true
Hi I upgraded from 5.0.45 to 5.0.51b yesterday. After installing the files, I just copied the var directory and started the database. mysql show create table cc\G *** 1. row *** Table: cc Create Table: CREATE TABLE `cc` ( `id` varchar(255) collate latin1_german1_ci NOT NULL, `max` int(11) NOT NULL, `available` int(11) NOT NULL, `version` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci 1 row in set (0.00 sec) mysql desc cc; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| varchar(255) | NO | PRI | NULL| | | max | int(11) | NO | | NULL| | | available | int(11) | NO | | NULL| | | version | int(11) | NO | | NULL| | +---+--+--+-+-+---+ 4 rows in set (0.00 sec) mysql delete from cc; Query OK, 3 rows affected (0.00 sec) mysql insert into cc values(asd,30,50,123); Query OK, 1 row affected (0.00 sec) mysql insert into cc (id,max) values(asdf,30); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql select * from cc; +--+-+---+-+ | id | max | available | version | +--+-+---+-+ | asd | 30 |50 | 123 | | asdf | 30 | 0 | 0 | +--+-+---+-+ 2 rows in set (0.00 sec) That's kind of misleading and feels inconsistent. Should I file a bug about this? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Francisco Rodrigo Cortinas Maseda wrote: Hi, I have experienced similar problems to the one you have; the problem you have is that the time gap between the failure and now is so big that you cannot resume replication, because of the big data portion you have to replicate. Well, that was yesterday - so I guess I could. I see the binlog ids where replication stopped and resumed. They match every time. How would I find out that replication resumed at a wrong position? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Hi in a 5.0.45 linux master-master replication setup, I see the error message from the subject every now and then. Sometimes it does not happen for a couple of months, then it happens a couple of times a day. I cannot see any network problems otherwise, and a memtest did not bring up any memory problems. It feels like this happens on a rather high load on the system, as it never happened at night. What can I do about that? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Hi. Juan Eduardo Moreno wrote: Sorry, what is your setting for max_allowed_packet variable? mysql show variables like %allowed%; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16776192 | ++--+ 1 row in set (0.00 sec) Here's an excerpt from the logs. It does not look like that value is involved in this: 080428 13:07:04 [ERROR] Error reading packet from server: Out of memory (Needed 2704 bytes) ( server_errno=5) 080428 13:07:04 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02bin.000131' position 447892967 080428 13:07:04 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 447892967 080428 13:22:14 [ERROR] Error reading packet from server: Out of memory (Needed 2704 bytes) ( server_errno=5) 080428 13:22:14 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 471157588 080428 13:22:14 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 471157588 080428 14:38:06 [ERROR] Error reading packet from server: Out of memory (Needed 3464 bytes) ( server_errno=5) 080428 14:38:06 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 583864714 080428 14:38:06 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 583864714 080428 15:13:24 [ERROR] Error reading packet from server: Out of memory (Needed 3128 bytes) ( server_errno=5) 080428 15:13:24 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 635409380 080428 15:13:24 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 635409380 080428 15:32:14 [ERROR] Error reading packet from server: Out of memory (Needed 2688 bytes) ( server_errno=5) 080428 15:32:14 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 665146758 080428 15:32:14 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 665146758 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug: drop view breaks replication
Hi I use mysql-5.0.45 on opensuse 10.2 linux x86. The following reproducably breaks replication: node A is master node B is slave A mysql use anydb; A mysql drop view asdasdasd; ERROR 1051 (42S02): Unknown table 'asdasdasd' The name of the view does not matter. It does not exist, so mysql gives an error, but the statement makes it to the slave and breaks replication there, as it does not succeed there either. Any hints? If you need additional information, let me know, I'll be happy to supply it. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
safe-updates error in replication
Hi when I execute mysql show slave status\G I see this: ... Last_Errno: 1175 Last_Error: Error 'You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column' on query. Default database: 'xxx'. Query: 'delete from xxx' ... However, safe-updates is not configured in my.cnf and I also think this is a CLIENT SESSION variable. How can I disable safe-updates for the slave-thread (if it should be configured). What else could be the reason? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[solved] Re: safe-updates error in replication
This helped: http://forums.mysql.com/read.php?26,133157,136626 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A 'Simple' Protocol for Manual MySQL Slave Promotion to Master
Here's another howto on this using keepalived instead: http://www.austintek.com/LVS/LVS-HOWTO/HOWTO/LVS-HOWTO.failover.html#ha_mysql Kishore Jalleda schrieb: HB, MON, IPFail would work well for this , here is some info from my website that I wrote very long ago, hope it helps http://kjalleda.googlepages.com/mysqlfailover http://kjalleda.googlepages.com/automatedmasterfailoverinmysql Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use Samba Share For Data Directory
Michael Stearne schrieb: We have 5.0.27 installed on a CentOS machine that doesn't have a ton of disk space. Is it possible to point the data directory to lie on a samba connected share? The samba share does not support Unix file permissions so it is not possible to set mysql as the owner of the files. Is this possible at all? If you use proper mount-options, you can set the owner of the files. mount -t smbfs -o uid=mysql $SHARE $DESTINATION In general: This should not be a problem, but it will be slow as the network is propably slower than your local disc. Guess you knew that. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to rename database name
1. 'mysqldump' the current database, drop it and create a new database. Move the dumped data into the new database. This should work with any engine. 2. Stop mysqld. Rename the database folder in the datadirectory. Start the server and grant access permissions This does imho only work for myisam databases. It does definetly not work for innodb databases. I do not work with other engines so I dont know about them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Renaming the root user - problems.
[EMAIL PROTECTED] schrieb: Hello, my question refers to the user root in MySQL 5.0.22. Is the standard MySQL root user really required with the name root or can I rename the root user for example to myroot? Our software vendor affirms that MySQL need the User root always but I argue the convers. The application of this vendor doesn't work by renaming the root user to myroot. In my opinion the application causes the fault and not the MySQL DBMS. Who is right? Well the root user in mysql has nothing special to it. It is just an account with all privileges plus the grant option. So grants those privileges to an account of your choice, revoke the non-necessary privileges from root and let them use the root account ... Might be a good idea to put this into your documentation, though :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Problems With Two Tables With Over 500K Rows
My innodb_buffer_pool_size is: innodb_buffer_pool_size | 8388608 That looks like 8MB... that sounds small if I have a DB with over 1M rows to process. No? Yes, that's extremely small. I'd go for at least 256M, and maybe 512M if your machine will primarily be doing mysql duties. Did you do this yet? This should speed it up imho. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Master Switch (Or Write by SQL_THREAD only)
Is there a way to allow the SQL_THREAD to write while holding everything else ? iptables -A INPUT -p tcp --dport 3306 -s MASTER_IP -j ACCEPT iptables -A INPUT -p tcp --dport 3306 -j REJECT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
See how far the slave has replicated from master
Hi is there a way to see how far the slave has replicated from the master machine? I know I can issue show slave status on slave, but is there any command on the MASTER to see information on what the slave has read already? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How many databases does MySQL 5 support?
John M.Brown schrieb: Thanks for the info, but my question is how many databases, not so much how many rows per table or how big the database can be... I mean, how many create database ABCinsert # here can I do before MySQL says sorry, you can't have more than X databases. Say I create 1000 empty MySQL databases (meaning no tables, just the schema)... would that work? what about 5000? ... make sense? I just test-created 10.000 dbs on a cheap celeron 2400 ide hdd testserver with 256 megs of ram and it was no problem at all. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get into mysql command line?
The error message I receive is the following: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) This message indicates you tried to login without giving a password. Add -p to your commandline. It will then prompt you for your password and log you in if the password is correct. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get into mysql command line?
Unfortunately, that is not the reason, why I get this message. I have tried 'mysql', 'mysql -uroot', mysql -u root', 'mysql -uroot -p', 'mysql -uroot -pMY_PASSWORD', but anything fails. When I am using the password option, the error message is like this: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) Any ideas? Did you restart the server after you set the password? Did you execute flush privileges? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table status for innodb tables show innodb free 2 times
I recently deleted about 7.000.000 rows from a table, there are about 4.000.000 left. So I want to know how much space is free in table space now and execute: mysql show table status like table\G *** 1. row *** Name: table Engine: InnoDB Version: 10 Row_format: Compact Rows: 4354196 Avg_row_length: 210 Data_length: 917536768 Max_data_length: 0 Index_length: 2294349824 Data_free: 0 Auto_increment: 35040856 Create_time: 2006-10-12 10:29:36 Update_time: NULL Check_time: NULL Collation: latin1_german1_ci Checksum: NULL Create_options: Comment: InnoDB free: 6144 kB; InnoDB free: 1762304 kB 1 row in set (0,26 sec) Why does it show two values for InnoDB free? Which one is correct? I use MySQL 5.0.21 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
what does SHOW TABLE STATUS show for other tables? It shows 2 values for about 3 of 260 tables. So most tables are okay. It does not seem to depend on table size, as the other tables only have a few hundred rows. Are you using innodb_file_per_table? Yes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table status for innodb tables show innodb free 2 times
Heikki thanks for filing that report. You can close it again. I had a look at the create-table statements for these 3 tables. As it turns out, the person who initially created those tables had a create statement like create table ... comment='InnoDB free: 6144 kB' for some tables. All my (well, his, I did not create these tables ... ) fault, sorry to have wasted your time. Regards Dominik Heikki Tuuri schrieb: Dominik, I have now filed: http://bugs.mysql.com/bug.php?id=23211 about this. Is there any pattern that could explain why the double print is only in those 3 tables? What values does it print for the tables where the printout is wrong, and what values does it print for ok tables? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speed up index creation on existing tables?
Sounds like a very nice idea to me, but this could be a problem if temp tables need to be bigger than RAM+Swap, which could easily be the case in a table with 100,000,000 rows. Gabriel PREDA schrieb: For this table this is to late... leave it running... If you want to do this on another table(s)... and in general on huge loaded MySQL servers I recomend the following... Create a directory let's say /mnt/mem_fs Mount in it /dev/shm use tmpfs as filesystem... Now you have a directory that stores all the info in memory... if the available alocated memory in consumed then it will start swaping... but compute all values so that it dosen't... In my.cnf set a MySQL directive like: tmpdir = /mnt/mem_fs This way MySQL will create temporary tables in memory rather than creating them on disk !!! I'm pretty sure you can figure out the speed improvment ! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Balancer for mysql
Is there a hardware/software loadbalancer for multiple mysql servers servicing read transactions? You might want to take a look at www.linuxvirtualserver.org and www.keepalived.org Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I set many IP address with bind-address ? If not, how can do same thing ?
You can only specify one IP address to bind to. If you omit this option, mysqld will bind to all addresses on the machine. If this is not what you want, you could block mysql-access with a packet filter for the IP addresses you do not want to bind to. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Langsames Insert, Optimierung
Diese Zeit muss verringert werden ~ 3 Sekunden wären noch akzeptabel. Idee o. MySQL Optimierungen? Du kannst versuchen, alle Zeilen in einem Statement einzufügen. also so etwa: insert into test values (1),(2),(3),...,(n); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to sort last n entries?
I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
Chris Sansom schrieb: At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n Last n entries means I want the last (highest) n ids. And that result sorted by date. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to get size of biggest blob (for max_allowed_packet)?
For adjusting max allowed packet value, I need to know the maximum size of my blob fields. How can I get that? Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql privileges
* now i can access with [EMAIL PROTECTED] but i can't create databases What does show grants display when you login as root? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication shattered
The problem is now the slave is saying there are duplicate key entries. Im not sure how this is possible. Any thoughts ? Your procedure looks right. Do you use innodb tables? Otherwise, single-transaction does not work as you expect. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: write-protection for some tables while other are writable
Is there a possibility to have some tables write-protected while others in the same db are not (and yet the write-protected ones are updatable through the replication mechanism, ie. there are tables on a slave server). I guess that both, LOCK TABLES and read-only in my.cnf, don't get this result. Set proper privileges for your user accounts. This will not affect replication, so replication will continue to write to that table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can a table be copied across the DBs?
In the destination database the table doesn't exist. Please let me know, if there is any way to do it. CREATE TABLE DB2.tblname LIKE DB1.tblname; INSERT INTO DB2.tblname SELECT * FROM DB1.tblname; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing engines, MyISAM to InnoDB Heelp
most of my Storage enines is MyISAM i wanna change them all to|InnoDB how do i do this ? ALTER TABLE tblname ENGINE=innodb; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow log logs non-slow statements
I have specified log-slow-queries long-query-time=10 in my.cnf and restarted my server. After that I see statements logged into the slow-log-file. But it seems that mysql logs too much into that file. When executing this statement: mysql show variables like %tx%; +---+-+ | Variable_name | Value | +---+-+ | tx_isolation | REPEATABLE-READ | +---+-+ 1 row in set (0,00 sec) it immediately shows up in the slow-log: # Time: 060815 14:40:22 # [EMAIL PROTECTED]: root[root] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 1 show variables like %tx%; This is also true vor simple select statements which give a result in (0,00 sec). How can I make mysql log only those slow queries, that are really slow. Thanks in advance Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 5: Error 1130: host X is not allowed to connect to this mysql ....
Simo Sentissi schrieb: Hello there I just finished installing mysql 5 on my linux server and I reset the root password as an initial post-install setting. now that i try to connnect from other machines in the network I always get a message similar to the following: C:\Documents and Settings\msentissimysql -u root -h 192.168.3.60 ERROR 1130 (0): Host '192.168.3.10' is not allowed to connect to this MySQL server I looked for the my.cnf file and it is nowhere to be found ? some guidance please. Thanks Simo Sentissi Collaboration Networks 406-579-8256 The password you changed only gives you local access. Log in to your linux server via SSH or directly at the machine. Then log in to mysql as root with your given password (mysql -u root -p). Then execute something like this: GRANT ALL PRIVILEGES ON *.* TO msentissi@192.168.3.10 identified by yourpassword; This will allow you to login from your Windows machine. You may also want to add with grant option to the line above, which will give you full administrator privileges on the mysql server. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump error
I am getting the below error [EMAIL PROTECTED] drupal]# mysqldump --tab=/usr/local/drupal/drupalbckup/ --opt drupal mysqldump: Got error: 1: Can't create/write to file '/usr/local/drupal/drupalbckup/access.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Change the owner of that directory so the mysql server can write into it. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump error
[EMAIL PROTECTED] drupal]# mysqldump --tab=/usr/local/drupal/drupalbckup/ --opt drupal mysqldump: Got error: 1: Can't create/write to file '/usr/local/drupal/drupalbckup/access.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE' Change the owner of that directory so the mysql server can write into it. Regards Dominik Thanks it did worked but i want it to dump on a single file for example backup.sql, as i could see lot of file Please write to the list next time. If you want a single file use mysqldump [your options] [your database] backup.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to disable foreign_key_checks when using mysqlimport?
Gabriel PREDA schrieb: Try: ALTER TABLE `tbl_name` DISABLE KEYS; -- now insert in the TXT file ALTER TABLE `tbl_name` ENABLE KEYS; I think this is what you were looking for ! That would have been a possibility. I did it this way now: ... echo set sql_log_bin=0; set foreign_key_checks=0; use $db; load data infile \$txtfilename\ into table $tablename fields enclosed by '\';| $MYSQL_BIN $MYSQL_CONNECT ... Script performs several checks before this statement and puts the txtfile in the appropriate directory. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to disable foreign_key_checks when using mysqlimport?
mysqldump -u [user] -h [host] -p [database] [table] fixme.sql This is for one table. As I need it for all my tables in all my databases, I'd have to write a script for that. And as --tab uses less space, I prefer --tab option for mysqldump. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to grant replication slave/client to class c
Michael M. schrieb: I'm attempting to take a brand new mysql server build on gentoo and set up replication. I'm using GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@192.168.1.0/255.255.255.0 IDENTIFIED BY 'secret'; I'm not sure if this is correct syntax. Use the specific IP address instead and execute show grants for repl@ip Do this for both machines in your multimaster setup (replace the ip of course) Now, what I'll eventually be setting up is a master-master replication (basically only for failover using keepalived), so if anyone has any experience with that, I'd be much appreciated. I set up such a system and wrote a little howto on that in the LVS documentation: http://www.austintek.com/LVS/LVS-HOWTO/HOWTO/LVS-HOWTO.failover.html#ha_mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with synchronisation master - slave
Hi Thomas I try to configure replication from master to one slave for a database with various table types (InnoDB, MyISAM) without stopping the master. Therefore I make a mysqldump on the slave from master with this options: mysqldump -h master-db \ -udummyuser \ -pdummypass \ -v \ --all-databases \ --disable-keys \ --quick \ --single-transaction \ Single-transaction only affects innodb tables. As single-transaction sets --skip-lock-tables automatically, MyISAM-tables are not locked during the dump. I think this is why you get the row exists already problem. I'd suggest dumping innodb data and myisam data separatly. One mysqldump WITH single-transaction (for innodb) and one without. This leaves the problem of data being inserted in the meantime, as master position will vary. No idea at hand right now :( --master-data \ backup.file an read the dump to the slave with: mysql \ -u dummyuser \ -pdummypass \ backup.file When I take a look to the backup.file, I see a line like this: CHANGE MASTER TO MASTER_LOG_FILE='webdb1-bin.000170', MASTER_LOG_POS=151635461; caused by the option --master-data. But when I start the slave, there are soon the error message, that the replication process will insert new row to a table, where this row exists already. Next try was, to delete all data in slave and then start replication from the master from the beginning on (master-bin.01), because we haven't deleted any binlogs on master. After some time there appears an error message, that there was an unsuccessful insert to an table, which doesn't exist. Also the schema/catalog for this new table doesn't exist. The question is now, why the creation of the new schema/catalog and the table wasn't logged in the binlogs, so that they are not created via replication before some inserts/updates are processed on them. Maybe someone disabled log-bin temporarily when creating the db/table (for tests or whatever) and forgot about this when inserting data later. Are the binlogs not consistent? By the way, the problem tables are of type MyISAM. Problem tables = binlogged, but non-existent tables? or Problem tables = row exists problem-tables? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to disable foreign_key_checks when using mysqlimport?
Hello When I re-insert dumped data with mysql file.sql, I can simply put set foreign_key_checks=0; at the beginning of the file and this works fine. How can I achieve this when inserting a text file that is read with mysqlimport? I tried to put the mentioned sql-statement in the txt-file, but this does not affect anything. Background: One of my developers accidently dropped a table yesterday which had to be re-created from a dump. I was sort of lucky as the table was in a not too large database, so I could just open the dumpfile of that database and get the lines I needed to recreate the table and data. If I imagine this happened on a larger database which results in several-GB-size dumpfiles, it would have taken MUCH longer to get that table back. So now I'm playing with mysqldump --tab which gives nice per-table data and structure files. So if there's any other well-known solution for per-table dumpfiles, let me know. I'm not too keen on writing something myself right now. Thanks for your help. Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MysqlCC
Use old-passwords on the server or upgrade your client library. Regards Dominik [localhost] ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client You have something similar about mysql client last week but what about MysqlCC? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multi master replication question
Replication setup: A - B - A | C One thing I can't remember is do I have to set an option somewhere to tell the masters to ignore the queries in the binlog that oringated from them? Make sure you set different Server IDs on each machine and you should be just fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrade from 4.1.7 to 4.1.20
My question is : do I have to follow the upgrade procedure from 4.0 to 4.1 or can I juste installed the new binaries? We are not ready yet for the version 5. As always when upgrading: Make sure you have a backup :) Then install the new version and move (copy if you have the space) the data-directory. When not upgrading to a different major version, one should be fine with just copying data-files. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to copy a large innodb table
Recently I deleted ~200.000.000 rows out of a history table. Still there are 20.000.000 rows in the table. So now I want to clear some discspace by copying the table, dropping the old one and renaming the copy afterwards. Is there another (faster) way to do that and how does one copy such a large table? Normally I would do create table new like table old; insert into new select * from old; But this runs into this error: The total number of locks exceeds the lock table size. So I wrote a shell script which creates the table and copies like this: insert into new select * from old limit 100 offset 0 insert into new select * from old limit 100 offset 100 insert into new select * from old limit 100 offset 200 and so on. But I'm not sure if this will produce an exact copy of my table and on top of that the seventeenth loop fails with the same error mentioned above. Any help would be appreciated. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to copy a large innodb table
Thank you very much. I did not know this command. Well at least I never looked up what it does. I'll give it a try and see how it works out. Why not just use an OPTIMIZE TABLE ? This will map to an ALTER table command for an InnoDB table which will free the now unused space. From the manual at http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication stopped
How do you usually check automatically that slaves are up and running ? echo show slave status\G|mysql -u user -ppassword|grep -i slave.*running|grep -i no mail -s MySQL Slave stopped [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication stopped
echo show slave status\G|mysql -u user -ppassword|grep -i slave.*running|grep -i no mail -s MySQL Slave stopped [EMAIL PROTECTED] well, actually echo show slave status\G|mysql -u user -ppassword|grep -i slave.*running|grep -i no echo .|mail -s MySQL Slave stopped [EMAIL PROTECTED] sorry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on mysqldump --single-transaction
Hi everyone I'm wondering about the --single-transaction option on mysqldump. Documentation says --- This option issues a BEGIN SQL statement before dumping data from the server. ... --- So does this include the entire dump in one transaction? Or is it one transaction per database (or even table?)? I could not find an answer to this in the documentation. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump: Got errno 27 on write. file too large
How can I solve this problem? This might be a filesystem problem. Some filesystem (in certain configurations) cannot hold files larger than a particular size. Do you have any files larger than that cut dumpfile on that partition? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Position in master.info: read or executed master_log_pos?
Hi, for recovery purposes I need to know what exactly is in the master.info file. Especially the log position. Is it Read_Master_Log_Pos or Exec_Master_Log_Pos? Another question: Does stop slave; only stop reading the log from master or does it also stop executing the log that has been read already, but not yet executed? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to see why client got blocked
sheeri kritzer schrieb: If your server has log-warnings set to ON, you can check the error logs, and use a script to count how many times for each host, in a row, this happens. +---+---+ | Variable_name | Value | +---+---+ | log_warnings | 1 | I did not turn it off and documentation says it is on by default. I do not see any error regarding replication in the log on the slave. (`hostname`.err) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to see why client got blocked
I experience that my slave gets blocked after a while (a couple of slave stop and slave start happen in the meantime). In errorlog I see Slave I/O thread: error reconnecting to master '[EMAIL PROTECTED]:3306': Error: 'Host 'myhost.mydomain.de' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'' errno: 1129 retry-time: 60 retries: 86400 Slave and Master are 5.0.20. How can I see why the slave was blocked? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to see why client got blocked
another question on this error message: is it possible to see the count of errors for each host from some table or file? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Killing MySQL connections on a specific interface from a specific host
I am not really sure why you would need this , but I am just throwing in a possible solution.. First I would say since you need to kill connections on a specific interface (eth0), It would be fair to assume that you have more than one interface, if thats the case and you don't have the --bind-address option set in my.cnf, then your mysqld daemon would listen on all the available IP's on all interfaces, ACK. And that's what I need. then it is vey difficult to know on what IP did mysql serve a specific connection, unless you would do a netstat, then correspond that IP to the clients IP in show processlist, etc etc , so pretty cumbersume... That's what I've done so far ... I can list and grep all connections on the interface and get the client IP. I can also get the corresponding MySQL Thread-IDs from the mysql-processlist. But I could not find a way to only select connections from a specific Client IP to a specific Server IP, as the Processlist only shows the Client-address. But unless there is a real need , you can just have the deamon to listen only on one specific IP residing on eth0, like this --bind-address= xxx.xxx.xx.x ( this IP resides on eth0) The Server has to listen on all (two) interfaces and clients can connect to both. If this is feasible in your setup, then killing threads from a specific IP should be easy, if you need to kill threads manually then use a toll like mytop (http://jeremy.zawodny.com/mysql/mytop/ http://jeremy.zawodny.com/mysql/mytop/), or if you want it automated then you could easily write a perl script which would parse the output of show full processlist, get all the connections from a specific client IP, and KILL them ... Well, that's what I've done ... But it also kills connections from the client to another interface. I know this is somewhat special and it would take quite a while to explain why exactly I need this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Killing MySQL connections on a specific interface from a specific host
Hi I'd like to know if there is anything to kill connections from a specific client ip that came to the server on a specific interface. I do not want to block them on layer 2 (which could easily be done with netfilter), I would like to be able to kill active connections. For example: I would like to kill connections from 192.168.50.3 that came in on interface eth0. Connections from that client IP to another interface should not be affected. Of course one could script something using lsof -i or netstat and the mysql processlist, but that would end in some nasty shellscript and I don't know how to only kill connections for one interface as the mysql processlist only shows the client ip, not the ip, the client connected to. Thanks for any ideas Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (Errcode: 13) after moving data directory
boll schrieb: Hi- Using MySQL 4.1.11 on Fedora 4. I moved my data directory to a FAT partition in order to share it with Windows dual-boot. Now when I try to start mysqld normally, it fails with these messages in the log: 060420 18:16:03 mysqld started 060420 18:16:03 [Warning] Can't create test file /mnt/FAT/mysqldata/localhost.lower-test /usr/libexec/mysqld: Can't change dir to '/mnt/FAT/mysqldata/' (Errcode: 13) 060420 18:16:03 [ERROR] Aborting However, I can start mysqld using: mysqld_safe, so I know it's possible. Any suggestions? Thanks in advance. Did you check FAT-permissions? When mounting a FAT-partition, you have to set explicit permissions while mounting as FAT does not understand the unix permission concept. Try to mount this way: mount -t vfat -o uid=mysql,gid=mysql,rw,umask=007 /dev/[yourdevicename] /your/mountpoint Then it should work. Regards, Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import .frm .myd .myi to Mysql
hicham schrieb: Hello I'm new bie user of mysql, I need to create a database and import some frm .myd .myi files to that database , also if you can point me to some easy to start tutorial for how to create user account in mysql , create a database , etc Thanks for replying Hicham http://dev.mysql.com/doc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [NEWBIE] How To Trim Database To N Records
Is there a query that will, say, trim a table down to a million rows (with some sort order, of course, as I'm interested in deleting the oldest ones)? If you have got a datecolumn, you might also want to delete anything that is older than x days (2 in my example): DELETE FROM database.table WHERE datecolumn = DATE_SUB(sysdate(),INTERVAL 2 day); Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting up phpmyadmin problem
[EMAIL PROTECTED] schrieb: http://www.blue-fly.co.uk/screen.jpg I cannot seem to add a server..anyone shed any light on it? Just edit config.inc.php It has good documentation comments, so it should not be a problem. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Prinitng MySQL Structure from ssh
mysql mysqldump --no-data osc -u admin; ERROR 1064: You have an error in your SQL syntax near 'mysqldump --no-data osc -u admin' at line 1 You are supposed to execute that from a shell, not from within mysql Client. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
Post output of the commands uname -a and /lib/libc.so.6|head -1 What do you plan on doing with MySQL? Clustering or rather normal DB usage? Then we can tell you - or actually you should at least then be able to decide yourself ;) balaraju mandala schrieb: Hi Comunity, I have some probleme for selecting correct package of mysql software from download section in the site. I am confused which Linux version i have to use, as there are different packages. please help me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
please read what i wrote and do so - I cannot help you without this info as I do not know any Linux Enter prise ver4 please post your replies to the mysql mailing list, not to my email-address balaraju mandala schrieb: Hi DK, I just want use MySql for personal use. But i am confused which Linux package i have to download as there are different packages. I have a system(Pentium4-HT) loaded with Linux Enter prise ver4 or i can load Linux Enter prise ver3. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need to select correct package
The user -a output is : uname -a, but anyway ... Linux wizon.secureserver.net 2.6.9-11.EL #1 Fri May 20 18:17:57 EDT 2005 i686 i6 I didn't understand what is secound command is. You should have just pasted it into your command line and paste back the output into your mail. i686 i6 Thats the needed infortmation. Not necessarily. On some systems you can still find glibc V2.2, thats why I added the second command earlier. MySQL which was built with glibc2.3 will not work on such systems. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql timezone problem
[EMAIL PROTECTED] schrieb: after changing my system timezone from UTC to MSD i have the following problem: after restarting mysql server its timezone has not changet at all: mysql show variables like '%zone%'; +--++ | Variable_name| Value | +--++ | system_time_zone | UTC| | time_zone| SYSTEM | +--++ This is not necessarily wrong. See if select now(); gives you the correct time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
This was done as root and shows that TZ works. dk:/usr/local/mysql # bin/mysql -V bin/mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.0 dk:/usr/local/mysql # echo $TZ dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 802 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 09:26:35 | +-+ 1 row in set (0.05 sec) mysql Bye dk:/usr/local/mysql # support-files/mysql.server stop Shutting down MySQL...STOPPING server from pid file /usr/local/mysql/data/dk.pid 060327 09:26:45 mysqld ended done [1]+ Donebin/mysqld_safe --user=mysql dk:/usr/local/mysql # export TZ=America/Argentina/Mendoza dk:/usr/local/mysql # bin/mysqld_safe --user=mysql [1] 889 dk:/usr/local/mysql # Starting mysqld daemon with databases from /usr/local/mysql/data dk:/usr/local/mysql # bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql select now(); +-+ | now() | +-+ | 2006-03-27 04:27:09 | +-+ 1 row in set (0.00 sec) mysql Bye -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld_safe and timezone settings
or what I although could do to start my Server in another than the SYSTEM timezone? I use the MySQL 5.0.18 Server on a Suse Linux 10.0 From: http://dev.mysql.com/doc/refman/5.0/en/timezone-problems.html You can set the time zone for the server with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld. So try inserting export TZ=America/Argentina/Mendoza to the beginning of your mysql-(rc)startscript. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to take dump of a query instead of table / database
I need to take the backup of a query, is it possible. If yes how. man mysqldump: -w|--where= dump only selected records; QUOTES mandatory! Regards, Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: beginning confusions
[EMAIL PROTECTED] ~]$ mysqlshow +---+ | Databases | +---+ | test | +---+ In standard setup, there is a user without a name and password, who can connect from localhost to database test. So, accessing with this user gives you a list of the databases this user can see. [EMAIL PROTECTED] ~]$ mysqlshow mysql mysqlshow: Access denied for user ''@'localhost' to database 'mysql' As is not allowed to use database mysql, you get the access denied message. [EMAIL PROTECTED] ~]$ su - Password: [EMAIL PROTECTED] ~]# mysqlshow mysql mysqlshow: Access denied for user 'root'@'localhost' (using password: NO) I suppose the root-Account for your MySQL has got a password. As you have not given a password, you are not allowed to connect. Try mysqlshow -p mysql your second mail: More Confusions: When I try to follow the manual and enter 'bin/mysqld_safe --user=mysql ' I get this: [EMAIL PROTECTED] ~]$ cd /usr [EMAIL PROTECTED] usr]$ bin/mysqld_safe --user=mysql [1] 10340 [EMAIL PROTECTED] usr]$ cat: /var/run/mysqld/mysqld.pid: Permission denied rm: cannot remove `/var/run/mysqld/mysqld.pid': Permission denied Fatal error: Can't remove the pid file: /var/run/mysqld/mysqld.pid When mysql is starting, it removes any old pid-Files. If another user started MySQL before, this pid file belongs to that user and eric cannot remove it. bin/mysqld_safe: line 284: /var/log/mysqld.log: Permission denied Again here: check permissions on /var/log/mysqld.log and wether eric is able to write to that file. mysqld start'. Isn't there a way to start mysql other than as root? You can start mysql as any user. Just make sure you have proper file permissions. Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: beginning confusions
Thanks. This gets me going. Re proper file permissions: there seem to be mysql files all over the place. Do I have to find them all and change permissions on all of them? The MySQL manual I downloaded lists about eight different directories as including MySQL files, and I'll already seen at least one case where my installation put something in a different directory. (The rpm I installed is mysql-4.1.11-2 according to 'rpm -q mysql'.) I personally would suggest installing a mysql binary distribution from mysql.com This way you have all files in /usr/local/mysql/ which might be easier for you than to start with the redheat-distribution mysql, which (according to you) seems to split files in different directories. If you want to run mysql server as user eric, make sure to replace the user mysql mentioned in the INSTALL-BINARY-instruction-file with the user eric. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very large from
You could also use a temp table, put data into it page-by-page and insert the complete row after a last check into the real table. This temp table might have an additional timestamp field according to which evth. older than 1h(or some other time period) could easily be deleted by a cronjob. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to build a client?
I get various errors from my php based applications and php building that all refer to the need to upgrade my mysqlclient. Did you try old-passwords in /etc/my.cnf ? Old clients use an old authentication protocol, which is not the default on modern mysql servers. For compatibility, old-passwords uses the old algorithm. I have not found instructions on where to get the client software to build or upgrade a client. For php: ./configure options --with-mysql=/path/to/newmysql/ Regards, Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to build a client?
[addon to my previous mail] I have not found instructions on where to get the client software to build or upgrade a client. just use an up-to-date binary mysql distribution for your OS. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
