普通雙主復(fù)制:非GTID類型的雙主復(fù)制
一、實(shí)驗(yàn)環(huán)境
兩臺CentOS-7.5虛擬機(jī)
虛機(jī)配置:1G內(nèi)存啃洋、1核CPU怖侦、40G磁盤
MySQL版本:社區(qū)版MySQL-5.7
master_a:192.168.218.128
master_b:192.168.218.136
防火墻+selinux:均已關(guān)閉
雙主:兩臺服務(wù)器互為主從
雙主分兩步實(shí)現(xiàn):
1.配置以master_a為主,master_b為從的主從環(huán)境虫给;
2.配置以master_b為主躯喇,master_a為從的主從環(huán)境
配置思路:
先實(shí)施Master->Slave的主主同步辫封。主主是數(shù)據(jù)雙向同步硝枉,主從是數(shù)據(jù)單向同步。
二倦微、安裝MySQL服務(wù)
說明:兩臺服務(wù)器同時(shí)執(zhí)行以下命令妻味,因?yàn)閮膳_MySQL服務(wù)器要安裝一模一樣的環(huán)境
1)卸載系統(tǒng)自帶的mariadb服務(wù)
[root@CentOS ~]#
[root@CentOS ~]# rpm -qa mariadb?#先查看是否已安裝mariadb服務(wù)
[root@CentOS ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64 #卸載該程序
[root@CentOS ~]# rpm -qa mariadb
?#查看卸載mariadb是否成功
[root@CentOS ~]#
2)下載并安裝mysql-5.7的安裝源
yum localinstall?https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm?#安裝mysql-5.7的安裝源
[root@CentOS ~]#
[root@CentOS ~]# ll /etc/yum.repos.d/ | grep 'mysql-community'
[root@CentOS ~]#
[root@CentOS ~]# yum list mysql-community-server #查看該MySQL源中可以安裝的MySQL版本
3)刪除Linux系統(tǒng)原來自帶的 my.cnf 文件
刪除Linux系統(tǒng)原來自帶的 my.cnf 文件,如果my.cnf 文件存在的話
[root@CentOS ~]# rm -rf /etc/my.cnf #刪除該文件
[root@CentOS ~]#
4)正式安裝MySQL-5.7
[root@CentOS ~]#
[root@CentOS ~]# yum -y install mysql-community-server #安裝MySQL服務(wù)
[root@CentOS ~]# rpm -qa mysql-community-server
5)啟動MySQL-5.7
[root@CentOS ~]#
[root@CentOS ~]# systemctl start mysqld.service
[root@CentOS ~]# systemctl enable mysqld.service
[root@CentOS ~]#
三欣福、修改兩臺MySQL服務(wù)器的配置文件
說明:
因?yàn)閮蓚€(gè)服務(wù)器都互為對方的主從责球,所以配置差不多,但是server-id是不一樣的
下面的 /etc/my.cnf 配置文件截圖是我后期重新調(diào)整過的拓劝,第一次做實(shí)驗(yàn)的時(shí)候雏逾,其實(shí)兩臺master分別是master_a和master_b ;但是下邊截圖的這個(gè)實(shí)驗(yàn)我將兩臺master命名為M1和M2,所以兩次實(shí)驗(yàn)binlog日志前綴不一樣郑临,而且binlog日志存放路徑也不一樣
1)master_a 端配置
[root@CentOS ~]#
[root@CentOS ~]# vim /etc/my.cnf #編輯修改master_a 的配置文件
2)master_b 端配置
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# vim /etc/my.cnf #編輯修改master_b 的配置文件
3)創(chuàng)建用于存放binlog文件的目錄
master_a 端創(chuàng)建:
[root@CentOS ~]#
[root@CentOS ~]# mkdir /var/lib/mysql/binary_log
[root@CentOS ~]#
[root@CentOS ~]# chown mysql:mysql /var/lib/mysql/binary_log #授權(quán)
[root@CentOS ~]#
[root@CentOS ~]# ll /var/lib/mysql | grep 'binary_log'
master_b 端創(chuàng)建:
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# mkdir /var/lib/mysql/binary_log
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# chown mysql:mysql /var/lib/mysql/binary_log #授權(quán)
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# ll /var/lib/mysql | grep 'binary_log'
四栖博、重啟MySQL服務(wù)
說明:修改完配置文件后,重啟MySQL服務(wù)使配置生效
1)master_a 端執(zhí)行
[root@CentOS ~]#
[root@CentOS ~]# systemctl restart mysqld
[root@CentOS ~]#
2)master_b 端執(zhí)行
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# systemctl restart mysqld
[root@CentOS-2 ~]#
五厢洞、查看二進(jìn)制文件是否生成
1)master_a 端執(zhí)行
[root@CentOS ~]#
[root@CentOS ~]# ls /var/lib/mysql/binary_log/
2)master_b 端執(zhí)行
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# ls /var/lib/mysql/binary_log/
六仇让、獲取MySQL臨時(shí)登錄密碼
1)master_a 端獲取:
[root@CentOS ~]#
[root@CentOS ~]# grep 'temporary' /var/log/mysqld.log
2)master_b 端獲取
七躺翻、使用安全配置向?qū)гO(shè)置MySQL
說明:MySQL使用前的基本設(shè)置
注:兩臺MySQL服務(wù)器操作幾乎一模一樣妹孙,但是root密碼可以設(shè)置的不一樣
1)master_a 端設(shè)置
[root@CentOS ~]#
[root@CentOS ~]# mysql_secure_installation #安全配置向?qū)?/p>
Securing the MySQL server deployment.
Enter password for user root: #輸入上面獲取的臨時(shí)密碼 ;76/.favFurg
The existing password for the user account root has expired. Please set a new password.
New password: #設(shè)置新root密碼 Ctsi.2020
Re-enter new password: #再次輸入新root密碼 Ctsi.2020
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.
Estimated strength of the password: 100 #密碼強(qiáng)度為100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no #不再修改root密碼了,上面已經(jīng)改過了
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success. #移除匿名用戶
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success. #禁止root用戶遠(yuǎn)程登錄mysql服務(wù)器
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #刪除測試數(shù)據(jù)庫
Dropping test database...
Success.
Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success. #刷新授權(quán)表
All done!
[root@CentOS ~]#
2)master_b 端設(shè)置
master_b 端安全配置向?qū)гO(shè)置和master_a 端設(shè)置一模一樣获枝,連root用戶的密碼都一樣,都是 Ctsi.2020
具體設(shè)置過程省略……
八骇笔、先配置以master_a 為主省店,master_b 為從的主從復(fù)制模式
1)設(shè)置MySQL復(fù)制賬號并做授權(quán)
master_a 端操作:
[root@CentOS ~]#
[root@CentOS ~]# mysql -uroot -p'Ctsi.2020'
mysql>
mysql> show databases;
mysql>
mysql> grant replication slave on?.?to repluser@192.168.218.136 identified by 'Mysql.2020'; #授權(quán)repluser賬戶可以通過從服務(wù)器192.168.218.136來登錄主服務(wù)器并復(fù)制主服務(wù)器的所有庫和所有表
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql> flush privileges; #刷新授權(quán)表
Query OK, 0 rows affected (0.00 sec)
mysql>
2)master_a 主服務(wù)器端導(dǎo)出一個(gè)全備
[root@CentOS ~]#
[root@CentOS ~]# mysqldump -uroot -p'Ctsi.2020' --all-databases --single-transaction > /tmp/mysql_full-1.sql #導(dǎo)出一個(gè)全備
[root@CentOS ~]#
[root@CentOS ~]# ll /tmp/mysql_full-1.sql
3)將mysql主服務(wù)器的第一次全備拷貝到從服務(wù)器
[root@CentOS ~]#
[root@CentOS ~]# scp /tmp/mysql_full-1.sql root@192.168.218.136:/tmp/
[root@CentOS ~]#
[root@CentOS ~]# ssh root@192.168.218.136 #遠(yuǎn)程登錄到從服務(wù)器端查看全備是否復(fù)制成功
[root@CentOS-2 ~]# ll /tmp/mysql_full-1.sql
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# logout #退出從服務(wù)器遠(yuǎn)程登錄
4)從服務(wù)器端導(dǎo)入數(shù)據(jù)庫全備
master_b 端操作:
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# mysql -uroot -p'Ctsi.2020' < /tmp/mysql_full-1.sql #導(dǎo)入全備,使得主從兩端狀態(tài)處于一致
此時(shí)笨触,master端和slave端數(shù)據(jù)處于一致狀態(tài)
5)查看主服務(wù)器端數(shù)據(jù)庫的狀態(tài)并記住
說明:這個(gè)狀態(tài)非常重要懦傍,后面要用到,所以要記住
master_a 端執(zhí)行:
[root@CentOS ~]#
[root@CentOS ~]# mysql -uroot -p'Ctsi.2020' #登錄主服務(wù)器端數(shù)據(jù)庫
mysql>
mysql> show master status; #查看主庫狀態(tài)
6)設(shè)置主從復(fù)制
master_b 端執(zhí)行:
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# mysql -uroot -p'Ctsi.2020' #登錄從服務(wù)器端數(shù)據(jù)庫
mysql>
mysql> change master to
-> master_host='192.168.218.128',
-> master_user='repluser',
-> master_password='Mysql.2020',
-> master_log_file='master_a-binlog.000001',
-> master_log_pos=1163;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql>
mysql>
mysql>
mysql> start slave; #啟動slave同步進(jìn)程
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> show slave status\G #查看slave狀態(tài)
九芦劣、驗(yàn)證以master_a 為主粗俱,master_b為從的主從復(fù)制
方法:在master_a 端插入數(shù)據(jù),在master_b 端驗(yàn)證是否同步過去
1)在master端創(chuàng)建數(shù)據(jù)庫并插入數(shù)據(jù)
[root@CentOS ~]#
[root@CentOS ~]# mysql -uroot -p'Ctsi.2020'
mysql>
mysql> create database t1;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use t1;
Database changed
mysql>
mysql> create table Student(
-> Sid varchar(10),
-> Sname nvarchar(10),
-> Sage datetime,
-> Ssex nvarchar(10)
-> );
Query OK, 0 rows affected (0.15 sec)
mysql>
mysql>
mysql> insert into Student values('01','張磊','1995-02-08','男');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Student values('02','李華','1995-06-11','男');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from Student;
2)去slave端查看是否存在master中插入的數(shù)據(jù)
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# mysql -uroot -p'Ctsi.2020' #登錄master_b 端數(shù)據(jù)庫
mysql>
mysql> show databases;
mysql>
mysql> select * from t1.Student;
結(jié)論:對比 master_a 和 master_b 發(fā)現(xiàn)虚吟,兩端數(shù)據(jù)一致寸认,主從同步成功
十、再配置以master_b 為主串慰,master_a 為從的主從復(fù)制模式
理解:在原來的CentOS主節(jié)點(diǎn)上創(chuàng)建新的用戶(※這點(diǎn)很重要偏塞,因?yàn)橹耙呀?jīng)是主從配置,所以在master_a節(jié)點(diǎn)上創(chuàng)建會自動同步至master_b上邦鲫,如果在master_b上創(chuàng)建用戶灸叼,則可能會導(dǎo)致兩節(jié)點(diǎn)數(shù)據(jù)不一致情況發(fā)生)
在master_a端進(jìn)行操作:
1)設(shè)置MySQL復(fù)制賬號并做授權(quán)
在master_a端執(zhí)行:
[root@CentOS ~]#
[root@CentOS ~]# mysql -uroot -p'Ctsi.2020'
mysql>
mysql> grant replication slave on?.?to repluser@192.168.218.128 identified by 'Mysql.2020';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
2)查看主服務(wù)器端數(shù)據(jù)庫的狀態(tài)并記住
說明:這個(gè)狀態(tài)非常重要神汹,后面要用到,所以要記住
在master_b 端執(zhí)行:
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# mysql -uroot -p'Ctsi.2020'
3)設(shè)置主從復(fù)制
在master_a端執(zhí)行:
[root@CentOS ~]#
[root@CentOS ~]# mysql -uroot -p'Ctsi.2020'
mysql>
mysql> change master to
-> master_host='192.168.218.136',
-> master_user='repluser',
-> master_password='Mysql.2020',
-> master_log_file='master_b-binlog.000001',
-> master_log_pos=831197;
Query OK, 0 rows affected, 2 warnings (0.13 sec)
mysql>
mysql>
mysql> start slave; #啟動slave同步進(jìn)程
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> show slave status\G #查看slave狀態(tài)
十一古今、驗(yàn)證以master_b 為主屁魏,master_a 為從的主從復(fù)制
方法:在master_b 端插入數(shù)據(jù),在master_a 端驗(yàn)證是否同步過去
1)在master端創(chuàng)建數(shù)據(jù)庫并插入數(shù)據(jù)
[root@CentOS-2 ~]#
[root@CentOS-2 ~]# mysql -uroot -p'Ctsi.2020'
mysql>
mysql> create database t2; #創(chuàng)建數(shù)據(jù)庫t2
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use t2;
Database changed
mysql>
mysql> create table Student(
-> Sid varchar(10),
-> Sname nvarchar(10),
-> Sage datetime,
-> Ssex nvarchar(10)
-> );
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql>
mysql> insert into Student values('03','王青','1996-01-06','女'); #插入數(shù)據(jù)
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> insert into Student values('04','三毛','1980-09-25','女');
Query OK, 1 row affected (0.10 sec)
mysql>
mysql> select * from Student;
2)去slave端查看是否存在master中插入的數(shù)據(jù)
[root@CentOS ~]#
[root@CentOS ~]# mysql -uroot -p'Ctsi.2020'
mysql>
mysql> show databases;
mysql>
mysql> select * from t2.Student;
結(jié)論:對比 master_a 和 master_b 發(fā)現(xiàn)捉腥,兩端數(shù)據(jù)一致氓拼,主從同步成功
十二、本實(shí)驗(yàn)結(jié)論
經(jīng)過兩輪驗(yàn)證得出:MySQL雙主復(fù)制配置成功