Mysql master-slave synchronous backup policy sharing _ MySQL

Mysql master-slave synchronous backup policy sharing bitsCN.com environment:
The MySQL database version on the master and slave servers is 5.1.34.
Host IP: 192.168.0.1
Slave IP address: 192.168.0.2
I. MySQL master server configuration
1. edit the configuration file/etc/my. cnf
# Make sure it is as downstream
Server-id = 1
Log-bin = mysql-bin
Binlog-do-db = mysql # Name of the database to be backed up. if multiple databases are backed up, set this option again.
Binlog-ignore-db = mysql # The database name that does not need to be backed up. if multiple databases are backed up, set this option again.
Log-slave-updates # This parameter must be added; otherwise, the updated records will not be added to the binary file.
Slave-skip-errors # Skip the error and continue the copy operation
2. create a user
Mysql> Grant Replication Slave On *.* To Slave@192.168.0.2 Identified By '000000 ′;
# Grant Replication Slave On *.* To 'Username' @ 'host' Identified By 'Password ';
# You can perform connection tests on Slave: Mysql -H 192.168.0.1 -U Test -P
3. lock the master database table
Mysql> FLUSH TABLES WITH READ LOCK;
4. display master database information
Record File and Position, which will be used in slave database settings
==================================
Mysql> SHOW MASTER STATUS;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_do_db | Binlog_ignore_db |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 106 | | |
+ ------------------ + ---------- + -------------- + ------------------ +
5. open another terminal and package the master database
Cd /Usr/local/mysql # Mysql database Directory
Tar Zcvf Var.tar.gz Var
======================================
II. MySQL slave server configuration
1. data packets are transmitted to and unbound from the master database.
# Cd /Usr/local/mysql
# Scp 192.168.0.1:/usr/local/mysql/var.tar.gz .
# Tar Zxvf Var.tar.gz
2. view and modify the var folder permissions
# Chown -R Mysql: mysql Var
3. Edit /Etc/my. cnf
Server-id = 2
Log-bin = mysql-bin
Master-host = 192.168.0.1
Master-user = slave
Masters-password = 111111
Master-port = 3306
Replicate-do-db = test # Name of the database to be backed up
Replicate-ignore-db = mysql # Ignored database
Master-connect-retry = 60 # The time difference (in seconds) when the master server is disconnected from the server)
Log-slave-updates # This parameter must be added; otherwise, the updated records will not be added to the binary file.
Slave-skip-errors # Skip the error and continue the copy operation
4. verify the connection to the MASTER
# Mysql -H192.168.0.1 -Uslave -Ppassword
Mysql> Show Grants For Slave@192.168.0.2;
5. set synchronization on SLAVE
Set MASTER connection MASTER_LOG_FILE is the File of the master database, and MASTER_LOG_POS is the Position of the master database.
======================================
Mysql> Slave Stop;
Mysql> CHANGE MASTER TO MASTER_HOST = '1970. 168.0.1 ', MASTER_USER = 'Slave', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER_LOG_POS = 192;
6. start the SLAVE service
Mysql> Slave Start;
7. view the SLAVE status
Mysql> SHOW SLAVE STATUS/G;
Where Slave_IO_Running And Slave_ SQL _Running The values of both columns are "Yes", indicating Slave Of I/O And SQL All threads are running normally.
8. unlock the master database table
Mysql> UNLOCK TABLES;
The master-slave database is successfully built. You can insert data into the master database to test whether the synchronization is normal.
--------------------------
Appendix: handle some error messages, commands on the master and slave servers, and status information.
Use show on the slave server Slave Status/G
Slave_IO_Running, No,
The IO_THREAD is not started. run start Slave Io_thread
Slave_ SQL _Running is No
A replication error occurs. check the Last_error field to exclude the error and run start. Slave SQL _thread
The Slave_IO_State field is empty. // The replication is not started.
Connecting To Master // no connection to the master
Waiting For Master To Send Event // already connected
Commands on the master server:
Show Master Status
Show Slave Hosts
Show Logs
Show Binlog Events
Purge Logs To 'Log _ name'
Purge Logs Before 'Date'
Reset Master (earlier version of flush Master)
Set SQL _log_bin =
Commands on the slave server:
Slave Start
Slave Stop
SLAVE STOP IO_THREAD // This thread writes logs of the master segment to the local device.
SLAVE Start IO_THREAD
SLAVE STOP SQL _THREAD // This thread applies logs written locally to the database
SLAVE Start SQL _THREAD
Reset Slave
SET GLOBAL SQL _SLAVE_SKIP_COUNTER
Load Data From Master
Show Slave Status (SUPER, REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST =, MASTER_PORT =, MASTER_USER =, MASTER_PASSWORD = // Dynamically change master information
PURGE MASTER [Before 'Date'] Delete logs that have been synchronized from the master.
6.3.1 Master Synchronous thread status
The following lists Binlog Dump Thread State The most common status of a field. If no Binlog Dump The synchronization is not running.
That is to say, there is no slave connection.
Sending Binlog Event To Slave
An event is composed of binary logs. an event is usually composed of an update statement and other information. The thread reads an event and sends it to slave.
Finished Reading One Binlog; Switching To Next Binlog
After reading a binary log, switch to the next one.
Has Sent All Binlog To Slave; Waiting For Binlog To Be Updated
You have read all the incomplete update logs and sent them to slave. It is idle and is waiting to execute new update operations on the master to generate new data in binary logs.
And then read them.
Waiting To Finalize Termination
The current thread has stopped. this time is short.
6.3.2 Slave I/O thread status
The following lists the slave I/O threads. State The most common status of a field. From MySQL 4.1.1. this status is being executed. SHOW SLAVE STATUS Statement result
Slave_IO_State Fields will also appear. This means that you can only execute SHOW SLAVE STATUS Statement to learn more.
Connecting To Master
This thread certificate tries to connect to the master.
Checking Master Version
Determine a transient status after the master node is connected.
Registering Slave On Master
Determine a transient status after the master node is connected.
Requesting Binlog Dump
Determine a transient status after the master node is connected. This thread sends a request to the master to inform it of the binary file to be requested and the starting position.
Waiting To Reconnect After A Failed Binlog Dump Request
If binary log dump (binary Log Dump) if the request fails (due to disconnection), the thread enters this state during sleep and reconnects regularly. The reconnection interval is --
Master-connect-retry Option.
Reconnecting After A Failed Binlog Dump Request
This thread is trying to reconnect to the master.
Waiting For Master To Send Event
Connected to the master, waiting for it to send binary logs. If the master node is idle, the status may last for a long time. Slave_read_timeout Seconds
. In this case, it will consider disconnecting and then trying to reconnect.
Queueing Master Event To The Relay Log
An event has been read and is being copied to the relay log for processing by the SQL thread.
Waiting To Reconnect After A Failed Master Event Read
An error occurred while reading the log (due to disconnection ). This thread sleep before reconnection Master-connect-retry Seconds.
Reconnecting After A Failed Master Event Read
Attempting to reconnect to the master. After the connection is confirmed, the status changes Waiting For Master To Send Event.
Waiting For The Slave SQL Thread To Free Enough Relay Log Space
Relay_log_space_limit The value is not zero. The total size of the relay log exceeds this value. The I/O thread waits for the SQL thread to process the relay logs and then delete them to free up enough space.
.
Waiting For Slave Mutex On Exit
The current thread has stopped. this time is short.
6.3.3 Slave SQL thread status
The SQL threads of slave are listed below State The most common status of a field:
Reading Event From The Relay Log
Read an event from the relay log for execution.
Has Read All Relay Log; Waiting For The Slave I/O Thread To Update It
All the events in the relay log have been processed and are waiting for the I/O thread to write the updated log.
Waiting For Slave Mutex On Exit
The current thread has stopped. this time is short.
--------------
Common problems:
I. SLAVE database SLAVE startup problems
Due to some incorrect operations CHANGE MASTER And SLAVE The service cannot be started. The system reports the following error:
**************************************** *************************
Cocould Not Initialize Master Info Structure; More Error Messages Can Be Found In The MySQL Error Log.
**************************************** *************************
Unable to initialize master Info structure. MySQL error logs record more detailed error information.
Solution:
1. view the MySQL error log, for example, the previous Position of synchronization. in many cases, the service cannot be started because the synchronization identified by mysql always stays on the previous Position.
2. View master.info and relay-log.info, master.info Record MASTER related information, relay-log.info Record the current synchronization log information.
3. stop the myslq service and delete master.info and relay-log.info.
4. start the mysql service.
5. CHANGE again MASTER to restart the SLAVE service.
2. master/slave 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. Mainly refer:
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 // Skipping dns name queries helps speed up connection and synchronization
Max_connections = 1000 // Increase the number of Mysql connections (100 by default)
Max_connect_errorrs = 100 // Increase the number of wrong connections of Mysql (10 by default)
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 on the master. Processlist/G;
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 To
Be Updated
Info: NULL
If Command: Binlog Dump indicates that 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 # When running on the host, the super permission is required to enable or stop logs at will, which may result in inconsistent host data and errors
SET GLOBAL SQL _SLAVE_SKIP_COUNTER = n # The client runs and is used to skip several events. it can be executed only when the synchronization process stops due to an error.
RESET MASTER # Run the command on the host to clear all logs. This command is the original FLUSH command. MASTER
RESET SLAVE # Run from the server, 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 # When running from 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 timeout time limit, you need to adjust the timeout time. To execute this command, the synchronization account must have Reload and super permissions. And have the select permission on the corresponding database. If the table is large, add net_read_timeout. And Value of net_write_timeout
LOAD DATA FROM MASTER # Run the command on the slave machine and read all the data from the host again. 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 net_read_timeout. And Value of net_write_timeout
CHANGE MASTER TO Master_def_list # Online change of some host settings, separated by commas (,). For example
CHANGE MASTER TO
MASTER_HOST = 'master2 .mycompany.com ',
MASTER_USER = 'replicase ',
MASTER_PASSWORD = 'bigs3cret'
MASTER_POS_WAIT () # Run from the slave machine
SHOW MASTER STATUS # Host running, view log export information
SHOW SLAVE HOSTS # Run the host to check the connection status of the slave.
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'
Show Binlog Events; # View the binary log file of the master database:
Note:
1. the primary and secondary databases are synchronized mainly through binary logs.
2. when starting the secondary database, you must synchronize the data and delete the master.info file in the log directory. Because master.info records the information of the master database to be connected last time. if it is not deleted, it does not work even if it is modified in my. cnf. Because the read information is still in the master.info file. BitsCN.com