2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

MySQL 主主同步

準(zhǔn)備工作

設(shè)備： 服務(wù)器A 10.1.1.7 服務(wù)器B 10.1.1.8

OS：CentOS 7.3

目標(biāo)：兩臺(tái)服務(wù)器主主同步，實(shí)現(xiàn)高可用，

安裝MySQL 5.1綠色版：

wget~tar~mv~useradd~mkdir~scripts/~cp support x2~vim~chkconfig~start

配置主主同步

關(guān)防火墻

systemctl stop firewalld systemctl disable firewalld

調(diào)整my.cnf配置

A:

vim /etc/my.cnf ~ [mysqld] server-id = 1 auto_increment_offset = 1 auto_increment_increment = 2log-bin=mysql-bin log-slave-updates

B:

vim /etc/my.cnf ~ [mysqld] server-id = 2 auto_increment_offset = 2 auto_increment_increment = 2log-bin=mysql-bin log-slave-updates

注： server-id 不能一樣，可以考慮改為IP末位

auto_increment_offset 為（主鍵）起始值，兩臺(tái)設(shè)備主鍵相同會(huì)導(dǎo)致主主斷裂，需要錯(cuò)開

auto_increment_increment 為（主鍵）增長(zhǎng)值，因?yàn)槠鹗贾狄呀?jīng)錯(cuò)開，增加的值為錯(cuò)開值即可保證不會(huì)沖突。

舉個(gè)例子：四臺(tái)設(shè)備a,b,c,d的主鍵起始值分別為1,2,3,4。增長(zhǎng)值為4。a主鍵則會(huì)以1,5,9,13...增長(zhǎng)，b主鍵會(huì)以2,6,10,14...增長(zhǎng)，c主鍵以3,7,11,15增長(zhǎng)，d我懶得寫了。反正結(jié)果是不會(huì)出現(xiàn)主鍵沖突。兩臺(tái)設(shè)備則呈現(xiàn)主鍵奇偶增長(zhǎng)。

調(diào)整數(shù)據(jù)庫(kù)

將mysql命令加入PATH

echo "PATH=$PATH:/usr/local/mysql/bin/" >> .bash_profile source !$

A上操作：A給B開戶，查看A主信息

mysql -uroot mysql> grant replication slave on *.* to 'repl'@'10.1.1.8' identified by 'axianglinux'; mysql> flush privileges; mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000007 | 106 | | | +------------------+----------+--------------+------------------+

B上操作：B從A

mysql> change master to master_host='10.1.1.7',master_port=3306,master_user='repl',master_password='axianglinux',master_log_file='mysql-bin.000007',master_log_pos=106; mysql> start slave; mysql> show slave status\G; *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.1.1.7Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 106Relay_Log_File: axiang-02-relay-bin.000002Relay_Log_Pos: 251Relay_Master_Log_File: mysql-bin.000007Slave_IO_Running: YesSlave_SQL_Running: Yes ~

反著再來(lái)一遍 B上操作：B給A開戶，查看B主信息

grant replication slave on *.* to 'repl'@'10.1.1.7' identified by 'axianglinux' flush privileges; show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000009 | 462 | | | +------------------+----------+--------------+------------------+

A上操作：A從B

change master to master_host='10.1.1.8',master_port=3306,master_user='repl',master_password='axianglinux',master_log_file='mysql-bin.000009',master_log_pos=462; start slave; show slave status\G *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.1.1.8Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000009Read_Master_Log_Pos: 622Relay_Log_File: axiang-01-relay-bin.000002Relay_Log_Pos: 331Relay_Master_Log_File: mysql-bin.000009Slave_IO_Running: YesSlave_SQL_Running: Yes ~

測(cè)試

A:

mysql> grant all on *.* to 'usera'@'127.0.0.1';mysql> create database dbusera;

B:

mysql> select user,host,password from mysql.user; +-------+-----------+-------------------------------------------+ | user | host | password | +-------+-----------+-------------------------------------------+ | root | localhost | | | root | axiang-02 | | | root | 127.0.0.1 | | | | localhost | | | | axiang-02 | | | repl | 10.1.1.7 | *8E1A3402D66F8DDD8D9D19596B706C6D238C0F34 | | usera | 127.0.0.1 | | +-------+-----------+-------------------------------------------+ 7 rows in set (0.01 sec)mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dbusera | | mysql | | test | +--------------------+ 4 rows in set (0.01 sec)mysql> create database dbuserb-> ; Query OK, 1 row affected (0.00 sec)

A:

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | dbusera | | dbuserb | | mysql | | test | | wordpress | +--------------------+

• 因?yàn)锳上有wordpress庫(kù)，B沒(méi)有，對(duì)wordPress的操作會(huì)導(dǎo)致B從A斷裂

恢復(fù)

A：

mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000008 | 945 | | | +------------------+----------+--------------+------------------+

B:

mysql> stop slave-> ; Query OK, 0 rows affected (0.02 sec)mysql> change master to master_host='10.1.1.7',master_port=3306,master_user='repl',master_password='axianglinux',master_log_file='mysql-bin.000008',master_log_pos=945; Query OK, 0 rows affected (0.01 sec)mysql> start slave; Query OK, 0 rows affected (0.02 sec)mysql> show slave status\G *************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.1.1.7Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 945Relay_Log_File: axiang-02-relay-bin.000002Relay_Log_Pos: 251Relay_Master_Log_File: mysql-bin.000008Slave_IO_Running: YesSlave_SQL_Running: Yes ~

• 以上實(shí)驗(yàn)追求簡(jiǎn)單，實(shí)際生產(chǎn)環(huán)境有寫入的情況需先備份數(shù)據(jù)
• mysqldump備份需要鎖表，innobackup貌似不用
• 從的logbin內(nèi)的Pos點(diǎn)可以斷點(diǎn)續(xù)傳。后面主從邏輯不出錯(cuò)則可以從舊Pos開啟

轉(zhuǎn)載于:https://my.oschina.net/u/3579690/blog/1572420

總結(jié)

以上是生活随笔為你收集整理的14-4-5 17 MySQL 主主同步的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。