测试原由随着PXC的逐步上线。线上数据库的同步方式慢慢由之前的STATEMENT模式转换到了ROW模式。由于同步方式的改变引发了一些同步问题。测试目的一定程度上解决R
测试原由
随着PXC的逐步上线。线上数据库的同步方式慢慢由之前的STATEMENT模式转换到了ROW模式。由于同步方式的改变引发了一些同步问题。
测试目的
一定程度上解决ROW模式下主从同步的问题。作为以后PXC集群down掉，人工修复的操作文档。
测试环境
masterold02:7301
masterold03:7302
skavetest178:7303
主库操作
vim my.cnf 加入下一面一句
binlog_format=ROW 数据库binlog使用ROW模式同步
分别赋予丛库同步用户的权限
grant all on *.* to okooo_rep@'192.168.%.%' identified by 'Bjfcmlc@Mhxzkhl';
flush privileges;
测试开始
测试基础同步功能
?.让test178作为从去同步old02的数据
CHANGE MASTER TO MASTER_HOST='192.168.8.72',MASTER_USER='okooo_rep',MASTER_PASSWORD='Bjfcmlc@Mhxzkhl',
MASTER_PORT=7301,MASTER_LOG_FILE='logbin.000001',MASTER_LOG_POS=4;
? 查看主从状态，我们看到很快test178就可以和old02保持一致了。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.72
Master_User: okooo_rep
Master_Port: 7301
Connect_Retry: 60
Master_Log_File: logbin.000006
Read_Master_Log_Pos: 332
Relay_Log_File: relay.000007
Relay_Log_Pos: 475
Relay_Master_Log_File: logbin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
? 让test178作为从去同步old03的数据，我们看到很快test178也和old03保持一致了。
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.8.73',MASTER_USER='okooo_rep',MASTER_PASSWORD='Bjfcmlc@Mhxzkhl',MASTER_PORT=7302,MASTER_LOG_FILE='logbin.000001',MASTER_LOG_POS=4;
start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.8.73
Master_User: okooo_rep
Master_Port: 7302
Connect_Retry: 60
Master_Log_File: logbin.000005
Read_Master_Log_Pos: 332
Relay_Log_File: relay.000006
Relay_Log_Pos: 475
Relay_Master_Log_File: logbin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
总结：基础同步测试完成，说明在数据库新搭建结束的时候数据库中数据一致的情况下，test178可以正常的和old02和old03中任意主库同步数据。
写入测试
? 分别在old02，old03上建立新的数据库和表
create database row_slave;
CREATE TABLE `row_test` (
`id` int(10) unsigned NOT NULL,
`hostname` varchar(20) NOT NULL default '',
`create_time` datetime NOT NULL default '0000-00-00 00:00:00',
`update_time` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 ;
? old02写入数据
insert into row_test values(1,'old02','2013-12-11 00:00:00','2013-12-11 00:00:00');
insert into row_test values(2,'old02','2013-12-11 00:00:00','2013-12-11 00:00:00');
insert into row_test values(3,'old03','2013-12-11 01:00:00','2013-12-11 01:00:00');
insert into row_test values(4,'old03','2013-12-11 01:00:00','2013-12-11 01:00:00');
?查看old02，old03，test178 皆可以查出来
mysql> select * from row_test;
+----+----------+---------------------+---------------------+
| id | hostname | create_time | update_time |
+----+----------+---------------------+---------------------+
| 1 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 2 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 3 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
| 4 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
+----+----------+---------------------+---------------------+
?old03写入数据，此时old03（主）和test178（丛）在同步
insert into row_test values(5,'old03','2013-12-11 02:00:00','2013-12-11 02:00:00');
insert into row_test values(6,'old03','2013-12-11 02:00:00','2013-12-11 02:00:00');
?查看old03，test178 皆可查出。此时test178和 old02数据已经不一致了，丛库比old02多出2条数据id=5,6。
+----+----------+---------------------+---------------------+
| id | hostname | create_time | update_time |
+----+----------+---------------------+---------------------+
| 1 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 2 | old02 | 2013-12-11 00:00:00 | 2013-12-11 00:00:00 |
| 3 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
| 4 | old03 | 2013-12-11 01:00:00 | 2013-12-11 01:00:00 |
| 5 | old03 | 2013-12-11 02:00:00 | 2013-12-11 02:00:00 |
| 6 | old03 | 2013-12-11 02:00:00 | 2013-12-11 02:00:00 |
+----+----------+---------------------+---------------------+
?old02写入数据 此时主从库还是test178和old03在同步，和old02没有关系
insert into row_test values(7,'old02','2013-12-11 03:00:00','2013-12-11 03:00:00');
insert into row_test values(8,'old02','2013-12-11 03:00:00','2013-12-11 03:00:00');
?查看 old02的binlog 来找到插入id =7,8的 pos点
cd /home/okooo/apps/tmp_slave01/logs
../bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v -v ./logbin.000007
# at 1399
#131211 11:36:42 server id 1287301 end_log_pos 1472 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1386733002/*!*/;
BEGIN
/*!*/;
# at 1472
# at 1529
#131211 11:36:42 server id 1287301 end_log_pos 1529 Table_map: `row_slave`.`row_test` mapped to number 33
#131211 11:36:42 server id 1287301 end_log_pos 1585 Write_rows: table id 33 flags: STMT_END_F
### INSERT INTO row_slave.row_test
### SET
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='old02' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @4=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
# at 1585
#131211 11:36:42 server id 1287301 end_log_pos 1612 Xid = 40
COMMIT/*!*/;
# at 1612
#131211 11:36:43 server id 1287301 end_log_pos 1685 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1386733003/*!*/;
BEGIN
/*!*/;
# at 1685
# at 1742
#131211 11:36:43 server id 1287301 end_log_pos 1742 Table_map: `row_slave`.`row_test` mapped to number 33
#131211 11:36:43 server id 1287301 end_log_pos 1798 Write_rows: table id 33 flags: STMT_END_F
### INSERT INTO row_slave.row_test
### SET
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='old02' /* VARSTRING(20) meta=20 nullable=0 is_null=0 */
### @3=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
### @4=2013-12-11 03:00:00 /* DATETIME meta=0 nullable=0 is_null=0 */
# at 1798
#131211 11:36:43 server id 1287301 end_log_pos 1825 Xid = 41
COMMIT/*!*/;
DELIMITER ;
# End of log file
?改变test178的同步点和old02同步
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.8.72',MASTER_USER='okooo_rep',MASTER_PASSWORD='Bjfcmlc@Mhxzkhl',MASTER_PORT=7301,MASTER_LOG_FILE='logbin.000007',MASTER_LOG_POS=1399;
start slave;
show slave status\G
MySQL String Types: Storage, Performance, and Best PracticesMay 10, 2025 am 12:02 AMMySQLstringtypesimpactstorageandperformanceasfollows:1)CHARisfixed-length,alwaysusingthesamestoragespace,whichcanbefasterbutlessspace-efficient.2)VARCHARisvariable-length,morespace-efficientbutpotentiallyslower.3)TEXTisforlargetext,storedoutsiderows,
Understanding MySQL String Types: VARCHAR, TEXT, CHAR, and MoreMay 10, 2025 am 12:02 AMMySQLstringtypesincludeVARCHAR,TEXT,CHAR,ENUM,andSET.1)VARCHARisversatileforvariable-lengthstringsuptoaspecifiedlimit.2)TEXTisidealforlargetextstoragewithoutadefinedlength.3)CHARisfixed-length,suitableforconsistentdatalikecodes.4)ENUMenforcesdatainte
What are the String Data Types in MySQL?May 10, 2025 am 12:01 AMMySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,2)VARCHARforvariable-lengthtext,3)BINARYandVARBINARYforbinarydata,4)BLOBandTEXTforlargedata,and5)ENUMandSETforcontrolledinput.Eachtypehasspecificusesandperformancecharacteristics,sochoose
How to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AMTograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)
How to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AMToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl
MySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AMToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'
MySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AMThe string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.
MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AMThe best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
Zend Studio 13.0.1
Powerful PHP integrated development environment
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
