Collation of MySQL master-slave synchronization configuration and non-synchronization problem

Master-Slave synchronization in Linux
Primary server ip:250.110.120.119
IP:110.120.119.250 from server

1. Create a new user on the primary server and use it synchronously from the server.

The code is as follows	Copy Code
GRANT all privileges in *.* to ' Tongbu ' @ ' 110.120.119.250. ' Identified by ' Tongbu ';

Creates a new account that has all the permissions for the user named Tongbu password Tongbu.

2. Modify the master server my.cnf file
In fact, this step can be omitted, because the default configuration file can be left.
/*
Add to
Server-id=1
and open the Log-bin binary log file
Log-bin=mysql-bin
Note: The default server-id=1 need to be removed
*/

3. Restart the master server MySQL database

4. Enter the MySQL interface execute the following command to get the log file name and offset

The code is as follows	Copy Code
Show master status; Display such as: Mysql> Show master status; + —————— + ———-+ ———— –+ —————— + | File | Position | binlog_do_db | binlog_ignore_db | + —————— + ———-+ ———— –+ —————— + | mysql-bin.000010 | 106 | | | + —————— + ———-+ ———— –+ —————— + 1 row in Set (0.00 sec)

The resulting log file name is a mysql-bin.000010 offset of 106

5. Modify the my.cnf from the server
Add a
server-id=2
Note: The default server-id=1 need to be removed

6. Reboot from server MySQL database

7. Set from the database, @@@ 这 is the most important place.

The code is as follows	Copy Code
Change Master to master_host= ' 250.110.120.119 ', master_user= ' Tongbu ', master_password= ' Tongbu ', master_log_file= ' Mysql-bin.000010 ', master_log_pos=106;

8. Start from server

Start slave;
9. Perform the following command to view the process

Show Processlistg
Shows the following

The code is as follows

Copy Code

Mysql> Show Processlistg 1. Row *************************** Id:4 User:root Host:localhost Db:test Command:query time:0 State:null Info:show processlist 2. Row *************************** Id:5 User:system User Host: Db:null Command:connect time:1773 State:waiting for Master to send event Info:null 3. Row *************************** Id:6 User:system User Host: Db:null Command:connect time:1630 State:has read all relay log; Waiting for the slave I/O thread to update it Info:null 3 Rows in Set (0.00 sec)

10. View from server Status

The code is as follows

Copy Code

Show Slave STATUSG Shows the following Mysql> Show Slave STATUSG 1. Row *************************** Slave_io_state:waiting for Master to send event master_host:250.110.120.119 Master_user:tongbu master_port:3306 Connect_retry:60 master_log_file:mysql-bin.000010 read_master_log_pos:281 relay_log_file:up2-relay-bin.000002 relay_log_pos:426 relay_master_log_file:mysql-bin.000010 Slave_io_running:yes Slave_sql_running:yes replicate_do_db: 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:281 relay_log_space:579 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 Master_ssl_verify_server_cert:no last_io_errno:0 Last_io_error: last_sql_errno:0 Last_sql_error: 1 row in Set (0.00 sec)

Test:
1. Create the user table in the master server test database

The code is as follows	Copy Code
Mysql>use test; Mysql>create table user (id int);

2. View the user table from the server

The code is as follows	Copy Code
Mysql>use test; Mysql> Show tables like ' user '; + ———————-+ | Tables_in_test (user) | + ———————-+ | user | + ———————-+ 1 row in Set (0.00 sec)

Demonstrates the success of master-slave data synchronization.

Windows Master-slave synchronous operation scheme

The medium responsible for transmitting various modifications to the master and from the server is the master server's binary change log, which records the various modifications that need to be transmitted to the server. Therefore, the primary server must activate the binary logging feature. The server must have sufficient permissions to connect it to the primary server and request the primary server to transmit the binary change log to it.

Environment:
A, b MySQL database version of the same as 5.0.18
A:
Operating system: Windows 2003
IP Address: 10.100.0.100
B:
Operating system: Windows server
IP Address: 10.100.0.200

Configuration process:
1. Set up a backup account in the database of a, the command is as follows:

The code is as follows	Copy Code
GRANT REPLICATION Slave,reload,super on *.* To backup@ ' 10.100.0.200 ' Identified by ' 1234 ';

Set up an account backup and only allow landing from 10.100.0.200 this address, the password is 1234.

2, because the MySQL version of the new password algorithm is different, so enter the MySQL, enter:

The code is as follows	Copy Code
Set password for ' backup ' @ ' 10.100.0.200 ' =old_password (' 1234 ');

3, shut down a server, the data in a copy to the B server, so that the data in A and B synchronization, and ensure that all settings before the end of the operation of A and B to prohibit the write operation, so that the data in both databases must be the same!

4, modify the configuration of a server, open the Mysql/my.ini file, add the following content under [Mysqld]:

The code is as follows	Copy Code
Server-id=1 Log-bin=c:log-bin.log

Server-id: ID value for primary server A
Log-bin: Binary Change Day value

4, restart a server, from now on, it will be the customer heap of database modification records to the binary change log.

5, shut down B server, to B server Kam-hee configuration, so that it knows its own mirror ID, where to Shing the server and how to connect to the server. The simplest scenario is that the master, from the server, runs on separate hosts and uses the default TCP/IP port, as long as the following lines are added to the Mysql/my.ini file that is read from the server startup.

The code is as follows	Copy Code
[Mysqld] server-id=2 master-host=10.100.0.100 Master-user=backup master-password=1234 The following items are optional Replicate-do-db=backup

Server-id: The ID value from Server B. Note You cannot have the same ID value as the primary server.
Master-host: The IP address of the primary server.
Master-user: The account number that connects the primary server from the server.
Master-password: Connect the primary server's account password from the server.
Replicate-do-db: Tells the master server to synchronize mirrors only for the specified database.

6, reboot from Server B. All settings are complete. Update the data in a, and the update will be synchronized immediately in B. If you do not synchronize updates from the server, you can check the Mysql_error.log log files from the server for errors.

8, check the log some commands
1, show Master STATUSG;
The main thing here is to see if the Log-bin files are the same.
show slave statusg;
Here is mainly to see:
Slave_io_running=yes
Slave_sql_running=yes
If all is yes, the configuration is successful.

2, enter show Processlistg on master;

/tr>

code is as follows	copy code
mysql> show Processlistg      * * * 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 to &N bsp;        be updated        info:null

If Command:binlog Dump appears, the configuration is successful.

MySQL Master never sync problem Resolution

1. First stop slave service:

mysql> slave stop2. View host status on the primary server:
Record the values for file and position.

The code is as follows

Copy Code

mysql> Show master status; +------------------+-----------+--------------+------------------+ | File | Position | binlog_do_db | binlog_ignore_db | +------------------+-----------+--------------+------------------+ | mysql-bin.000020 | 135617781 | | | +- -----------------+-----------+--------------+------------------+ 1 row in Set (0.00 sec) 3. Perform a manual synchronization on the slave server: Mysql> Change Master to > master_host= ' master_ip ', > master_user= ' user ', > master_password= ' PWD ', > master_port=3307, > master_log_file= ' mysql-bin.000020 ', > master_log_pos=135617781; 1 row in Set (0.00 sec) mysql> slave start; 1 row in Set (0.00 sec) View slave status Discovery again: slave_io_running:yes< br> slave_sql_running:yes

...
Seconds_behind_master:0 we know that because the data replication mechanism is entirely based on the incremental SQL executed in the master to be propagated to the secondary server and run successfully. This is bound to require that the data in the primary and secondary database be consistent before running this mechanism, and that in running this mechanism, the secondary database prohibits write operations from other SQL (non-master-propagated SQL). However, inconsistent production may still be encountered in the runtime, which can cause communication to continue indefinitely. So once the master and slave problems, first of all should solve the problem of synchronization location, repair lost data

mysql Master never sync problem Resolution

Problem Description: Master-slave replication at some point, synchronization from the server failed, and give an error message.

Message content: Basic meaning is missing a table for a library

Cause: The master-slave service is not synchronized for a long time, the main library has generated many new tables or libraries, but not from the library.

Operation Process:
(1) Log on to the primary server to view the status of the primary server

The code is as follows	Copy Code
Mysql>show Master status;

(2) Log in from the server, perform synchronous operation. [Code]

The code is as follows	Copy Code
Mysql>stop slave;mysql > Change Master to ... (omitted here); MySQL > Start slave;

(3) View status from the server [code]

The code is as follows	Copy Code
MySQL > show slave statusg

Read the wrong information less what table or library, what is less directly from the primary server through the SCP replication, and then repeat the process (1) ~ (3) until no error