MySQL Replication

Database

MySQL Replication Configuration

1
2
3
4
5
6
GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.8.11' identified by 'test123456';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;

my.conf

1
2
3
4
5
6
7
8
[mysqld]
general_log = 'OFF'
general_log_file=/usr/log/general.log
#bind-address = 0.0.0.0
server-id=9579751
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=replication_test

service mysqld restart

my.conf

1
2
3
4
server-id=1582587781
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
read-only=1

1
2
3
4
5
6
set global read_only=1;
show slave status\G;
change master to master_host='192.168.1.105',master_port=3306,master_user='slave108',master_password='slave108',master_log_file='mysql-bin.000001',master_log_pos=154;
start slave;
reset slave;     # 清除binlog 文件名及位置
reset slave all; # 清除slave 的连接配置信息
1
2
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1
2
3
4
5
6
7
8
9
stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave;

mysqldump -uzach -p replication_test > /home/zac/mysql/replication_test.sql
scp /home/zac/mysql/replication_test.sql root@192.168.1.108:/home/

stat /home/replication_test.sql

create database cmdb default charset utf8;
mysql -uroot -p replication_test</home/replication_test.sql

When Slave_SQL_Running is False

It means the slave and master data is different, slave data may be changed somehow.
Try to figure out what SQL caused the difference, and execute the SQL correspondingly.

1
/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS /usr/local/mysql/data/myslq-bin.000797 |grep -A '10' 876403636