Centos7 Mysql 5.6 多主一从 解决方案与详细配置
<p><strong>业务场景：</strong></p>
<p>
公司几个主要的业务已经独立，放在不同的数据库服务器上面，但是有一个业务又需要关联多个业务库进行联合查询统计。这时候就需要将不同的业务库数据同步到一台从库进行统计。根据mysql主从同步原理使用多从一主的方案解决。主库使用innodb引擎，从库开启多实例使用myisam引擎并将多个实例的数据同步到同一个目录，并通过flush tables 在一个实例里面访问其他实例的数据。</p>
<p>
<strong>解决思路：</strong></p>
<p>
1、主数据库使用innodb引擎，并设置sql_mode为 no_auto_create_user<br>
2、从库开启多实例，将多个主库里面的数据通过主从复制同步到同一个数据目录。从库的每个实例对应一个主库。多个实例使用同一个数据目录。<br>
3、从库使用myisam引擎，关闭从库默认的innodb引擎,myisam引擎可以访问同一个数据目录里面其他实例的表。<br>
4、从库的每个实例需要执行flush tables 才能看到其他实例表的数据变化，可以设置crontab任务计划每分钟在第一个实例刷新表，以便程序连接的默认实例能看到表的实时变化。<br>
5、设置主库和从库的sql_mode都为no_auto_create_user，只有这样主库的innodb引擎的sql同步到从库的时候才能执行成功。</p>
<p>
<strong>方案架构图：</strong></p>
<p>
<img style="max-width:100%!important;height:auto!important;"title="Centos7 Mysql 5.6 多主一从 解决方案与详细配置" alt="Centos7 Mysql 5.6 多主一从 解决方案与详细配置" src="https://zhuji.jb51.net/uploads/img/202305/ae695113be8dc27c94f7c6947d86a227.jpg"></p>
<p>
<strong>环境说明：</strong></p>
<p>
主库-1：192.168.1.1<br>
主库-2：192.168.1.2<br>
从库-3：192.168.1.3<br>
从库-3：192.168.1.4<br>
从库-3：192.168.1.5</p>
<p>
<strong>实现步骤：（mysql安装步骤这里不在描述）</strong></p>
<p>
1、主数据库配置文件，多个主库配置文件除了server-id不能一样其他都一样。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_190994">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
<div class="line number44 index43 alt1">
44</div>
<div class="line number45 index44 alt2">
45</div>
<div class="line number46 index45 alt1">
46</div>
<div class="line number47 index46 alt2">
47</div>
<div class="line number48 index47 alt1">
48</div>
<div class="line number49 index48 alt2">
49</div>
<div class="line number50 index49 alt1">
50</div>
<div class="line number51 index50 alt2">
51</div>
<div class="line number52 index51 alt1">
52</div>
<div class="line number53 index52 alt2">
53</div>
<div class="line number54 index53 alt1">
54</div>
<div class="line number55 index54 alt2">
55</div>
<div class="line number56 index55 alt1">
56</div>
<div class="line number57 index56 alt2">
57</div>
<div class="line number58 index57 alt1">
58</div>
<div class="line number59 index58 alt2">
59</div>
<div class="line number60 index59 alt1">
60</div>
<div class="line number61 index60 alt2">
61</div>
<div class="line number62 index61 alt1">
62</div>
<div class="line number63 index62 alt2">
63</div>
<div class="line number64 index63 alt1">
64</div>
<div class="line number65 index64 alt2">
65</div>
<div class="line number66 index65 alt1">
66</div>
<div class="line number67 index66 alt2">
67</div>
<div class="line number68 index67 alt1">
68</div>
<div class="line number69 index68 alt2">
69</div>
<div class="line number70 index69 alt1">
70</div>
<div class="line number71 index70 alt2">
71</div>
<div class="line number72 index71 alt1">
72</div>
<div class="line number73 index72 alt2">
73</div>
<div class="line number74 index73 alt1">
74</div>
<div class="line number75 index74 alt2">
75</div>
<div class="line number76 index75 alt1">
76</div>
<div class="line number77 index76 alt2">
77</div>
<div class="line number78 index77 alt1">
78</div>
<div class="line number79 index78 alt2">
79</div>
<div class="line number80 index79 alt1">
80</div>
<div class="line number81 index80 alt2">
81</div>
<div class="line number82 index81 alt1">
82</div>
<div class="line number83 index82 alt2">
83</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">#cat /etc/my.cnf</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain"></code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">port= 3306</code>
</div>
<div class="line number4 index3 alt1">
<code class="plain plain">socket= /tmp/mysql.sock</code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain"></code>
</div>
<div class="line number6 index5 alt1">
<code class="plain plain">port = 3306</code>
</div>
<div class="line number7 index6 alt2">
<code class="plain plain">basedir = /usr/local/mysql</code>
</div>
<div class="line number8 index7 alt1">
<code class="plain plain">datadir = /data/mysql</code>
</div>
<div class="line number9 index8 alt2">
<code class="plain plain">character-set-server = utf8mb4</code>
</div>
<div class="line number10 index9 alt1">
<code class="plain plain">default-storage-engine = innodb</code>
</div>
<div class="line number11 index10 alt2">
<code class="plain plain">socket = /tmp/mysql.sock</code>
</div>
<div class="line number12 index11 alt1">
<code class="plain plain">skip-name-resolv = 1</code>
</div>
<div class="line number13 index12 alt2">
<code class="plain plain">open_files_limit = 65535 </code>
</div>
<div class="line number14 index13 alt1">
<code class="plain plain">back_log = 103</code>
</div>
<div class="line number15 index14 alt2">
<code class="plain plain">max_connections = 512</code>
</div>
<div class="line number16 index15 alt1">
<code class="plain plain">max_connect_errors = 100000</code>
</div>
<div class="line number17 index16 alt2">
<code class="plain plain">table_open_cache = 2048</code>
</div>
<div class="line number18 index17 alt1">
<code class="plain plain">tmp-table-size = 32m</code>
</div>
<div class="line number19 index18 alt2">
<code class="plain plain">max-heap-table-size = 32m</code>
</div>
<div class="line number20 index19 alt1">
<code class="plain plain">#query-cache-type = 0</code>
</div>
<div class="line number21 index20 alt2">
<code class="plain plain">query-cache-size = 0</code>
</div>
<div class="line number22 index21 alt1">
<code class="plain plain">external-locking = false</code>
</div>
<div class="line number23 index22 alt2">
<code class="plain plain">max_allowed_packet = 32m</code>
</div>
<div class="line number24 index23 alt1">
<code class="plain plain">sort_buffer_size = 2m</code>
</div>
<div class="line number25 index24 alt2">
<code class="plain plain">join_buffer_size = 2m</code>
</div>
<div class="line number26 index25 alt1">
<code class="plain plain">thread_cache_size = 51</code>
</div>
<div class="line number27 index26 alt2">
<code class="plain plain">query_cache_size = 32m</code>
</div>
<div class="line number28 index27 alt1">
<code class="plain plain">tmp_table_size = 96m</code>
</div>
<div class="line number29 index28 alt2">
<code class="plain plain">max_heap_table_size = 96m</code>
</div>
<div class="line number30 index29 alt1">
<code class="plain plain">query_cache_type=1</code>
</div>
<div class="line number31 index30 alt2">
<code class="plain plain">log-error=/data/logs/mysqld.log</code>
</div>
<div class="line number32 index31 alt1">
<code class="plain plain">slow_query_log = 1</code>
</div>
<div class="line number33 index32 alt2">
<code class="plain plain">slow_query_log_file = /data/logs/slow.log</code>
</div>
<div class="line number34 index33 alt1">
<code class="plain plain">long_query_time = 0.1</code>
</div>
<div class="line number35 index34 alt2">
<code class="plain plain"># binary logging #</code>
</div>
<div class="line number36 index35 alt1">
<code class="plain plain">server-id = 1</code>
</div>
<div class="line number37 index36 alt2">
<code class="plain plain">log-bin = /data/binlog/mysql-bin</code>
</div>
<div class="line number38 index37 alt1">
<code class="plain plain">log-bin-index =/data/binlog/mysql-bin.index</code>
</div>
<div class="line number39 index38 alt2">
<code class="plain plain">expire-logs-days = 14</code>
</div>
<div class="line number40 index39 alt1">
<code class="plain plain">sync_binlog = 1</code>
</div>
<div class="line number41 index40 alt2">
<code class="plain plain">binlog_cache_size = 4m</code>
</div>
<div class="line number42 index41 alt1">
<code class="plain plain">max_binlog_cache_size = 8m</code>
</div>
<div class="line number43 index42 alt2">
<code class="plain plain">max_binlog_size = 1024m</code>
</div>
<div class="line number44 index43 alt1">
<code class="plain plain">log_slave_updates</code>
</div>
<div class="line number45 index44 alt2">
<code class="plain plain">#binlog_format = row </code>
</div>
<div class="line number46 index45 alt1">
<code class="plain plain">binlog_format = mixed //这里使用的混合模式复制</code>
</div>
<div class="line number47 index46 alt2">
<code class="plain plain">relay_log_recovery = 1</code>
</div>
<div class="line number48 index47 alt1">
<code class="plain plain">#不需要同步的表</code>
</div>
<div class="line number49 index48 alt2">
<code class="plain plain">replicate-wild-ignore-table=mydb.sp_counter</code>
</div>
<div class="line number50 index49 alt1">
<code class="plain plain">#不需要同步的库</code>
</div>
<div class="line number51 index50 alt2">
<code class="plain plain">replicate-ignore-db = mysql,information_schema,performance_schema</code>
</div>
<div class="line number52 index51 alt1">
<code class="plain plain">key_buffer_size = 32m</code>
</div>
<div class="line number53 index52 alt2">
<code class="plain plain">read_buffer_size = 1m</code>
</div>
<div class="line number54 index53 alt1">
<code class="plain plain">read_rnd_buffer_size = 16m</code>
</div>
<div class="line number55 index54 alt2">
<code class="plain plain">bulk_insert_buffer_size = 64m</code>
</div>
<div class="line number56 index55 alt1">
<code class="plain plain">myisam_sort_buffer_size = 128m</code>
</div>
<div class="line number57 index56 alt2">
<code class="plain plain">myisam_max_sort_file_size = 10g</code>
</div>
<div class="line number58 index57 alt1">
<code class="plain plain">myisam_repair_threads = 1</code>
</div>
<div class="line number59 index58 alt2">
<code class="plain plain">myisam_recover</code>
</div>
<div class="line number60 index59 alt1">
<code class="plain plain">transaction_isolation = repeatable-read</code>
</div>
<div class="line number61 index60 alt2">
<code class="plain plain">innodb_additional_mem_pool_size = 16m</code>
</div>
<div class="line number62 index61 alt1">
<code class="plain plain">innodb_buffer_pool_size = 5734m</code>
</div>
<div class="line number63 index62 alt2">
<code class="plain plain">innodb_buffer_pool_load_at_startup = 1</code>
</div>
<div class="line number64 index63 alt1">
<code class="plain plain">innodb_buffer_pool_dump_at_shutdown = 1</code>
</div>
<div class="line number65 index64 alt2">
<code class="plain plain">innodb_data_file_path = ibdata1:1024m:autoextend</code>
</div>
<div class="line number66 index65 alt1">
<code class="plain plain">innodb_flush_log_at_trx_commit = 2</code>
</div>
<div class="line number67 index66 alt2">
<code class="plain plain">innodb_log_buffer_size = 32m</code>
</div>
<div class="line number68 index67 alt1">
<code class="plain plain">innodb_log_file_size = 2g</code>
</div>
<div class="line number69 index68 alt2">
<code class="plain plain">innodb_log_files_in_group = 2</code>
</div>
<div class="line number70 index69 alt1">
<code class="plain plain">innodb_io_capacity = 4000</code>
</div>
<div class="line number71 index70 alt2">
<code class="plain plain">innodb_io_capacity_max = 8000</code>
</div>
<div class="line number72 index71 alt1">
<code class="plain plain">innodb_max_dirty_pages_pct = 50</code>
</div>
<div class="line number73 index72 alt2">
<code class="plain plain">innodb_flush_method = o_direct</code>
</div>
<div class="line number74 index73 alt1">
<code class="plain plain">innodb_file_format = barracuda</code>
</div>
<div class="line number75 index74 alt2">
<code class="plain plain">innodb_file_format_max = barracuda</code>
</div>
<div class="line number76 index75 alt1">
<code class="plain plain">innodb_lock_wait_timeout = 10</code>
</div>
<div class="line number77 index76 alt2">
<code class="plain plain">innodb_rollback_on_timeout = 1</code>
</div>
<div class="line number78 index77 alt1">
<code class="plain plain">innodb_print_all_deadlocks = 1</code>
</div>
<div class="line number79 index78 alt2">
<code class="plain plain">innodb_file_per_table = 1</code>
</div>
<div class="line number80 index79 alt1">
<code class="plain plain">innodb_locks_unsafe_for_binlog = 0</code>
</div>
<div class="line number81 index80 alt2">
<code class="plain plain"></code>
</div>
<div class="line number82 index81 alt1">
<code class="plain plain">quick</code>
</div>
<div class="line number83 index82 alt2">
<code class="plain plain">max_allowed_packet = 32m</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
2、从库配置文件。多个从库配置文件除了server-id不能一样其他都一样。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_151267">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
<div class="line number19 index18 alt2">
19</div>
<div class="line number20 index19 alt1">
20</div>
<div class="line number21 index20 alt2">
21</div>
<div class="line number22 index21 alt1">
22</div>
<div class="line number23 index22 alt2">
23</div>
<div class="line number24 index23 alt1">
24</div>
<div class="line number25 index24 alt2">
25</div>
<div class="line number26 index25 alt1">
26</div>
<div class="line number27 index26 alt2">
27</div>
<div class="line number28 index27 alt1">
28</div>
<div class="line number29 index28 alt2">
29</div>
<div class="line number30 index29 alt1">
30</div>
<div class="line number31 index30 alt2">
31</div>
<div class="line number32 index31 alt1">
32</div>
<div class="line number33 index32 alt2">
33</div>
<div class="line number34 index33 alt1">
34</div>
<div class="line number35 index34 alt2">
35</div>
<div class="line number36 index35 alt1">
36</div>
<div class="line number37 index36 alt2">
37</div>
<div class="line number38 index37 alt1">
38</div>
<div class="line number39 index38 alt2">
39</div>
<div class="line number40 index39 alt1">
40</div>
<div class="line number41 index40 alt2">
41</div>
<div class="line number42 index41 alt1">
42</div>
<div class="line number43 index42 alt2">
43</div>
<div class="line number44 index43 alt1">
44</div>
<div class="line number45 index44 alt2">
45</div>
<div class="line number46 index45 alt1">
46</div>
<div class="line number47 index46 alt2">
47</div>
<div class="line number48 index47 alt1">
48</div>
<div class="line number49 index48 alt2">
49</div>
<div class="line number50 index49 alt1">
50</div>
<div class="line number51 index50 alt2">
51</div>
<div class="line number52 index51 alt1">
52</div>
<div class="line number53 index52 alt2">
53</div>
<div class="line number54 index53 alt1">
54</div>
<div class="line number55 index54 alt2">
55</div>
<div class="line number56 index55 alt1">
56</div>
<div class="line number57 index56 alt2">
57</div>
<div class="line number58 index57 alt1">
58</div>
<div class="line number59 index58 alt2">
59</div>
<div class="line number60 index59 alt1">
60</div>
<div class="line number61 index60 alt2">
61</div>
<div class="line number62 index61 alt1">
62</div>
<div class="line number63 index62 alt2">
63</div>
<div class="line number64 index63 alt1">
64</div>
<div class="line number65 index64 alt2">
65</div>
<div class="line number66 index65 alt1">
66</div>
<div class="line number67 index66 alt2">
67</div>
<div class="line number68 index67 alt1">
68</div>
<div class="line number69 index68 alt2">
69</div>
<div class="line number70 index69 alt1">
70</div>
<div class="line number71 index70 alt2">
71</div>
<div class="line number72 index71 alt1">
72</div>
<div class="line number73 index72 alt2">
73</div>
<div class="line number74 index73 alt1">
74</div>
<div class="line number75 index74 alt2">
75</div>
<div class="line number76 index75 alt1">
76</div>
<div class="line number77 index76 alt2">
77</div>
<div class="line number78 index77 alt1">
78</div>
<div class="line number79 index78 alt2">
79</div>
<div class="line number80 index79 alt1">
80</div>
<div class="line number81 index80 alt2">
81</div>
<div class="line number82 index81 alt1">
82</div>
<div class="line number83 index82 alt2">
83</div>
<div class="line number84 index83 alt1">
84</div>
<div class="line number85 index84 alt2">
85</div>
<div class="line number86 index85 alt1">
86</div>
<div class="line number87 index86 alt2">
87</div>
<div class="line number88 index87 alt1">
88</div>
<div class="line number89 index88 alt2">
89</div>
<div class="line number90 index89 alt1">
90</div>
<div class="line number91 index90 alt2">
91</div>
<div class="line number92 index91 alt1">
92</div>
<div class="line number93 index92 alt2">
93</div>
<div class="line number94 index93 alt1">
94</div>
<div class="line number95 index94 alt2">
95</div>
<div class="line number96 index95 alt1">
96</div>
<div class="line number97 index96 alt2">
97</div>
<div class="line number98 index97 alt1">
98</div>
<div class="line number99 index98 alt2">
99</div>
<div class="line number100 index99 alt1">
100</div>
<div class="line number101 index100 alt2">
101</div>
<div class="line number102 index101 alt1">
102</div>
<div class="line number103 index102 alt2">
103</div>
<div class="line number104 index103 alt1">
104</div>
<div class="line number105 index104 alt2">
105</div>
<div class="line number106 index105 alt1">
106</div>
<div class="line number107 index106 alt2">
107</div>
<div class="line number108 index107 alt1">
108</div>
<div class="line number109 index108 alt2">
109</div>
<div class="line number110 index109 alt1">
110</div>
<div class="line number111 index110 alt2">
111</div>
<div class="line number112 index111 alt1">
112</div>
<div class="line number113 index112 alt2">
113</div>
<div class="line number114 index113 alt1">
114</div>
<div class="line number115 index114 alt2">
115</div>
<div class="line number116 index115 alt1">
116</div>
<div class="line number117 index116 alt2">
117</div>
<div class="line number118 index117 alt1">
118</div>
<div class="line number119 index118 alt2">
119</div>
<div class="line number120 index119 alt1">
120</div>
<div class="line number121 index120 alt2">
121</div>
<div class="line number122 index121 alt1">
122</div>
<div class="line number123 index122 alt2">
123</div>
<div class="line number124 index123 alt1">
124</div>
<div class="line number125 index124 alt2">
125</div>
<div class="line number126 index125 alt1">
126</div>
<div class="line number127 index126 alt2">
127</div>
<div class="line number128 index127 alt1">
128</div>
<div class="line number129 index128 alt2">
129</div>
<div class="line number130 index129 alt1">
130</div>
<div class="line number131 index130 alt2">
131</div>
<div class="line number132 index131 alt1">
132</div>
<div class="line number133 index132 alt2">
133</div>
<div class="line number134 index133 alt1">
134</div>
<div class="line number135 index134 alt2">
135</div>
<div class="line number136 index135 alt1">
136</div>
<div class="line number137 index136 alt2">
137</div>
<div class="line number138 index137 alt1">
138</div>
<div class="line number139 index138 alt2">
139</div>
<div class="line number140 index139 alt1">
140</div>
<div class="line number141 index140 alt2">
141</div>
<div class="line number142 index141 alt1">
142</div>
<div class="line number143 index142 alt2">
143</div>
<div class="line number144 index143 alt1">
144</div>
<div class="line number145 index144 alt2">
145</div>
<div class="line number146 index145 alt1">
146</div>
<div class="line number147 index146 alt2">
147</div>
<div class="line number148 index147 alt1">
148</div>
<div class="line number149 index148 alt2">
149</div>
<div class="line number150 index149 alt1">
150</div>
<div class="line number151 index150 alt2">
151</div>
<div class="line number152 index151 alt1">
152</div>
<div class="line number153 index152 alt2">
153</div>
<div class="line number154 index153 alt1">
154</div>
<div class="line number155 index154 alt2">
155</div>
<div class="line number156 index155 alt1">
156</div>
<div class="line number157 index156 alt2">
157</div>
<div class="line number158 index157 alt1">
158</div>
<div class="line number159 index158 alt2">
159</div>
<div class="line number160 index159 alt1">
160</div>
<div class="line number161 index160 alt2">
161</div>
<div class="line number162 index161 alt1">
162</div>
<div class="line number163 index162 alt2">
163</div>
<div class="line number164 index163 alt1">
164</div>
<div class="line number165 index164 alt2">
165</div>
<div class="line number166 index165 alt1">
166</div>
<div class="line number167 index166 alt2">
167</div>
<div class="line number168 index167 alt1">
168</div>
<div class="line number169 index168 alt2">
169</div>
<div class="line number170 index169 alt1">
170</div>
<div class="line number171 index170 alt2">
171</div>
<div class="line number172 index171 alt1">
172</div>
<div class="line number173 index172 alt2">
173</div>
<div class="line number174 index173 alt1">
174</div>
<div class="line number175 index174 alt2">
175</div>
<div class="line number176 index175 alt1">
176</div>
<div class="line number177 index176 alt2">
177</div>
<div class="line number178 index177 alt1">
178</div>
<div class="line number179 index178 alt2">
179</div>
<div class="line number180 index179 alt1">
180</div>
<div class="line number181 index180 alt2">
181</div>
<div class="line number182 index181 alt1">
182</div>
<div class="line number183 index182 alt2">
183</div>
<div class="line number184 index183 alt1">
184</div>
<div class="line number185 index184 alt2">
185</div>
<div class="line number186 index185 alt1">
186</div>
<div class="line number187 index186 alt2">
187</div>
<div class="line number188 index187 alt1">
188</div>
<div class="line number189 index188 alt2">
189</div>
<div class="line number190 index189 alt1">
190</div>
<div class="line number191 index190 alt2">
191</div>
<div class="line number192 index191 alt1">
192</div>
<div class="line number193 index192 alt2">
193</div>
<div class="line number194 index193 alt1">
194</div>
<div class="line number195 index194 alt2">
195</div>
<div class="line number196 index195 alt1">
196</div>
<div class="line number197 index196 alt2">
197</div>
<div class="line number198 index197 alt1">
198</div>
<div class="line number199 index198 alt2">
199</div>
<div class="line number200 index199 alt1">
200</div>
<div class="line number201 index200 alt2">
201</div>
<div class="line number202 index201 alt1">
202</div>
<div class="line number203 index202 alt2">
203</div>
<div class="line number204 index203 alt1">
204</div>
<div class="line number205 index204 alt2">
205</div>
<div class="line number206 index205 alt1">
206</div>
<div class="line number207 index206 alt2">
207</div>
<div class="line number208 index207 alt1">
208</div>
<div class="line number209 index208 alt2">
209</div>
<div class="line number210 index209 alt1">
210</div>
<div class="line number211 index210 alt2">
211</div>
<div class="line number212 index211 alt1">
212</div>
<div class="line number213 index212 alt2">
213</div>
<div class="line number214 index213 alt1">
214</div>
<div class="line number215 index214 alt2">
215</div>
<div class="line number216 index215 alt1">
216</div>
<div class="line number217 index216 alt2">
217</div>
<div class="line number218 index217 alt1">
218</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain"># cat /etc/my.cnf</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain"></code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">port= 3306</code>
</div>
<div class="line number4 index3 alt1">
<code class="plain plain">socket= /tmp/mysql.sock</code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain"></code>
</div>
<div class="line number6 index5 alt1">
<code class="plain plain"># 指定相关命令的路径</code>
</div>
<div class="line number7 index6 alt2">
<code class="plain plain">mysqld = /usr/local/mysql/bin/mysqld_safe</code>
</div>
<div class="line number8 index7 alt1">
<code class="plain plain">mysqladmin = /usr/local/mysql/bin/mysqladmin</code>
</div>
<div class="line number9 index8 alt2">
<code class="plain plain">##复制主库1的数据##</code>
</div>
<div class="line number10 index9 alt1">
<code class="plain plain"></code>
</div>
<div class="line number11 index10 alt2">
<code class="plain plain">port = 3306</code>
</div>
<div class="line number12 index11 alt1">
<code class="plain plain">basedir = /usr/local/mysql</code>
</div>
<div class="line number13 index12 alt2">
<code class="plain plain">datadir = /data/mysql</code>
</div>
<div class="line number14 index13 alt1">
<code class="plain plain">character-set-server = utf8mb4</code>
</div>
<div class="line number15 index14 alt2">
<code class="plain plain">#指定实例1的sock文件和pid文件</code>
</div>
<div class="line number16 index15 alt1">
<code class="plain plain">socket = /tmp/mysql.sock</code>
</div>
<div class="line number17 index16 alt2">
<code class="plain plain">pid-file=/data/mysql/mysql.pid</code>
</div>
<div class="line number18 index17 alt1">
<code class="plain plain">skip-name-resolv = 1</code>
</div>
<div class="line number19 index18 alt2">
<code class="plain plain">open_files_limit = 65535 </code>
</div>
<div class="line number20 index19 alt1">
<code class="plain plain">back_log = 103</code>
</div>
<div class="line number21 index20 alt2">
<code class="plain plain">max_connections = 512</code>
</div>
<div class="line number22 index21 alt1">
<code class="plain plain">max_connect_errors = 100000</code>
</div>
<div class="line number23 index22 alt2">
<code class="plain plain">table_open_cache = 2048</code>
</div>
<div class="line number24 index23 alt1">
<code class="plain plain">tmp-table-size = 32m</code>
</div>
<div class="line number25 index24 alt2">
<code class="plain plain">max-heap-table-size = 32m</code>
</div>
<div class="line number26 index25 alt1">
<code class="plain plain">query-cache-size = 0</code>
</div>
<div class="line number27 index26 alt2">
<code class="plain plain">external-locking = false</code>
</div>
<div class="line number28 index27 alt1">
<code class="plain plain">max_allowed_packet = 32m</code>
</div>
<div class="line number29 index28 alt2">
<code class="plain plain">sort_buffer_size = 2m</code>
</div>
<div class="line number30 index29 alt1">
<code class="plain plain">join_buffer_size = 2m</code>
</div>
<div class="line number31 index30 alt2">
<code class="plain plain">thread_cache_size = 51</code>
</div>
<div class="line number32 index31 alt1">
<code class="plain plain">query_cache_size = 32m</code>
</div>
<div class="line number33 index32 alt2">
<code class="plain plain">tmp_table_size = 96m</code>
</div>
<div class="line number34 index33 alt1">
<code class="plain plain">max_heap_table_size = 96m</code>
</div>
<div class="line number35 index34 alt2">
<code class="plain plain">query_cache_type=1</code>
</div>
<div class="line number36 index35 alt1">
<code class="plain plain">#指定第一个实例的错误日志和慢查询日志路径</code>
</div>
<div class="line number37 index36 alt2">
<code class="plain plain">log-error=/data/logs/mysqld.log</code>
</div>
<div class="line number38 index37 alt1">
<code class="plain plain">slow_query_log = 1</code>
</div>
<div class="line number39 index38 alt2">
<code class="plain plain">slow_query_log_file = /data/logs/slow.log</code>
</div>
<div class="line number40 index39 alt1">
<code class="plain plain">long_query_time = 0.1</code>
</div>
<div class="line number41 index40 alt2">
<code class="plain plain"># binary logging#</code>
</div>
<div class="line number42 index41 alt1">
<code class="plain plain"># 指定实例1的binlog和relaylog路径为/data/binlog目录</code>
</div>
<div class="line number43 index42 alt2">
<code class="plain plain"># 每个从库和每个实例的server_id不能一样。</code>
</div>
<div class="line number44 index43 alt1">
<code class="plain plain">server-id = 2</code>
</div>
<div class="line number45 index44 alt2">
<code class="plain plain">log-bin = /data/binlog/mysql-bin</code>
</div>
<div class="line number46 index45 alt1">
<code class="plain plain">log-bin-index =/data/binlog/mysql-bin.index</code>
</div>
<div class="line number47 index46 alt2">
<code class="plain plain">relay_log = /data/binlog/mysql-relay-bin</code>
</div>
<div class="line number48 index47 alt1">
<code class="plain plain">relay_log_index = /data/binlog/mysql-relay.index</code>
</div>
<div class="line number49 index48 alt2">
<code class="plain plain">master-info-file = /data/mysql/master.info</code>
</div>
<div class="line number50 index49 alt1">
<code class="plain plain">relay_log_info_file = /data/mysql/relay-log.info</code>
</div>
<div class="line number51 index50 alt2">
<code class="plain plain">read_only = 1</code>
</div>
<div class="line number52 index51 alt1">
<code class="plain plain">expire-logs-days = 14</code>
</div>
<div class="line number53 index52 alt2">
<code class="plain plain">sync_binlog = 1</code>
</div>
<div class="line number54 index53 alt1">
<code class="plain plain">#需要同步的库，如果不设置，默认同步所有库。</code>
</div>
<div class="line number55 index54 alt2">
<code class="plain plain">#replicate-do-db = xxx</code>
</div>
<div class="line number56 index55 alt1">
<code class="plain plain">#不需要同步的表</code>
</div>
<div class="line number57 index56 alt2">
<code class="plain plain">replicate-wild-ignore-table=mydb.sp_counter</code>
</div>
<div class="line number58 index57 alt1">
<code class="plain plain">#不需要同步的库</code>
</div>
<div class="line number59 index58 alt2">
<code class="plain plain">replicate-ignore-db = mysql,information_schema,performance_schema</code>
</div>
<div class="line number60 index59 alt1">
<code class="plain plain">binlog_cache_size = 4m</code>
</div>
<div class="line number61 index60 alt2">
<code class="plain plain">max_binlog_cache_size = 8m</code>
</div>
<div class="line number62 index61 alt1">
<code class="plain plain">max_binlog_size = 1024m</code>
</div>
<div class="line number63 index62 alt2">
<code class="plain plain">log_slave_updates =1</code>
</div>
<div class="line number64 index63 alt1">
<code class="plain plain">#binlog_format = row </code>
</div>
<div class="line number65 index64 alt2">
<code class="plain plain">binlog_format = mixed</code>
</div>
<div class="line number66 index65 alt1">
<code class="plain plain">relay_log_recovery = 1</code>
</div>
<div class="line number67 index66 alt2">
<code class="plain plain">key_buffer_size = 32m</code>
</div>
<div class="line number68 index67 alt1">
<code class="plain plain">read_buffer_size = 1m</code>
</div>
<div class="line number69 index68 alt2">
<code class="plain plain">read_rnd_buffer_size = 16m</code>
</div>
<div class="line number70 index69 alt1">
<code class="plain plain">bulk_insert_buffer_size = 64m</code>
</div>
<div class="line number71 index70 alt2">
<code class="plain plain">myisam_sort_buffer_size = 128m</code>
</div>
<div class="line number72 index71 alt1">
<code class="plain plain">myisam_max_sort_file_size = 10g</code>
</div>
<div class="line number73 index72 alt2">
<code class="plain plain">myisam_repair_threads = 1</code>
</div>
<div class="line number74 index73 alt1">
<code class="plain plain">myisam_recover</code>
</div>
<div class="line number75 index74 alt2">
<code class="plain plain">#设置默认引擎为myisam，下面这些参数一定要加上。</code>
</div>
<div class="line number76 index75 alt1">
<code class="plain plain">default-storage-engine=myisam</code>
</div>
<div class="line number77 index76 alt2">
<code class="plain plain">default-tmp-storage-engine=myisam</code>
</div>
<div class="line number78 index77 alt1">
<code class="plain plain">#关闭innodb引擎</code>
</div>
<div class="line number79 index78 alt2">
<code class="plain plain">skip-innodb</code>
</div>
<div class="line number80 index79 alt1">
<code class="plain plain">innodb = off</code>
</div>
<div class="line number81 index80 alt2">
<code class="plain plain">disable-innodb</code>
</div>
<div class="line number82 index81 alt1">
<code class="plain plain">#设置sql_mode模式为no_auto_create_user</code>
</div>
<div class="line number83 index82 alt2">
<code class="plain plain">sql_mode = no_auto_create_user</code>
</div>
<div class="line number84 index83 alt1">
<code class="plain plain">#关闭innodb引擎</code>
</div>
<div class="line number85 index84 alt2">
<code class="plain plain">loose-skip-innodb</code>
</div>
<div class="line number86 index85 alt1">
<code class="plain plain">loose-innodb-trx=0 </code>
</div>
<div class="line number87 index86 alt2">
<code class="plain plain">loose-innodb-locks=0 </code>
</div>
<div class="line number88 index87 alt1">
<code class="plain plain">loose-innodb-lock-waits=0 </code>
</div>
<div class="line number89 index88 alt2">
<code class="plain plain">loose-innodb-cmp=0 </code>
</div>
<div class="line number90 index89 alt1">
<code class="plain plain">loose-innodb-cmp-per-index=0</code>
</div>
<div class="line number91 index90 alt2">
<code class="plain plain">loose-innodb-cmp-per-index-reset=0</code>
</div>
<div class="line number92 index91 alt1">
<code class="plain plain">loose-innodb-cmp-reset=0 </code>
</div>
<div class="line number93 index92 alt2">
<code class="plain plain">loose-innodb-cmpmem=0 </code>
</div>
<div class="line number94 index93 alt1">
<code class="plain plain">loose-innodb-cmpmem-reset=0 </code>
</div>
<div class="line number95 index94 alt2">
<code class="plain plain">loose-innodb-buffer-page=0 </code>
</div>
<div class="line number96 index95 alt1">
<code class="plain plain">loose-innodb-buffer-page-lru=0 </code>
</div>
<div class="line number97 index96 alt2">
<code class="plain plain">loose-innodb-buffer-pool-stats=0 </code>
</div>
<div class="line number98 index97 alt1">
<code class="plain plain">loose-innodb-metrics=0 </code>
</div>
<div class="line number99 index98 alt2">
<code class="plain plain">loose-innodb-ft-default-stopword=0 </code>
</div>
<div class="line number100 index99 alt1">
<code class="plain plain">loose-innodb-ft-inserted=0 </code>
</div>
<div class="line number101 index100 alt2">
<code class="plain plain">loose-innodb-ft-deleted=0 </code>
</div>
<div class="line number102 index101 alt1">
<code class="plain plain">loose-innodb-ft-being-deleted=0 </code>
</div>
<div class="line number103 index102 alt2">
<code class="plain plain">loose-innodb-ft-config=0 </code>
</div>
<div class="line number104 index103 alt1">
<code class="plain plain">loose-innodb-ft-index-cache=0 </code>
</div>
<div class="line number105 index104 alt2">
<code class="plain plain">loose-innodb-ft-index-table=0 </code>
</div>
<div class="line number106 index105 alt1">
<code class="plain plain">loose-innodb-sys-tables=0 </code>
</div>
<div class="line number107 index106 alt2">
<code class="plain plain">loose-innodb-sys-tablestats=0 </code>
</div>
<div class="line number108 index107 alt1">
<code class="plain plain">loose-innodb-sys-indexes=0 </code>
</div>
<div class="line number109 index108 alt2">
<code class="plain plain">loose-innodb-sys-columns=0 </code>
</div>
<div class="line number110 index109 alt1">
<code class="plain plain">loose-innodb-sys-fields=0 </code>
</div>
<div class="line number111 index110 alt2">
<code class="plain plain">loose-innodb-sys-foreign=0 </code>
</div>
<div class="line number112 index111 alt1">
<code class="plain plain">loose-innodb-sys-foreign-cols=0</code>
</div>
<div class="line number113 index112 alt2">
<code class="plain spaces"> </code>
</div>
<div class="line number114 index113 alt1">
<code class="plain spaces"> </code>
</div>
<div class="line number115 index114 alt2">
<code class="plain plain">##复制主库2的数据##</code>
</div>
<div class="line number116 index115 alt1">
<code class="plain plain"></code>
</div>
<div class="line number117 index116 alt2">
<code class="plain plain">port = 3307</code>
</div>
<div class="line number118 index117 alt1">
<code class="plain plain">basedir = /usr/local/mysql</code>
</div>
<div class="line number119 index118 alt2">
<code class="plain plain">datadir = /data/mysql</code>
</div>
<div class="line number120 index119 alt1">
<code class="plain plain">character-set-server = utf8mb4</code>
</div>
<div class="line number121 index120 alt2">
<code class="plain plain">#指定实例2的sock文件和pid文件</code>
</div>
<div class="line number122 index121 alt1">
<code class="plain plain">socket = /tmp/mysql3.sock</code>
</div>
<div class="line number123 index122 alt2">
<code class="plain plain">pid-file=/data/mysql/mysql3.pid</code>
</div>
<div class="line number124 index123 alt1">
<code class="plain plain">skip-name-resolv = 1</code>
</div>
<div class="line number125 index124 alt2">
<code class="plain plain">open_files_limit = 65535 </code>
</div>
<div class="line number126 index125 alt1">
<code class="plain plain">back_log = 103</code>
</div>
<div class="line number127 index126 alt2">
<code class="plain plain">max_connections = 512</code>
</div>
<div class="line number128 index127 alt1">
<code class="plain plain">max_connect_errors = 100000</code>
</div>
<div class="line number129 index128 alt2">
<code class="plain plain">table_open_cache = 2048</code>
</div>
<div class="line number130 index129 alt1">
<code class="plain plain">tmp-table-size = 32m</code>
</div>
<div class="line number131 index130 alt2">
<code class="plain plain">max-heap-table-size = 32m</code>
</div>
<div class="line number132 index131 alt1">
<code class="plain plain">query-cache-size = 0</code>
</div>
<div class="line number133 index132 alt2">
<code class="plain plain">external-locking = false</code>
</div>
<div class="line number134 index133 alt1">
<code class="plain plain">max_allowed_packet = 32m</code>
</div>
<div class="line number135 index134 alt2">
<code class="plain plain">sort_buffer_size = 2m</code>
</div>
<div class="line number136 index135 alt1">
<code class="plain plain">join_buffer_size = 2m</code>
</div>
<div class="line number137 index136 alt2">
<code class="plain plain">thread_cache_size = 51</code>
</div>
<div class="line number138 index137 alt1">
<code class="plain plain">query_cache_size = 32m</code>
</div>
<div class="line number139 index138 alt2">
<code class="plain plain">tmp_table_size = 96m</code>
</div>
<div class="line number140 index139 alt1">
<code class="plain plain">max_heap_table_size = 96m</code>
</div>
<div class="line number141 index140 alt2">
<code class="plain plain">query_cache_type=1</code>
</div>
<div class="line number142 index141 alt1">
<code class="plain plain">log-error=/data/logs/mysqld3.log</code>
</div>
<div class="line number143 index142 alt2">
<code class="plain plain">slow_query_log = 1</code>
</div>
<div class="line number144 index143 alt1">
<code class="plain plain">slow_query_log_file = /data/logs/slow3.log</code>
</div>
<div class="line number145 index144 alt2">
<code class="plain plain">long_query_time = 0.1</code>
</div>
<div class="line number146 index145 alt1">
<code class="plain plain"># binary logging #</code>
</div>
<div class="line number147 index146 alt2">
<code class="plain plain"># 这里一定要注意，不能把两个实例的binlog和relaylog放到同一个目录，</code>
</div>
<div class="line number148 index147 alt1">
<code class="plain plain"># 这里指定实例2的binlog日志为/data/binlog2目录</code>
</div>
<div class="line number149 index148 alt2">
<code class="plain plain"># 每个从库和每个实例的server_id不能一样。</code>
</div>
<div class="line number150 index149 alt1">
<code class="plain plain">server-id = 22</code>
</div>
<div class="line number151 index150 alt2">
<code class="plain plain">log-bin = /data/binlog2/mysql-bin</code>
</div>
<div class="line number152 index151 alt1">
<code class="plain plain">log-bin-index =/data/binlog2/mysql-bin.index</code>
</div>
<div class="line number153 index152 alt2">
<code class="plain plain">relay_log = /data/binlog2/mysql-relay-bin</code>
</div>
<div class="line number154 index153 alt1">
<code class="plain plain">relay_log_index = /data/binlog2/mysql-relay.index</code>
</div>
<div class="line number155 index154 alt2">
<code class="plain plain">master-info-file = /data/mysql/master3.info</code>
</div>
<div class="line number156 index155 alt1">
<code class="plain plain">relay_log_info_file = /data/mysql/relay-log3.info</code>
</div>
<div class="line number157 index156 alt2">
<code class="plain plain">read_only = 1</code>
</div>
<div class="line number158 index157 alt1">
<code class="plain plain">expire-logs-days = 14</code>
</div>
<div class="line number159 index158 alt2">
<code class="plain plain">sync_binlog = 1</code>
</div>
<div class="line number160 index159 alt1">
<code class="plain plain">#不需要复制的库</code>
</div>
<div class="line number161 index160 alt2">
<code class="plain plain">replicate-ignore-db = mysql,information_schema,performance_schema</code>
</div>
<div class="line number162 index161 alt1">
<code class="plain plain">binlog_cache_size = 4m</code>
</div>
<div class="line number163 index162 alt2">
<code class="plain plain">max_binlog_cache_size = 8m</code>
</div>
<div class="line number164 index163 alt1">
<code class="plain plain">max_binlog_size = 1024m</code>
</div>
<div class="line number165 index164 alt2">
<code class="plain plain">log_slave_updates =1</code>
</div>
<div class="line number166 index165 alt1">
<code class="plain plain">#binlog_format = row </code>
</div>
<div class="line number167 index166 alt2">
<code class="plain plain">binlog_format = mixed</code>
</div>
<div class="line number168 index167 alt1">
<code class="plain plain">relay_log_recovery = 1</code>
</div>
<div class="line number169 index168 alt2">
<code class="plain plain">key_buffer_size = 32m</code>
</div>
<div class="line number170 index169 alt1">
<code class="plain plain">read_buffer_size = 1m</code>
</div>
<div class="line number171 index170 alt2">
<code class="plain plain">read_rnd_buffer_size = 16m</code>
</div>
<div class="line number172 index171 alt1">
<code class="plain plain">bulk_insert_buffer_size = 64m</code>
</div>
<div class="line number173 index172 alt2">
<code class="plain plain">myisam_sort_buffer_size = 128m</code>
</div>
<div class="line number174 index173 alt1">
<code class="plain plain">myisam_max_sort_file_size = 10g</code>
</div>
<div class="line number175 index174 alt2">
<code class="plain plain">myisam_repair_threads = 1</code>
</div>
<div class="line number176 index175 alt1">
<code class="plain plain">myisam_recover</code>
</div>
<div class="line number177 index176 alt2">
<code class="plain plain">#设置默认引擎为myisam</code>
</div>
<div class="line number178 index177 alt1">
<code class="plain plain">default-storage-engine=myisam</code>
</div>
<div class="line number179 index178 alt2">
<code class="plain plain">default-tmp-storage-engine=myisam</code>
</div>
<div class="line number180 index179 alt1">
<code class="plain plain">#关闭innodb引擎</code>
</div>
<div class="line number181 index180 alt2">
<code class="plain plain">skip-innodb</code>
</div>
<div class="line number182 index181 alt1">
<code class="plain plain">innodb = off</code>
</div>
<div class="line number183 index182 alt2">
<code class="plain plain">disable-innodb</code>
</div>
<div class="line number184 index183 alt1">
<code class="plain plain">#设置sql_mode模式为no_auto_create_user</code>
</div>
<div class="line number185 index184 alt2">
<code class="plain plain">sql_mode = no_auto_create_user</code>
</div>
<div class="line number186 index185 alt1">
<code class="plain plain">#关闭innodb引擎，下面这些参数一定要加上。</code>
</div>
<div class="line number187 index186 alt2">
<code class="plain plain">loose-skip-innodb</code>
</div>
<div class="line number188 index187 alt1">
<code class="plain plain">loose-innodb-trx=0 </code>
</div>
<div class="line number189 index188 alt2">
<code class="plain plain">loose-innodb-locks=0 </code>
</div>
<div class="line number190 index189 alt1">
<code class="plain plain">loose-innodb-lock-waits=0 </code>
</div>
<div class="line number191 index190 alt2">
<code class="plain plain">loose-innodb-cmp=0 </code>
</div>
<div class="line number192 index191 alt1">
<code class="plain plain">loose-innodb-cmp-per-index=0</code>
</div>
<div class="line number193 index192 alt2">
<code class="plain plain">loose-innodb-cmp-per-index-reset=0</code>
</div>
<div class="line number194 index193 alt1">
<code class="plain plain">loose-innodb-cmp-reset=0 </code>
</div>
<div class="line number195 index194 alt2">
<code class="plain plain">loose-innodb-cmpmem=0 </code>
</div>
<div class="line number196 index195 alt1">
<code class="plain plain">loose-innodb-cmpmem-reset=0 </code>
</div>
<div class="line number197 index196 alt2">
<code class="plain plain">loose-innodb-buffer-page=0 </code>
</div>
<div class="line number198 index197 alt1">
<code class="plain plain">loose-innodb-buffer-page-lru=0 </code>
</div>
<div class="line number199 index198 alt2">
<code class="plain plain">loose-innodb-buffer-pool-stats=0 </code>
</div>
<div class="line number200 index199 alt1">
<code class="plain plain">loose-innodb-metrics=0 </code>
</div>
<div class="line number201 index200 alt2">
<code class="plain plain">loose-innodb-ft-default-stopword=0 </code>
</div>
<div class="line number202 index201 alt1">
<code class="plain plain">loose-innodb-ft-inserted=0 </code>
</div>
<div class="line number203 index202 alt2">
<code class="plain plain">loose-innodb-ft-deleted=0 </code>
</div>
<div class="line number204 index203 alt1">
<code class="plain plain">loose-innodb-ft-being-deleted=0 </code>
</div>
<div class="line number205 index204 alt2">
<code class="plain plain">loose-innodb-ft-config=0 </code>
</div>
<div class="line number206 index205 alt1">
<code class="plain plain">loose-innodb-ft-index-cache=0 </code>
</div>
<div class="line number207 index206 alt2">
<code class="plain plain">loose-innodb-ft-index-table=0 </code>
</div>
<div class="line number208 index207 alt1">
<code class="plain plain">loose-innodb-sys-tables=0 </code>
</div>
<div class="line number209 index208 alt2">
<code class="plain plain">loose-innodb-sys-tablestats=0 </code>
</div>
<div class="line number210 index209 alt1">
<code class="plain plain">loose-innodb-sys-indexes=0 </code>
</div>
<div class="line number211 index210 alt2">
<code class="plain plain">loose-innodb-sys-columns=0 </code>
</div>
<div class="line number212 index211 alt1">
<code class="plain plain">loose-innodb-sys-fields=0 </code>
</div>
<div class="line number213 index212 alt2">
<code class="plain plain">loose-innodb-sys-foreign=0 </code>
</div>
<div class="line number214 index213 alt1">
<code class="plain plain">loose-innodb-sys-foreign-cols=0</code>
</div>
<div class="line number215 index214 alt2">
<code class="plain plain"></code>
</div>
<div class="line number216 index215 alt1">
<code class="plain plain">quick</code>
</div>
<div class="line number217 index216 alt2">
<code class="plain plain">max_allowed_packet = 32m</code>
</div>
<div class="line number218 index217 alt1">
<code class="plain plain">```</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
3、设置主库sql_mode,mysql5.6默认需要在启动文件文件里面设置sql_mode才可以生效。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_510445">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain"># cat /etc/init.d/mysqld</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">#other_args="$*" # uncommon, but needed when called from an rpm upgrade action</code>
</div>
<div class="line number3 index2 alt2">
<code class="plain spaces"> </code><code class="plain plain"># expected: "--skip-networking --skip-grant-tables"</code>
</div>
<div class="line number4 index3 alt1">
<code class="plain spaces"> </code><code class="plain plain"># they are not checked here, intentionally, as it is the resposibility</code>
</div>
<div class="line number5 index4 alt2">
<code class="plain spaces"> </code><code class="plain plain"># of the "spec" file author to give correct arguments only.</code>
</div>
<div class="line number6 index5 alt1">
<code class="plain plain">#将上面默认的#other_args开启后改为</code>
</div>
<div class="line number7 index6 alt2">
<code class="plain plain">other_args="--sql-mode=no_auto_create_user"</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
4、开启主库和从库</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_32872">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">#主库</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">service mysqld start</code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">#开启从库的二个实例</code>
</div>
<div class="line number4 index3 alt1">
<code class="plain plain">/usr/local/mysql/bin/mysqld_multi start 2</code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain">/usr/local/mysql/bin/mysqld_multi start 3</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
5、在两台主库上面分别授权复制账号</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_507538">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">#需要授权三个从库的ip可以同步</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">mysql> grant replication slave on *.* to rep@'192.168.1.3' identified by 'rep123';</code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">mysql> grant replication slave on *.* to rep@'192.168.1.4' identified by 'rep123';</code>
</div>
<div class="line number4 index3 alt1">
<code class="plain plain">mysql> grant replication slave on *.* to rep@'192.168.1.5' identified by 'rep123';</code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain">mysql> flush privileges;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
6、在三个从库分别开启同步。</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_546671">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain">#进入第一个实例执行</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">$ mysql -s /tmp/mysql.sock</code>
</div>
<div class="line number3 index2 alt2">
<code class="plain plain">mysql> change master to master_host='192.168.1.1',master_user='rep',master_password='rep123',master_log_file='mysql-bin.000001',master_log_pos=112;</code>
</div>
<div class="line number4 index3 alt1">
<code class="plain spaces"> </code>
</div>
<div class="line number5 index4 alt2">
<code class="plain plain">#进入第二个实例执行</code>
</div>
<div class="line number6 index5 alt1">
<code class="plain plain">$ mysql -s /tmp/mysql3.sock</code>
</div>
<div class="line number7 index6 alt2">
<code class="plain plain">mysql> change master to master_host='192.168.1.2',master_user='rep',master_password='rep123',master_log_file='mysql-bin.000001',master_log_pos=112;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
7、测试数据同步</p>
<p>
在二个主数据库分别建表和插入数据，到从库查看可以看到二个主库同步到同一个从库上面的所有数据。</p>
<p>
8、在每台从库服务器上设置任务计划每分钟刷新第一个实例的表</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterplain" id="highlighter_704471">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="plain plain"># crontab -l</code>
</div>
<div class="line number2 index1 alt1">
<code class="plain plain">*/1 * * * * mysql -s /tmp/mysql.sock -e 'flush tables;'</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
</div>
<p>
<strong>mysql5.6多主一从的坑</strong></p>
<p>
</p>
<p>
1、mysql5.6默认的引擎是innodb默认同步的时候一定要把主和从的sql_mode模式里面的no_engine_substitution这个参数关闭。如果不关闭innodb同步到从库上面的sql将会找不到innodb引擎导致同步失败。</p>
<p>
2、在mysql5.6开启多实例的时候第一次启动的时候在你数据库的安装目录里面（/usr/local/mysql/)会生成my.cnf配置文件，默认会优先读取数据库安装目录里面的配置文件。导致多实例不生效。</p>
頁:
[1]