mysql数据库的主从复制环境搭建

有两台MySQL数据库服务器Master和slave，Master为主服务器，slave为从服务器，初始状态时，Master和slave中的数据信息相同，当Master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。

原理图如下：

简单来说，mysql的主从复制的原理就是slave把master上面执行的 update，insert这些会使数据发生改变的sql语句从master上面同步过来，然后在自己的机器上再执行一遍，那么这两台数据库服务器上的数据就一模一样了，而那些要同步的sql语句就存在bin-log文件里面

我这里有两台机器分别是192.168.1.6(master) 192.168.1.5(slave)

我们先从master机器开始配置

修改/etc/my.cnf 文件（mysql启动默认是从/etc/my.cnf读取的，所以你别的地方有配置文件的话建议直接移到/etc目录下）

[html]  view plain  copy

1. [client]  
2. port = 3306  
3. socket = /usr/local/mysql/mysql.sock  
4.   
5. [mysqld]  
6. character-set-server = utf8  
7. collation-server = utf8_general_ci  
8.   
9. skip-external-locking  
10. skip-name-resolve  
11.   
12. user = mysql  
13. port = 3306  
14. basedir = /usr/local/mysql  
15. datadir = /home/mysql/data  
16. tmpdir = /home/mysql/temp  
17. # server_id = .....  
18. socket = /usr/local/mysql/mysql.sock  
19. log-error = /home/mysql/logs/mysql_error.log  
20. pid-file = /home/mysql/mysql.pid  
21.   
22. open_files_limit = 10240  
23.   
24. back_log = 600  
25. max_connections=500  
26. max_connect_errors = 6000  
27. wait_timeout=605800  
28.   
29. #open_tables = 600  
30. #table_cache = 650  
31. #opened_tables = 630  
32.   
33. max_allowed_packet = 32M  
34.   
35. sort_buffer_size = 4M  
36. join_buffer_size = 4M  
37. thread_cache_size = 300  
38. query_cache_type = 1  
39. query_cache_size = 256M  
40. query_cache_limit = 2M  
41. query_cache_min_res_unit = 16k  
42.   
43. tmp_table_size = 256M  
44. max_heap_table_size = 256M  
45.   
46. key_buffer_size = 256M  
47. read_buffer_size = 1M  
48. read_rnd_buffer_size = 16M  
49. bulk_insert_buffer_size = 64M  
50.   
51. lower_case_table_names=1  
52.   
53. default-storage-engine = INNODB  
54.   
55. innodb_buffer_pool_size = 2G  
56. innodb_log_buffer_size = 32M  
57. innodb_log_file_size = 128M  
58. innodb_flush_method = O_DIRECT  
59.   
60. #####################  
61. thread_concurrency = 32  
62. long_query_time= 2  
63. slow-query-log = on  
64. slow-query-log-file = /home/mysql/logs/mysql-slow.log    
65.   
66. ## replication  
67. server_id=6  
68. binlog-ignore-db=mysql  
69. log-bin=master-mysql-bin  
70. binlog_cache_size=1M  
71. binlog_format=mixed  
72. expire_logs_days=7  
73. slave_skip_errors=1062  
74.   
75. [mysqldump]  
76. quick  
77. max_allowed_packet = 32M  
78.   
79. [mysqld_safe]  
80. log-error=/var/log/mysqld.log  
81. pid-file=/var/run/mysqld/mysqld.pid  

这里主要的配置就是和复制相关的这段配置，其它配置是mysql源码安装时配置，里面具体参数代表什么意思可以网上搜一下，文档很多的

[html]  view plain  copy

1. ## replication  
2. server_id=6  
3. binlog-ignore-db=mysql  
4. log-bin=master-mysql-bin  
5. binlog_cache_size=1M  
6. binlog_format=mixed  
7. expire_logs_days=7  
8. slave_skip_errors=1062  

我简单解释一下，复制有关的参数

serverid 全局唯一的

binlog-ignore-db=mysql复制过滤，我们不同步mysql系统自带的数据库

log-bin=master-mysql-bin 开启logbin功能并设置logbin文件的名称

binlog_format=mixed 混合型复制模式，默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

接下来重启mysql服务并用客户端登录

slave想要同步master上的数据首先肯定需要权限，所以我们要在master上面开通权限

[html]  view plain  copy

1. grant replication slave, replication client on *.* to 'root'@'192.168.1.5' identified by  
2. 'root';  

账号和密码都是root ，允许192.168.1.5这台机器向master发送同步请求，当然你可以设成别的，我这里只是为了方便记忆，设了太多不同的密码容易忘记

刷新一下授权信息，然后查看当前master的状态

show master status，我这里解释一下，我们知道关于对数据库修改的sql全部记录在了bin-log里面，那么我们就每次都把bin-log里面的sql全部执行一遍？肯定不是，我们肯定要记录我上次复制到哪儿里了，然后我下次再从这个点开始同步，就像我们玩单机游戏闯了3关以后要存下档一样，下次玩我们继续从第三关开始玩，这是一个意思。

接下来我们随便写点测试数据到数据库里面去

[html]  view plain  copy

1. create database if not exists pcx default charset utf8 collate utf8_general_ci;  
2. use pcx;  
3. DROP TABLE IF EXISTS `fruits`;  
4. CREATE TABLE fruits  
5. (  
6. f_id    char(10)        NOT NULL,  
7. s_id    INT         NOT NULL,  
8. f_name  char(255)   NOT NULL,  
9. f_price decimal(8,2)    NOT NULL,  
10. PRIMARY KEY(f_id)   
11. )ENGINE=InnoDB DEFAULT CHARSET=utf8 ;  
12.   
13. INSERT INTO fruits (f_id, s_id, f_name, f_price)  
14.      VALUES('a1', 101,'apple',5.2),  
15.      ('b1',101,'blackberry', 10.2),  
16.      ('bs1',102,'orange', 11.2),  
17.      ('bs2',105,'melon',8.2),  
18.      ('t1',102,'banana', 10.3),  
19.      ('t2',102,'grape', 5.3),  
20.      ('o2',103,'coconut', 9.2),  
21.      ('c0',101,'cherry', 3.2),  
22.      ('a2',103, 'apricot',2.2),  
23.      ('l2',104,'lemon', 6.4),  
24.      ('b2',104,'berry', 7.6),  
25.      ('m1',106,'mango', 15.6),  
26.      ('m2',105,'xbabay', 2.6),  
27.      ('t4',107,'xbababa', 3.6),  
28.      ('m3',105,'xxtt', 11.6),  
29.      ('b5',107,'xxxx', 3.6);  

接下来我们把pcx这个库下的数据全部备份下来

首先我们要锁表

[html]  view plain  copy

1. flush tables with read lock;  

接下来用mysql的备份命令进行备份

最后我们把edu-master.sql发送到slave的机器上面

好了，我们接下来开始配置slave(192.168.1.5)

同样打开my.cnf文件开始配置

[html]  view plain  copy

1. vi /etc/my.cnf  

[html]  view plain  copy

1. [client]  
2. port = 3306  
3. socket = /usr/local/mysql/mysql.sock  
4.   
5. [mysqld]  
6. character-set-server = utf8  
7. collation-server = utf8_general_ci  
8.   
9. skip-external-locking  
10. skip-name-resolve  
11.   
12. user = mysql  
13. port = 3306  
14. basedir = /usr/local/mysql  
15. datadir = /home/mysql/data  
16. tmpdir = /home/mysql/temp  
17. # server_id = .....  
18. socket = /usr/local/mysql/mysql.sock  
19. log-error = /home/mysql/logs/mysql_error.log  
20. pid-file = /home/mysql/mysql.pid  
21.   
22. open_files_limit = 10240  
23.   
24. back_log = 600  
25. max_connections=500  
26. max_connect_errors = 6000  
27. wait_timeout=605800  
28.   
29. #open_tables = 600  
30. #table_cache = 650  
31. #opened_tables = 630  
32.   
33. max_allowed_packet = 32M  
34.   
35. sort_buffer_size = 4M  
36. join_buffer_size = 4M  
37. thread_cache_size = 300  
38. query_cache_type = 1  
39. query_cache_size = 256M  
40. query_cache_limit = 2M  
41. query_cache_min_res_unit = 16k  
42.   
43. tmp_table_size = 256M  
44. max_heap_table_size = 256M  
45.   
46. key_buffer_size = 256M  
47. read_buffer_size = 1M  
48. read_rnd_buffer_size = 16M  
49. bulk_insert_buffer_size = 64M  
50.   
51. lower_case_table_names=1  
52.   
53. default-storage-engine = INNODB  
54.   
55. innodb_buffer_pool_size = 2G  
56. innodb_log_buffer_size = 32M  
57. innodb_log_file_size = 128M  
58. innodb_flush_method = O_DIRECT  
59.   
60. #####################  
61. thread_concurrency = 32  
62. long_query_time= 2  
63. slow-query-log = on  
64. slow-query-log-file = /home/mysql/logs/mysql-slow.log    
65.   
66.   
67. ## replication  
68. server_id=5  
69. binlog-ignore-db=mysql  
70. log-bin=mysql-slave-bin  
71. binlog_cache_size = 1M  
72. binlog_format=mixed  
73. expire_logs_days=7  
74. slave_skip_errors=1062  
75. relay_log=mysql-relay-bin  
76. log_slave_updates=1  
77. read_only=1  
78.   
79.   
80. [mysqldump]  
81. quick  
82. max_allowed_packet = 32M  
83.   
84. [mysqld_safe]  
85. log-error=/var/log/mysqld.log  
86. pid-file=/var/run/mysqld/mysqld.pid  

配置文件和master的差不多，主要差别在一下几点

log-bin=mysql-slave-bin开启了二进制日志，实际上不开也没关系，因为我们这个slave只是做slave，如果你这台slave还有可能要做别人的master的话那么必须开启

relay_log=mysql-relay-bin配置中继日志，用来存放从master的bin-log那边同步来的数据

配置好后重启数据库服务

[html]  view plain  copy

1. service mysql restart  

我们首先要创建一下数据库，因为我们备份下来的数据库文件里面是不包含创建数据库的命令的，所以我们要手动创建一下

把我们从master那边备份过来的数据恢复到slave中

登录 Slave 数据库

在mysql客户端中输入一下命令连接master

change  master  to  ,
master_host='192.168.1.6'master主机的ip地址

 master_user='root',

master_password='root',我们刚刚在master有执行过授权的账号密码就是这个

master_port=3306,master数据库的端口号

 master_log_file='edu-mysql-bin.000002',

master_log_pos=427,这个是我们通过show master status看到的position

master_connect_retry=30;

使用命令查看slave状态，我们可以看到slave目前还未开始同步

[html]  view plain  copy

1. show slave status\G;  

开始主从同步,主要看到两个yes就代表同步成功了

[html]  view plain  copy

1. start slave;  

在master机器上查看状态，可以看见slave的连接信息

[html]  view plain  copy

1. show processlist\G;  

接下来我们测试一下主从复制的功能

master 上原来的数据

这是我插入的记录

我们到slave上面看看有没有,有就代表成功了

至此mysql原生支持的主从复制搭建完毕，当然我们说mysql的主从复制

性能上回有延迟，master上的数据不是无延迟的同步到slave上面，所以如果你对数据的一致性要求非常高的话，那么mysql官方的主从复制就不合适了，可以考虑用别的数据同步方案例如“Galera Cluster for MySQL” ，当然也有人说mysql官方支持的主从复制只是适合在中小规模的集群下运行。