板子上mysql热备份

原创 2015年07月08日 17:23:52

主服务器配置文件修改

 [mysqld]

server-id        = 1
log-bin=mysql-bin
binlog_do_db        = dispatch_web

binlog_format=mixed

创建角色

mysql> grant replication slave on *.* to 'sync0'@'%' identified by '12345';
Query OK, 0 rows affected (0.03 sec)

mysql> show grants for sync0\G;               
*************************** 1. row ***************************
Grants for sync0@%: GRANT REPLICATION SLAVE ON *.* TO 'sync0'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9'
1 row in set (0.00 sec)

mysql> select user, host, password from mysql.user;
+-------+-----------+-------------------------------------------+
| user  | host      | password                                  |
+-------+-----------+-------------------------------------------+
| root  | localhost |                                           |
| root  | crong     |                                           |
| root  | 127.0.0.1 |                                           |
|       | localhost |                                           |
|       | crong     |                                           |
| sync0 | %         | *00A51F3F48415C7D4E8908980D443C29C69B60C9 |
+-------+-----------+-------------------------------------------+
6 rows in set (0.01 sec)

获取master信息

mysql>  show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000018 |      248 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql重启

/app/bin/app_init脚本

root@CRong:bin# ps |grep mysql
  459 root      1508 S    {mysqld_safe} /bin/sh /mnt/data/mysql-arm/bin/mysqld
  575 mysql    34732 S    /mnt/data/mysql-arm/libexec/mysqld --basedir=/mnt/da
  681 root      1512 S    grep mysql
root@CRong:bin# kill -9 459
root@CRong:bin# kill 575
root@CRong:bin# ps |grep mysql
  684 root      1512 S    grep mysql
root@CRong:bin# /mnt/data/mysql-arm/etc/mysqld start
root@CRong:bin# ps |grep mysql
  701 root      1508 S    {mysqld_safe} /bin/sh /mnt/data/mysql-arm/bin/mysqld
  816 mysql    33612 S    /mnt/data/mysql-arm/libexec/mysqld --basedir=/mnt/da
  820 root      1512 S    grep mysql

清空master的bin log

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
查看binlog

主数据备份

root@CRong:/server# tar  -cvf data.tar /mnt/data

a@ubuntu:~$ ftp 192.168.1.215 
ftp> get data.tar
ftp> bye

切换到从板子,恢复数据库

ps|grep mysql

kill -9 **

删除了所有的mysql进程

ftp发送过去

telnet然后mv到mnt目录

然后tar -xvf data.tar

a@ubuntu:~$ ftp 192.168.1.215
ftp> put data.tar
ftp> bye
a@ubuntu:~$ telnet 192.168.1.215 
root@CRong:/# cd server
root@CRong:/server# mv data.tar /mnt
root@CRong:/server# cd /mnt  
root@CRong:/mnt# tar -xvf data.tar

数据库重启

root@CRong:bin# /mnt/data/mysql-arm/etc/mysqld start
root@CRong:data# mysql -uroot

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dispatch_web       |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.01 sec)

修改从数据库的cnf文件

[mysqld]
 
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id    = 2
replicate-do-db = dispatch_web

重启数据库

设置master信息

mysql> change master to MASTER_HOST='1.1.1.99', MASTER_USER='sync0',MASTER_PASSWO
RD='12345',MASTER_LOG_FILE='mysql-bin.000012',MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 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.000012
          Read_Master_Log_Pos: 106
               Relay_Log_File: CRong-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: No
            Slave_SQL_Running: No
            
                   Last_Errno: 1
                   Last_Error: Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 13)
               
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: 1
               Last_SQL_Error: Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 13)
1 row in set (0.00 sec)


mysql> show  variables like "%master_%";      
Empty set (0.00 sec)

mysql> show  variables like "%id%";    
+------------------------------+-----------------------------------+
| Variable_name                | Value                             |
+------------------------------+-----------------------------------+
| identity                     | 0                                 |
| insert_id                    | 0                                 |
| last_insert_id               | 0                                 |
| pid_file                     | /mnt/data/mysql-arm/var/CRong.pid |
| pseudo_thread_id             | 3                                 |
| query_cache_wlock_invalidate | OFF                               |
| server_id                    | 1                                 |
+------------------------------+-----------------------------------+
7 rows in set (0.01 sec)



mysql> show  variables like "%slave_%";
+---------------------------+------------+
| Variable_name             | Value      |
+---------------------------+------------+
| log_slave_updates         | OFF        |
| slave_compressed_protocol | OFF        |
| slave_exec_mode           | STRICT     |
| slave_load_tmpdir         | /tmp       |
| slave_max_allowed_packet  | 1073741824 |
| slave_net_timeout         | 3600       |
| slave_skip_errors         | OFF        |
| slave_transaction_retries | 10         |
| sql_slave_skip_counter    |            |
+---------------------------+------------+
9 rows in set (0.01 sec)


主备份各自的内网IP

1.1.1.99(备份)

1.1.1.215(主)

root@CRong:/# cat app/cfg/netconfig
config ip wan
                option addr 192.168.1.215
                option mask 255.255.255.0
config ip gw
                option addr 192.168.1.1
                option mask 255.255.255.0
config ip lan
                option addr 1.1.1.215
                option mask 255.255.255.0
                option mode static
config ip slave
                option addr 1.1.1.99

从机日志:
150710 09:42:30 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710  9:42:30 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000012' at position 106, relay log './CRong-relay-bin.000001' position: 4
150710  9:42:30 [ERROR] Slave SQL: Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 13), Error_code: 1
150710  9:42:30 [Note] Event Scheduler: Loaded 0 events
150710  9:42:30 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution
150710  9:42:33 [ERROR] Slave I/O: error connecting to master 'sync0@1.1.1.99:3306' - retry-time: 60  retries: 86400, Error_code: 2013

总结一下OpenWrt的启动流程:

1.CFE->2.linux->3./etc/preinit->4./sbin/init ->5./etc/inittab ->6./etc/init.d/rcS->7./etc/rc.d/S* ->8.

1.首先,CFE(common firmware environment)-

它就是一个bootloader,类似u-boot,redboot之类,有点broadcom公司御用之嫌--

最先启动。它的任务只是创造一个简单的环境,让系统先运行起来。除了能够跳转到特定地址上启动操作系统(如Linux)外,

它还能让你download东西到上面,比如download一个linux,然后启动它。

这时候就没CFE什么事情了。唯一的瓜葛是CFE传递给内核一个命令行的参数,这个可以在linux启动起来之后用下面的命令查看:

 

root@OpenWrt:/# cat /proc/cmdline

console=ttyS0,115200 mtdparts=spi_flash:1m(u-boot)ro,3m(kernel),-(rootfs)

 

2linux系统启动起来了。

它使用类似如下的脚本命令来解析cmdline:

对于OpenWrt这个cmdline的意思就是:root=/dev/mtdblock2 文件系统在第三个flash分区上(mtdblock0,1,2);

rootfstype=squashfs,jffs2 文件系统类型是squashfs和jffs2(为什么两种类型,目前还不清楚,

不过可以确定OpenWrt使用了较为复杂的文件系统,实现了squashfs的压缩和jffs2的可写 ); 

init=/etc/preinit 执行该初始化,noinitrd console=ttyS0,115200

没有initrd和console口设定。

  3.init=/etc/preinit 是linux会执行的初始化,

具体内容如下:

root@OpenWrt:/# cat /etc/preinit 

mount proc /proc -t proc

mount sysfs /sys -t sysfs

mount tmpfs /tmp -t tmpfs -o size=$size,nosuid,nodev,mode=1777

mount devfs /dev -t devfs

mount -t tmpfs tmpfs /dev -o size=512K

mknod /dev/console c 5 1

mount -n -t tmpfs -o mode=0755 udev /dev

mkdir -p /dev/pts /dev/shm

mount devpts /dev/pts -t devpts 

exec /sbin/init

可见它主要是挂载一些系统需要的文件系统例如tmpfsproc和sysfs(是否真正挂载取决于内核是否2.6的)。

并且会准备设备节点和故障恢复(failsafe)的准备。(这里还没看懂 )     

  4.exec /sbin/init启动文件系统

最后,exec /sbin/init启动文件系统,在OpenWrt上也就是busybox的init程序。

它会自动分析/etc/inittab这个文件,其内容解释详见busybox网站的cmd help。

  5./etc/inittab的内容:

::sysinit:/etc/init.d/rcS S boot 

 6.运行/etc/init.d/rcS:

run_scripts() {

for i in /etc/rc.d/$1*; do

[ -x $i ] && $i $2 2>&1

done | $LOGGER

}  

    7.将执行/etc/rc.d/S*这些脚本: 

root@OpenWrt:/# ls /etc/rc.d/S*

/etc/rc.d/S10boot 

/etc/rc.d/S50cron

按照数字从小到大的顺序执行。

二、实现自启动脚本

OpenWRT的启动脚本放在 /etc/init.d 目录下,而系统开机时自动运行/etc/rc.d目录下的脚本。所以在rc.d目录下、有init.d下脚本的链接文件。

然后,我们加入自己的脚本,实现模块驱动的加载、应用程序的开机自启动等。

首先在/etc/init.d里添加需要启动的shell脚本

例如:

vim startCamera

内容:

01 #!/bin/sh /etc/rc.common
02 # /init.d/startCamera
03 START=50
04 start()
05 {
06 ./opt/ipnc/system_server &
07 }
08 stop()
09 {
10 killallsystem_server
11 }

之后还需要在rc.d目录下做一个链接,启动时系统会按顺序启动rc.d目录下的脚本链接,对应执行init.d目录下的启动脚本。脚本的命名要符合系统规范,init.d下telnet脚本在rc.d目录下的链接文件名为S50telnet。所以链接文件要在脚本名前加S+启动顺序数字,启动顺序要等系统进行完必要的初始化。所以我们命名为S95+脚本名。

命令:ln -s /etc/init.d/startCamera /etc/rc.d/S95startCamera

重启,即可

现在实现了应用程序的开机自启动。

板子自启动流程

/init脚本

root@CRong:/# cat init
#!/bin/busybox ash
# Copyright (C) 2006 OpenWrt.org

/bin/busybox mount -t sysfs sysfs /sys
/bin/busybox mount -t proc  proc  /proc

exec /sbin/init "$@" </dev/console >/dev/console 2>&1

/etc/inittab脚本

root@CRong:/etc# cat inittab
::sysinit:/etc/init.d/rcS S boot
::shutdown:/etc/init.d/reboot
ttyS0::askfirst:/bin/ash --login

/etc/init.d/rcS脚本

root@CRong:init.d# cat rcS
#!/bin/sh
export PATH=/bin:/sbin:/usr/bin:/usr/sbin

echo "remount root fs"
mount -n -o remount,rw /

echo "mounting all filesystems"
mount -a
 
telnetd -l /bin/login

echo "init syslog"
mkdir -p /var/run
mkdir -p /var/log
mkdir -p /var/lock
syslogd -m 0 -S -s 8 -b 8

echo "start ftpd"
mkdir /server
tcpsvd -E 0 21 ftpd -S -w /server -t 60 &

echo "start user app"
. /usr/sbin/init

/usr/sbin/init脚本

root@CRong:sbin# cat init
mkdir /mnt/app
ubiattach -m 7 -d 0 /dev/ubi_ctrl
if [ $? -eq 0 ] ; then
        mount -t ubifs ubi0_0 /mnt/app
        if [ $? -ne 0 ] ; then
                echo "failed to mount app"
        else
                echo "successed to mount app"
        fi
fi
ln -s /mnt/app /app
ln -s /mnt/app/lib /usr/lib
ln -s /mnt/app/bin /usr/local/bin
ln -s /mnt/app/sbin /usr/local/sbin
if [ -d /app/modules ] ; then
        module.sh /app/modules
fi

echo "mount flash part data & log"
mkdir /mnt/data
ubiattach -m 8 -d 1 /dev/ubi_ctrl
if [ $? -eq 0 ] ; then
        mount -t ubifs ubi1_0 /mnt/data
fi
ln -s /mnt/data /data
chmod 777 /mnt/data

mkdir /mnt/log
ubiattach -m 9 -d 2 /dev/ubi_ctrl
if [ $? -eq 0 ] ; then
        mount -t ubifs ubi2_0 /mnt/log
fi
ln -s /mnt/log /log

if [ -e /app/bin/app_init ] ; then
        /app/bin/app_init 2>&1 > /dev/null &
fi

/app/bin/app_init脚本

root@CRong:/# cat /app/bin/app_init
cp /app/cfg/my.cnf /mnt/data/mysql-arm/etc/my.cnf
/mnt/data/mysql-arm/etc/mysqld start &
sleep 5
/mnt/data/mysql-arm/bin/mysql -uroot -v -f < /app/cfg/slave.sql; &
sleep 1 

/etc/my.cnf

root@CRong:/# cat /etc/my.cnf
[mysqld]   
binlog_format=mixed   
server-id       = 2
master-host     =  1.1.1.215
master-user     =  sync0
master-password =   12345
master-port     =  3306
log-bin=mysql-bin
replicate-do-db = dispatch_web  

/app/cfg/my.cnf

cat /app/cfg/my.cnf

server-id       = 2
master-host     =  1.1.1.215
master-user     =  sync0
master-password =   12345
master-port     =  3306
log-bin=mysql-bin
replicate-do-db = dispatch_web

/app/cfg/slave.sql

root@CRong:sbin# cat /app/cfg/slave.sql
  #mysql -uroot dispatch_web -v -f < /tmp/test.sql;
 change master to MASTER_HOST='1.1.1.215', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=107;
 start slave;
 show slave status;

slave参数

mysql> show slave status\G;
*************************** 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.000012
          Read_Master_Log_Pos: 106
               Relay_Log_File: CRong-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: dispatch_web      
                   Last_Errno: 1
                   Last_Error: Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 13)                 
                Last_IO_Errno: 2013
                Last_IO_Error: error connecting to master 'sync0@1.1.1.99:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 1
               Last_SQL_Error: Unable to use slave's temporary directory /tmp - Can't create/write to file '/tmp/SQL_LOAD-' (Errcode: 13)

root@CRong:/# cat /mnt/data/mysql-arm/var/CRong.err
150710 14:42:43 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 14:42:44 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 14:42:44 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead.
150710 14:42:44 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=CRong-relay-bin' to avoid this problem.
150710 14:42:44 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000012' at position 106, relay log './CRong-relay-bin.000024' position: 4
150710 14:42:44 [Note] Event Scheduler: Loaded 0 events
150710 14:42:44 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution
150710 14:42:47 [ERROR] Slave I/O: error connecting to master 'sync0@1.1.1.99:3306' - retry-time: 60  retries: 86400, Error_code: 2013

root@CRong:/#mysql -uroot -v -f < /app/cfg/slave.sql;

--------------
stop slave
--------------

--------------
change master to MASTER_HOST='1.1.1.215', MASTER_USER='sync0',MASTER_PASSWORD='12345',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=107
--------------

--------------
start slave
--------------

--------------
show slave status
--------------

*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 1.1.1.215
                  Master_User: sync0
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 107
               Relay_Log_File: CRong-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: dispatch_web
       
root@CRong:/#  tail /mnt/data/mysql-arm/var/CRong.err

150710 14:51:36 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='1.1.1.99', master_port='3306', master_log_file='mysql-bin.000012', master_log_pos='106'. New state master_host='1.1.1.215', master_port='3306', master_log_file='mysql-bin.000006', master_log_pos='107'.
150710 14:51:36 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000006' at position 107, relay log './CRong-relay-bin.000001' position: 4
150710 14:51:36 [Note] Slave I/O thread: connected to master 'sync0@1.1.1.215:3306',replication started in log 'mysql-bin.000006' at position 107
150710 14:51:36 [ERROR] Slave I/O: 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). Error_code: 1593
150710 14:51:36 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000006', position 107

主机数据库

日志

root@CRong:/#  tail /mnt/data/mysql-arm/var/CRong.err

150710 15:17:24 [Note] Event Scheduler: Purging the queue. 0 events
150710 15:17:24 [Note] /mnt/data/mysql-arm/libexec/mysqld: Shutdown complete

150710 15:17:24 mysqld_safe mysqld from pid file /mnt/data/mysql-arm/var/CRong.pid ended
150710 15:17:50 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 15:17:51 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 15:17:51 [Note] Event Scheduler: Loaded 0 events
150710 15:17:51 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution
root@CRong:/# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

No entry for terminal type "vt102";
using dumb terminal settings.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

角色查询

mysql> show grants for sync0\G;
*************************** 1. row ***************************
Grants for sync0@%: GRANT REPLICATION SLAVE ON *.* TO 'sync0'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9'
1 row in set (0.01 sec)

ERROR:
No query specified

查看log是否启动

mysql> show variables like "%log_%";
+-----------------------------------------+----------------------------------------+
| Variable_name                           | Value                                  |
+-----------------------------------------+----------------------------------------+
| log_bin                                 | ON                                     |
| sql_log_bin                             | ON                                     |
| sql_log_off                             | OFF                                    |
| sql_log_update                          | ON                                     |
+-----------------------------------------+----------------------------------------+
25 rows in set (0.03 sec)

清空所有binlog


mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

查看所有binlog

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000012 |       106 |
+------------------+-----------+
1 row in set (0.01 sec)

查看master状态

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |      106 | dispatch_web |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


从机状态主从同时开启

root@CRong:/#   tail /mnt/data/mysql-arm/var/CRong.err
150710 15:59:31 [ERROR] Failed to open the relay log './CRong-relay-bin.000001' (relay_log_pos 4)
150710 15:59:31 [ERROR] Could not find target log during relay log initialization
150710 15:59:31 [ERROR] Failed to initialize the master info structure
150710 15:59:31 [Note] Event Scheduler: Loaded 0 events
150710 15:59:31 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution



mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)


mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 1.1.1.215
                  Master_User: sync0
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 106
               Relay_Log_File: CRong-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: dispatch_web       
                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:
1 row in set (0.00 sec)

mysql> show variables like '%id';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| insert_id        | 0     |
| last_insert_id   | 0     |
| pseudo_thread_id | 15    |
| server_id        | 2     |
+------------------+-------+

主机状态

root@CRong:/#  tail /mnt/data/mysql-arm/var/CRong.err
150710 15:41:27 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 15:41:27 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 15:41:28 [Note] Event Scheduler: Loaded 0 events
150710 15:41:28 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution
150710 15:59:15 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 15:59:15 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 15:59:16 [Note] Event Scheduler: Loaded 0 events
150710 15:59:16 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution
mysql> show variables like '%id';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| insert_id        | 0     |
| last_insert_id   | 0     |
| pseudo_thread_id | 16    |
| server_id        | 1     |
+------------------+-------+
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |      106 | dispatch_web |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

从机修改了port的值

[client]                                                               
#password       = your_password                                        
port            = 13306                                    
socket          = /mnt/data/mysql-arm/etc/mysql.sock       
                                                           
# Here follows entries for some specific programs   
                                                    
# The MySQL server                                  
[mysqld]                                            
port            = 13306           

重新启动数据库后从机状态

root@CRong:etc# tail /mnt/data/mysql-arm/var/CRong.err
150710 16:40:15 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000012', position 106
150710 17:00:16 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 17:00:16 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 17:00:16 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead.
150710 17:00:16 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=CRong-relay-bin' to avoid this problem.
150710 17:00:16 [Note] Event Scheduler: Loaded 0 events
150710 17:00:16 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 13306  Source distribution
150710 17:00:16 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000012' at position 106, relay log './CRong-relay-bin.000001' position: 4
150710 17:00:16 [ERROR] Slave I/O: error connecting to master 'sync0@1.1.1.215:3306' - retry-time: 60  retries: 86400, Error_code: 2013

主机状态

root@CRong:/#  tail /mnt/data/mysql-arm/var/CRong.err
150710 15:41:27 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 15:41:27 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 15:41:28 [Note] Event Scheduler: Loaded 0 events
150710 15:41:28 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution
150710 15:59:15 mysqld_safe Starting mysqld daemon with databases from /mnt/data/mysql-arm/var
150710 15:59:15 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
150710 15:59:16 [Note] Event Scheduler: Loaded 0 events
150710 15:59:16 [Note] /mnt/data/mysql-arm/libexec/mysqld: ready for connections.
Version: '5.1.73-log'  socket: '/mnt/data/mysql-arm/etc/mysql.sock'  port: 3306  Source distribution

但是能ping通主机从从机

主机ping从机失败

nginx日志文件切割

nginx日志按日期自动切割脚本如下 #nginx日志切割脚本 #!/bin/bash #设置日志文件存放目录 logs_path="/usr/local/nginx/logs/" #设置pid...

mysql升级(5.6.15升级到5.7.15)

mysql升级(5.6.15升级到5.7.15): 1,备份旧库: mysqldump -uuei -puei123 uei > /data/uei.sql    备份权限:参考mysql权限备份...

mysql双击热备份

  • 2013年12月11日 13:19
  • 47KB
  • 下载

MySQL热备份

  • 2013年07月24日 15:53
  • 218KB
  • 下载

MySQL双机热备份实施方案

1、MySQL数据库没有增量备份的机制,当数据量太大的时候备份是一个很大的问题。还好MySQL数据库提供了一种主从备份的机制,其实就是把主数据库的所有的数据同时写到备份数据库中。实现MySQL数据库的...

mysql热备份

  • 2012年03月15日 16:03
  • 120KB
  • 下载

Mysql 双机双向热备份

【背景说明】           目前应用中的mysql并没有采用双机热备的策略,但考虑到系统的高可用性,双机热备份是必要的,另外实现数据热备份后还可进一步实现读写分离,提高应用中访问数据的性能,何乐...

MySQL双机热备份实施方案

1、MySQL数据库没有增量备份的机制,当数据量太大的时候备份是一个很大的问题。还好MySQL数据库提供了一种主从备份的机制,其实就是把主数据库的所有的数据同时写到备份数据库中。实现MySQL数据库的...
  • mls2012
  • mls2012
  • 2011年05月29日 17:30
  • 369

MySQL双机热备份的配置及原理

MySQL双机热备份的配置及原理 1.       主题 MySQL双机热备份的配置及原理 2.       摘要 双机热备就是使用MySQL提供的一种主从备份机制实现。其实就是把主数据库(m...

MySQL双机热备份实施方案

1、MySQL数据库没有增量备份的机制,当数据量太大的时候备份是一个很大的问题。还好MySQL数据库提供了一种主从备份的机制,其实就是把主数据库的所有的数据同时写到备份数据库中。实现MySQL数据库的...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:板子上mysql热备份
举报原因:
原因补充:

(最多只允许输入30个字)