Linux Cache

查看备份数据库文件大小约11GB
[root@msr11 ~]# ll
total 10857420
-rw——-. 1 root root 1048 Jun 12 07:29 anaconda-ks.cfg
-rw-r–r– 1 root root 11049373430 Jun 16 09:47 linuxcache.140616.sql
-rw-r–r–. 1 root root 8526 Jun 12 07:29 install.log
-rw-r–r–. 1 root root 3384 Jun 12 07:28 install.log.syslog
-r–r–r– 1 root root 68592501 Jun 12 08:10 VMwareTools-8.6.11-1310128.tar.gz
drwxr-xr-x 7 201 201 4096 Sep 5 2013 vmware-tools-distrib
[root@msr11 ~]#

执行导入数据库操作是中断并出现错误提示
[root@msr11 ~]# time mysql -u root -pmysqlpass linuxcache <linuxcache.140616.sql
ERROR 1153 (08S01) at line 1557: Got a packet bigger than ‘max_allowed_packet’ bytes

real 0m18.396s
user 0m2.516s
sys 0m0.176s
[root@msr11 ~]#

编辑my.cnf修改设置为大于当前数据库文件大小并重启数据库服务
[root@msr11 mysql]# vi /etc/my.cnf
max_allowed_packet=12G
[root@msr11 ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@msr11 ~]#

重新导入数据库文件
[root@msr11 ~]# time mysql -u root -pmysqlpass linuxcache <linuxcache.140616.sql

real 38m7.555s
user 3m33.893s
sys 0m13.377s
[root@msr11 ~]#

处于同步状态的MySQL Slave服务区所产生的错误信息
mysql> show slave status\G;
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got a packet bigger than ‘slave_max_allowed_packet’ bytes

修改配置后数据复制任务不再报错
[root@msr12 mysql]# service mysqld stop
Stopping mysqld: [ OK ]
[root@msr12 mysql]# vi /etc/my.cnf
max_allowed_packet=12G

查看mysqld.log日志

[root@msr02 ~]# less /var/log/mysqld.log
140603 10:39:36 [ERROR] Slave SQL: Error ‘Duplicate entry ‘78419’ for key ‘PRIMARY” on
query. Default database: ‘linuxcache’. Query: ‘insert into ls_signedlist(order_id, signed_time,
amount) values (88556, ‘2014-05-31 23:58:44’, 52),(87405, ‘2014-05-31 23:57:52’, 20),(87625,
‘2014-05-31 23:32:20’, 92),(87110, ‘2014-05-31 23:13:09’, 13),(88140, ‘2014-05-31 23:12:19’,
155),(88015, ‘2014-05-31 23:12:08’, 20),(88422, ‘2014-05-31 23:00:06’, 84),(85980, ‘2014-05
-31 22:48:13’, 9),(85994, ‘2014-05-31 22:48:07’, 19),(85986, ‘2014-05-31 22:48:01’, 22),
(85938, ‘2014-05-31 22:46:19’, 40),(85998, ‘2014-05-31 22:46:18’, 16.5),(85928, ‘2014-05-31
22:46:16’, 34),(85925, ‘2014-05-31 22:46:16’, 105),(85972, ‘2014-05-31 22:46:16’, 37),
(85912, ‘2014-05-31 22:46:15’, 48),(85921, ‘2014-05-31 22:46:15’, 13),(85947, ‘2014-05-31
22:46:14’, 68),(85936, ‘2014-05-31 22:46:12’, 251),(85949, ‘2014-05-31 22:46:11’, 11),
(85905, ‘2014-05-31 22:46:11’, 19),(85940, ‘2014-05-31 22:46:10’, 9),(85976, ‘2014-05-31
22:46:10’, 21),(85881, ‘2014-05-31 22:46:10’, 28),(85887,
140603 10:39:36 [Warning] Slave: Duplicate entry ‘78419’ for key ‘PRIMARY’ Error_code: 1062
140603 10:39:36 [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.000005’
position 684145438
Last_SQL_Error: Error ‘Duplicate entry ‘78419’ for key ‘PRIMARY” on query. Default
database: ‘linuxcache’. Query: ‘insert into ls_signedlist(order_id, signed_time, amount) values
(88556, ‘2014-05-31 23:58:44’, 52),(87405, ‘2014-05-31 23:57:52’, 20),(87625, ‘2014-05-31
23:32:20’, 92),(87110, ‘2014-05-31 23:13:09’, 13),(88140, ‘2014-05-31 23:12:19’, 155),
(88015, ‘2014-05-31 23:12:08’, 20),(88422, ‘2014-05-31 23:00:06’, 84),(85980, ‘2014-05-31
22:48:13’, 9),(85994, ‘2014-05-31 22:48:07’, 19),(85986, ‘2014-05-31 22:48:01’, 22),(85938,
‘2014-05-31 22:46:19’, 40),(85998, ‘2014-05-31 22:46:18’, 16.5),(85928, ‘2014-05-31
22:46:16’, 34),(85925, ‘2014-05-31 22:46:16’, 105),(85972, ‘2014-05-31 22:46:16’, 37),
(85912, ‘2014-05-31 22:46:15’, 48),(85921, ‘2014-05-31 22:46:15’, 13),(85947, ‘2014-05-31
22:46:14’, 68),(85936, ‘2014-05-31 22:46:12’, 251),(85949, ‘2014-05-31 22:46:11’, 11),
(85905, ‘2014-05-31 22:46:11’, 19),(85940, ‘2014-05-31 22:46:10’, 9),(85976, ‘2014-05-31
22:46:10’, 21),(85881, ‘2014-05-31 22:46:10’, 28),(85887, ‘2014-05-31
在Slave主机删除原有数据库并重新启用复制
mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: No

mysql> drop database linuxcache;
Query OK, 145 rows affected (0.62 sec)

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

mysql> change master to
master_host=’192.168.197.85′,master_user=’msrslave’,master_password=’slavepasswd’;
Query OK, 0 rows affected (0.18 sec)

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

mysql>

查看复制运行状态

mysql> show master status;
+——————+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———–+————–+——————+
| mysql-bin.000005 | 712371369 | | mysql |
+——————+———–+————–+——————+
1 row in set (0.00 sec)

mysql>

mysql> show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 712371369
[root@msr02 ~]# less /var/log/mysqld.log
140604 10:54:49 [Note] Slave I/O thread killed while reading event
140604 10:54:49 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000005’, position
712820015
140604 10:54:53 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-
bin.000002’ at position 454737874, relay log ‘./mysqld-relay-bin.000005’ position: 454738019
140604 10:54:53 [Note] Slave I/O thread: connected to master
‘msrslave@192.168.197.85:3306’,replication started in log ‘mysql-bin.000005’ at position
712820015