您好,登錄后才能下訂單哦!
本文主要給大家簡單講講實現(xiàn)互為雙主搭建MySQL數(shù)據(jù)庫方法,相關(guān)專業(yè)術(shù)語大家可以上網(wǎng)查查或者找一些相關(guān)書籍補充一下,這里就不涉獵了,我們就直奔主題吧,希望實現(xiàn)互為雙主搭建MySQL數(shù)據(jù)庫方法這篇文章可以給大家?guī)硪恍嶋H幫助。
1、讓表的id 自增,然后主一寫1,3,5,,,,,主2寫2,,4,6,,,,
2、不讓表的id 自增,然后通過web程序去seq云服務(wù)器讀取id ,寫入雙主。
雙主工作場景:高鬢發(fā)寫的場景,慎用。
和多實例及主從同步對比:主主數(shù)據(jù)庫多增加的參數(shù):
2.第一個方法實現(xiàn)主主復(fù)制:
auto_increment_increment = 2
auto_increment_offset = 1
log-slave-updates
log-bin = /data/3306/mysql-bin
expire_logs_days = 7
/etc/init.d/mysqldrestart
auto_increment_increment = 2
auto_increment_offset = 2
log-bin = /data/3307/mysql-bin
log-slave-updates
expire_logs_days = 7
/etc/init.d/mysqld restart
mysql -uroot -p123456 -e "showvariables like 'log_%';"|grep -i "on"
mysql -uroot -p123456 -e "showvariables like 'log_%';"|grep -i "on"
mysql -uroot -p123456 -e "showvariables like 'auto%';"|egrep "2|1|2"
mysql -uroot -p123456 -e "showvariables like 'auto%';"|egrep "2|1|2"
mysqldump -uroot -p123456 -B -A -F -R -x--master-data=1 --events|gzip>/backup/slave_$(date +%F).sql.gz
scp -rp -P22/backup/slave_2016-08-19.sql.gz root@172.16.1.41:/backup
gzip -d /backup/slave_2016-08-19.sql.gz
mysql -uroot -p123456</backup/slave_2016-08-19.sql
mysql -uroot -p123456 -e "CHANGEMASTER TO MASTER_HOST='172.16.1.42',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='111111';startslave;”
[root@mysql-5 data]# mysql -uroot-p123456 -e "show slave status\G"|grep -i yes
Warning: Using a password on the commandline interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
172.16.1.42這個ip地址為從庫MySQL-5.6.16-slave1的ip地址
到此處實現(xiàn)雙主
Master1端插入數(shù)據(jù)演示:
Master1:172.16.1.41
mysql -uroot -p123456 -e "create databasewjw05"
mysql> use wjw01;
mysql> CREATE table `t1` (`id` bigint(12) NOTNULL auto_increment,`name` varchar(12) NOT NULL,PRIMARY KEY (`id`) );
mysql> INSERT INTO t1(name) values('wangwu');
slave2:172.16.1.43
[root@mysql-5 backup]# mysql -uroot-p123456 -e "select * from wjw01.t1"
Warning: Using a password on the commandline interface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | wangwu |
在從庫slave2查看 ,數(shù)據(jù)已經(jīng)同步過來了
Master2___slave1:172.16.1.42
mysql -uroot -p123456 -e "select *from wjw01.t1"
主庫master1 的數(shù)據(jù)沒有同步到主庫master2__slave1
Master2__slave1端插入數(shù)據(jù)演示:
mysql> use wjw01;
mysql> INSERT INTO t1(name) values('oldgirl');
slave2:172.16.1.43查看:
[root@mysql-5 backup]# mysql -uroot -p123456 -e"select * from wjw01.t1"
Warning: Using a password on the command lineinterface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | wangwu |
| 2 |oldgirl |
數(shù)據(jù)同步過去了
Master1:172.16.1.41查看:
[root@mysql-5 data]# mysql -uroot -p123456-e "select * from wjw01.t1"
Warning: Using a password on the commandline interface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1| wangwu |
Master2_slave1端的數(shù)據(jù)庫沒有同步到master1端
在master2__slave1端和master1端查看:同步過程中并沒有報錯,太奇怪了
[root@mysql-5 binlog]# mysql -uroot -p123456 -e "show slavestatus\G"|grep -i "yes"
Warning: Using a password on the commandline interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@mysql-5 data]# mysql -uroot -p123456 -e "show slavestatus\G"|grep -i "yes"
Warning: Using a password on the commandline interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
在網(wǎng)上查找找到問題的原因了:
解決辦法:(網(wǎng)友提供)
statement模式不足以應(yīng)付應(yīng)用,換成mixed試試看吧:
在master2__slave1端操作:
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> SET GLOBAL binlog_format=MIXED;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
問題解決。提示:此處修改只是臨時生效,一旦重啟MySQL服務(wù),就會失效,把參數(shù)加在my.cnf配置文件從啟MySQL服務(wù),永久生效,
Master2__slave1端的參數(shù)正確的設(shè)置方法:
[root@mysql-5 binlog]# cat -n /etc/my.cnf |sed -n'42,49p'
42 ###雙主實現(xiàn)方法###
43 log_bin = /home/mysql/data/binlog/mysql-bin
44 auto_increment_increment = 2
45 auto_increment_offset = 2
46 log-slave-updates
47 expire_logs_days = 7
48 binlog_format = mixed
49 ###雙主實現(xiàn)方法###
在master1端繼續(xù)插入數(shù)據(jù)
mysql> INSERT INTO t1(name) values('pig');
slave2段查看:
[root@mysql-5 binlog]# mysql -uroot -p123456 -e "select * fromwjw01.t1"
Warning: Using a password on the command lineinterface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | wangwu |
| 2 |oldgirl |
| 3 |pig |
Master2__slave1端查看:
[root@mysql-5 backup]# mysql -uroot -p123456 -e"select * from wjw01.t1"
Warning: Using a password on the command lineinterface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | wangwu |
| 2 |oldgirl |
| 3 |pig |
Master2__slave1端插入數(shù)據(jù):
mysql> INSERT INTO t1(name) values('apple');
在master1段查看:數(shù)據(jù)同步過去了
[root@mysql-5 data]# mysql -uroot -p123456 -e"select * from wjw01.t1"
Warning: Using a password on the command lineinterface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | wangwu |
| 2 |oldgirl |
| 3 |pig |
| 4 |apple |
在slave2段查看:數(shù)據(jù)同步過去了
[root@mysql-5 data]# mysql -uroot -p123456 -e"select * from wjw01.t1"
Warning: Using a password on the command lineinterface can be insecure.
+----+---------+
| id | name |
+----+---------+
| 1 | wangwu |
| 2 | oldgirl|
| 3 |pig |
| 4 |apple |
實現(xiàn)互為雙主搭建MySQL數(shù)據(jù)庫方法就先給大家講到這里,對于其它相關(guān)問題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會捕捉一些行業(yè)新聞及專業(yè)知識分享給大家的。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。