CentOS6.8配置Mysql数据库主从复制

来源：互联网发布：word修改属性软件编辑：程序博客网时间：2024/05/18 00:28

Centos6.8配置MySql5.1数据库主从复制

数据库安装：

1、系统信息：

1. [root@bogon local]# cat /proc/version

2. Linux version 2.6.32-642.el6.x86_64(mockbuild@worker1.bsys.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-17)(GCC) ) #1 SMP Tue May 10 17:27:01 UTC 2016

3. CentOS 6.8 64位CentOS-6.8-x86_64-bin-DVD1.iso

2、安装MySql数据库：如果有冲突：

4. ##[root@bogon develop]# yum remove mysql-libsCentos中彻底删除Mysql（rpm、yum安装的情况）

5. 安装mysql数据库(默认的为5.1)：yum install -ymysql-server mysql mysql-deve

6. 查看安装数据库信息：rpm -qi mysql-server

启动MySql服务

7. [root@bogon ~]# /etc/init.d/mysql star

配置root用户密码

8. /usr/bin/mysqladmin -u root password '密码'

允许远程访问：

9. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '密码' WITH GRANT OPTION;

10. FLUSH PRIVILEGES 刷新权限

###########################################################################

简单的master配置：

11. vi /etc/my.cnf

12. [mysqld]

13. datadir=/var/lib/mysql

14. socket=/var/lib/mysql/mysql.sock

15. user=mysql

16. # Disablingsymbolic-links is recommended to prevent assorted security risks

17. symbolic-links=0

# 设置服务器ID

18. server-id=153

# 设置需要写日志的数据库

19. binlog-do-db=test

# 设置不需要写日志的数据库

20. binlog-ignore-db=mysql

# 日志基于行模式

21. # binlog_format=row

# 二进制日志文件存放位置

log-bin=mysql-bin

22. [mysqld_safe]

23. log-error=/var/log/mysqld.log

24. pid-file=/var/run/mysqld/mysqld.pid

进入sql命令行：

25. mysql> show masterstatus;

26. +------------------+----------+--------------+------------------+

28. +------------------+----------+--------------+------------------+

30. +------------------+----------+--------------+------------------+

31. 1 row in set (0.00 sec)

mysql>

红色部分是必须修改的部分

32. # 创建slave帐号slave，密码123456

33. mysql>grant replication slave on *.* to 'slave'@'%' identified by '123456';

34. # 允许主库可以被从库使用该账号远程访问

35. GRANT ALL PRIVILEGES ON*.* TO 'slave'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

36. FLUSH PRIVILEGES 刷新权限

37. service mysqld restart 重启数据库服务

简单的slave配置：

38. [mysqld]

39. datadir=/var/lib/mysql

40. socket=/var/lib/mysql/mysql.sock

41. user=mysql

42. # Disablingsymbolic-links is recommended to prevent assorted security risks

43. symbolic-links=0

44. server-id=130

45. [mysqld_safe]

46. log-error=/var/log/mysqld.log

47. pid-file=/var/run/mysqld/mysqld.pid

重启mysql服务

进入sql命令行：

1. mysql> show slave status\G;

2. *************************** 1. row***************************

3. Slave_IO_State: Waiting formaster to send event

4. Master_Host: 192.168.100.153

5. Master_User: slave

6. Master_Port: 3306

7. Connect_Retry: 60

8. Master_Log_File: mysql-bin.000004

9. Read_Master_Log_Pos: 696

10. Relay_Log_File: mysqld-relay-bin.000009

11. Relay_Log_Pos: 636

12. Relay_Master_Log_File: mysql-bin.000004

13. Slave_IO_Running: Yes #这两项必须是yes才行（此为no是可能是主库没关防火墙）

14. Slave_SQL_Running: Yes #这两项必须是yes才行

15. Replicate_Do_DB:

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: 696

25. Relay_Log_Space: 792

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)

42.

43. ERROR:

44. No query specified

45.

mysql>

红色部分是不许修改的内容

47. # 执行同步命令，设置主数据库ip，同步帐号密码，同步位置

48. mysql>change master to master_host='192.168.100.153',master_user='slave',master_password='123456',master_log_file='mysqld-relay-bin.000009',master_log_pos=636;

49. # 开启同步功能

50. mysql>start slave;

51. # 停止主从同步

52. mysql> stop slave;

53. # 连接断开时，重新连接超时时间

54. mysql> change master to master_connect_retry=50;

55. # 开启主从同步

56. mysql> start slave;

附录：

关闭防火墙

1、重启后永久性生效：

开启：chkconfig iptables on