儲存過程在主從庫上的測試
儲存過程在主從庫上的測試：
結論：
從理論上來講，從庫可以不存在procedure, 主庫上執行的procedure，會在binlog中轉換為相應的sql語句，而不是呼叫procedure,
所以從庫上可以不存在這個procedure， 依然可以於主庫資料同步。
但是實際環境中，從庫上還是要有這個procedure的， 畢竟主庫down了之後，從轉為主的話，沒有相應的procedure，對於應用來說是錯誤 的。
--在主庫上
MariaDB [test]> create table test_pro(id int);
Query OK, 0 rows affected (0.09 sec)
MariaDB [test]> delimiter //
MariaDB [test]> create procedure insert_test_pro()
-> begin
-> declare i int;
-> set i=0;
-> while i<10 do
-> insert into test_pro values (i);
-> set i=i+1;
-> end while;
-> end;
-> //
delimiter ;
Query OK, 0 rows affected (0.33 sec)
MariaDB [test]> delimiter ;
MariaDB [test]> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000002 | 4663 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [test]> flush logs;
Query OK, 0 rows affected (0.03 sec)
MariaDB [test]> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 | 359 | | |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [test]> call insert_test_pro();
Query OK, 1 row affected (0.05 sec)
MariaDB [test]> select count(*) from test_pro;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
-- 從庫上沒有建這個儲存過程：實際上從庫上是同步建了這個procedure的：
MariaDB [test]> show create procedure insert_test_pro;
+-----------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-----------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| insert_test_pro | | CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test_pro`()
begin
declare i int;
set i=0;
while i<10 do
insert into test_pro values (i);
set i=i+1;
end while;
end | utf8 | utf8_general_ci | utf8_general_ci |
+-----------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
在從庫上檢視：
MariaDB [test]> select count(*) from test_pro;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec) --資料是同步過來的
--在主庫上分析binlog看看
MariaDB [test]> show binlog events in 'binlog.000003' from 359;
+---------------+------+---------------+-----------+-------------+--------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+---------------+-----------+-------------+--------------------------------+
| binlog.000003 | 359 | Gtid | 33091 | 397 | BEGIN GTID 0-33091-36 |
| binlog.000003 | 397 | Table_map | 33091 | 444 | table_id: 83 (test.test_pro) |
| binlog.000003 | 444 | Write_rows_v1 | 33091 | 478 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 478 | Xid | 33091 | 505 | COMMIT /* xid=150 */ |
| binlog.000003 | 505 | Gtid | 33091 | 543 | BEGIN GTID 0-33091-37 |
| binlog.000003 | 543 | Table_map | 33091 | 590 | table_id: 83 (test.test_pro) |
| binlog.000003 | 590 | Write_rows_v1 | 33091 | 624 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 624 | Xid | 33091 | 651 | COMMIT /* xid=153 */ |
| binlog.000003 | 651 | Gtid | 33091 | 689 | BEGIN GTID 0-33091-38 |
| binlog.000003 | 689 | Table_map | 33091 | 736 | table_id: 83 (test.test_pro) |
| binlog.000003 | 736 | Write_rows_v1 | 33091 | 770 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 770 | Xid | 33091 | 797 | COMMIT /* xid=156 */ |
| binlog.000003 | 797 | Gtid | 33091 | 835 | BEGIN GTID 0-33091-39 |
| binlog.000003 | 835 | Table_map | 33091 | 882 | table_id: 83 (test.test_pro) |
| binlog.000003 | 882 | Write_rows_v1 | 33091 | 916 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 916 | Xid | 33091 | 943 | COMMIT /* xid=159 */ |
| binlog.000003 | 943 | Gtid | 33091 | 981 | BEGIN GTID 0-33091-40 |
| binlog.000003 | 981 | Table_map | 33091 | 1028 | table_id: 83 (test.test_pro) |
| binlog.000003 | 1028 | Write_rows_v1 | 33091 | 1062 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 1062 | Xid | 33091 | 1089 | COMMIT /* xid=162 */ |
| binlog.000003 | 1089 | Gtid | 33091 | 1127 | BEGIN GTID 0-33091-41 |
| binlog.000003 | 1127 | Table_map | 33091 | 1174 | table_id: 83 (test.test_pro) |
| binlog.000003 | 1174 | Write_rows_v1 | 33091 | 1208 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 1208 | Xid | 33091 | 1235 | COMMIT /* xid=165 */ |
| binlog.000003 | 1235 | Gtid | 33091 | 1273 | BEGIN GTID 0-33091-42 |
| binlog.000003 | 1273 | Table_map | 33091 | 1320 | table_id: 83 (test.test_pro) |
| binlog.000003 | 1320 | Write_rows_v1 | 33091 | 1354 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 1354 | Xid | 33091 | 1381 | COMMIT /* xid=168 */ |
| binlog.000003 | 1381 | Gtid | 33091 | 1419 | BEGIN GTID 0-33091-43 |
| binlog.000003 | 1419 | Table_map | 33091 | 1466 | table_id: 83 (test.test_pro) |
| binlog.000003 | 1466 | Write_rows_v1 | 33091 | 1500 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 1500 | Xid | 33091 | 1527 | COMMIT /* xid=171 */ |
| binlog.000003 | 1527 | Gtid | 33091 | 1565 | BEGIN GTID 0-33091-44 |
| binlog.000003 | 1565 | Table_map | 33091 | 1612 | table_id: 83 (test.test_pro) |
| binlog.000003 | 1612 | Write_rows_v1 | 33091 | 1646 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 1646 | Xid | 33091 | 1673 | COMMIT /* xid=174 */ |
| binlog.000003 | 1673 | Gtid | 33091 | 1711 | BEGIN GTID 0-33091-45 |
| binlog.000003 | 1711 | Table_map | 33091 | 1758 | table_id: 83 (test.test_pro) |
| binlog.000003 | 1758 | Write_rows_v1 | 33091 | 1792 | table_id: 83 flags: STMT_END_F |
| binlog.000003 | 1792 | Xid | 33091 | 1819 | COMMIT /* xid=177 */ |
+---------------+------+---------------+-----------+-------------+--------------------------------+
40 rows in set (0.00 sec)
[mysql@localhost bin]$ mysqlbinlog binlog.000003 --start-position=359
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150323 21:30:02 server id 33091 end_log_pos 248 Start: binlog v 4, server v 10.0.10-MariaDB-log created 150323 21:30:02
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
WhUQVQ9DgQAA9AAAAPgAAAABAAQAMTAuMC4xMC1NYXJpYURCLWxvZwAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA3AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQAYca98Q==
'/*!*/;
# at 359
#150323 21:30:29 server id 33091 end_log_pos 397 **GTID type** 0-33091-36
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=33091*//*!*/;
/*!100001 SET @@session.gtid_seq_no=36*//*!*/;
BEGIN
/*!*/;
# at 397
# at 444
#150323 21:30:29 server id 33091 end_log_pos 444 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 478 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAALwBAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAN4BAAAAAFMAAAAAAAEAAf/+AAAAAA==
'/*!*/;
# at 478
#150323 21:30:29 server id 33091 end_log_pos 505 Xid = 150
COMMIT/*!*/;
# at 505
#150323 21:30:29 server id 33091 end_log_pos 543 **GTID type** 0-33091-37
/*!100001 SET @@session.gtid_seq_no=37*//*!*/;
BEGIN
/*!*/;
# at 543
# at 590
#150323 21:30:29 server id 33091 end_log_pos 590 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 624 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAE4CAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAHACAAAAAFMAAAAAAAEAAf/+AQAAAA==
'/*!*/;
# at 624
#150323 21:30:29 server id 33091 end_log_pos 651 Xid = 153
COMMIT/*!*/;
# at 651
#150323 21:30:29 server id 33091 end_log_pos 689 **GTID type** 0-33091-38
/*!100001 SET @@session.gtid_seq_no=38*//*!*/;
BEGIN
/*!*/;
# at 689
# at 736
#150323 21:30:29 server id 33091 end_log_pos 736 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 770 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAOACAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAAIDAAAAAFMAAAAAAAEAAf/+AgAAAA==
'/*!*/;
# at 770
#150323 21:30:29 server id 33091 end_log_pos 797 Xid = 156
COMMIT/*!*/;
# at 797
#150323 21:30:29 server id 33091 end_log_pos 835 **GTID type** 0-33091-39
/*!100001 SET @@session.gtid_seq_no=39*//*!*/;
BEGIN
/*!*/;
# at 835
# at 882
#150323 21:30:29 server id 33091 end_log_pos 882 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 916 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAHIDAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAJQDAAAAAFMAAAAAAAEAAf/+AwAAAA==
'/*!*/;
# at 916
#150323 21:30:29 server id 33091 end_log_pos 943 Xid = 159
COMMIT/*!*/;
# at 943
#150323 21:30:29 server id 33091 end_log_pos 981 **GTID type** 0-33091-40
/*!100001 SET @@session.gtid_seq_no=40*//*!*/;
BEGIN
/*!*/;
# at 981
# at 1028
#150323 21:30:29 server id 33091 end_log_pos 1028 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 1062 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAAQEAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAACYEAAAAAFMAAAAAAAEAAf/+BAAAAA==
'/*!*/;
# at 1062
#150323 21:30:29 server id 33091 end_log_pos 1089 Xid = 162
COMMIT/*!*/;
# at 1089
#150323 21:30:29 server id 33091 end_log_pos 1127 **GTID type** 0-33091-41
/*!100001 SET @@session.gtid_seq_no=41*//*!*/;
BEGIN
/*!*/;
# at 1127
# at 1174
#150323 21:30:29 server id 33091 end_log_pos 1174 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 1208 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAJYEAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAALgEAAAAAFMAAAAAAAEAAf/+BQAAAA==
'/*!*/;
# at 1208
#150323 21:30:29 server id 33091 end_log_pos 1235 Xid = 165
COMMIT/*!*/;
# at 1235
#150323 21:30:29 server id 33091 end_log_pos 1273 **GTID type** 0-33091-42
/*!100001 SET @@session.gtid_seq_no=42*//*!*/;
BEGIN
/*!*/;
# at 1273
# at 1320
#150323 21:30:29 server id 33091 end_log_pos 1320 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 1354 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAACgFAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAEoFAAAAAFMAAAAAAAEAAf/+BgAAAA==
'/*!*/;
# at 1354
#150323 21:30:29 server id 33091 end_log_pos 1381 Xid = 168
COMMIT/*!*/;
# at 1381
#150323 21:30:29 server id 33091 end_log_pos 1419 **GTID type** 0-33091-43
/*!100001 SET @@session.gtid_seq_no=43*//*!*/;
BEGIN
/*!*/;
# at 1419
# at 1466
#150323 21:30:29 server id 33091 end_log_pos 1466 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 1500 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAALoFAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAANwFAAAAAFMAAAAAAAEAAf/+BwAAAA==
'/*!*/;
# at 1500
#150323 21:30:29 server id 33091 end_log_pos 1527 Xid = 171
COMMIT/*!*/;
# at 1527
#150323 21:30:29 server id 33091 end_log_pos 1565 **GTID type** 0-33091-44
/*!100001 SET @@session.gtid_seq_no=44*//*!*/;
BEGIN
/*!*/;
# at 1565
# at 1612
#150323 21:30:29 server id 33091 end_log_pos 1612 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 1646 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAEwGAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAG4GAAAAAFMAAAAAAAEAAf/+CAAAAA==
'/*!*/;
# at 1646
#150323 21:30:29 server id 33091 end_log_pos 1673 Xid = 174
COMMIT/*!*/;
# at 1673
#150323 21:30:29 server id 33091 end_log_pos 1711 **GTID type** 0-33091-45
/*!100001 SET @@session.gtid_seq_no=45*//*!*/;
BEGIN
/*!*/;
# at 1711
# at 1758
#150323 21:30:29 server id 33091 end_log_pos 1758 Table_map: `test`.`test_pro` mapped to number 83
#150323 21:30:29 server id 33091 end_log_pos 1792 Write_rows: table id 83 flags: STMT_END_F
BINLOG '
dRUQVRNDgQAALwAAAN4GAAAAAFMAAAAAAAEABHRlc3QACHRlc3RfcHJvAAEDAAE=
dRUQVRdDgQAAIgAAAAAHAAAAAFMAAAAAAAEAAf/+CQAAAA==
'/*!*/;
# at 1792
#150323 21:30:29 server id 33091 end_log_pos 1819 Xid = 177
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
--在從庫上刪掉procedure insert_test_pro;
MariaDB [test]> drop procedure insert_test_pro;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show create procedure insert_test_pro;
ERROR 1305 (42000): PROCEDURE insert_test_pro does not exist
MariaDB [test]>
在主庫上執行procedure;
MariaDB [test]> call insert_test_pro();
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select count(*) from test_pro;
+----------+
| count(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
--在從庫上檢視：
MariaDB [test]> select count(*) from test_pro;
+----------+
| count(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
--資料是同步的
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1471118/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE儲存過程中建立子過程的測試！Oracle儲存過程
- 我的測試儲存過程程式碼儲存過程
- [原創]儲存過程效能測試儲存過程
- 全面的儲存過程 測試可用儲存過程
- 從sybase的儲存過程轉向oracle的儲存過程儲存過程Oracle
- 在儲存過程A中呼叫儲存過程B的結果儲存過程
- 資料庫設計：儲存過程主體資料庫儲存過程
- 在sqlplus互動環境下測試儲存過程SQL儲存過程
- 使用dbms_profiler測試儲存過程效能儲存過程
- oracle 匿名儲存過程插入大量測試資料Oracle儲存過程
- mysql儲存過程小試牛刀MySql儲存過程
- MySql資料庫——儲存過程MySql資料庫儲存過程
- 簡單測試在儲存過程中臨時表與union all的效能差別儲存過程
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- 儲存過程儲存過程
- 基於儲存過程的百萬級測試資料自動生成儲存過程
- 在Entity Framework中使用儲存過程Framework儲存過程
- 在儲存過程中寫truncate table儲存過程
- 資料庫設計：儲存過程資料庫儲存過程
- oracle的儲存過程Oracle儲存過程
- 修改的儲存過程儲存過程
- mysql的儲存過程MySql儲存過程
- 在C#中使用儲存過程的方法C#儲存過程
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- 儲存過程呼叫其他模式的儲存過程需要注意的地方儲存過程模式
- 淺談資料庫中的儲存過程資料庫儲存過程
- 【資料庫】資料庫儲存過程(一)資料庫儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- Oracle儲存過程乾貨（一）：儲存過程基礎Oracle儲存過程
- 從根源揭祕HashMap的資料儲存過程HashMap儲存過程
- 系統測試-從研發到測試過程
- Mysql 的儲存過程和儲存函式MySql儲存過程儲存函式
- 儲存過程與儲存函式儲存過程儲存函式
- Oracle儲存過程Oracle儲存過程
- Mysql 儲存過程MySql儲存過程
- 使用儲存過程儲存過程
- sybase儲存過程儲存過程