I also posted this article on oschina. I have encountered many problems before. Thanks to your help, I have solved them all one by one. Now the record is as follows: 1.20.work mysqlbench uses mysql-5.5.30-win32.zip to test and decompress the package in win and copy two copies, for example, d: mysqlmymy: mysqlmysql2mysql1 master database, mysql
I also posted this article on oschina. I have encountered many problems before. Thanks to your help, I have solved them one by one. Now the record is as follows: 1. prepare mysql version is the use of mysql-5.5.30-win32.zip in win test decompression, and copy two copies, such as d: \ mysql \ mysql1 d: \ mysql \ mysql2 mysql1 master database, mysql
I also posted this article on oschina.
I have encountered many problems before. Thanks to your help, I have solved them one by one. The record is as follows:
1. Preparations
Mysql version uses mysql-5.5.30-win32.zip
Test done in win
Decompress the package and copy the two copies, as shown in figure
D: \ mysql \ mysql1
D: \ mysql \ mysql2
Mysql1 is the master database, and mysql2 is the slave database.
Create the ini directory in the two directories, copy the my-small.ini file to the ini directory (here you can use an ini file, which can be modified at will), and rename it my. ini.
Create the run. bat, stop. bai, and restart. bat files in the two directories for starting, stopping, and restarting mysql respectively.
2. Start Configuration
Modify the my. ini file
Go to the mysql1 directory, open the my. ini file in the ini directory, and modify the mysql port to 3311.
Note: There are two places in the my. ini file to be modified.
Port = 3311
Similarly, the my. ini file under the ini directory in mysql2 also needs to be modified to 3312.
In the [mysqld] section, add:
Server-id = 1
And set the log file:
Log-bin = d:/mysql/mysql1/log/mysql-bin1.log
Log-error = d:/mysql/mysql1/log/mysql-bin1.err
If you only need to synchronize a database, set:
Binlog_ignore_db = Database Name
Otherwise, you do not need to add this row.
In the [mysqld] section of the my. ini file of mysql2, add:
Server-id = 2
Modify the bat file
For your reference:
Run. bat
@ ECHO off
Cls
Color 0A
ECHO welcome to start the MYSQL program in windows2000/2003 of titanium.
ECHO ===================================================== ==============================
Pause
The ECHO program is running. Please wait ......
D: \ MySQL \ mysql1 \ bin \ mysqld.exe -- defaults-file = D: \ MySQL \ mysql1 \ ini \ my. ini
ECHO started successfully
ECHO ===================================================== ==============================
ECHO press any key to exit...
Pause
Exit
Stop. bat
@ ECHO off
Cls
Color 0A
ECHO welcome to use the MYSQL database in windows2000/2003 of titanium to close the program
ECHO ===================================================== ==============================
Pause
The ECHO program is running. Please wait ......
ECHO is shutting down the MYsql program. Please wait...
D: \ MySQL \ mysql1 \ bin \ mysqladmin-uroot-p -- port = 3311 shutdown
ECHO disabled successfully
ECHO ===================================================== ==============================
ECHO press any key to exit...
Pause
Exit
Restart. bat
@ ECHO off
Cls
Color 0A
ECHO welcome to use titanium to restart MYSQL program in windows2000/2003
ECHO ===================================================== ==============================
Pause
The ECHO program is running. Please wait ......
ECHO is shutting down the MYsql program. Please wait...
D: \ MySQL \ mysql1 \ bin \ mysqladmin-uroot-p -- port = 3311 shutdown
ECHO disabled successfully
ECHO press any key to restart MYSQL...
Pause
D: \ MySQL \ mysql1 \ bin \ mysqld.exe -- defaults-file = D: \ MySQL \ mysql1 \ ini \ my. ini
ECHO started successfully
ECHO ===================================================== ==============================
ECHO press any key to exit...
Pause
Exit
Similarly, the mysql2 directory also contains a copy.
After the configuration, run mysql. Do not operate on it immediately (that is, use the database), because we need to ensure that the two databases obtain consistent snapshots. Of course, you can set the read lock:
Flush tables with read lock;
Run two run. bat files. You can close these two command line windows later (not automatically closed)
Enter mysql1 and add an account for REPLICATION. Note that there are two permissions added here: replication client and replication slave.
After that, note that create a new database, such as shopdb, and create a table in the MySQL 1 Database. Then, create a database with the same name and table in mysql2 (or copy the table. For details about how to copy the database, see other documents ).
Then go to mysql2 and set:
CHANGE MASTER
-> MASTER_HOST = 'master _ host_name ',
-> MASTER_USER = 'replication _ user_name ',
-> MASTER_PASSWORD = 'replication _ password ',
-> MASTER_LOG_FILE = 'recorded _ log_file_name ',
-> MASTER_LOG_POS = recorded_log_position;
Then start slave on mysql2:
Start slave;
In this case, run show processlist \ G to check whether mysql1 has been connected and accept logs.
3 Test
Enter mysql1, enter the created shopdb database, and insert data into the created table
Go to mysql2 to check whether data has been automatically copied.
Copying is successful. Otherwise, you must check the problem.
Appendix (for better information ):
1. error message,
Slave I/O: error connecting to master 'backup @ 192.168.1.x: 100'-retry-time: 60 retries: 3306, Error_code: 86400
Solution
Delete all binary log files from the server, including the master.info file in a data directory and files starting with hostname-relay-bin.
Master.info: Records log files, record locations, and connection passwords on the Mysql master server.
2. error message
Error reading packet from server: File '/home/mysql/mysqlLog/log.000001' not found (Errcode: 2) (server_errno = 29)
Solution:
Because the master server has been running for a period of time, a binary file is generated. slave reads the binary file from log.20.01 and deletes the host binary file, including the log. index file.
3. The error message is as follows:
Slave SQL: Error 'table 'xxxx' doesn' t exist 'on query. default database: 't591 '. query: 'insert INTO 'xxxx' (type, post_id, browsenum) SELECT type, post_id, browsenum FROM xxxx WHERE hitdate = '000000', Error_code: 20090209
Solution
Because slave does not have this table, you can use slave start to continue the synchronization.
4. The error message is as follows:
Error 'duplicate entry '1' for key 1' on query. default database: 'movivi1 '. query: 'insert INTO 'v1vid0 _ user_samename 'values (null, 1, '20170101', '11', '20160301', '20160301 ')'
Error '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 ''at line 1' on query. default database: 'Club '. query: 'insert INTO club. point_process (GIVEID, GETID, POINT, CREATETIME, DEMO) VALUES (0, 4971112, 5, '2017-12-19 16:29:28 ','
1 row in set (0.00 sec)
Mysql> Slave status \ G;
Display: Slave_ SQL _Running is NO
Solution:
Mysql> stop slave;
Mysql> set global SQL _slave_skip_counter = 1;
Mysql> start slave;
5. The error message is as follows:
# Show slave status \ G;
Master_Log_File: mysql-bin.000029
Read_Master_Log_Pos: 3154083
Relay_Log_File: c7-relay-bin.000178
Relay_Log_Pos: 633
Relay_Master_Log_File: mysql-bin.000025
Slave_IO_Running: Yes
Slave_ SQL _Running: No
Replicate_Do_DB: club
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_errno.: 1594
Last_Error: Relay log read failure: cocould not parse relay log event entry. the possible reasons are: the master's binary log is upted (you can check this by running 'mysqlbinlog' on the binary log ), the slave's relay log is already upted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. if you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'show SLAVE status' on this slave.
Skip_Counter: 0
Exec_Master_Log_Pos: 1010663436
The cause of this problem is that the master database suddenly stops or the problem ends, changed the mysql-bin.xxx log, the slave server could not find this file, you need to find the synchronization point and log file, and then chage master.
Solution:
Change master
Master_host = '2017. 103.156.198 ',
Master_user = 'synchronization account ',
Master_password = 'synchronized password ',
Master_port = 3306,
Master_log_file = 'mysql-bin.000025 ',
Master_log_pos = 1010663436;
6. The error message is as follows:
Error 'unknown column 'qdir' in 'field list''' on query. default database: 'Club '. query: 'insert into club. question_del (id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime, banzhu_uid, expiration, del_cause, qdir) select id, pid, ques_name, givepoint, title, subject, subject_pid, createtime, approve, did, status, intime, order_d, endtime, '123', 'admin0523 ', 'meaningless restore', qdir from club. question where id = 7330212'
1 row in set (0.00 sec)
If this error occurs, the club. question_del table does not contain the qdir field ~!
Query Desc club. question_del in the master mysql;
Run alter table question_del add qdir varchar (30) not null on the wrong slave server;
7. The error message is as follows:
Slave_IO_Running: NO
This error indicates that the IO process is not connected. If you want to connect, you must correct the POS number and file of the master and then reload the data. Procedure:
Slave stop;
Change master to master_host = 'IP address', master_user = 'Club ', master_password = 'mim', master_log_file = 'mysqld-bin.000048', MASTER_LOG_POS = 396549485;
Note: master_log_file = 'mysqld-bin.000048', MASTER_LOG_POS = 396549485; is found from the master: show master status \ G;
Load data from master;
Load data from master;
Slave start;
Solve the problem!
Network Disk resources of Titanium
Download computers and other learning materials
Http://taiyuansu.400gb.com
Hey, now I have uploaded more than 1500 documents, books, and documents.
Http://taiyuansu.400gb.com
If an advertisement page pops up, use
ADSafe is the best web advertisement interception software.
Http://www.400gb.com/file/19338616
