標籤：linux mysql 主從 互為主從
系統內容：CentOS 6.5
master1：192.168.100.204
master2：192.168.100.205
mysql版本：mysql-5.6.19
註：mysql的5.5以下的版本和5.5以上的版本配置方法不一樣
以下兩個節點安裝
1：防火牆開啟相應連接埠
# /sbin/iptables -I INPUT -p tcp --dport 3306 -j ACCEPT
# /etc/rc.d/init.d/iptables save
2：安裝相關依賴包
# yum -y install gcc gcc-c++ ncurses ncurses-devel openssl openssl-devel cmake perl lsof bison
3：刪除內建的mysql
# rpm -qa|grep mysql
# rpm -e --allmatches --nodeps mysql-libs-5.1.71-1.el6.x86_64
4：建立相關目錄
# mkdir /doiido/soft
# mkdir -p /doiido/mysql/data/
5：建立mysql使用者
# /usr/sbin/groupadd mysql
# /usr/sbin/useradd -s /sbin/nologin -M -g mysql mysql
# chown -R mysql:mysql /doiido/mysql/
6：安裝mysql
# cd /doiido/soft
# tar -zxvf mysql-5.6.19.tar.gz
# cd mysql-5.6.19
# cmake -DCMAKE_INSTALL_PREFIX=/doiido/server/mysql -DMYSQL_DATADIR=/doiido/mysql/data -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc/ -DWITH_SSL=yes -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=on
# make && make install
7：修改相關目錄許可權並建立軟串連
# chmod +w /doiido/server/mysql
# chown -R mysql:mysql /doiido/server/mysql/
# ln -s /doiido/server/mysql/lib/lib* /usr/lib/
# ln -s /doiido/server/mysql/bin/mysql /usr/bin
8：修改設定檔
# cp /doiido/server/mysql/support-files/my-default.cnf /etc/my.cnf
# vi /etc/my.cnf
[mysqld]character-set-server = utf8default-storage-engine = MyISAMbasedir = /doiido/server/mysqldatadir = /doiido/mysql/datalog-error = /doiido/mysql/mysql_error.logpid-file = /doiido/mysql/mysql.pidmax_allowed_packet = 32Mexplicit_defaults_for_timestamp = true
9：安裝mysql資料庫
# /doiido/server/mysql/scripts/mysql_install_db --basedir=/doiido/server/mysql --datadir=/doiido/mysql/data --user=mysql
10：設定mysql開機自動啟動服務
# cp /doiido/server/mysql/support-files/mysql.server /etc/init.d/mysqld
# chkconfig --add mysqld
# chkconfig --level 345 mysqld on
11：修改mysqld檔案並啟動mysql
# vi /etc/init.d/mysqld
#修改mysqld檔案中的下面兩項basedir=/doiido/server/mysqldatadir=/doiido/mysql/data
# service mysqld start
12：建立需同步的資料庫
# mysql -uroot -p
mysql> create database doiido;
mysql> GRANT ALL PRIVILEGES ON doiido.* TO [email protected]"%" IDENTIFIED BY ‘doiido123‘;
mysql> exit
--------------------以下在伺服器master1上操作
修改設定檔
# vi /etc/my.cnf
[mysqld]#預設是1，master和slave不能相同即可server-id=1log-bin=bin.logport=3306binlog-do-db =doiido#要複製的資料庫replicate-do-db =doiido
重啟mysql
# service mysqld restart
建立用於同步的賬戶
# mysql -uroot -p
mysql> grant replication slave on *.* to ‘dodo‘@‘192.168.100.205‘ identified by ‘jjjjjj‘;
查看File對應值，並記錄下來
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| bin.000001 | 120 | doiido | | |+------------------+----------+--------------+------------------+-------------------+
mysql> exit
--------------------以下在伺服器master2上操作
2.修改slave上的設定檔my.cnf
# vi /etc/my.cnf
[mysqld]server-id=2port=3306log-bin=bin.logbinlog-do-db =doiidoreplicate-do-db =doiido
重啟mysql
# service mysqld restart
在從伺服器上建立相應的資料庫
# mysql -uroot -p
將192.168.100.204設定為自己的主伺服器
mysql> CHANGE MASTER TO
MASTER_HOST=‘192.168.100.204‘,MASTER_PORT=3306,
MASTER_USER=‘dodo‘,
MASTER_PASSWORD=‘jjjjjj‘,
MASTER_LOG_FILE=‘bin.000001‘,
MASTER_LOG_POS=120;
啟動從伺服器複製功能
mysql> start slave;
mysql> show slave status\G;
...................Slave_IO_Running: YesSlave_SQL_Running: Yes...................
註：
#Slave_IO_Running：串連到主庫，並讀取主庫的日誌到本地，產生本地記錄檔
#Slave_SQL_Running:讀取本地記錄檔，並執行日誌裡的SQL命令。
上面兩個必須都是yes，其中一個NO均屬錯誤
mysql> exit
至此，單向主從配置完畢，如需要互為主從則配置如下
--------------------以下在伺服器master2上操作
建立用於同步的賬戶
# mysql -uroot -p
mysql> grant replication slave on *.* to ‘dodo‘@‘192.168.100.204‘ identified by ‘jjjjjj‘;
查看File對應值，並記錄下來
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| bin.000001 | 649 | doiido | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
mysql> exit
--------------------以下在伺服器master1上操作
將192.168.100.205設定為自己的主伺服器
mysql> CHANGE MASTER TO
MASTER_HOST=‘192.168.100.205‘,
MASTER_PORT=3306,
MASTER_USER=‘dodo‘,
MASTER_PASSWORD=‘jjjjjj‘,
MASTER_LOG_FILE=‘bin.000001‘,
MASTER_LOG_POS=649;
啟動從伺服器複製功能
mysql> start slave;
mysql> show slave status\G;
...................Slave_IO_Running: YesSlave_SQL_Running: Yes...................
上面兩個必須都是yes，其中一個NO均屬錯誤
mysql> exit
至此互為主從配置完畢
Linux下 Mysql 互為主從
