mysql 主主环境搭建_Mysql主从环境搭建

搭建Mysql的主从环境，一台主机，一台从机

主从原理：

1、在主库上把数据更改记录到二进制日志(Binary Log)中，这些记录称为二进制日志事件。

2、从库通过IO线程将主库上的日志复制到自己的中继日志(Relay Log)中。

3、从库通过SQL线程读取中继日志中的事件，将其重放到自己数据上。

Mysql的一些关键文件的位置：

1、配置文件位置：

配置文件一般在/etc/mysql中，如下所示：

2、Binary Log所在的位置

Binary Log文件一般放在/var/lib/mysql 目录，如下所示：

在/var/lib/mysql 下还会有各个数据库的目录，每个目录下就是这个数据库里面的表文件，例如jgyw数据库就会对应一个jgyw目录，目录内容如下所示：

在/var/lib/mysql下还有一个mysql目录，这个是自带的mysql数据库，里面包含很多表，内容如下：

3、log所在的位置

log一般在/var/log/mysql 目录下，如下：

现在这个目录下只有一个error.log，一般还会有慢查询日志mysql-slow.log， Binary Log日志 mysql-bin.log，通用日志mysql.log。

4、字符集文件

字符集文件一般在 /usr/share/mysql 目录，如下：

5、pid文件和sock文件

pid文件和sock文件一般在/var/run/mysqld 目录下，如下所示：

配置主库：

1、修改master机器的配置， 修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf

1 #2 # The MySQL database server configuration file.3 #4 # You can copy thisto one of:5 # - "/etc/mysql/my.cnf" to set globaloptions,6 # - "~/.my.cnf" to set user-specific options.7 #8 # One can use all longoptions that the program supports.9 # Run program with --help to geta list of available options and with10 # --print-defaults to see which it would actually understand and use.11 #12 # For explanations see13 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

14

15 # This will be passed to all mysql clients16 # It has been reported that passwords should be enclosed with ticks/quotes17 # escpecially if they contain "#"chars...18 # Remember to edit /etc/mysql/debian.cnf when changing the socket location.19

20 # Here is entries forsome specific programs21 # The following values assume you have at least 32M ram22

23 [mysqld_safe]24 socket = /var/run/mysqld/mysqld.sock25 nice = 0

26

27 [mysqld]28 server-id=1

29 log-bin=mysql-bin30 binlog_format=ROW31 binlog_row_image=minimal32 binlog-do-db=jgyw33 #34 # *Basic Settings35 #36 user =mysql37 pid-file = /var/run/mysqld/mysqld.pid38 socket = /var/run/mysqld/mysqld.sock39 port = 3306

40 basedir = /usr41 datadir = /var/lib/mysql42 tmpdir = /tmp43 lc-messages-dir = /usr/share/mysql44 skip-external-locking45 #46 # Instead of skip-networking the default isnow to listen only on47 # localhost which is more compatible and isnot less secure.48 # bind-address = 127.0.0.1

49 bind-address=0.0.0.0

50 #51 # *Fine Tuning52 #53 key_buffer_size =16M54 max_allowed_packet =16M55 thread_stack =192K56 thread_cache_size = 8

57 # This replaces the startup script and checks MyISAM tables ifneeded58 # the first time they are touched59 myisam-recover-options =BACKUP60 #max_connections = 100

61 #table_open_cache = 64

62 #thread_concurrency = 10

63 #64 # *Query Cache Configuration65 #66 query_cache_limit =1M67 query_cache_size =16M68 #69 # *Logging and Replication70 #71 # Both location gets rotated by the cronjob.72 # Be aware that this log type isa performance killer.73 # As of 5.1 you can enable the log at runtime!

74 #general_log_file = /var/log/mysql/mysql.log75 #general_log = 1

76 #77 # Error log -should be very few entries.78 #79 log_error = /var/log/mysql/error.log80 #81 # Here you can see queries with especially longduration82 #slow_query_log = 1

83 #slow_query_log_file = /var/log/mysql/mysql-slow.log84 #long_query_time = 2

85 #log-queries-not-using-indexes86 #87 # The following can be used as easy to replay backup logs or forreplication.88 # note: ifyou are setting up a replication slave, see README.Debian about89 # other settings you may need to change.90 #server-id = 1

91 #log_bin = /var/log/mysql/mysql-bin.log92 expire_logs_days = 10

93 max_binlog_size =100M94 #binlog_do_db =include_database_name95 #binlog_ignore_db =include_database_name96 #97 # *InnoDB98 #99 # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.100 # Read the manual for more InnoDB related options. There are many!

101 #102 # *Security Features103 #104 # Read the manual, too, if you want chroot!

105 # chroot = /var/lib/mysql/

106 #107 # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".108 #109 # ssl-ca=/etc/mysql/cacert.pem110 # ssl-cert=/etc/mysql/server-cert.pem111 # ssl-key=/etc/mysql/server-key.pem

28行到32行是为了配置主从而新加的行，48行要改成49行的样子，后面会详细解释。

2、service mysql restart 重启主节点的mysql服务

3、创建从库同步数据的账号

1 grant replication slave on *.* to 'jgyw'@'192.168.61.135' identified by 'jgyw@123';2 flush privileges;

第一行的意思是允许host为192.168.61.135的客户端以jgyw为用户名，jgyw@123为密码来访问这个数据库。

查看jgyw的授权：

1 show grants for jgyw@192.168.61.135;

4、查看主库的执行状态

show master status\G;

主库ip：

主库状态：

配置从库：

1、修改配置文件

1 #2 # The MySQL database server configuration file.3 #4 # You can copy thisto one of:5 # - "/etc/mysql/my.cnf" to set globaloptions,6 # - "~/.my.cnf" to set user-specific options.7 #8 # One can use all longoptions that the program supports.9 # Run program with --help to geta list of available options and with10 # --print-defaults to see which it would actually understand and use.11 #12 # For explanations see13 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html

14

15 # This will be passed to all mysql clients16 # It has been reported that passwords should be enclosed with ticks/quotes17 # escpecially if they contain "#"chars...18 # Remember to edit /etc/mysql/debian.cnf when changing the socket location.19

20 # Here is entries forsome specific programs21 # The following values assume you have at least 32M ram22

23 [mysqld_safe]24 socket = /var/run/mysqld/mysqld.sock25 nice = 0

26

27 [mysqld]28 server-id=2

29 log-bin=mysql-bin30 binlog_format=ROW31 binlog_row_image=minimal32 replicate-do-db=jgyw33 #34 # *Basic Settings35 #36 user =mysql37 pid-file = /var/run/mysqld/mysqld.pid38 socket = /var/run/mysqld/mysqld.sock39 port = 3306

40 basedir = /usr41 datadir = /var/lib/mysql42 tmpdir = /tmp43 lc-messages-dir = /usr/share/mysql44 skip-external-locking45 #46 # Instead of skip-networking the default isnow to listen only on47 # localhost which is more compatible and isnot less secure.48 bind-address = 127.0.0.1

49 #50 # *Fine Tuning51 #52 key_buffer_size =16M53 max_allowed_packet =16M54 thread_stack =192K55 thread_cache_size = 8

56 # This replaces the startup script and checks MyISAM tables ifneeded57 # the first time they are touched58 myisam-recover-options =BACKUP59 #max_connections = 100

60 #table_open_cache = 64

61 #thread_concurrency = 10

62 #63 # *Query Cache Configuration64 #65 query_cache_limit =1M66 query_cache_size =16M67 #68 # *Logging and Replication69 #70 # Both location gets rotated by the cronjob.71 # Be aware that this log type isa performance killer.72 # As of 5.1 you can enable the log at runtime!

73 #general_log_file = /var/log/mysql/mysql.log74 #general_log = 1

75 #76 # Error log -should be very few entries.77 #78 log_error = /var/log/mysql/error.log79 #80 # Here you can see queries with especially longduration81 #slow_query_log = 1

82 #slow_query_log_file = /var/log/mysql/mysql-slow.log83 #long_query_time = 2

84 #log-queries-not-using-indexes85 #86 # The following can be used as easy to replay backup logs or forreplication.87 # note: ifyou are setting up a replication slave, see README.Debian about88 # other settings you may need to change.89 #server-id = 1

90 #log_bin = /var/log/mysql/mysql-bin.log91 expire_logs_days = 10

92 max_binlog_size =100M93 #binlog_do_db =include_database_name94 #binlog_ignore_db =include_database_name95 #96 # *InnoDB97 #98 # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.99 # Read the manual for more InnoDB related options. There are many!

100 #101 # *Security Features102 #103 # Read the manual, too, if you want chroot!

104 # chroot = /var/lib/mysql/

105 #106 # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".107 #108 # ssl-ca=/etc/mysql/cacert.pem109 # ssl-cert=/etc/mysql/server-cert.pem110 # ssl-key=/etc/mysql/server-key.pem

第18行到32行是新加的。

2、重启mysql服务   service mysqld restart

3、执行同步命令

1 # 设置主服务器ip，同步账号密码，同步位置2 change master to master_host='192.168.61.140',master_user='jgyw',master_password='jgyw@123',master_log_file='mysql-bin.000001',master_log_pos=145;3

4 # 开启同步功能5 start slave;

其中master_host是主服务器的ip，master_user是在主服务器上为这个从机设置的用户名，master_password是在主服务器上为这个从机设置的密码。

4、查看从机的状态

1 show slave status\G;

从库ip：

从库状态：

可以看到Slave_IO_Running和Slave_SQL_Running都是yes。说明同步成功了。

如果执行stop slave，那么这两行都为No，说明主从不同步。 需要执行start slave再次同步。

可能出现的问题：

1、master配置文件的第48行是默认配置，默认是监听在127.0.0.1上，这个地址是环回地址，这样的话master数据库就只能接受本机的连接，而不能接受其他机器的连接，表现出来的形式就是从库执行完start slave之后，show slave status一直显示connecting，但是始终连接不上。这时可以在从库所在的机器上用mysql命令尝试去连接一下，例如mysql -uusername -h ip -p。如果连接不上，那么就有可能是主库的配置文件中配置了监听环回地址。这时只需要修改成通配地址即可。

如果修改成通配地址还是连接不上，那么可能是主库的授权账户有问题，使用主库进行授权时需要确认，授权的地址是从库的ip，用户名和密码也是给从库分配的，而这个用户名和密码和主库所在的机器的用户名和密码没有任何关系，这一点需要注意。

授权如果检查没有问题了，这是还是连接不上，那么很有可能是从库进行change master设置时有问题。用change master进行设置时，master_host一定是主库所在机器的ip地址，master_user是主库为这个从库授权的用户名，master_password是主库为从库授权的用户名对应的密码，这一点不要弄错了。

以上几点设置好之后基本就可以连接上了，参考：https://blog.csdn.net/qq_34355232/article/details/88396574