down后数据不同步的实验
来源：互联网 发布：excel2007办公软件 编辑：程序博客网 时间：2024/05/02 00:23
1 主板show master status； P1
2 主板插入若干条数据
3 主板show master status；
4 从板change master to P1
5 看从板能否slave连接成功并且数据正确，如果成功说明这个方法可行如果失败必须获取每次主板的最新的master数据进行同步
6 每次主板reset master后必须同步到从板的master点上去，从板重新同步
7 如果失败要考虑pt-sync-table命令的arm版本了
a@ubuntu:~$ ./t
root@CRong:/# telnet 1.1.1.99
root@CRong:/# mysql -uroot -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 1.1.1.99
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: CRong-relay-bin.000044
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: dispatch_web
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: 0
Relay_Log_Space: 125
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: 0
Last_SQL_Error:
root@CRong:/# mysql -uroot -e "stop slave"
root@CRong:/# exit
Connection closed by foreign host
root@CRong:/# mysql -uroot -e "show master status\G"
*************************** 1. row ***************************
File: mysql-bin.000058
Position: 106
Binlog_Do_DB: dispatch_web
Binlog_Ignore_DB:
root@CRong:/# mysql -uroot -e "use dispatch_web;insert into in_code_info values(
200,12,13,14,15,16,17,18,19,110);insert into in_code_info values(201,12,13,14,15
,16,17,18,19,110);insert into in_code_info values(202,12,13,14,15,16,17,18,19,11
0);show master status\G"
*************************** 1. row ***************************
File: mysql-bin.000058
Position: 511
Binlog_Do_DB: dispatch_web
Binlog_Ignore_DB:
root@CRong:/# telnet 1.1.1.99
root@CRong:/# mysql -uroot -e "use dispatch_web;delete from in_code_info where
type=13;select * from in_code_info"
root@CRong:/# mysql -uroot
mysql> use dispatch_web;
Database changed
mysql> select * from in_code_info;
Empty set (0.01 sec)
root@CRong:/# mysql -uroot -e "stop slave;reset slave;change master to MASTER_HO
ST='1.1.1.99', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE='mysq
l-bin.000058',MASTER_LOG_POS=106;start slave"
root@CRong:/# mysql -uroot -e "use dispatch_web;select * from in_code_info"
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
说明这个方法是可行的，只要定位到合适的master点，是可以通过change master to强制复原的
方案：
主从配置完成后在从里有上次对齐后数据
下次从启动后取到对齐的数据然后重新同步一次数据就可以同步过来了
这里涉及到两个问题：
1 如果这中间对方reset 过master那么会导致同步不上，这个时候就会丢数据而且导致slave不能连接到主机
如果连接不上就要从主机获取master点
root@CRong:/# mysql -uroot -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.1.1.99
Master_User: sync0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000058
Read_Master_Log_Pos: 511
Relay_Log_File: CRong-relay-bin.000045
Relay_Log_Pos: 656
Relay_Master_Log_File: mysql-bin.000058
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dispatch_web
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: 511
Relay_Log_Space: 811
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:
10 修改新的脚本
slave_to_master()
{
echo "mysql to master from slave..."
$mysql_bin -u"${mysql_user}" -e "stop slave;show master status\G"
#log_file=$(${mysql_bin} -u"${mysql_user}" -e "show master status\G" | grep "File:" | awk -F ': ' '{printf $2}')
#log_id=$(${mysql_bin} -u"${mysql_user}" -e "show master status\G" | grep "Position:" |awk -F ': ' '{printf $2}')
#$mysql_bin -u"${mysql_user}" -e "use ms_state;update ms_log set mlf='${log_file}',mlp='${log_id}'"
#$mysql_bin -u"${mysql_user}" -e "use ms_state;select * from ms_log;"
echo "mysql is master"
}
master_to_slave()
{
echo "mysql to slave from master..."
#if (${mysql_bin} -h"${vip}" -u"${mysql_user}" -e "show slave status" > /dev/null 2>&1)
#then
#log_file=$(${mysql_bin} -h1.1.1.99 -usync0 -p12345 -e "use ms_state;select mlf from ms_log\G" | grep "mlf:" | awk -F': ' '{printf $2}')
#log_id=$(${mysql_bin} -h1.1.1.99 -usync0 -p12345 -e "use ms_state;select mlp from ms_log\G" | grep "mlp:" | awk -F': ' '{printf $2}')
log_file=$(${mysql_bin} -u"${mysql_user}" -e "show slave status\G" | grep "Master_Log_File:" | awk -F ': ' '{printf $2}')
log_id=$(${mysql_bin} -u"${mysql_user}" -e "show slave status\G" | grep "Read_Master_Log_Pos:" |awk -F ': ' '{printf $2}')
echo $log_file
echo $log_id
$mysql_bin -u"${mysql_user}" -e "stop slave;reset slave;CHANGE MASTER TO MASTER_HOST='1.1.1.99', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE= '${log_file}',MASTER_LOG_POS = ${log_id};start slave;show slave status\G";
echo "mysql is slave"
#fi
}
主到从的时候从上次的备份里面拿出来上次的master点进行备份
但是没有检测是否出异常
重新烧入到主和从
然后启动主和从，查看主和从是否同步，然后stop 从，主里插入数据，然后重新切换从，数据应该成功
root@CRong:/# mysql -uroot -e "stop slave"
root@CRong:/# exit
root@CRong:bin# mysql -uroot -e "use dispatch_web;delete from in_code_info wher
e type=13;select * from in_code_info"
root@CRong:bin# mysql -uroot -e "use dispatch_web;insert into in_code_info value
s(200,12,13,14,15,16,17,18,19,110);insert into in_code_info values(201,12,13,14,
15,16,17,18,19,110);insert into in_code_info values(202,12,13,14,15,16,17,18,19,
110);show master status\G"
*************************** 1. row ***************************
File: mysql-bin.000060
Position: 621
Binlog_Do_DB: dispatch_web
Binlog_Ignore_DB:
root@CRong:bin# mysql -uroot -e "use dispatch_web;insert into in_code_info value
s(200,12,13,14,15,16,17,18,19,110);insert into in_code_info values(201,12,13,14,
15,16,17,18,19,110);insert into in_code_info values(202,12,13,14,15,16,17,18,19,
110);"
root@CRong:bin# telnet 1.1.1.99
root@CRong:/# cd app
root@CRong:app# cd bin
root@CRong:bin# . start.sh
2
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| insert_id | 0 |
| last_insert_id | 0 |
| pseudo_thread_id | 54 |
| server_id | 2 |
+------------------+-------+
mysql to slave from master...
mysql-bin.000060
106
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 1.1.1.99
Master_User: sync0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000060
Read_Master_Log_Pos: 106
Relay_Log_File: CRong-relay-bin.000044
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000060
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: dispatch_web
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: 106
Relay_Log_Space: 106
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: 0
Last_SQL_Error:
mysql is slave
root@CRong:bin# mysql -uroot -e "use dispatch_web;select * from in_code_info"
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
现在start.sh
#!/bin/sh
# author:wdlinux
# url:http://www.wdlinux.cn
# description: mysql start
mip=1.1.1.215
vip=1.1.1.99
mysql_in=/mnt/data/mysql-arm
mysql_bin=${mysql_in}/bin/mysql
mysql_user=root
mysqld_start=/mnt/data/mysql-arm/etc/mysqld
slave_to_master()
{
echo "mysql to master from slave..."
$mysql_bin -u"${mysql_user}" -e "stop slave;show master status\G"
#log_file=$(${mysql_bin} -u"${mysql_user}" -e "show master status\G" | grep "File:" | awk -F ': ' '{printf $2}')
#log_id=$(${mysql_bin} -u"${mysql_user}" -e "show master status\G" | grep "Position:" |awk -F ': ' '{printf $2}')
#$mysql_bin -u"${mysql_user}" -e "use ms_state;update ms_log set mlf='${log_file}',mlp='${log_id}'"
#$mysql_bin -u"${mysql_user}" -e "use ms_state;select * from ms_log;"
echo "mysql is master"
}
master_to_slave()
{
echo "mysql to slave from master..."
#if (${mysql_bin} -h"${vip}" -u"${mysql_user}" -e "show slave status" > /dev/null 2>&1)
#then
#log_file=$(${mysql_bin} -h1.1.1.99 -usync0 -p12345 -e "use ms_state;select mlf from ms_log\G" | grep "mlf:" | awk -F': ' '{printf $2}')
#log_id=$(${mysql_bin} -h1.1.1.99 -usync0 -p12345 -e "use ms_state;select mlp from ms_log\G" | grep "mlp:" | awk -F': ' '{printf $2}')
log_file=$(${mysql_bin} -u"${mysql_user}" -e "show slave status\G" | grep "[^_]Master_Log_File:" | awk -F ': ' '{printf $2}')
log_id=$(${mysql_bin} -u"${mysql_user}" -e "show slave status\G" | grep "Read_Master_Log_Pos:" |awk -F ': ' '{printf $2}')
echo $log_file
echo $log_id
$mysql_bin -u"${mysql_user}" -e "stop slave;reset slave;CHANGE MASTER TO MASTER_HOST='1.1.1.99', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE= '${log_file}',MASTER_LOG_POS = ${log_id};start slave;show slave status\G";
echo "mysql is slave"
#fi
}
###############################################################################
. /app/bin/setsql.sh
val=`netcfg -r 38004|grep 54`
if [ -n "$val" ]
then
slave_to_master
else
master_to_slave
fi
setsql.sh
#!/bin/sh
val=`netcfg -r 38004|grep 54`
echo $val
if [ -n "$val" ]
then
echo 1
mysql -uroot -e "set GLOBAL server_id=1"
cp /app/cfg/my1.cnf /mnt/data/mysql-arm/etc/my.cnf
else
echo 2
mysql -uroot -e "set GLOBAL server_id=2"
cp /app/cfg/my2.cnf /mnt/data/mysql-arm/etc/my.cnf
fi
mysql -uroot -e "show variables like '%id'"
app_init
#!/bin/sh
#change lan speed to 100M
#rtlcfg -f -p 1 -s 1 -d 8
#rtlcfg -f -p 2 -s 1 -d 8
netcfg -f /app/cfg/netconfig
udhcpd -f /app/cfg/udhcpd.conf &
#exit 0
netcfg -w 38018 1
insmod /app/bin/tdm.ko
export SIP_CORE_SVR_PATH=/app/bin/sipserver.ini
###############################################################
. /app/bin/setsql.sh
chmod 777 /tmp
##############################################################
/mnt/data/mysql-arm/etc/mysqld start &
sleep 5
###############################################################
. /app/bin/start.sh
sleep 2
###############################################################
ulimit -S -c unlimited > /dev/null 2>&1
/app/bin/sip_server &
/app/apache/bin/apachectl start
/app/bin/timeServer 4000 &
主备板都能正常开机
主板
root@CRong:bin# mysql -uroot -e "show master status;show slave status\G"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000066 | 106 | dispatch_web | |
+------------------+----------+--------------+------------------+
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 1.1.1.99
Master_User: sync0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000065
Read_Master_Log_Pos: 7154
Relay_Log_File: CRong-relay-bin.000026
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000065
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: dispatch_web
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: 7154
Relay_Log_Space: 530
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: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Last_SQL_Errno: 0
Last_SQL_Error:
root@CRong:bin# mysql -uroot -e "show variables like '%id'"
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| insert_id | 0 |
| last_insert_id | 0 |
| pseudo_thread_id | 24 |
| server_id | 1 |
+------------------+-------+
root@CRong:bin# mysql -uroot -e "use dispatch_web;select * from in_code_info"
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
备板
root@CRong:/# mysql -uroot -e "show master status;show slave status\G;use dispat
ch_web;select * from in_code_info;show variables like '%id'"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000072 | 106 | dispatch_web | |
+------------------+----------+--------------+------------------+
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.1.1.99
Master_User: sync0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000066
Read_Master_Log_Pos: 106
Relay_Log_File: CRong-relay-bin.000046
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000066
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dispatch_web
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: 106
Relay_Log_Space: 551
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:
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| insert_id | 0 |
| last_insert_id | 0 |
| pseudo_thread_id | 27 |
| server_id | 2 |
+------------------+-------+
主板插入几条数据看看背板是否到齐
root@CRong:bin# mysql -uroot -e "use dispatch_web;insert into in_code_info values(200,12,13,14,15,16,17,18,19,110);insert into in
_code_info values(201,12,13,14,15,16,17,18,19,110);insert into in_code_info values(202,12,13,14,15,16,17,18,19,110);;select * fro
m in_code_info"
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
root@CRong:bin# telnet 1.1.1.99
root@CRong:/# mysql -uroot -e "use dispatch_web;select * from in_code_info"
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
只插入主板测试批量同步
root@CRong:/# mysql -uroot -e "use dispatch_web;insert into in_code_info values(200,12,13,14,15,16,17,18,19,110);insert into in_c
ode_info values(201,12,13,14,15,16,17,18,19,110);insert into in_code_info values(202,12,13,14,15,16,17,18,19,110);;select * from
in_code_info"
执行了两次
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
root@CRong:/# mysql -uroot -e "show master status;"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 | 916 | dispatch_web | |
+------------------+----------+--------------+------------------+
关机
进从机器
root@CRong:/# mysql -uroot -e "use dispatch_web;select * from in_code_info"
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| in_code_id | group_id | type | con_num | slot_num | pcm_channel | port | receive_gain | send_gain | master_num |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 202 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 201 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
| 200 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 110 |
+------------+----------+------+---------+----------+-------------+------+--------------+-----------+------------+
root@CRong:/# mysql -uroot -e "show master status;show slave status\G"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000073 | 916 | dispatch_web | |
+------------------+----------+--------------+------------------+
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.1.1.99
Master_User: sync0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000068
Read_Master_Log_Pos: 106
Relay_Log_File: CRong-relay-bin.000047
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000068
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dispatch_web
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: 106
Relay_Log_Space: 1361
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:
做了一次主备切换，看看主从同步有没有同步上
备
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event'
Last_SQL_Errno: 0
Last_SQL_Error:
原因，原来上次就没有同步上，上次的master点是不对的
root@CRong:/# exit
Connection closed by foreign host
root@CRong:/# mysql -uroot -e "show master status;"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000073 | 916 | dispatch_web | |
+------------------+----------+--------------+------------------+
root@CRong:/# telnet 1.1.1.99
root@CRong:/# mysql -uroot -e "stop slave;reset slave;change master to MASTER_HO
ST='1.1.1.99', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE='mysq
l-bin.000073',MASTER_LOG_POS=916;start slave"
root@CRong:/# mysql -uroot -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.1.1.99
Master_User: sync0
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000073
Read_Master_Log_Pos: 916
Relay_Log_File: CRong-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000073
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dispatch_web
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: 916
Relay_Log_Space: 406
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:
就能同步上，下次以后每个板子的slave的master点都是正确的了
初始化时候要给两边的备做一次同步
后来就不需要每次主备同时打开
- down后数据不同步的实验
- 典型数据不同步造成的错误(1)
- TimesTen 自动刷新的 cachegroup 数据不同步
- 关于gluster数据不同步的处理
- Solr数据不同步
- mysql主从数据不同步
- Acvitity和Fragment通信数据显示不同步的问题
- Spring的配置依赖 协调不同步的Bean Bean的后处理器 容器的后处理器
- veridata实验举例（3）验证veridata查找出insert操作导致的不同步现象
- veridata实验举例（4）验证veridata查找出updata、delete操作导致的不同步现象
- 转载：网站down掉后，自动发信的shell
- MySQL数据库Drop Down后的紧急处置
- MYSQL 日志同步数据不同步
- 解决数据不同步(2)----- synchronized()
- LeanCloud数据更新不同步问题
- 协调不同步的Bean
- 线程不同步的概念
- 系统启动后，添加新的Appwidget到手机更新不同步问题解决
- Java内存模型
- DevExpress v15.1：DevExtreme控件升级（一）
- Two Sum
- GCC生成.a静态库与.so动态库
- vim的基本使用
- down后数据不同步的实验
- 创建Maven创建src/main/java提示重复
- Oauth认证原理
- 在线教育遭遇瓶颈 扣丁领衔O2O时代
- hdu 2545 树上战争（并查集）
- git pro 笔记
- Java数据结构-线性表之队列
- 安装YII2
- 黑马程序员——Java交通灯管理系统&银行业务系统
