CentOS6.5搭建MySQL5.1主从复制

1、主库需改配置文件,在/etc/my.cnf中添加:

  1. port = 3306
  2. log_bin = /var/lib/mysql/mysql-binlog
  3. server-id = 1 //只要主从不一样就行
  4. binlog_do_db = test//要做同步的数据库名字,可以是多个数据库,之间用分号分割。

2、从库的配置文件中添加

  1. server-id = 2
  2. master-host = 10.4.14.168 //主库的ip地址
  3. master-user = gechong //同步的mysql账号
  4. master-password = gechong //同步的mysql密码
  5. master-port = 3306
  6. master-connect-retry = 5
  7. replicate-do-db = test //数据库名

3、分别重启服务,登陆数据库。

4、主库上创建复制用户

  1. mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO gechong@'%' IDENTIFIED BY 'gechong';

5、在主库上执行

  1. mysql> show master status;
  2. +---------------+----------+--------------+------------------+
  3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  4. +---------------+----------+--------------+------------------+
  5. | binlog.000003 | 412 | | |
  6. +---------------+----------+--------------+------------------+
  7. 1 row in set (0.00 sec)

6、从库上执行

  1. mysql> show slave status\G
  2. *************************** 1. row ***************************
  3. Slave_IO_State: Waiting for master to send event
  4. Master_Host: 10.4.14.168
  5. Master_User: gechong
  6. Master_Port: 3306
  7. Connect_Retry: 5
  8. Master_Log_File: mysql-binlog.000001
  9. Read_Master_Log_Pos: 325
  10. Relay_Log_File: mysqld-relay-bin.000002
  11. Relay_Log_Pos: 473
  12. Relay_Master_Log_File: mysql-binlog.000001
  13. Slave_IO_Running: Yes
  14. Slave_SQL_Running: Yes
  15. Replicate_Do_DB: test
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno: 0
  22. Last_Error:
  23. Skip_Counter: 0
  24. Exec_Master_Log_Pos: 325
  25. Relay_Log_Space: 629
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: 0
  29. Master_SSL_Allowed: No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master: 0
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: 0
  38. Last_IO_Error:
  39. Last_SQL_Errno: 0
  40. Last_SQL_Error:
  41. 1 row in set (0.00 sec)

可以看到:Slave_IO_Running | Slave_SQL_Running两个值都是YES,说明配置成功了。可以在主库的test库里执行DML或者DDL验证下。

如果同步不成功:
1:停掉从库

  1. mysql> slave stop

2:主库上找到日志和位置

  1. mysql> show master status;

3:手动同步

  1. mysql> change master to
  2. > master_host='master_ip',
  3. > master_user='gechong',
  4. > master_password='gechong',
  5. > master_port=3306,
  6. > master_log_file='mysql-bin.000020',
  7. > master_log_pos=135617781;
  8. 1 row in set (0.00 sec)

4:启动从库

  1. mysql> slave start;
  2. 1 row in set (0.00 sec)

如果有异常需要跳过:

  1. mysql>slave stop;
  2. >SET GLOBAL sql_slave_skip_counter = 1;
  3. >slave start;

主从搭建一般步骤:

 收藏 (0) 打赏

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

未经允许不得转载:小林博客 www.vvso.cn小林博客 » CentOS6.5搭建MySQL5.1主从复制

分享到: 更多 (0)

评论 抢沙发

春节
快乐
切换注册

登录

忘记密码 ?

切换登录

注册