mysql 复制 常用命令

1.创建复制帐号

 

GRANT REPLICATION SLAVE  ON *.* TO 'rep1'@'192.168.1.2' IDENTIFIED BY '123test';

 

2. 锁所有表

flush  tables with read lock;

 

 

3. 显示 master 状态

show master status;

 

4. 解锁

unlock tables;

 

5. 配置slave

 

CHANGE MASTER TO

MASTER_HOST='192.168.4.5',

MASTER_USER='root',

MASTER_PASSWORD='23434',

MASTER_LOG_FILE='mysql.bin',

MASTER_LOG_POS='32323';

 

 

6. 启动 slave

start slave;

 

7. 显示mysql 进程列表

show processlist /G

 

8. 显示slave 状态

show slave status /G

 

9. 同步主数据

select MASTER_POS_WAIT('mysql-bin.00003','978');

 

10.  slave 跳过错误SQL

SET GLOBAL SQL_slave_SKIP_COUNTER=1;

 

11. 查询和设置 max_allowed_packet

 

show variables like 'max_allowed_packet';

 

SET @@global.max_allowed_packet=12343424;

 

12. 查询和设置主键起始和步距

SHOW VARIABLES LIKE  'auto_inc%';

 

SET @@auto_increment_increment=10;

SET @@auto_increment_offset=5;

 

13. 停止slave io 进程

stop slave IO_THREAD;