How To Repair MySQL Replication

How To Repair MySQL Replication

If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again.  

1. Identifying The Problem

To find out whether replication is/is not working and what has caused to stop it, you can take a look at the logs. On Debian, for example, MySQL logs to /var/log/syslog:

grep mysql /var/log/syslog

server1:/home/admin# grep mysql /var/log/syslog
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views
May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001079' position 203015142
server1:/home/admin#

You can see what query caused the error, and at what log position the replication stopped.

To verify that the replication is really not working, log in to MySQL:

mysql -u root -p

On the MySQL shell, run:

mysql> SHOW SLAVE STATUS \G

If one of Slave_IO_Running or Slave_SQL_Running is set to No, then the replication is broken:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 1.2.3.4
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001079
        Read_Master_Log_Pos: 269214454
             Relay_Log_File: slave-relay.000130
              Relay_Log_Pos: 100125935
      Relay_Master_Log_File: mysql-bin.001079
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: mydb
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1146
                 Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'.
Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
        SET thread.views = thread.views + aggregate.views
        WHERE thread.threadid = aggregate.threadid'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 203015142
            Relay_Log_Space: 166325247
            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: NULL
1 row in set (0.00 sec)

mysql>

2. Repairing The Replication

Just to go sure, we stop the slave:

mysql> STOP SLAVE;

Fixing the problem is actually quite easy. We tell the slave to simply skip the invalid SQL query:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you'd like to skip two queries, you'd use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.

That's it already. Now we can start the slave again...

mysql> START SLAVE;

... and check if replication is working again:

mysql> SHOW SLAVE STATUS \G

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 1.2.3.4
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.001079
        Read_Master_Log_Pos: 447560366
             Relay_Log_File: slave-relay.000130
              Relay_Log_Pos: 225644062
      Relay_Master_Log_File: mysql-bin.001079
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: mydb
        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: 447560366
            Relay_Log_Space: 225644062
            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
1 row in set (0.00 sec)

mysql>

As you see, both Slave_IO_Running and Slave_SQL_Running are set to Yes now.

Now leave the MySQL shell...

mysql> quit;

... and check the log again:

grep mysql /var/log/syslog

server1:/home/admin# grep mysql /var/log/syslog
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate
May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views
May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146
May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.001079' position 203015142
May 29 11:42:13 http2 mysqld[1380]: 080529 11:42:13 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.001079' at position 203015142, relay log '/var/lib/mysql/slave-relay.000130' position: 100125935
server1:/home/admin#

The last line says that replication has started again, and if you see no errors after that line, everything is ok.

Read More

Fixing MySQL replication after slaves’s relay log was corrupted

MySQL replication on slave (version 5.1.61) has stopped. Slave_IO_Running was marked as Yes, but Slave_SQL_Running as No. Simple stop/start slave didn’t help so further problem analysis was needed. It seemed that current slave’s relay log was corrupted because testing with “mysqlbinlog” has printed out an error. Therefore, the solution was to discard current relay binlogs and to point slave to the last master binlog position.

Here is complete output from show slave status\G on stopped slave server:

               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.79.48
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.002046
          Read_Master_Log_Pos: 639600842
               Relay_Log_File: triton-relay-bin.001957
                Relay_Log_Pos: 243
        Relay_Master_Log_File: mysql-bin.002045
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: pretinac_radio,web
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: web.logging_www,web.logging_raspored,web.web_korisnik
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry.
                               The possible reasons are: the master's binary log is corrupted
                               (you can check this by running 'mysqlbinlog' on the binary log),
                               the slave's relay log is corrupted (you can check this by running
                               'mysqlbinlog' on the relay log), a network problem, or a bug in the
                               master's or slave's MySQL code. If you want to check the master's
                               binary log or slave's relay log, you will be able to know their names
                               by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 103641119
              Relay_Log_Space: 983411603
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1594
               Last_SQL_Error: [the same error description as in Last_Error]

To fix the error, current binlog files on slave should be discarded and set new position. Before setting new binlog position it’s important to remember Relay_Master_Log_File and Exec_Master_Log_Pos values:

Relay_Master_Log_File: mysql-bin.002045
Exec_Master_Log_Pos: 103641119

OK, with this values, new binlog position can be set:

# stop slave
mysql> stop slave;

# make slave forget its replication position in the master's binary log
mysql> reset slave;

# change slave to start reading from stopped position
mysql> change master to master_log_file='mysql-bin.002045', master_log_pos=103641119;

# start slave
mysql> start slave;

Just to note that “reset slave” will delete master.info, relay-log.info and all the relay log files, so it’s not needed to clean leftovers in /var/lib/mysql directory. After all commands were executed, slave has reconnected to the master and start to read SQL statements (Seconds_Behind_Master value was not NULL any more).

Read More

Ubuntu 14.04 not booting after error message. /tmp could not be mounted

Ubuntu 14.04 not booting after error message. /tmp could not be mounted

If you installed Ubuntu 14.04 using WUBI and after installation when it booted it showed an error:

Serious errors were found while checking the disk drive for /. 

There were three options:

press [I] to ignore, press [S] to skip mounting and press [M] to mount manually. 

After pressed I, it showed /tmp could not be mounted and my Ubuntu isn't booting.

Lot of people will get or  got this kind of error after installing Ubuntu 14.04.I also got same error but after some modification i fixed it.

Please Follow This Step 

1. In Windows Boot Manager, select Ubuntu.
2. Press any key and enter GNU Grub2 menu.
3. You can press "e" to edit GRUB2 boot entry.

You need to change the GRUB2 boot entry from "ro" to "rw",

e.g.

linux   /boot/vmlinuz-3.13.0-24-generic root=UUID=AAC884AC1F144321 loop=/ubuntu/disks/root.disk ro   quiet splash $vt_handoff

to

linux   /boot/vmlinuz-3.13.0-24-generic root=UUID=AAC884AC1F144321 loop=/ubuntu/disks/root.disk rw   quiet splash $vt_handoff

Press F10, you can boot in Ubuntu 14.04.

and you can fix GRUB2 boot entry:

sudo vi /etc/grub.d/10_lupin

Change the line:

linux   ${rel_dirname}/${basename} root=${LINUX_HOST_DEVICE} loop=${loop_file_relative} ro ${args}

to:

linux   ${rel_dirname}/${basename} root=${LINUX_HOST_DEVICE} loop=${loop_file_relative} rw ${args}

Regenerate GRUB2 boot entry:(type in terminal)

sudo update-grub

After this all step surely you can use your new ubuntu 

So enjoy!!!!!!!!!!!!!!!!!!!!!!

Read More

Dumping and importing from/to MySQL in an UTF-8 safe way

In a nutshell: to avoid your shell character set from messing with imports, use -r to export and SOURCE when importing.

Dumping safely

COPY
# Do not do this, since it might screw up encoding
mysqldump -uroot -p database > utf8.dump # this is bad

Better do:

COPY
mysqldump -uroot -p database -r utf8.dump

Note that when your MySQL server is not set to UTF-8 you need to do mysqldump --default-character-set=latin1 (!) to get a correctly encoded dump. In that case you will also need to remove the SET NAMES='latin1' comment at the top of the dump, so the target machine won't change its UTF-8 charset when sourcing.

If you only want to dump the structure without data, use

COPY
mysqldump -uroot -p --no-data database -r utf8.dump

Importing a dump safely

COPY
# Do not do this, since it might screw up encoding
mysql -u username -p database < dump_file # this is bad

Better do:

COPY
mysql -uroot -p --default-character-set=utf8 database
mysql> SET names 'utf8'
mysql> SOURCE utf8.dump

Read More

[Monitoring] Cài đặt Nagios trên CentOS7

[root@svr-alert ~]#yum update -y && yum groupinstall "Development Tools" -y
[root@svr-alert ~]#yum install vim wget net-tools -y
[root@svr-alert ~]#vim /etc/sysconfig/selinux => Disabled
[root@svr-alert ~]# systemctl stop firewalld
[root@svr-alert ~]#systemctl disable firewalld

[1] Cài package:

[root@svr-alert ~]#yum install perl wget httpd php gcc glibc glibc-common gd gd-devel
[root@svr-alert ~]#yum install openssl-devel xinetd make net-snmp libpng-devel libjpeg-turbo-devel

[2]Tạo user:

[root@svr-alert ~]# useradd -m nagios
[root@svr-alert ~]# passwd nagios (nagios)
[root@svr-alert ~]# groupadd nagcmd
[root@svr-alert ~]# usermod -a -G nagcmd nagios && usermod -a -G nagcmd apache

[3]Cài đặt Nagios Core:

[root@svr-alert ~]# wget https://assets.nagios.com/downloads/nagioscore/releases/nagios-4.3.4.tar.gz
[root@svr-alert ~]# tar -zxvf nagios-4.3.4.tar.gz
[root@svr-alert ~]# cd nagios-4.3.4
[root@svr-alert nagios-4.3.4]#./configure –-with-command-group=nagcmd
[root@svr-alert nagios-4.3.4]#make all
[root@svr-alert nagios-4.3.2]#make install
[root@svr-alert nagios-4.3.4]#make install-init
[root@svr-alert nagios-4.3.4]#make install-config
[root@svr-alert nagios-4.3.4]#make install-commandmode
[root@svr-alert nagios-4.3.4]#make install-webconf

Đặt password login Nagios web:

[root@svr-alert nagios-4.3.4]#htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin (nagiosa123)
[root@svr-alert nagios-4.3.4]#systemctl restart httpd

[4]Cài đặt Nagios plugin:

[root@svr-alert ~]# wget https://nagios-plugins.org/download/nagios-plugins-2.2.1.tar.gz
[root@svr-alert ~]# tar xzvf nagios-plugins-2.2.1.tar.gz
[root@svr-alert ~]# cd nagios-plugins-2.2.1
[root@svr-alert nagios-plugins-2.2.1]#yum install bind-utils net-snmp net-snmp-devel net-snmp-utils net-snmp-perl perl-Net-SNMP
[root@svr-alert nagios-plugins-2.2.1]#./configure --with-nagios-user=nagios --with-nagios-group=nagios
[root@svr-alert nagios-plugins-2.2.1]#make && make install

[5]Cài NRPE (more):
Trong trường hợp bạn có dùng check_nrpe thì cài như sau:

[root@svr-alert ~]# tar -zxvf nrpe-3.2.0.tar.gz
[root@svr-alert ~]# cd nrpe-3.2.0
[root@svr-alert nrpe-3.2.0]#./configure
[root@svr-alert nrpe-3.2.0]#make all
[root@svr-alert nrpe-3.2.0]#make install-plugin

• Add check_nrpe trong command.cfg

[root@svr-alert ~]#vim /usr/local/nagios/etc/objects/commands.cfg

• #Add command end file:

define command{
 command_name check_nrpe
 command_line $USER1$/check_nrpe -H $HOSTADDRESS$ -c $ARG1$
 }

• Test “check_nrpe”:

#/usr/local/nagios/libexec/check_nrpe -H IP_remote => Output: NRPE v.xxx => OK

[6]Khởi start dịch vụ:

[root@svr-alert ~]# systemctl restart httpd
[root@svr-alert ~]# systemctl enable httpd
[root@svr-alert ~]# chkconfig --add nagios
[root@svr-alert ~]# chkconfig --level 35 nagios on

• Kểm tra config

[root@svr-alert ~]# /usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
[root@svr-alert ~]# systemctl start nagios

Read More

Một cách để phục hồi mysql replication break

Giới thiệu

Khi mysql replication bị hỏng thì có rất nhiều cách để xử lý nhưng có một cách tốt hơn cả là có thể bê nguyên toàn bộ data của master về slave ngay lập tức và tái lập replication bắt đầu tại thời điểm chuyển toàn bộ data sang. Rất may, percona xtrabackup có thể được tận dụng để làm việc đó.

Một số điểm chú ý

• Bạn không cần phải down master nhưng vẫn cần phải down slave. Thời gian slave down cũng không quá lâu nên nói chung là giải pháp này thực hiện online được ( nếu table là innodb hoàn toàn )
• Bạn có thể sửa hầu như mọi lỗi khiến replication bị hỏng mà không cần biết lỗi đó là gì.
• Thời gian thực hiên toàn bộ quá trình phục hồi được đo bằng = thời gian backup master + rsync backup sang slave + thời gian restore trên slave. Tốc độ backup và restore của percona xtrabackup khá cao vì có thể tận dụng khả năng xử lý đa luồng của CPU để thực hiện song song. Thường thì master và slave kết nối qua LAN nên tốc độ rsync cũng không thể quá thấp được. Vấn đề còn lại chỉ là độ lớn của data. Percona xtrabackup có hỗ trợ nén data nhưng khi đó lại phụ trội thời gian nén và xả nén. Tôi thì chưa sử dụng giải pháp này cho các data quá lớn nên chưa thấy tác động về thời gian.
• Percona xtrabackup là online backup. Nhưng chỉ áp dụng được với innodb table. Với các myisam table thì xtrabackup cần phải lock tables một chút. Do đó, bạn cần cân nhắc sử dụng khi có table myisam.
• Tôi đã áp dụng giải pháp này với mysql replication GTID và non-GTID thành công. Với các cơ sở dữ liệu khác như mariadb, percona, tôi chưa sử dụng bao giờ.
• Percona xtrabackup phải cùng được cài đặt trên cả master và slave.
• Master phải đủ không gian disk để chứa bản backup (Một bản backup không nén có dung lượng tương đương với data dir của master). Điều tương tự với slave. Nếu điều kiện này không đảm bảo thì bạn sẽ không thể tạo ra được bản backup trên master cũng như không thể chứa được backup rsync về trên slave. Một giải pháp cho vấn đề này là sử dụng một storage rồi mount storage đó lên cả master và slave.
• Mysql replication trong bài viết này sử dụng GTID. Trường hợp non-GTID bạn có thể làm tương tự.

Thực hiện

Cài đặt percona xtrabackup trên hai server

Trên master và slave

wget http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

sudo rpm -ivh percona-release-0.1-3.noarch.rpm

sudo yum install percona-xtrabackup

Backup trên master

sudo innobackupex  --user=root --password=<passwd of root>  --parallel=12 /data/backup/mysql/

Bạn không nên đặt giá trị parallel vượt quá tổng số core của CPU mà bạn có.

Kết quả của câu lệnh trên là: Folder 2015-06-11_14-38-08/ được tạo ra. Tên folder có dạng timestamp rất tiện lợi cho lưu trữ. Trong folder đó có một file rất quan trọng là xtrabackup_binlog_info

cat xtrabackup_binlog_info

mysql-bin.000092        63416014        ead3ffdd-0684-11e4-b2b3-00a0d1ea853c:1-8788345

Thông tin tương tự có thể tìm thấy trong output của câu lệnh backup

innobackupex: MySQL binlog position: filename 'mysql-bin.000092', position 63416014, GTID of the last change 'ead3ffdd-0684-11e4-b2b3-00a0d1ea853c:1-8788345'

Vì mysql replication sử dụng GTID nên thông tin binlog có transaction id. Nếu sử dụng mysql replication non-GTID, bạn sẽ chỉ tìm thấy có log file và log pos trong xtrabackup_binlog_info

Rsync sang slave

sudo rsync -avzP 2015-06-11_14-38-08   test@192.168.3.100:/home/test

Apply backup trên slave

sudo innobackupex  --use-memory=4G --apply-log /home/test/2015-06-11_14-38-08/

Hai file ib_logfile0 và ib_logfile1 sẽ được tạo ra trong thư mục backup

Restore trên slave
Tắt slave - Đến thời điểm này slave mới bị down

sudo service mysqld stop

Backup master.info trong datadir của slave

sudo cp master.info ~

Xóa nội dung trong datadir của slave

sudo rm -rf /var/lib/mysql/*

Sau đó restore

sudo innobackupex --copy-back --parallel=12  /home/test/2015-06-11_14-38-08/

Gán lại quyền:

sudo chown -R mysql:mysql /var/lib/mysql/

Khởi động slave

sudo service mysqld start

mysql> SET GLOBAL gtid_purged="ead3ffdd-0684-11e4-b2b3-00a0d1ea853c:1-8788345";

Giá trị truyền vào gtid_purged lấy từ xtrabackup_binlog_info

mysql> CHANGE MASTER TO MASTER_HOST='12.34.56.789',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=  107;

Trường hợp mysql replication non-GTID, bạn không cần set global gtid_purged chỉ cần change master to dùng thông tin kết nối trong master.info và thông tin log file, log pos trong xtrabackup_binlog_info để tái lập replication.

mysql> START SLAVE;

Kết thúc bài viết. Hi vọng bài viết giúp ích cho các bạn khi xử lý các mysql replication break.

Nguồn tham khảo:

https://www.percona.com/blog/2013/05/09/how-to-create-a-new-or-repair-a-broken-gtid-based-slave-with-percona-xtrabackup/

Read More