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

Source: Internet
Author: User
Tags log log mysql version

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.