MySQL AB復(fù)制
欒川ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景,ssl證書未來市場(chǎng)廣闊!成為創(chuàng)新互聯(lián)的ssl證書銷售渠道,可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠!如果有意向歡迎電話聯(lián)系或者加微信:18980820575(備注:SSL證書合作)期待與您的合作!
Mysql AB 復(fù)制又稱主從復(fù)制,實(shí)現(xiàn)的是數(shù)據(jù)同步,要求最好所有的mysql版本相同,如果版本不一致,從服務(wù)器版本要高于主服務(wù)器,而且版本不一致不能做雙向復(fù)制。
AB復(fù)制主要的有點(diǎn)有兩點(diǎn):
1、解決宕機(jī)帶來的數(shù)據(jù)不一致,因?yàn)閙ysql AB 復(fù)制可以實(shí)時(shí)備份數(shù)據(jù),
2、減輕數(shù)據(jù)庫(kù)服務(wù)壓力
但是mysql AB復(fù)制不適用于大數(shù)據(jù)環(huán)境,如果是大數(shù)據(jù)環(huán)境推薦使用集群
Mysql復(fù)制的三個(gè)主要步驟:
①主服務(wù)器更改記錄到二進(jìn)制文件中(二進(jìn)制日志事件)
②從服務(wù)器吧主服務(wù)器的二進(jìn)制文件拷貝到自己的中繼日志中
③從服務(wù)器執(zhí)行中繼日志中的事件,達(dá)到自己和主服務(wù)器的環(huán)境一致
因?yàn)镸ysql5.1和5.7版本有點(diǎn)差距
所以此篇博文以5.1和5.7實(shí)現(xiàn)各自版本的主從復(fù)制
開始搭建:
環(huán)境:redhat6.5
三臺(tái)裝有相同版本MySQL 5.1的虛擬機(jī):
Server1(master):172.25.141.4
Server2(master&slave):172.25.141.5
Server3(slave):172.25.141.6
Server1(172.25.141.4):
vim /etc/my.cnf (添加以下)
server-id=1
log-bin=mysql-bin
binlog-do-db=testdb
binlog-ignore-db=mysql
/etc/init.d/mysqld start
mysql ##登陸
mysql> create databasetestdb;
mysql> grant replication slave on *.* to redhat@'172.25.141.5' identified by 'test123';
mysql> show master status; ##查看master狀態(tài)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|mysql-bin.000003 | 1019 | testdb | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql>quit
mysqlbinlog mysql-bin.000003 ##查看MySQL日志
Server2(172.25.141.5):
mysql -h 172.25.141.4 -u redhat -ptest123 ##測(cè)試能否登上
vim /etc/my.cnf(添加以下)
server-id=2
mysql
mysql> create databasetestdb;
mysql> change master to master_host='172.25.141.4',master_user='redhat',master_password='test123',master_log_file='mysql-bin.000003',master_log_pos=1019;
###master_log_file與master status里面的File名一樣
###master_log_pos=1019數(shù)字一定與master的position一樣
mysql> slave start;
mysql> show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
#######################################################
測(cè)試:
Server1:
mysql> use testdb;
mysql> create table users ( username varchar(25) not null, password varchar(25) not null );
mysql> insert into users values ('user1','123');
mysql> insert into users values ('user2','456');
Server2端database testdb也會(huì)出現(xiàn)以上所添加項(xiàng)目:
mysql> select * from users;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
| user2 | 456 |
+----------+----------+
2 rows in set (0.00 sec)
####delete from users where username='user1';
####Server1端刪除某項(xiàng)Server2端也會(huì)刪除
#######################################################
server1(master)------>server2(master&slave)------>server3(slave)
Server2(172.25.141.5):
vim /etc/my.cnf
server-id=2
log-bin=mysql-bin
binlog-do-db=testdb
binlog-ignore-db=mysql
log-slave-updates
/etc/init.d/mysqld start
mysql
mysql> grant replication slave on *.* to redhat@'172.25.141.6' identified by 'test123';
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|mysql-bin.000001 | 106 | testdb | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Server3(172.25.141.6):
mysqlbinlog --start-position=193 mysql-bin.000003 | mysql -uroot -p***
###導(dǎo)入serverA之前的MySQL操作日志,因?yàn)閟erver3之前并沒有那些操作,要同步數(shù)據(jù)那此時(shí)server3的環(huán)境要與server1一致
vim /etc/my.cnf
Server-id=3
/etc/init.d/mysqld start
mysql
mysql> change master to master_host='172.25.141.5',master_user='redhat',master_password='test123',master_log_file='mysql-bin.000001',master_log_pos=106 ;
mysql> slave start;
mysql> show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
出現(xiàn)這個(gè)則證明server3的slave開啟成功,可以同步數(shù)據(jù)
MySQL 5.7
兩臺(tái)裝有相同版本MySQL的虛擬機(jī):
Server1(master)
Server2(slave)
安裝包:mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
Server1:
tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
yum install * -y
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
binlog-do-db=testdb
gtid-mode=on
enforce-gtid-consistency=on
/etc/init.d/mysqld start
cat /var/log/mysqld.log | grep temporary ##查看root密碼
mysql_secure_installation
##更改密碼,必須含有大小寫字母、數(shù)字和特殊字符并不少于8個(gè)字符
mysql -p**** ##登陸
mysql> create database testdb;
mysql> grant replication slave on *.* to redhat@172.25.141.5 identified by '@Ling110';
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 319 | testdb | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Server2:
tar xf mysql-5.7.11-1.el6.x86_64.rpm-bundle.tar
yum install * -y
mysql -h 172.25.141.4 -uredhat -p@Ling110 ##測(cè)試能否登上
vim /etc/my.cnf
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
/etc/init.d/mysqld start
mysql_secure_installation
mysql -p****
mysql> create database testdb;
mysql> change master to master_host='172.25.141.4',master_user='redhat',master_password='@Ling110',master_auto_position=1;
mysql> start slave;
mysql> show slave status\G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
測(cè)試同MySQL5.1
MySQL5.7和MySQL5.1不同的一點(diǎn)在于MySQL5.7可以開啟多線程模式,有效解決數(shù)據(jù)同步的延遲問題
開啟多線程模式:
Server2:
vim /etc/my.cnf
slave-parallel-type=LOGICAL_CLOCK ##開啟多線程模式
slave-parallel-workers=16
##16為官方推薦數(shù)目,0為原始單線程模式,切記不可設(shè)為1,性能會(huì)比0還差,因?yàn)檫€是單線程但多了一層轉(zhuǎn)發(fā)降低效率
master-info-repository=TABLE ##優(yōu)化
relay_log_info_repository=TABLE ##優(yōu)化
/etc/init.d/mysqld restart
mysql -p***
mysql> show processlist; ##可以看到開啟的多個(gè)線程
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 12 | Slave has read all relay log; waiting for more updates | NULL |
| 2 | system user | | NULL | Connect | 13 | Waiting for master to send event | NULL |
| 3 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 4 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 5 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 7 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 8 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 9 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 10 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 11 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 12 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 13 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 14 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 15 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 16 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 17 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 18 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 19 | system user | | NULL | Connect | 13 | Waiting for an event from Coordinator | NULL |
| 21 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
19 rows in set (0.00 sec)
本文名稱:兩個(gè)版本的Mysql主從復(fù)制
文章位置:http://muchs.cn/article44/gdchee.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供品牌網(wǎng)站設(shè)計(jì)、網(wǎng)站內(nèi)鏈、面包屑導(dǎo)航、App開發(fā)、網(wǎng)站導(dǎo)航、品牌網(wǎng)站制作
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來源: 創(chuàng)新互聯(lián)