這篇文章主要講解了“Linux下的MySQL主主復(fù)制的用法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Linux下的MYSQL主主復(fù)制的用法”吧！
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計、成都做網(wǎng)站、湖口網(wǎng)絡(luò)推廣、小程序制作、湖口網(wǎng)絡(luò)營銷、湖口企業(yè)策劃、湖口品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供湖口建站搭建服務(wù)，24小時服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com
為什么，會有mysql的主主復(fù)制。因為在一些高可用的環(huán)境中，mysql的主從不能滿足現(xiàn)實中的一些實際需求。比如，一些流量大的網(wǎng)站數(shù)據(jù)庫訪問有了瓶頸，需要負(fù)載均衡的時候就用兩個或者多個的mysql服務(wù)器，而這些mysql服務(wù)器的數(shù)據(jù)庫數(shù)據(jù)必須要保持一致，那么就會用到主主復(fù)制。
mysql主從架構(gòu)中其實就一個主在工作，而從就相當(dāng)于一個備份機器，從通過日志監(jiān)測的方式來備份主庫上的數(shù)據(jù)而保證主庫的數(shù)據(jù)安全。在這種架構(gòu)中如果從上的數(shù)據(jù)做了改變，主數(shù)據(jù)是不會用任何變化的。因為mysql主從架構(gòu)主要是mysql從監(jiān)控mysql主的日志變化來實現(xiàn)同步，相反的在這個架構(gòu)中主并沒有監(jiān)控從的日志變化。所以，mysql從數(shù)據(jù)反生變化，主也就沒有什么變化了。
通過上述描述，可以看到如果想實現(xiàn)主主復(fù)制，無非就是在mysql主從架構(gòu)上讓mysql主實現(xiàn)監(jiān)測從的日志變化，從而實現(xiàn)兩臺機器相互同步。(主從的架構(gòu)前面有博文http://duyunlong.blog.51cto.com/1054716/1102237)
實驗環(huán)境：兩臺服務(wù)器：
主機名：HA1,HA2(呵呵,這個主機名是英文縮寫High availability，高可用的意思)
ip:192.168.1.231
192.168.1.232
主機系統(tǒng)：centos6.4
mysql版本5.5.22
首先，看下HA1（192.168.1.231）的mysql配置文件
vim /etc/my.cnf# Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the "--help" option.# The following options will be passed to all MySQL clients[client]#password = your_passwordport = 3306socket = /usr/local/mysql/tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port = 3306socket = /usr/local/mysql/tmp/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8# Don't listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld useless!##skip-networking# Replication Master Server (default)# binary logging is required for replicationlog-bin=mysql-binlog-slave-updates# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1
在這個配置文件中，需要特別注意的三處地方：
log-bin=mysql-bin：這個選項基本默認(rèn)都是開著的，如果沒有打開，可以手動打開。
log-slave-updates：這個選項特別的重要它是為了讓slave也能充當(dāng)master，同時也為了更好的服務(wù)于 m-m + s 的環(huán)境，保證slave掛在任何一臺master上都會接收到另一個master的寫入信息。當(dāng)然不局限于這個架構(gòu)，級聯(lián)復(fù)制的架構(gòu)同樣也需要log-slave-updates的支持。
server-id = 1：這個ID為服務(wù)器ID如果配置一樣會出現(xiàn)沖突，而不能復(fù)制
接著再看下HA2（192.168.1.232）的mysql配置文件
vim /etc/my.cnf# Example MySQL config file for very large systems.## This is for a large system with memory of 1G-2G where the system runs mainly# MySQL.## MySQL programs look for option files in a set of# locations which depend on the deployment platform.# You can copy this option file to one of those# locations. For information about these locations, see:# http://dev.mysql.com/doc/mysql/en/option-files.html## In this file, you can use all long options that a program supports.# If you want to know which options a program supports, run the program# with the "--help" option.# The following options will be passed to all MySQL clients[client]#password = your_passwordport = 3306socket = /usr/local/mysql/tmp/mysql.sock# Here follows entries for some specific programs# The MySQL server[mysqld]port = 3306socket = /usr/local/mysql/tmp/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8query_cache_size = 32M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8# Don't listen on a TCP/IP port at all. This can be a security enhancement,# if all processes that need to connect to mysqld run on the same host.# All interaction with mysqld must be made via Unix sockets or named pipes.# Note that using this option without enabling named pipes on Windows# (via the "enable-named-pipe" option) will render mysqld useless!##skip-networking# Replication Master Server (default)# binary logging is required for replicationlog-bin=mysql-binlog-slave-updates# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 10# Replication Slave (comment out master section to use this)
在HA2的mysql配置文件中，除了server-id不一樣，其他幾乎一模一樣。配置文件寫好后，我們把兩臺服務(wù)器上的mysql服務(wù)器啟動起來。
首先，登錄HA2（192.168.1.232）的mysql中，查看master狀態(tài)
mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000016 | 615 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)mysql>
然后，登錄HA1（192.168.1.231）的msyql中，把HA2配置成自己的主，在做這個之前先在兩臺機器的mysql中建立一個可以復(fù)制用的帳號：
mysql>grant all on *.* to duyunlong@'192.168.1.%' identified by '123456'; Query OK, 0 rows affected (0.01 sec) mysql>change master to master_host='192.168.1.232',master_user='duyunlong',master_password='123456',master_log_file='mysql-bin.000016',master_log_pos=615;
同上，查看HA1(192.168.1.231)master，然后登錄HA2(192.168.1.232),把HA1(192.168.1.231)，配置成自己的主，然后分別在兩臺機器的mysql中，啟動slave
啟動后HA1狀態(tài)
mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.232 Master_User: duyunlong Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000016 Read_Master_Log_Pos: 615 Relay_Log_File: HA1-relay-bin.000002 Relay_Log_Pos: 346 Relay_Master_Log_File: mysql-bin.000016 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 615 Relay_Log_Space: 500 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 row in set (0.00 sec)ERROR:No query specified
可以看到 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
然后在看HA2的狀態(tài)：
mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.231 Master_User: duyunlong Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000018 Read_Master_Log_Pos: 552 Relay_Log_File: HA2-relay-bin.000002 Relay_Log_Pos: 441 Relay_Master_Log_File: mysql-bin.000018 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 552 Relay_Log_Space: 595 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec)ERROR:No query specified
可以看到Slave_IO_Running: Yes
Slave_SQL_Running: Yes
接下來，我們要測試，是不是已經(jīng)可以主主復(fù)制了呢，首先登錄HA1(192.168.1.231)的mysql中，建立一數(shù)據(jù)庫，當(dāng)然在測試前我們先看下，兩臺服務(wù)器中的mysql中有哪些數(shù)據(jù)
首先看下HA1(192.168.1.231)
[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA1 ~]#
再看下HA2（192.168.1.232）
[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA2 ~]#
可以看到，現(xiàn)在兩臺服務(wù)器上的mysql中數(shù)據(jù)是一樣的，接下來在HA中建立一數(shù)據(jù)庫“a”,再看結(jié)果
[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e 'create database a;'[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || a || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA1 ~]#
然后看下HA2（192.168.1.232）是不是會把剛建立的數(shù)據(jù)庫“a”復(fù)制過來
[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || a || mysql || performance_schema || test || wanghaipeng |+--------------------+
可以看到，數(shù)據(jù)庫“a”已經(jīng)成功復(fù)制過來了，反過來我們在HA2（192.168.1.232）上建立一數(shù)據(jù)庫“b”看是否HA1也可以復(fù)制過去
[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e 'create database b;'[root@HA2 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.232 -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || a || b || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA2 ~]#
然后登錄HA1（192.168.1.231），查看是否復(fù)制成功
[root@HA1 ~]# mysql mysql -uduyunlong -p123456 -h292.168.1.231 -e 'show databases;'+--------------------+| Database |+--------------------+| information_schema || a || b || mysql || performance_schema || test || wanghaipeng |+--------------------+[root@HA1 ~]#
在HA1（192.168.1.231）可以看到數(shù)據(jù)庫“b”已經(jīng)復(fù)制過來了。
那么到此，主主復(fù)制架構(gòu)已經(jīng)成功！
感謝各位的閱讀，以上就是“Linux下的MYSQL主主復(fù)制的用法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對Linux下的MYSQL主主復(fù)制的用法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！
重慶分公司
重慶分公司
