MySQL master-slave synchronization instance-slave synchronization backup steps

Environment:
MySQL database versions of A and B are 5.0.24.

A: master server
Operating System: Windows XP
IP Address: 192.168.0.81
B: slave server
Operating System: Windows XP
IP Address: 192.168.0.82

Configuration process:
1. Create a backup account in database A. The command is as follows:
Grant replication slave, reload, super on *.*
To test @ '192. 168.0.82'
Identified by '000000 ′;

Create an account "test" and only Allow logon from 192.168.0.82. The password is 1234.

Perform a connection test on B slave:

Mysql-H 192.168.0.81-U test-P

2. modify the configuration of server a, open the MySQL/My. ini file, and add the following content under [mysqld:
Server-id = 1
Log-bin = D: \ server2 \ mysql \ log-bin.log

Server-ID: ID value of master server
Log-bin: Binary Change Daily Value

3. Restart server A. From now on, it will record changes to the database related to the customer heap into the binary change log.

4. modify the configuration of server B, open the MySQL/My. ini file, and add the following content under [mysqld:
[Mysqld]
Server-id = 2
Master-host = 192.168.0.81
Master-user = test
Masters-Password = 1234

Master-Port = 3306 # master server port
Master-connect-retry = 60 # synchronization interval: 60 seconds
// Optional

# Databases to be synchronized
Replicate-do-DB = test

# Ignore databases that do not need to be synchronized
BINLOG-ignore-DB = MySQL

Server-ID: ID of slave server B. Note that it cannot be the same as the ID of the master server.
Master-Host: the IP address of the master server.
Master-user: The account connecting the slave server to the master server.
Master-Password: the account and password used to connect the slave server to the master server.
Replicate-do-DB: tells the master server to only synchronize images for the specified database.

BINLOG-ignore-DB: tells the master server not to synchronize the image of the specified database.

5. Restart slave server B. Now all settings are complete. Update the data in a, and B will also perform synchronization updates immediately. If the slave server is not synchronously updated, You Can troubleshoot the problem by checking the mysql_error.log file on the slave server.

On the master node, you do not need to set anything. You only need to open log-bin, write server-id = 1, and write the database to be backed up. The master mode is automatically used, as a result, the problem is mainly concentrated on slave. so how does slave work?
MySQL replication on slave has two threads: I/O thread and SQL thread, i/O is used to retrieve its BINLOG from the master port 3306 (after the master has modified anything, write the modified information to your BINLOG for slave update) and write it to the local relay-log, while the SQL thread reads the local relay-log, the conversion cost is what MySQL can understand, So synchronization is completed step by step. mySQL/master.info decides I/O thread, and SQL
Thread is MySQL/relay-log.info.
Note that the content in BINLOG is changed, rather than the content in the future. Therefore, you must ensure that the two databases are identical before synchronization, otherwise, an error may occur. for example. on Machine A, there is a table with 2 tuples, and the operation is minus one, then BINLOG only records the operation minus one. If there is no such operation on machine B, the data cannot be synchronized, machine B does not have this word segment, so it does not know what operations to perform.

========================================================== ======================================

Error FAQ:

1. Master/Slave nodes cannot be synchronized:

Show slave status; error: Error XXX dosn't exist
And show slave status \ G:
Slave_ SQL _running: No
Seconds_behind_master: NULL

Solution:
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave;

After that, slave will synchronize with the master. The main points are as follows:

Slave_io_running: Yes
Slave_ SQL _running: Yes
Seconds_behind_master is synchronized if it is 0 or 0

2. Some optimizations and monitoring are also required:
Show full processlist; // view the current MySQL synchronization thread number
Skip-name-resolve // skip DNS name query, which helps speed up connection and Synchronization
Max_connections = 1000 // increase the number of MySQL connections (100 by default)
Max_connect_errors = 100 // increase the number of MySQL error connections (default: 10)

View logs
1. show Master Status \ G;
Here, we mainly check whether the log-bin files are the same.
Show slave status \ G;
Here we mainly look:
Slave_io_running = Yes
Slave_ SQL _running = Yes
If yes, the configuration is successful.

2. Enter show processlist \ G on the master;
Mysql> show processlist \ G
* *************************** 1. row ***************************
ID: 2
User: Root
HOST: localhost: 32931
DB: NULL
Command: BINLOG dump
Time: 94
State: has sent all BINLOG to slave; waiting for BINLOG
Be updated
Info: NULL

If command: BINLOG dump appears, the configuration is successful.

========================================================== ============================================
Stop slave # Stop Synchronization
Start slave # Start synchronization and update from the log termination location.
Set SQL _log_bin = 0 | 1 # run on the host. The super permission is required to enable or stop the log. If the log is enabled or disabled at will, the data on the host slave will be inconsistent, resulting in errors.
Set global SQL _slave_skip_counter = N # Run the client to skip several events. It can be executed only when the synchronization process stops when an error occurs.
Reset master # run on the host to clear all logs. This command is the original flush master.
Reset slave # Run from the machine, clear the log synchronization location mark, and regenerate master.info
Although master.info is re-generated, it cannot be used. It is best to restart the MySQL process on the slave machine,
Load table tblname from master # run on the slave machine. The data in the specified table can be re-viewed from the host. Only one table can be read at a time. Due to the time limit of timeout, you need to adjust the timeout time. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Load data from master # Run from the slave machine and read all the data from the host. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Change master to master_def_list # online Change of some host settings. Separate multiple settings with commas (,). For example
Change master
Master_host = 'master2 .mycompany.com ',
Master_user = 'replicase ',
Master_password = 'bigs3cret'
Master_pos_wait () # Run from the slave machine
Show Master Status # Run the host and view the log export information
Show slave hosts # Run the host to check the connected slave machine.
Show slave status (slave)
Show Master logs (master)
Show BINLOG events [IN 'logname'] [from POS] [limit [offset,] rows]
Purge [Master] logs to 'logname'; purge [Master] logs before 'date'