mysql replication

由 datahunter 在一, 24/03/2014 - 11:00 發表

最後更新: 2020-07-08

Replication 介紹

MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in its binary logs.

Benefit:

load-balancing strategy is effective if non-updating queries, database backups using a slave server without disturbing the master

Server type

master server

- writes updates to its binary log files

- keep track of log rotation

- all updates to the tables that are replicated should be performed on the master server

slave server

- it informs the master of the position up to which the slave read the logs at its last successful update

(The slave receives any updates that have taken place since that time)

* The master has no knowledge of how many slaves it has or which ones are up to date at any given time.)

- slave keeps trying to connect periodically

Replication 的 Data type

statement-based replication(SBR)[default]

may be issues with replicating stored routines or triggers

row-based replication(RBR){--binlog-format=row}[--binlog-row-event-max-size(1024)]

Instead of sending SQL statements to the slave,

the master writes events to its binary log that indicate how individual table rows are affected.

Three types of logs used in the REPLICATION process

The relay log consists of the events read from the binary log of the master and written by the slave I/O thread.

Events in the relay log are executed on the slave as part of the SQL thread.

The master info log contains status and current configuration information for the slave's connection to the master.

This log holds information on the master host name, login credentials, and coordinates indicating how far the slave has read from the master's binary log.

The relay log info log holds status information about the execution point within the slave's relay log.

Slave Status Logs

a slave server creates several logs that hold the binary log events relayed from the master to the slave,

and to record information about the current status

master.info <-- (Master_Host, Master_User, )

relay-log.info   <-- Relay_Log_Pos,
                         (The current position within the relay log file;)
                         Exec_Master_Log_Pos
                       (equivalent position within the master's binary log file)

* survive a slave server's shutdown

* The status logs are needed to resume replication after you restore the data from the slave

* The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it.

Configure the Master

Step

Modify Config File
Restart Service
建立 slave user 及設定權限
在 Master dump 現有 Data
查看 status

1. Modify Config File

/etc/mysql/my.cnf

[mysqld]
# 要一個上網 IP
#bind-address = 127.0.0.1
bind-address = 192.168.123.51

# Master 多數是用 1, 不過只要係 unique id 即可
server-id      = 1

# 要 replicate 的 binary log 放在那裡
log_bin        = /var/log/mysql/mysql-bin

# 指定那個 DB 要做 replicate
binlog_do_db   = test_db
binlog-do-db   = test_db2

2. Restart Service 後才會生效

service mysql restart

mysqld restart 之後會建立

/var/log/mysql/mysql-bin.index     # 記錄了有什麼 log 檔
/var/log/mysql/mysql-bin.000001    # Log Data

cat /var/log/mysql/mysql-bin.index

/var/log/mysql/mysql-bin.000001

3. 建立 slave user 及設定他的權限

建立 User

CREATE USER 'slave_user'@'192.168.123.52' IDENTIFIED BY 'slavepass';

設定權限

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'192.168.123.52';

FLUSH PRIVILEGES;

Remark: 不能用 'test_db'.*

GRANT REPLICATION SLAVE ON 'test_db'.* TO 'slave_user'@'192.168.123.52';

不過出錯了 ...

ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

原因:

係唔用得 'test_db'.* 的, 一定要用 *.* !!

因為 Server 本身有 Settings "--replicate-do-db=????"

4. 在 Master dump 現有 Data

[方法1]

USE test_db;

FLUSH TABLES WITH READ LOCK;

mysqldump -u root test_db -p > test_db.sql

UNLOCK TABLES;

* --lock-all-tables ( -x) Lock all tables across all databases

* --lock-tables   (-l)              Lock all tables before dumping them
                                           ( locks tables for each database separately
                                           does not guarantee that the tables between databases)

[方法2]

mysqldump --master-data -u root test_db -p > test_db.sql

5. 查看 status

SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |     1974 | test_db      |                  |
+------------------+----------+--------------+------------------+

Configure the Slave

Step

Config file
Load data from master
Setup
Start replication
查看 Status

1. Config file

/etc/mysql/my.cnf

[mysqld]
bind-address            = 192.168.123.52

server-id               = 2
replicate-do-db         = test_db
relay-log               = /var/log/mysql/mysql-relay-bin.log

# slave rotates the relay logs, Default 0 (server uses max_binlog_size)
# max_relay_log_size      = 10M

# 非必要 ( for data backups and crash recovery on the slave)
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = test_db

2. Load data from master

[方法1]

CREATE DATABASE test_db;

source /root/test_db.sql

[方法2]

mysql --default-character-set=utf8 -u root test_db -p < /path/to/test_db.sql

3. Setup

[方法1]

# File 及 Position 是在 master 行 "SHOW MASTER STATUS;" 查看

CHANGE MASTER TO MASTER_HOST='192.168.123.51',
MASTER_USER='slave_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=1974;

[方法2]

CHANGE MASTER TO MASTER_HOST='192.168.123.51',
 MASTER_USER='slave_user',
 MASTER_PASSWORD='password';

4. Start replication

START SLAVE;

5. 查看 Status

SHOW SLAVE STATUS\G;

P.S.

ego (\G) Send command to mysql server, display result Vertical Format.

當 Running 成功時:

Slave_IO_Running: Yes     <-- only if the I/O thread is running and connected
Slave_SQL_Running: Yes    <-- SQL thread for executing events in the relay log is running.

Force the slave delay master N second (MySQL 5.6)

* An event received from the master is not executed until at least N seconds later

( no delay for format description events or log file rotation events )

# N seconds, Default: 0 (即時生效)

CHANGE MASTER TO MASTER_DELAY = N;

Remark

* RESET SLAVE resets the delay to 0

* START SLAVE and STOP SLAVE take effect immediately and ignore any delay

Checking:

show slave status\G

.............
SQL_Delay: N
SQL_Remaining_Delay: M
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
(SHOW PROCESSLIST 都會見到'Slave_SQL_Running_State', 表示有 pending statements)

STOP / START SLAVE

STOP SLAVE;

STOP SLAVE SQL_THREAD;

* stopping only the SQL thread can be useful if you want to perform a backup or other task

STOP SLAVE IO_THREAD;

* ensure that it has processed all updates to a specific point.

Slave Network Timeout Setting

--slave-net-timeout=seconds

The number of seconds to wait for more data from the master before the slave considers the connection broken,

aborts the read, and tries to reconnect.

Master-Master Replication

Master-master replication is actually two master-slave replications.

autoincrement indexes (Ver: 5.0.2)

On the Master 1/Slave 2 add to /etc/my.cnf:

    auto_increment_increment= 2                  # 每次加多少
    auto_increment_offset   = 1                  # increment 後的 offset

On the Master 2/Slave 1 add to /etc/my.cnf:

    auto_increment_increment= 2
    auto_increment_offset   = 2

SHOW VARIABLES LIKE 'auto_inc%';

DOC:

http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html

Troubleshooting

SHOW MASTER STATUS

SHOW SLAVE STATUS

SHOW PROCESSLIST

RESET SLAVE

forget its replication position in the master's binary log. 相當於 clean start

It deletes the master.info and relay-log.info files, all the relay log files

P.S.

要 reset mysql 之後 "SHOW SLAVE STATUS" 才見不到 configure.

SHOW SLAVE HOSTS;

+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3306 |         1 |
+-----------+------+------+-----------+

Issue 1

mysql> SHOW SLAVE STATUS

Last_Errno: 1007
Last_Error: Error 'Can't create database 'test_db'; database exists' on query. Default database: 'test_db'. Query: 'create database test_db'

解決

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

read-only flag

my.cnf

read-only=1

Variable Scope Global

Dynamic Variable Yes

功能

the server permits no updates except from users that have the

* SUPER privilege

* updates performed by slave threads.

作用

to ensure that slaves accept updates only from the master server and not from clients.

Make sure all non-privileged users do not have the SUPER Privilege.

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';

FLUSH PRIVILEGES;

Upgrading Server

Upgrading Replication to 5.0

you should first upgrade the slaves. To upgrade each slave, shut it down, upgrade it to the appropriate 5.0.x version, restart it, and restart replication.

The 5.0 slave is able to read the old relay logs written prior to the upgrade and to execute the statements they contain.

Relay logs created by the slave after the upgrade are in 5.0 format.

(slaves running MySQL 5.0.x can be used with older masters)

binlog-do-db option is dangerous

* point-in-time recovery

* 出事的 statement

   USE test;
   INSERT INTO DB.Table VALUES (...)

所以不建議用

* replicate-do-db

* binlog-do-db

解決: 在 Slave 上做 filter

replicate-ignore-db

# 一行一個 db
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = testdb

replicate_wild_ignore_table

--replicate-wild-do-table=db_name.tbl_name (my.cnf: replicate_wild_do_table)

Replication slave 1 should use --replicate-wild-do-table=databaseA.%.

Replication slave 2 should use --replicate-wild-do-table=databaseB.%.

Replication slave 3 should use --replicate-wild-do-table=databaseC.%.

* This option applies to tables, views, and triggers. It does not apply to stored procedures and functions

Check Slave replication script

checkmysql.sh

#!/bin/bash

USERNAME="root"
PASSWORD=""
EMAIL=""

MYSQL=`mysql --user=$USERNAME --password=$PASSWORD -e "SHOW SLAVE STATUS \G"`

IO=`echo "$MYSQL" | grep 'Slave_IO_Running:' | awk '{print $2}'`
SQL=`echo "$MYSQL" | grep 'Slave_SQL_Running:' | awk '{print $2}'`

echo "IO: " $IO
echo "SQL: " $SQL

if [ "Yes" == "$IO" ] && [ "Yes" == "$SQL" ]; then
    touch /tmp/slave_running
else
    echo `date` | mail -s "Slave Fail" $EMAIL
fi

exit

chmod 500 checkmysql.sh

check by cron

# check mysql replication
* * * * *       /root/scripts/checkmysql.sh &> /dev/null

check by monit

check file slave_running with path /opt/slave_running
if timestamp > 3 minutes then alert

瀏覽次數： 2758

夢想家

mysql replication

目錄

Replication 介紹

Server type

Replication 的 Data type

Three types of logs used in the REPLICATION process

Slave Status Logs

Configure the Master

Configure the Slave

Force the slave delay master N second (MySQL 5.6)

STOP / START SLAVE

Slave Network Timeout Setting

Master-Master Replication

Troubleshooting

read-only flag

Upgrading Server

binlog-do-db option is dangerous

Check Slave replication script