Backup method of master-slave synchronous data in MySQL database

I remember I used to write a diary in this site is a record of the master and slave MySQL Tutorial database tutorial synchronization Backup related articles, do not know that a friend has not seen, the method is very simple, is certainly can be achieved, the following I only refer to some online methods, not tested.

Environment:
Master server: CentOS 5.2 mysql 5.1.35 source ip:192.168.1.22
From server: CentOS 5.2 mysql 5.1.35 source ip:192.168.1.33
Configuration:
First, the main server
1.1, create a replication user, with replication slave permissions.
Mysql>grant replication Slave on *.* to ' repl ' @ ' 192.168.1.22 ' identified by ' repl ';
1.2. Edit My.cnf file
Vi/etc/my.cnf
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
1.3. Start MySQL Database
Mysqld_safe--user=mysql &
1.4. Set Read lock
Mysql>flush tables with read lock;
1.5, get binlog log file name and offset
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.0000010 | 106| | |
+------------------+----------+--------------+------------------+
1.6, Backup to synchronize the database
Mysqldump Test > Test.sql
1.7, Unlock
Mysql>unlock tables;
Second, from the server
2.1. Edit My.cnf File
Vi/etc/my.cnf
Add to
server-id=2
Note: The default server-id=1 need to be removed
2.2. Start from Database
Mysqld_safe--user=mysql &
2.3, the corresponding settings from the database
Mysql> Change Master to
-> master_host= ' 192.168.1.22 '
-> master_user= ' Repl '
-> master_password= ' Repl '
-> master_log_file= ' mysql-bin.0000010 '
-> master_log_pos=106;
2.4, start from the server slave thread
Mysql>start slave;
Perform the show processlist command to display the following processes:
Mysql>show PROCESSLISTG
2. Row ***************************
Id:2
User:system User
Host:
Db:null
Command:connect
time:2579
State:has read all relay log; Waiting for the slave I/O thread to update it
Info:null indicates that slave is connected to master and begins to accept and execute the log
2.5. View slave thread Status
mysql>show slave status;
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.1.22
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.0000010
read_master_log_pos:106
relay_log_file:centos-relay-bin.000002
relay_log_pos:529
relay_master_log_file:mysql-bin.0000010
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:106
relay_log_space:830
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)
Verify that the configuration is correct
Executing on the server from
show slave statusg;
Waiting for master to send event
Slave_io_running:yes
Slave_sql_running:yes
If the above two lines at the same time for Yes to describe the successful configuration

Test
1. Create the user table in the master server test database
Mysql>use test;
Mysql>create table user (id int);
2. View the user table from the server
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.
Problem?
1. Occurs when viewing the slave state from the database
The slave I/O thread stops tutorial because master and slave have equal MySQL server IDs; These IDs must is different for replication to work (or the--replicate-same-server-id option must is used on slave but th Is does to always make sense; Please check the manual before using it)
The description side has the same server-id from the MY.CNF in the server.
Solution:
Modify the Server-id in the MY.CNF and restart the database service. MY.CNF file Default has server-id=1
Other Notes
Primary server My.cnf
#binlog-do-db= The name of the database you want to back up, write multiple lines
#binlog-ignore-db= database names that do not need to be backed up, write multiple lines
MY.CNF from server
# Replicate-do-db=test The name of the database that needs to be backed up
# Replicate-ignore-db=mysql ignored Database
# MASTER-CONNECT-RETRY=60 If the primary server is found broken from the server, the time lag (in seconds) to reconnect
The following settings can also directly modify the MY.CNF configuration file
Log-bin=mysql-bin
master-host=192.168.1.22
Master-user=repl
Master-password=repl
master-port=3306

Synchronous maintenance of master-slave server
For a variety of reasons, resulting in inconsistent master-slave data, when the load is low, manual synchronization.
Executing on the primary server
Mysql>flush tables with read lock;
Query Ok,rows Affected (0.01 sec)
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.0000011 | 260| | |
+------------------+----------+--------------+------------------+
Executing on the server from
Get the binary file name and offset of the current primary server, and execute the command to sync from the server to the primary server
Mysql>select master_pos_wait (' mysql-bin.0000011 ', ' 260 ');
+--------------------------------------------------+
| Master_pos_wait (' mysql-bin.0000011 ', ' 260 ') |
+--------------------------------------------------+
| 0 |
+--------------------------------------------------+
1 row in Set (0.01 sec)
After synchronization completes, unlocking on the primary server is performed
Mysql>unlock tables;
Switching between master and slave servers
When a primary server fails, it can be used from the server as the primary server. The steps are as follows:
1, to ensure that all from the database has been executed in the relay log all the updates, the implementation from the server
Stop slave io_thread, check with show processlist to see if the status is has read all relay log to indicate that the update is complete.
Mysql>stop slave io_thread;
Query ok,0 Affected (0.00 sec)
Mysql>show Processlistg;
2. Row ***************************
Id:2
User:system User
Host:
Db:null
Command:connect
time:4757
State:has read all relay log; Waiting for the slave I/O thread to update it
Info:null
2, the implementation of the Stop Slave,reset Master command from the server, reset to the main database
Mysql>stop slave;
Query ok,0 Affected (0.00 sec)
Mysql>reset master;
Query ok,0 Affected (0.00 sec)
3, delete the new master server database directory of Master.info and Relay-log.info files, or the next reboot will also follow the boot from the server.