Re: best way to copy a innodb table
Hi, You should check pt-archiver. http://www.percona.com/doc/percona-toolkit/pt-archiver.html The goal is a low-impact, forward-only job to nibble old data out of the table without impacting OLTP queries much. This tool can copy data and insert in another table in chunks of data. The destination doesn't need to be on the same database or on the same server. Take in account that this tool by default deletes data from the source table, so use --no-delete option. Try it on a testing environment before executing it in production :) Regards, -- Miguel Ángel Nieto, Support Engineer, Percona Inc. http://www.percona.com | http://www.mysqlperformanceblog.com Email : miguel.ni...@percona.com Skype : percona.migueln 24/7 Emergency : +1 888 401 3401 ext 911 Training : http://www.percona.com/training/ Support : http://www.percona.com/mysql-support/ Percona Live MySQL Conference April 10-12 Santa Clara http://www.percona.com/live/mysql-conference-2012/ El 01/12/2011, a las 23:16, Angela liu escribió: Hi, folks: I have a situation: A large innodb table t1 with 45 million rows, need to have a new table t2 exactly the same as t1, to copy the data from t1 to t2, I have the following query: create table t2 like t1; insert into t2 select * from t1; but the above insert may run long time , that can interface with performance, is there a way to chunk the insert into the new table? like breaking it down into chunks of 100,000 rows ? Thanks signature.asc Description: Message signed with OpenPGP using GPGMail
Re: MySQL slave master replication
Hi, MySQL replicate the databases at SQL level, so if the two servers are using the same engine (innodb), they will be replicated without problems. If the BLOB data is big, don't configure the replication over a WAN or you will get too much latency (of course, it depends on your bandwidth). 2009/12/28 Jamie DelleMonache jam...@ways-n-means.com: I know the master/slave replication scheme for MySQL is pretty easy to set up; I'm doing it lab now. My question is does anyone know if it will successfully replicate foreign key constraints and large BLOB date types. Any feedback on this would be gratefully appreciated. _ Jamie DelleMonache | Solutions Specialist MEI | 610 Old York Rd, Suite 250 | Jenkintown, PA 19046 P: 215-886-5662 x282 | F: 215-886-5681 E-Mail: jdellemona...@maned.com http://www.maned.com -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't execute the given command because you have active locked tables or an active transaction
Hi, I am getting Can't execute the given command because you have active locked tables or an active transaction error when I am trying to truncate table. I am unable to understand the error as when I am using the mysql query browser then the same command is working fine but when I am doing it through NetBeans then I am getting this error. Well that is what is very strange and I am unable to solve this problem. Try enabling MySQL querie log: log=/var/log/somelog.log and execute the command from netbeans. Look at the log and execute again from query browser. Now, look if there is any difference on the log (perhaps netbeans execute some other queries) ;) -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Compiling mysql-5.1.41 in Fedora 12
Hi, http://bugs.mysql.com/bug.php?id=42039 The --with-mysqld-libs option should be given an argument, something like --with-mysqld-libs=-L/path/to/lib -lfoo -lbar 2009/12/27 Kurian Thayil kurianmtha...@gmail.com: Hi All, I was trying to compile MySQL 5.1.41 in a Fedora 12 box. I don't want to do an RPM install as I have to always upgrade the mysql package whenever an upgrade is released. So it is always convenient to use source rather than RPM. I used the following configure option. r...@home-desktop mysql-5.1.41]# sudo -u mysql ./configure -- localstatedir=/home/mysql/data --sysconfdir=/etc/mysql --with-default- charset=latin --with-unix-socket-path=/home/mysql/unix.sock --with-tcp- port=3307 --with-mysqld-user=mysql --with-mysqld-libs --with-ssl --with- plugins=partition,csv,innobase,myisam,ndbcluster --enable-thread-safe-client Gets configured successfully. But when doing make it gives me error. I think its yassl package dependency. I installed yassl-1.9.6 but still the error. The following is the last part of the make output. r...@home-desktop mysql-5.1.41]# sudo -u mysql make .. .. .. .. .. .. /bin/sh ../libtool --preserve-dup-deps --tag=CXX --mode=link g++ -O3 - fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysqld sql_lex.o sql_handler.o sql_partition.o item.o item_sum.o item_buff.o item_func.o item_cmpfunc.o item_strfunc.o item_timefunc.o thr_malloc.o item_create.o item_subselect.o item_row.o item_geofunc.o item_xmlfunc.o field.o strfunc.o key.o sql_class.o sql_list.o net_serv.o protocol.o sql_state.o lock.o my_lock.o sql_string.o sql_manager.o sql_map.o mysqld.o password.o hash_filo.o hostname.o sql_connect.o scheduler.o sql_parse.o set_var.o sql_yacc.o sql_base.o table.o sql_select.o sql_insert.o sql_profile.o sql_prepare.o sql_error.o sql_locale.o sql_update.o sql_delete.o uniques.o sql_do.o procedure.o sql_test.o log.o init.o derror.o sql_acl.o unireg.o des_key_file.o log_event.o rpl_record.o log_event_old.o rpl_record_old.o discover.o time.o opt_range.o opt_sum.o records.o filesort.o handler.o ha_partition.o debug_sync.o sql_db.o sql_table.o sql_rename.o sql_crypt.o sql_load.o mf_iocache.o field_conv.o sql_show.o sql_udf.o sql_analyse.o sql_cache.o slave.o sql_repl.o rpl_filter.o rpl_tblmap.o rpl_utility.o rpl_injector.o rpl_rli.o rpl_mi.o rpl_reporting.o sql_union.o sql_derived.o sql_client.o repl_failsafe.o sql_olap.o sql_view.o gstream.o spatial.o sql_help.o sql_cursor.o tztime.o my_decimal.o sp_head.o sp_pcontext.o sp_rcontext.o sp.o sp_cache.o parse_file.o sql_trigger.o event_scheduler.o event_data_objects.o event_queue.o event_db_repository.o events.o sql_plugin.o sql_binlog.o sql_builtin.o sql_tablespace.o partition_info.o sql_servers.o event_parse_data.o mini_client_errors.o pack.o client.o my_time.o my_user.o libndb.la ../storage/csv/libcsv.a ../storage/heap/libheap.a ../storage/innobase/libinnobase.a ../storage/myisam/libmyisam.a ../storage/myisammrg/libmyisammrg.a ../storage/ndb/src/.libs/libndbclient.a ../vio/libvio.a ../mysys/libmysys.a ../dbug/libdbug.a ../regex/libregex.a ../strings/libmystrings.a -lz -ldl ../extra/yassl/src/libyassl.la ../extra/yassl/taocrypt/src/libtaocrypt.la yes -lpthread -lcrypt -lnsl -lm - lpthread libtool: link: g++ -O3 -fno-implicit-templates -fno-exceptions -fno-rtti - rdynamic -o mysqld sql_lex.o sql_handler.o sql_partition.o item.o item_sum.o item_buff.o item_func.o item_cmpfunc.o item_strfunc.o item_timefunc.o thr_malloc.o item_create.o item_subselect.o item_row.o item_geofunc.o item_xmlfunc.o field.o strfunc.o key.o sql_class.o sql_list.o net_serv.o protocol.o sql_state.o lock.o my_lock.o sql_string.o sql_manager.o sql_map.o mysqld.o password.o hash_filo.o hostname.o sql_connect.o scheduler.o sql_parse.o set_var.o sql_yacc.o sql_base.o table.o sql_select.o sql_insert.o sql_profile.o sql_prepare.o sql_error.o sql_locale.o sql_update.o sql_delete.o uniques.o sql_do.o procedure.o sql_test.o log.o init.o derror.o sql_acl.o unireg.o des_key_file.o log_event.o rpl_record.o log_event_old.o rpl_record_old.o discover.o time.o opt_range.o opt_sum.o records.o filesort.o handler.o ha_partition.o debug_sync.o sql_db.o sql_table.o sql_rename.o sql_crypt.o sql_load.o mf_iocache.o field_conv.o sql_show.o sql_udf.o sql_analyse.o sql_cache.o slave.o sql_repl.o rpl_filter.o rpl_tblmap.o rpl_utility.o rpl_injector.o rpl_rli.o rpl_mi.o rpl_reporting.o sql_union.o sql_derived.o sql_client.o repl_failsafe.o sql_olap.o sql_view.o gstream.o spatial.o sql_help.o sql_cursor.o tztime.o my_decimal.o sp_head.o sp_pcontext.o sp_rcontext.o sp.o sp_cache.o parse_file.o sql_trigger.o event_scheduler.o event_data_objects.o event_queue.o event_db_repository.o events.o sql_plugin.o sql_binlog.o sql_builtin.o sql_tablespace.o partition_info.o sql_servers.o event_parse_data.o mini_client_errors.o pack.o client.o my_time.o my_user.o yes ./.libs/libndb.a -lpthread -lpthread -
Re: mysql load balancing
Load balancing, or high availability? I do not think there is anything good and simple AND generic out of the box. As previous posters have noted, you generally have to build something on top of other tools. Hi, I have the HA solved with MMM. Now, I want load balacing, sending read queries to slaves and write queries to masters. I read about mysql proxy, sqlrelay... but I didn't know the difference betwen them, and the possible problems of each tool (latency for example). If anyone, like Pascal, have experience with those kind of tools, please, share them :P -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: mysql load balancing
Hi, El día 22 de diciembre de 2009 10:14, Jaime Crespo Rincón jcre...@warp.es escribió: 2009/12/21 Miguel Angel Nieto cor...@miguelangelnieto.net: Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? Hi, The solutions I have heard most from our customers (in production) are not mysql-specific: 1) Simple, not load-aware *DNS balancing* for simple applications without fault-tolerance: http://en.wikipedia.org/wiki/Round_robin_DNS 2) More complex and customizable *Linux Virtual Server*, with integrated heartbeat and session aware: http://www.linuxvirtualserver.org/ It depends a lot on how you plan to coordinate the db servers (sharding, replication, ndb), the kind of applications you are going to deploy and how much scability you need. Thank you. I have read about LVS and keepalived but I can't see the difference between them. Are they the same thing? I want the load balancing for my replicated servers. I suppose that LVS can't distinguish between inserts and selects (to send queries to the master o slave server). See you. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql load balancing
Hi, I am searching fot a Mysql Load Balacing tool. I read about mysql proxy, sqlrelay, haproxy... What do you prefer? What are the benefits and bugs? :) Thank you. -- Lo que haría sería hacerme pasar por sordomudo y así no tendría que hablar. Si querían decirme algo, tendrían que escribirlo en un papelito y enseñármelo. Al final se hartarían y ya no tendría que hablar el resto de mi vida. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org