当前位置: 澳门新濠3559 > 数据库 > 正文

Percona-Tookit工具包之pt-heartbeat澳门新濠3559:

时间:2019-10-05 04:13来源:数据库

*    pt-heartbeat is the very tool can be used to monitor replication delay of MySQL or PostgreSQL.It provides a way to update a master or to monitor a replica.If you do not speicify the connection options,it will retrieve them in your default my.cnf file.
*

Note

Note that certain steps within the setup process require the SUPER privilege. If you do not have this privilege then enabling replication may not be possible.

    /*
     * app_name_redirect_preference_list = 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
     */
    char *app_name_redirect_preference_list;    /* raw string in pgpool.conf */
    RegArray *redirect_app_names;                /* Precompiled regex patterns for app name prefrence list */
    Left_right_tokens *app_name_redirect_tokens;/* app name redirect for app_name and node string */

16.2.6. Switching Masters During Failover

There is currently no official solution for providing failover between master and slaves in the event of a failure. With the currently available features, you would have to set up a master and a slave (or several slaves), and to write a script that monitors the master to check whether it is up. Then instruct your applications and the slaves to change master in case of failure.

Remember that you can tell a slave to change its master at any time, using the CHANGE MASTER TO statement. The slave will not check whether the databases on the master are compatible with the slave, it will just start executing events from the specified log and position on the new master. In a failover situation all the servers in the group are probably executing the same events from the same binary log, so changing the source of the events should not affect the database structure or integrity providing you are careful.

Run your slaves with the --log-bin option and without --log-slave-updates. In this way, the slave is ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO statement on the other slaves. For example, assume that you have the structure shown in Figure 16.4, “Redundancy using replication, initial structure”.

Figure 16.4. Redundancy using replication, initial structure

澳门新濠3559 1

In this diagram, the MySQL Master holds the master database, the MySQL Slave computers are replication slaves, and the Web Client machines are issuing database reads and writes. Web clients that issue only reads (and would normally be connected to the slaves) are not shown, as they do not need to switch to a new server in the event of failure. For a more detailed example of a read/write scaleout replication structure, see Section 16.2.3, “Using Replication for Scale-Out”.

Each MySQL Slave (Slave 1, Slave 2, and Slave 3) are slaves running with --log-bin and without --log-slave-updates. Because updates received by a slave from the master are not logged in the binary log unless --log-slave-updates is specified, the binary log on each slave is empty initially. If for some reason MySQL Master becomes unavailable, you can pick one of the slaves to become the new master. For example, if you pick Slave 1, all Web Clients should be redirected to Slave 1, which will log updates to its binary log. Slave 2 and Slave 3 should then replicate from Slave 1.

The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. Suppose that Slave 1 has --log-slave-updates enabled. Then it will write updates that it receives from Master to its own binary log. When Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master

Make sure that all slaves have processed any statements in their relay log. On each slave, issue STOP SLAVE IO_THREAD, then check the output of SHOW PROCESSLIST until you see Has read all relay log. When this is true for all slaves, they can be reconfigured to the new setup. On the slave Slave 1 being promoted to become the master, issue STOP SLAVE and RESET MASTER.

On the other slaves Slave 2 and Slave 3, use STOP SLAVE and CHANGE MASTER TO MASTER_HOST='Slave1' (where 'Slave1' represents the real host name of Slave 1). To use CHANGE MASTER TO, add all information about how to connect to Slave 1 from Slave 2 or Slave 3 (user, password, port). In CHANGE MASTER TO, there is no need to specify the name of Slave 1's binary log or binary log position to read from: We know it is the first binary log and position 4, which are the defaults for CHANGE MASTER TO. Finally, use START SLAVE on Slave 2 and Slave 3.

Once the new replication is in place, you will then need to instruct each Web Client to direct their statements to Slave 1. From that point on, all updates statements sent by Web Client to Slave 1 are written to the binary log of Slave 1, which then contains every update statement sent to Slave 1 since Master died.

The resulting server structure is shown in Figure 16.5, “Redundancy using replication, after master failure”.

Figure 16.5. Redundancy using replication, after master failure

澳门新濠3559 2

When Master is up again, you must issue on it the same CHANGE MASTER TO as that issued on Slave 2 and Slave 3, so that Master becomes a slave of S1 and picks up each Web Client writes that it missed while it was down.

To make Master a master again (because it is the most powerful machine, for example), use the preceding procedure as if Slave 1 was unavailable and Master was to be the new master. During this procedure, do not forget to run RESET MASTER on Master before making Slave 1, Slave 2, and Slave 3 slaves of Master. Otherwise, they may pick up old Web Client writes from before the point at which Master became unavailable.

Note that there is no synchronization between the different slaves to a master. Some slaves might be ahead of others. This means that the concept outlined in the previous example might not work. In practice, however, the relay logs of different slaves will most likely not be far behind the master, so it would work, anyway (but there is no guarantee).

A good way to keep your applications informed as to the location of the master is by having a dynamic DNS entry for the master. With bind you can use nsupdate to dynamically update your DNS.

 

16.1.2.2. Usage of Row-based Logging and Row-Based Replication

Using row-based logging or replication, rather than statement-based logging or replication, can result in major changes in the replication environment and in the behavior of applications. This section describes a number of issues known to exist when using row-based logging or row-based replication, and discusses some best practices for taking advantage of row-based logging (RBL) and row-based replication (RBR).

For additional information, see Section 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”, and Section 16.1.2, “Replication Formats”.

  • RBR and primary keys.  Currently, row-based replication may fail if any table to be replicated does not have an explicit primary key. This is a known issue which we are working to fix in a future MySQL release.

    ### Note

    Beginning with MySQL 5.1.7, it is possible to replicate NDBCLUSTER tables without explicit primary keys. However, in the event that a MySQL Cluster data node fails, MySQL Cluster Replication can also fail for tables without explicit primary keys. For this reason, it is highly recommended that all NDBCLUSTER tables being replicated have explicit primary keys. For more information, see Section 17.9.3, “Known Issues in MySQL Cluster Replication”.

  • RBL, RBR, and temporary tables.  As noted elsewhere in this chapter (see Section 16.3.1.22, “Replication and Temporary Tables”), temporary tables are not replicated when using the row-based format. However, you can use the mixed format; when mixed format is in effect, “safe” statements involving temporary tables are logged using the statement-based format. For more information, see Section 16.1.2.1, “Comparison of Statement-Based and Row-Based Replication”.

    ### Note

    There is actually no need to replicate temporary tables when using RBR. In addition, since temporary tables can be read only from the thread which created them, there is seldom if ever any benefit obtained from replicating them, even when using statement-based mode.

  • RBL and the BLACKHOLE storage engine.  Prior to MySQL 5.1.29, DELETE and UPDATE statements did not work with RBL and BLACKHOLE tables. (Bug#38360)

  • RBL and synchronization of non-transactional tables.  When using row-based replication of a MyISAM or other non-transactional table, changed rows are written to the transaction cache. Often, when many rows are affected, the set of changes are split into several events; when the statement commits, all of these events are written to the binary log. When executing on the slave, a table lock is taken on all tables involved, then the rows are applied in batch mode. (This may or may not be effective, depending on the engine used for the slave's copy of the table).

  • Latency and binary log size.  Because RBL writes changes for each row to the binary log, the size of the binary log can grow quite rapidly. When used in a replication environment, this can significantly increase the time required for making the changes on the slave that match those on the master. You should be aware of the potential for this delay in your applications.

  • Reading the binary log.  With the --base64-output=DECODE-ROWS and --verbose options, mysqlbinlog is able to format the contents of the binary log in a manner that is easily human-readable, in case you want to read or recover from a replication or database failure using the contents of the binary log. For more information, see Section 4.6.7.2, “mysqlbinlog Row Event Display”. Before MySQL 5.1.28, this was not possible (Bug#31455).

  • Binary log execution errors and slave_exec_mode If you use slave_exec_mode=IDEMPOTENT, a failure to apply changes from RBL because the original row cannot be found does not trigger an error, and does not cause replication to fail. This means that it is possible that updates are not applied on the slave, so that the master and slave are no longer synchronized. Latency issues and use of non-transactional tables when using slave_exec_mode=IDEMPOTENT and RBR can cause the master and slave to diverge even further. For more information about slave_exec_mode, see Section 5.1.3, “Server System Variables”.

    ### Note

    slave_exec_mode=IDEMPOTENT is generally useful only for circular replication or multi-master replication with MySQL Cluster (see Section 17.9, “MySQL Cluster Replication”). For other scenarios, the default value (slave_exec_mode=STRICT) is normally sufficient.

  • Lack of binary log checksums.  No checksums are used for RBL. This means that network, disk, and other errors may not be identified when processing the binary log. To ensure that data is transmitted without network corruption, you may want to consider using SSL, which adds another layer of checksumming, for replication connections. See Section 5.5.7, “Using SSL for Secure Connections”, for more information about setting up MySQL with SSL.

  • Filtering based on server ID not supported.  A common practice is to filter out changes on some slaves by using a WHERE clause that includes the relation @server_id <> server-id clause with UPDATE and DELETE statements, a simple example of such a clause being WHERE @server_id <> 1. However, this does not work correctly with row-based logging. If you must use the server_id system variable for statement filtering, then you must also use --binlog_format=STATEMENT.

  • Database-level replication options.  The effects of the options --replicate-do-db, --replicate-ignore-db, and --replicate-rewrite-db differ considerably depending on whether row-based or statement-based logging is in use. Because of this, we recommend that you avoid the database-level options and use the table-level options such as --replicate-do-table and --replicate-ignore-table instead. For more information about these options and the impact that your choice of replication format has on how they operate, see Section 16.1.3, “Replication and Binary Logging Options and Variables”.

    bool allow_sql_comments;        /* if on, ignore SQL comments when judging if load balance or query cache
                                     * is possible. If off, SQL comments effectively prevent the judgment
                                     * (pre 3.4 behavior). For backward compatibilty sake, default is off.
                                     */
    /*
     * add for watchdog
     */
    bool use_watchdog;                        /* Enables watchdog */
    WdLifeCheckMethod wd_lifecheck_method;    /* method of lifecheck. 'heartbeat' or 'query' */
    bool clear_memqcache_on_escalation;        /* Clear query cache on shmem when escalating ?*/
    char *wd_escalation_command;            /* Executes this command at escalation on new active pgpool.*/
    char *wd_de_escalation_command;            /* Executes this command when master pgpool goes down.*/
    char *wd_hostname;                        /* watchdog hostname */
    int wd_port;                            /* watchdog port */
    int wd_priority;                        /* watchdog node priority, during leader election*/
    WdRemoteNodesConfig wd_remote_nodes;    /* watchdog lists */
    char * trusted_servers;                    /* icmp reachable server list (A,B,C) */
    char * delegate_IP;                        /* delegate IP address */
    int  wd_interval;                        /* lifecheck interval (sec) */
    char *wd_authkey;                        /* Authentication key for watchdog communication */
    char * ping_path;                        /* path to ping command */
    char * if_cmd_path;                        /* path to interface up/down command */
    char * if_up_cmd;                        /* ifup command */
    char * if_down_cmd;                        /* ifdown command */
    char * arping_path;                        /* path to arping command */
    char * arping_cmd;                        /* arping command */
    int  wd_life_point;                        /* life point (retry times at lifecheck) */
    char *wd_lifecheck_query;                /* lifecheck query */
    char *wd_lifecheck_dbname;                /* Database name connected for lifecheck */
    char *wd_lifecheck_user;                /* PostgreSQL user name for watchdog */
    char *wd_lifecheck_password;            /* password for watchdog user */
    int wd_heartbeat_port;                    /* Port number for heartbeat lifecheck */
    int wd_heartbeat_keepalive;                /* Interval time of sending heartbeat signal (sec) */
    int wd_heartbeat_deadtime;                /* Deadtime interval for heartbeat signal (sec) */
    WdHbIf hb_if[WD_MAX_IF_NUM];            /* interface devices */
    int num_hb_if;                            /* number of interface devices */
    char **wd_monitoring_interfaces_list;    /* network interface name list to be monitored by watchdog */
} POOL_CONFIG;

Note

You should use full path to specify the location of your certificate files.

The options are as follows:

  • ssl-ca identifies the Certificate Authority (CA) certificate.

  • ssl-cert identifies the server public key. This can be sent to the client and authenticated against the CA certificate that it has.

  • ssl-key identifies the server private key.

On the slave, you have two options available for setting the SSL information. You can either add the slaves certificates to the client section of the slave configuration file, or you can explicitly specify the SSL information using the CHANGE MASTER TO statement.

Using the former option, add the following lines to the client section of the slave configuration file:

[client]
ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

Restart the slave server, using the --skip-slave to prevent the slave from connecting to the master. Use CHANGE MASTER TO to specify the master configuration, using the master_ssl option to enable SSL connectivity:

mysql> CHANGE MASTER TO 
    MASTER_HOST='master_hostname', 
    MASTER_USER='replicate', 
    MASTER_PASSWORD='password', 
    MASTER_SSL=1;

To specify the SSL certificate options during the CHANGE MASTER TO command, append the SSL options:

CHANGE MASTER TO 
      MASTER_HOST='master_hostname', 
      MASTER_USER='replicate', 
      MASTER_PASSWORD='password', 
      MASTER_SSL=1, 
      MASTER_SSL_CA = 'ca_file_name', 
      MASTER_SSL_CAPATH = 'ca_directory_name', 
      MASTER_SSL_CERT = 'cert_file_name', 
      MASTER_SSL_KEY = 'key_file_name';

Once the master information has been updated, start the slave replication process:

mysql> START SLAVE;

You can use the SHOW SLAVE STATUS to confirm that SSL connection has been completed.

For more information on the CHANGE MASTER TO syntax, see Section 12.6.2.1, “CHANGE MASTER TO Syntax”.

If you want to enforce SSL connections to be used during replication, then create a user with the REPLICATION SLAVE privilege and use the REQUIRE_SSL option for that user. For example:

mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass' REQUIRE SSL;
  1 [root@zlm2 04:16:24 ~/sysbench-1.0/src/lua]
  2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=1 --table-size=10000000 --mysql-storage-engine=innodb prepare
  3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
  4 
  5 Creating table 'sbtest1'...
  6 Inserting 10000000 records into 'sbtest1'
  7 FATAL: mysql_drv_query() returned error 1114 (The table 'sbtest1' is full) for query 'INSERT INTO sbtest1(k, c, pad) VALUES(5007652, '86766538515-66952496290-52637383744-17777505783-29917382722-84335317264-36504610739-38121689798-84219011968-91366385874', '49260963569-36115667021-10552248239-18067079911-61369170530'),(4980838, '54654185213-72432916652-68658723905-01905687478-16685611449-05853232015-51457796697-51819403372-03987006557-33172822697', '21128589837-49224626065-81618031353-10869323172-08902635377'),(4369690, '19346998374-76157809978-61295763131-66817961402-14155793123-89485702729-52643919933-37547938307-65477074083-42947658759', '48407396301-60012651284-13188093294-65156227928-43584415056'),(4980019, '70453863386-09471491416-32761370166-00808275356-79347375847-13151837625-86471458250-39802434455-19087793196-21946389164', '33264881266-44980565111-26757160090-86077734704-00045098929'),(4997783, '58799482920-02512874911-59302214059-00300283635-35103600246-26397800977-48778245550-17735174970-94350595573-16543920606', '23090791422-15031674988-38742619953-96110910554-14814557148'),(4996542, '02526238751-03201222067-54203245066-96888627735-91568973200-70159837175-05408478565-91790491503-65208127071-01416690185', '49867218536-45143413529-09551369789-59447916184-43851467884'),(5016589, '74498181715-26316533762-09669873675-78321331809-32282301673-46976991777-42037713521-13856469837-75059942943-62359417982', '17496574432-77585238967-84065009097-69416190626-30209633909'),(4994933, '71859258017-48270005429-16959639772-39718531870-78995154112-71510567312-49374130735-00512159149-93514688119-69243734179', '23940826906-68127513411-04170272492-93173723876-07024484156'),(5024203, '01612812086-88501262208-29821147265-27833323182-26465297118-67798980074-90884201988-32250326631-52571482980-27120623387', '32676071422-40807168337-23971263649-30743049902-70369274393'),(4162450, '82998769803-54845523696-25503288553-48015207428-64262352808-97661531385-74384257021-19880800516-86393457605-98507752399', '02796854361-18464776198-06166096636-53988338130-49624903182'),(4995444, '66477706887-39253236907-03545461775-18565950790-70596896493-96343527077-27384490842-67789533300-13229220655-04716282051', '93177901449-78610249192-43379190612-81683429736-42624094282'),(5014023, '00026114498-69425370048-46519646170-15138611515-26550011239-94540841776-52595856582-34808319037-14376366590-67052294983', '56614501773-95380362876-81940933213-48069389750-96896566709'),(5024261, '49006870590-81308017607-77748035537-80561018761-10903078210-73474291600-34790301926-33257007983-62587725038-62428934340', '19026821007-38039245675-09902375187-72268734965-98944641058'),(5024236, '10110443501-28024080051-93513912505-09437560828-34230239659-09523768623-23688243983-59021499845-46898378159-07771020838', '24997608907-31970368651-18989687877-75418790951-46453076386'),(5049980, '19429198356-67366238484-55380613354-35977328479-83088969020-14656225800-25216048033-45250663251-98396746788-21125067075', '71090143904-45428036794-81542588223-10154666659-75546296798'),(5471904, '75703864024-12238744221-17802200544-33445694372-23795426538-35115713085-36013837723-42595287843-50130126814-13689292194', '33729585352-83996219254-04756782645-33242342933-26629849933'),(5019788, '26736440482-52751355069-31401045555-46875459054-74471530241-80094262346-04701622365-49412060729-73926195317-10568100029', '94218352951-20508982927-74070061682-74623136939-21804519403'),(5021303, '68369236188-31813582854-91111181432-49899358000-41024394879-31154794562-68384652868-38150046317-87574937898-60778176522', '57835818598-56026673181-41314509499-62765615632-08938407644'),(5015123, '67124253129-45231369455-80389246483-30556743024-23531555287-47922585120-65610495252-46796320051-45675319664-05751522029', '17364348778-61672427639-08537678844-68212837883-00352572907'),(3676715, '05788144973-64579114586-22354309091-20803641999-44828403219-79251514115-97961584283-16337702597-51136491029-67923490682', '13053760861-60814974240-44320882636-73693584211-05836315795'),(5236763, '68612629880-77489830256-75974497057-76984695030-FATAL: `sysbench.cmdline.call_command' function failed: ./oltp_common.lua:230: db_bulk_insert_next() failed
  8 
  9 //Master
 10 [root@zlm2 04:33:54 ~/sysbench-1.0/src/lua]
 11 #df -h
 12 Filesystem               Size  Used Avail Use% Mounted on
 13 /dev/mapper/centos-root  8.4G  8.4G   68M 100% / //The disk space of master is full.
 14 devtmpfs                 488M     0  488M   0% /dev
 15 tmpfs                    497M     0  497M   0% /dev/shm
 16 tmpfs                    497M  6.6M  491M   2% /run
 17 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
 18 /dev/sda1                497M  118M  379M  24% /boot
 19 none                      87G   80G  6.6G  93% /vagrant
 20 
 21 //Slave
 22 [root@zlm3 04:34:17 ~]
 23 #df -h
 24 Filesystem               Size  Used Avail Use% Mounted on
 25 /dev/mapper/centos-root  8.4G  8.4G   20K 100% / //The disk space of slave is full,too.
 26 devtmpfs                 488M     0  488M   0% /dev
 27 tmpfs                    497M     0  497M   0% /dev/shm
 28 tmpfs                    497M  6.5M  491M   2% /run
 29 tmpfs                    497M     0  497M   0% /sys/fs/cgroup
 30 /dev/sda1                497M  118M  379M  24% /boot
 31 none                      87G   80G  6.6G  93% /vagrant
 32 
 33 //Check the output logfile.
 34 [root@zlm2 04:18:21 ~]
 35 #tail monitor.log 
 36 1.00s [  6.40s,  1.65s,  0.55s ]
 37 0.00s [  6.26s,  1.65s,  0.55s ]
 38 0.99s [  6.13s,  1.65s,  0.55s ]
 39 1.99s [  6.00s,  1.66s,  0.55s ]
 40 2.99s [  5.86s,  1.67s,  0.56s ]
 41 3.99s [  5.73s,  1.68s,  0.56s ]
 42 4.99s [  5.59s,  1.70s,  0.57s ]
 43 6.00s [  5.46s,  1.72s,  0.57s ]
 44 4.01s [  5.29s,  1.73s,  0.58s ]
 45 4.99s [  5.16s,  1.75s,  0.58s ]
 46 
 47 //Long time later.
 48 
 49 [root@zlm2 04:59:24 ~]
 50 #tail monitor.log 
 51 1769.00s [ 1739.50s, 1619.50s, 1319.50s ]
 52 1769.99s [ 1740.50s, 1620.50s, 1320.50s ]
 53 1770.99s [ 1741.50s, 1621.50s, 1321.50s ]
 54 1771.99s [ 1742.50s, 1622.50s, 1322.50s ]
 55 1772.99s [ 1743.50s, 1623.50s, 1323.50s ]
 56 1773.99s [ 1744.50s, 1624.50s, 1324.50s ]
 57 1774.99s [ 1745.50s, 1625.50s, 1325.50s ]
 58 1775.99s [ 1746.50s, 1626.50s, 1326.50s ]
 59 1776.99s [ 1747.50s, 1627.50s, 1327.50s ]
 60 1778.00s [ 1748.50s, 1628.50s, 1328.50s ]
 61 
 62 [root@zlm2 05:02:54 ~]
 63 #tail monitor.log 
 64 2122.00s [ 2092.50s, 1972.50s, 1672.50s ]
 65 2123.00s [ 2093.50s, 1973.50s, 1673.50s ]
 66 2124.00s [ 2094.50s, 1974.50s, 1674.50s ]
 67 2125.00s [ 2095.50s, 1975.50s, 1675.50s ]
 68 2126.00s [ 2096.50s, 1976.50s, 1676.50s ]
 69 2127.00s [ 2097.50s, 1977.50s, 1677.50s ]
 70 2127.99s [ 2098.50s, 1978.50s, 1678.50s ]
 71 2129.00s [ 2099.50s, 1979.50s, 1679.50s ]
 72 2129.99s [ 2100.50s, 1980.50s, 1680.50s ]
 73 2131.00s [ 2101.50s, 1981.50s, 1681.50s ]
 74 
 75 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 76 ERROR 2013 (HY000): Lost connection to MySQL server during query
 77 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 78 ERROR 2006 (HY000): MySQL server has gone away
 79 No connection. Trying to reconnect...
 80 Connection id:    10
 81 Current database: sysbench
 82 
 83 //The slave has hung because of the lack of disk space.
 84 //The delay continuously increasing what can be seen in the logfile on master.
 85 
 86 #180719  4:31:55 server id 1013306  end_log_pos 2044211         Table_map: `sysbench`.`hb` mapped to number 108
 87 # at 2044211
 88 #180719  4:31:55 server id 1013306  end_log_pos 2044359         Update_rows: table id 108 flags: STMT_END_F
 89 ### UPDATE `sysbench`.`hb`
 90 ### WHERE
 91 ###   @1='2018-07-19T04:31:55.004000' /* VARSTRING(78) meta=78 nullable=0 is_null=0 */
 92 ###   @2=1013306 /* INT meta=0 nullable=0 is_null=0 */
 93 ###   @3='mysql-bin.000019' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
 94 ###   @4=2043294 /* LONGINT meta=0 nullable=1 is_null=0 */
 95 ###   @5=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
 96 ###   @6=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
 97 ### SET
 98 ###   @1='2018-07-19T04:31:55.004400' /* VARSTRING(78) meta=78 nullable=0 is_null=0 */
 99 ###   @2=1013306 /* INT meta=0 nullable=0 is_null=0 */
100 ###   @3='mysql-bin.000019' /* VARSTRING(765) meta=765 nullable=1 is_null=0 */
101 ###   @4=2043294 /* LONGINT meta=0 nullable=1 is_null=0 */
102 ###   @5=NULL /* VARSTRING(765) meta=765 nullable=1 is_null=1 */
103 ###   @6=NULL /* LONGINT meta=0 nullable=1 is_null=1 */
104 # at 2044359
105 #180719  4:31:55 server id 1013306  end_log_pos 2044386         Xid = 94022
106 COMMIT/*!*/;
107 
108 //The output above is the detail of updating heartbeat table "hb" on master.

Note

Ensure that the skip-networking option has not been enabled on your replication master. If networking has been disabled, then your slave will not able to communicate with the master and replication will fail.

    int64 relcache_expire;                    /* relation cache life time in seconds */
    int relcache_size;                        /* number of relation cache life entry */
    bool check_temp_table;                    /* enable temporary table check */
    bool check_unlogged_table;                /* enable unlogged table check */

16.2.3. Using Replication for Scale-Out

You can use replication as a scale-out solution, i.e. where you want to split up the load of database queries across multiple database servers, within some reasonable limitations.

Because replication works from the distribution of one master to one or more slaves, using replication for scaleout works best in an environment where you have a high number of reads and low number of writes/updates. Most websites fit into this category, where users are browsing the website, reading articles, posts, or viewing products. Updates only occur during session management, or when making a purchase or adding a comment/message to a forum.

Replication in this situation enables you to distribute the reads over the replication slaves, while still allowing your web servers to communicate with the replication master when a write is required. You can see a sample replication layout for this scenario in Figure 16.1, “Using replication to improve the performance during scaleout”.

Figure 16.1. Using replication to improve the performance during scaleout

澳门新濠3559 3

If the part of your code that is responsible for database access has been properly abstracted/modularized, converting it to run with a replicated setup should be very smooth and easy. Change the implementation of your database access to send all writes to the master, and to send reads to either the master or a slave. If your code does not have this level of abstraction, setting up a replicated system gives you the opportunity and motivation to clean it up. Start by creating a wrapper library or module that implements the following functions:

  • safe_writer_connect()

  • safe_reader_connect()

  • safe_reader_statement()

  • safe_writer_statement()

safe_ in each function name means that the function takes care of handling all error conditions. You can use different names for the functions. The important thing is to have a unified interface for connecting for reads, connecting for writes, doing a read, and doing a write.

Then convert your client code to use the wrapper library. This may be a painful and scary process at first, but it pays off in the long run. All applications that use the approach just described are able to take advantage of a master/slave configuration, even one involving multiple slaves. The code is much easier to maintain, and adding troubleshooting options is trivial. You need modify only one or two functions; for example, to log how long each statement took, or which statement among those issued gave you an error.

If you have written a lot of code, you may want to automate the conversion task by using the replace utility that comes with standard MySQL distributions, or write your own conversion script. Ideally, your code uses consistent programming style conventions. If not, then you are probably better off rewriting it anyway, or at least going through and manually regularizing it to use a consistent style.

 

Note

Because the server gives an existing master.info file precedence over the startup options just described, you might prefer not to use startup options for these values at all, and instead to specify them by using the CHANGE MASTER TO statement. Beginning with MySQL 5.1.17, you must use CHANGE MASTER TO to set the values corresponding to the deprecated options listed earlier in this section.

Startup options for replication slaves.  The following list describes startup options for controlling replication slaves. Many of these options can be reset while the server is running by using the CHANGE MASTER TO statement. Others, such as the --replicate-* options, can be set only when the slave server starts. Replication-related system variables are discussed later in this section.

  • --log-slave-updates

    Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the --log-bin option to enable binary logging. --log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:

    A -> B -> C
    

    Here, A serves as the master for the slave B, and B serves as the master for the slave C. For this to work, B must be both a master and a slave. You must start both A and B with --log-bin to enable binary logging, and B with the --log-slave-updates option so that updates received from A are logged by B to its binary log.

    When using MySQL Cluster Replication prior to MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.13, records for “empty” epochs — that is, epochs in which no changes to NDBCLUSTER data or tables took place — were inserted into the ndb_apply_status and ndb_binlog_index tables on the slave even when --log-slave-updates was disabled (Bug#37472). Beginning with MySQL Cluster NDB 6.3.21 and MySQL Cluster NDB 6.4.1, it is possible to re-enable the older behavior by using the --ndb-log-empty-epochs option.

  • --log-slow-slave-statements

    Version Introduced 5.1.21
    Command Line Format --log-slow-slave-statements
    Config File Format log-slow-slave-statements
    Value Set
    Type boolean
    Default off

    When the slow query log is enabled, this option enables logging for queries that have taken more than long_query_time seconds to execute on the slave.

    This option was added in MySQL 5.1.21.

  • --log-warnings[=level]

    This option causes a server to print more messages to the error log about what it is doing. With respect to replication, the server generates warnings that it succeeded in reconnecting after a network/connection failure, and informs you as to how each slave thread started. This option is enabled by default; to disable it, use --skip-log-warnings. Aborted connections are not logged to the error log unless the value is greater than 1.

    Note that the effects of this option are not limited to replication. It produces warnings across a spectrum of server activities.

  • --master-connect-retry=seconds

    The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the master.info file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of --slave-net-timeout. The number of reconnection attempts is limited by the --master-retry-count option.

    This option is deprecated as of MySQL 5.1.17, and removed as of MySQL 6.0.

  • --master-host=host_name

    The host name or IP number of the master replication server. The value in master.info takes precedence if it can be read. If no master host is specified, the slave thread does not start.

    This option is deprecated as of MySQL 5.1.17, and removed as of MySQL 6.0.

  • --master-info-file=file_name

    The name to use for the file in which the slave records information about the master. The default name is master.info in the data directory.

  • --master-password=password

    The password of the account that the slave thread uses for authentication when it connects to the master. The value in the master.info file takes precedence if it can be read. If not set, an empty password is assumed.

    This option is deprecated as of MySQL 5.1.17, and removed as of MySQL 6.0.

  • --master-port=port_number

    The TCP/IP port number that the master is listening on. The value in the master.info file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).

    This option is deprecated as of MySQL 5.1.17, and removed as of MySQL 6.0.

  • --master-retry-count=count

    The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by --master-connect-retry and reconnects are triggered when data reads by the slave time out according to the --slave-net-timeout option. The default value is 86400.

    You can also set the retry count by using the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement.

  • --master-ssl, --master-ssl-ca=file_name, --master-ssl-capath=directory_name, --master-ssl-cert=file_name, --master-ssl-cipher=cipher_list, --master-ssl-key=file_name

    These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding --ssl, --ssl-ca, --ssl-capath, --ssl-cert, --ssl-cipher, --ssl-key options that are described in Section 5.5.7.3, “SSL Command Options”. The values in the master.info file take precedence if they can be read.

    These options are deprecated as of MySQL 5.1.17, and removed as of MySQL 6.0.

  • --master-user=user_name

    The user name of the account that the slave thread uses for authentication when it connects to the master. This account must have the REPLICATION SLAVE privilege. The value in the master.info file takes precedence if it can be read. If the master user name is not set, the name test is assumed.

    This option is deprecated as of MySQL 5.1.17, and removed as of MySQL 6.0.

  • --max-relay-log-size=size

    The size at which the server rotates relay log files automatically. For more information, see Section 16.4.2, “Replication Relay and Status Files”. The default size is 1GB.

  • --read-only

    Cause the slave to allow no updates except from slave threads or from users having the SUPER privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply to TEMPORARY tables.

  • --relay-log=file_name

    The basename for the relay log. The default basename is host_name-relay-bin. The server creates relay log files in sequence by adding a numeric suffix to the basename.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.

    If you specify this option, the value specified is also used as the basename for the relay log index file. You can override this behavior by specifying a different relay log index file basename using the --relay-log-index option.

    You may find the --relay-log option useful in performing the following tasks:

    • Creating relay logs whose names are independent of host names.

    • If you need to put the relay logs in some area other than the data directory, because your relay logs tend to be very large and you do not want to decrease max_relay_log_size.

    • To increase speed by using load-balancing between disks.

  • --relay-log-index=file_name

    The name to use for the relay log index file. The default name is host_name-relay-bin.index in the data directory, where host_name is the name of the slave server.

    Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log-index option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.

    If you specify this option, the value specified is also used as the basename for the relay logs. You can override this behavior by specifying a different relay log file basename using the --relay-log option.

  • --relay-log-info-file=file_name

    The name to use for the file in which the slave records information about the relay logs. The default name is relay-log.info in the data directory.

  • --relay-log-purge={0|1}

    Disable or enable automatic purging of relay logs as soon as they are no longer needed. The default value is 1 (enabled). This is a global variable that can be changed dynamically with SET GLOBAL relay_log_purge = N.

  • --relay-log-space-limit=size

    This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means “no limit.” This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs, because not doing so would cause a deadlock. You should not set --relay-log-space-limit to less than twice the value of --max-relay-log-size (or --max-binlog-size if --max-relay-log-size is 0). In that case, there is a chance that the I/O thread waits for free space because --relay-log-space-limit is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to ignore --relay-log-space-limit temporarily.

  • --replicate-do-db=db_name

    The effects of this option depend on whether statement-based or row-based replication is in use.

    Statement-based replication.  Tell the slave to restrict replication to statements where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not replicate cross-database statements such as UPDATE some_db.some_table SET foo='bar' while a different database (or no database) is selected.

    ### Warning

    To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

    An example of what does not work as you might expect when using statement-based replication: If the slave is started with --replicate-do-db=sales and you issue the following statements on the master, the UPDATE statement is not replicated:

    USE prices;
    UPDATE sales.january SET amount=amount 1000;
    

    The main reason for this “check just the default database” behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

    Row-based replication.  Tells the slave to restrict replication to database db_name. Only tables belonging to db_name are changed; the current database has no effect on this. For example, suppose that the slave is started with --replicate-do-db=sales and row-based replication is in effect, and then the following statements are run on the master:

    USE prices;
    UPDATE sales.february SET amount=amount 100;
    

    The february table in the sales database on the slave is changed in accordance with the UPDATE statement; this occurs whether or not the USE statement was issued. However, issuing the following statements on the master has no effect on the slave when using row-based replication and --replicate-do-db=sales:

    USE prices;
    UPDATE prices.march SET amount=amount-25;
    

    Even if the statement USE prices were changed to USE sales, the UPDATE statement's effects would still not be replicated.

    Another important difference in how --replicate-do-db is handled in statement-based replication as opposed to row-based replication occurs with regard to statements that refer to multiple databases. Suppose the slave is started with --replicate-do-db=db1, and the following statements are executed on the master:

    USE db1;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    

    If you are using statement-based replication, then both tables are updated on the slave. However, when using row-based replication, only table1 is affected on the slave; since table2 is in a different database, table2 on the slave is not changed by the UPDATE. Now suppose that, instead of the USE db1 statement, a USE db4 statement had been used:

    USE db4;
    UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
    

    In this case, the UPDATE statement would have no effect on the slave when using statement-based replication. However, if you are using row-based replication, the UPDATE would change table1 on the slave, but not table2 — in other words, only tables in the database named by --replicate-do-db are changed, and the choice of current database has no effect on this behavior.

    If you need cross-database updates to work, use --replicate-wild-do-table=db_name.% instead. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

    ### Note

    This option effects replication in the same manner that --binlog-do-db affects binary logging, and the affects of the replication format on how --replicate-do-db affects replication behavior are the same as those of the logging format on the behavior of --binlog-do-db.

  • --replicate-ignore-db=db_name

    As with --replicate-do-db, the effects of this option depend on whether statement-based or row-based replication is in use.

    Statement-based replication.  Tells the slave to not replicate any statement where the default database (that is, the one selected by USE) is db_name.

    Row-based replication.  Tells the slave not to update any tables in the database db_name. The current database has no effect.

    When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with --replicate-ignore-db=sales and you issue the following statements on the master:

    USE prices;
    UPDATE sales.january SET amount=amount 1000;
    

    The UPDATE statement is replicated in such a case because --replicate-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, the UPDATE statement's effects are not propagated to the slave, and the slave's copy of the sales.january table is unchanged; in this instance, --replicate-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored by the slave.

    To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

    You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

    If you need cross-database updates to work, use --replicate-wild-ignore-table=db_name.% instead. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

    ### Note

    This option effects replication in the same manner that --binlog-ignore-db affects binary logging, and the affects of the replication format on how --replicate-ignore-db affects replication behavior are the same as those of the logging format on the behavior of --binlog-ignore-db.

  • --replicate-do-table=db_name.tbl_name

    Tells the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-do-db. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

  • --replicate-ignore-table=db_name.tbl_name

    Tells the slave thread to not replicate any statement that updates the specified table, even if any other tables might be updated by the same statement. To specify more than one table to ignore, use this option multiple times, once for each table. This works for cross-database updates, in contrast to --replicate-ignore-db. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

  • --replicate-rewrite-db=from_name->to_name

    Tells the slave to translate the default database (that is, the one selected by USE) to to_name if it was from_name on the master. Only statements involving tables are affected (not statements such as CREATE DATABASE, DROP DATABASE, and ALTER DATABASE), and only if from_name is the default database on the master. This does not work for cross-database updates. To specify multiple rewrites, use this option multiple times. The server uses the first one with a from_name value that matches. The database name translation is done before the --replicate-* rules are tested.

    If you use this option on the command line and the “>” character is special to your command interpreter, quote the option value. For example:

    shell> mysqld --replicate-rewrite-db="olddb->newdb"
    
  • --replicate-same-server-id

    To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave does not skip events having its own server ID. Normally, this is useful only in rare configurations. Cannot be set to 1 if --log-slave-updates is used. By default, the slave I/O thread does not write binary log events to the relay log if they have the slave's server ID (this optimization helps save disk usage). If you want to use --replicate-same-server-id, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute.

  • --replicate-wild-do-table=db_name.tbl_name

    Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the “%” and “_” wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates. See Section 16.4.3, “How Servers Evaluate Replication Rules”.

    Example: --replicate-wild-do-table=foo%.bar% replicates only updates that use a table where the database name starts with foo and the table name starts with bar.

    If the table name pattern is %, it matches any table name and the option also applies to database-level statements (CREATE DATABASE, DROP DATABASE, and ALTER DATABASE). For example, if you use --replicate-wild-do-table=foo%.%, database-level statements are replicated if the database name matches the pattern foo%.

    To include literal wildcard characters in the database or table name patterns, escape them with a backslash. For example, to replicate all tables of a database that is named my_own

    编辑:数据库 本文来源:Percona-Tookit工具包之pt-heartbeat澳门新濠3559:

    关键词: 澳门新濠3559