搭建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 this to one of:
5 # - "/etc/mysql/my.cnf" to set global options,
6 # - "~/.my.cnf" to set user-specific options.
7 #
8 # One can use all long options that the program supports.
9 # Run program with --help to get a list of available options and with
10 # --print-defaults to see which it would actually understand and use.
11 #
12 # For explanations see
13 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
14
15 # This will be passed to all mysql clients
16 # It has been reported that passwords should be enclosed with ticks/quotes
17 # escpecially if they contain "#" chars...
18 # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
19
20 # Here is entries for some specific programs
21 # The following values assume you have at least 32M ram
22
23 [mysqld_safe]
24 socket = /var/run/mysqld/mysqld.sock
25 nice = 0
26
27 [mysqld]
28 server-id=1
29 log-bin=mysql-bin
30 binlog_format=ROW
31 binlog_row_image=minimal
32 binlog-do-db=jgyw
33 #
34 # * Basic Settings
35 #
36 user = mysql
37 pid-file = /var/run/mysqld/mysqld.pid
38 socket = /var/run/mysqld/mysqld.sock
39 port = 3306
40 basedir = /usr
41 datadir = /var/lib/mysql
42 tmpdir = /tmp
43 lc-messages-dir = /usr/share/mysql
44 skip-external-locking
45 #
46 # Instead of skip-networking the default is now to listen only on
47 # localhost which is more compatible and is not less secure.
48 # bind-address = 127.0.0.1
49 bind-address=0.0.0.0
50 #
51 # * Fine Tuning
52 #
53 key_buffer_size = 16M
54 max_allowed_packet = 16M
55 thread_stack = 192K
56 thread_cache_size = 8
57 # This replaces the startup script and checks MyISAM tables if needed
58 # the first time they are touched
59 myisam-recover-options = BACKUP
60 #max_connections = 100
61 #table_open_cache = 64
62 #thread_concurrency = 10
63 #
64 # * Query Cache Configuration
65 #
66 query_cache_limit = 1M
67 query_cache_size = 16M
68 #
69 # * Logging and Replication
70 #
71 # Both location gets rotated by the cronjob.
72 # Be aware that this log type is a performance killer.
73 # As of 5.1 you can enable the log at runtime!
74 #general_log_file = /var/log/mysql/mysql.log
75 #general_log = 1
76 #
77 # Error log - should be very few entries.
78 #
79 log_error = /var/log/mysql/error.log
80 #
81 # Here you can see queries with especially long duration
82 #slow_query_log = 1
83 #slow_query_log_file = /var/log/mysql/mysql-slow.log
84 #long_query_time = 2
85 #log-queries-not-using-indexes
86 #
87 # The following can be used as easy to replay backup logs or for replication.
88 # note: if you are setting up a replication slave, see README.Debian about
89 # other settings you may need to change.
90 #server-id = 1
91 #log_bin = /var/log/mysql/mysql-bin.log
92 expire_logs_days = 10
93 max_binlog_size = 100M
94 #binlog_do_db = include_database_name
95 #binlog_ignore_db = include_database_name
96 #
97 # * InnoDB
98 #
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 Features
103 #
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.pem
110 # ssl-cert=/etc/mysql/server-cert.pem
111 # 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 this to one of:
5 # - "/etc/mysql/my.cnf" to set global options,
6 # - "~/.my.cnf" to set user-specific options.
7 #
8 # One can use all long options that the program supports.
9 # Run program with --help to get a list of available options and with
10 # --print-defaults to see which it would actually understand and use.
11 #
12 # For explanations see
13 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
14
15 # This will be passed to all mysql clients
16 # It has been reported that passwords should be enclosed with ticks/quotes
17 # escpecially if they contain "#" chars...
18 # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
19
20 # Here is entries for some specific programs
21 # The following values assume you have at least 32M ram
22
23 [mysqld_safe]
24 socket = /var/run/mysqld/mysqld.sock
25 nice = 0
26
27 [mysqld]
28 server-id=2
29 log-bin=mysql-bin
30 binlog_format=ROW
31 binlog_row_image=minimal
32 replicate-do-db=jgyw
33 #
34 # * Basic Settings
35 #
36 user = mysql
37 pid-file = /var/run/mysqld/mysqld.pid
38 socket = /var/run/mysqld/mysqld.sock
39 port = 3306
40 basedir = /usr
41 datadir = /var/lib/mysql
42 tmpdir = /tmp
43 lc-messages-dir = /usr/share/mysql
44 skip-external-locking
45 #
46 # Instead of skip-networking the default is now to listen only on
47 # localhost which is more compatible and is not less secure.
48 bind-address = 127.0.0.1
49 #
50 # * Fine Tuning
51 #
52 key_buffer_size = 16M
53 max_allowed_packet = 16M
54 thread_stack = 192K
55 thread_cache_size = 8
56 # This replaces the startup script and checks MyISAM tables if needed
57 # the first time they are touched
58 myisam-recover-options = BACKUP
59 #max_connections = 100
60 #table_open_cache = 64
61 #thread_concurrency = 10
62 #
63 # * Query Cache Configuration
64 #
65 query_cache_limit = 1M
66 query_cache_size = 16M
67 #
68 # * Logging and Replication
69 #
70 # Both location gets rotated by the cronjob.
71 # Be aware that this log type is a performance killer.
72 # As of 5.1 you can enable the log at runtime!
73 #general_log_file = /var/log/mysql/mysql.log
74 #general_log = 1
75 #
76 # Error log - should be very few entries.
77 #
78 log_error = /var/log/mysql/error.log
79 #
80 # Here you can see queries with especially long duration
81 #slow_query_log = 1
82 #slow_query_log_file = /var/log/mysql/mysql-slow.log
83 #long_query_time = 2
84 #log-queries-not-using-indexes
85 #
86 # The following can be used as easy to replay backup logs or for replication.
87 # note: if you are setting up a replication slave, see README.Debian about
88 # other settings you may need to change.
89 #server-id = 1
90 #log_bin = /var/log/mysql/mysql-bin.log
91 expire_logs_days = 10
92 max_binlog_size = 100M
93 #binlog_do_db = include_database_name
94 #binlog_ignore_db = include_database_name
95 #
96 # * InnoDB
97 #
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 Features
102 #
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.pem
109 # ssl-cert=/etc/mysql/server-cert.pem
110 # 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是主库为从库授权的用户名对应的密码,这一点不要弄错了。