Re: Which replication solution should I choose?
Thanks for the info, Walter. I checked some basic info about Galera and it looks very promising. Can you tell what is preferable for loadbalancing of requests? HAProxy/Galera loadbalancer or maybe something else? Can you tell me also how much does MHA differ from MMM? Functionality looks quite similar. BR, Rafal. 2014-10-29 11:38 GMT+01:00 Heck, Walter : > Hi Rafael, > On Wed, Oct 29, 2014 at 10:15 AM, Rafał Radecki > wrote: > >> I am creating an environment based on about 15 hardware nodes. 6 of them >> will be for mysql databases. They will be divided into three pairs (3x2 >> nodes). Nodes in every pair will be configured with replication. >> >> I done similar configuration about an year ago. I used than: >> - percona 5.5 with standard asynchronous replication; >> - Multi Master Replication Manager for MySQL (http://mysql-mmm.org/ >> <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/df42c03e188abb8c80b981666b82e7ff?ytl=http%3A%2F%2Fmysql-mmm.org%2F>) >> for >> automatic assignment/failover of read and write IP addresses and checking >> the status of replication (mmm provides status scripts); >> > While it works fine for many situations, I wouldn't recommend using it for > any new setups. There hasn't been any updates for years and there are a > decent number of edge cases where MMM fails miserably. There are better > alternatives out there these days. > > >> My question is: I heard that there are other options than standard >> asynchronous replication which sometimes was problematic (for example >> there >> was one slave thread only in percona 5.5 and there was a possibility that >> slave node will fall behind master node in high load situations). I also >> am >> thinking which MySQL fork is the best option if I plan to use replication. >> What can you propose based on your experience? >> > If you want to stick with standard replication, either MariaDB 10 or > Percona 5.6 will do just fine. Instead of MMM you can google for MHA for > instance. > > I would recommend taking a look at Galera though, which takes a different > approach but with some nice benefits. The one thing is that with Galera > your data generally lives on 3 or more servers (2 is possible, but not > recommended). If that is a good idea to you, then Galera would be my > personal preference. > > > -- > Best regards, > > Walter Heck > CEO / Founder OlinData > <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/fd1ca40450db4c95f61e02cfe4940db2?ytl=http%3A%2F%2Folindata.com%2F%3Fsrc%3Dwh_gapp> > - Open Source Training & Consulting > > Check out our upcoming trainings > <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/63ebe1eaf25f15c25c5b43a8b2954a8d?ytl=http%3A%2F%2Folindata.com%2Ftraining%2Fupcoming> > >
Which replication solution should I choose?
Hi All :) I am creating an environment based on about 15 hardware nodes. 6 of them will be for mysql databases. They will be divided into three pairs (3x2 nodes). Nodes in every pair will be configured with replication. I done similar configuration about an year ago. I used than: - percona 5.5 with standard asynchronous replication; - Multi Master Replication Manager for MySQL (http://mysql-mmm.org/) for automatic assignment/failover of read and write IP addresses and checking the status of replication (mmm provides status scripts); - Pacemaker to create a cluster in which scripts for Multi Master Replication Manager were used. My question is: I heard that there are other options than standard asynchronous replication which sometimes was problematic (for example there was one slave thread only in percona 5.5 and there was a possibility that slave node will fall behind master node in high load situations). I also am thinking which MySQL fork is the best option if I plan to use replication. What can you propose based on your experience? BR, Rafal.
pt-table-checksum: --ignore-tables-regex does not work properly?
Hi All. I use: percona-toolkit-2.2.4-1.noarch Percona-Server-server-55-5.5.28-rel29.1.335.rhel6.x86_64 Percona-Server-shared-compat-5.5.28-rel29.1.335.rhel6.x86_64 Percona-Server-client-55-5.5.28-rel29.1.335.rhel6.x86_64 Percona-Server-shared-55-5.5.28-rel29.1.335.rhel6.x86_64 on Centos 6.3 2.6.32-279.14.1.el6.x86_64 I have created checksum table and tried to use --ignore-tables-regex to remove some tables from checking. pt-table-checksum --chunk-size-limit= --nocheck-plan --replicate-check --ignore-tables-regex=^test.s_.*_tmp$ --ignore-tables=test.catalogsearch_fulltext,test.catalogsearch_result,test.report_event,test.report_viewed_product_index,test.z_crawler_log,test.z_logger_debug_ajax,test.z_crawler_queue,test.catalog_category_anc_categs_index_tmp,test.catalog_category_anc_products_index_tmp,test.catalog_category_product_index_enbl_tmp,test.catalog_category_product_index_tmp,test.catalog_product_index_eav_decimal_tmp,test.catalog_product_index_eav_tmp,test.catalog_product_index_price_bundle_opt_tmp,test.catalog_product_index_price_bundle_sel_tmp,test.catalog_product_index_price_bundle_tmp,test.catalog_product_index_price_cfg_opt_agr_tmp,test.catalog_product_index_price_cfg_opt_tmp,test.catalog_product_index_price_downlod_tmp,test.catalog_product_index_price_final_tmp,test.catalog_product_index_price_opt_agr_tmp,test.catalog_product_index_price_opt_tmp,test.catalog_product_index_price_tmp,test.cataloginventory_stock_status_tmp,test.z_I04_data_for_crawler,test.z_import_prices_mdk,test.z_import_prices_sku,test.z_import_translations,test.z_import_translations_model --recursion-method dsn=h=localhost,D=percona,t=dsns --user=percona --password=percona --nocheck-replication-filters --databases=test,mysql localhost But it does not work, for example table test.s_xxx_tmp gives error message: 09-18T03:10:47 Skipping table test.s_xxx_tmp because it has problems on these replicas: Table test.s_xxx_tmp does not exist on replica server.local This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check. 09-18T03:10:47 Error checksumming table test.s_xxx_tmp: DBD::mysql::db selectrow_hashref failed: Tab le 'test.s_xxx_tmp' doesn't exist [for Statement "EXPLAIN SELECT * FROM `test`.`s_xxx_tmp` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6528. I've tried --ignore-tables-regex= multiple times: ^test.s_.*_tmp$ '^test.s_.*_tmp$' ^test.s_.\*_tmp$ ^test.s_\.\*_tmp$ but without good result. What is the correct syntax in this case? Best regards, Rafal Radecki.
binlog_format and pt-table-checksum?
Hi All. I use binlog_format = row for my production databases. In this format most binlog_format changes are sent not as SQL statements but in some other format. I understand that when binlog_format = statement is used, queries on master and slave can give different results, but should pt-table-cheksum be used in situation when we use binlog_format = row? I've seen opinions that regardles of binlog_format the data on slave and master may differ. What are your experiences? Best regards, Rafał.
Re: ALTER TABLE - correct way of adding columns?
Have you used pt-online-schema-change.html from http://www.percona.com/doc/percona-toolkit/2.0/pt-online-schema-change.html ? What do you think about this tool? Best regards, Rafal Radecki. 2013/7/8 Rafał Radecki > Hi All. > > I would like to change the layout of my production database, I would like > to add a column to an existing table. As I think before the ALTER TABLE > statement all access to the database should be denied/ended, then the ALTER > TABLE should be performed, and then user/applications should be able to use > the database once again. > > My tables is quite small ~4MB data & indexes. > > So is the ALTER TABLE on a running/opened to clients database/table > desirable or should it be run when all access to the database/table is > forbidden? > > Best regards, > Rafal Radecki. >
ALTER TABLE - correct way of adding columns?
Hi All. I would like to change the layout of my production database, I would like to add a column to an existing table. As I think before the ALTER TABLE statement all access to the database should be denied/ended, then the ALTER TABLE should be performed, and then user/applications should be able to use the database once again. My tables is quite small ~4MB data & indexes. So is the ALTER TABLE on a running/opened to clients database/table desirable or should it be run when all access to the database/table is forbidden? Best regards, Rafal Radecki.
Mysql resource limits.
Hi All. I would like to limit resources available to a given user in mysql. I know that there is https://dev.mysql.com/doc/refman/5.5/en/user-resources.html, I also know that cgroups can be used at operating system level. What are your experiences in limiting resources in mysql? I've user percona statistics and had information provided by it. Are there any better solutions? Best regards, Rafal Radecki.
Re: SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
As I can see the changes in these values are use by percona cacti monitoring templates to graph "InnoDB I/O". Can anyone answer the question finally? ;) 2013/6/21 Hartmut Holzgraefe > On 21.06.2013 13:59, Rafał Radecki wrote: > > Hi All. > > > > I've searched but with no luck... what do exactly these variables mean: > > > > 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs > > > > ? > > these are the total number of reads/writes/fsyncs (number of system > calls actually?) since the server started (or maybe last FLUSH call?) > and not very meaningful by themselves without knowing the time span > it took to come up to those counter values. > > The per second values on the following line are much more interesting. > > > http://www.mysqlperformanceblog.com/2006/07/17/show-innodb-status-walk-through/ > > has a pretty good description of the SHOW ENGINE INNODB STATUS output, > even though it is not too detailed in this specific section. > > > -- > Hartmut Holzgraefe > Principal Support Engineer (EMEA) > SkySQL AB - http://www.skysql.com/ > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
SHOW INNODB STATUS - FILE I/O - OS reads/writes/syncs?
Hi All. I've searched but with no luck... what do exactly these variables mean: 1343928 OS file reads, 1085452262 OS file writes, 19976022 OS fsyncs ? I am wondering if my innodb_buffer_pool setting is not to low. Does 'file reads' show number of times innodb files have been read into memory from server's start? What about file writes/fsyncs? Best regards, Rafal Radecki.
Mysql server - which filesystem to choose? Is it really that important nowadays?
Hi All. I use mysql/perconna/maria on my production CentOS 6 Linux servers. I currently try to choose the default filesystem for partitions with mysql data. Some time ago (previous dba) reiserfs was the choice but now it is not in the kernel and the main author is in prison. >From what I've read xfs and ext4 are valid choices and performance benchmarks over the web show that they are "comparable" (no clear winner). I've also read that with every new kernel there can be changes in performance in every filesystem ( for example http://gtowey.blogspot.com/2013/02/serious-xfs-performance-regression-in.html ). >From your experiences: which filesystem to choose for a mysql db? Is ext4 or xfs better? Or is it more a case of proper filesystem tuning to my workload? Any articles worth reading which you can recommend? Best regards, Rafal.
Innodb innodb_buffer_pool_size?
Hi All. I am trying to set the best value for innodb_buffer_pool_size. My system has 6GB of ram. My system is: 2.6.32-279.19.1.el6.centos.plus.x86_64 CentOS release 6.3 (Final) mysql-server-5.0.58 My current setting: innodb_buffer_pool_size = 1024M (my.cnf attached) >From show innodb status: -- BUFFER POOL AND MEMORY -- Total memory allocated 1250582306; in additional pool allocated 12842496 Buffer pool size 65536 Free buffers 0 Database pages 61505 Modified db pages 86 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 96616, created 97554, written 126547 0.80 reads/s, 0.20 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 Size of innodb tables: du -h /mysql/ibdata1 1.8G/mysql/ibdata1 My question: how to tell if my innodb_buffer_pool_size is ok? Does Buffer pool hit rate 1000 / 1000 mean that I can lower it? Does Free buffers 0 mean that I should make it larger? Eventually what else to check? Best regards, Rafal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
All client commands to syslog?
Hi All. I have a production setup of four databases connected with replication. I would like to log every command that clients execute for auditing. I've read http://www.percona.com/doc/percona-server/5.5/diagnostics/mysql_syslog.html?id=percona-server:features:mysql_syslog&redirect=1#client-variables but despite the fact that I use percona Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 it does not seem to work. I've added "syslog"/"syslog ON" to my my.cnf server configuration file but no info about executed commands in logs. I've also read http://www.mysqlperformanceblog.com/2008/07/08/logging-your-mysql-command-line-client-sessions/. What is the best way to log all client commands? Best regards, Rafal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.
013/03/13 14:04:40 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:42 DEBUG Listener: Connect! 2013/03/13 14:04:42 DEBUG Listener: Disconnect! 2013/03/13 14:04:42 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:42 DEBUG Listener: Connect! 2013/03/13 14:04:45 DEBUG Listener: Disconnect! 2013/03/13 14:04:45 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:45 DEBUG Listener: Connect! 2013/03/13 14:04:48 DEBUG Listener: Disconnect! 2013/03/13 14:04:48 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:48 DEBUG Listener: Connect! 2013/03/13 14:04:51 DEBUG Listener: Disconnect! 2013/03/13 14:04:51 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:51 DEBUG Listener: Connect! 2013/03/13 14:04:54 DEBUG Listener: Disconnect! 2013/03/13 14:04:54 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:54 DEBUG Listener: Connect! 2013/03/13 14:04:57 DEBUG Listener: Disconnect! 2013/03/13 14:04:57 DEBUG Listener: Waiting for connection... 2013/03/13 14:04:57 DEBUG Listener: Connect! 2013/03/13 14:05:00 DEBUG Listener: Disconnect! 2013/03/13 14:05:00 DEBUG Listener: Waiting for connection... 2013/03/13 14:05:00 DEBUG Listener: Connect! 2013/03/13 14:05:03 DEBUG Listener: Disconnect! On host with monitor I have more and more connections to mmm agents and mysql instances in TIME_WAIT state. I modified net.ipv4.tcp_fin_timeout = 5 (from 60 default) but that does not help. netstat -an -> tcp0 0 192.168.0.100:44075 192.168.0.1:3306 TIME_WAIT tcp0 0 192.168.0.100:57280 192.168.0.2:9989 TIME_WAIT tcp0 0 192.168.0.100:57374 192.168.0.2:3306 TIME_WAIT tcp0 0 192.168.0.100:44077 192.168.0.1:3306 TIME_WAIT tcp0 0 192.168.0.100:57240 192.168.0.2:9989 TIME_WAIT tcp0 0 192.168.0.100:43897 192.168.0.1:3306 TIME_WAIT tcp0 0 192.168.0.100:57250 192.168.0.2:9989 TIME_WAIT tcp0 0 192.168.0.100:39126 192.168.0.1:9989 TIME_WAIT ... Any tips? Best regards, Rafal. 2013/3/11 Manuel Arostegui : > > > 2013/3/11 Rafał Radecki >> >> Hi All. >> >> I use: >> >> cat /etc/redhat-release >> CentOS release 6.3 (Final) >> >> uname -a >> Linux prod1.local 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 >> UTC 2012 x86_64 x86_64 x86_64 GNU/Linux >> >> on db host: >> rpm -qa | grep mmm >> mysql-mmm-2.2.1-1.el6.noarch >> bmysql-mmm-agent-2.2.1-1.el6.noarch >> >> on monitor host: >> rpm -qa | grep mmm >> mysql-mmm-monitor-2.2.1-1.el6.noarch >> mysql-mmm-2.2.1-1.el6.noarch >> mysql-mmm-tools-2.2.1-1.el6.noarch >> >> When I make mmm_control show/checks the command executes for example >> for ~15 seconds and sometimes returns no output. In logs there is no >> info about problems and overall the monitor performs well. But I >> cannot use mmm_control to check its status. The servers are not over >> loaded. I have restarted agents and monitor but that has not resolved >> the problem. When I have rebooted the monitor host first use of >> mmm_control show was ok but then the problem was active again. >> >> Any advice? Have you had any similar problems? > > > Hello, > > Have you tried to set "debug 1" in your mmm_common.conf and start the > monitor? You might find useful output in there. > > Manuel. > > > -- > Manuel Aróstegui > Systems Team > tuenti.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.
Hi All. I use: cat /etc/redhat-release CentOS release 6.3 (Final) uname -a Linux prod1.local 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux on db host: rpm -qa | grep mmm mysql-mmm-2.2.1-1.el6.noarch bmysql-mmm-agent-2.2.1-1.el6.noarch on monitor host: rpm -qa | grep mmm mysql-mmm-monitor-2.2.1-1.el6.noarch mysql-mmm-2.2.1-1.el6.noarch mysql-mmm-tools-2.2.1-1.el6.noarch When I make mmm_control show/checks the command executes for example for ~15 seconds and sometimes returns no output. In logs there is no info about problems and overall the monitor performs well. But I cannot use mmm_control to check its status. The servers are not over loaded. I have restarted agents and monitor but that has not resolved the problem. When I have rebooted the monitor host first use of mmm_control show was ok but then the problem was active again. Any advice? Have you had any similar problems? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to verify if backup is ok?
Thanks, I will use this tool :) 2013/2/19 Johan De Meersman : > - Original Message - >> From: "Rafał Radecki" >> >> pt-table-checksum performs an online replication consistency check by >> executing checksum queries on the master, which produces >>different results on replicas that are inconsistent with the >> master. -> It should be used for verifing mysql replication, not for >> my problem. > > Hmm, I didn't realise that that was not a part of pt-table-checksum. The > older mk-table-checksum from Maatkit doesn't particularly care about masters > and slaves, and will happily compare to unrelated databases. You can still > find it at http://www.maatkit.org/doc/mk-table-checksum.html#description . > > I just copied the contents of a db to another db on the same server, and it > works as advertised. > > Not the first feature that I noticed hasn't been merged into the PT suite :-( > > > -- > Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to verify if backup is ok?
Thanks for the reply. pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master. -> It should be used for verifing mysql replication, not for my problem. Any other tips? Best regards, Rafal Radecki. 2013/2/18 Johan De Meersman : > - Original Message - >> From: "Rafał Radecki" >> >> 3) drop mysql and app databases; >> 4) restore them from backup; > > Instead of dropping the DBs, simply restore to another database or server. > That will also allow you to perform a comparison using some graphical tool, > or if that fails mysqldumps and diff. > >> tips? Should I do it on filesystem level or on mysql level? Are there >> any external tools? > > Filesystem level won't work, as it's fairly unlikely that the records will > have been written in the same order - let alone that you won't have delete > gaps etc. > > Percona toolkit has tools to verify master/slave setups (pt-table-compare, I > believe), I suppose they would also work on non-replicated setups. > > > -- > Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
How to verify if backup is ok?
Hi All. I have a development database which is replicated to a slave. Backup is taken daily from this slave. I need to test if the restore procedure is ok. I would like to: 1) block all application access (stop services + iptables block) to the database and lock it read only; 2) make a backup with my script; 3) drop mysql and app databases; 4) restore them from backup; 5) verify if data after restore is exactly the same as before the process. I know how to make steps 1-4 but I do not know how to make step 5. Any tips? Should I do it on filesystem level or on mysql level? Are there any external tools? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Mysqldump routines dump, problem with lock tables.
Hi All. I use: # rpm -qa | grep -i percona-server-server Percona-Server-server-55-5.5.28-rel29.3.388.rhel6.x86_64 My system: # uname -a;cat /etc/redhat-release Linux prbc01.mg.local 2.6.32-279.19.1.el6.centos.plus.x86_64 #1 SMP Wed Dec 19 06:20:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux Red Hat Enterprise Linux Server release 6.3 (Santiago) I have a backup script which at some point calls: mysqldump --default-character-set=utf8 --routines --no-data --no-create-info --skip-triggers -S /mysql/database.sock -u backup -pxxx database and I have error: mysqldump: Got error: 1045: Access denied for user 'yyy'@'zzz' (using password: YES) when using LOCK TABLES So I thinke that mysqldump locks the table (--add-locks) by default. But for this user: mysql> show grants for yyy@'zzz'; ++ | Grants for backup@localhost | ++ | GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW ON *.* TO 'yyy'@'zzz' IDENTIFIED BY PASSWORD ... | | ++ 2 rows in set (0.00 sec) So why is this error showing? When I add --single-transaction to mysqldump everything is ok. But I would like to have this table locked because: mysql> SELECT ENGINE -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = 'information_schema' -> AND TABLE_NAME = 'routines'; ++ | ENGINE | ++ | MyISAM | ++ so information_schema.tables is myisam. So why do I get the error about LOCK TABLES? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Percona 5.5 semisynchronous replication.
Hi all. I have installed a setup in which I use semisync replication. On master I have: mysql> show status like '%rpl%'; ++-+ | Variable_name | Value | ++-+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 1389| | Rpl_semi_sync_master_net_wait_time | 159296355 | | Rpl_semi_sync_master_net_waits | 114643 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status| ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 33380 | | Rpl_semi_sync_master_tx_wait_time | 77877106| | Rpl_semi_sync_master_tx_waits | 2333| | Rpl_semi_sync_master_wait_pos_backtraverse | 43 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx| 114667 | | Rpl_semi_sync_slave_status | ON | | Rpl_status | AUTH_MASTER | ++-+ So semisync replication is enabled and working and there have been no switches to async replication since mysql start. But at the same time I have on the slave: Seconds_Behind_Master: 364 And I wonder how is it possible? As far as I understand semisync replication it works that way: - master starts executing the query - master sends the query to slave - master starts the commit and waits for rpl_semi_sync_master_timeout for ack from one of the slaves So if I have rpl_semi_sync_master_timeout=5000 (5s) and semisync replication is working and it has not switched to async replication why is it possible on the slave to fall behind the master? Best regards, Rafal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Semisynchrounous replication - how to clear Rpl_semi_sync_master_no_tx/_times.
Hi all. I would like to clear values off Rpl_semi_sync_master_no_tx and Rpl_semi_sync_master_no_times status variables because I am plugging a sync replication setup into nagios. I've tried: mysql> SET global Rpl_semi_sync_master_no_tx=0; ERROR 1193 (HY000): Unknown system variable 'Rpl_semi_sync_master_no_tx' mysql> SET global Rpl_semi_sync_master_no_times=0; ERROR 1193 (HY000): Unknown system variable 'Rpl_semi_sync_master_no_times' Is there a way to clear these counters? Best regards, Rafal Radecki. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL 5.1.59 - slow_log purge problem.
Hi all.
I write a script to delete rows from slow_log older than 2 weeks.
#!/bin/bash
if [ $# -ne 1 ]; then
echo "Usage: $0 mysql_config_file"
exit 1
fi
SELECTQUERY="select * from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
DELETEQUERY="delete from slow_log where start_time <
DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
CONFIG_FILE="$1"
HOSTNAME="$(hostname | awk -F'.' '{print $1}')"
INSTANCENAME="$(grep datadir $CONFIG_FILE | awk -F'/' '{print $4}'|uniq)"
LOG_FILE="/var/log/${INSTANCENAME}-${HOSTNAME}-slowlog-clean.log"
echo "***" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Started cleaning..." >> $LOG_FILE
echo "Before there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
mysql --defaults-file=${CONFIG_FILE} mysql -e \"$DELETEQUERY\" > /dev/null
2>&1
echo "After there are $(mysql --defaults-file=${CONFIG_FILE} mysql -e
\"${SELECTQUERY}\" | wc -l) entries older than 2 weeks" >> $LOG_FILE
echo "$(date +'%Y-%m-%d %H:%M:%S %Z') Stopped cleaning..." >> $LOG_FILE
echo "***" >> $LOG_FILE
When I issue the delete statement I get:
mysql --defaults-file=/etc/my.cnf mysql -e "delete from slow_log where
start_time < DATE_ADD(NOW(),INTERVAL - 2 WEEK)"
ERROR 1556 (HY000) at line 1: You can't use locks with log tables.
mysql --defaults-file=/etc/my.cnf mysql -e "select * from slow_log where
start_time < DATE_ADD(NOW(),INTERVAL - 4 WEEK) limit 1"
+-+---++---+---+---+++---+---+-+
| start_time | user_host | query_time | lock_time
| rows_sent | rows_examined | db | last_insert_id | insert_id | server_id |
sql_text|
+-+---++---+---+---+++---+---+-+
| 2011-10-09 23:06:17 | root[root] @ localhost [] | 00:00:06 | 00:00:00
| 1 | 0 || 0 | 0 |72
| select sleep(6) |
+-+---++---+---+---+++---+---+-+
Above select works fine.
How can I resolve the error? What is the proper way to clean slow_log?
Best regards,
Rafal Radecki.
MySQL slowlog - only in file?
Hi all. Is there a possibility to see the info from slowlog somewhere in database? I would like to see slow queries using mysql and not by watching the log file. I've searched on google and mysql website but hasn't found the solution. Best regards, Rafal Radecki.
