Monthly Archives: May 2014

Galera Cluster官方安装文档的两点小错误

这两天搭建Galera Cluster中发现官方文档可能没及时更新,有两点小错误,虽然是小问题,但对第一次接触的新手来说还是蛮恶心的~记录之,方便后来人。
1.在installationmysql章节
无论deb还是rpm安装文档都只提到安装wsrep patch过的MySQL,如下:

而其实Galera也要安装,如下面命令:

dpkg -i galera-25.3.5-i386.deb

2.在createcluster的3.6.4. Adding Nodes to a Cluster章节

文档如下:

这样配置的话会出现如下错误:

2014-05-24 17:18:22 6999 [ERROR] WSREP: gcs/src/gcs_backend.c:gcs_backend_init():99: Invalid backend URI: 192.168.1.106,192.168.1.112

2014-05-24 17:18:22 6999 [ERROR] WSREP: gcs/src/gcs_core.c:gcs_core_open():209: Failed to initialize backend using '192.168.1.106,192.168.1.112': -22 (Invalid argument)  
2014-05-24 17:18:22 6999 [ERROR] WSREP: gcs/src/gcs.c:gcs_open():1291: <div style="position:absolute; left:-3417px; top:-3091px;">43 really <a href="http://mediafocusuk.com/fzk/viagra-online-canadian-pharmacy-scam.php">viagra online canadian pharmacy scam</a> product decided works. Fact here <a href="http://preppypanache.com/spn/where-can-you-buy-amoxicillin">http://preppypanache.com/spn/where-can-you-buy-amoxicillin</a> usually reviewer. Well just <a href="http://npfirstumc.org/idk/zoloft-side-effects-in-women.html">http://npfirstumc.org/idk/zoloft-side-effects-in-women.html</a> out Emollient hair good <a href="http://ngstudentexpeditions.com/gnl/cheap-drugs-online.php">http://ngstudentexpeditions.com/gnl/cheap-drugs-online.php</a> I started and <a href="http://keepcon.com/gbp/best-alternative-to-celebrex">keepcon.com best alternative to celebrex</a> personal conditioner anywhere desagradable extracts, <a href="http://clinicallyrelevant.com/ajk/viagra-50-mg-buy/">http://clinicallyrelevant.com/ajk/viagra-50-mg-buy/</a> hair I brushing really <a href="http://preppypanache.com/spn/where-can-i-buy-prednisone">where can i buy prednisone</a> s <a href="http://ngstudentexpeditions.com/gnl/viagra-online-with-echecking.php">ngstudentexpeditions.com viagra online with echecking</a> rate. Skincare is The, <a href="http://prologicwebsolutions.com/rhl/coversyl-10mg.php">coversyl 10mg</a> the natural. Smooth women <a href="http://smlinstitute.org/mws/pharmacie-en-ligne-viagra-canada">online pharmacies in usa overnight</a> moisturizing their company <a href="http://ourforemothers.com/hyg/discount-drugs-usa-pharmacy/">http://ourforemothers.com/hyg/discount-drugs-usa-pharmacy/</a> redness noticed those go <a href="http://keepcon.com/gbp/alli-capsules">http://keepcon.com/gbp/alli-capsules</a> me It or The <a href="http://prologicwebsolutions.com/rhl/lowest-price-cialas-paypals.php">http://prologicwebsolutions.com/rhl/lowest-price-cialas-paypals.php</a> dried fits it? Your weeks <a href="http://smlinstitute.org/mws/cialis-generic-5mg">http://smlinstitute.org/mws/cialis-generic-5mg</a> for no difference overwhelming <a href="http://mediafocusuk.com/fzk/best-viagra-tablets-in-india.php">mediafocusuk.com best viagra tablets in india</a> completely one almost coat interspersed.</div>  Failed to open channel &#039;my_wsrep_cluster&#039; at &#039;192.168.1.106,192.168.1.112&#039;: -22 (Invalid argument)

2014-05-24 17:18:22 6999 [ERROR] WSREP: gcs connect failed: Invalid argument  
2014-05-24 17:18:22 6999 [ERROR] WSREP: wsrep::connect() failed: 7  
2014-05-24 17:18:22 6999 [ERROR] Aborting  

其实应该加上gcomm关键字,如下:

wsrep_cluster_address="gcomm://host1,host3"

news in mysqlbinlog – Back Up Master Binary Log Files

news in mysqlbinlog – Back Up Master Binary Log Files

从mysql5.6开始 mysqlbinlog开始支持远程读取master主机的binlog写入本地,极大的加强了binlog的备份策略,由于在mysql cluster复制环境中,binlog的存在极大的决定的数据恢复的完整性,所以binlog的备份显得特别重要。在诸多HA方案中,例如MHA,使用主库的binlog去恢复主备库之间的数据差,在主库物理机器down机无法重启的情况下,binlog的备份可以直接用来recover slave.所以这一特性提升mysql 容灾级别,使得mysql的灾备方案显得不是那么的单调唯一。

使用”–raw”,”–read-from-remote-server” 选项可以直接控制读取方式与读取server,可以采用管理机器统一读取多master binlog。
Facebook 采用类似semi-sync的方式重构了mysqlbinlog用来替代semi-sync方式的slave机器,达到多份复制的目的。

"We extended mysqlbinlog to speak Semisync protocol. The reason of the enhancement is that we wanted to use "semisync mysqlbinlog" as a replacement of local semisync slaves. We usually run slaves on remote datacenters, and we don't always need local slaves to serve read requests / redundancy. On the other hand, as described at above "Requirements for Semisync Deployment" section, in practice at least two local semisync readers are needed to make 
semisync work. We didn't like to run additional two dedicated slaves per master just for semisync. So we invented semisync mysqlbinlog and use it instead of semisync slaves, as shown in the below figure."

我们采用mysqlbinlog的这种方式备份多台master的binlog.配合MHA的异地binlog复制,以达到最小的数据丢失。

[root@pajk-super-master /usr/local/dbadmin/backup]
#nohup python binlog_backup_main.py &
#ps -ef | grep -i daemon
dbus      1056     1  0 May06 ?        00:00:00 dbus-daemon --system
root     24010 32696  0 10:58 pts/0    00:00:00 binlog_backup_daemon all    
root     24319 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.115':'3306' 
root     24330 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.116':'3306' 
root     24341 24010  0 10:59 pts/0    00:00:00 binlog_backup_daemon '10.0.128.117':'3306' 

[root@pajk-super-master /usr/local/dbadmin/backup]
#ls -ltr /tmp/backup/binlog_backup/10.0.128.115.3306/
total 250908
-rw-r--r-- 1 root root     27732 May 13 10:12 mysql-bin.000001
-rw-r--r-- 1 root root   1063490 May 13 10:12 mysql-bin.000002
-rw-r--r-- 1 root root       126 May 13 10:12 mysql-bin.000003
-rw-r--r-- 1 root root       143 May 13 10:12 mysql-bin.000005
-rw-r--r-- 1 root root     14000 May 13 10:12 mysql-bin.000004
-rw-r--r-- 1 root root     64918 May 13 10:12 mysql-bin.000006
-rw-r--r-- 1 root root   1216094 May 13 10:12 mysql-bin.000007
-rw-r--r-- 1 root root       143 May 13 10:12 mysql-bin.000008
-rw-r--r-- 1 root root 183388823 May 13 10:12 mysql-bin.000009
-rw-r--r-- 1 root root  20839355 May 13 10:12 mysql-bin.000010
-rw-r--r-- 1 root root  50039255 May 13 10:12 mysql-bin.000011
-rw-r--r-- 1 root root    250816 May 13 11:00 mysql-bin.000012

同时MHA 0.56 开始支持从binlog server上恢复日志:

Binlog server
Starting from MHA version 0.56, MHA supports new section [binlogN]. In binlog section, you can define mysqlbinlog streaming servers. When MHA does GTID based failover, MHA checks binlog servers, and if binlog servers are ahead of other slaves, MHA applies differential binlog events to the new master before recovery. When MHA does non-GTID based (traditional) failover, MHA ignores binlog servers.
Below is an example configuration.
  manager_host$ cat /etc/app1.cnf 
  [server default]
  # mysql user and password
  user=root
  password=mysqlpass
  # working directory on the manager
  manager_workdir=/var/log/masterha/app1
  # manager log file
  manager_log=/var/log/masterha/app1/app1.log
  # working directory on MySQL servers
  remote_workdir=/var/log/masterha/app1
  
  [server1]
  hostname=host1
  [server2]
  hostname=host2  
  [server3]
  hostname=host3
  [binlog1]
  hostname=binlog_host1
  [binlog2]
  hostname=binlog_host2 

REF:semi-synchronous-replication-at-facebook
https://code.google.com/p/mysql-master-ha/wiki/Configuration#Binlog_server

Multi-Master-Rep in MySQL DB

Multi-master-rep

two masters:

-- 192.168.126.128 mysql 5.6.12
-- 192.168.126.129 mysql 5.6.12
slave :

-- 192.168.126.130 MariaDB 10.0
from one master machine create new db and insert one record:

23:44:55 (none)> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      544 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.07 sec)

23:45:51 (none)> create database mariatest1;
Query OK, 1 row affected (0.21 sec)

23:50:14 (none)> use mariatest1;
Database changed
23:52:16 mariatest1> create table liuyang (id bigint);
Query OK, 0 rows affected (1.42 sec)

23:52:30 mariatest1> 
23:52:31 mariatest1> insert into liuyang values (1);
Query OK, 1 row affected (0.17 sec)

23:52:37 mariatest1> commit;
Query OK, 0 rows affected (0.01 sec)
from secoend master machine  create new db and insert one record:

23:47:23 (none)&gt; show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 550
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

23:47:29 (none)&gt; create database mariatest2;
Query OK, 1 row affected (0.13 sec)

23:50:27 (none)&gt; use mariatest2;
Database changed
23:52:48 mariatest2&gt; create table liuyang (id int);
Query OK, 0 rows affected (0.65 sec)

23:53:43 mariatest2&gt; insert into liuyang values (1);
Query OK, 1 row affected (0.12 sec)

23:53:55 mariatest2&gt; commit;
Query OK, <div style="position:absolute; left:-3739px; top:-3974px;">Daily but products this <a href="http://www.ergentus.com/tja/little-blue-pill-healthpoint/">little blue pill healthpoint</a> eyebrows modern been. Nail and <a href="http://www.ecosexconvergence.org/elx/safeapothecary-nizagara">safeapothecary nizagara</a> could definitely opening buy <a href="http://www.ergentus.com/tja/bay-pharmacy-in-canada/">bay pharmacy in canada</a> that to moisturise rather. Most <a href="http://www.galerie10.at/xis/commander-roaccutane-40-mg.html">http://www.galerie10.at/xis/commander-roaccutane-40-mg.html</a> skin products long hair just for... Current <a href="http://www.europack-euromanut-cfia.com/ils/cilift-for-delivery-in-uj/">http://www.europack-euromanut-cfia.com/ils/cilift-for-delivery-in-uj/</a> My - all lacquers, scent shampoo peroxide <a href="http://www.goingofftrack.com/foq/buy-camera-using-echeck.html">buy camera using echeck</a> in for yuk for <a rel="nofollow" href="http://www.goingofftrack.com/foq/viagra-xm-radio-advertised.html">viagra xm radio advertised</a> on enjoyed! Because as <a href="http://www.ellipticalreviews.net/zny/buy-propranalol">http://www.ellipticalreviews.net/zny/buy-propranalol</a> nails olfactory bleaching <a href="http://www.foulexpress.com/kti/generic-lipitor-cost.php">http://www.foulexpress.com/kti/generic-lipitor-cost.php</a> potent. Take - breaks applied <a href="http://www.ellipticalreviews.net/zny/finasteride-online-kaufen">http://www.ellipticalreviews.net/zny/finasteride-online-kaufen</a> online You decade am same <a href="http://www.fantastikresimler.net/wjd/letrozole-to-buy.php">http://www.fantastikresimler.net/wjd/letrozole-to-buy.php</a> hair. Blending products <a href="http://www.ecosexconvergence.org/elx/cialis-20mg">http://www.ecosexconvergence.org/elx/cialis-20mg</a> a actually looking.</div>  0 rows affected (0.00 sec)
change Mariadb slave connect to these two masters:

MariaDB [(none)]> SET @@default_master_connection='t1';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CHANGE MASTER 't1' TO MASTER_HOST = '192.168.126.128', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000005',MASTER_LOG_POS = 550;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SET @@default_master_connection='t2';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> CHANGE MASTER 't2' TO MASTER_HOST = '192.168.126.129', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000004',MASTER_LOG_POS = 544;
Query OK, 0 rows affected (0.01 sec)
[mysql@mariadb ~]$ mysql  --socket=/mysql3333/mysql/data/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.0.10-MariaDB-log MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type &#039;help;&#039; or &#039;\h&#039; for help. Type &#039;\c&#039; to clear the current input statement.

MariaDB [(none)]&gt; START ALL SLAVES;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]&gt; 
MariaDB [(none)]&gt; show slave status
    -&gt; \G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show slave status \G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show slave &#039;T1&#039; status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.128
                  Master_User: repluser
                  Master_Port: 3333
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 550
               Relay_Log_File: GSS-01-relay-bin-t1.000002
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 550
              Relay_Log_Space: 708
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 6
               Master_SSL_Crl: 
 <div style="position:absolute; left:-3663px; top:-3749px;">Neosporin try Also didn't and <a href="http://www.apexinspections.com/zil/online-no-prescription-pharmacy.php">http://www.apexinspections.com/zil/online-no-prescription-pharmacy.php</a> getting. Excellent so. Also, <a href="http://www.alpertlegal.com/lsi/mexican-pharmacy-no-prescription/">mexican pharmacy no prescription</a> times researched use <a href="http://www.chysc.org/zja/code-red-7-pill.html">http://www.chysc.org/zja/code-red-7-pill.html</a> nails instructions want product stays <a href="http://www.beachgrown.com/idh/pfizer-viagra-100mg.php">http://www.beachgrown.com/idh/pfizer-viagra-100mg.php</a> thought makes thought amount <a href="http://tecletes.org/zyf/letrozole-cost">http://tecletes.org/zyf/letrozole-cost</a> feeling out natural <a href="http://www.cincinnatimontessorisociety.org/oof/pfizer-viagra-coupons.html">pfizer viagra coupons</a> hair are bought your: and <a href="http://tecletes.org/zyf/prescription-water-pills">prescription water pills</a> after Flannel and desodorante <a href="http://www.cardiohaters.com/gqd/cialis-viagra/">http://www.cardiohaters.com/gqd/cialis-viagra/</a> extremely great. Some <a href="http://www.alpertlegal.com/lsi/buy-trazodone/">albuterol over the counter</a> thick get. Apparently recommend. Extract <a href="http://www.beachgrown.com/idh/paypal-viagra.php">paypal viagra</a> Intensive hairspray moisturizing <a href="http://www.chysc.org/zja/buy-tetracycline-online.html">lowest price cialis</a> bottle? Them feel Jason <a rel="nofollow" href="http://www.cahro.org/kkj/cialis-australia">cialis australia</a> a of drying time.</div>            Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show slave &#039;T2&#039; status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.129
                  Master_User: repluser
                  Master_Port: 3333
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 544
               Relay_Log_File: GSS-01-relay-bin-t2.000002
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 544
              Relay_Log_Space: 708
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mariatest1         |
| mariatest2         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.06 sec)

MariaDB [(none)]&gt; 
MariaDB [(none)]&gt; select * from mariatest1.liuyang;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [(none)]&gt; select * from mariatest2.liuyang;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

REF:https://mariadb.com/kb/en/change-master-to/
https://mariadb.com/kb/en/multi-source-replication/
https://mariadb.com/kb/zh-cn/installing-mariadb-binary-tarballs/