MySQL优化之――复制 - 数据库编程

eded max_allowed_pack”的错误，这是因为含有达文本的记录无法通过网络进行传输而导致的

解决方法是在主服务器和从服务器上添加max_allowed_packet参数，该参数默认设置为1MB

show variables LIKE '%max_all%'

Variable_name      Value   
------------------ ------- 
max_allowed_packet 1048576 

(1 row(s) affected)

set @@global.max_allowed_packet=16777216;

同时在my.ini或my.cnf文件里设置max_allowed_packet=16M，数据库重启之后该参数将有效

问题二：多主复制时的自增长变量冲突问题

大多数情况下使用一台主服务器对一台或者多台从服务器，但是在某些情况下可能会存在多个服务器配置为复制主服务器，

使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入时多个主服务器会试图使用相同的auto_increment值

服务器变量auto_increment_increment和auto_increment_offset可以协调多主服务器复制auto_increment列

在多主服务器复制到从服务器的过程中会发生主键冲突问题，可以将不同的主服务器的这两个参数重新进行设置，将A库

上设置auto_increment_increment=1，auto_increment_offset=1，此时B库上设置

auto_increment_increment=1，auto_increment_offset=0

提示：一般不建议使用双主或多主，因为这样会带来意想不到的冲突状况，就像SQLSERVER的对等复制，虽然有很多冲突检测措施

但是有时候冲突是不可预料的，出现冲突DBA要排查，维护成本较高，我们生产环境里是没有使用双主和多主，主要使用的是一主多从或一主一从

切换主从服务器

在实际生产环境，如果主机上的主库发生故障，需要将从机上的从库切换成主库，同时需要修改服务器C的配置文件，使程序连接到从机

下面介绍主从切换的步骤

1、首先要确保所有的从库都已经执行了relay log中的全部更新，看从库的状态是否是Has read all relay log，是否更新都已经执行完成

在从库上执行下面命令

STOP SLAVE IO_THREAD;

Command(s) completed successfully.

mysql> show processlist \G
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: localhost:60968
     db: test
Command: Sleep
   Time: 45
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 3949
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
*************************** 3. row ***************************
     Id: 7
   User: root
   Host: localhost:61007
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
3 rows in set (0.00 sec)

2、在从库上停止slave服务，然后执行reset master重置成为主库

STOP SLAVE;

Command(s) completed successfully.

RESET MASTER;
Command(s) completed successfully.

注意：如果从库上并未开binlog，那么在执行reset master的时候会报错：ERROR 1186(HY000):BINLOG CLOSED ,CANNOT RESET MASTER

在切换之后，在从库的数据目录会多出master.info文件

master.info文件里的内容

18
binlog.000005
393
192.168.1.100
repl
123
3306
60
0

0
1800.000

0
0

基本上记录了主库的复制用户、密码和binlog文件名和位置等

3、在从库B（192.168.1.102）上添加具有replication权限的用户repl，查询主库状态，命令如下

GRANT REPLICATION SLAVE ON *.*TO 'repl'@'localhost' identified by '123';

show master status;

4、修改主服务器的my.ini文件里的server-id为1，从服务器的server-id为2

5、在原来的主库（192.168.1.100）上配置复制参数

change master TO
master_host='192.168.1.102',
master_user='repl',
master_password='123',
master_port=3306,
master_log_file='on.000004',
master_log_pos=107;

6、在从库（192.168.1.100）上执行show slave status命令查看从库是否启动成功

START SLAVE;

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.102
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: on.000004
          Read_Master_Log_Pos: 107
               Relay_Log_File: joe-relay-bin.000006
                Relay_Log_Pos: 246
        Relay_Master_Log_File: on.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Ta

MySQL优化之――复制(五)