MySQL主主複制

       MySQL主主複制結構差別于主從複制結構。在主主複制結構中，兩台伺服器的任何一台上面的資料庫存發生了改變都會同步到另一台伺服器上，這樣兩台伺服器互為主從，并且都能向外提供服務。

一、搭建環境描述：

主機A IP：192.168.1.201

主機B IP：192.168.1.202

作業系統：centos6.5-x86

Mysql版本：mysql-5.7.12-linux-glibc2.5-x86_64.tar.gz

安裝所需依賴包：yum install  libaio-devel   –y

關閉SElinux及IPtables。

二、配置主主複制：

1、修改配置檔案

vim /etc/my.cnf

<code>[mysqld]</code>

<code>########basic settings########</code>

<code>server-</code><code>id</code> <code>= 1    </code><code>#另一台ID不同</code>

<code>port = 3306</code>

<code>user = mysql</code>

<code>bind_address = 192.168.1.201    </code><code>#另一台IP不用</code>

<code>autocommit = 0</code>

<code>character_set_server=utf8mb4</code>

<code>skip_name_resolve = 1</code>

<code>max_connections = 800</code>

<code>max_connect_errors = 1000</code>

<code>datadir = </code><code>/usr/local/mysql/data</code>

<code>transaction_isolation = READ-COMMITTED</code>

<code>explicit_defaults_for_timestamp = 1</code>

<code>join_buffer_size = 134217728</code>

<code>tmp_table_size = 67108864</code>

<code>tmpdir = </code><code>/tmp</code>

<code>max_allowed_packet = 16777216</code>

<code>sql_mode = </code><code>"STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"</code>

<code>interactive_timeout = 1800</code>

<code>wait_timeout = 1800</code>

<code>read_buffer_size = 16777216</code>

<code>read_rnd_buffer_size = 33554432</code>

<code>sort_buffer_size = 33554432</code>

<code>########log settings########</code>

<code>log_error = error.log</code>

<code>slow_query_log = 1</code>

<code>slow_query_log_file = slow.log</code>

<code>log_queries_not_using_indexes = 1</code>

<code>log_slow_admin_statements = 1</code>

<code>log_slow_slave_statements = 1</code>

<code>log_throttle_queries_not_using_indexes = 10</code>

<code>expire_logs_days = 90</code>

<code>long_query_time = 2</code>

<code>min_examined_row_limit = 100</code>

<code>########replication settings########</code>

<code>master_info_repository = TABLE</code>

<code>relay_log_info_repository = TABLE</code>

<code>log_bin = bin.log</code>

<code>sync_binlog = 1</code>

<code>gtid_mode = on</code>

<code>enforce_gtid_consistency = 1</code>

<code>log_slave_updates</code>

<code>binlog_format = row </code>

<code>relay_log = relay.log</code>

<code>relay_log_recovery = 1</code>

<code>binlog_gtid_simple_recovery = 1</code>

<code>slave_skip_errors = all</code>

<code># slave</code>

<code>slave-parallel-</code><code>type</code> <code>= LOGICAL_CLOCK</code>

<code>slave-parallel-workers = 0</code>

<code>#slave-parallel-workers = 16</code>

<code>########innodb settings########</code>

<code>innodb_buffer_pool_size = 750M </code><code>//</code><code>記憶體的75%比較合适</code>

<code>innodb_buffer_pool_instances = 16</code>

<code>innodb_buffer_pool_load_at_startup = 1</code>

<code>innodb_buffer_pool_dump_at_shutdown = 1</code>

<code>innodb_lru_scan_depth = 2000</code>

<code>innodb_lock_wait_timeout = 10000</code>

<code>innodb_io_capacity = 4000</code>

<code>innodb_io_capacity_max = 8000</code>

<code>innodb_flush_method = O_DIRECT</code>

<code>innodb_flush_neighbors = 1</code>

<code>innodb_log_file_size = 4G</code>

<code>innodb_log_buffer_size = 16777216</code>

<code>innodb_purge_threads = 4</code>

<code>innodb_large_prefix = 1</code>

<code>innodb_thread_concurrency = 64</code>

<code>innodb_print_all_deadlocks = 1</code>

<code>innodb_strict_mode = 1</code>

<code>innodb_sort_buffer_size = 67108864 </code>

<code>innodb_flush_log_at_trx_commit = 2</code>

<code>innodb_read_io_threads = 16</code>

<code>innodb_write_io_threads = 16 </code>

<code>########semi sync replication settings########</code>

<code>plugin_dir=</code><code>/usr/local/mysql/lib/plugin</code>

<code>plugin_load = </code><code>"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"</code>

<code>loose_rpl_semi_sync_master_enabled = 1</code>

<code>loose_rpl_semi_sync_slave_enabled = 1</code>

<code>loose_rpl_semi_sync_master_timeout = 5000</code>

<code>[mysqld-5.7]</code>

<code>innodb_buffer_pool_dump_pct = 40</code>

<code>innodb_page_cleaners = 4</code>

<code>innodb_undo_log_truncate = 1</code>

<code>innodb_max_undo_log_size = 2G</code>

<code>innodb_purge_rseg_truncate_frequency = 128</code>

<code>binlog_gtid_simple_recovery=1</code>

<code>log_timestamps=system</code>

<code>transaction_write_set_extraction=MURMUR32</code>

<code>show_compatibility_56=on</code>

2、初始化mysql資料庫

<code>/usr/local/mysql/bin/mysqld</code> <code>--user=mysql --basedir=</code><code>/usr/local/mysql</code> <code>--datadir=</code><code>/usr/local/mysql/data</code> <code>--initialize</code>

<code>/etc/init</code><code>.d</code><code>/mysqld</code> <code>start</code>

3、設定密碼

mysql5.7不允許密碼為空了，是以必須得設定密碼

<code></code>

<code>grep</code> <code>"temporary password"</code> <code>/usr/local/mysql/data/error</code><code>.log</code>

<code>2016-07-04T09:50:17.455227+08:00 1 [Note] A temporary password is generated </code><code>for</code> <code>root@localhost: wLEM;g+?&amp;1eF</code>

<code>/usr/local/mysql/bin/mysql</code> <code>-uroot -p  </code>

<code>#輸入日志裡的密碼</code>

<code>mysql&gt; alter user </code><code>'root'</code><code>@</code><code>'localhost'</code> <code>identified by </code><code>'123456'</code><code>;</code>

<code>mysql&gt; flush privileges;</code>

4、配置資料同步

#兩台Mysql同時添加同步使用者

<code>mysql&gt; create user </code><code>'repl'</code><code>@</code><code>'192.168.1.%'</code> <code>identified by </code><code>'123456'</code><code>;</code>

<code>mysql&gt; GRANT REPLICATION SLAVE ON *.* TO </code><code>'repl'</code><code>@</code><code>'192.168.1.%'</code><code>;</code>

鎖一下表，追平兩台機器資料，然後解鎖。

先看看GTID是否打開

<code>mysql&gt; show global variables like </code><code>'%gtid%'</code><code>;</code>

<code>+--------------------------+-------+</code>

<code>| Variable_name            | Value |</code>

<code>| enforce_gtid_consistency | ON    |</code>

<code>| gtid_executed            |       |</code>

<code>| gtid_mode                | ON    |</code>

<code>| gtid_owned               |       |</code>

<code>| gtid_purged              |       |</code>

<code>5 rows </code><code>in</code> <code>set</code> <code>(0.10 sec)</code>

#說明gtid功能已啟動。

GTID同步資料不用再記錄對方的log檔案和位置了，用master_auto_position=1就行，不過你用老的方法檢視master的logfile和logpos，同步也是可以的。

#在兩台Mysql上都運作，IP得互指

mysql&gt; change master to master_host='192.168.1.201', master_user='repl',master_password='123456',master_auto_position=1; 

mysql&gt; start slave;

show slave status\G ;

#表示同步的檔案和位置

     Master_Log_File:

          Read_Master_Log_Pos: 4

#顯示下面表示工作正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#表示目前同步的資料庫

Replicate_Do_DB: nzabbix,szabbix

過程中可能遇到的錯誤及解決方法：

錯誤一：

mysql&gt;  start slave;

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

解決方法：

mysql&gt; reset slave;

錯誤二：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

修改my.cnf配置檔案中，slave_skip_errors = all

本文轉自 goldfishe 51CTO部落格，原文連結:http://blog.51cto.com/jack88/1901252