這篇文章將為大家詳細(xì)講解有關(guān)MySQL中怎么利用mysql-mm實(shí)現(xiàn)互為主從復(fù)制,文章內(nèi)容質(zhì)量較高,因此小編分享給大家做個(gè)參考,希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。
雅安網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián)公司,雅安網(wǎng)站設(shè)計(jì)制作,有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為雅安1000+提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站制作要多少錢,請(qǐng)找那個(gè)售后服務(wù)好的雅安做網(wǎng)站的公司定做!
原理講述:
AB復(fù)制主要是通過兩個(gè)slave進(jìn)程(Sql和I/O進(jìn)程)和Master的I/O進(jìn)程完成的
復(fù)制過程主要是Slave從Master端獲取該日志然后再在自己身上完全順序的執(zhí)行日志中所記錄的各種操作
復(fù)制過程三部曲:
1)Slave啟動(dòng)I/O進(jìn)程連接Master,并請(qǐng)求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容
2)Master接到請(qǐng)求后通過負(fù)責(zé)復(fù)制的IO進(jìn)程將Master端的bin-log文件的名稱 bin-log的位置以及日志信息返回給Slave
3)Slave收到信息后將接收到的日志內(nèi)容依次添加到Slave端的relay-log文件的最末端,并將讀取到的Master端的
bin-log的文件名和位置記錄到master-info文件中,以便在下一次讀取的時(shí)候能夠清楚的告訴Master“我需要從
某個(gè)bin-log的哪個(gè)位置開始往后的日志內(nèi)容,請(qǐng)發(fā)給我”
Slave的Sql進(jìn)程檢測(cè)到relay-log中新增加了內(nèi)容后,會(huì)馬上解析relay-log的內(nèi)容成為在Master端真實(shí)執(zhí)行時(shí)候
的那些可執(zhí)行的內(nèi)容,并在自身執(zhí)行
環(huán)境描述:最好兩臺(tái)機(jī)器的mysql版本完全相同
A:211.100.97.246 Linux x86_64 mysql5.1.56
B:211.100.97.250 Linux x86_64 mysql5.1.56
啟動(dòng)mysql進(jìn)程
A 和 B均啟動(dòng)mysql進(jìn)程
修改安全級(jí)別
關(guān)閉selinux,iptables允許兩臺(tái)機(jī)器之間的mysql端口互連
可以在/etc/sysconfig/selinux中設(shè)置參數(shù)selinux= disabled。
添加iptables -A INPUT -s SourceIP -p tcp --dport 3306 -j ACCEPT
修改完測(cè)試一下端口:
A: telnet B_IP 3306
B: telnet A_IP 3306
創(chuàng)建賬戶
A: useradd repl1
B: useradd repl2
添加完查看賬戶信息
A: id repl1
B: id repl2
A:mysql配置文件
user=mysql
log-bin=mysql-bin
server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
B:mysql配置文件
user=mysql
log-bin=mysql-bin
server-id = 2
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
說明:
server-id表示本機(jī)的序列號(hào),如果為1的話一般代表master
binlog-do-db 表示需要備份哪個(gè)數(shù)據(jù)庫,如果要備份多個(gè)數(shù)據(jù)庫,應(yīng)該添加多條記錄
replicate-do-db 表示要同步的那個(gè)數(shù)據(jù)庫
log-bin 表示開啟binlog日志功能,打開該選項(xiàng)才可以通過I/O進(jìn)程將mater上的日志信息寫入到Slave的relay-log
auto_increment_increment定義下一次AUTO_INCREMENT的步長(zhǎng)
auto_increment_offset 定義AUTO_INCREMENT的起點(diǎn)值
授權(quán)用戶【至少賦予FILE,SELECT,REPLICATION SLAVE權(quán)限】
A:允許B通過repl2賬戶與A同步數(shù)據(jù)
mysql> grant replication client on *.* to identified by 'PASSWD';
mysql> flush privileges;
查看一下授權(quán)情況:
mysql> select * from mysql.user where ;
*************************** 1. row ***************************
Host: 211.100.97.250
User: repl2
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)
B:允許A通過repl1賬戶與B同步數(shù)據(jù)
mysql> grant replication client on *.* to identified by 'PASSWD';
mysql> flush privileges;
mysql> select * from mysql.user where ;
*************************** 1. row ***************************
Host: 211.100.97.246
User: repl1
Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
1 row in set (0.00 sec)
授權(quán)以后需要測(cè)試
A: /usr/local/mysql/bin/mysql -h'B_IP' -urepl1 -p
B: /usr/local/mysql/bin/mysql -h'A_IP' -urepl2 -p
兩臺(tái)機(jī)器上均重啟mysql
killall mysqld
ps aux |grep mysql
/usr/local/mysql/bin/mysqld_safe &
ps aux |grep mysql
進(jìn)入MYSQL的SHELL
/usr/local/mysql/bin/mysql -uroot -p
A:
服務(wù)器鎖表(鎖表狀態(tài)下不能終止mysql進(jìn)程,否則會(huì)失敗)
mysql> flush tables with read lock\G;
Query OK, 0 rows affected (0.01 sec)
----------------
查看 A 服務(wù)器主機(jī)狀態(tài)(記錄二進(jìn)制開始文件,位置)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 106
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
----------------
修改A服務(wù)器配置
mysql> change master to
-> master_host='211.100.97.250',
-> master_user='repl2',
-> master_password='123456',
-> master_log_file='mysql-bin.000014',
-> master_log_pos=98;
Query OK, 0 rows affected (0.01 sec)
說明:
master_host表示主機(jī)B(250)是A(246)的master
master_user表示允許A(246)上的賬戶repl1連接到master進(jìn)行復(fù)制,建議兩臺(tái)主機(jī)的授權(quán)用戶和密碼完全相同。
master_password 表示授權(quán)用戶repl1的密碼
master_log_file 表示master上日志文件的名稱
master_log_pos 表示日志文件的位置
----------------
mysql> slave stop;
mysql> change master to master_host='B_IP', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
然后啟動(dòng)slave
mysql> slave start;
啟動(dòng)之后查看slave的狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 211.100.97.250
Master_User: repl1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: XKWB5510-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 409
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
查看相關(guān)進(jìn)程
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 4
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 18
User: system user
Host:
db: NULL
Command: Connect
Time: 100
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 19
User: system user
Host:
db: NULL
Command: Connect
Time: 100
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 4. row ***************************
Id: 21
User: repl2
Host: 211.100.97.250:34536
db: NULL
Command: Binlog Dump
Time: 19
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)
----------------------
同步兩個(gè)數(shù)據(jù)庫的基礎(chǔ)庫
----------------
解鎖服務(wù)器
mysql> unlock tables;
----------------
mysql> use test;
mysql> show tables;
Empty set (0.00 sec)
----------------
mysql> create table t11_replicas
-> (id int not null auto_increment primary key,
-> str varchar(255) not null) engine myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t11_replicas(str) values
-> ('This is a master to master test table');
Query OK, 1 row affected (0.00 sec)
----------------
mysql> show tables;
----------------
mysql> select * from t11_replicas;
------------------------------------------------------------------------------------------------
B:
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000014
Position: 98
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
----------------
mysql> stop slave;
mysql> change master to master_host='A_IP', master_user='repl2', master_password='123456', master_log_file='mysql-bin.000005',master_log_pos=106;
mysql> start slave;
----------------
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 15
User: repl1
Host: 211.100.97.246:51840
db: NULL
Command: Binlog Dump
Time: 101
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 16
User: system user
Host:
db: NULL
Command: Connect
Time: 20
State: Waiting for master to send event
Info: NULL
*************************** 4. row ***************************
Id: 17
User: system user
Host:
db: NULL
Command: Connect
Time: 20
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
4 rows in set (0.00 sec)
----------------
mysql> show slave status\G;
----------------
mysql> use test;
Database changed
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
----------------
重置日志
mysql> reset master;
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 106
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
[root@XKWB5510 ~]# ls -l /var/mysql/database/data/
total 0
查看data目錄下是否有報(bào)錯(cuò)文件:
[root@XKWB5510 data]# ls /var/mysql/database/data/
重新執(zhí)行change master to 命令以后,再啟動(dòng)slave,再看一下slave的狀態(tài),I/O進(jìn)程起來了
進(jìn)程都起來之后,就實(shí)施監(jiān)控
-------------------------------------------------------------------------------------------------------------
報(bào)錯(cuò):
1)
change master導(dǎo)致的:
Last_IO_Error: error connecting to master - retry-time: 60 retries
2)
在沒有解鎖的情況下停止slave進(jìn)程:
mysql> stop slave;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
3)
change master語法錯(cuò)誤,落下逗號(hào)
mysql> change master to
-> master_host='211.100.97.250'
-> master_user='repl2',
-> master_password='123456',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=106;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_user='repl2',
master_password='123456',
master_log_file='mysql-bin.000002' at line 3
4)
在沒有停止slave進(jìn)程的情況下change master
mysql> change master to master_host='211.100.97.246', master_user='repl1', master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=106;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
5)
A B的server-id相同:
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;
these ids must be different for replication to work (or the --replicate-same-server-id option must be used on
slave but this does not always make sense; please check the manual before using it).
查看server-id
mysql> show variables like 'server_id';
手動(dòng)修改server-id
mysql> set global server_id=2; #此處的數(shù)值和my.cnf里設(shè)置的一樣就行
mysql> slave start;
6)change master之后,查看slave的狀態(tài),發(fā)現(xiàn)slave_IO_running 為NO
需要注意的是,做完上述操作之后最后重啟mysql進(jìn)程
---------------------------------------
同步數(shù)據(jù)情況
A:在A上插入數(shù)據(jù)
mysql> create table aniya (id int not null auto_increment primary key, str varchar(255) not null);
mysql> insert into aniya(str) values
-> ('This is a master to master test table');
mysql> select * from aniya;
+----+---------------------------------------+
| id | str |
+----+---------------------------------------+
| 1 | This is a master to master test table |
+----+---------------------------------------+
1 row in set (0.00 sec)
查看B的日志:
[root@XKWB5705 var]# ls -lrth XKWB5705-relay-bin.000003
-rw-rw---- 1 mysql mysql 576 Sep 26 12:29 XKWB5705-relay-bin.000003
[root@XKWB5705 var]# more XKWB5705-relay-bin.000003
.in.N
(id int not null auto_increment primary key,
str varchar(255) not null)3
('This is a master to master test table')
-----
B A主從同步測(cè)試
在B上創(chuàng)建表lian,并插入數(shù)據(jù)
mysql> create table lian (a int,b char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into lian (a,b)values(22,hahah);
ERROR 1054 (42S22): Unknown column 'hahah' in 'field list'
mysql> insert into lian (a,b)values(22,'hahah');
Query OK, 1 row affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| lian |
+----------------+
1 row in set (0.00 sec)
mysql> select * from lian;
+------+-------+
| a | b |
+------+-------+
| 22 | hahah |
+------+-------+
1 row in set (0.00 sec)
查看一下B的master日志,證明以上操作成功:
cat mysql-bin.000002
.?Nh?@stdtestcreate table lian (a int,b char(10))??Nl>@stdtestinsert into lian (a,b)values(22,'hahah')
現(xiàn)在查看從服務(wù)器A的relay日志,發(fā)現(xiàn)日志已經(jīng)同步了
[root@XKWB5510 var]# cat XKWB5510-relay-bin.000003
.?Nh?@stdtestcreate table lian (a int,b char(10))??Nl>@stdtestinsert into lian (a,b)values(22,'hahah')
再在從服務(wù)器A上看一下數(shù)據(jù)庫是不是存在lian這個(gè)表:
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aniya |
| lian |
+----------------+
2 rows in set (0.00 sec)
現(xiàn)在說明數(shù)據(jù)B A 主 從 同步成功
---------------------------------------------------------------------------
測(cè)試A B主從
在A上創(chuàng)建表From246,并插入數(shù)據(jù)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A246 |
| aniya |
| lian |
+----------------+
3 rows in set (0.00 sec)
mysql> create table From246(Name varchar(255),Sex varchar(255),Age int(10));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| A246 |
| From246 |
| aniya |
| lian |
+----------------+
4 rows in set (0.00 sec)
mysql> insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24);
Query OK, 1 row affected (0.00 sec)
mysql> select * from From246;
+--------+------+------+
| Name | Sex | Age |
+--------+------+------+
| Zhaoyj | Girl | 24 |
+--------+------+------+
1 row in set (0.00 sec)
查看A的master日志,證明上述操作成功
[root@XKWB5510 var]# tail -1 mysql-bin.000002
testcreate table From246(Name varchar(255),Sex varchar(255),Age int(10))?N?R@stdtestinsert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)
查看A的master日志狀態(tài)
[root@XKWB5510 var]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000003 |tail -15
/*!*/;
# at 702
#110926 14:01:51 server id 1 end_log_pos 838 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016911/*!*/;
create table From246(Name varchar(255),Sex varchar(255),Age int(10))
/*!*/;
# at 838
#110926 14:02:05 server id 1 end_log_pos 966 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016925/*!*/;
insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET ;
查看B的relay日志,同步日志成功
testcreate table From246(Name varchar(255),Sex varchar(255),Age int(10))?N?R@stdtestinsert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)[root@XKWB5705 var]
查看B的relay日志狀態(tài)
[root@XKWB5705 var]# /usr/local/mysql/bin/mysqlbinlog XKWB5705-relay-bin.000005|tail -13
/usr/local/mysql/bin/mysqlbinlog: Character set '#28' is not a compiled character set and is not specified in the '/usr/local/mysql/share/mysql/charsets/Index.xml' file
#110926 14:01:51 server id 1 end_log_pos 838 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016911/*!*/;
create table From246(Name varchar(255),Sex varchar(255),Age int(10))
/*!*/;
# at 853
#110926 14:02:05 server id 1 end_log_pos 966 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1317016925/*!*/;
insert into From246 (Name,Sex,Age)values('Zhaoyj','Girl',24)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET ;
但是數(shù)據(jù)卻沒有插入
> show tables;
+----------------+
| Tables_in_test |
+----------------+
| lian |
+----------------+
1 row in set (0.00 sec)
當(dāng)我刪除A上的表時(shí),B的relay日志也同步了
[root@XKWB5705 var]# tail -4 XKWB5705-relay-bin.000005
??NS?@stdtestdrop table A246??NT@stdtestdrop table aniya??NSd@stdtestdrop table lian??NV?@stdtestdrop table From246
------------------------------------------------------------------------------
問題排查:
首先在Master上用
show processlist; 查看下進(jìn)程是否Sleep太多。發(fā)現(xiàn)很正常。
show master status; 也正常。
再跑到Slave上查看也正常
show slave status;
發(fā)現(xiàn)一個(gè)問題:
當(dāng)我手動(dòng)從A導(dǎo)入B數(shù)據(jù)時(shí)
mysql> load table From246 from master;
ERROR 1115 (42000): Unknown character set: 'gbk'
懷疑:難道是因?yàn)樽址膯栴}導(dǎo)致AB主從復(fù)制失敗 ?
通過show character set 命令查看到
A有g(shù)bk字符集而B沒有
mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
27 rows in set (0.00 sec)
那現(xiàn)在應(yīng)該是在啟動(dòng)mysql的時(shí)候統(tǒng)一他們的字符集
A :[root@XKWB5510 var]# /usr/local/mysql/bin/mysqld_safe --default-character-set=latin1 &
B :[root@XKWB5705 var]# /usr/local/mysql/bin/mysqld_safe --default-character-set=latin1 &
在B上從A導(dǎo)入數(shù)據(jù):
mysql> show tables;
Empty set (0.00 sec)
mysql> load table From246 from master;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| From246 |
+----------------+
1 row in set (0.00 sec)
現(xiàn)在字符集的問題解決了
-----------------------------------
現(xiàn)在手動(dòng)啟動(dòng)一下“將日志應(yīng)用于數(shù)據(jù)庫”的線程:SLAVE start SQL_THREAD
和“把master段的日志寫到本地”的線程:SLAVE start IO_THREAD
發(fā)現(xiàn)同步數(shù)據(jù)還是失敗,那說明不是線程的問題
如果發(fā)現(xiàn) Seconds_Behind_Master 為 (null)
解決:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;
之后Slave會(huì)和Master去同步 主要看Seconds_Behind_Master是否為0,直到為0時(shí)就已經(jīng)同步了。。
-----------------------------------
slave B機(jī)器上master.info信息,與master A上的信息是否是同步的
mater A:
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 808
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
slave B:
[root@XKWB5705 var]# cat master.info
15
mysql-bin.000004
808
211.100.97.246
repl2
123456
3306
60
0
從以上可以看到是同步的
--------------------------------------------
flush master
flush slave
關(guān)于mysql中怎么利用mysql-mm實(shí)現(xiàn)互為主從復(fù)制就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò),可以把它分享出去讓更多的人看到。
分享名稱:mysql中怎么利用mysql-mm實(shí)現(xiàn)互為主從復(fù)制
文章轉(zhuǎn)載:http://muchs.cn/article34/ghjspe.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供網(wǎng)站內(nèi)鏈、建站公司、網(wǎng)站收錄、搜索引擎優(yōu)化、網(wǎng)站策劃、關(guān)鍵詞優(yōu)化
聲明:本網(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)