Standalone version of Mysql Master/Slave Database Configuration

Environment mysql-5.0.22 decompress version. (Copy in two copies, master, slave) windowsXP I. Master Settings 1. Prepare my. ini [client] port3308 [mysqld] port3308server-id1binlog-do-dbtest1 # The database name to be synchronized is the test1log-binmysql-bin and then run: GR on the Console

Environment mysql-5.0.22 decompression version. (Copy in two copies, master, slave) windowsXP I. Master Settings 1. Prepare my. ini [client] port = 3308 [mysqld] port = 3308 server-id = 1 binlog-do-db = test1 # Name of the database to be synchronized: test1 log-bin = mysql-bin then run the following command on the Console: GR

Environment
Mysql-5.0.22 decompress version. (Copy two copies, master and slave)
Windows XP
1. Master Settings
1. Prepare my. ini
[Client]
Port = 3308
[Mysqld]
Port = 3308
Server-id = 1
Binlog-do-db = test1 # Name of the database to be synchronized: test1
Log-bin = mysql-bin

Then execute: grant file, SELECT, replication slave on *. * TO 'slaver1' @ '192. 0.0.1 'identified by 'slaver1pwd' ON the Console ';
Open a new account (username: slaver1 password: slaver1pwd) and grant permissions
Run mysqld.exe and create a new database test1. you can enter show master status \ G on the Console to view the status!

* *************************** 1. row *****************
File: mysql-bin.000305
Position: 98
Binlog_Do_DB: test1
Binlog_Ignore_DB:

Ii. Slaver settings

1. Prepare my. ini
[Client]
Port = 3309

[Mysqld]
Port = 3309
Server-id = 2

Master-host = 127.0.0.1

Master-user = slaver1

Master-password = slaver1pwd

Master-port = 3308

Replicate-do-db = test1 # database to be synchronized

Run mysqld.exe of Slaver and run show slave status \ G in the Console window to view the status, as shown below:

* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slaver1
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000305
Read_Master_Log_Pos: 98
Relay_Log_File: ccf-3acf6017ef4-relay-bin.000005
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000305
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: test1
Replicate_Ignore_DB:
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: 98
Relay_Log_Space: 235
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

FAQs

1. SQL I/O Running: no

Cause: the user name or password is incorrect, and the slaver1 permission is insufficient!

If the execution is correct: grant file, SELECT, replication slave on *. * TO 'slaver1' @ '127. 0.0.1 'identified BY 'slaver1pwd'; SQL I/O Running: NO, it may be that the Slaver User in the User management of the Master does not select the REPLICATION Client (use Navicat-Manager User-> Slaver1 to the right of the optional list)

2. Slave_ SQL _Running: NO

Cause:
A. The program may write data on slave.
B. Transaction rollback after the Server Load balancer instance restarts.

Solution:

On the Slaver Console:

2. 1. Stop the Slave Service

Mysql> slave stop
2.2 view the status of the Master: (execute on the Master)
Show master status \ G

2.3 manual synchronization

Mysql> change master
> Master_host = '2017. 0.0.1 ',
> Master_user = 'slaver1 ',
> Master_password = 'slaver1pwd ',
> Master_port = 3308,
> Master_log_file = 'mysql-bin.20.20 ',
> Master_log_pos = 98;
2.4 restart slave

Mysql> slave start;

Appendix

1. View abnormal information files: files with the suffix ". err" are generally stored in the folder of data (where the data inventory files are stored ).

2. show processlist \ G can help you view user information